ASH production practice I of Oracle10G

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






  

Tags: Linux SQL Session Oracle

Posted on Wed, 04 Dec 2019 13:37:21 -0500 by robotman321