加快dba_hist_active_sess_history的查詢速度

核心資料庫的快照保留時間提高到1個月，導致從dba_hist_active_sess_history這張表裡找點東西非常慢。從v$session裡看髮型是對WRH$_ACTIVE_SESSION_HISTORY走direct path read，很吃IO，而且速度也不快。

看一下WRH$_ACTIVE_SESSION_HISTORY的定義：
17:01:51 SQL> select dbms_metadata.get_ddl('TABLE','WRH$_ACTIVE_SESSION_HISTORY','SYS') from dual;


DBMS_METADATA.GET_DDL('TABLE','WRH$_ACTIVE_SESSION_HISTORY','SYS')
--------------------------------------------------------------------------------


  CREATE TABLE "SYS"."WRH$_ACTIVE_SESSION_HISTORY"
   (    "SNAP_ID" NUMBER NOT NULL ENABLE,
        "DBID" NUMBER NOT NULL ENABLE,
        "INSTANCE_NUMBER" NUMBER NOT NULL ENABLE,
        "SAMPLE_ID" NUMBER NOT NULL ENABLE,
        "SAMPLE_TIME" TIMESTAMP (3) NOT NULL ENABLE,
        "SESSION_ID" NUMBER NOT NULL ENABLE,
        "SESSION_SERIAL#" NUMBER,
        "USER_ID" NUMBER,
        "SQL_ID" VARCHAR2(13),
        "SQL_CHILD_NUMBER" NUMBER,
        "SQL_PLAN_HASH_VALUE" NUMBER,
        "SERVICE_HASH" NUMBER,
        "SESSION_TYPE" NUMBER,
        "SQL_OPCODE" NUMBER,
        "QC_SESSION_ID" NUMBER,
        "QC_INSTANCE_ID" NUMBER,
        "CURRENT_OBJ#" NUMBER,
        "CURRENT_FILE#" NUMBER,
        "CURRENT_BLOCK#" NUMBER,
        "SEQ#" NUMBER,
        "EVENT_ID" NUMBER,
        "P1" NUMBER,
        "P2" NUMBER,
        "P3" NUMBER,
        "WAIT_TIME" NUMBER,
        "TIME_WAITED" NUMBER,
        "PROGRAM" VARCHAR2(64),
        "MODULE" VARCHAR2(64),
        "ACTION" VARCHAR2(64),
        "CLIENT_ID" VARCHAR2(64),
        "FORCE_MATCHING_SIGNATURE" NUMBER,
        "BLOCKING_SESSION" NUMBER,
        "BLOCKING_SESSION_SERIAL#" NUMBER,
        "XID" RAW(8),
        "CONSUMER_GROUP_ID" NUMBER,
        "PLSQL_ENTRY_OBJECT_ID" NUMBER,
        "PLSQL_ENTRY_SUBPROGRAM_ID" NUMBER,
        "PLSQL_OBJECT_ID" NUMBER,
        "PLSQL_SUBPROGRAM_ID" NUMBER,
        "QC_SESSION_SERIAL#" NUMBER,
        "REMOTE_INSTANCE#" NUMBER,
        "SQL_PLAN_LINE_ID" NUMBER,
        "SQL_PLAN_OPERATION#" NUMBER,
        "SQL_PLAN_OPTIONS#" NUMBER,
        "SQL_EXEC_ID" NUMBER,
        "SQL_EXEC_START" DATE,
        "TIME_MODEL" NUMBER,
        "TOP_LEVEL_SQL_ID" VARCHAR2(13),
        "TOP_LEVEL_SQL_OPCODE" NUMBER,
        "CURRENT_ROW#" NUMBER,
        "FLAGS" NUMBER,
        "BLOCKING_INST_ID" NUMBER,
        "ECID" VARCHAR2(64),
        "TM_DELTA_TIME" NUMBER,
        "TM_DELTA_CPU_TIME" NUMBER,
        "TM_DELTA_DB_TIME" NUMBER,
        "DELTA_TIME" NUMBER,
        "DELTA_READ_IO_REQUESTS" NUMBER,
        "DELTA_WRITE_IO_REQUESTS" NUMBER,
        "DELTA_READ_IO_BYTES" NUMBER,
        "DELTA_WRITE_IO_BYTES" NUMBER,
        "DELTA_INTERCONNECT_IO_BYTES" NUMBER,
        "PGA_ALLOCATED" NUMBER,
        "TEMP_SPACE_ALLOCATED" NUMBER,
        "TOP_LEVEL_CALL#" NUMBER,
        "MACHINE" VARCHAR2(64),
        "PORT" NUMBER,
        "DBREPLAY_FILE_ID" NUMBER,
        "DBREPLAY_CALL_COUNTER" NUMBER,
        "PX_FLAGS" NUMBER,
         CONSTRAINT "WRH$_ACTIVE_SESSION_HISTORY_PK" PRIMARY KEY ("DBID", "SNAP_ID", "INSTANCE_NU
MBER", "SAMPLE_ID", "SESSION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"  LOCAL
 (PARTITION "WRH$_ACTIVE_2127610945_35001"
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" ,
 PARTITION "WRH$_ACTIVE_2127610945_47787"
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" ,
 PARTITION "WRH$_ACTIVE_2127610945_49713"
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" ,
 PARTITION "WRH$_ACTIVE_SES_MXDB_MXSN"
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" )  ENABLE
   ) PCTFREE 1 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
  PARTITION BY RANGE ("DBID","SNAP_ID")
 (PARTITION "WRH$_ACTIVE_2127610945_35001"  VALUES LESS THAN (2127610945, 36592)
  PCTFREE 1 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" ,
 PARTITION "WRH$_ACTIVE_2127610945_47787"  VALUES LESS THAN (2127610945, 49713)
  PCTFREE 1 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" ,
 PARTITION "WRH$_ACTIVE_2127610945_49713"  VALUES LESS THAN (2127610945, MAXVALUE)
  PCTFREE 1 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" ,
 PARTITION "WRH$_ACTIVE_SES_MXDB_MXSN"  VALUES LESS THAN (MAXVALUE, MAXVALUE)
  PCTFREE 1 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" )  ENABLE ROW MOVEMENT

看了主鍵和分割槽鍵的設定後，在查詢里加上dbid，snap_id的範圍，以及instance_number，速度快了不少而且大大降低對資料庫的壓力。