Home » SQL & PL/SQL » SQL & PL/SQL » a question about explain plan
a question about explain plan [message #387355] Wed, 18 February 2009 23:44 Go to next message
yqhwd
Messages: 4
Registered: February 2009
Junior Member
here is my sql statement and running result:
SQL> @bind_variables_peeking_test.sql
SQL> SET ECHO OFF
SQL> 
SQL> REM
SQL> REM Setup test environment
SQL> REM
SQL> 
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> 
SQL> DROP TABLE t;
DROP TABLE t
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> 
SQL> CREATE TABLE t
  2  AS
  3  SELECT rownum AS id
  4  FROM dual
  5  CONNECT BY level <= 1000;
SQL> 
SQL> execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t');
SQL> SELECT count(id), count(DISTINCT id), min(id), max(id) FROM t;

 COUNT(ID) COUNT(DISTINCTID)    MIN(ID)    MAX(ID)                                                  
---------- ----------------- ---------- ----------                                                  
      1000              1000          1       1000                                                  
SQL> 
SQL> PAUSE

SQL> 
SQL> REM
SQL> REM Without bind variables different execution plans are used if the value
SQL> REM used in the WHERE clause change. This is because the query optimizer
SQL> REM recognize the different selectivity of the two predicates.
SQL> REM
SQL> 
SQL> SELECT count(*) FROM t WHERE id < 990;

  COUNT(*)                                                                                          
----------                                                                                          
       989                                                                                          
SQL> 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'typical'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  bspfffaycy92u, child number 0                                                               
-------------------------------------                                                               
SELECT count(*) FROM t WHERE id < 990                                                               
                                                                                                    
Plan hash value: 2966233522                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                         
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |                         
|*  2 |   TABLE ACCESS FULL| T    |   990 |  2970 |     3   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter("ID"<990)                                                                             
                                                                                                    
SQL> 
SQL> PAUSE

SQL> 
SQL> SELECT count(*) FROM t WHERE id < 10;

  COUNT(*)                                                                                          
----------                                                                                          
         9                                                                                          
SQL> 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'typical'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  4xfxt0frkj40y, child number 0                                                               
-------------------------------------                                                               
SELECT count(*) FROM t WHERE id < 10                                                                
                                                                                                    
Plan hash value: 2966233522                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                         
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |                         
|*  2 |   TABLE ACCESS FULL| T    |     9 |    27 |     3   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter("ID"<10)                                                                              
                                                                                                    
SQL> 
SQL> PAUSE

SQL> 
SQL> REM
SQL> REM With bind variables the same execution plan is used. Depending on the
SQL> REM peeked value (10 or 990), a full table scan or an index range scan is used.
SQL> REM
SQL> 
SQL> EXECUTE :id := 10;
SQL> 
SQL> SELECT count(*) FROM t WHERE id < :id;

  COUNT(*)                                                                                          
----------                                                                                          
         9                                                                                          
SQL> 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'typical'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  dkva59ypaxa6w, child number 0                                                               
-------------------------------------                                                               
SELECT count(*) FROM t WHERE id < :id                                                               
                                                                                                    
Plan hash value: 2966233522                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                         
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |                         
|*  2 |   TABLE ACCESS FULL| T    |     9 |    27 |     3   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter("ID"<:ID)                                                                             
                                                                                                    
SQL> 
SQL> PAUSE

SQL> 
SQL> EXECUTE :id := 10;
SQL> 
SQL> SELECT count(*) FROM t WHERE id < :id;

  COUNT(*)                                                                                          
----------                                                                                          
         9                                                                                          
SQL> 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'typical'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  dkva59ypaxa6w, child number 0                                                               
-------------------------------------                                                               
SELECT count(*) FROM t WHERE id < :id                                                               
                                                                                                    
Plan hash value: 2966233522                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                         
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |                         
|*  2 |   TABLE ACCESS FULL| T    |     9 |    27 |     3   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter("ID"<:ID)                                                                             
                                                                                                    
SQL> 
SQL> PAUSE

SQL> 
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> 
SQL> PAUSE

SQL> 
SQL> EXECUTE :id := 10;
SQL> SELECT count(*) FROM t WHERE id < :id;

  COUNT(*)                                                                                          
----------                                                                                          
         9                                                                                          
SQL> explain plan for SELECT count(*) FROM t WHERE id < :id;
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
Plan hash value: 2966233522                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT   |      |     1 |     3 |     3   (0)| 00:00:01 |                         
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |                         
|*  2 |   TABLE ACCESS FULL| T    |    50 |   150 |     3   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter("ID"<TO_NUMBER(:ID))                                                                  
SQL> 
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> PAUSE

SQL> 
SQL> EXECUTE :id := 10;
SQL> 
SQL> SELECT count(*) FROM t WHERE id < :id;

  COUNT(*)                                                                                          
----------                                                                                          
         9                                                                                          
SQL> 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'typical'));

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
SQL_ID  dkva59ypaxa6w, child number 0                                                               
-------------------------------------                                                               
SELECT count(*) FROM t WHERE id < :id                                                               
                                                                                                    
Plan hash value: 2966233522                                                                         
                                                                                                    
---------------------------------------------------------------------------                         
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                         
---------------------------------------------------------------------------                         
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                         
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |                         
|*  2 |   TABLE ACCESS FULL| T    |     9 |    27 |     3   (0)| 00:00:01 |                         
---------------------------------------------------------------------------                         
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter("ID"<:ID)                                                                             
                                                                                                    
SQL> 
SQL> 
SQL> PAUSE

SQL> 
SQL> REM
SQL> REM Cleanup
SQL> REM
SQL> 
SQL> UNDEFINE sql_id
SQL> 
SQL> DROP TABLE t;
SQL> PURGE TABLE t;
SQL> 
SQL> spool off;


please noticed the last two explain plan result,one sql statement but have two result,why?

[Mod-Edit: Frank added code-tags to improve readability]

[Updated on: Thu, 19 February 2009 00:39] by Moderator

Report message to a moderator

Re: a question about explain plan [message #387369 is a reply to message #387355] Thu, 19 February 2009 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the question?
Where are there different execution plans?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: a question about explain plan [message #387373 is a reply to message #387355] Thu, 19 February 2009 00:53 Go to previous messageGo to next message
yqhwd
Messages: 4
Registered: February 2009
Junior Member
this is one result:
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T | 50 | 150 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

this is another result:
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T | 9 | 27 | 3 (0)| 00:00:01 |

the rows and bytes column result not same.
Re: a question about explain plan [message #387379 is a reply to message #387373] Thu, 19 February 2009 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From which test?

Michel Cadot wrote on Thu, 19 February 2009 07:46
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Re: a question about explain plan [message #387382 is a reply to message #387379] Thu, 19 February 2009 01:11 Go to previous messageGo to next message
yqhwd
Messages: 4
Registered: February 2009
Junior Member
if i run this statement
explain plan for SELECT count(*) FROM t WHERE id < :id;
select * from table(dbms_xplan.display);
the result of rows and bytes is 50 and 150.

if run this statement
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'typical'));
the result of rows and bytes is 9 and 27.

both of the two sql statement is
SELECT count(*) FROM t WHERE id < :id;
and the value of the variable id is 10.
BTW,can you see the running step and result?
Re: a question about explain plan [message #387383 is a reply to message #387382] Thu, 19 February 2009 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again read forum guide. Don't use IM speak.

Regards
Michel
Re: a question about explain plan [message #387384 is a reply to message #387382] Thu, 19 February 2009 01:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You must have left something out of your example, since there is no "explain plan for" statement for the last plan.

Ah, skip that. Misread the xplan query

[Updated on: Thu, 19 February 2009 01:21]

Report message to a moderator

Re: a question about explain plan [message #387388 is a reply to message #387384] Thu, 19 February 2009 01:45 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I never used DBMS_XPLAN package before, but what's the difference between these two actions:

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


and

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'typical'));


[Updated on: Thu, 19 February 2009 01:46]

Report message to a moderator

Re: a question about explain plan [message #387390 is a reply to message #387388] Thu, 19 February 2009 01:48 Go to previous messageGo to next message
yqhwd
Messages: 4
Registered: February 2009
Junior Member
yes,i think so too,but i don't know what different between display and display_cursor.
Re: a question about explain plan [message #387391 is a reply to message #387355] Thu, 19 February 2009 02:01 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well according to the documentation:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm#ARPLS378

dbms_xplan.display shows the EXPLAIN plan for a SQL statement
dbms_xplan.display_cursor shows the EXECUTION plan for a SQL statement.

Explain is what oracle thinks it will do to execute the query - rows and bytes are estimates.

Execution plan is what oracle actually did to execute the query - so rows and bytes should be accurate.

And looking at your results the explain plan gives rows of 50 for a query that'll return 9 rows and the execution plan gives an accurrate row count of 9.

It was news to me that you could do this - could prove very handy.
Re: a question about explain plan [message #387394 is a reply to message #387355] Thu, 19 February 2009 02:04 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Didn't had (or didn't make) the time to go look at the documentation.

But great management summary from cookiemonster Wink
Previous Topic: Create tab delimited report for excel
Next Topic: Dynamic sql
Goto Forum:
  


Current Time: Tue Dec 06 00:26:39 CST 2016

Total time taken to generate the page: 0.14258 seconds