查看执行计划是否过期EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT OWNER, TABLE_NAME, OBJECT_TYPE, STALE_STATS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL)
AND OWNER NOT IN ('SYS',
'SYSTEM',
'SYSMAN',
'DMSYS',
'OLAPSYS',
'XDB',
'EXFSYS',
'CTXSYS',
'WMSYS',
'DBSNMP',
'ORDSYS',
'OUTLN',
'TSMSYS',
'MDSYS')
AND TABLE_NAME NOT LIKE 'BIN%';
查看对象占用空间
select partition_name, sum(bytes) / 1024 / 1024 / 1024
from dba_segments
where segment_name = ''
group by partition_name;
查看执行计划是否变化:
select to_char(TIMESTAMP, 'yyyymmddhh24') time, sql_id, PLAN_HASH_VALUE
from DBA_HIST_SQL_PLAN
where TIMESTAMP > sysdate - 7
and sql_id = 'xxxx'
group by to_char(TIMESTAMP, 'yyyymmddhh24'), sql_id, PLAN_HASH_VALUE
order by 1 desc;
统计执行次数
select to_char(END_INTERVAL_TIME, 'yyyymmddhh24') time,
sql_id,
plan_hash_value,
t.DISK_READS_DELTA,
DISK_READS_TOTAL,
EXECUTIONS_DELTA,
EXECUTIONS_TOTAL
from DBA_HIST_SQLSTAT t, dba_hist_snapshot d
where t.SNAP_ID = d.SNAP_ID
and t.dbid = t.dbid
and t.INSTANCE_NUMBER = d.INSTANCE_NUMBER
and t.sql_id = 'xxx'
and END_INTERVAL_TIME < sysdate
and END_INTERVAL_TIME > sysdate - 3
order by 1 desc;
查看历史执行计划:
SELECT * FROM table (dbms_xplan.display_awr('xxxx','xxxx'));