a question about explain plan [message #387355] |
Wed, 18 February 2009 23:44  |
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 #387373 is a reply to message #387355] |
Thu, 19 February 2009 00:53   |
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 #387382 is a reply to message #387379] |
Thu, 19 February 2009 01:11   |
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 #387391 is a reply to message #387355] |
Thu, 19 February 2009 02:01   |
cookiemonster
Messages: 13963 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.
|
|
|
|