ASH practice 1 of Oracle10G
ASH is a new feature of Oracle 10g, which is very valuable for DBA. It is based on V $sssion and samples once a second,
Records events waiting for an active session. The information of ASH can be viewed through two kinds of data dictionary views. One part is v $active? Session? History, in which the data is relatively new;
The other part is DBA, historical data.
-----1 > V $active? Session? History is a synonym of public from the DBA? Objects Query
SQL> select owner,object_name,object_type from dba_objects where object_name='V$ACTIVE_SESSION_HISTORY'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ -------------------------------------------------------------------------------- ------------------- PUBLIC V$ACTIVE_SESSION_HISTORY SYNONYM
----2 > go to the synonym view to see where it comes from and find v $active? Session? History
SQL> select * from dba_synonyms where synonym_name='V$ACTIVE_SESSION_HISTORY'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- PUBLIC V$ACTIVE_SESSION_HISTORY SYS V_$ACTIVE_SESSION_HISTORY
-----3 > View V $active? Session? History, which is a view.
SQL> select owner,object_name,object_type from dba_objects where object_name='V_$ACTIVE_SESSION_HISTORY'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ -------------------------------------------------------------------------------- ------------------- SYS V_$ACTIVE_SESSION_HISTORY VIEW
----4 > check the view code and find out whether the base table is v $active session history, and return to the origin of the problem.
SQL> select text from dba_views where view_name='V_$ACTIVE_SESSION_HISTORY'; TEXT -------------------------------------------------------------------------------- select "SAMPLE_ID","SAMPLE_TIME","SESSION_ID","SESSION_SERIAL#","USER_ID","SQL_ID","SQL_CHILD_NUMBER","SQL_PLAN_HASH_VALUE","FORCE_MATCHING_SIGNATURE","SQL_OPCODE","SERVICE_HASH","SESSION_TYPE","SESSION_STATE","QC_SESSION_ID","QC_INSTANCE_ID","BLOCKING_SESSION","BLOCKING_SESSION_STATUS","BLOCKING_SESSION_SERIAL#","EVENT","EVENT_ID","EVENT#","SEQ#","P1TEXT","P1","P2TEXT","P2","P3TEXT","P3","WAIT_CLASS","WAIT_CLASS_ID","WAIT_TIME","TIME_WAITED","XID","CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","PROGRAM","MODULE","ACTION","CLIENT_ID" from v$active_session_history
----5 > it suddenly occurred to me that the dynamic performance view is memory based.
View v $fixed view? Definition, which is based on GV $active? Session? History.
SQL> select VIEW_DEFINITION from V$fixed_view_definition where view_name='V$ACTIVE_SESSION_HISTORY'; VIEW_DEFINITION -------------------------------------------------------------------------------- SELECT sample_id, sample_time, session_id, session_serial#, user_id, sql_id, sql_child_number, sql_plan_hash_value, force_matching_signature, sql_opcode, service_hash, session_type, session_state, qc_session_id, qc_instance_id, blocking_session, blocking_session_status, blocking_session_serial#, event, event_id, event#, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, time_waited, xid, current_obj#, current_file#, current_block#, program, module, action, client_id FROM GV$ACTIVE_SESSION_HISTORY WHERE inst_id = USERENV('INSTANCE')
----6 > find that GV $active? Session? History is a synonym
SQL> select owner,object_name,object_type from dba_objects where object_name='GV$ACTIVE_SESSION_HISTORY'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ -------------------------------------------------------------------------------- ------------------- PUBLIC GV$ACTIVE_SESSION_HISTORY SYNONYM
----7 > view the V $fixed view definition, but the maximum length of the field is 4000, which means that if the definition of this view exceeds 4000, you will not see the complete content
SQL> desc v$fixed_view_definition; Name Type Nullable Default Comments --------------- -------------- -------- ------- -------- VIEW_NAME VARCHAR2(30) Y VIEW_DEFINITION VARCHAR2(4000) Y
---8 > the base table is still not found. In another way, you can try to find out if there is a similar situation with sql view.
select VIEW_DEFINITION from v$fixed_view_definition where view_name=upper('v$fixed_view_definition'); VIEW_DEFINITION -------------------------------------------------------------------------------- select VIEW_NAME , VIEW_DEFINITION from GV$FIXED_VIEW_DEFINITION where inst_id = USERENV('Instance')
---9 > found the definition and base table, and view the column definition.
SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name=upper('gv$fixed_view_definition'); VIEW_DEFINITION -------------------------------------------------------------------------------- select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx = t.indx
--10 > it can be obtained from the execution plan. Check the GV $active session history directly, and finally find out
SQL> explain plan for select * from GV$ACTIVE_SESSION_HISTORY; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2905781256 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 328 | 216K| | 1 | VIEW | GV$ACTIVE_SESSION_HISTORY | 328 | 216K| | 2 | NESTED LOOPS | | 328 | 233K| | 3 | FIXED TABLE FULL | X$KEWASH | 100 | 4800 | |* 4 | FIXED TABLE FIXED INDEX| X$ASH (ind:1) | 3 | 2046 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPL "S"."SAMPLE_TIME"="A"."SAMPLE_TIME") 17 rows selected