Month: August 2017

Missing Optimizer Statistics

by

ELECT ‘TABLE’ object_type, owner, table_name object_name, last_analyzed, stattype_locked, stale_stats FROM all_tab_statistics WHERE (last_analyzed IS NULL OR stale_stats = ‘YES’) AND stattype_locked IS NULL AND owner NOT IN (‘ANONYMOUS’, ‘CTXSYS’, ‘DBSNMP’, ‘EXFSYS’, ‘LBACSYS’, ‘MDSYS’, ‘MGMT_VIEW’, ‘OLAPSYS’, ‘OWBSYS’, ‘ORDPLUGINS’, ‘ORDSYS’, ‘OUTLN’, ‘SI_INFORMTN_SCHEMA’, ‘SYS’, ‘SYSMAN’, ‘SYSTEM’, ‘TSMSYS’, ‘WK_TEST’, ‘WKSYS’, ‘WKPROXY’, ‘WMSYS’, ‘XDB’) AND owner NOT LIKE ‘FLOW%’ UNION … Read more

Disk IO_WAIT

by

SELECT target_name, round(avg(average),2) FROM mgmt$metric_daily WHERE column_label = ‘Total Disk I/O made across all disks (per second)’ AND rollup_timestamp >= sysdate-14 AND rollup_timestamp <= sysdate group by target_name order by 2 desc; Up to 2000 is low (normal) Between 2000-5000 is busy over 5000 is high over 10000 is extreme

What Objects are in Use

by

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME, S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

Active Session History Longest SQL

by

SELECT STAT.INSTANCE_NUMBER, STAT.SQL_ID, SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME_DELTA/1000000 SECONDS, round((ELAPSED_TIME_DELTA/1000000)/60,2) MINUTES, (ELAPSED_TIME_DELTA/1000000) / EXECUTIONS_DELTA, round((ELAPSED_TIME_DELTA/1000000)/60 / EXECUTIONS_DELTA,2) “TIME PER EXEC”, STAT.SNAP_ID, SS.END_INTERVAL_TIME, EXECUTIONS_DELTA FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND STAT.SNAP_ID = SS.SNAP_ID AND PARSING_SCHEMA_NAME = ‘SCOTT’ AND SS.BEGIN_INTERVAL_TIME … Read more

How Many Inserts In A Day

by

SELECT STAT.SQL_ID, SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME_DELTA / 1000000, STAT.SNAP_ID, SS.END_INTERVAL_TIME FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND STAT.SNAP_ID = SS.SNAP_ID –AND STAT.DBID = ? — AND STAT.INSTANCE_NUMBER in (1,2,3) AND SS.BEGIN_INTERVAL_TIME >= SYSDATE – 7 AND lower (STAT.SQL_ID) … Read more

Tables With No foreign Keys

by

SELECT table_name, constraint_name, cname1 || NVL2 (cname2, ‘,’ || cname2, NULL) || NVL2 (cname3, ‘,’ || cname3, NULL) || NVL2 (cname4, ‘,’ || cname4, NULL) || NVL2 (cname5, ‘,’ || cname5, NULL) || NVL2 (cname6, ‘,’ || cname6, NULL) || NVL2 (cname7, ‘,’ || cname7, NULL) || NVL2 (cname8, ‘,’ || cname8, NULL) columns FROM … Read more