Home » RDBMS Server » Performance Tuning » Same query with different Explain Plan in two Database (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0)
Same query with different Explain Plan in two Database [message #563792] Fri, 17 August 2012 02:38 Go to next message
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
Member
Hi All,

I am facing a weird situation wherein the explain plan of same sql in SIT and PROD is different.
In fact the explain plan is very costly in Prod.

Also the DB version of both SIT and PROD is same.

Below is the sql and corresponding explain plan in Prod and SIT respectively.

Query:

SELECT seq,CCN,ProcessorPart,root_item,comp_path,Item,comp_item,comp_item_type,
lag(comp_item_type,1,'PART') over(PARTITION BY seq ORDER BY lvl)Nxt_comp_item_type,lvl,bom_qty,
        ROUND(CASE min(abs(bom_qty)) OVER (PARTITION BY seq ORDER BY lvl)
        WHEN 0 THEN 0 ELSE 1 END * EXP (SUM (LN (nullif(abs(bom_qty),0))) OVER (PARTITION BY seq ORDER BY lvl))) Ulti_qty,
        'AMER'
        FROM
        (
        SELECT y.seq,y.CCN,y.ProcessorPart,y.root_item,y.comp_path,y.Item,y.comp_item,y.lvl,y.bom_qty,
        (
        SELECT comp_item_type
        FROM E2EC_ECAPS_BOM_TMP a
        WHERE Region= 'AMER'
        AND y.comp_item=a.comp_item
        AND a.CCN=y.CCN
        union
        SELECT item_type
        FROM E2EC_ECAPS_BOM_TMP a
        WHERE Region= 'AMER'
        AND y.comp_item=a.item
        AND a.CCN=y.CCN
        ) comp_item_type
        FROM
        (
        select Seq,CCN,root_item,root_comp_item ProcessorPart,comp_path,
        substr (t.comp_path, instr (t.comp_path, '/', -1, 2)+ 1,instr (t.comp_path, '/', -1, 1)- instr (t.comp_path, '/', -1, 2)-1) Item,
        SUBSTR(t.comp_path, instr (t.comp_path, '/', 1, x.column_value) + 1,instr (t.comp_path, '/', 1, x.column_value + 1)- instr (t.comp_path, '/', 1, x.column_value) - 1) comp_item,
        SUBSTR(t.ultimate_qty, instr (t.ultimate_qty, '*', 1, x.column_value) + 1,instr (t.ultimate_qty, '*', 1, x.column_value + 1)- instr (t.ultimate_qty, '*', 1, x.column_value) - 1) bom_qty,
        x.column_value lvl
        from ( SELECT t.root_item,t.ccn,t.seq,t.root_comp_item,'*1'||t.ultimate_qty||'*' ultimate_qty,t.comp_path||'/'||t.item||'/' comp_path
               FROM E2EC_ECAPS_MOD_DTL t
               WHERE t.Region = 'AMER') t,
               TABLE(CAST(MULTISET(select LEVEL from dual connect by level <= regexp_count (t.comp_path, '/') - 1)as sys.odcinumberlist)) x
        )y
        );


The tables referred in above query is small tables containing arnd 10k records.The above tables are partitioned on Region and not indexed.

Explain Plan in Prod: COST CARDINALITY BYTES

SELECT STATEMENT, GOAL = ALL_ROWS 165173 61353932 2883634804
SORT UNIQUE 236 3 60
UNION-ALL
PARTITION LIST SINGLE 117 2 40
TABLE ACCESS FULL ADMIN_COSTPL_OWNER E2EC_ECAPS_BOM_TMP 117 2 40
PARTITION LIST SINGLE 117 1 20
TABLE ACCESS FULL ADMIN_COSTPL_OWNER E2EC_ECAPS_BOM_TMP 117 1 20
NESTED LOOPS 165173 61353932 2883634804
PARTITION LIST SINGLE 26 7512 338040
TABLE ACCESS FULL ADMIN_COSTPL_OWNER E2EC_ECAPS_MOD_DTL 26 7512 338040
COLLECTION ITERATOR SUBQUERY FETCH
CONNECT BY WITHOUT FILTERING
FAST DUAL 2 1

Explain Plan in SIT: COST CARDINALITY BYTES

SELECT STATEMENT, GOAL = ALL_ROWS 32 1 1689
SORT UNIQUE 347 2 40
UNION-ALL
PARTITION LIST SINGLE 172 1 20
TABLE ACCESS FULL ADMIN_COSTPL_OWNER E2EC_ECAPS_BOM_TMP 172 1 20
PARTITION LIST SINGLE 172 1 20
TABLE ACCESS FULL ADMIN_COSTPL_OWNER E2EC_ECAPS_BOM_TMP 172 1 20
WINDOW SORT 32 1 1689
NESTED LOOPS 31 1 1689
PARTITION LIST SINGLE 2 1 1687
TABLE ACCESS FULL ADMIN_COSTPL_OWNER E2EC_ECAPS_MOD_DTL 2 1 1687
COLLECTION ITERATOR SUBQUERY FETCH
CONNECT BY WITHOUT FILTERING
FAST DUAL 2 1

I am not able to attribute why there is a huge change in Cost between SIT and Prod.Apparently the Job is going for 3-5 hours which used to get completed within 20mins in SIT.

Kindly help..
Re: Same query with different Explain Plan in two Database [message #563803 is a reply to message #563792] Fri, 17 August 2012 03:47 Go to previous messageGo to next message
knw15pwr
Messages: 131
Registered: March 2010
Senior Member
Is the amount of data same in both environments ?
Also check if the stats are up to date in both the environments.
Re: Same query with different Explain Plan in two Database [message #563812 is a reply to message #563803] Fri, 17 August 2012 05:52 Go to previous messageGo to next message
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
Member
Amount of data is same in both environments.

I analysed both the tables in SIT and now the cost is more than what currently shown in Prod.

Here is the Explain plan in SIT after table analyse.

SELECT STATEMENT, GOAL = ALL_ROWS										23673303	        66464701	        112258879989
 SORT UNIQUE													713			12			3576
  UNION-ALL					
   PARTITION LIST SINGLE											356			6			2088
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_BOM_TMP			                        356	                6	                2088
   PARTITION LIST SINGLE										        356	                6	                1488
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_BOM_TMP			                        356	                6	                1488
 WINDOW SORT													23673303	        66464701	        112258879989
  NESTED LOOPS													221089	                66464701	        112258879989
   PARTITION LIST SINGLE										        68	                8137	                13727119
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_MOD_DTL			                        68	                8137	                13727119
   COLLECTION ITERATOR SUBQUERY FETCH					
    CONNECT BY WITHOUT FILTERING					
     FAST DUAL													2	                 1



It will be of great help if u let me know how to reduce the cost of Nested loops and Window sort.

Re: Same query with different Explain Plan in two Database [message #563826 is a reply to message #563812] Fri, 17 August 2012 06:20 Go to previous messageGo to next message
cookiemonster
Messages: 11089
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use the following method to get the explain plans for both DBs, the ones you've posted so far are unreadable:
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> 
Re: Same query with different Explain Plan in two Database [message #563843 is a reply to message #563826] Fri, 17 August 2012 08:18 Go to previous message
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
Member
Below is the explain plan generated.

Plan hash value: 1350433263

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |    60M|  3126M|       |  1007K  (1)| 03:21:31 |       |       |
|   1 |  SORT UNIQUE                         |                    |     4 |    80 |       |   355   (4)| 00:00:05 |       |       |
|   2 |   UNION-ALL                          |                    |       |       |       |            |          |       |       |
|   3 |    PARTITION LIST SINGLE             |                    |     3 |    60 |       |   346   (2)| 00:00:05 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL                | E2EC_ECAPS_BOM_TMP |     3 |    60 |       |   346   (2)| 00:00:05 |     1 |     1 |
|   5 |    TABLE ACCESS BY GLOBAL INDEX ROWID| E2EC_ECAPS_BOM_TMP |     1 |    20 |       |     7   (0)| 00:00:01 |     1 |     1 |
|*  6 |     INDEX RANGE SCAN                 | TEST               |     4 |       |       |     3   (0)| 00:00:01 |       |       |
|   7 |  WINDOW SORT                         |                    |    60M|  3126M|  3487M|  1007K  (1)| 03:21:31 |       |       |
|   8 |   NESTED LOOPS                       |                    |    60M|  3126M|       |   201K  (1)| 00:40:24 |       |       |
|   9 |    PARTITION LIST SINGLE             |                    |  7433 |   377K|       |    68   (0)| 00:00:01 |   KEY |   KEY |
|  10 |     TABLE ACCESS FULL                | E2EC_ECAPS_MOD_DTL |  7433 |   377K|       |    68   (0)| 00:00:01 |     1 |     1 |
|  11 |    COLLECTION ITERATOR SUBQUERY FETCH|                    |       |       |       |            |          |       |       |
|* 12 |     CONNECT BY WITHOUT FILTERING     |                    |       |       |       |            |          |       |       |
|  13 |      FAST DUAL                       |                    |     1 |       |       |     2   (0)| 00:00:01 |       |       |
Previous Topic: Re: Get Unique row number for list of values (split from unrelated thread by bb)
Next Topic: dbms_xplan.display_cursor
Goto Forum:
  


Current Time: Fri Oct 31 07:19:53 CDT 2014

Total time taken to generate the page: 0.14833 seconds