oracle dbms_stat與analyze 獲取有效的統計資訊(4)

#  設定dbms_stats
10g:可修改cascade,estimate_percent,degree,method_opt,no_invalidate,granularity 用set_param修改這些可以（需要analyze any dictionary&analyze any許可權）
get_param獲取 當前值

SQL> select dbms_stats.get_param('CASCADE') from dual;

DBMS_STATS.GET_PARAM('CASCADE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE

#直接獲取這些引數的值
SELECT sname AS parameter, nvl(spare4,sval1) AS default_value
FROM sys.optstat_hist_control$
WHERE sname IN ('CASCADE','ESTIMATE_PERCENT','DEGREE',
'METHOD_OPT','NO_INVALIDATE','GRANULARITY');

11g:對dbms_stats的配置有了改進,default=首選項(preference),另外可在schema,表級設定(10g是在global 設定),如果使用get_param(set/reset_param_default)都將廢棄（需要analyze any dictionary&analyze any許可權）

11g可以修改cascade,estimate_percent,degree,method_opt,no_invalidate,granularity,publish,increment,statle_percent的default值
1.set_global_prefs（代替set_param，自己建立的和資料字典都行）
2.set_database_prefs（僅僅用於自己建的object,資料字典不行）
3.set_schema_prefs（schema的首選項）
4.set_table_prefs(表級)

#在使用set_database_prefs&set_schema_prefs時候其中的所有物件都轉成表級別的（既 他們只是呼叫set_table_prefs 對其中的object設,但這也表示新建立的object會採用global選項）
#表級最高 有表級使用表級，沒有才用global級別
#查詢global級別設定
SELECT sname AS parameter, nvl(spare4,sval1) AS default_value
FROM sys.optstat_hist_control$
WHERE sname IN ('CASCADE','ESTIMATE_PERCENT','DEGREE',
'METHOD_OPT','NO_INVALIDATE','GRANULARITY');
#查詢表級dba_tab_stat_prefs
SELECT table_name, preference_name, preference_value
 FROM dba_tab_stat_prefs
 WHERE lower(owner) = '&owner'
 AND lower(table_name)='&tablename'
ORDER BY table_name, preference_name;

#獲取是get,恢復是reset,刪除是delete

 

*****************10g 自動收集物件統計資訊job
oracle 逐漸讓query optimizer認識到統計資訊的正確性，所以一直在優化收集統計資訊方面的工作
10g 是由scheduler來完成的（下面指令碼來自trouble shooting oracle）,job_name就是GATHER_STATS_JOB

SQL> COLUMN program_owner FORMAT A13
SQL> COLUMN program_name FORMAT A17
SQL> COLUMN schedule_owner FORMAT A14
SQL> COLUMN schedule_name FORMAT A24
SQL> COLUMN schedule_type FORMAT A15
SQL> COLUMN enabled FORMAT A7
SQL> COLUMN state FORMAT A9
SQL> COLUMN program_type FORMAT A16
SQL> COLUMN program_action FORMAT A41
SQL> COLUMN enabled FORMAT A7
SQL> COLUMN window_name FORMAT A16
SQL> COLUMN repeat_interval FORMAT A37
SQL> COLUMN duration FORMAT A13
SQL> COLUMN enabled FORMAT A7
SQL>
SQL>
SQL> SELECT program_name, schedule_name, schedule_type, enabled, state
  2  FROM dba_scheduler_jobs
  3  WHERE wner = 'SYS'
  4  AND job_name = 'GATHER_STATS_JOB';

PROGRAM_NAME      SCHEDULE_NAME            SCHEDULE_TYPE   ENABLED STATE
----------------- ------------------------ --------------- ------- ---------
GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP WINDOW_GROUP    TRUE    SCHEDULED

可以看到這個scheduler中資訊 是一個視窗組,裡面還有一個過程GATHER_STATS_PROG
SQL> SQL>
SQL> SELECT program_action, number_of_arguments, enabled
  2  FROM dba_scheduler_programs
  3  WHERE wner = 'SYS'
  4  AND program_name = 'GATHER_STATS_PROG';

PROGRAM_ACTION                            NUMBER_OF_ARGUMENTS ENABLED
----------------------------------------- ------------------- -------
dbms_stats.gather_database_stats_job_proc                   0 TRUE

看這裡面呼叫的程式執行的動作(呼叫dbms_stats.gather_database_stats_job_proc 未帶引數)

SQL> SQL> SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
  2  FROM dba_scheduler_jobs j, dba_scheduler_wingroup_members m,
  3       dba_scheduler_windows w
  4  WHERE j.schedule_name = m.window_group_name
  5  AND m.window_name = w.window_name
  6  AND j.owner = 'SYS'
  7  AND j.job_name = 'GATHER_STATS_JOB'
  8  AND j.schedule_type = 'WINDOW_GROUP';

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

WEEKEND_WINDOW   freq=daily;byday=SAT;byhour=0;byminut +002 00:00:00 TRUE
                 e=0;bysecond=0

SQL> SQL> SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
  2  FROM dba_scheduler_wingroup_members m, dba_scheduler_windows w
  3  WHERE m.window_name = w.window_name
  4  AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP';

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

WEEKEND_WINDOW   freq=daily;byday=SAT;byhour=0;byminut +002 00:00:00 TRUE
                 e=0;bysecond=0

# MAINTENANCE_WINDOW_GROUP-呼叫過程GATHER_STATS_PROG-呼叫dbms_stats.gather_database_stats_job_proc
# MAINTENANCE_WINDOW_GROU包含2個member，一個是夜間一個是週末，夜間22點開始執行8小時，週末全天
#預設會在負載低的時候執行，如果負載一直很好，也會無法完成
#dbms_schedluer.enable(name=>'sys.gather_stats_job'),dbms_schedluer.enable(name=>'sys.gather_stats_job') 開關（default sys可執行其他user要有改物件的alert許可權，既alert on gater_stats_jobs）

#檢查自動收集執行情況
set linesize 1000
col job_name format a20
 SELECT log_id, job_name, status,
           TO_CHAR (log_date, 'YYYY-MM-DD HH24:MI:SS') log_date
      FROM dba_scheduler_job_run_details
    WHERE job_name = 'GATHER_STATS_JOB' order by log_date desc;

 

#修改自動收集內容
#execute dbms_output.put_line(dbms_stats.get_param(pname=>'autostats_target'))
SQL> set serveroutput on

SQL> execute dbms_output.put_line(dbms_stats.get_param(pname=>'autostats_target'))
AUTO

PL/SQL procedure successfully completed. 可以看到 值是auto

It takes the following values:
 'ALL'    -- statistics collected for all objects in system
 'ORACLE' -- statistics collected for all oracle owned objects
 'AUTO'   -- oracle decides for which objects to collect stats

In Oracle10g and Oracle11g Release 1 'ALL' and 'AUTO' are equivalent(可以看到 10g-11g r1中all=auto) 而auto是預設的

 

#可以看到實際上oracle 限制下列列表中schema自動收集
'ORACLE' actually restricts the list of schemas for which the automatic stats
gathering job will gather statistics to a list of Oracle component system
schemas generated by the following queries:

In 10.1:

select distinct u.name
from registry$ r, user$ u
where r.schema#=u.user#
and   r.status in (1,3,5)
and   r.namespace = 'SERVER';

In 10.2 and 11.1:

select  distinct name from (
  select u.name
    from registry$ r, user$ u
    where r.status in (1,3,5)
    and   r.namespace = 'SERVER'
    and   r.schema#=u.user#
  union all
  select u.name -- get additional component schemas
    from registry$ r, registry$schemas s, user$ u
    where r.status in (1,3,5)
    and   r.namespace = 'SERVER'
    and   r.cid=s.cid
    and   s.schema#=u.user#)
order by name;

e.g. such schemas are SYS, SYSMAN, WMSYS and EXFSYS in a sample database.

 

 

 

 

11g(以下查詢指令碼來自oracle trouble shooting)

#收集資訊整合了自動維護任務
SQL> COLUMN task_name FORMAT A17
SQL> COLUMN status FORMAT A7
SQL> COLUMN program_action FORMAT A41
SQL> COLUMN enabled FORMAT A7
SQL> COLUMN window_group FORMAT A14
SQL> COLUMN window_name FORMAT A16
SQL> COLUMN repeat_interval FORMAT A42
SQL> COLUMN duration FORMAT A13
SQL> COLUMN enabled FORMAT A7
SQL> SELECT task_name, status
  2  FROM dba_autotask_task
  3  WHERE client_name = 'auto optimizer stats collection';

TASK_NAME         STATUS
----------------- -------
gather_stats_prog ENABLED             ~~~獲得的其實就是program_name                      

SQL> SELECT program_action, number_of_arguments, enabled
  2  FROM dba_scheduler_programs
  3  WHERE wner = 'SYS'
  4  AND program_name = 'GATHER_STATS_PROG';

PROGRAM_ACTION                            NUMBER_OF_ARGUMENTS ENABLED
----------------------------------------- ------------------- -------
dbms_stats.gather_database_stats_job_proc                   0 TRUE

看到這個program裡的呼叫就是不帶任何引數執行dbms_stats.gather_database_stats_job_proc

SQL> SELECT window_group
  2  FROM dba_autotask_client
  3  WHERE client_name = 'auto optimizer stats collection';

WINDOW_GROUP
--------------
ORA$AT_WGRP_OS~~~~~~~~所屬的window

SQL> SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
  2  FROM dba_autotask_window_clients c, dba_scheduler_windows w
  3  WHERE c.window_name = w.window_name
  4  AND c.optimizer_stats = 'ENABLED';

WINDOW_NAME      REPEAT_INTERVAL                            DURATION
---------------- ------------------------------------------ -------------
ENABLED
-------
MONDAY_WINDOW    freq=daily;byday=MON;byhour=22;byminute=0; +000 04:00:00
                  bysecond=0
TRUE

TUESDAY_WINDOW   freq=daily;byday=TUE;byhour=22;byminute=0; +000 04:00:00
                  bysecond=0
TRUE

WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; +000 04:00:00

WINDOW_NAME      REPEAT_INTERVAL                            DURATION
---------------- ------------------------------------------ -------------
ENABLED
-------
                  bysecond=0
TRUE

THURSDAY_WINDOW  freq=daily;byday=THU;byhour=22;byminute=0; +000 04:00:00
                  bysecond=0
TRUE

FRIDAY_WINDOW    freq=daily;byday=FRI;byhour=22;byminute=0; +000 04:00:00
                  bysecond=0

WINDOW_NAME      REPEAT_INTERVAL                            DURATION
---------------- ------------------------------------------ -------------
ENABLED
-------
TRUE

SATURDAY_WINDOW  freq=daily;byday=SAT;byhour=6;byminute=0;  +000 20:00:00
                 bysecond=0
TRUE

SUNDAY_WINDOW    freq=daily;byday=SUN;byhour=6;byminute=0;  +000 20:00:00
                 bysecond=0
TRUE

WINDOW_NAME      REPEAT_INTERVAL                            DURATION
---------------- ------------------------------------------ -------------
ENABLED
-------

7 rows selected.

#可以看到11g時間調整了，做了7個視窗，其中1-5每天晚上22點開始執行4小時
週末6點開始執行20小時

 

#11g開啟/關閉 自動收集統計資訊
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL)
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL)

 

#lock統計資訊

SQL> create table t1(a int);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats('SYS','T1');

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name='T1';

  NUM_ROWS
----------
         1

 

#lock該schema下所有patch
execute dbms_stats.lock_schema_stats(ownname=>'&user')；
#lock一張表的統計資訊
execute dbms_stats.lock_table_stats(ownname=>'&user',tabname=>'&tablename');

SQL> insert into t1 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.lock_table_stats(ownname=>'&user',tabname=>'&tablename');
Enter value for user: SYS
Enter value for tablename: T1

PL/SQL procedure successfully completed.

SQL> select num_rows,table_lock from user_tables where table_name='T1';

  NUM_ROWS TABLE_LO
---------- --------
         1 ENABLED

SQL>
SQL> execute dbms_stats.gather_table_stats('SYS','T1');

BEGIN dbms_stats.gather_table_stats('SYS','T1'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 17806
ORA-06512: at "SYS.DBMS_STATS", line 17827
ORA-06512: at line 1

#force可以強制收集
SQL> SQL> execute dbms_stats.gather_table_stats('SYS','T1',force=>TRUE);

PL/SQL procedure successfully completed.

SQL> SQL> select num_rows,table_lock from user_tables where table_name='T1';

  NUM_ROWS TABLE_LO
---------- --------
         2 enabled

#unlock
SQL> execute dbms_stats.unlock_table_stats(ownname=>'&user',tabname=>'&tablename');
Enter value for user: SYS
Enter value for tablename: T1

#查詢哪些表的統計資訊被lock了
select table_name from user_tab_statistics where stattype_locked is not null;

 

SQL> create index t1_id on t1(a);

Index created.

SQL> select table_name from user_tab_statistics where stattype_locked is not null;

TABLE_NAME
------------------------------

T1

SQL> alter index t1_id rebuild;

Index altered.

SQL> alter index t1_id rebuild compute statistics; 顯示指定報錯
alter index t1_id rebuild compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-664241/，如需轉載，請註明出處，否則將追究法律責任。