Tables used for sqlid and its stats

SELECT a.object_owner, a.object_name, a.object_type, b.stale_stats FROM v$sql_plan a JOIN dba_tab_statistics b ON a.object_owner = b.owner AND a.object_name = b.TABLE_NAME WHERE b.object_type = 'TABLE' AND a.sql_id = TRIM ('&1') AND (stale_stats IS NULL OR stale_stats = 'YES') UNION ALL SELECT a.object_owner, a.object_name, a.object_type, b.stale_stats FROM v$sql_plan a JOIN dba_tab_statistics b ON a.object_owner = b.owner AND a.object_name = b.PARTITION_NAME WHERE b.object_type = 'PARTITION' AND a.sql_id = TRIM ('&1') AND (stale_stats IS NULL OR stale_stats = 'YES') UNION ALL SELECT a.object_owner, a.object_name, b.object_type, b.stale_stats FROM v$sql_plan a JOIN dba_ind_statistics b ON a.object_owner = b.owner AND a.object_name = b.index_name WHERE b.object_type!='SUBPARTITION' and a.sql_id = TRIM ('&1') AND (stale_stats IS NULL OR stale_stats = 'YES');

find out a SQL how many times its running in a min and avg response time/cpu time/cluster activity/io time

SET SERVEROUTPUT ON
DECLARE a INT := 0; a1 FLOAT := 0; a2 FLOAT := 0; a3 FLOAT := 0; b INT := 0; b1 FLOAT := 0; b2 FLOAT := 0; b3 FLOAT := 0; c INT := 0; d INT := 1; BEGIN SELECT SUM (executions), SUM (elapsed_time) / 1000, SUM (CONCURRENCY_WAIT_TIME) / 1000, SUM (USER_IO_WAIT_TIME) / 1000 INTO a, a1, a2, a3 FROM gv$sql WHERE sql_id = 'gnf46h863mwh3'; DBMS_LOCK.Sleep (60); SELECT SUM (executions), SUM (elapsed_time) / 1000, SUM (CONCURRENCY_WAIT_TIME) / 1000, SUM (USER_IO_WAIT_TIME) / 1000 INTO b, b1, b2, b3 FROM gv$sql WHERE sql_id = 'gnf46h863mwh3'; DBMS_OUTPUT.put_line ( 'gnf46h863mwh3 .. .. count=' || (b - a) || '..........elasped_time_ms=' || (b1 - a1) / (b - a) || '.......conc_time_ms' || (b2 - a2) / (b - a) || '....userwait_time_ms' || (b3 - a3) / (b - a)); end;