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
1. Archive Log Mode
Should
always be "ARCHIVELOG", otherwise no offline redo logs are created and
recovery is not possible (note 863417).
2. Archiver stuck - Existence of archiver stuck situation since last database startup
Improved proactive monitoring is required to avoid this scenario in the future (note 391).
3. Autotask auto optimizer stats job - Automatic Oracle statistics collector
Should be DISABLED so that only BRCONNECT creates CBO statistics (note 974781).
4. Autotask auto space advisor job
Autotask automatic sql tuning task
Should only be ENABLED if really required (note 974781).
5. Characterset - Name of database characterset
Should
usually be UTF8 (Unicode) or WE8DEC (non-Unicode) (note 606359).
US7ASCII not officially supported any more (note 819830)
6. Columns without statistics - Number of columns without CBO statistics
Eliminate reason for missing column statistics and create new CBO statistics (note 588668).
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
Redo log layout and DBWR performance needs to be examined (note 79341).
8. DDIC Components inconsistent - Number of DDIC components that are inconsistent
Version of the Catalog and Catproc should match to the Oracle software release and be VALID (note 648203).
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
DDIC statistics should be in place and not be older than 6 month (note 838725).
10. Domain indexes - Number of domain indexes
Should only be used in Bugseye / Requisite environments (note 105047)
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
Can
result in errors of analysis and monitoring commands, if technical LOB
columns (e.g. for SQL text) are accessed.
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
Can
result in critical processes being busy a long time with AUTOEXTEND
operations and should therefore be avoided
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
Fixed objects statistics should be in place and not be older than 6 month (note 838725).
14. Force Logging Mode - Checks whether forced logging is active (redo log generation even in case of NOLOGGING requests)
Should only be activated if really required e.g. in case of standby databases (note 824212).
15. Function based indexes - Number of indexes with at least one function based column
Check
if function is really on purpose and required. If it was created
because of note 723879 using the RAWTOHEX function it is not required if
another index exists with the same columns but without the RAWTOHEX
function.
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
Can
be related to ALTER TABLE ... SET UNUSED commands (like executed by
SAP upgrades) or online reorganizations, can be cleaned up via
reorganization.
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
Indexed
columns of BW infocubes should normally have histogram statistics.
Check why they are missing and take appropriate actions to collect and
keep them (note 797629).
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
High
number of partitions can result in different performance problems,
should be avoided for F fact tables (notes 722188, 590370).
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
Should normally not happen in SAP ABAP environments.
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
Should normally not happen, recreation of indexes with correct user recommended.
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
Reset parallelism to NOPARALLEL in order to avoid undesired parallel executions (note 651060).
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
Eliminate reason for missing index statistics and create new CBO statistics (note 588668).
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
Should not happen, validation useful.
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
All
objects should be VALID, otherwise there is a risk that certain Oracle
functionality doesn't work properly (note 648203)
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
Should
not happen, validation useful.Old /BI0/04 BW procedures are not needed
anymore on BW >= 7.0 (note 449891).
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
Should not happen, validation useful.
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)
Should only exist while an index is rebuilt online (note 641435).
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
Should only be ENABLED if really required (note 927813).
29. Job GATHER_STATS_JOB - Automatic Oracle statistics collector
Should be DISABLED so that only BRCONNECT creates CBO statistics (note 974781)
30. Job MGMT_STATS_CONFIG_JOB
Job MGMT_CONFIG_JOB
Oracle Configuration Manger (OCM) jobs
Should be deactivated because OCM is not supported (note 974781).
31. Last CBO statistics creation - Date of last CBO statistics creation
Should normally be within the last 24 hours. If not, BRCONNECT may not be scheduled daily (note 588668).
32. LOBs with PCTVERSION > 10 - Number of LOBs that reserve more than 10 % for undo information
Can result in increased space demands
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)
Check
if the quick log switches happen on a regular basis and increase the
online redo logs if required (note 79341).
34. Maintenance windows using Resource Manager - Number of maintenance windows with assigned Resource Manager mode
Should be avoided (note 1579946).
35. Missing ST04 initialization - Check if transaction ST04 is initialized properly
If "Yes" and ST04 / ST04N / DBACOCKPIT is available, SAP Note 706927 needs to be implemented.
36.
MLOG$ tables - Number of MLOG$ tables that should only exist during
online reorganizations or in case of user defined materialized views
No longer needed MLOG$ tables should be purged (note 741478).
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
If
yes, it is important not to mix up history information of the current
database with history information from a previous / different database
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
Make sure that all redo logs are created with the same size (note 1259767).
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
Should always be a Unicode characterset (note 606359).
40. NOLOGGING indexes (Non-BI) - Number of Non-BI indexes defined with NOLOGGING
Can
require an index rebuild after a recovery (note 547464). BI indexes are
not listed here because it is common praxis to use NOLOGGING for that
indexes. The potential additional index rebuild after recovery is
usually accepted to have a better performance during normal operation.
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
Can
be critical because recovery of table data is no longer possible in
case of direct path operations (data loss!)
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
If
Oracle license is purchased via SAP additional users are only allowed
for administrative purposes (note 581312).
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
Not allowed (note 105047).
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
Only allowed in case of administrative triggers (note 105047).
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 few captured statements needed analysis data may be incomplete.
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
Should always be 0 (note 328785).
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
Often
caused by SQL statements that are running very long e.g. because they
are not executed optimally or because they have to wait until fetched
data is processed (note 185822).
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
Performance problems possible, needs to be cleaned (note 871455).
51. Outdated DBA_SEGMENTS definition used - DBA_SEGMENTS view exists with old definition (not taking into account "TYPE2 UNDO")
Performance problems possible, needs to be cleaned (note 871455).
52. Outdated PLAN_TABLE used
Problems
to explain statement possible. Drop the plan_table in that schemas
where it doesn´t have the column "FILTER_PREDICATES" and recreate it as
described in note 641630.
53. Outdated SYS_DBA_SEGS helper view used - Old SYS_DBA_SEGS_2 view still in place
Performance problems possible, needs to be cleaned (note 871455).
54. Outlines - Number of existing outlines
Should normally be avoided.
55. Parallel operations downgraded - Number of parallel executions (PX) that were performed with a reduced parallelism degree
Check PX configuration and reduce parallelism degrees if higher than necessary (note 651060).
56. PGA multipass executions - Number of workarea operations like sort, hash or bitmap that have to use PSAPTEMP intensively
Optimize PGA configuration and workarea operations (note 619876).
57. Protection mode - Dataguard standby database protection mode
"MAXIMUM
PROTECTION" or "MAXIMUM AVAILABILITY" can negatively impact "log file
sync" performance and should normally be avoided(note 105047)
58. PSAPTEMP type - Type of PSAPTEMP tablespace
Should usually be locally managed with temporary contents (LMTS/T) (note 659946).
59. QCM tables - Number of QCM tables
QCM
tables should only exist during a SAP table conversion. Relicts can be
responsible for follow up problems during online reorganizations or
system copies Existing QCM tables are usually a relict of previous
conversions. It has to be checked if they can be removed.
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
Should always be 0 (note 328785).
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
Usually
the recyclebin should not be used. Switching off the recyclebin does
not purge the objects. See note 838982 for further details.
62. Redo log mirroring not by Oracle
Independently
if hardware mirroring is used for the online redo logs or not software
mirroring by Oracle should be enabled to prevent from loosing the
current online redo logs completely (e.g. by deleting the content of
the current directory executed accidentally in the redo log directory).
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
Should only be activated if there is a good reason (note 1589924).
64. Reverse key indexes
Check if reverse key indexes are really on purpose and required.
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
No longer needed RUPD$ tables should be purged (note 741478).
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
Check if keep or recycle pool are really required (note 762808).
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
Segments can be pre-calculated using DBMS_SPACE_ADMIN functionality (note 871455).
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
Should
be avoided because it can result in wasted space if amount of data is
significantly smaller than initial extent size (note 666061).
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
Can result in reduced space utilization and should normally be avoided.
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 long snapshot intervals the granularity of historical data may not
be fine enough for an efficent analysis.
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 short snapshot retention historical data needed for an analysis may be not available anymore.
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
Can
result in unusual problems (e.g. during online reorganizations) and
increased redo log generation. Check if supplemental logging is really
required.
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)
Should only exist while an index is rebuilt online (note 641435).
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
High
number of partitions can result in different performance problems,
should be avoided for F fact tables (notes 722188, 590370).
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
Reason
for ANALYZE statistics needs to be eliminated and new DBMS_STATS
statistics need to be created (note 588668).
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
Should
not happen, because all indexes of a table should be located in the
same tablespace, indexes should be rebuilt so that all are in the same
tablespace.
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
Prevents DDL operations like CREATE INDEX (ORA-00069), can be enabled via ALTER TABLE # ENABLE TABLE LOCK.
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
Check if
histogram statistics are created on purpose. If not, create CBO
statistics without histograms (note 797629).
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
Reset parallelism to NOPARALLEL in order to avoid undesired parallel executions (note 651060).
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
Can result in chained rows and performance overhead and should normally be avoided
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
Can result in a high amount of unused space (only in case of Manual Segment Space Management).
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
Should normally not be required, can be disabled via ALTER TABLE ... NOCACHE.
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
Normal
for index organized tables and during online reorganizations: Should
be dropped if not required in order to avoid problems (like secondary
indexes used to enforce the 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
Statistics with a sufficient sample size should be created (note 588668).
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)
Create new CBO statistics (note 588668).
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
Reason for missing statistics needs to be eliminated and new statistics need to be created (note 588668).
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
Should not happen because an index rebuild can fail due to an overflow of the temporary tablespace.
88. Undo space errors - Number of space errors for undo tablespace in history
Either too much data is processed without COMMIT or the undo tablespace is defined too small (note 600141).
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
If
greater than 0, the UNUSABLE index partitions have to be rebuilt and
the root cause needs to be eliminated (note 618868).
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
If
greater than 0, the UNUSABLE indexes have to be rebuilt and the root
cause needs to be eliminated (note 618868).
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
If greater than 0, the UNUSABLE tables have to be dropped (note 1087203).
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
May be related to function based indexes or multi column statistics, has to be checked individually
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;
