10g NewFeatures學習筆記(轉)

2007.11.05

一、Automatic Shared Memory Management(ASMM)

1.base:
  1) In Oracle 10g, you need to specify only the SGA_TARGET parameter, which specifies the total size of the SGA. Individual components of the SGA are automatically allocated by the database based on the workload and history information.

  2) The new parameter SGA_TARGET is the size of total SGA, which includes the automatically sized components, manually sized components, and any internal allocations during instance startup.

2.enabling and disabling ASSM
  1) ASMM is enabled when the STATISTICS_LEVEL parameter is set to TYPICAL or ALL and the SGA_TARGET parameter is set to a nonzero value. When enabled, ASMM distributes memory appropriately for the following memory areas: DB_CACHE_SIZE、SHARED_POOL_SIZE、LARGE_POOL_SIZE、JAVA_POOL_SIZE 
  2) The following areas should be manually configured and are not affected by ASMM:LOG_BUFFER、DB_KEEP_CACHE_SIZE、DB_RECYCLE_CACHE_SIZE、DB_nK_CACHE_SIZE、STREAMS_POOL_SIZE、Fixed-SGA area and internal allocations
  3) The SGA_TARGET parameter is dynamic and can be resized using the ALTER SYSTEM statement.The value of SGA_TARGET cannot be higher than the SGA_MAX_SIZE parameter, which is not dynamically changeable. Reducing the size of SGA_TARGET affects only the autotuned components of the SGA. SGA_TARGET can be reduced until one of the autotuned components reaches its minimum size (a user-specified or Oracle-determined minimum).
  4)You can query the current sizes of the SGA components using the V$SGA_DYNAMIC_
COMPONENTS dictionary view, like so:
  SQL> select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE from v$sga_dynamic_components;

COMPONENT                  CURRENT_SIZE   MIN_SIZE   MAX_SIZE
-------------------------- ------------ ---------- ----------
shared pool                    71303168   62914560          0
large pool                      4194304    4194304          0
java pool                       4194304    4194304          0
streams pool                          0          0          0
DEFAULT buffer cache          125829120  125829120          0
KEEP buffer cache                     0          0          0
RECYCLE buffer cache                  0          0          0
DEFAULT 2K buffer cache               0          0          0
DEFAULT 4K buffer cache               0          0          0
DEFAULT 8K buffer cache               0          0          0
DEFAULT 16K buffer cache              0          0          0
DEFAULT 32K buffer cache              0          0          0
ASM Buffer Cache                      0          0          0

13 rows selected
 
  5)When SGA_TARGET is set to a nonzero value, the autotuned SGA parameters will have default values of zero. If you specify a value for the autotuned SGA parameters,the value will be treated as the lower limit of that component.
 
  6)Resizing the autotuned SGA parameters is possible even if ASMM is enabled. For autotuned parameters, manual resizing will result in immediate component resizing if the current value is smaller than the new value. If the new value is smaller, the component is not resized, but a new minimum size is set.

  7)Setting SGA_TARGET to zero will disable ASMM. The autotuned components will have values of their current sizes, and these values are written to the SPFILE to use for the next instance startup.

  8)For manually configured SGA parameters, resizing will immediately take effect to the precise new value. If the size of a component is increased, one or more of the autotuned components will be reduced. If the size of a manually configured component is reduced, the memory that is released is given to the automatically sized components.

3.related views
  1)V$SGA_CURRENT_RESIZE_OPS： SGA resize operations that are currently in progress
  2)V$SGA_RESIZE_OPS ：Information about the last 400 completed SGA resize  operations
  3)V$SGA_DYNAMIC_COMPONENTS ：Information about the dynamic components of the SGA
  4)V$SGA_DYNAMIC_FREE_MEMORY：Information about the amount of SGA memory available for future dynamic SGA resize operations

4.The Memory Manager Process
  1) Oracle 10g comes with the new MMAN process (which stands for memory manager) to manage the automatic shared memory. MMAN serves as the SGA memory broker and coordinates the sizing of the memory components. It keeps track of the sizes of the components and pending resize operations.
  2)The MMAN process observes the system and workload to determine the ideal distribution of memory. MMAN performs this check every few minutes so that memory can always be present where needed. When SPFILE is used, component sizes are used from the last shutdown.
 
5.示例

1)查詢當前的設定

SQL> Select component,current_size,min_size,user_specified_size From v$sga_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE
------------------------- ------------ ---------- -------------------
shared pool                  113246208  109051904                   0
large pool                     4194304    4194304                   0
java pool                      4194304    4194304                   0
streams pool                         0          0                   0
DEFAULT buffer cache          41943040   37748736                   0
KEEP buffer cache                    0          0                   0
RECYCLE buffer cache                 0          0                   0
DEFAULT 2K buffer cache              0          0                   0
DEFAULT 4K buffer cache              0          0                   0
DEFAULT 8K buffer cache              0          0                   0
DEFAULT 16K buffer cache             0          0                   0
DEFAULT 32K buffer cache             0          0                   0
ASM Buffer Cache                     0          0                   0

SQL> select name,value from v$parameter where name in
('statistics_level','sga_target','db_cache_size','shared_pool_size','large_pool_size','java_pool_size','sga_max_size');

NAME               VALUE
------------------ -----------
sga_max_size       167772160
shared_pool_size   0
large_pool_size    0
java_pool_size     0
sga_target         171966464
db_cache_size      0
statistics_level   TYPICAL

7 rows selected

說明：

a.當前屬於enable ASMM，sga_target=164M,其它引數均為0(USER_SPECIFIED_SIZE)

b.當前各個元件的大小從v$sga_dynamic_components中的current_size可以看出

2)disable assm

＃取消assm

SQL> alter system set sga_target=0;

System altered

#查詢檢視，發現oracle根據之前的current_size自動設定了shared_pool_size、db_cache_size等幾個引數。

SQL> Select component,current_size,min_size,user_specified_size From v$sga_dynamic_components;

COMPONENT                CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE
------------------------ ------------ ---------- -------------------
shared pool                 113246208  109051904           113246208
large pool                    4194304    4194304             4194304
java pool                     4194304    4194304             4194304
streams pool                        0          0                   0
DEFAULT buffer cache         41943040   37748736            41943040
KEEP buffer cache                   0          0                   0
RECYCLE buffer cache                0          0                   0
DEFAULT 2K buffer cache             0          0                   0
DEFAULT 4K buffer cache             0          0                   0
DEFAULT 8K buffer cache             0          0                   0
DEFAULT 16K buffer cache            0          0                   0
DEFAULT 32K buffer cache            0          0                   0
ASM Buffer Cache                    0          0                   0

13 rows selected

SQL> select name,value from v$parameter
where name in ('statistics_level','sga_target','db_cache_size','shared_pool_size','large_pool_size','java_pool_size','sga_max_size');

NAME               VALUE    
------------------ ----------
sga_max_size       167772160
shared_pool_size   113246208
large_pool_size    4194304
java_pool_size     4194304
sga_target         0
db_cache_size      41943040
statistics_level   TYPICAL

7 rows selected

SQL>

 

 

二、Automatic Undo Retention

1.base
  1) 9i引入了aum，10g進一步優化了一下，可以自動調整undo_retention引數，該引數預設900秒。當把該引數設定為0或空時，10g自動調整undo_retention引數，並以900秒為最小值。當把該引數設定為非0值時，10g同樣自動調整undo_retention，但以設定值為最小值。

  2) AUM啟動時，AUR功能自動啟動，並且不能禁用。它主要是為了longest-running query。

2.Undo Advisor
  1) Undo Advisor是10g的一個新特性：資料庫會自動分析undo的使用情況，給出undo空間大小的建議，從而支援longest-running query .
  
3.removed parameters
  1) 10g為了簡化undo配置，去掉了幾個引數，如下：max_rollback_segments、undo_suppress_errors、row_locking、serializable、transaction_auditing

4.rentention guarantee
  1) 10g自動調整retention主要為了long-running query，防止snap too old錯誤，但非常heavy的查詢也保證不了，監於這種情況，10g引入了rentention guarantee機制

  2) 該機制保證指定的undo retention內一定會保持undo可用

  3) 在建立undo tablespace時，可以用 retention guarantee子句來啟動該機制，或者使用alter tablespace ... retention guarantee也可以啟用

  4) 同理，可以使用retention noguarantee子句來停止該機制。

  5) 使用dba_tablespaces，可以檢視undo tablespace 的retention guarantee機制是否啟用
  SQL> SELECT tablespace_name, contents, retention  FROM dba_tablespaces;

  TABLESPACE_NAME                CONTENTS  RETENTION
  ------------------------------ --------- -----------
  SYSTEM                         PERMANENT NOT APPLY
  UNDOTBS1                       UNDO      NOGUARANTEE
  SYSAUX                         PERMANENT NOT APPLY
  TEMP                           TEMPORARY NOT APPLY
  USERS                          PERMANENT NOT APPLY
  EXAMPLE                        PERMANENT NOT APPLY

三、Automatic Checkpoint Tunning

1.Mean Time to Recovery(MTTR) advisor
  1) MTTR advisor 用於ACT(Automatic Checkpoint Tuning),9i 引入了fast_start_mttr_target引數，用於設定期望的例項恢復時間，但是，通過9iR2引入的MTTR advisor，很難設定準確合適的fast_start_mttr_target引數，因為你總是要在例項最小恢復時間和正常執行時良好的I/O狀況之間做權衡。
 
  2) 預設情況下，10g支援ACT，可以達到很好的例項恢復時間和執行時IO狀況，而且你不用設定更多引數

  3) 設定fast_start_mttr_target為1個非0值或空值，將會啟動ACT，不過有個前提， statistics_level必須為TYPICAL或者ALL. 如果設定fast_start_mttr_target為0，則將會禁用ACT。

2.related views
  1)當啟用ACT時，可以用v$mttr_target_advice檢視來檢視建議。

  2)10g的v$instance_recovery檢視新增加了一列，optimal_logfile_size，重做日誌成員的大小最好是大於該值。(啟動了ACT這個列才會有值)

四、Collecting Automatic Optimizer Statistics

1.base
  1) 為了讓查詢優化器(query optimizer)產生最優的執行計劃，物件上的statistics一定是要有效的。
oracle資料庫收集statistics的功能在每個版本都在得到不斷的提高
 
  2) 8i中，引入了dbms_stats包，dba可以利用它來決定何時以何種方式來收集statistics，9i中引入了監控功能，資料庫決定如何收集statistics，但監控功能必須在收集統計資訊時手工開啟，即使用gather auto子句來使資料庫發現statistics無效時重新收集statistics。
 
  3)10g中，收集statistics完全自動，而且table monitoring功能預設是開啟的，不過前提是statistics_level等於typical(default)或者all。在create table或alter table時，[no] monitoring子句已經被obsolete,但如果你使用了，也不會報錯，10g會ignore該子句.

2.How Statistics Are Maintained Current
  1) 當建立1個10g database或者升級為10g database時，會自動建立1個Job，gather_stats_job,
該job用來收集statistics.
  The job is managed by the scheduler and runs when the MAINTENANCE_WINDOW_GROUP window group is opened. The MAINTENANCE_WINDOW_GROUP window group, which has the WEEKEND_WINDOW window and the WEEKNIGHT_WINDOW window, are also created at the database creation time. By default WEEKNIGHT_WINDOW opens Monday through Friday at 10 p.m. for 8 hours. By default WEEKEND_WINDOW opens Saturday at 0000 hours and continues for 48 hours.
 
  2) 該job實際呼叫了dbms_stats.gather_database_stats_job_proc過程，這是屬於10g的一個內部過程，類似於dbms_stats.gather_database_stats. 該任務收集那些缺失statistics，或者statistics太陳舊(比如超過10%的行變化)的物件的stats。
 
  3) 可以用dbms_scheduler.disable('GATHER_STATS_JOB')來禁用自動收集statistics的功能，但不建議這樣做。

3.以下情況需要手工收集statistics
  1)When a table is loaded using bulk operation
 
  2)When using external tables

  3)To collect system statistics

  4)To collect statistics on fixed objects (dynamic performance dictionary tables)


4.收集dictionary的stats
  1)10g以前版本不關注dictionary的stats，但10g為了更好的效能，需要收集dictionary、OS的stats.
  2)DBMS_STATS包具有收集dictionary和fixed table的stats的功能，收集時，最好使用gather auto選項。
  3)收集dictionary 的stats方法有以下幾種：

    a. DBMS_STATS.GATHER_DATABASE_STATS (GATHER_SYS=>TRUE, PTIONS=>'GATHER AUTO');     The default for GATHER_SYS parameter is FALSE. If you set it to TRUE, the
statistics on the objects owned by the SYS schema are analyzed along with the other objects of the database.

    b. DBMS_STATS.GATHER_SCHEMA_STATS ('SYS', PTIONS=>'GATHER AUTO');
    Use this option to gather the schema statistics by specifying the SYS schema name.

    c. DBMS_STATS.GATHER_DICTIONARY_STATS (OPTIONS=>'GATHER AUTO');
    This option collects statistics on the SYS, SYSTEM, and any other schema that owns the server components。

    d.需要有analyze any dictionary的許可權
  4)收集fixed view的stats的方法
    使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

5.收集system stats
  1)用dbms_stats.gather_system_stats收集system stats,例子：
     exec dbms_stats.gather_system_stats('NOWORKLOAD');
     exec dbms_stats.gather_system_stats('START');
     exec dbms_stats.gather_system_stats('STOP');

注:（AUTOMATIC Optimizer statistics collection可以自動收集dictionary stats，但不會自動收集fixed table stats和system stats）

6.管理statistics
  1)10g具有鎖定stats,維護歷史stats,還原stats的功能

  2)lock stats：
     一旦lock，不能更新其stats。
     利用dbms_stats.lock_table_stats來鎖定表的stats,利用dbms_stats.unlock_table_stats來解鎖。同理，還有dbms_stats.lock_schema_stats。
     可以使用dba_tab_statistics的stattype_locked=ALL列來檢視其是否被鎖住。

7.statistics history
  1) 10g可以自動儲存stats歷史，dbms_stats包裡的gather_*,import_*,set_*過程，用來在更改stats之前自動儲存stats資訊。
  2) dba_optstat_operations字典顯示了所有的database-level、schema-level的update stats操作的star time和end time。
     SQL> SELECT stats_update_time FROM dba_tab_stats_history
          WHERE wner = 'TRAING' AND TABLE_NAME = 'ENROLLMENT';
     STATS_UPDATE_TIME
     -----------------------------------
     20-APR-04 11.45.49.898795 AM -05:00
     14-MAY-04 06.20.41.034775 AM -05:00
     SQL>
  3) old stats每一interval就會被廢棄，預設31天，可以用dbms_stats.get_stats_history_retention函式可以檢視當前設定
    SQL> select dbms_stats.get_stats_history_retention from dual;

    GET_STATS_HISTORY_RETENTION
    ---------------------------
                             31
  4) dbms_stats.get_stats_history_availabity函式可以查詢the oldest time stamp of stats history.
    SQL> select dbms_stats.get_stats_history_availability from dual;

    GET_STATS_HISTORY_AVAILABILITY
    ---------------------------------------------------
    10-9月 -07 10.58.28.000000000 下午 +08:00
 
  5)dbms_stat.alter_stats_history_retention過程可以修改interval
   
    SQL> exec dbms_stats.alter_stats_history_retention(15);

    PL/SQL procedure successfully completed

    SQL> select dbms_stats.get_stats_history_retention from dual;

    GET_STATS_HISTORY_RETENTION
    ---------------------------
                             15

  6)可以利用dbms_stats.purge_stats過程，來手工廢棄stats history
    SQL> exec dbms_stats.purge_stats(to_timestamp('20071001','YYYYMMDD'));

    PL/SQL procedure successfully completed

    SQL> select dbms_stats.get_stats_history_availability from dual;

    GET_STATS_HISTORY_AVAILABILITY
    -------------------------------------------------------------
    01-10月-07 12.00.00.000000000 上午 +08:00
 
  7) dbms_stats中的以下過程用於restore stats history
    restore_database_stats
    restore_dictionary_stats
    restore_fixed_objects_stats
    restore_schema_stats
    restore_system_stats
    restore_tables_stats
 
  備註：如果用analyze命令收集stats,old stats不會保留到AWR中，因此也不能用於還原。

 
8.other
  10g中，create index或 rebuild index時會自動collect stats，而compute statistics 子句現在已經被obsolete.

 

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