数据库自动化运维的调优

botang · 发表于 2020-1-4 11:30:21

有一些DBA（统计信息相关）视图肯定根V$视图有千丝万缕的联系：

	TABLE_NAME	COMMENTS
1	DBA_HIST_ACTIVE_SESS_HISTORY	Active Session Historical Statistics Information
2	V$MAX_ACTIVE_SESS_TARGET_MTH	Synonym for V_$MAX_ACTIVE_SESS_TARGET_MTH
3	GV$ACTIVE_SESSION_HISTORY	Synonym for GV_$ACTIVE_SESSION_HISTORY
4	GV$ACTIVE_SESS_POOL_MTH	Synonym for GV_$ACTIVE_SESS_POOL_MTH
5	GV$MAX_ACTIVE_SESS_TARGET_MTH	Synonym for GV_$MAX_ACTIVE_SESS_TARGET_MTH
6	V$ACTIVE_SESSION_HISTORY	Synonym for V_$ACTIVE_SESSION_HISTORY
7	V$ACTIVE_SESS_POOL_MTH	Synonym for V_$ACTIVE_SESS_POOL_MTH

V$ACTIVE_SESSION_HISTORY背后的字段：

create or replace view v_$active_session_history as
select "SAMPLE_ID","SAMPLE_TIME","IS_AWR_SAMPLE","SESSION_ID","SESSION_SERIAL#","SESSION_TYPE","FLAGS","USER_ID","SQL_ID","IS_SQLID_CURRENT","SQL_CHILD_NUMBER","SQL_OPCODE","SQL_OPNAME","FORCE_MATCHING_SIGNATURE","TOP_LEVEL_SQL_ID","TOP_LEVEL_SQL_OPCODE","SQL_PLAN_HASH_VALUE","SQL_PLAN_LINE_ID","SQL_PLAN_OPERATION","SQL_PLAN_OPTIONS","SQL_EXEC_ID","SQL_EXEC_START","PLSQL_ENTRY_OBJECT_ID","PLSQL_ENTRY_SUBPROGRAM_ID","PLSQL_OBJECT_ID","PLSQL_SUBPROGRAM_ID","QC_INSTANCE_ID","QC_SESSION_ID","QC_SESSION_SERIAL#","PX_FLAGS","EVENT","EVENT_ID","EVENT#","SEQ#","P1TEXT","P1","P2TEXT","P2","P3TEXT","P3","WAIT_CLASS","WAIT_CLASS_ID","WAIT_TIME","SESSION_STATE","TIME_WAITED","BLOCKING_SESSION_STATUS","BLOCKING_SESSION","BLOCKING_SESSION_SERIAL#","BLOCKING_INST_ID","BLOCKING_HANGCHAIN_INFO","CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#","TOP_LEVEL_CALL_NAME","CONSUMER_GROUP_ID","XID","REMOTE_INSTANCE#","TIME_MODEL","IN_CONNECTION_MGMT","IN_PARSE","IN_HARD_PARSE","IN_SQL_EXECUTION","IN_PLSQL_EXECUTION","IN_PLSQL_RPC","IN_PLSQL_COMPILATION","IN_JAVA_EXECUTION","IN_BIND","IN_CURSOR_CLOSE","IN_SEQUENCE_LOAD","CAPTURE_OVERHEAD","REPLAY_OVERHEAD","IS_CAPTURED","IS_REPLAYED","SERVICE_HASH","PROGRAM","MODULE","ACTION","CLIENT_ID","MACHINE","PORT","ECID","DBREPLAY_FILE_ID","DBREPLAY_CALL_COUNTER","TM_DELTA_TIME","TM_DELTA_CPU_TIME","TM_DELTA_DB_TIME","DELTA_TIME","DELTA_READ_IO_REQUESTS","DELTA_WRITE_IO_REQUESTS","DELTA_READ_IO_BYTES","DELTA_WRITE_IO_BYTES","DELTA_INTERCONNECT_IO_BYTES","PGA_ALLOCATED","TEMP_SPACE_ALLOCATED" from v$active_session_history;

WR?$ 一共只有4个品种（H I M R ），来自V$视图，是对应DBA视图的基表：

QQ图片20191229214415.png

-- Create table
create table WRH$_ACTIVE_SESSION_HISTORY
(
snap_id NUMBER not null,
dbid NUMBER not null,
instance_number NUMBER not null,
sample_id NUMBER not null,
sample_time TIMESTAMP(3) not null,
session_id NUMBER not null,
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
)
partition by range (DBID, SNAP_ID)
(
partition WRH$_ACTIVE_1554722616_0 values less than (1554722616, MAXVALUE)
tablespace SYSAUX
pctfree 1
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition WRH$_ACTIVE_SES_MXDB_MXSN values less than (MAXVALUE, MAXVALUE)
tablespace SYSAUX
pctfree 1
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table WRH$_ACTIVE_SESSION_HISTORY
add constraint WRH$_ACTIVE_SESSION_HISTORY_PK primary key (DBID, SNAP_ID, INSTANCE_NUMBER, SAMPLE_ID, SESSION_ID)
using index
local;
alter index WRH$_ACTIVE_SESSION_HISTORY_PK nologging;

复制代码

select * from DBA_HIST_SNAPSHOT;

复制代码