A. Because the PGA_AGGREGATE_TARGET parameter is not set
B. Because the STATISTICS_LEVEL parameter is set to BASIC
C. Because MEMORY_TARGET and MEMORY_MAX_TARGET cannot be equal
D. Because both the SGA_TARGET and MEMORY_TARGET parameters are set.
Correct Answer: B
Section: (none)
Explanation
Explanation/Reference:
应该是B。
关键在于 STATISTICS_LEVEL 被设置成了basic
Setting SGA Target Size
You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to
a nonzero value. This parameter in effect replaces the parameters that control the memory allocated for a
specific set of individual components, which are now automatically and dynamically resized (tuned) as
needed.
Note:The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the
default) or ALL for automatic shared memory management to function.
D) check sga_target and memory target set together
SQL> show parameter memory
NAME TYPE VALUE
hi_shared_memory_address integer 0
memory_max_target big integer 1408M
memory_target big integer 1408M
shared_memory_address integer 0
SQL> show parameter sga
NAME TYPE VALUE
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1408M
sga_target big integer 1008M
SQL>
statistics_level 参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个值:basic,typical,all.支持alter session,alter system 动态修改.如果要用statspack或者AWR收集系统性能统计数据.那么这个参数的值必须为typical或all.通常all是一个全面收集,包括 OS以及sql执行路径方面的一些统计信息,除非遇见非常严重的性能问题或在一些特殊的性能挣断方面才会用到statistics_level=all, 平常statistics_level=typeical已经足够诊断99%的性能问题了.
example: seting statistics_level
alter system set statistics_level=basic;
alter system set statistics_level=typical;
alter system set statistics_level=all;
or
alter session set statistics_level=basic;
alter session set statistics_level=typical;
alter session set statistics_level=all;
statistics_level=basic的情况下,oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集,只要设置alter system set statistics_level=basic;就行了;
statistics_level=typical的时
候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集,如要要收集这个两项,必须设置statistics_level=all;
如果当statistics_level=ALL,系统收集所有的统计信息.
设置 STATISTICS_LEVEL = BASIC 会禁用一下oracle 的功能特性
Automatic Workload Repository (AWR) Snapshots
Automatic Database Diagnostic Monitor (ADDM)
All server-generated alerts
Automatic SGA Memory Management (ASMM)和自动内存管理
Automatic optimizer statistics collection
Object level statistics
End to End Application Tracing (V$CLIENT_STATS)
Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
Service level statistics
Buffer cache advisory
MTTR advisory
Shared pool sizing advisory
Segment level statistics
PGA Target advisory
Timed statistics
Monitoring of statistics