Summary
Symptom
A Mini Check executed on the DB is not returning the expected value. It depends on a lot of factors if this is a problem, a potential risk or acceptable or even intended behavior.
The following section contains a lists of Mini Checks and information how to get more information about each Mini Check.
Other terms
EWA
Reason and Prerequisites
It is not a must that all of the following Mini Checks have been executed on the system, because the execution depends on a lot of factors (scope of the check, system type, time restrictions, etc.).
Especially in EWA we currently use just a small fraction of the listed mini checks.
The list is intended to be used with the Mini Check name as an entry point: search for the Mini Check name to get further information about those Mini Checks you got informed about. Ignore the other Mini Check list entries.
Solution
Mini Checks in alphabetical order
Get a list of columns without statistics:
SELECT TCS.TABLE_NAME, TCS. COLUMN_NAME, 'brconnect -u / -c -f stats -t '||TCS.TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES T, DBA_TAB_COL_STATISTICS TCS WHERE TCS.OWNER LIKE USER AND TCS.OWNER = T.OWNER AND TCS.TABLE_NAME = T.TABLE_NAME AND TCS.LAST_ANALYZED IS NULL ORDER BY TCS.TABLE_NAME, TCS.COLUMN_NAME;
Get a list of inconsistent DDIC Components:
SELECT COMP_ID, STATUS, VERSION FROM DBA_REGISTRY WHERE COMP_ID IN ('CATALOG','CATPROC');
Get a list of the domain indexes:
SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE = 'DOMAIN' ORDER BY INDEX_NAME;
Grant the authorization "Execute on DBMS_LOB" to the SAP DB user via the SAPCONN role (SAP notes 700548, 834917).
Get a list of the files with autoextent increment > 100 MB:
SELECT DF.INCREMENT_BY*TS.BLOCK_SIZE/1024 INCREMENT_BY_KB, DF.FILE_ID, 'ALTER DATABASE DATAFILE '''||DF.FILE_NAME||''' <AUTOEXTEND ON NEXT 100M;' COMMAND FROM DBA_TABLESPACES TS, DBA_DATA_FILES DF WHERE DF.TABLESPACE_NAME = TS.TABLESPACE_NAME AND DF.INCREMENT_BY * TS.BLOCK_SIZE > 100 * 1024 * 1024 ORDER BY DF.INCREMENT_BY DESC, DF.FILE_ID;
Get a list of the function based indexes:
SELECT IE.INDEX_OWNER, IE.INDEX_NAME, IE.COLUMN_EXPRESSION, IC.COLUMN_NAME FROM DBA_IND_EXPRESSIONS IE, DBA_IND_COLUMNS IC WHERE IE.INDEX_OWNER = IC.INDEX_OWNER AND IE.INDEX_NAME = IC.INDEX_NAME AND IE.COLUMN_POSITION = IC.COLUMN_POSITION AND IE.INDEX_OWNER LIKE USER ORDER BY IE.INDEX_NAME;
Get a list of the hidden columns:
SELECT TABLE_NAME, COLUMN_NAME FROM DBA_TAB_COLS WHERE OWNER LIKE USER AND HIDDEN_COLUMN = 'YES' ORDER BY TABLE_NAME, COLUMN_NAME;
Get a list of indexed columns of infocubes without histograms:
SELECT TC.TABLE_NAME, TC.COLUMN_NAME, 'brconnect -u / -c -f stats -t '||TC.TABLE_NAME||' -f collect' COMMAND FROM DBA_IND_COLUMNS IC, DBA_TAB_COL_STATISTICS TC WHERE TC.OWNER LIKE USER AND IC.TABLE_OWNER = TC.OWNER AND IC.TABLE_NAME = TC.TABLE_NAME AND IC.COLUMN_NAME = TC.COLUMN_NAME AND ( TC.TABLE_NAME LIKE '/BIC/F%' OR TC.TABLE_NAME LIKE '/BIC/9AF%' OR TC.TABLE_NAME LIKE '/BI0/F%' OR TC.TABLE_NAME LIKE '/BI0/9AF%' OR TC.TABLE_NAME LIKE '/BIC/E%' OR TC.TABLE_NAME LIKE '/BIC/9AE%' OR TC.TABLE_NAME LIKE '/BI0/E%' OR TC.TABLE_NAME LIKE '/BI0/9AE%' OR TC.TABLE_NAME LIKE '/BIC/D%' OR TC.TABLE_NAME LIKE '/BIC/9AD%' OR TC.TABLE_NAME LIKE '/BI0/D%' OR TC.TABLE_NAME LIKE '/BI0/9AD%' OR TC.TABLE_NAME LIKE '/BIC/S%' OR TC.TABLE_NAME LIKE '/BIC/9AS%' OR TC.TABLE_NAME LIKE '/BI0/S%' OR TC.TABLE_NAME LIKE '/BI0/9AS%' OR TC.TABLE_NAME LIKE '/BIC/X%' OR TC.TABLE_NAME LIKE '/BIC/9AX%' OR TC.TABLE_NAME LIKE '/BI0/X%' OR TC.TABLE_NAME LIKE '/BI0/9AX%' OR TC.TABLE_NAME LIKE '/BIC/Y%' OR TC.TABLE_NAME LIKE '/BIC/9AY%' OR TC.TABLE_NAME LIKE '/BI0/Y%' OR TC.TABLE_NAME LIKE '/BI0/9AY%' OR TC.TABLE_NAME LIKE '/BIC/I%' OR TC.TABLE_NAME LIKE '/BIC/9AI%' OR TC.TABLE_NAME LIKE '/BI0/I%' OR TC.TABLE_NAME LIKE '/BI0/9AI%' OR TC.TABLE_NAME LIKE '/BIC/P%' OR TC.TABLE_NAME LIKE '/BIC/9AP%' OR TC.TABLE_NAME LIKE '/BI0/P%' OR TC.TABLE_NAME LIKE '/BI0/9AP%' OR TC.TABLE_NAME LIKE '/BIC/Q%' OR TC.TABLE_NAME LIKE '/BIC/9AQ%' OR TC.TABLE_NAME LIKE '/BI0/Q%' OR TC.TABLE_NAME LIKE '/BI0/9AQ%' OR TC.TABLE_NAME LIKE '/BI0/02%' OR TC.TABLE_NAME LIKE '/BI0/06%' ) AND TC.NUM_DISTINCT > 0 AND TC.HISTOGRAM = 'NONE' ORDER BY TC.TABLE_NAME, TC.COLUMN_NAME;
Get a list of indexes with > 100 partitions:
SELECT INDEX_NAME, PARTITION_COUNT FROM DBA_PART_INDEXES WHERE OWNER LIKE USER AND PARTITION_COUNT > 100 ORDER BY INDEX_NAME;
Get a list of the indexes with name not including table name:
SELECT INDEX_NAME FROm DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE != 'LOB' AND INDEX_NAME NOT LIKE TABLE_NAME||'%' ORDER BY INDEX_NAME;
Get a list of the indexes with owner different from table:
SELECT INDEX_NAME, OWNER, TABLE_OWNER FROM DBA_INDEXES WHERE OWNER != TABLE_OWNER ORDER BY INDEX_NAME;
Get a list of the indexes with parallel degree > 1:
SELECT INDEX_NAME, DEGREE, INSTANCES, 'ALTER INDEX "'||INDEX_NAME||'" NOPARALLEL;' COMMAND FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE != 'LOB' AND (DEGREE != '1' OR INSTANCES NOT IN ('0', '1')) ORDER BY INDEX_NAME;
Get a list of indexes without statistics:
SELECT INDEX_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_INDEXES WHERE OWNER LIKE USER AND LAST_ANALYZED IS NULL AND INDEX_TYPE != 'LOB' ORDER BY INDEX_NAME;
Get a list of the invalid constraints:
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM DBA_CONSTRAINTS WHERE OWNER LIKE USER AND INVALID = 'INVALID' ORDER BY CONSTRAINT_NAME;
Get a list of the invalid objects in DDIC:
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER IN ('SYS', 'SYSTEM') AND STATUS = 'INVALID' ORDER BY OBJECT_NAME;
Get a list of the invalid procedures:
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER LIKE USER AND OBJECT_TYPE = 'PROCEDURE' AND STATUS = 'INVALID' ORDER BY OBJECT_NAME;
Get a list of invalid views:
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER LIKE USER AND OBJECT_TYPE = 'VIEW' AND STATUS = 'INVALID' ORDER BY OBJECT_NAME;
Get a list of the SYS_IOT_TOP indexes:
SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_NAME LIKE 'SYS_IOT_TOP%' ORDER BY INDEX_NAME;
Should be deactivated because OCM is not supported (note 974781).
Should be reduced to 10 % unless there is a specific reason (note 500340).
Get a list of the LOBs with PCTVERSION > 10:
SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME, PCTVERSION, 'ALTER TABLE "'||TABLE_NAME||'" MODIFY LOB ("'||COLUMN_NAME||'") (PCTVERSION 10);' COMMAND FROM DBA_LOBS WHERE OWNER LIKE USER AND PCTVERSION > 10 ORDER BY TABLE_NAME, COLUMN_NAME;
Get a list of MLOG$ tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER LIKE USER AND TABLE_NAME LIKE 'MLOG$%' ORDER BY TABLE_NAME;
Get a list of statistic snapshots for the different DB IDs to check in which timeframes there are overlaps:
SELECT HS.DBID||DECODE(HS.DBID, V.DBID, ' (CURRENT)','') DBID, TO_CHAR(HS.BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_INTERVAL_TIME, HS.SNAP_ID FROM DBA_HIST_SNAPSHOT HS, V$DATABASE V ORDER BY 1,2;
Get a list of all redolog group sizes of all instances:
SELECT THREAD#, GROUP#, BYTES FROM V$LOG ORDER BY THREAD#, GROUP#;
Get a list of the NON-BI indexes with nologging:
SELECT INDEX_NAME, 'ALTER INDEX "'||INDEX_NAME||'" NOLOGGING;' COMMAND FROM DBA_INDEXES WHERE OWNER LIKE USER AND LOGGING = 'NO' AND NOT INDEX_NAME like '/BI%' ORDER BY INDEX_NAME;
Get a list of the tables with nologging:
SELECT TABLE_NAME, 'ALTER TABLE "'||TABLE_NAME||'" NOLOGGING;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND LOGGING = 'NO' AND TEMPORARY = 'N' ORDER BY TABLE_NAME;
Get a list of the Non-default database users:
SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'TSMSYS', 'OUTLN', 'DIP', 'ORACLE_OCM', 'MGMT_VIEW', 'PERFSTAT', 'APPQOSSYS') AND USERNAME NOT LIKE 'OPS$%' AND USERNAME NOT LIKE 'SAP%' ORDER BY USERNAME;
Get a list of the Non-default sequences:
SELECT SEQUENCE_NAME FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER LIKE USER AND SEQUENCE_NAME != 'DDLOG_SEQ' ORDER BY SEQUENCE_NAME;
Get a list of the Non-default triggers:
SELECT TRIGGER_NAME, TRIGGER_TYPE FROM DBA_TRIGGERS WHERE TRIGGER_NAME NOT IN ('DEF$_PROPAGATOR_TRIG', 'REPCATLOGTRIG', 'AW_DROP_TRG', 'AW_TRUNC_TRG', 'AW_REN_TRG') ORDER BY TRIGGER_NAME;
With too many statements the duration of analysis queries may grow significantly and space may be wasted, without any significant benefit.
The recommended value is the Oracle default value 'DEFAULT' (=30 statements).
Get a list of the offline datafiles:
SELECT FILE#, NAME FROM V$DATAFILE WHERE STATUS = 'OFFLINE' ORDER BY FILE#;
Get a list how many ORA-1555 occured when:
SELECT TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD HH24:MI:SS')|| TO_CHAR(END_TIME, '-HH24:MI:SS') TIMEFRAME, SSOLDERRCNT "ORA-01555_ERRORS" FROM DBA_HIST_UNDOSTAT WHERE SSOLDERRCNT > 0 ORDER BY 1;
Get a list of QCM Tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER LIKE USER AND TABLE_NAME LIKE 'QCM%' ORDER BY TABLE_NAME;
Get the datafiles needing recovery:
SELECT FILE#, NAME FROM V$DATAFILE WHERE STATUS = 'RECOVER' ORDER BY FILE#;
Get a list of redo log groups without Oracle mirroring:
SELECT * FROM GV$LOG WHERE MEMBERS = 1;
Get a list of reverse key indexes:
SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE = 'NORMAL/REV' ORDER BY INDEX_NAME;
Get a list of RUPD$ tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER LIKE USER AND TABLE_NAME LIKE 'RUPD$%' ORDER BY TABLE_NAME;
Get a list of segments not in DEFAULT buffer pool:
SELECT DISTINCT SEGMENT_NAME, SEGMENT_TYPE, BUFFER_POOL, 'ALTER ' ||SEGMENT_TYPE||' "'||SEGMENT_NAME||'" STORAGE (BUFFER_POOL DEFAULT);' COMMAND FROM DBA_SEGMENTS WHERE OWNER LIKE USER AND BUFFER_POOL != 'DEFAULT' ORDER BY SEGMENT_NAME;
Get a list of segments not pre-calculated for DBA_SEGMENTS:
SELECT SEGMENT_NAME FROM SYS.SYS_DBA_SEGS WHERE BITAND(SEGMENT_FLAGS, 131073) = 1 AND SEGMENT_TYPE NOT IN ('ROLLBACK', 'TYPE2 UNDO') AND OWNER = USER ORDER BY SEGMENT_NAME;
Get a list of segments with INITIAL > 100 M:
SELECT SEGMENT_NAME, INITIAL_EXTENT FROM DBA_SEGMENTS WHERE INITIAL_EXTENT > 100 * 1024 * 1024 ORDER BY SEGMENT_NAME;
Get a list of segments with PCTFREE > 10:
SELECT TABLE_NAME, PCT_FREE, 'TAB' "TYPE", 'ALTER INDEX "'||TABLE_NAME||'" PCTFREE 1;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND PCT_FREE > 10 UNION ALL ( SELECT INDEX_NAME, PCT_FREE, 'IND' "TYPE", 'ALTER TABLE "'||INDEX_NAME||'" PCTFREE 10;' COMMAND FROM DBA_INDEXES WHERE OWNER LIKE USER AND PCT_FREE > 10);
With too short snapshot intervals the duration of analysis queries may grow significantly and space may be wasted, without any significant benefit.
The recommended value is the Oracle default value of 60 minutes.
With a too long snapshot retention the duration of analysis queries may grow significantly and space may be wasted, without any significant benefit.
The recommended value is 42 days. The Oracle default value of 7 days is too low (note 1326067).
Get a list of tables with supplemental logging switched on:
SELECT DISTINCT TABLE_NAME FROM DBA_LOG_GROUPS WHERE OWNER LIKE USER ORDER BY TABLE_NAME;
Get a list of the SYS_JOURNAL tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME LIKE 'SYS_JOURNAL%' ORDER BY TABLE_NAME;
Get a list of the tables with > 100 partitions:
SELECT TABLE_NAME, PARTITION_COUNT FROM DBA_PART_TABLES WHERE OWNER LIKE USER AND PARTITION_COUNT > 100 ORDER BY TABLE_NAME;
Get a list of tables with ANALYZE statistics:
SELECT TABLE_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND LAST_ANALYZED IS NOT NULL AND GLOBAL_STATS = 'NO' ORDER BY TABLE_NAME;
Get a list of the tables with diff. indexes in different tablespaces:
SELECT TABLE_NAME FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE NOT IN ('IOT - TOP', 'LOB') GROUP BY TABLE_NAME HAVING COUNT(DISTINCT(TABLESPACE_NAME)) > 1 ORDER BY TABLE_NAME;
Get a list of tables with disabled table lock:
SELECT TABLE_NAME, 'ALTER TABLE "'||TABLE_NAME||'" ENABLE TABLE LOCK;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND TABLE_LOCK = 'DISABLED' ORDER BY TABLE_NAME;
Get a list of tables with non-default histograms:
SELECT DISTINCT TABLE_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TAB_COL_STATISTICS WHERE OWNER LIKE USER AND HISTOGRAM != 'NONE' AND TABLE_NAME NOT LIKE '/BI%' AND TABLE_NAME NOT LIKE '/B20%' AND TABLE_NAME NOT IN ('MSEG', 'MKPF', 'RSDD_TMPNM_ADM', 'LTAP', 'LTBP', 'MKPF', 'BDCP', 'BDCPS', 'PPC_HEAD', 'RSREQDONE', 'RSSELDONE', 'RSSTATMANREQMAP', 'RSSTATMANSTATUS', 'RSTSODSREQUEST' ) ORDER BY TABLE_NAME;
Get a list of the tables with parallel degree > 1:
SELECT TABLE_NAME, DEGREE, INSTANCES, 'ALTER TABLE "'||TABLE_NAME||'" NOPARALLEL;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND (DEGREE !=' 1' OR INSTANCES NOT IN ( ' 0', ' 1')) ORDER BY TABLE_NAME;
Get the tables with PCTFREE < 10:
SELECT TABLE_NAME, PCT_FREE, 'ALTER TABLE "'||TABLE_NAME||'" PCTFREE 10;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND PCT_FREE < 10 AND COMPRESSION != 'ENABLED' ORDER BY TABLE_NAME;
Get a list of the tables with PCTUSED < 40:
SELECT TABLE_NAME, PCT_USED, 'ALTER TABLE "'||TABLE_NAME||'" PCTUSED 40;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND PCT_USED < 40 ORDER BY TABLE_NAME;
Get a list of the tables with preferred caching:
SELECT TABLE_NAME, 'ALTER TABLE "'||TABLE_NAME||'" NOCACHE;' COMMAND FROM DBA_TABLES WHERE CACHE = LPAD('Y',5) ORDER BY TABLE_NAME;
Get the tables with a primary key constraint:
SELECT TABLE_NAME, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER LIKE USER AND CONSTRAINT_TYPE = 'P' ORDER BY TABLE_NAME;
Get a list of tables with small statistic sample size:
SELECT TABLE_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND USER_STATS = 'NO' AND NUM_ROWS > 50000 AND SAMPLE_SIZE > 100 AND SAMPLE_SIZE < 0.2 * NUM_ROWS * DECODE(NUM_ROWS, 0, 0, DECODE(TRUNC(LOG(10, GREATEST(NUM_ROWS, BLOCKS))), 0, 1, 1, 1, 2, 1, 3, 1, 4, 0.3, 5, 0.1, 6, 0.03, 7, 0.01, 8, 0.003, 9, 0.001, 10, 0.0003, 11, 0.0001, 12, 0.00003, 0.00001)) ORDER BY TABLE_NAME;
Get a list of tables with statistics older than segment:
SELECT TABLE_NAME, TO_CHAR(LAST_ANALYZED, 'YYYY-MM_DD HH24:MI:SS') LAST_ANALYZED, TO_CHAR(O.CREATED, 'YYYY-MM_DD HH24:MI:SS') CREATED, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES T, DBA_OBJECTS O WHERE T.OWNER LIKE USER AND T.OWNER = O.OWNER AND T.TABLE_NAME = O.OBJECT_NAME AND O.OBJECT_TYPE = 'TABLE' AND O.SUBOBJECT_NAME IS NULL AND T.LAST_ANALYZED < O.CREATED ORDER BY TABLE_NAME;
Get a list of tables without statistics:
SELECT TABLE_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND LAST_ANALYZED IS NULL AND TEMPORARY = 'N' ORDER BY TABLE_NAME;
Get a list how many space errors occured when:
SELECT TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD HH24:MI:SS')|| TO_CHAR(END_TIME, '-HH24:MI:SS') TIMEFRAME, NOSPACEERRCNT "SPACE_ERRORS" FROM DBA_HIST_UNDOSTAT WHERE NOSPACEERRCNT > 0 ORDER BY 1;
Get a list of the UNUSABLE index partitions:
SELECT INDEX_NAME, PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE STATUS = 'UNUSABLE' ORDER BY INDEX_NAME, PARTITION_NAME;
Get a list of the UNUSABLE indexes:
SELECT INDEX_NAME FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE' ORDER BY INDEX_NAME;
Get a list of the UNUSABLE tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE STATUS = 'UNUSABLE' ORDER BY TABLE_NAME;
Get a list of virtual columns:
SELECT TABLE_NAME, COLUMN_NAME FROM DBA_TAB_COLS WHERE OWNER LIKE USER AND VIRTUAL_COLUMN = 'YES' ORDER BY TABLE_NAME, COLUMN_NAME;
A Mini Check executed on the DB is not returning the expected value. It depends on a lot of factors if this is a problem, a potential risk or acceptable or even intended behavior.
The following section contains a lists of Mini Checks and information how to get more information about each Mini Check.
Other terms
EWA
Reason and Prerequisites
It is not a must that all of the following Mini Checks have been executed on the system, because the execution depends on a lot of factors (scope of the check, system type, time restrictions, etc.).
Especially in EWA we currently use just a small fraction of the listed mini checks.
The list is intended to be used with the Mini Check name as an entry point: search for the Mini Check name to get further information about those Mini Checks you got informed about. Ignore the other Mini Check list entries.
Solution
Mini Checks in alphabetical order
- 1. Archive Log Mode
- 2. Archiver stuck - Existence of archiver stuck situation since last database startup
- 3. Autotask auto optimizer stats job - Automatic Oracle statistics collector
- 4. Autotask auto space advisor job
Autotask automatic sql tuning task
- 5. Characterset - Name of database characterset
- 6. Columns without statistics - Number of columns without CBO statistics
Get a list of columns without statistics:
SELECT TCS.TABLE_NAME, TCS. COLUMN_NAME, 'brconnect -u / -c -f stats -t '||TCS.TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES T, DBA_TAB_COL_STATISTICS TCS WHERE TCS.OWNER LIKE USER AND TCS.OWNER = T.OWNER AND TCS.TABLE_NAME = T.TABLE_NAME AND TCS.LAST_ANALYZED IS NULL ORDER BY TCS.TABLE_NAME, TCS.COLUMN_NAME;
- 7. DBWR Throughput - Existence of checkpoint not complete or free buffer wait situations since last database startup
- 8. DDIC Components inconsistent - Number of DDIC components that are inconsistent
Get a list of inconsistent DDIC Components:
SELECT COMP_ID, STATUS, VERSION FROM DBA_REGISTRY WHERE COMP_ID IN ('CATALOG','CATPROC');
- 9. DDIC statistics creation - Date of last DDIC statistics creation
- 10. Domain indexes - Number of domain indexes
Get a list of the domain indexes:
SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE = 'DOMAIN' ORDER BY INDEX_NAME;
- 11. EXECUTE on DBMS_LOB revoked - Checks if the permission to execute DBMS_LOB functions is revoked from PUBLIC and user role
Grant the authorization "Execute on DBMS_LOB" to the SAP DB user via the SAPCONN role (SAP notes 700548, 834917).
- 12. Files with AUTOEXTEND increment > 100 M
Get a list of the files with autoextent increment > 100 MB:
SELECT DF.INCREMENT_BY*TS.BLOCK_SIZE/1024 INCREMENT_BY_KB, DF.FILE_ID, 'ALTER DATABASE DATAFILE '''||DF.FILE_NAME||''' <AUTOEXTEND ON NEXT 100M;' COMMAND FROM DBA_TABLESPACES TS, DBA_DATA_FILES DF WHERE DF.TABLESPACE_NAME = TS.TABLESPACE_NAME AND DF.INCREMENT_BY * TS.BLOCK_SIZE > 100 * 1024 * 1024 ORDER BY DF.INCREMENT_BY DESC, DF.FILE_ID;
- 13. Fixed objects statistics creation - Date of last fixed objects statistics creation
- 14. Force Logging Mode - Checks whether forced logging is active (redo log generation even in case of NOLOGGING requests)
- 15. Function based indexes - Number of indexes with at least one function based column
Get a list of the function based indexes:
SELECT IE.INDEX_OWNER, IE.INDEX_NAME, IE.COLUMN_EXPRESSION, IC.COLUMN_NAME FROM DBA_IND_EXPRESSIONS IE, DBA_IND_COLUMNS IC WHERE IE.INDEX_OWNER = IC.INDEX_OWNER AND IE.INDEX_NAME = IC.INDEX_NAME AND IE.COLUMN_POSITION = IC.COLUMN_POSITION AND IE.INDEX_OWNER LIKE USER ORDER BY IE.INDEX_NAME;
- 16. Hidden columns - Number of hidden columns
Get a list of the hidden columns:
SELECT TABLE_NAME, COLUMN_NAME FROM DBA_TAB_COLS WHERE OWNER LIKE USER AND HIDDEN_COLUMN = 'YES' ORDER BY TABLE_NAME, COLUMN_NAME;
- 17. Indexed columns of infocubes without histograms - Number of indexed infocube columns without histogram statistics
Get a list of indexed columns of infocubes without histograms:
SELECT TC.TABLE_NAME, TC.COLUMN_NAME, 'brconnect -u / -c -f stats -t '||TC.TABLE_NAME||' -f collect' COMMAND FROM DBA_IND_COLUMNS IC, DBA_TAB_COL_STATISTICS TC WHERE TC.OWNER LIKE USER AND IC.TABLE_OWNER = TC.OWNER AND IC.TABLE_NAME = TC.TABLE_NAME AND IC.COLUMN_NAME = TC.COLUMN_NAME AND ( TC.TABLE_NAME LIKE '/BIC/F%' OR TC.TABLE_NAME LIKE '/BIC/9AF%' OR TC.TABLE_NAME LIKE '/BI0/F%' OR TC.TABLE_NAME LIKE '/BI0/9AF%' OR TC.TABLE_NAME LIKE '/BIC/E%' OR TC.TABLE_NAME LIKE '/BIC/9AE%' OR TC.TABLE_NAME LIKE '/BI0/E%' OR TC.TABLE_NAME LIKE '/BI0/9AE%' OR TC.TABLE_NAME LIKE '/BIC/D%' OR TC.TABLE_NAME LIKE '/BIC/9AD%' OR TC.TABLE_NAME LIKE '/BI0/D%' OR TC.TABLE_NAME LIKE '/BI0/9AD%' OR TC.TABLE_NAME LIKE '/BIC/S%' OR TC.TABLE_NAME LIKE '/BIC/9AS%' OR TC.TABLE_NAME LIKE '/BI0/S%' OR TC.TABLE_NAME LIKE '/BI0/9AS%' OR TC.TABLE_NAME LIKE '/BIC/X%' OR TC.TABLE_NAME LIKE '/BIC/9AX%' OR TC.TABLE_NAME LIKE '/BI0/X%' OR TC.TABLE_NAME LIKE '/BI0/9AX%' OR TC.TABLE_NAME LIKE '/BIC/Y%' OR TC.TABLE_NAME LIKE '/BIC/9AY%' OR TC.TABLE_NAME LIKE '/BI0/Y%' OR TC.TABLE_NAME LIKE '/BI0/9AY%' OR TC.TABLE_NAME LIKE '/BIC/I%' OR TC.TABLE_NAME LIKE '/BIC/9AI%' OR TC.TABLE_NAME LIKE '/BI0/I%' OR TC.TABLE_NAME LIKE '/BI0/9AI%' OR TC.TABLE_NAME LIKE '/BIC/P%' OR TC.TABLE_NAME LIKE '/BIC/9AP%' OR TC.TABLE_NAME LIKE '/BI0/P%' OR TC.TABLE_NAME LIKE '/BI0/9AP%' OR TC.TABLE_NAME LIKE '/BIC/Q%' OR TC.TABLE_NAME LIKE '/BIC/9AQ%' OR TC.TABLE_NAME LIKE '/BI0/Q%' OR TC.TABLE_NAME LIKE '/BI0/9AQ%' OR TC.TABLE_NAME LIKE '/BI0/02%' OR TC.TABLE_NAME LIKE '/BI0/06%' ) AND TC.NUM_DISTINCT > 0 AND TC.HISTOGRAM = 'NONE' ORDER BY TC.TABLE_NAME, TC.COLUMN_NAME;
- 18. Indexes with > 100 partitions - Number of indexes with more than 100 partitions
Get a list of indexes with > 100 partitions:
SELECT INDEX_NAME, PARTITION_COUNT FROM DBA_PART_INDEXES WHERE OWNER LIKE USER AND PARTITION_COUNT > 100 ORDER BY INDEX_NAME;
- 19. Indexes with name not including table name - Number of indexes with a name that doesn't start with the table's name
Get a list of the indexes with name not including table name:
SELECT INDEX_NAME FROm DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE != 'LOB' AND INDEX_NAME NOT LIKE TABLE_NAME||'%' ORDER BY INDEX_NAME;
- 20. Indexes with owner different from table - Number of indexes that are created under a different user than the related table
Get a list of the indexes with owner different from table:
SELECT INDEX_NAME, OWNER, TABLE_OWNER FROM DBA_INDEXES WHERE OWNER != TABLE_OWNER ORDER BY INDEX_NAME;
- 21. Indexes with parallel degree > 1 - Number of indexes with a parallelism degree greater than 1 defined on segment level
Get a list of the indexes with parallel degree > 1:
SELECT INDEX_NAME, DEGREE, INSTANCES, 'ALTER INDEX "'||INDEX_NAME||'" NOPARALLEL;' COMMAND FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE != 'LOB' AND (DEGREE != '1' OR INSTANCES NOT IN ('0', '1')) ORDER BY INDEX_NAME;
- 22. Indexes without statistics - Number of indexes without CBO statistics
Get a list of indexes without statistics:
SELECT INDEX_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_INDEXES WHERE OWNER LIKE USER AND LAST_ANALYZED IS NULL AND INDEX_TYPE != 'LOB' ORDER BY INDEX_NAME;
- 23. INVALID constraints - Number of INVALID constraints
Get a list of the invalid constraints:
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM DBA_CONSTRAINTS WHERE OWNER LIKE USER AND INVALID = 'INVALID' ORDER BY CONSTRAINT_NAME;
- 24. INVALID objects in DDIC - Number of Oracle DDIC objects with status INVALID
Get a list of the invalid objects in DDIC:
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER IN ('SYS', 'SYSTEM') AND STATUS = 'INVALID' ORDER BY OBJECT_NAME;
- 25. INVALID procedures - Number of INVALID procedures
Get a list of the invalid procedures:
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER LIKE USER AND OBJECT_TYPE = 'PROCEDURE' AND STATUS = 'INVALID' ORDER BY OBJECT_NAME;
- 26. INVALID views - Number of INVALID views
Get a list of invalid views:
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER LIKE USER AND OBJECT_TYPE = 'VIEW' AND STATUS = 'INVALID' ORDER BY OBJECT_NAME;
- 27. IOT_TOP indexes - Number of SYS_IOT_TOP indexes (related to index online rebuilds)
Get a list of the SYS_IOT_TOP indexes:
SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_NAME LIKE 'SYS_IOT_TOP%' ORDER BY INDEX_NAME;
- 28. Job AUTO_SPACE_ADVISOR_JOB - Automatic Segment advisor job
- 29. Job GATHER_STATS_JOB - Automatic Oracle statistics collector
- 30. Job MGMT_STATS_CONFIG_JOB
Job MGMT_CONFIG_JOB
Should be deactivated because OCM is not supported (note 974781).
- 31. Last CBO statistics creation - Date of last CBO statistics creation
- 32. LOBs with PCTVERSION > 10 - Number of LOBs that reserve more than 10 % for undo information
Should be reduced to 10 % unless there is a specific reason (note 500340).
Get a list of the LOBs with PCTVERSION > 10:
SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME, PCTVERSION, 'ALTER TABLE "'||TABLE_NAME||'" MODIFY LOB ("'||COLUMN_NAME||'") (PCTVERSION 10);' COMMAND FROM DBA_LOBS WHERE OWNER LIKE USER AND PCTVERSION > 10 ORDER BY TABLE_NAME, COLUMN_NAME;
- 33. Log switches within less
than 1 minute - Percentage of online redo log switches that happened
within less than 1 minute (taking into account the last 7 days)
- 34. Maintenance windows using Resource Manager - Number of maintenance windows with assigned Resource Manager mode
- 35. Missing ST04 initialization - Check if transaction ST04 is initialized properly
- 36.
MLOG$ tables - Number of MLOG$ tables that should only exist during
online reorganizations or in case of user defined materialized views
Get a list of MLOG$ tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER LIKE USER AND TABLE_NAME LIKE 'MLOG$%' ORDER BY TABLE_NAME;
- 37. Multiple database IDs - Checks whether more than one DBID exists in history tables
Get a list of statistic snapshots for the different DB IDs to check in which timeframes there are overlaps:
SELECT HS.DBID||DECODE(HS.DBID, V.DBID, ' (CURRENT)','') DBID, TO_CHAR(HS.BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_INTERVAL_TIME, HS.SNAP_ID FROM DBA_HIST_SNAPSHOT HS, V$DATABASE V ORDER BY 1,2;
- 38. Multiple redo log sizes - Redo logs exist with different sizes
Get a list of all redolog group sizes of all instances:
SELECT THREAD#, GROUP#, BYTES FROM V$LOG ORDER BY THREAD#, GROUP#;
- 39. National Characterset - Name of database national characterset
- 40. NOLOGGING indexes (Non-BI) - Number of Non-BI indexes defined with NOLOGGING
Get a list of the NON-BI indexes with nologging:
SELECT INDEX_NAME, 'ALTER INDEX "'||INDEX_NAME||'" NOLOGGING;' COMMAND FROM DBA_INDEXES WHERE OWNER LIKE USER AND LOGGING = 'NO' AND NOT INDEX_NAME like '/BI%' ORDER BY INDEX_NAME;
- 41. NOLOGGING tables - Number of tables defined with NOLOGGING
Get a list of the tables with nologging:
SELECT TABLE_NAME, 'ALTER TABLE "'||TABLE_NAME||'" NOLOGGING;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND LOGGING = 'NO' AND TEMPORARY = 'N' ORDER BY TABLE_NAME;
- 42. Non-default database users - Number of Oracle users that don't exist per default
Get a list of the Non-default database users:
SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'TSMSYS', 'OUTLN', 'DIP', 'ORACLE_OCM', 'MGMT_VIEW', 'PERFSTAT', 'APPQOSSYS') AND USERNAME NOT LIKE 'OPS$%' AND USERNAME NOT LIKE 'SAP%' ORDER BY USERNAME;
- 43. Non-default sequences - Number of sequences that don't exist per default
Get a list of the Non-default sequences:
SELECT SEQUENCE_NAME FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER LIKE USER AND SEQUENCE_NAME != 'DDLOG_SEQ' ORDER BY SEQUENCE_NAME;
- 44. Non-default triggers - Number of triggers that don't exist per default
Get a list of the Non-default triggers:
SELECT TRIGGER_NAME, TRIGGER_TYPE FROM DBA_TRIGGERS WHERE TRIGGER_NAME NOT IN ('DEF$_PROPAGATOR_TRIG', 'REPCATLOGTRIG', 'AW_DROP_TRG', 'AW_TRUNC_TRG', 'AW_REN_TRG') ORDER BY TRIGGER_NAME;
- 45. Number of captured SQL statements - Number of top SQL statements per AWR snapshot
With too many statements the duration of analysis queries may grow significantly and space may be wasted, without any significant benefit.
The recommended value is the Oracle default value 'DEFAULT' (=30 statements).
- 46. OFFLINE datafiles - Number of datafiles that are currently OFFLINE
Get a list of the offline datafiles:
SELECT FILE#, NAME FROM V$DATAFILE WHERE STATUS = 'OFFLINE' ORDER BY FILE#;
- 47. ORA-01555 errors - Number of ORA-01555 errors ("snapshot too old") in history
Get a list how many ORA-1555 occured when:
SELECT TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD HH24:MI:SS')|| TO_CHAR(END_TIME, '-HH24:MI:SS') TIMEFRAME, SSOLDERRCNT "ORA-01555_ERRORS" FROM DBA_HIST_UNDOSTAT WHERE SSOLDERRCNT > 0 ORDER BY 1;
- 48. Oracle Patchset not applied
Oracle Patchset update available
Oracle Patchset outdated
Oracle Patchset not supported
Oracle Release outdated
Oracle Release not supported
The Oracle software used is not up to date.
Check note 1174136 for end-of-support dates and notes 871735 (Oracle 10.2) resp. 1431799 (Oracle 11.2) for currently available Patchsets.
- 49. Oracle Release out of standard support
Oracle 10.2 reached the chargeable extended support phase. Notes 1174136 and 1339724 describe the details.
- 50. Outdated DBA_EXTENTS helper view used - Old SAP_DBA_EXTENTS view still in place
- 51. Outdated DBA_SEGMENTS definition used - DBA_SEGMENTS view exists with old definition (not taking into account "TYPE2 UNDO")
- 52. Outdated PLAN_TABLE used
- 53. Outdated SYS_DBA_SEGS helper view used - Old SYS_DBA_SEGS_2 view still in place
- 54. Outlines - Number of existing outlines
- 55. Parallel operations downgraded - Number of parallel executions (PX) that were performed with a reduced parallelism degree
- 56. PGA multipass executions - Number of workarea operations like sort, hash or bitmap that have to use PSAPTEMP intensively
- 57. Protection mode - Dataguard standby database protection mode
- 58. PSAPTEMP type - Type of PSAPTEMP tablespace
- 59. QCM tables - Number of QCM tables
Get a list of QCM Tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER LIKE USER AND TABLE_NAME LIKE 'QCM%' ORDER BY TABLE_NAME;
- 60. RECOVER datafiles - Number of datafiles that currently require recovery
Get the datafiles needing recovery:
SELECT FILE#, NAME FROM V$DATAFILE WHERE STATUS = 'RECOVER' ORDER BY FILE#;
- 61. RECYCLEBIN segments - Number of segments in Oracle recyclebin
- 62. Redo log mirroring not by Oracle
Get a list of redo log groups without Oracle mirroring:
SELECT * FROM GV$LOG WHERE MEMBERS = 1;
- 63. Resource Manager active - Name of currently active Resource Manager mode
- 64. Reverse key indexes
Get a list of reverse key indexes:
SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE = 'NORMAL/REV' ORDER BY INDEX_NAME;
- 65.
RUPD$ tables - Number of RUPD$ tables that should only exist during
online reorganizations or in case of user defined materialized views
Get a list of RUPD$ tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER LIKE USER AND TABLE_NAME LIKE 'RUPD$%' ORDER BY TABLE_NAME;
- 66. Segments not in DEFAULT buffer pool - Number of segments assigned to KEEP or RECYCLE pool
Get a list of segments not in DEFAULT buffer pool:
SELECT DISTINCT SEGMENT_NAME, SEGMENT_TYPE, BUFFER_POOL, 'ALTER ' ||SEGMENT_TYPE||' "'||SEGMENT_NAME||'" STORAGE (BUFFER_POOL DEFAULT);' COMMAND FROM DBA_SEGMENTS WHERE OWNER LIKE USER AND BUFFER_POOL != 'DEFAULT' ORDER BY SEGMENT_NAME;
- 67. Segments not pre-calculated
for DBA_SEGMENTS - Number of segments that require a segment header
access for extent and size information
Get a list of segments not pre-calculated for DBA_SEGMENTS:
SELECT SEGMENT_NAME FROM SYS.SYS_DBA_SEGS WHERE BITAND(SEGMENT_FLAGS, 131073) = 1 AND SEGMENT_TYPE NOT IN ('ROLLBACK', 'TYPE2 UNDO') AND OWNER = USER ORDER BY SEGMENT_NAME;
- 68. Segments with INITIAL > 100 M - Number of segments with an initial extent size of more than 100 MB
Get a list of segments with INITIAL > 100 M:
SELECT SEGMENT_NAME, INITIAL_EXTENT FROM DBA_SEGMENTS WHERE INITIAL_EXTENT > 100 * 1024 * 1024 ORDER BY SEGMENT_NAME;
- 69. Segments with PCTFREE > 10 - Number of segments with a PCTFREE value greater than 10
Get a list of segments with PCTFREE > 10:
SELECT TABLE_NAME, PCT_FREE, 'TAB' "TYPE", 'ALTER INDEX "'||TABLE_NAME||'" PCTFREE 1;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND PCT_FREE > 10 UNION ALL ( SELECT INDEX_NAME, PCT_FREE, 'IND' "TYPE", 'ALTER TABLE "'||INDEX_NAME||'" PCTFREE 10;' COMMAND FROM DBA_INDEXES WHERE OWNER LIKE USER AND PCT_FREE > 10);
- 70. Snapshot interval (minutes) - AWR snapshot interval frequency in minutes
With too short snapshot intervals the duration of analysis queries may grow significantly and space may be wasted, without any significant benefit.
The recommended value is the Oracle default value of 60 minutes.
- 71. Snapshot retention (days) - AWR history retention in days
With a too long snapshot retention the duration of analysis queries may grow significantly and space may be wasted, without any significant benefit.
The recommended value is 42 days. The Oracle default value of 7 days is too low (note 1326067).
- 72. Supplemental logging tables - Number of tables with activated supplemental logging
Get a list of tables with supplemental logging switched on:
SELECT DISTINCT TABLE_NAME FROM DBA_LOG_GROUPS WHERE OWNER LIKE USER ORDER BY TABLE_NAME;
- 73. SYS_JOURNAL tables - Number of SYS_JOURNAL IOTs (related to index online rebuilds)
Get a list of the SYS_JOURNAL tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME LIKE 'SYS_JOURNAL%' ORDER BY TABLE_NAME;
- 74. Tables with > 100 partitions - Number of tables with more than 100 partitions
Get a list of the tables with > 100 partitions:
SELECT TABLE_NAME, PARTITION_COUNT FROM DBA_PART_TABLES WHERE OWNER LIKE USER AND PARTITION_COUNT > 100 ORDER BY TABLE_NAME;
- 75. Tables with ANALYZE statistics - Number of tables with CBO statistics calculated via ANALYZE rather than DBMS_STATS
Get a list of tables with ANALYZE statistics:
SELECT TABLE_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND LAST_ANALYZED IS NOT NULL AND GLOBAL_STATS = 'NO' ORDER BY TABLE_NAME;
- 76.
Tables with diff. indexes in different tablespaces - Number of tables
with different indexes located in different tablespaces
Get a list of the tables with diff. indexes in different tablespaces:
SELECT TABLE_NAME FROM DBA_INDEXES WHERE OWNER LIKE USER AND INDEX_TYPE NOT IN ('IOT - TOP', 'LOB') GROUP BY TABLE_NAME HAVING COUNT(DISTINCT(TABLESPACE_NAME)) > 1 ORDER BY TABLE_NAME;
- 77. Tables with disabled table lock - Number of tables with disabled DDL locking on table level
Get a list of tables with disabled table lock:
SELECT TABLE_NAME, 'ALTER TABLE "'||TABLE_NAME||'" ENABLE TABLE LOCK;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND TABLE_LOCK = 'DISABLED' ORDER BY TABLE_NAME;
- 78. Tables with non-default
histograms - Number of tables that have histogram statistics although
SAP doesn't recommend them per default
Get a list of tables with non-default histograms:
SELECT DISTINCT TABLE_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TAB_COL_STATISTICS WHERE OWNER LIKE USER AND HISTOGRAM != 'NONE' AND TABLE_NAME NOT LIKE '/BI%' AND TABLE_NAME NOT LIKE '/B20%' AND TABLE_NAME NOT IN ('MSEG', 'MKPF', 'RSDD_TMPNM_ADM', 'LTAP', 'LTBP', 'MKPF', 'BDCP', 'BDCPS', 'PPC_HEAD', 'RSREQDONE', 'RSSELDONE', 'RSSTATMANREQMAP', 'RSSTATMANSTATUS', 'RSTSODSREQUEST' ) ORDER BY TABLE_NAME;
- 79. Tables with parallel degree > 1 - Number of tables with a parallelism degree greater than 1 defined on segment level
Get a list of the tables with parallel degree > 1:
SELECT TABLE_NAME, DEGREE, INSTANCES, 'ALTER TABLE "'||TABLE_NAME||'" NOPARALLEL;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND (DEGREE !=' 1' OR INSTANCES NOT IN ( ' 0', ' 1')) ORDER BY TABLE_NAME;
- 80. Tables with PCTFREE < 10 - Number of tables with a PCTFREE value smaller than 10
Get the tables with PCTFREE < 10:
SELECT TABLE_NAME, PCT_FREE, 'ALTER TABLE "'||TABLE_NAME||'" PCTFREE 10;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND PCT_FREE < 10 AND COMPRESSION != 'ENABLED' ORDER BY TABLE_NAME;
- 81. Tables with PCTUSED < 40 - Number of tables with a PCTUSED value smaller than 10
Get a list of the tables with PCTUSED < 40:
SELECT TABLE_NAME, PCT_USED, 'ALTER TABLE "'||TABLE_NAME||'" PCTUSED 40;' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND PCT_USED < 40 ORDER BY TABLE_NAME;
- 82. Tables with preferred caching - Number of
tables with CACHE=Y so that blocks of the table are cached in the same
way like indexes
Get a list of the tables with preferred caching:
SELECT TABLE_NAME, 'ALTER TABLE "'||TABLE_NAME||'" NOCACHE;' COMMAND FROM DBA_TABLES WHERE CACHE = LPAD('Y',5) ORDER BY TABLE_NAME;
- 83. Tables with primary key constraint - Number of tables with a primary key constraint
Get the tables with a primary key constraint:
SELECT TABLE_NAME, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER LIKE USER AND CONSTRAINT_TYPE = 'P' ORDER BY TABLE_NAME;
- 84.
Tables with small statistics sample size - Number of tables with CBO
statistics based on a number of rows that is significantly lower than
the BRCONNECT statistics sample size
Get a list of tables with small statistic sample size:
SELECT TABLE_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND USER_STATS = 'NO' AND NUM_ROWS > 50000 AND SAMPLE_SIZE > 100 AND SAMPLE_SIZE < 0.2 * NUM_ROWS * DECODE(NUM_ROWS, 0, 0, DECODE(TRUNC(LOG(10, GREATEST(NUM_ROWS, BLOCKS))), 0, 1, 1, 1, 2, 1, 3, 1, 4, 0.3, 5, 0.1, 6, 0.03, 7, 0.01, 8, 0.003, 9, 0.001, 10, 0.0003, 11, 0.0001, 12, 0.00003, 0.00001)) ORDER BY TABLE_NAME;
- 85. Tables with
statistics older than segment - Number of tables with CBO statistics
that are older than the segment itself (can e.g. happen after an online
reorganization)
Get a list of tables with statistics older than segment:
SELECT TABLE_NAME, TO_CHAR(LAST_ANALYZED, 'YYYY-MM_DD HH24:MI:SS') LAST_ANALYZED, TO_CHAR(O.CREATED, 'YYYY-MM_DD HH24:MI:SS') CREATED, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES T, DBA_OBJECTS O WHERE T.OWNER LIKE USER AND T.OWNER = O.OWNER AND T.TABLE_NAME = O.OBJECT_NAME AND O.OBJECT_TYPE = 'TABLE' AND O.SUBOBJECT_NAME IS NULL AND T.LAST_ANALYZED < O.CREATED ORDER BY TABLE_NAME;
- 86. Tables without statistics - Number of tables without CBO statistics
Get a list of tables without statistics:
SELECT TABLE_NAME, 'brconnect -u / -c -f stats -t '||TABLE_NAME||' -f collect' COMMAND FROM DBA_TABLES WHERE OWNER LIKE USER AND LAST_ANALYZED IS NULL AND TEMPORARY = 'N' ORDER BY TABLE_NAME;
- 87. Temporary
tablespace smaller than largest index - Checkes whether the size of the
temporary tablespace is smaller than the largest index segment
- 88. Undo space errors - Number of space errors for undo tablespace in history
Get a list how many space errors occured when:
SELECT TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD HH24:MI:SS')|| TO_CHAR(END_TIME, '-HH24:MI:SS') TIMEFRAME, NOSPACEERRCNT "SPACE_ERRORS" FROM DBA_HIST_UNDOSTAT WHERE NOSPACEERRCNT > 0 ORDER BY 1;
- 89. UNUSABLE index partitions - Number of UNUSABLE index partitions
Get a list of the UNUSABLE index partitions:
SELECT INDEX_NAME, PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE STATUS = 'UNUSABLE' ORDER BY INDEX_NAME, PARTITION_NAME;
- 90. UNUSABLE indexes - Number of UNUSABLE indexes
Get a list of the UNUSABLE indexes:
SELECT INDEX_NAME FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE' ORDER BY INDEX_NAME;
- 91. UNUSABLE tables - Number of UNUSABLE tables
Get a list of the UNUSABLE tables:
SELECT TABLE_NAME FROM DBA_TABLES WHERE STATUS = 'UNUSABLE' ORDER BY TABLE_NAME;
- 92. Virtual columns - Number of virtual columns
Get a list of virtual columns:
SELECT TABLE_NAME, COLUMN_NAME FROM DBA_TAB_COLS WHERE OWNER LIKE USER AND VIRTUAL_COLUMN = 'YES' ORDER BY TABLE_NAME, COLUMN_NAME;
Header Data
| Release Status: | Released for Customer |
| Released on: | 23.08.2012 07:14:51 |
| Master Language: | English |
| Priority: | Recommendations/additional info |
| Category: | Consulting |
| Primary Component: | BC-DB-ORA Oracle |
Affected Releases
Release-Independent
No comments:
Post a Comment