Home » RDBMS Server » Performance Tuning » SQL id shows multiple plan table (11g, 11.2.0.3, sun-solaris10)
SQL id shows multiple plan table [message #614767] Tue, 27 May 2014 00:25 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

We have tuned the query and when we generate plan for the query, it shows correct & updated plan.
When we generated plan as per sql id, its generate multiple plan, previous and current one.

Below are the plan -

SQL> select * from table(dbms_xplan.display_awr('6wynt7zdd5sg1'));

PLAN_TABLE_OUTPUT
--------------------------
SQL_ID 6wynt7zdd5sg1
--------------------
SELECT NVL(SUM(DECODE(W.TXT_DR_CR, 'DR', W.NUM_AMOUNT, (-1) *
W.NUM_AMOUNT)), 0) FROM ACC_GENERAL_LEDGER_TMP W WHERE W.NUM_OFFICE_CD
= :B3 AND TXT_LEDGER_ACCOUNT_CD = :B2 AND W.TXT_DIMENSION_5_VALUE_CD = 
:B1 

Plan hash value: 683203280

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                        |       |       |  6603 (100)|          |
|   1 |  SORT AGGREGATE    |                        |     1 |    30 |            |          |
|   2 |   TABLE ACCESS FULL| ACC_GENERAL_LEDGER_TMP |    39 |  1170 |  6603   (2)| 00:01:20 |
---------------------------------------------------------------------------------------------

SQL_ID 6wynt7zdd5sg1 
--------------------
SELECT NVL(SUM(DECODE(W.TXT_DR_CR, 'DR', W.NUM_AMOUNT, (-1) *
W.NUM_AMOUNT)), 0) FROM ACC_GENERAL_LEDGER_TMP W WHERE W.NUM_OFFICE_CD 
= :B3 AND TXT_LEDGER_ACCOUNT_CD = :B2 AND W.TXT_DIMENSION_5_VALUE_CD = 
:B1 

Plan hash value: 3640920916 

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |       |       |  1794 (100)|          |
|   1 |  SORT AGGREGATE              |                            |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACC_GENERAL_LEDGER_TMP     |   200 |  6000 |  1794   (1)| 00:00:22 |
|   3 |    INDEX RANGE SCAN          | NU1_ACC_GENERAL_LEDGER_TMP |  8845 |       |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------- 


Need your valuable suggestion on the same...

Regards,
Ashish Kumar Mahanta

[Updated on: Tue, 27 May 2014 01:57] by Moderator

Report message to a moderator

Re: SQL id shows multiple plan table [message #614768 is a reply to message #614767] Tue, 27 May 2014 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the question?

Re: SQL id shows multiple plan table [message #614769 is a reply to message #614768] Tue, 27 May 2014 01:37 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

I want to know why two plan is appearing for the same SQL ID? We have already created indexes on the columns. Please clarify me what is the logic behind it?

SQL id should show only updated plan -

SQL_ID 6wynt7zdd5sg1
--------------------
SELECT NVL(SUM(DECODE(W.TXT_DR_CR, 'DR', W.NUM_AMOUNT, (-1) *
W.NUM_AMOUNT)), 0) FROM ACC_GENERAL_LEDGER_TMP W WHERE W.NUM_OFFICE_CD
= :B3 AND TXT_LEDGER_ACCOUNT_CD = :B2 AND W.TXT_DIMENSION_5_VALUE_CD = 
:B1

Plan hash value: 3640920916

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |       |       |  1794 (100)|          |
|   1 |  SORT AGGREGATE              |                            |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACC_GENERAL_LEDGER_TMP     |   200 |  6000 |  1794   (1)| 00:00:22 |
|   3 |    INDEX RANGE SCAN          | NU1_ACC_GENERAL_LEDGER_TMP |  8845 |       |    29   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------


Regards,

Ashish

[Updated on: Tue, 27 May 2014 01:56] by Moderator

Report message to a moderator

Re: SQL id shows multiple plan table [message #614771 is a reply to message #614769] Tue, 27 May 2014 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Different bind variables values or types or length could lead to different plans.

Please use "set trimout on" on your SQL*Plus session to avoid very very very long lines.

Re: SQL id shows multiple plan table [message #614775 is a reply to message #614769] Tue, 27 May 2014 02:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ashishkumarmahanta80 wrote on Tue, 27 May 2014 12:07
I want to know why two plan is appearing for the same SQL ID?


The most common reason is bind peeking. But, to be sure, we need to see the full SQL text. Also, to check which plan is latest used for the execution, you can query dba_hist_sqlstat and dba_hist_snapshot for the sql_id and look for the plan_hash_value based on begin_interval_time.
Re: SQL id shows multiple plan table [message #614781 is a reply to message #614775] Tue, 27 May 2014 02:47 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Probably the old plan is still in the v$ views after you made the index.
Previous Topic: monitoring
Next Topic: SQL performance tunning
Goto Forum:
  


Current Time: Thu Mar 28 19:48:41 CDT 2024