| SQL id shows multiple plan table [message #614767] |
Tue, 27 May 2014 00:25  |
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 #614769 is a reply to message #614768] |
Tue, 27 May 2014 01:37  |
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 #614775 is a reply to message #614769] |
Tue, 27 May 2014 02:24 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ashishkumarmahanta80 wrote on Tue, 27 May 2014 12:07I 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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
