Response time is high in 10g [message #415255] |
Sun, 26 July 2009 17:49 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
explain plan FOR
DELETE
FROM WP
WHERE sid IN (
SELECT a.sid
FROM WP a, CSM b, C_I_CSM c, ITM d
WHERE a.p_id IN (
SELECT p_id
FROM WP
GROUP BY p_id
HAVING COUNT(*) > 1)
AND a.p_id = b.c_id
AND b.csid = c.csid
AND c.cid = d.p_id
AND c.scid = d.spid
AND d.last_upd_tms = mx_tms(d.p_id, d.spid)
AND c.cid = b.csid
AND a.nme IN (
SELECT DISTINCT nme
FROM app
WHERE nme IS NOT NULL)
AND TRIM(a.nme) <> TRIM(d.nme)) ;
SELECT * FROM TABLE(dbms_xplan.display);
Execution Plan
in 10G.
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 28080 (100)| |
| 1 | DELETE | WP | | | | |
| 2 | PX COORDINATOR | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10004 | 76225 | 6845K| 28080 (1)| 00:05:37 |
| 4 | HASH JOIN RIGHT SEMI | | 76225 | 6845K| 28080 (1)| 00:05:37 |
| 5 | PX RECEIVE | | 1 | 13 | 42722 (35)| 00:08:33 |
| 6 | PX SEND HASH | :TQ10003 | 1 | 13 | 42722 (35)| 00:08:33 |
| 7 | VIEW | VW_NSO_2 | 1 | 13 | 42722 (35)| 00:08:33 |
| 8 | HASH JOIN | | 1 | 130 | 42722 (1)| 00:08:33 |
| 9 | TABLE ACCESS BY INDEX ROWID | ITM | 1 | 31 | 4 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 117 | 42592 (1)| 00:08:32 |
| 11 | NESTED LOOPS | | 5172 | 429K| 22152 (1)| 00:04:26 |
| 12 | BUFFER SORT | | | | | |
| 13 | PX RECEIVE | | | | | |
| 14 | PX SEND ROUND-ROBIN | :TQ10000 | | | | |
| 15 | NESTED LOOPS | | 5172 | 344K| 11932 (1)| 00:02:24 |
| 16 | NESTED LOOPS | | 2963 | 159K| 71 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | APP | 65 | 780 | 6 (0)| 00:00:01 |
| 18 | INDEX RANGE SCAN | PK_WP | 45 | 1935 | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID| CSM | 2 | 28 | 4 (0)| 00:00:01 |
| 20 | INDEX RANGE SCAN | XIF155CSM | 2 | | 2 (0)| 00:00:01 |
| 21 | INDEX RANGE SCAN | XPKC_I_CSM | 1 | 17 | 2 (0)| 00:00:01 |
| 22 | INDEX RANGE SCAN | XPKITM | 1 | | 3 (0)| 00:00:01 |
| 23 | BUFFER SORT | | | | | |
| 24 | PX RECEIVE | | 76225 | 752K| 127 (5)| 00:00:02 |
| 25 | PX SEND BROADCAST | :TQ10001 | 76225 | 752K| 127 (5)| 00:00:02 |
| 26 | VIEW | VW_NSO_1 | 76225 | 752K| 127 (5)| 00:00:02 |
| 27 | FILTER | | | | | |
| 28 | SORT GROUP BY | | 76225 | 752K| 127 (5)| 00:00:02 |
| 29 | INDEX FAST FULL SCAN | WP01 | 76225 | 752K| 122 (1)| 00:00:02 |
| 30 | BUFFER SORT | | | | | |
| 31 | PX RECEIVE | | 76225 | 5961K| 122 (1)| 00:00:02 |
| 32 | PX SEND HASH | :TQ10002 | 76225 | 5961K| 122 (1)| 00:00:02 |
| 33 | INDEX FAST FULL SCAN | WP01 | 76225 | 5961K| 122 (1)| 00:00:02 |
[code]
[/code]
In 9i,
--------------------------------------------------------------------------------------------------------------------
283818490 0 |DELETE STATEMENT |----- ----- | | 12101 |
283818490 1 |DELETE | | | |
283818490 2 | HASH JOIN SEMI | | 1 | 12101 |
283818490 3 | INDEX FAST FULL SCAN |PK_WP | 29K| 196 |
283818490 3 | VIEW | | 1 | 10908 |
283818490 4 | HASH JOIN | | 1 | 10908 |
283818490 5 | TABLE ACCESS BY INDEX ROWID |ITM | 1 | 4 |
283818490 6 | NESTED LOOPS | | 1 | 10904 |
283818490 7 | NESTED LOOPS | | 1 | 10900 |
283818490 8 | NESTED LOOPS | | 2K| 6056 |
283818490 9 | HASH JOIN | | 1K| 432 |
283818490 10 | VIEW | | 961 | 235 |
283818490 11 | FILTER | | | |
283818490 12 | SORT GROUP BY | | 961 | 235 |
283818490 13 | INDEX FAST FULL SCAN|PK_WP | 29K| 196 |
283818490 10 | INDEX FAST FULL SCAN |PK_WP | 29K| 196 |
283818490 9 | TABLE ACCESS BY INDEX RO|CSM | 2 | 4 |
283818490 10 | INDEX RANGE SCAN |XIF155CSM | 2 | 2 |
283818490 8 | INDEX RANGE SCAN |XPKC_I_CSM | 1 | 2 |
283818490 7 | INDEX RANGE SCAN |XPKITM| 1 | 3 |
283818490 5 | TABLE ACCESS FULL |APP | 65 | 4 |
The sql is taking much time in 10g (after upgrading) than 9i
In 9i: 2 sec
In 10g 1 min
What need to be done to tune it?
Thanks..
|
|
|
|
|
|
|
|
|
Re: Response time is high in 10g [message #415351 is a reply to message #415255] |
Mon, 27 July 2009 05:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I think we've gathered that by now.
Run vamsi's query on both nodes, and this one as well:
SELECT table_name, degree
FROM user_tables
WHERE table_name in (<list of tables referenced in your query>);
|
|
|