alter session set statistics_level = allcreate table t as
select rownum x from dual connect by level <= 1000select /* MY QUERY */count(*) from t| COUNT(*) | 1000 |
|---|
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'ALLSTATS LAST'
)
) p
where s.sql_text like '%MY QUERY%'
and s.sql_text not like '%not this%'| PLAN_TABLE_OUTPUT | SQL_ID 8k3njz4u929m4, child number 0 | ------------------------------------- | select /* MY QUERY */count(*) from t | Plan hash value: 2966233522 | ------------------------------------------------------------------------------------------------------ | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | | ------------------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 2 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 2 | | | 2 | TABLE ACCESS STORAGE FULL| T | 1 | 1000 | 1000 |00:00:00.01 | 3 | 2 | | ------------------------------------------------------------------------------------------------------ |
|---|
select min ( x ) from t| MIN(X) | 1 |
|---|
Get Last Execution with :LIVESQL_LAST_SQL_ID
select *
from dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'ALLSTATS LAST')| PLAN_TABLE_OUTPUT | SQL_ID 6fg4jt8xtq0t1, child number 0 | ------------------------------------- | select min ( x ) from t | Plan hash value: 2966233522 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | 2 | TABLE ACCESS STORAGE FULL| T | 1 | 1000 | 1000 |00:00:00.01 | 3 | | --------------------------------------------------------------------------------------------- |
|---|