Home » RDBMS Server » Performance Tuning » Response time is high in 10g (Oracle10.2.0.3)
Response time is high in 10g [message #415255] Sun, 26 July 2009 17:49 Go to next message
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 #415256 is a reply to message #415255] Sun, 26 July 2009 18:43 Go to previous messageGo to next message
BlackSwan
Messages: 25037
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 Go to previous messageGo to next message
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
Re: Response time is high in 10g [message #415321 is a reply to message #415255] Mon, 27 July 2009 04:41 Go to previous messageGo to next message
cookiemonster
Messages: 12404
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.
Re: Response time is high in 10g [message #415340 is a reply to message #415321] Mon, 27 July 2009 05:35 Go to previous messageGo to next message
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.





Re: Response time is high in 10g [message #415344 is a reply to message #415340] Mon, 27 July 2009 05:47 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: Response time is high in 10g [message #415347 is a reply to message #415344] Mon, 27 July 2009 05:54 Go to previous messageGo to next message
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
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
cookiemonster
Messages: 12404
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>);
Previous Topic: Funtion Tuning
Next Topic: Different plan for same sql
Goto Forum:
  


Current Time: Mon Dec 05 10:53:37 CST 2016

Total time taken to generate the page: 0.25340 seconds