統計情報

sqlplus / as sysdba

alter session set container=retpdb;

sqlplus aaa/aaa@192.168.100.113:1521/RETPDB.RETDOMAIN

set linesize 30000

set pagesize 50000

select dbms_stats.get_stats_history_availability from dual;

select table_name,stats_update_time from dba_tab_stats_history;

col JOB_NAME for a50

col PROGRAM_NAME for a50

col SCHEDULE_TYPE for a50

exec dbms_stats.gather_schema_stats(ownname => 'SYS',OPTIONS =>'GATHER');

with test as (select to_char(STATS_UPDATE_TIME, 'YYYY-MM-DD') date1 from dba_tab_stats_history)

select date1,count(date1) from test group by date1 order by date1;

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(0);

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

select * from dba_tab_stats_history where table_name = 'AAA';

select JOB_NAME,PROGRAM_NAME,SCHEDULE_TYPE from all_scheduler_jobs;

set linesize 30000

col LOG_DATE for a40

col JOB_NAME for a40

col JOB_CLASS for a40

col OPERATION for a10

select LOG_DATE,JOB_NAME,JOB_CLASS,OPERATION from dba_SCHEDULER_JOB_LOG order by LOG_DATE ;

select client_name,status from dba_autotask_client order by client_name;

col window_name for a20

select window_name, enabled,active,con_id from cdb_scheduler_windows;

col table_name for a40

col stats_update_time for a40

select table_name,stats_update_time from dba_tab_stats_history order by stats_update_time;

select client_name,status from dba_autotask_client order by client_name;

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => NULL);

END;

/

select client_name,status from dba_autotask_client order by client_name;

select WINDOW_NAME,ACTUAL_START_DATE,ACTUAL_DURATION from ALL_SCHEDULER_WINDOW_DETAILS;

begin

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'MONDAY_WINDOW', attribute => 'start_date', value => '20-03-02 22:00:00.000000 ASIA/TOKYO');

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'TUESDAY_WINDOW', attribute => 'start_date', value => '20-03-03 22:00:00.000000 ASIA/TOKYO');

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'WEDNESDAY_WINDOW', attribute => 'start_date', value => '20-03-04 22:00:00.000000 ASIA/TOKYO');

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'THURSDAY_WINDOW', attribute => 'start_date', value => '20-03-05 22:00:00.000000 ASIA/TOKYO');

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'FRIDAY_WINDOW', attribute => 'start_date', value => '20-02-28 22:00:00.000000 ASIA/TOKYO');

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'SATURDAY_WINDOW', attribute => 'start_date', value => '20-02-29 06:00:00.000000 ASIA/TOKYO');

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'SUNDAY_WINDOW', attribute => 'start_date', value => '20-03-01 06:00:00.000000 ASIA/TOKYO');

end;

/

col WINDOW_NAME for a30

col NEXT_START_DATE for a40

select WINDOW_NAME,NEXT_START_DATE from DBA_SCHEDULER_WINDOWS;