OraCsdP: Statistics

0] Statictics Control are they up to date ?

Optimizer Statictics

Dictionnary Statictics

Automatic Statistics Gathering job , see below

Note 1 Fixed objects Statictics

should be gathered one-shot at peak load

Note 2 System

Default stats are recommended.

Problem I got with Oracle 12C 12.1.0.2

0.1 Control of the Optimizer stats quality :

Find STALE or MISSING Statistics

select m.TABLE_OWNER,

'NO PART' as IF_PARTITION_NAME,

m.TABLE_NAME as NAME,

m.INSERTS,

m.UPDATES,

m.DELETES,

m.TRUNCATED, -- Indicates whether the table has been truncated since the last analyze (YES) or not (NO)

m.TIMESTAMP as LAST_MODIFIED,

round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,

t.num_rows as last_known_rows_number,

t.last_analyzed,

a.stale_stats

From dba_tab_modifications m, dba_tables t, dba_tab_statistics a

where a.owner = t.owner and a.table_name = t.table_name

and m.table_owner=t.owner and m.table_name=t.table_name and t.secondary = 'N' and stattype_locked is NULL -- DATA/CACHE/ALL

and m.table_owner not in ('SYS','SYSTEM','ANONYMOUS', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSNMP', 'DIP', 'DVF', 'DVSYS', 'FLOWS_FILES', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER',

'LBACSYS', 'MDDATA', 'MDSYS', 'OJVMSYS', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'SYSBACKUP', 'SYSDG', 'SYSKM',

'WMSYS', 'XDB', 'XGE', 'XS$NULL')

and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)

and not exists ( select '1' from dba_mview_logs b where m.table_owner = b.log_owner and m.table_name = b.log_table ) -- avoid materialized view log table

union

select m.TABLE_OWNER,

p.TABLE_NAME as IF_PARTITION_NAME,

m.PARTITION_NAME as NAME,

m.INSERTS,

m.UPDATES,

m.DELETES,

m.TRUNCATED,

m.TIMESTAMP as LAST_MODIFIED,

round((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0),2) as EST_PCT_MODIFIED,

p.num_rows as last_known_rows_number,

p.last_analyzed,

a.stale_stats

From dba_tab_modifications m, dba_tab_partitions p, dba_tab_statistics a

where a.owner = p.table_owner and a.table_name = p.table_name and stattype_locked is NULL -- DATA/CACHE/ALL

and m.table_owner=p.table_owner and m.table_name=p.table_name

and m.PARTITION_NAME = p.PARTITION_NAME and p.table_name not like 'BIN$%'

'WMSYS', 'XDB', 'XGE', 'XS$NULL')

and ((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0) > 10 or p.last_analyzed is null)

order by 8 desc;

n secondary = yes : Indicates whether the table is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N)

Ran on the 10/03/2019

TABLE_OWNER	IF_PARTITION_NAME	NAME	INSERTS	UPDATES	DELETES	TRUNCATED	LAST_MODIFIED	EST_PCT_MODIFIED	LAST_KNOWN_ROWS_NUMBER	LAST_ANALYZED	STALE_STATS
BI_42	NO PART	CMS_INFOOBJECTS7	691	68994	1148	NO	03/10/2019 07:52	126,3	56084	30/09/2019 07:44	YES
BI_42_AUDIT	NO PART	ADS_AUDITEE	0	1655005	0	NO	03/10/2019 07:52	840104,06	197	30/09/2019 07:44	YES
BI_42_AUDIT	NO PART	ADS_CLUSTER	0	52470	0	NO	03/10/2019 07:52	2623500	2	30/09/2019 07:44	YES
APP1	NO PART	INSTI_USER	12497	0	0	NO	02/10/2019 22:33
APP2	NO PART	INSTI_PUBLIC	1178	0	0	NO	02/10/2019 22:24

So there are STALE and MISSING STATS, even whith the 12C Automatic Statistics Gathering job !

Let’s check the Automatic feature, is it correctly running :

select client_name, status, window_group from dba_autotask_client;

CLIENT_NAME STATUS WINDOW_GROUP

auto space advisor ENABLED ORA$AT_WGRP_SA

auto optimizer stats collection ENABLED ORA$AT_WGRP_OS

sql tuning advisor ENABLED ORA$AT_WGRP_SQ

Parameters

select 'GRANULARITY' as parameter,dbms_stats.get_prefs('GRANULARITY') as value from dual

UNION

select 'CASCADE' as parameter,dbms_stats.get_prefs('CASCADE') as value from dual

UNION

select 'DEGREE' as parameter,dbms_stats.get_prefs('DEGREE') as value from dual

UNION

select 'ESTIMATE_PERCENT' as parameter,dbms_stats.get_prefs('ESTIMATE_PERCENT') as value from dual

UNION

select 'METHOD_OPT' as parameter,dbms_stats.get_prefs('METHOD_OPT') as value from dual

UNION

select 'CASCADE' as parameter,dbms_stats.get_prefs('CASCADE') as value from dual

UNION

select 'NO_INVALIDATE' as parameter,dbms_stats.get_prefs('NO_INVALIDATE') as value from dual

UNION

select 'PUBLISH' as parameter,dbms_stats.get_prefs('PUBLISH') as value from dual

UNION

select 'INCREMENTAL' as parameter,dbms_stats.get_prefs('INCREMENTAL') as value from dual

UNION

select 'STALE_PERCENT' as parameter,dbms_stats.get_prefs('STALE_PERCENT') as value from dual;

PARAMETER	VALUE
CASCADE	DBMS_STATS.AUTO_CASCADE
DEGREE	NULL
ESTIMATE_PERCENT	DBMS_STATS.AUTO_SAMPLE_SIZE
GRANULARITY	AUTO
INCREMENTAL	FALSE
METHOD_OPT	FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE	DBMS_STATS.AUTO_INVALIDATE
PUBLISH	TRUE
STALE_PERCENT	10

select * from DBA_AUTOTASK_JOB_HISTORY where client_name = 'auto optimizer stats collection' order by job_start_time desc

CLIENT_NAME	WINDOW_NAME	WINDOW_START_TIME	WINDOW_DURATION	JOB_NAME	JOB_STATUS	JOB_START_TIME	JOB_DURATION	JOB_ERROR	JOB_INFO

auto optimizer stats collection	WEDNESDAY_WINDOW	02/10/2019 22:00:01,831533 +02:00	+00 04:00:00.104691	ORA$AT_OS_OPT_SY_12461	SUCCEEDED	02/10/2019 22:00:04,033617 +02:00	+00 00:00:03.000000	0
auto optimizer stats collection	TUESDAY_WINDOW	01/10/2019 22:00:01,315360 +02:00	+00 04:00:00.077451	ORA$AT_OS_OPT_SY_12441	SUCCEEDED	01/10/2019 22:00:03,662334 +02:00	+00 00:00:05.000000	0
auto optimizer stats collection	MONDAY_WINDOW	30/09/2019 22:00:00,692265 +02:00	+00 04:00:00.036978	ORA$AT_OS_OPT_SY_12421	SUCCEEDED	30/09/2019 22:00:02,973274 +02:00	+00 00:00:03.000000	0

Since it’s working normally, I decided to disable this automatic feature and install my own Statistics gathering, a better

way than Oracle installed it !!

0.2 Disable the 'auto optimizer stats collection'

Connect as SYS and EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('auto optimizer stats collection', NULL, NULL);

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 3 08:45:28 2019

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('auto optimizer stats collection', NULL, NULL);

PL/SQL procedure successfully completed.

select client_name, status, window_group from dba_autotask_client;

CLIENT_NAME STATUS WINDOW_GROUP

auto space advisor ENABLED ORA$AT_WGRP_SA

auto optimizer stats collection DISABLED ORA$AT_WGRP_OS

sql tuning advisor ENABLED ORA$AT_WGRP_SQ

0.3 Install custom Job

Install new Job with

exec DBMS_STATS.GATHER_DATABASE_STATS ( estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,block_sample=>null,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',degree=>2,granularity=>'ALL',cascade=>TRUE,options=>'GATHER AUTO',gather_sys=>TRUE) ;

GATHER AUTO: Gathers all necessary statistics automatically.

Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics.

When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored.

exec dbms_scheduler.drop_job('CD13_GATHER_DATABASE_STATS_DD') ;

BEGIN

SYS.DBMS_SCHEDULER.CREATE_JOB

(

job_name => 'CD13_GATHER_DATABASE_STATS_DD'

,start_date => TO_TIMESTAMP_TZ('2019/10/03 05:00:00.000000 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')

,repeat_interval => 'FREQ=DAILY'

,end_date => NULL

,job_class => 'DEFAULT_JOB_CLASS'

,job_type => 'PLSQL_BLOCK'

,job_action => 'BEGIN DBMS_STATS.GATHER_DATABASE_STATS ( estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,block_sample=>null,method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'',degree=>2,granularity=>''ALL'',cascade=>TRUE,options=>''GATHER AUTO'',gather_sys=>TRUE) ; END;'

,comments => 'AUC 2019103: auto optimizer stats collection replacement'

);

END ;

exec DBMS_SCHEDULER.ENABLE('CD13_GATHER_DATABASE_STATS_DD') ;

0.4 Check after the job execution

The query 0.1 returns no rows OK.

Note : find Locked Statistics

select * from dba_tab_statistics where stattype_locked = 'ALL'

and owner not in ('SYS','SYSTEM','ANONYMOUS', 'APEX_040200', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSNMP', 'DIP', 'DVF', 'DVSYS', 'FLOWS_FILES', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER',

'WMSYS', 'XDB', 'XGE', 'XS$NULL')

order by 1,2

select log_date , job_name , status, run_duration from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name = 'CD13_GATHER_DATABASE_STATS_DD' order by log_date desc

LOG_DATE JOB_NAME STATUS RUN_DURATION

07/10/2019 06:03:18,052543 +02:00 CD13_GATHER_DATABASE_STATS_DD SUCCEEDED +00 00:03:15.000000

06/10/2019 06:10:52,129637 +02:00 CD13_GATHER_DATABASE_STATS_DD SUCCEEDED +00 00:10:49.000000

05/10/2019 06:14:05,209342 +02:00 CD13_GATHER_DATABASE_STATS_DD SUCCEEDED +00 00:14:02.000000

04/10/2019 07:11:06,811329 +02:00 CD13_GATHER_DATABASE_STATS_DD SUCCEEDED +00 01:11:03.000000

First execution takes a longer duration.

1] Optimizer Statistics Gathering

1.1 Table

exec DBMS_STATS.GATHER_TABLE_STATS ( 'IOW_OPA_DTM','sgl_dtm_dim_personne',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

select table_name , last_analyzed from user_tables where lower(table_name) in ('zy00','zy15','zx8k','zx5v') order by 2 desc

1.2 Index

exec DBMS_STATS.GATHER_INDEX_STATS( 'SAFRAN_DM','IDX_XXGL_INT_OBS_TO_SFR_ARCH',DBMS_STATS.AUTO_SAMPLE_SIZE,4,'ALL' ) ;

1.3 Schema

exec DBMS_STATS.GATHER_SCHEMA_STATS ( 'SPE5',DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

1.4 Database

exec dbms_stats.gather_database_stats( estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,block_sample=>FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO', degree=>4,granularity=>'ALL',cascade=>TRUE,options=>'GATHER',gather_sys=>TRUE) ;

2] Modify Statistics

2.1 Restore

Table

exec DBMS_STATS.RESTORE_TABLE_STATS( ownname => 'HR', tabname => 'ZX5V', as_of_timestamp => to_timestamp('25-11-2011 06:00:00','DD-MM-YYYY hh24:mi:ss'), no_invalidate => FALSE ) ;

Schema

Under SYS or SYSTEM

exec DBMS_STATS.RESTORE_SCHEMA_STATS( ownname => 'IOW_OIN_ODS', as_of_timestamp => to_timestamp('07-11-2012 11:00:00','DD-MM-YYYY hh24:mi:ss'), no_invalidate => FALSE ) ;

2.2 Set

Table

exec dbms_stats.set_table_stats( ownname => 'SCH01', tabname => 'TEMP_PREST_TABLE', stattab => null, statid => null, numrows => 14510, numblks => 50, avgrlen => 100);

If they are locked : exec dbms_stats.set_table_stats( ownname => 'SCH01', tabname => 'TEMP_PREST_TABLE', stattab => null, statid => null, numrows => 14510, numblks => 50, avgrlen => 100 , force=> true );

Table Partition

exec dbms_stats.set_table_stats( ownname => 'OWNER1', tabname => 'PT', partname => 'P1_2013', stattab => null, statid => null, numrows => 3000000, numblks => 50000, avgrlen => 100);

2.3 Lock

Table

exec dbms_stats.lock_table_stats( ownname => 'OWNER1', tabname => 'COMPTJ');

exec dbms_stats.unlock_table_stats( ownname => 'SCH01', tabname => 'TEMP_PREST_TABLE');

exec DBMS_STATS.GATHER_TABLE_STATS ( 'SCH01','TEMP_PREST_TABLE',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

exec dbms_stats.lock_table_stats( ownname => 'SCH01', tabname => 'TEMP_PREST_TABLE');

-- shows when stats is locked the value of stattype_locked is ALL

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEMP_PREST_TABLE' and owner = 'SCH01';

Schema

exec DBMS_STATS.LOCK_SCHEMA_STATS('IOW_OIN_ODS');

exec DBMS_STATS.UNLOCK_SCHEMA_STATS('IOW_OIN_ODS');

oracle how to see if table statistics are locked

select stattype_locked from dba_tab_statistics where table_name = 'TABLE1'-- where stattype_locked != 'TRUE'

exec dbms_stats.unlock_schema_stats('SCHEMA');

3] Copy from one Database to Another

3.1 Copy from one Database to Another

Schema Level Stats : Export/Import Stats from 10g to 11g

[10g] Export Stats

under sys

exec dbms_stats.create_stat_table ( ownname => 'SYSTEM', stattab => 'IOW_HHR_stats', tblspace => 'SYSAUX' );

exec dbms_stats.export_schema_stats ( ownname => 'IOW_HHR', stattab => 'IOW_HHR_stats', statown => 'SYSTEM' );

exec dbms_stats.create_stat_table ( ownname => 'SYSTEM', stattab => 'USR1_stats', tblspace => 'SYSAUX' );

exec dbms_stats.export_schema_stats ( ownname => 'USR1', stattab => 'USR1_stats', statown => 'SYSTEM' );

$ expdp system directory=DB01_DP_DIR dumpfile=iow_hhr_stats.dmp tables=IOW_HHR_stats,USR1_stats

Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 08 November, 2012 8:49:29

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=DB01_DP_DIR dumpfile=iow_hhr_stats.dmp tables=IOW_HHR_stats,USR1_stats

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 2.375 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

. . exported "SYSTEM"."USR1_STATS" 1.282 MB 11009 rows

. . exported "SYSTEM"."IOW_HHR_STATS" 310.1 KB 2511 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

/WORK_EXP/oracle/DB01/iow_hhr_stats.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 08:49:42

exec dbms_stats.drop_stat_table ( ownname => 'SYSTEM', stattab => 'USR1_stats');

[11g] Import

$ impdp system directory=DB01_DP_DIR dumpfile=iow_hhr_stats.dmp

Import: Release 11.2.0.2.0 - Production on Thu Nov 8 09:29:26 2012

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=DB01_DP_DIR dumpfile=iow_hhr_stats.dmp

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SYSTEM"."USR1_STATS" 1.282 MB 11009 rows

. . imported "SYSTEM"."IOW_HHR_STATS" 310.1 KB 2511 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 09:29:52

under sys: delete stats, upgrade stats to 11G version ( to avoid error below ), import & lock

exec dbms_stats.delete_schema_stats ( ownname => 'USR1' );

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYSTEM','USR1_stats') ;

exec dbms_stats.import_schema_stats ( ownname => 'USR1', stattab => 'USR1_stats', statown => 'SYSTEM' );

exec DBMS_STATS.LOCK_SCHEMA_STATS('USR1');

Error when Upgrade is not done :

ORA-20002 on Importing Statistics using DBMS_STATS Procedures [ID 740345.1]

BEGIN dbms_stats.import_schema_stats ( ownname => 'IOW_OIN_ODS', stattab => 'IOW_OIN_ODS', statown => 'SYSTEM' ); END;

ERROR at line 1:

ORA-20002: Version of statistics table SYSTEM.IOW_OIN_ODS is too old. Please

try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11271

ORA-06512: at "SYS.DBMS_STATS", line 11288

ORA-06512: at "SYS.DBMS_STATS", line 12420

ORA-06512: at line 1

4] How to modify Automatic Statistics Gathering

4.1 [10G] Example : Add another daily schedule

col REPEAT_INTERVAL for a80

col DURATION for a30

SQL> select window_name, repeat_interval, duration from dba_scheduler_windows where window_name like 'WEEK%';

WINDOW_NAME REPEAT_INTERVAL DURATION

------------------------------ ---------------------------------------------------------------WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00

SQL> BEGIN

2 dbms_scheduler.disable('WEEKNIGHT_WINDOW');

3 DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON,TUE,WED,THU,FRI,SAT;byhour=5,21;byminute=0; bysecond=0');

4 dbms_scheduler.enable('WEEKNIGHT_WINDOW');

5 END;

6 /

PL/SQL procedure successfully completed.

col REPEAT_INTERVAL for a80

col DURATION for a30

SQL> select window_name, repeat_interval, duration from dba_scheduler_windows where window_name like 'WEEK%';

WINDOW_NAME REPEAT_INTERVAL DURATION

------------------------------ ---------------------------------------------------------------

WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI,SAT;byhour=6,22;byminute=0; bysecond=0 +000 08:00:00

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00

SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

STATE

---------------

SCHEDULED

4.2 [10G] Example2 : Disable WEEKEND_WINDOW

SQL> select window_name, repeat_interval, duration, enabled , active from dba_scheduler_windows where window_name like 'WEEK%';

WINDOW_NAME REPEAT_INTERVAL DURATION ENABL ACTIV

------------------------------ ---------------------------------------------------------------WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI,SAT;byhour=5,21;byminute=0; bysecond=0 +000 08:00:00 TRUE TRUE

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 TRUE FALSE

BEGIN

dbms_scheduler.disable('WEEKEND_WINDOW');

END;

PL/SQL procedure successfully completed.

SQL> select window_name, repeat_interval, duration, enabled , active from dba_scheduler_windows where window_name like 'WEEK%';

WINDOW_NAME REPEAT_INTERVAL DURATION ENABL ACTIV

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 FALSE FALSE

4.3 [10G] Example3 : Disable SATURDAY Gathering

-- disable Saturday Gather Stats

-- 11g they are now executed as "autotasks" http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/mgmt_db.htm#CNCPT1375

BEGIN

DBMS_AUTO_TASK_ADMIN.disable(

client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => 'SATURDAY_WINDOW');

END;

select * from DBA_AUTOTASK_WINDOW_CLIENTS

WINDOW_NAME	WINDOW_NEXT_TIME	WINDOW_ACTIVE	AUTOTASK_STATUS	OPTIMIZER_STATS	SEGMENT_ADVISOR	SQL_TUNE_ADVISOR	HEALTH_MONITOR
MONDAY_WINDOW	24/02/2014 22:00:00,000000 +01:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED	DISABLED
TUESDAY_WINDOW	25/02/2014 22:00:00,000000 +01:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED	DISABLED
WEDNESDAY_WINDOW	26/02/2014 22:00:00,000000 +01:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED	DISABLED
THURSDAY_WINDOW	20/02/2014 22:00:00,000000 +01:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED	DISABLED
FRIDAY_WINDOW	21/02/2014 22:00:00,000000 +01:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED	DISABLED
SATURDAY_WINDOW	22/02/2014 06:00:00,000000 +01:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED	DISABLED
SUNDAY_WINDOW	23/02/2014 06:00:00,000000 +01:00	FALSE	ENABLED	ENABLED	ENABLED	ENABLED	DISABLED

WINDOW_NAME	WINDOW_NEXT_TIME	WINDOW_ACTIVE	AUTOTASK_STATUS	OPTIMIZER_STATS	SEGMENT_ADVISOR	SQL_TUNE_ADVISOR	HEALTH_MONITOR
SATURDAY_WINDOW	22/02/2014 06:00:00,000000 +01:00	FALSE	ENABLED	DISABLED	ENABLED	ENABLED	DISABLED

4.4 [11G] Change Scheduled Running Hour

select client_name , status from DBA_AUTOTASK_CLIENT where client_name = 'auto optimizer stats collection'

-- auto optimizer stats collection ENABLED

BEGIN

DBMS_SCHEDULER.disable(name => 'SYS.MONDAY_WINDOW', force => TRUE);

DBMS_SCHEDULER.set_attribute(

name => 'SYS.MONDAY_WINDOW',

attribute => 'repeat_interval',

value => 'freq=daily;byday=MON;byhour=07;byminute=0; bysecond=0') ;

DBMS_SCHEDULER.enable(name=>'SYS.MONDAY_WINDOW');

END;

OraCsdP

Categories

mardi 2 juillet 2013

Statistics