Please log in to the Grid Control For the Online Weekly Taks
Please log in to the repository by using the sqlplus utility, or isqlplus as a sysman user for the Offline Montly Tasks
While doing offline montly Task make sure that your OMS and Agent are down.(Its Mandatory)
Sqlplus sysman/xxxx@aliasname
The following query gives a rough idea of the tables that may require rebuild/reorganization:
SELECT UT.TABLE_NAME, ROUND(UT.NUM_ROWS * UT.AVG_ROW_LEN / 1024 / 1024, 2) “CALCULATED SIZE MB”,
ROUND(US.BYTES / 1024 /1024,2) “ALLOCATED SIZE MB”,
ROUND(US.BYTES / (UT.NUM_ROWS * UT.AVG_ROW_LEN),2) “TIMES LARGER”
FROM USER_TABLES UT, USER_SEGMENTS US
WHERE (UT.NUM_ROWS > 0 AND UT.AVG_ROW_LEN > 0 AND US.BYTES > 0)
AND UT.PARTITIONED = ‘NO’
AND UT.IOT_TYPE IS NULL
AND UT.IOT_NAME IS NULL
AND UT.TABLE_NAME = US.SEGMENT_NAME
AND ROUND(US.BYTES / 1024 /1024,2) > 5
AND ROUND(US.BYTES / 1024 /1024,2) > (ROUND(UT.NUM_ROWS * UT.AVG_ROW_LEN / 1024 / 1024, 2)* 2)
ORDER BY 4 DESC;
*Onine Weekly Tasks
-> Check the System error page and resolve the causes of all errors. Some may be related to product bugs,but resolve as many as you can. Look for applicable patches if you suspect a bug. Clear the error table from the EM UI when you are done or when you have resolved all that you can.
-> Check the alerts/errors for any metric collection errors. Most of these are going to be due to configuration issues at the target being monitored. Resolve these errors by fixing the reported problem. The error should then clear automatically.
->Try to resolve any open alerts in the system. Also, if there are severities that are frequently oscillating between clear and warning/critical, try adjusting the threshold(s) to stop frequent warning and critical alert conditions. Frequent alert oscillation can add significant load at the Management Server. Adjusting the threshold(s) to a more reasonable level will keep the “noise” level down from Enterprise Manager, making it work more efficiently for you. Adjusting the threshold for an alert may be the only way to close it. This is perfectly acceptable in cases where the tolerances are too tight for a metric.
-> Watch the Alert Log error metric for the repository database for critical (ORA-0600, etc) errors. Resolve these as soon as possible. A search on Metalink using the error details almost always will reveal some clues to its cause and provide available patches.
->Analyze the “big 3” tables in the repository: MGMT_METRICS_RAW, MGMT_METRICS_1HOUR, and MGMT_METRICS_1DAY. If your Management Repository is in a 10g database, then these tables are automatically analyzed weekly and you can skip this task. If your Management Repository is in an Oracle version 9 database, then you will need to ensure that the following commands are run weekly:
Enterprise Manager Grid Control Performance Best Practices Page 11:
exec dbms_stats.gather_table_stats(‘SYSMAN’, ‘MGMT_METRICS_RAW’, null, .000001, false, ‘for all indexed columns’, null, ‘global’, true, null, null, null);
exec dbms_stats.gather_table_stats(‘SYSMAN’, ‘MGMT_METRICS_1HOUR’, null, .000001, false, ‘for all indexed columns’, null, ‘global’, true, null, null, null);
exec dbms_stats.gather_table_stats(‘SYSMAN’, ‘MGMT_METRICS_1DAY’, null, .000001, false, ‘for all indexed columns’, null, ‘global’, true, null, null, null);
Offline Monthly Tasks (Management Server(s) down):
The following query gives a rough idea of the tables that may require rebuild/reorganization:
SELECT UT.TABLE_NAME, ROUND(UT.NUM_ROWS * UT.AVG_ROW_LEN / 1024 / 1024, 2) “CALCULATED SIZE MB”,
ROUND(US.BYTES / 1024 /1024,2) “ALLOCATED SIZE MB”,
ROUND(US.BYTES / (UT.NUM_ROWS * UT.AVG_ROW_LEN),2) “TIMES LARGER”
FROM USER_TABLES UT, USER_SEGMENTS US
WHERE (UT.NUM_ROWS > 0 AND UT.AVG_ROW_LEN > 0 AND US.BYTES > 0)
AND UT.PARTITIONED = ‘NO’
AND UT.IOT_TYPE IS NULL
AND UT.IOT_NAME IS NULL
AND UT.TABLE_NAME = US.SEGMENT_NAME
AND ROUND(US.BYTES / 1024 /1024,2) > 5
AND ROUND(US.BYTES / 1024 /1024,2) > (ROUND(UT.NUM_ROWS * UT.AVG_ROW_LEN / 1024 / 1024, 2)* 2)
ORDER BY 4 DESC;
Note:415025.1
Related posts:
