10g新特性(zt)

一、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/104446/viewspace-558230/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/104446/viewspace-558230/