| explain plan details vary between display & display_cursor [message #621977] |
Wed, 20 August 2014 04:43  |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
Dear All,
SQL> select * from table(dbms_xplan.display_awr('4m6dzyapcvsv6'));
Output is as below -
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 227 (100)| | | |
| 1 | PARTITION RANGE ALL| | 920 | 35880 | 227 (1)| 00:00:03 | 1 | 6 |
| 2 | PARTITION HASH ALL| | 920 | 35880 | 227 (1)| 00:00:03 | 1 | 30 |
| 3 | TABLE ACCESS FULL| GEN_PROP_INFORMATION_TAB | 920 | 35880 | 227 (1)| 00:00:03 | 1 | 180 |
----------------------------------------------------------------------------------------------------------------
If i do select * from table(dbms_xplan.display);
it shows query using index. How ever the above one is executed with Full table scan.
SQL> explain plan for
2 SELECT A0.TXT_OFFICE_NAME,
3 A0.DAT_POLICY_EFF_FROMDATE,
4 A0.DAT_POLICY_EFF_TODATE,
5 A0.TXT_POLICY_NO_CHAR
6 FROM GEN_PROP_INFORMATION_TAB A0
7 WHERE A0.NUM_POLCIY_NO = :B1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1477075031
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 3 (0)| 00:00:01 | | |
| 1 | FOR UPDATE | | | | | | | |
| 2 | BUFFER SORT | | | | | | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| GEN_PROP_INFORMATION_TAB | 1 | 86 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN | PK_GEN_PROP_INFO | 1 | | 2 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("X1"."NUM_REFERENCE_NUMBER"=TO_NUMBER(:B2))
filter(TRUNC(INTERNAL_FUNCTION("X1"."DAT_REFERENCE_DATE"))=TO_DATE(TO_CHAR(TO_NUMBER(:B1),'dd/mm/yyyy'),'dd/mm/yyy
y'))
18 rows selected.
Please assist me why it is happening?
Regards,
Ishika
CM: removed extraneous white space
[Updated on: Wed, 20 August 2014 04:56] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: explain plan details vary between display & display_cursor [message #621982 is a reply to message #621979] |
Wed, 20 August 2014 05:10  |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
Dear Lalit,
For tuning of sql statement, we generally use DISPLAY for generating explain plan for the removal of FTS or any other activities to tune the query? Now, help me out to tune the query as both of them is showing different output?
Note:-Column of where clause is having index, also gather_stats are up to date.
Regards,
Ishika
|
|
|
|
| Re: explain plan details vary between display & display_cursor [message #621984 is a reply to message #621982] |
Wed, 20 August 2014 05:16 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
ishika_20 wrote on Wed, 20 August 2014 11:10Dear Lalit,
For tuning of sql statement, we generally use DISPLAY for generating explain plan for the removal of FTS or any other activities to tune the query? Now, help me out to tune the query as both of them is showing different output?
Note:-Column of where clause is having index, also gather_stats are up to date.
Regards,
Ishika EXPLAIN PLAN does not do "bind variable peeking", so it may have an incorrect cardinality estimate which pushes it to a different plan. There are also factors such as "cardinality feedback", "adaptive cursor sharing", and "adaptive execution plans" which may also give this same result. I've "double-quoted" the key words you need to research.
|
|
|
|
|
|
| Re: explain plan details vary between display & display_cursor [message #621993 is a reply to message #621982] |
Wed, 20 August 2014 05:54 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ishika_20 wrote on Wed, 20 August 2014 15:40Now, help me out to tune the query
The query executed in less than a second, that's what at least the execution plan shows. so I don't see the need to tune the query. How much time the query exactly takes to execute, and how many rows does it return? You also said that the stats are up to date, so cardinality estimate shouldn't be way off than reality.
|
|
|
|
| Re: explain plan details vary between display & display_cursor [message #621999 is a reply to message #621993] |
Wed, 20 August 2014 06:15 |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
Dear Lalit,
As suggested by John, bind variable can be an exception between DISPLAY & DISPLAY_CURSOR. If bind variables would not there then it would fetch same output, rite? In DISPALY_CURSOR shows 990 rows, but in DISPLAY explain plan shows only 1 rows.
Actually, it should fetch only one record.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
