Creditos: http://www.undomain.es/node/167

SELECT ‘Instancia y Host’ "DESCRIPCION", INSTANCE_NAME||’ (‘||HOST_NAME||’)’ "VALOR", NULL "COMENTARIO" FROM V$INSTANCE

UNION ALL

SELECT NAME, TO_CHAR(TO_NUMBER(VALUE)/1024/1024)||’ MB’, NULL FROM V$PARAMETER WHERE NAME =’shared_pool_size’

UNION ALL

SELECT NAME, TO_CHAR(ROUND(BYTES/1024/1024,2))||’ MB’, NULL FROM V$SGASTAT WHERE POOL = ‘shared pool’ AND NAME = ‘free memory’

UNION ALL

SELECT ‘Miss Ratio’, TO_CHAR(ROUND(SUM(GETMISSES)/SUM(GETS),2))||’%’ "MISS RATIO", DECODE(SIGN(ROUND(SUM(GETMISSES)/SUM(GETS),0)-15), -1,NULL,’Ampliar SHARED_POOL_SIZE’) FROM V$ROWCACHE

UNION ALL

SELECT ‘Reparsed querys’, TO_CHAR(COUNT(*)), DECODE(SIGN(COUNT(*)-1),-1,NULL,’Ampliar SHARED_POOL_SIZE’) FROM V$SQLAREA A, V$SQLTEXT T WHERE PARSE_CALLS >1 AND PARSE_CALLS = EXECUTIONS AND A.ADDRESS=T.ADDRESS AND EXECUTIONS > 10000

UNION ALL

SELECT ‘Hit Ratio’, TO_CHAR(ROUND(SUM(PINHITS) / SUM(PINS) * 100 ,2))||’%’, DECODE(SIGN(ROUND(SUM(PINHITS) / SUM(PINS) * 100 ,0)-85),-1,’Ampliar SHARED_POOL_SIZE y/o el OPEN_CURSORS’,NULL) FROM V$LIBRARYCACHE WHERE NAMESPACE IN (‘SQL AREA’, ‘TABLE/PROCEDURE’, ‘BODY’, ‘TRIGGER’)

UNION ALL

SELECT ‘Reload percent’, TO_CHAR(ROUND(SUM(RELOADS) / SUM(PINS) * 100 ,2))||’%’, DECODE(SIGN(ROUND(SUM(RELOADS) / SUM(PINS) * 100 ,0)-2),-1,NULL,’Ampliar SHARED_POOL_SIZE y/o el OPEN_CURSORS’) FROM V$LIBRARYCACHE WHERE NAMESPACE IN (‘SQL AREA’, ‘TABLE/PROCEDURE’, ‘BODY’, ‘TRIGGER’)

UNION ALL

SELECT ‘Cache Hit Ratio’, TO_CHAR(ROUND(100*(1 – (V3.VALUE / (V1.VALUE + V2.VALUE))),2))||’%’, DECODE(SIGN(ROUND(100*(1 – (V3.VALUE / (V1.VALUE + V2.VALUE))),0)-85),-1,’Ampliar DB_CACHE_SIZE’,NULL) FROM V$SYSSTAT V1, V$SYSSTAT V2, V$SYSSTAT V3 WHERE V1.NAME = ‘db block gets’ AND V2.NAME = ‘consistent gets’ AND V3.NAME = ‘physical reads’

UNION ALL

SELECT NAME, TO_CHAR(VALUE), DECODE(NAME,’sorts (disk)’,decode(sign((value*100/(select sum(value) from V$SYSSTAT WHERE NAME IN (‘sorts (memory)’, ‘sorts (disk)’)))-25),-1,NULL,’Ampliar la SORT_AREA_SIZE’),NULL) FROM V$SYSSTAT WHERE NAME IN (‘sorts (memory)’, ‘sorts (disk)’)

UNION ALL

SELECT NAME, TO_CHAR(VALUE), DECODE(NAME,’redo log space wait time’,DECODE(SIGN(VALUE-1),-1,NULL,’Ampliar el LOG_BUFFER’),NULL) FROM V$SYSSTAT WHERE NAME IN(‘redo log space requests’, ‘redo log space wait time’)

UNION ALL

SELECT ‘Esperas de segmentos de Rollback’ , TO_CHAR(ROUND(SUM(WAITS)/SUM(GETS)*100,2))||’%’, DECODE(SIGN(ROUND(SUM(WAITS)/SUM(GETS)*100,0)-1),-1,NULL,’Crear mas segmentos de rollback’) FROM V$ROLLSTAT

UNION ALL

SELECT ‘Tablas fragmentadas (>100 Extents) -> ‘||OWNER , TO_CHAR(COUNT(*)), DECODE(SIGN(COUNT(*)-1),-1,NULL,’Realizar tareas de desfragmentacion sobre la tablas. Para identificarlas: "SELECT OWNER, SEGMENT_NAME TABLE_NAME, TABLESPACE_NAME, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_TYPE=”TABLE” AND OWNER=”’||OWNER||”’ AND EXTENTS>25 ORDER BY EXTENTS DESC;"’) FROM DBA_SEGMENTS WHERE SEGMENT_TYPE=’TABLE’ AND OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘OLAPSYS’, ‘WKSYS’, ‘CTXSYS’, ‘RMAN’, ‘ODM’, ‘WMSYS’, ‘MDSYS’, ‘QS’, ‘QS_ES’, ‘QS_OS’, ‘QS_WS’, ‘ODM_MTR’, ‘QS_CBADM’, ‘QS_CS’, ‘ORDSYS’, ‘XDB’, ‘SCOTT’, ‘OUTLN’, ‘OE’) AND EXTENTS>100 GROUP BY OWNER

UNION ALL

SELECT ‘Indices fragmentados (>100 Extents) -> ‘||OWNER, TO_CHAR(COUNT(*)), DECODE(SIGN(COUNT(*)-1),-1,NULL,’Recrear los indices fragmentados. Para identificarlos: "SELECT A.OWNER, B.TABLE_NAME, A.SEGMENT_NAME INDICE, A.TABLESPACE_NAME, A.EXTENTS FROM DBA_SEGMENTS A, ALL_INDEXES B WHERE A.SEGMENT_TYPE=”INDEX” AND B.OWNER=A.OWNER AND B.INDEX_NAME=A.SEGMENT_NAME AND EXTENTS>25 AND A.OWNER=”’||OWNER||”’ ORDER BY EXTENTS DESC;"’) FROM DBA_SEGMENTS WHERE SEGMENT_TYPE=’INDEX’ AND OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘OLAPSYS’, ‘WKSYS’, ‘CTXSYS’, ‘RMAN’, ‘ODM’, ‘WMSYS’, ‘MDSYS’, ‘QS’, ‘QS_ES’, ‘QS_OS’, ‘QS_WS’, ‘ODM_MTR’, ‘QS_CBADM’, ‘QS_CS’, ‘ORDSYS’, ‘XDB’, ‘SCOTT’, ‘OUTLN’, ‘OE’) AND EXTENTS>100 GROUP BY OWNER

UNION ALL

SELECT ‘Tablas con estadisticas antiguas -> ‘||OWNER, TO_CHAR(COUNT(*)), DECODE(COUNT(*),0,NULL,’Actualiza las estadisticas con "SELECT ”ANALYZE TABLE ”||OWNER||”.”||TABLE_NAME||” COMPUTE STATISTICS;” FROM DBA_TABLES WHERE OWNER=”’||OWNER||”’ AND LAST_ANALYZED IS NULL;"’) FROM DBA_TABLES WHERE OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘OLAPSYS’, ‘WKSYS’, ‘CTXSYS’, ‘RMAN’, ‘ODM’, ‘WMSYS’, ‘MDSYS’, ‘QS’, ‘QS_ES’, ‘QS_OS’, ‘QS_WS’, ‘ODM_MTR’, ‘QS_CBADM’, ‘QS_CS’, ‘ORDSYS’, ‘XDB’, ‘SCOTT’, ‘OUTLN’, ‘OE’) AND LAST_ANALYZED IS NULL GROUP BY OWNER

UNION ALL

SELECT ‘Tablas sin estadisticas ->’||OWNER, TO_CHAR(COUNT(*)), DECODE(COUNT(*),0,NULL,’Actualiza las estadisticas con "SELECT ”ANALYZE TABLE ”||OWNER||”.”||TABLE_NAME||” COMPUTE STATISTICS;” FROM DBA_TABLES WHERE OWNER=”’||OWNER||”’ AND LAST_ANALYZED IS NULL;"’) FROM DBA_TABLES WHERE OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘OLAPSYS’, ‘WKSYS’, ‘CTXSYS’, ‘RMAN’, ‘ODM’, ‘WMSYS’, ‘MDSYS’, ‘QS’, ‘QS_ES’, ‘QS_OS’, ‘QS_WS’, ‘ODM_MTR’, ‘QS_CBADM’, ‘QS_CS’, ‘ORDSYS’, ‘XDB’, ‘SCOTT’, ‘OUTLN’, ‘OE’) AND LAST_ANALYZED IS NULL GROUP BY OWNER

UNION ALL

SELECT ‘Indices con estadisticas antiguas -> ‘||OWNER, TO_CHAR(COUNT(*)), DECODE(COUNT(*),0,NULL,’Actualiza las estadisticas con "SELECT ”ANALYZE INDEX ”||OWNER||”.”||INDEX_NAME||” COMPUTE STATISTICS;” FROM DBA_INDEXES WHERE OWNER=”’||OWNER||”’ AND LAST_ANALYZED IS NULL;"’) FROM DBA_INDEXES WHERE OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘OLAPSYS’, ‘WKSYS’, ‘CTXSYS’, ‘RMAN’, ‘ODM’, ‘WMSYS’, ‘MDSYS’, ‘QS’, ‘QS_ES’, ‘QS_OS’, ‘QS_WS’, ‘ODM_MTR’, ‘QS_CBADM’, ‘QS_CS’, ‘ORDSYS’, ‘XDB’, ‘SCOTT’, ‘OUTLN’, ‘OE’) AND LAST_ANALYZED IS NULL GROUP BY OWNER

UNION ALL

SELECT ‘Indices sin estadisticas -> ‘||OWNER, TO_CHAR(COUNT(*)), DECODE(COUNT(*),0,NULL,’Actualiza las estadisticas con "SELECT ”ANALYZE INDEX ”||OWNER||”.”||INDEX_NAME||” COMPUTE STATISTICS;” FROM DBA_INDEXES WHERE OWNER=”’||OWNER||”’ AND LAST_ANALYZED IS NULL;"’) FROM DBA_INDEXES WHERE OWNER NOT IN (‘SYS’, ‘SYSTEM’, ‘OLAPSYS’, ‘WKSYS’, ‘CTXSYS’, ‘RMAN’, ‘ODM’, ‘WMSYS’, ‘MDSYS’, ‘QS’, ‘QS_ES’, ‘QS_OS’, ‘QS_WS’, ‘ODM_MTR’, ‘QS_CBADM’, ‘QS_CS’, ‘ORDSYS’, ‘XDB’, ‘SCOTT’, ‘OUTLN’, ‘OE’) AND LAST_ANALYZED IS NULL GROUP BY OWNER