Automatic Optimizer Statistics Collection
To check internal task details :
QUERY:
SELECT * FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME='auto optimizer stats collection';
OUTPUT:
auto optimizer stats collection ENABLED ORA$AUTOTASK OS ON BY DEFAULT, VOLATILE, SAFE TO KILL ORA$AT_WGRP_OS FALSE 0 0:0:7.166666667 0 0:0:4.966388889 36 18 18 0 0:2:58.79 0 0:2:58.79 0 0:2:38.0 0 0:2:38.0 0 7:59:59.972 0 7:59:59.972
Checking maintenance window opens and closes , We can modifies accordingly with date and time:
QUERY:
SELECT WINDOW_NAME, REPEAT_INTERVAL, DURATION, ENABLED FROM DBA_SCHEDULER_WINDOWS WHERE '"SYS"."' || WINDOW_NAME ||'"' IN (SELECT MEMBER_NAME FROM DBA_SCHEDULER_GROUP_MEMBERS WHERE GROUP_NAME='ORA$AT_WGRP_OS') ORDER BY NEXT_START_DATE;
OUTPUT:
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 0 20:0:0.0 TRUE
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 0 20:0:0.0 TRUE
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 0 4:0:0.0 TRUE
Scheduler job automatically created, runs, and dropped
QUERY:
SELECT TO_CHAR(LOG_DATE,'DD-MM-YYYY HH24:MI:SS') AS LOG_DATE, JOB_NAME, OPERATION, STATUS FROM DBA_SCHEDULER_JOB_LOG WHERE JOB_NAME LIKE 'ORA$AT%OPT%' ORDER BY LOG_DATE DESC;
OUTPUT:
07-05-2020 01:44:15 ORA$AT_OS_OPT_SY_90 RUN FAILED
07-05-2020 01:44:15 ORA$AT_OS_OPT_SY_90 COMPLETED
07-05-2020 01:44:15 ORA$AT_OS_OPT_SY_90 DROP
07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 ENABLE
07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 UPDATE
07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 UPDATE
07-05-2020 01:44:12 ORA$AT_OS_OPT_SY_90 UPDATE
07-05-2020 01:34:15 ORA$AT_OS_OPT_SY_88 DROP
07-05-2020 01:34:15 ORA$AT_OS_OPT_SY_88 COMPLETED
07-05-2020 01:34:15 ORA$AT_OS_OPT_SY_88 RUN FAILED
07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 UPDATE
07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 UPDATE
07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 ENABLE
07-05-2020 01:34:11 ORA$AT_OS_OPT_SY_88 UPDATE
07-05-2020 01:24:11 ORA$AT_OS_OPT_SY_86 RUN FAILED
07-05-2020 01:24:11 ORA$AT_OS_OPT_SY_86 COMPLETED
07-05-2020 01:24:11 ORA$AT_OS_OPT_SY_86 ENABLE
Objects affected by the job can be obtained
QUERY:
SELECT TARGET, TARGET_TYPE, START_TIME, END_TIME, SUBSTR(TO_CHAR(END_TIME-START_TIME,'HH:MI:SS'),12,8) DURATION, STATUS FROM DBA_OPTSTAT_OPERATION_TASKS ORDER BY OPID DESC, START_TIME DESC;
OUTPUT:
SOE.WAREHOUSES_PK INDEX 07-05-20 04:26:42.183000000 PM +05:30 07-05-20 04:26:42.183000000 PM +05:30 00:00:00 COMPLETED
SOE.WAREHOUSES TABLE 07-05-20 04:26:42.167000000 PM +05:30 07-05-20 04:26:42.183000000 PM +05:30 00:00:00 COMPLETED
SOE.WHS_LOCATION_IX INDEX 07-05-20 04:26:42.167000000 PM +05:30 07-05-20 04:26:42.183000000 PM +05:30 00:00:00 COMPLETED
SOE.V TABLE 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.167000000 PM +05:30 00:00:00 COMPLETED
SOE.PRODUCT_INFORMATION_PK INDEX 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED
SOE.PROD_CATEGORY_IX INDEX 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED
SOE.PROD_SUPPLIER_IX INDEX 07-05-20 04:26:42.152000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED
SOE.PROD_NAME_IX INDEX 07-05-20 04:26:42.136000000 PM +05:30 07-05-20 04:26:42.136000000 PM +05:30 00:00:00 COMPLETED
SOE.PRODUCT_INFORMATION TABLE 07-05-20 04:26:42.136000000 PM +05:30 07-05-20 04:26:42.152000000 PM +05:30 00:00:00 COMPLETED
SOE.PRD_DESC_PK INDEX 07-05-20 04:26:42.121000000 PM +05:30 07-05-20 04:26:42.136000000 PM +05:30 00:00:00 COMPLETED
SOE.PRODUCT_DESCRIPTIONS TABLE 07-05-20 04:26:42.105000000 PM +05:30 07-05-20 04:26:42.136000000 PM +05:30 00:00:00 COMPLETED
SOE.ITEM_PRODUCT_IX INDEX 07-05-20 04:26:41.652000000 PM +05:30 07-05-20 04:26:42.105000000 PM +05:30 00:00:00 COMPLETED
SOE.ITEM_ORDER_IX INDEX 07-05-20 04:26:41.605000000 PM +05:30 07-05-20 04:26:41.652000000 PM +05:30 00:00:00 COMPLETED
SOE.ORDER_ITEMS_PK INDEX 07-05-20 04:26:39.371000000 PM +05:30 07-05-20 04:26:41.605000000 PM +05:30 00:00:02 COMPLETED
Gathering Optimizer Statistics Manually
Examples
Gather statistics of all the objects in the database:
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS;
PL/SQL procedure successfully completed.
Gather statistics of all the schema objects :
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SOE');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SOE', CASCADE => TRUE);
PL/SQL procedure successfully completed.
Gather statistics of specific table:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SOE', 'ADDRESSES');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SOE', 'ADDRESSES',CASCADE=> TRUE);
PL/SQL procedure successfully completed.
Gather statistics for schemas dictionary and RDBMS components:
SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
No comments:
Post a Comment