共享池抖动分析脚本

参考自《oracle性能优化实务》

SQL> set line 200
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> col component format a40 truncate;
SQL> select component,oper_type, oper_mode, start_time, end_time, trunc(target_size/1024/1024) target 
  2  from v$sga_resize_ops;

COMPONENT                                OPER_TYPE     OPER_MODE START_TIME          END_TIME                TARGET
---------------------------------------- ------------- --------- ------------------- ------------------- ----------
shared pool                              GROW          IMMEDIATE 2017-11-10 01:41:54 2017-11-10 01:41:54        320
KEEP buffer cache                        STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54          0
DEFAULT 32K buffer cache                 STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54          0
DEFAULT 16K buffer cache                 STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54          0
RECYCLE buffer cache                     STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54          0
shared pool                              GROW          IMMEDIATE 2017-11-10 01:41:54 2017-11-10 01:41:54        304
shared pool                              STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54        288
large pool                               STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54        576
java pool                                STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54         16
streams pool                             STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54          0
DEFAULT buffer cache                     SHRINK        IMMEDIATE 2017-11-10 01:41:54 2017-11-10 01:41:54        272

COMPONENT                                OPER_TYPE     OPER_MODE START_TIME          END_TIME                TARGET
---------------------------------------- ------------- --------- ------------------- ------------------- ----------
DEFAULT buffer cache                     SHRINK        IMMEDIATE 2017-11-10 01:41:54 2017-11-10 01:41:54        288
DEFAULT buffer cache                     INITIALIZING            2017-11-10 01:41:54 2017-11-10 01:41:54        304
ASM Buffer Cache                         STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54          0
DEFAULT buffer cache                     STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54        304
DEFAULT 2K buffer cache                  STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54          0
DEFAULT 4K buffer cache                  STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54          0
DEFAULT 8K buffer cache                  STATIC                  2017-11-10 01:41:54 2017-11-10 01:41:54          0
DEFAULT buffer cache                     GROW          DEFERRED  2017-11-10 01:42:51 2017-11-10 01:42:57        816
large pool                               SHRINK        DEFERRED  2017-11-10 01:42:51 2017-11-10 01:42:57         32
DEFAULT buffer cache                     SHRINK        DEFERRED  2017-11-10 02:32:52 2017-11-10 02:32:52        800
shared pool                              GROW          DEFERRED  2017-11-10 02:32:52 2017-11-10 02:32:52        336

22 rows selected.