删除Baseline使用DROP_BASELINE过程,删除时可以通过cascade参数选择是否将其关联的Snapshots级别进行删除,例如:
SQL> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => ¨am_baseline¨,
3 cascade => true);
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> select * from dba_hist_baseline;
no rows selected
SQL> select * from dba_hist_snapshot where snap_id between 7550 and 7660;
no rows selected
如上例中所示,删除时指定了cascade参数值为true,对应的snap也被级联删除了。
不管是EM也好,或是前面演示中使用的awr*.sql脚本也好,实质都是访问ORACLE中的部分相关视图来生成统计数据,因此如果DBA对自己的理解能力有足够的自信,也可以直接查询动态性能视图(或相关数据字典)的方式来获取自己想要的那部分性能数据。ORACLE将这部分性能统计数据保存在DBA_HIST开头的数据字典中,要查询当前实例所有能够访问的DBA_HIST字典,可以通过下列语句:
SQL> select * from dict where table_name like ¨DBA_HIST%¨;
TABLE_NAME COMMENTS
------------------------------ --------------------------------------------------------------------------------
DBA_HIST_DATABASE_INSTANCE Database Instance Information
DBA_HIST_SNAPSHOT Snapshot Information
DBA_HIST_SNAP_ERROR Snapshot Error Information
DBA_HIST_BASELINE Baseline Metadata Information
DBA_HIST_WR_CONTROL Workload Repository Control Information
DBA_HIST_DATAFILE Names of Datafiles
DBA_HIST_FILESTATXS Datafile Historical Statistics Information
DBA_HIST_TEMPFILE Names of Temporary Datafiles
DBA_HIST_TEMPSTATXS Temporary Datafile Historical Statistics Information
DBA_HIST_COMP_IOSTAT I/O stats aggregated on component level
DBA_HIST_SQLSTAT SQL Historical Statistics Information
DBA_HIST_SQLTEXT SQL Text
......................
该视图由ASH自动维护,以每秒一次的频率收集当前系统中活动session的信息。虽然说是记录SESSION的历史记录,不过该视图与V$SESSION还是有差异的。
SQL> desc v$active_session_history;
Name Type Nullable Default Comments
------------------------- ------------ -------- ------- --------
SAMPLE_ID NUMBER Y
SAMPLE_TIME TIMESTAMP(3) Y
SESSION_ID NUMBER Y
SESSION_SERIAL# NUMBER Y
USER_ID NUMBER Y
SQL_ID VARCHAR2(13) Y
SQL_CHILD_NUMBER NUMBER Y
SQL_PLAN_HASH_VALUE NUMBER Y
FORCE_MATCHING_SIGNATURE NUMBER Y
SQL_OPCODE NUMBER Y
PLSQL_ENTRY_OBJECT_ID NUMBER Y
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER Y
PLSQL_OBJECT_ID NUMBER Y
PLSQL_SUBPROGRAM_ID NUMBER Y
SERVICE_HASH NUMBER Y
SESSION_TYPE VARCHAR2(10) Y
SESSION_STATE VARCHAR2(7) Y
QC_SESSION_ID NUMBER Y
QC_INSTANCE_ID NUMBER Y
BLOCKING_SESSION NUMBER Y
BLOCKING_SESSION_STATUS VARCHAR2(11) Y
BLOCKING_SESSION_SERIAL# NUMBER Y
EVENT VARCHAR2(64) Y
EVENT_ID NUMBER Y
EVENT# NUMBER Y
SEQ# NUMBER Y
P1TEXT VARCHAR2(64) Y
P1 NUMBER Y
P2TEXT VARCHAR2(64) Y
P2 NUMBER Y
P3TEXT VARCHAR2(64) Y
P3 NUMBER Y
WAIT_CLASS VARCHAR2(64) Y
WAIT_CLASS_ID NUMBER Y
WAIT_TIME NUMBER Y
TIME_WAITED NUMBER Y
XID RAW(8) Y
CURRENT_OBJ# NUMBER Y
CURRENT_FILE# NUMBER Y
CURRENT_BLOCK# NUMBER Y
PROGRAM VARCHAR2(48) Y
MODULE VARCHAR2(48) Y
ACTION VARCHAR2(32) Y