核心数据库的快照保留时间提高到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,速度快了不少而且大大降低对数据库的压力。
看一下 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,速度快了不少而且大大降低对数据库的压力。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26239116/viewspace-1425339/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26239116/viewspace-1425339/