OraFAQ Forum: Performance Tuning » Response time is high in 10g

Home » RDBMS Server » Performance Tuning » Response time is high in 10g (Oracle10.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Response time is high in 10g [message #415255]

Sun, 26 July 2009 17:49

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..

Report message to a moderator

Re: Response time is high in 10g [message #415256 is a reply to message #415255]

Sun, 26 July 2009 18:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

from sqlplus on both versions do the following:
SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SQL> -- NOW INVOKE SUSPECT SQL
SQL> --CUT whole session & PASTE back here using <code tags>
SQL> EXIT

[Updated on: Sun, 26 July 2009 18:44]

Report message to a moderator

Re: Response time is high in 10g [message #415308 is a reply to message #415256]

Mon, 27 July 2009 03:34

prachij593
Messages: 266
Registered: May 2009

Senior Member

Sir,
The plan is much similar when we use select part. In 9i and 10g both are same.

But it becomes different with DELETE

Report message to a moderator

Re: Response time is high in 10g [message #415321 is a reply to message #415255]

Mon, 27 July 2009 04:41

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Your 10g plan is using parallel processing (PX steps), the 9i plan isn't. Try disabling parallel processing.

Report message to a moderator

Re: Response time is high in 10g [message #415340 is a reply to message #415321]

Mon, 27 July 2009 05:35

prachij593
Messages: 266
Registered: May 2009

Senior Member

I tried to use no_parallel hint.But it didnt work.
The plan is the same with the select but when I use this query
as subquery in DELETE its giving bad plan.

Report message to a moderator

Re: Response time is high in 10g [message #415344 is a reply to message #415340]

Mon, 27 July 2009 05:47

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Not sure whether this helps you or not. Try to check the parameters in both the instances.

select name,value
from v$parameter 
where name like '%parallel%';

By
Vamsi

Report message to a moderator

Re: Response time is high in 10g [message #415347 is a reply to message #415344]

Mon, 27 July 2009 05:54

prachij593
Messages: 266
Registered: May 2009

Senior Member

Sir, in both 9i and 10g we have same plan for the select .
But when we use the select query as sub and issue a delete from the select data its going for different plan in 10g

Report message to a moderator

Re: Response time is high in 10g [message #415351 is a reply to message #415255]

Mon, 27 July 2009 05:59

cookiemonster
Messages: 13958
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>);

Report message to a moderator

Previous Topic:	Funtion Tuning
Next Topic:	Different plan for same sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 23:10:12 CST 2024