advertisements
_____________________________________________________________________________________________________________________
There are 3 auto task maintenance jobs in Oracle and
they are listed below.
1. auto
optimizer stats collection
2. auto
space advisor
3. sql
tuning advisor
You can use below mentioned SQL to list the status of
the jobs.
select
client_name, status
from
dba_autotask_client
where
client_name in ( 'auto space advisor', 'auto optimizer stats collection','sql
tuning advisor');
CLIENT_NAME
STATUS
----------------------------------------------------------------
--------
auto optimizer
stats collection DISABLED
auto space
advisor
DISABLED
sql tuning
advisor
DISABLED
If you wanted to drop and recreate these jobs manually
you can use these steps.
Here is the query to list the group details for the
jobs.
SELECT
CLIENT_NAME,WINDOW_GROUP FROM DBA_AUTOTASK_CLIENT;
SQL
> /
CLIENT_NAME
--------------------------
WINDOW_GROUP
--------------------------
auto
optimizer stats collection
ORA$AT_WGRP_OS
auto
space advisor
ORA$AT_WGRP_SA
sql
tuning advisor
ORA$AT_WGRP_SQ
Here is the PL/SQL block to drop the
window group. Below one will drop Optimizer Stats group 'ORA$AT_WGRP_OS'.
Similarly you can use above window group for the respective auto task jobs
deletion.
BEGIN
DBMS_SCHEDULER.drop_window_group (
group_name => 'ORA$AT_WGRP_OS',
force
=> TRUE);
END;
/
If you wanted to schedule a job again
after the drop you can follow these steps. Mainly two things you have to take
care while creating the window and group member. First window resource_plan
must be 'DEFAULT_MAINTENANCE_PLAN'. Secondly the group_name should be 'MAINTENANCE_WINDOW_GROUP'.
BEGIN
dbms_scheduler.create_window(
window_name => 'SATURDAY_WINDOW',
duration =>
numtodsinterval(1, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval =>
'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=22;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
member => 'SATURDAY_WINDOW');
END;
/
PL/SQL
procedure successfully completed.
Now the all the jobs are disabled
status.
SQL
> select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR
, WINDOW_NEXT_TIME from DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME OPTIMIZE SEGMENT_ SQL_TUNE
WINDOW_NEXT_TIME
------------------
-------- -------- -------- ----------------------------------------
SATURDAY_WINDOW DISABLED DISABLED DISABLED 11-JUL-15
10.00.00.000000 PM PST8PDT
Subsequently you have to create
another window group with respect to each jobs as mentioned below.
BEGIN
DBMS_SCHEDULER.CREATE_window_group (
group_name
=> 'ORA$AT_WGRP_SQ',
window_list => 'SATURDAY_WINDOW',
comments
=> 'sql tuning advisor');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_window_group (
group_name
=> 'ORA$AT_WGRP_OS',
window_list => 'SATURDAY_WINDOW',
comments
=> 'auto optimizer stats collection');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_window_group (
group_name
=> 'ORA$AT_WGRP_SA',
window_list => 'SATURDAY_WINDOW',
comments
=> 'auto space advisor');
END;
/
Next enable the jobs using following
commands.
BEGIN
dbms_auto_task_admin.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
BEGIN
dbms_auto_task_admin.enable(
client_name => 'auto optimizer stats
collection',
operation
=> NULL,
window_name => NULL);
END;
/
BEGIN
dbms_auto_task_admin.enable(
client_name => 'auto space advisor',
operation
=> NULL,
window_name => NULL);
END;
/
select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR
, WINDOW_NEXT_TIME from DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME OPTIMIZE SEGMENT_ SQL_TUNE
WINDOW_NEXT_TIME
------------------
-------- -------- -------- ----------------------------------------
SATURDAY_WINDOW ENABLED
ENABLED ENABLED 11-JUL-15 10.00.00.000000 PM PST8PDT
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment