Home » RDBMS Server » Performance Tuning » explain plan details vary between display & display_cursor (11g, 11.2.0.3, solaris10)
explain plan details vary between display & display_cursor [message #621977] Wed, 20 August 2014 04:43 Go to next message
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 #621979 is a reply to message #621977] Wed, 20 August 2014 04:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
DISPLAY function is used to display the execution plan stored in the plan table, however, DISPLAY_CURSOR displays the actual execution plan which was used to execute the query.
Re: explain plan details vary between display & display_cursor [message #621982 is a reply to message #621979] Wed, 20 August 2014 05:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
ishika_20 wrote on Wed, 20 August 2014 11:10
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
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 #621991 is a reply to message #621984] Wed, 20 August 2014 05:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
John Watson wrote on Wed, 20 August 2014 15:46
"adaptive execution plans"


For this topic, this could be ruled out as OP is not on 12c. But it is good to have the knowledge, always.
Re: explain plan details vary between display & display_cursor [message #621993 is a reply to message #621982] Wed, 20 August 2014 05:54 Go to previous messageGo to next message
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:40
Now, 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 Go to previous message
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.
Previous Topic: Regarding sql id
Next Topic: sql query performance guide
Goto Forum:
  


Current Time: Thu Mar 28 08:19:52 CDT 2024