Buffer Cache Advice ENABLED ENABLED TYPICAL NO

MTTR Advice ENABLED ENABLED TYPICAL NO

Timed Statistics ENABLED ENABLED TYPICAL YES

Timed OS Statistics DISABLED DISABLED ALL YES

Segment Level Statistics ENABLED ENABLED TYPICAL NO

PGA Advice ENABLED ENABLED TYPICAL NO

Plan Execution Statistics DISABLED DISABLED ALL YES

Shared Pool Advice ENABLED ENABLED TYPICAL NO

Modification Monitoring ENABLED ENABLED TYPICAL NO

Longops Statistics ENABLED ENABLED TYPICAL NO

Bind Data Capture ENABLED ENABLED TYPICAL NO

Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO

Threshold-based Alerts ENABLED ENABLED TYPICAL NO

Global Cache Statistics ENABLED ENABLED TYPICAL NO

Active Session History ENABLED ENABLED TYPICAL NO

Undo Advisor, Alerts and Fast ENABLED ENABLED TYPICAL NO

Ramp up

Streams Pool Advice ENABLED ENABLED TYPICAL NO

Time Model Events ENABLED ENABLED TYPICAL YES

Plan Execution Sampling ENABLED ENABLED TYPICAL YES

Automated Maintenance Tasks ENABLED ENABLED TYPICAL NO

SQL Monitoring ENABLED ENABLED TYPICAL YES

Adaptive Thresholds Enabled ENABLED ENABLED TYPICAL NO

V$IOSTAT_* statistics ENABLED ENABLED TYPICAL NO

23 rows selected.?

可以看到在TYPICAL设置下，除Timed OS Statistics 和 Plan Execution Statistics 信息不收集外,其他信息都被收集。其中, Buffer Cache Advice 受 db_cache_advice 参数独立控制, Timed Statistics 受 timed_statistics 参数独立控制。其他统计信息的收集都受到STATISTICS_LEVEL 参数的控制。当STATISTICS_LEVEL为Basic时，除受独立参数影响的Buffer Cache Advice 和TimedStatistics 外，其他信息收集都将被禁止。

我们可以通过查询V$SHARED_POOL_ADVICE视图获得关于Shared Pool的建议信息：?

11:36:01 sys@felix SQL>selectSHARED_POOL_SIZE_FOR_ESTIMATE SPSFE,

11:47:38 2 SHARED_POOL_SIZE_FACTOR SPSF,

11:47:38 3 ESTD_LC_SIZE,

11:47:38 4 ESTD_LC_MEMORY_OBJECTS ELMO,

11:47:39 5 ESTD_LC_TIME_SAVED ELTS,

11:47:39 6 ESTD_LC_TIME_SAVED_FACTOR ELTSF,

11:47:39 7 ESTD_LC_MEMORY_OBJECT_HITS ELMOH

11:47:39 8 from v$shared_pool_advice;

SPSFE SPSF ESTD_LC_SIZE ELMO ELTS ELTSF ELMOH

---------- ---------- ------------ -------------------- ---------- ----------

104 .6341 8 834 814 .8781 25081

124 .7561 28 1903 843 .9094 25874

128 .7805 32 2109 848 .9148 25951

132 .8049 35 2318 861 .9288 26110

136 .8293 39 2508 867 .9353 26201

140 .8537 42 2715 873 .9417 26293

144 .878 46 2914 882 .9515 26404

148 .9024 50 3162 889 .959 26515

152 .9268 50 3221 897 .9676 26787

156 .9512 50 3221 904 .9752 27085

160 .9756 50 3221 916 .9881 28117

164 1 50 3221 927 1 29153

168 1.0244 50 3221 927 1 29157

172 1.0488 50 3221 927 1 29157

176 1.0732 50 3221 927 1 29157

180 1.0976 50 3221 927 1 29157

184 1.122 50 3221 927 1 29157

188 1.1463 50 3221 927 1 29157

192 1.1707 50 3221 927 1 29157

196 1.1951 50 3221 927 1 29157

200 1.2195 50 3221 927 1 29157

204 1.2439 50 3221 927 1 29157

224 1.3659 50 3221 927 1 29157

244 1.4878 50 3221 927 1 29157

264 1.6098 50 3221 927 1 29157

284 1.7317 50 3221 927 1 29157

304 1.8537 50 3221 927 1 29157

324 1.9756 50 3221 927 1 29157

344 2.0976 50 3221 927 1 29157

29 rows selected.

11:47:40 sys@felix SQL>?

通过以上统计数据分析，当shared_pool_size设置为304M时即可达到和现在相同的效果，

目前的shared_pool_size设置浪费了部分内存，那么我们就可以动态调整shared_pool_size参数，

释放这部分内存，留给其他内存组件使用。

SQL> alter system setshared_pool_size=304M;

当进行动态参数修改时，修改Session会处于等待状态，等待事件为background parameter

adjustment：

SQL> selectsid,seq#,event,SECONDS_IN_WAIT,state

2 from v$session_waitwhere sid=80;

SID SEQ# EVENT SECONDS_IN_WAIT STATE