Wednesday, June 8, 2011

How to see what session's are doing from V$ACTIVE_SESSION_HISTORY in oracle database

How to see what session's are doing from V$ACTIVE_SESSION_HISTORY in oracle database?
==========================================================================

V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database.
It contains snapshots of active database sessions taken once a second.
A database session is considered active if it was on the CPU or was waiting for
an event that didn't belong to the Idle wait class. Refer to the V$EVENT_NAME
view for more information on wait classes.

This view contains one row for each active session per sample and returns the
latest session sample rows first. A majority of the columns describing the session
in the active session history are present in the V$SESSION view.


v$active_session_history only holds data for last half an hour, to view historical information
of more than half an hour, information will be available in DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active
session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY



SELECT sql_id, blocking_session, session_id, event
FROM v$active_session_history
where session_id=2958


/* Formatted on 2011/06/08 14:45 (Formatter Plus v4.8.8) */
SELECT sql_id, blocking_session, session_id, event
FROM dba_hist_active_sess_history
where blocking_session=4404



example:-


SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 8 14:54:04 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn eraadmin@live
Enter password:
Connected.
SQL>
SQL>
SQL> set linesize 1000
SQL>
SQL> SELECT sql_id, blocking_session, session_id, event
2 FROM v$active_session_history
3 where session_id=2958 ;

SQL_ID BLOCKING_SESSION SESSION_ID EVENT
------------- ---------------- ---------- --------------------------------------
--------------------------
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd

SQL_ID BLOCKING_SESSION SESSION_ID EVENT
------------- ---------------- ---------- --------------------------------------
--------------------------
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq: Table Q Get Keys
5gsdgs2syh71d 2958 latch free
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
f97da85fghdwh 2958 PX Deq Credit: send blkd
5bd61nfc8m84n 2958

SQL_ID BLOCKING_SESSION SESSION_ID EVENT
------------- ---------------- ---------- --------------------------------------
--------------------------
5bd61nfc8m84n 2958
5bd61nfc8m84n 2958
5bd61nfc8m84n 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd

31 rows selected.


SQL>
SQL>
SQL>
SQL> select * from table(dbms_xplan.display_cursor('2ffckx228w8gm')) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 2ffckx228w8gm, child number 0
-------------------------------------
SELECT DISTINCT b.cacmpcde || ' - ' || b.CACMPNAM,b.cacmpcde || ' - ' ||
b.CACMPNAM FROM SYRIGHTS A,SYPARMAS B WHERE A.USERCODE=:1 AND
A.COMPCODE=B.CACMPCDE ORDER BY 1

Plan hash value: 1114147443

--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 154 (100)|
|
| 1 | SORT ORDER BY | | 2580 | 87720 | 154 (2)| 00:00
:02 |
| 2 | HASH UNIQUE | | 2580 | 87720 | 153 (2)| 00:00
:02 |
| 3 | NESTED LOOPS | | 2580 | 87720 | 151 (0)| 00:00
:02 |
| 4 | TABLE ACCESS FULL| SYPARMAS | 74 | 1702 | 3 (0)| 00:00
:01 |
|* 5 | INDEX RANGE SCAN | PK_SYRIGHTS4 | 35 | 385 | 2 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
5 - access("A"."COMPCODE"="B"."CACMPCDE" AND "A"."USERCODE"=:1)


24 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('5gsdgs2syh71d')) ;


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 5gsdgs2syh71d, child number 0
-------------------------------------
SELECT DISTINCT ACTNUM FROM STTRPROF ORDER BY 1

Plan hash value: 240056120

--------------------------------------------------------------------------------
-----------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-----------------------------------------------
| 0 | SELECT STATEMENT | | | | | 18877
(100)| | | | |
| 1 | PX COORDINATOR | | | | |
| | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
| 2 | PX SEND QC (ORDER) | :TQ10001 | 229K| 2690K| | 13998
(5)| 00:02:48 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT UNIQUE | | 229K| 2690K| 48M| 13998
(5)| 00:02:48 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,00 | PCWC | |
|* 7 | INDEX FAST FULL SCAN| PK_STTRPROF | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
-----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)


24 rows selected.

SQL>

No comments: