|
有一些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视图的基表:
- -- 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;
复制代码
|
|