In the previous 3 blog posts, I have shown how you can use the GATHER_PLAN_STATISTICS Hint or the STATISTICS_LEVEL='ALL' to get sql execution statistics.
This may work with Adaptive Plans as well.
SQL> select /*+ gather_plan_statistics */ count(*)
2 from tables_list t
3 where (owner, table_name)
4 not in
5 (
6 select owner, table_name
7 from indexes_list i
8 )
9 /
COUNT(*)
----------
593
SQL> select * from table(dbms_xplan.display_cursor('',format=>'ALLSTATS LAST +COST +ADAPTIVE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g6q8m94krdvz7, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from tables_list t where
(owner, table_name) not in ( select owner, table_name from
indexes_list i )
Plan hash value: 2460844421
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 (100)| 1 |00:00:00.01 | 37 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 37 | | | |
| * 2 | HASH JOIN ANTI | | 1 | 22 | 11 (0)| 593 |00:00:00.01 | 37 | 1335K| 1335K| 1376K (0)|
|- 3 | NESTED LOOPS ANTI | | 1 | 22 | 11 (0)| 2202 |00:00:00.01 | 17 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | | 2202 |00:00:00.01 | 17 | | | |
| 5 | INDEX FAST FULL SCAN| TABLES_LIST_NDX | 1 | 2202 | 5 (0)| 2202 |00:00:00.01 | 17 | | | |
|- * 6 | INDEX RANGE SCAN | INDEXES_LIST_NDX_1 | 0 | 2918 | 6 (0)| 0 |00:00:00.01 | 0 | | | |
| 7 | INDEX FAST FULL SCAN | INDEXES_LIST_NDX_1 | 1 | 2918 | 6 (0)| 2918 |00:00:00.01 | 20 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"="OWNER" AND "TABLE_NAME"="TABLE_NAME")
6 - access("OWNER"="OWNER" AND "TABLE_NAME"="TABLE_NAME")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
31 rows selected.
SQL>
SQL> alter session set statistics_level='ALL';
Session altered.
SQL> select count(*)
2 from tables_list t
3 where (owner, table_name)
4 not in
5 (
6 select owner, table_name
7 from indexes_list i
8 )
9 /
COUNT(*)
----------
593
SQL> select * from table(dbms_xplan.display_cursor('',format=>'ALLSTATS LAST +COST +ADAPTIVE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c1fdcdxhvjrat, child number 0
-------------------------------------
select count(*) from tables_list t where (owner, table_name) not in (
select owner, table_name from indexes_list i )
Plan hash value: 2460844421
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 (100)| 1 |00:00:00.01 | 37 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 37 | | | |
| * 2 | HASH JOIN ANTI | | 1 | 22 | 11 (0)| 593 |00:00:00.01 | 37 | 1335K| 1335K| 1381K (0)|
|- 3 | NESTED LOOPS ANTI | | 1 | 22 | 11 (0)| 2202 |00:00:00.01 | 17 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | | 2202 |00:00:00.01 | 17 | | | |
| 5 | INDEX FAST FULL SCAN| TABLES_LIST_NDX | 1 | 2202 | 5 (0)| 2202 |00:00:00.01 | 17 | | | |
|- * 6 | INDEX RANGE SCAN | INDEXES_LIST_NDX_1 | 0 | 2918 | 6 (0)| 0 |00:00:00.01 | 0 | | | |
| 7 | INDEX FAST FULL SCAN | INDEXES_LIST_NDX_1 | 1 | 2918 | 6 (0)| 2918 |00:00:00.01 | 20 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"="OWNER" AND "TABLE_NAME"="TABLE_NAME")
6 - access("OWNER"="OWNER" AND "TABLE_NAME"="TABLE_NAME")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
30 rows selected.
SQL>
ote : The two SQL statements, although being the same, have different SQL_IDs because of the presence / absence of the GATHER_PLAN_STATISTICS Hint.
No comments:
Post a Comment