溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

行業(yè)客戶 12c 何時(shí)自動(dòng)收集統(tǒng)計(jì)信息？

發(fā)布時(shí)間：2020-07-27 03:06:52 來(lái)源：網(wǎng)絡(luò) 閱讀：1146 作者：roidba 欄目：關(guān)系型數(shù)據(jù)庫(kù)

每個(gè)行業(yè)都有自己的特點(diǎn)，比如金融行業(yè)，每天晚上10點(diǎn)左右會(huì)跑批，而自動(dòng)收集統(tǒng)計(jì)信息默認(rèn)周一到周五晚上10點(diǎn)，持續(xù)4個(gè)小時(shí)收集統(tǒng)計(jì)信息，周六周日每天6點(diǎn)，持續(xù)20個(gè)小時(shí)收集統(tǒng)計(jì)信息。顯然我們應(yīng)該設(shè)計(jì)一個(gè)更加合適的時(shí)間點(diǎn)來(lái)收集統(tǒng)計(jì)信息。

這里演示每天凌晨1點(diǎn)，持續(xù)5個(gè)小時(shí)收集統(tǒng)計(jì)信息的方法。

1.查看版本
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> 
--默認(rèn)值如下
set lines 200
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a60 
col DURATION  for a20
SELECT w.window_name,
               w.repeat_interval,
               w.duration,
               w.enabled
          FROM dba_autotask_window_clients c, dba_scheduler_windows w
         WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';

SQL> set lines 200
SQL> col WINDOW_NAME for a20
SQL> col REPEAT_INTERVAL for a60 
SQL> col DURATION  for a20
SQL> SELECT w.window_name,
  2                 w.repeat_interval,
  3                 w.duration,
  4                 w.enabled
  5            FROM dba_autotask_window_clients c, dba_scheduler_windows w
  6           WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';

WINDOW_NAME          REPEAT_INTERVAL                                              DURATION             ENABL
-------------------- ------------------------------------------------------------ -------------------- -----
TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
FRIDAY_WINDOW        FREQ=daily;BYDAY=FRI;BYHOUR=22;BYMINUTE=30;BYSECOND=0        +000 04:00:00        TRUE
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00        TRUE
SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00        TRUE

6 rows selected.
--修改自動(dòng)收集統(tǒng)計(jì)信息每天凌晨一點(diǎn)，持續(xù)5個(gè)小時(shí)。
    --MONDAY
    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."MONDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=MON;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."MONDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /
    --TUESDAY
    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."TUESDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=TUE;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."TUESDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /

     --WEDNESDAY
     BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."WEDNESDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=WED;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."WEDNESDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /

     --THURSDAY
      BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."THURSDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=THU;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."THURSDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /

    --FRIDAY
     BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."FRIDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=FRI;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."FRIDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /
    --SATURDAY
    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."SATURDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=SAT;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."SATURDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /

    --SUNDAY
    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."SUNDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=SUN;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."SUNDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /
SQL> --驗(yàn)證是否變更
SQL> set lines 200
SQL> col WINDOW_NAME for a20
SQL> col REPEAT_INTERVAL for a60 
SQL> col DURATION  for a20
SQL> SELECT w.window_name,
  2                 w.repeat_interval,
  3                 w.duration,
  4                 w.enabled
  5            FROM dba_autotask_window_clients c, dba_scheduler_windows w
  6           WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';

WINDOW_NAME          REPEAT_INTERVAL                                              DURATION             ENABL
-------------------- ------------------------------------------------------------ -------------------- -----
MONDAY_WINDOW        FREQ=daily;BYDAY=MON;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
TUESDAY_WINDOW       FREQ=daily;BYDAY=TUE;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
WEDNESDAY_WINDOW     FREQ=daily;BYDAY=WED;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
THURSDAY_WINDOW      FREQ=daily;BYDAY=THU;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
FRIDAY_WINDOW        FREQ=daily;BYDAY=FRI;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
SATURDAY_WINDOW      FREQ=daily;BYDAY=SAT;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
SUNDAY_WINDOW        FREQ=daily;BYDAY=SUN;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE

7 rows selected.

SQL>

看到太多的客戶都是默認(rèn)值，當(dāng)然對(duì)非7*24小時(shí)的系統(tǒng)也沒(méi)有太大影響。但是，某些7X24小時(shí)的業(yè)務(wù)系統(tǒng)，我們需要做更多的優(yōu)化調(diào)整，然后持續(xù)跟蹤，不斷優(yōu)化。保證業(yè)務(wù)的連續(xù)性。不管大家有沒(méi)有設(shè)置，都需要大家知道這里可能會(huì)引起性能問(wèn)題的一個(gè)點(diǎn)，我們必須考慮到。

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手