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>
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
4096 Columns1 week ago
-
-
-
-
-
-
-
Oracle Cloud & Third party tools4 years ago
-
-
-
Moving Sideways8 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment