1.正常查询
SQL> select count(*) from system.wh where num1=100;
COUNT(*)
----------
101
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7uc0kqyghskzs, child number 0
-------------------------------------
select count(*) from system.wh where num1=100
Plan hash value: 1206455612
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| WH | 101 |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NUM1"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
2.使用 gather_plan_statistics 查询
SQL> select /*+ gather_plan_statistics */ count(*) from system.wh where num1=100;
COUNT(*)
----------
101
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g9rdy6074r3uw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from system.wh where
num1=100
Plan hash value: 1206455612
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ers |
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
23 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
23 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| WH | 1 | 101 | 101 |00:00:00.01 |
23 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NUM1"=100)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
24 rows selected.
区别很明显
SQL> select count(*) from system.wh where num1=100;
COUNT(*)
----------
101
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7uc0kqyghskzs, child number 0
-------------------------------------
select count(*) from system.wh where num1=100
Plan hash value: 1206455612
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| WH | 101 |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NUM1"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
2.使用 gather_plan_statistics 查询
SQL> select /*+ gather_plan_statistics */ count(*) from system.wh where num1=100;
COUNT(*)
----------
101
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g9rdy6074r3uw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from system.wh where
num1=100
Plan hash value: 1206455612
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ers |
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
23 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
23 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| WH | 1 | 101 | 101 |00:00:00.01 |
23 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NUM1"=100)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
24 rows selected.
区别很明显
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7569309/viewspace-2134168/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7569309/viewspace-2134168/