Your friendly custom session browser

Your friendly custom session browser

Every DBA sooner or later needs to do some session browsing, trying to find out clues about blocking sessions, check execution plans, better understand resource consumption and many other useful informations. All of that quickly enough and without waiting web pages to load.

Every DBA sooner or later start to collect useful queries and tools for his day to day work. Me aswell, of course. In many years, I’ve collected thousands, and some of them are now parts of my custom session browser.

I use Oracle SQL Developer since version 2.something because I personally find Toad for Oracle too expensive for what it gives. With SQL Developer I started to build a report to relieve my day to day work, one query at a time. Many of the queries in my reports came from other great DBAs and books, and I would like to thank them all (I will try to find them to give credits).

Session List

It all start creating a new report. Under Navigate menu, choose Reports to open the related tab. Right-click User Defined Reports and select New Report. A big window will appear where you have to put a Name for the report,  choose Table in the dropdown list Style so the report will be shown like a table. and a query inside the text area named SQL. The following query will retrieve all the non-background non-internal sessions. When done, click Apply and the report will be shown under the tree list.

WITH
 vs AS
 (
 SELECT
 rownum rnum,
 service_name,
 inst_id,
 sid,
 serial#,
 logon_time,
 status,
 username,
 EVENT,
 WAIT_CLASS,
 last_call_et,
 command,
 machine,
 osuser,
 module,
 action,
 resource_consumer_group,
 client_info,
 client_identifier,
 type,
 terminal,
 SQL_ID,
 SADDR,
 program,
 PADDR,
 SQL_CHILD_NUMBER
 FROM
 gv$session
 )
SELECT
 vs.inst_id,
 vs.sid ,
 serial# serial,
 vs.logon_time,
 vs.username "Username",
 CASE
 WHEN vs.status = 'ACTIVE'
 THEN (NUMTODSINTERVAL(last_call_et,'SECOND'))
 ELSE NULL
 END "Seconds in Wait (+DD HH:MI:SS)",
 lower(vs.status) "Status",
 EVENT,
 WAIT_CLASS,
 DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,
 'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,
 'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ',
 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,
 'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,
 'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,
 'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,
 'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,
 'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT',
 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXEC', 48,'SET XACTN', 49,
 'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,
 'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,
 'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,
 'ANALYZE TAB', 63,'ANALYZE IX', 64,'ANALYZE CLUS', 65,'CRE PROFILE', 66,
 'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,
 'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,
 'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 79,
 'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC',
 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,
 'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', 189, 'MERGE', TO_CHAR(vs.command))
 "Command",
 vs.machine "Machine",
 vs.osuser "OS User",
 vs.program,
 vs.module "Module",
 vs.action "Action",
 vs.resource_consumer_group,
 vs.service_name,
 vs.client_info,
 vs.client_identifier,
 vs.SQL_ID,
 vs.type,
 VS.SADDR AS SESSION_ADDRESS,
 vs.paddr as PROCESS_ADDRESS,
 vs.SQL_CHILD_NUMBER
FROM
 vs
WHERE
 vs.USERNAME IS NOT NULL
AND vs.type <> 'BACKGROUND'
ORDER BY
 2,3,1

From now on, whenever you will left-click on the report name, you will be requested to connect to a database and the query will be run showing the result in a nice table. Of course yhou need to connect with a user with sufficient privilegies.

Child reports

Now things will start to be interesting. Right-click on the report name just created and edit it. Under the Master Report tree, find the section Child Reports. After selecting it, on the right the window will change in an empty area. On the lower, find Add Child…button and click it and give it a name. You just created a child report and you can find it in a branch on the left tree under Child Reports. If you open that branch and select SQL Query you will be able to add a new query. A lower window will show you the parent query (the previous we just copied) for reference.

Do you want to know the rowid where a specific session is stuck on? Put this query in the child box:

 SELECT
 do.object_name AS TABLE_NAME,
 row_wait_obj# AS OBJECT_NUM,
 row_wait_file# AS FILE_NUM,
 row_wait_block# AS BLOCK_NUM,
 row_wait_row# AS ROW_NUM,
 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) AS ROW_ID
 FROM
 gv$session s,
 dba_objects DO
 WHERE
 s.sid = :SID
 AND s.inst_id = :INST_ID
 AND s.ROW_WAIT_OBJ# = do.OBJECT_ID

As you may have already noticed, the :SID and :INST_ID (both in uppercase, remember!) reference parent query columns. This means whenever you select a session (a row in the report), the child query will be executed with :SID and :INST_ID value from the session query.

You can add as many child reports as you want. This is a great way to compact many repetitive queries, expectially to semplify investigating issues, avoiding annoying copy&paste from notes, text files, etc.

Here some other useful queries for your child reports. Feel free to use, adapt and enhance them as you like. Please comment below if yuo want to share your findings and queries.

Query full text

SELECT S.SQL_FULLTEXT
 FROM GV$SQLAREA S
 JOIN GV$SESSION GS
 ON GS.SQL_ID = S.SQL_ID
 WHERE GS.INST_ID = :INST_ID
 AND GS.SID = :SID
 AND GS.SERIAL# = :SERIAL
 AND ROWNUM = 1

Explain Plan

SELECT RPAD('Inst: '
 ||v.inst_id, 9)
 ||' '
 ||RPAD('Child: '
 ||v.child_number, 11) inst_child,
 t.plan_table_output
 FROM gv$sql v,
 TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ADVANCED ALLSTATS LAST', 'inst_id = '
 ||:INST_ID
 ||' AND sql_id = '''||:SQL_ID||''' AND child_number = '||v.child_number)) t
 WHERE v.sql_id = :SQL_ID
 AND v.loaded_versions > 0
 AND NVL(:SQL_ID,'X') <> 'X'
 and v.CHILD_NUMBER = :SQL_CHILD_NUMBER

Blocking Locks

SELECT (
 CASE
 WHEN S2.INST_ID IS NOT NULL
 THEN 'This transaction is waiting =>'
 WHEN S3.INST_ID IS NOT NULL
 THEN 'Waiting for this transaction =>'
 ELSE 'Current locks'
 END ) AS Concurrency ,
 CASE
 WHEN S2.INST_ID IS NOT NULL
 THEN S2.INST_ID
 ELSE S3.INST_ID
 END AS Instance,
 CASE
 WHEN S2.INST_ID IS NOT NULL
 THEN S2.USERNAME
 ELSE S3.USERNAME
 END AS UserName,
 CASE
 WHEN S2.INST_ID IS NOT NULL
 THEN S2.MACHINE
 ELSE S3.MACHINE
 END AS MachineName,
 CASE
 WHEN S2.INST_ID IS NOT NULL
 THEN s2.sql_id
 ELSE (
 CASE
 WHEN S3.INST_ID IS NOT NULL
 AND s3.sql_id IS NULL
 THEN 'COMMIT/ROLLBACK?'
 ELSE s3.sql_id
 END )
 END AS SQL_ID,
 CASE
 WHEN S2.INST_ID IS NOT NULL
 THEN s2.SID
 ELSE s3.SID
 END AS SID,
 CASE
 WHEN S2.INST_ID IS NOT NULL
 THEN s2.SERIAL#
 ELSE s3.SERIAL#
 END AS SERIAL,
 AB.OWNER
 ||'.'
 ||AB.OBJECT_NAME AS BLOCKED_OBJECT,
 LT.NAME AS LOCK_TYPE ,
 LT.ID1_TAG , LT.ID2_TAG ,
 AB2.OWNER
 ||'.'
 ||AB2.OBJECT_NAME AS TAG_2,
 (
 CASE G.LMODE
 WHEN 0
 THEN 'NONE'
 WHEN 1
 THEN 'NULL (NULL)'
 WHEN 2
 THEN 'ROW-SS (SS)'
 WHEN 3
 THEN 'ROW-X (SX)'
 WHEN 4
 THEN 'SHARE (S)'
 WHEN 5
 THEN 'S/ROW-X (SSX)'
 WHEN 6
 THEN 'EXCLUSIVE (X)'
 END) AS LOCK_MODE ,
 (
 CASE G.REQUEST
 WHEN 0
 THEN 'NONE'
 WHEN 1
 THEN 'NULL (NULL)'
 WHEN 2
 THEN 'ROW-SS (SS)'
 WHEN 3
 THEN 'ROW-X (SX)'
 WHEN 4
 THEN 'SHARE (S)'
 WHEN 5
 THEN 'S/ROW-X (SSX)'
 WHEN 6
 THEN 'EXCLUSIVE (X)'
 END) AS LOCK_REQUESTS
 FROM GV$LOCK G
 JOIN V$LOCK_TYPE LT
 ON G.TYPE = LT.TYPE
 LEFT OUTER JOIN ALL_OBJECTS AB
 ON AB.OBJECT_ID = DECODE(LT.ID1_TAG, 'object #',G.ID1, NULL)
 LEFT OUTER JOIN ALL_OBJECTS AB2
 ON AB2.OBJECT_ID = DECODE(LT.ID2_TAG, 'table/partition',G.ID2, NULL)
 LEFT OUTER JOIN GV$LOCK G2
 ON G.ID1 = G2.ID1
 AND G.ID2 = G2.ID2
 AND G.BLOCK >= 1
 AND G2.REQUEST > 0
 LEFT OUTER JOIN GV$SESSION S2
 ON S2.INST_ID = G2.INST_ID
 AND S2.SID = G2.SID
 LEFT OUTER JOIN GV$LOCK G3
 ON G.ID1 = G3.ID1
 AND G.ID2 = G3.ID2
 AND G3.BLOCK >= 1
 AND G.REQUEST > 0
 LEFT OUTER JOIN GV$SESSION S3
 ON S3.INST_ID = G3.INST_ID
 AND S3.SID = G3.SID
 WHERE G.INST_ID = :INST_ID
 AND G.SID = :SID

Transactions

SELECT START_DATE,
 NAME,
 USED_UBLK AS UNDO_BLOCK_USED,
 USED_UREC AS UNDO_RECORDS_USED,
 LOG_IO AS LOGICAL_IO,
 PHY_IO AS PHYSICAL_IO,
 CR_GET AS CONSISTENT_GETS,
 CR_CHANGE AS CONSISTENT_CHANGES
 FROM GV$TRANSACTION
 WHERE SES_ADDR = :SESSION_ADDRESS
 AND INST_ID = :INST_ID

Long Operations

SELECT START_TIME
 ,LAST_UPDATE_TIME
 ,OPNAME AS OPERATION_NAME
 ,TARGET_DESC
 ,SOFAR AS SO_FAR
 ,TOTALWORK AS TOTAL_WORK
 ,CASE WHEN TOTALWORK = 0 THEN 'N/A' ELSE TO_CHAR(SOFAR/TOTALWORK*100,'999.99')||'%' END AS PERC
 ,UNITS AS UNITS_OF_MEASUREMENT
 ,TIME_REMAINING
 ,ELAPSED_SECONDS
 ,CONTEXT
 ,MESSAGE
 FROM GV$SESSION_LONGOPS
 WHERE INST_ID = :INST_ID
 AND SID = :SID
 AND SERIAL# = :SERIAL
 ORDER BY LAST_UPDATE_TIME DESC

Bind Variables

SELECT
 NAME,
 POSITION,
 DATATYPE_STRING AS DATA_TYPE,
 PRECISION,
 SCALE,
 VALUE_STRING AS VALUE
 FROM v$sql_bind_capture WHERE sql_id=:SQL_ID

Active Session History

SELECT NVL(a.event, 'ON CPU') AS event,
 A.WAIT_CLASS ,
 A.SESSION_TYPE ,
 A.SESSION_STATE,
 A.PROGRAM,
 A.MODULE,
 A.ACTION,
 COUNT(*) AS total_wait_time
 FROM Gv$active_session_history a
 WHERE a.session_id = :SID
 and a.SESSION_SERIAL# = :SERIAL
 AND A.INST_ID = :INST_ID
 GROUP BY NVL(a.event, 'ON CPU'),
 A.WAIT_CLASS ,
 A.SESSION_TYPE ,
 A.SESSION_STATE,
 A.PROGRAM,
 A.MODULE,
 A.ACTION
 ORDER BY total_wait_time DESC

Isolation Level

SELECT
 *
 FROM
 V$SES_OPTIMIZER_ENV v
 WHERE
 v.sid = :SID
 AND NAME = 'transaction_isolation_level'
 ORDER BY
 v.name

Session Current Locks

select
 nvl(S.USERNAME,'Internal') username,s.SERIAL#,
 L.SID,
 nvl(S.TERMINAL,'None') terminal,
 decode(command,
 0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
 decode(command,
 0,'BACKGROUND',
 1,'Create Table',
 2,'INSERT',
 3,'SELECT',
 4,'CREATE CLUSTER',
 5,'ALTER CLUSTER',
 6,'UPDATE',
 7,'DELETE',
 8,'DROP',
 9,'CREATE INDEX',
 10,'DROP INDEX',
 11,'ALTER INDEX',
 12,'DROP TABLE',
 13,'CREATE SEQUENCE',
 14,'ALTER SEQUENCE',
 15,'ALTER TABLE',
 16,'DROP SEQUENCE',
 17,'GRANT',
 18,'REVOKE',
 19,'CREATE SYNONYM',
 20,'DROP SYNONYM',
 21,'CREATE VIEW',
 22,'DROP VIEW',
 23,'VALIDATE INDEX',
 24,'CREATE PROCEDURE',
 25,'ALTER PROCEDURE',
 26,'LOCK TABLE',
 27,'NO OPERATION',
 28,'RENAME',
 29,'COMMENT',
 30,'AUDIT',
 31,'NOAUDIT',
 32,'CREATE EXTERNAL DATABASE',
 33,'DROP EXTERNAL DATABASE',
 34,'CREATE DATABASE',
 35,'ALTER DATABASE',
 36,'CREATE ROLLBACK SEGMENT',
 37,'ALTER ROLLBACK SEGMENT',
 38,'DROP ROLLBACK SEGMENT',
 39,'CREATE TABLESPACE',
 40,'ALTER TABLESPACE',
 41,'DROP TABLESPACE',
 42,'ALTER SESSION',
 43,'ALTER USER',
 44,'COMMIT',
 45,'ROLLBACK',
 46,'SAVEPOINT',
 47,'PL/SQL EXECUTE',
 48,'SET TRANSACTION',
 49,'ALTER SYSTEM SWITCH LOG',
 50,'EXPLAIN',
 51,'CREATE USER',
 52,'CREATE ROLE',
 53,'DROP USER',
 54,'DROP ROLE',
 55,'SET ROLE',
 56,'CREATE SCHEMA',
 57,'CREATE CONTROL FILE',
 58,'ALTER TRACING',
 59,'CREATE TRIGGER',
 60,'ALTER TRIGGER',
 61,'DROP TRIGGER',
 62,'ANALYZE TABLE',
 63,'ANALYZE INDEX',
 64,'ANALYZE CLUSTER',
 65,'CREATE PROFILE',
 66,'DROP PROFILE',
 67,'ALTER PROFILE',
 68,'DROP PROCEDURE',
 69,'DROP PROCEDURE',
 70,'ALTER RESOURCE COST',
 71,'CREATE SNAPSHOT LOG',
 72,'ALTER SNAPSHOT LOG',
 73,'DROP SNAPSHOT LOG',
 74,'CREATE SNAPSHOT',
 75,'ALTER SNAPSHOT',
 76,'DROP SNAPSHOT',
 79,'ALTER ROLE',
 85,'TRUNCATE TABLE',
 86,'TRUNCATE CLUSTER',
 87,'-',
 88,'ALTER VIEW',
 89,'-',
 90,'-',
 91,'CREATE FUNCTION',
 92,'ALTER FUNCTION',
 93,'DROP FUNCTION',
 94,'CREATE PACKAGE',
 95,'ALTER PACKAGE',
 96,'DROP PACKAGE',
 97,'CREATE PACKAGE BODY',
 98,'ALTER PACKAGE BODY',
 99,'DROP PACKAGE BODY',
 189,'MERGE',
 command||' - ???') COMMAND,
 decode(L.LMODE,1,'No Lock',
 2,'Row Share',
 3,'Row Exclusive',
 4,'Share',
 5,'Share Row Exclusive',
 6,'Exclusive','NONE') lmode,
 decode(L.REQUEST,1,'No Lock',
 2,'Row Share',
 3,'Row Exclusive',
 4,'Share',
 5,'Share Row Exclusive',
 6,'Exclusive','NONE') request,
 l.id1||'-'||l.id2 Laddr,
 l.type||' - '||
 decode(l.type,
 'BL','Buffer hash table instance lock',
 'CF',' Control file schema global enqueue lock',
 'CI','Cross-instance function invocation instance lock',
 'CS','Control file schema global enqueue lock',
 'CU','Cursor bind lock',
 'DF','Data file instance lock',
 'DL','Direct loader parallel index create',
 'DM','Mount/startup db primary/secondary instance lock',
 'DR','Distributed recovery process lock',
 'DX','Distributed transaction entry lock',
 'FI','SGA open-file information lock',
 'FS','File set lock',
 'HW','Space management operations on a specific segment lock',
 'IN','Instance number lock',
 'IR','Instance recovery serialization global enqueue lock',
 'IS','Instance state lock',
 'IV','Library cache invalidation instance lock',
 'JQ','Job queue lock',
 'KK','Thread kick lock',
 'MB','Master buffer hash table instance lock',
 'MM','Mount definition gloabal enqueue lock',
 'MR','Media recovery lock',
 'PF','Password file lock',
 'PI','Parallel operation lock',
 'PR','Process startup lock',
 'PS','Parallel operation lock',
 'RE','USE_ROW_ENQUEUE enforcement lock',
 'RT','Redo thread global enqueue lock',
 'RW','Row wait enqueue lock',
 'SC','System commit number instance lock',
 'SH','System commit number high water mark enqueue lock',
 'SM','SMON lock',
 'SN','Sequence number instance lock',
 'SQ','Sequence number enqueue lock',
 'SS','Sort segment lock',
 'ST','Space transaction enqueue lock',
 'SV','Sequence number value lock',
 'TA','Generic enqueue lock',
 'TD','DDL enqueue lock',
 'TE','Extend-segment enqueue lock',
 'TM','DML enqueue lock',
 'TO','Temporary Table Object Enqueue',
 'TT','Temporary table enqueue lock',
 'TX','Transaction enqueue lock',
 'UL','User supplied lock',
 'UN','User name lock',
 'US','Undo segment DDL lock',
 'WL','Being-written redo log instance lock',
 'WS','Write-atomic-log-switch global enqueue lock',
 'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
 'New block allocation enqueue lock (ID2=1)'),
 'LA','Library cache lock instance lock (A=namespace)',
 'LB','Library cache lock instance lock (B=namespace)',
 'LC','Library cache lock instance lock (C=namespace)',
 'LD','Library cache lock instance lock (D=namespace)',
 'LE','Library cache lock instance lock (E=namespace)',
 'LF','Library cache lock instance lock (F=namespace)',
 'LG','Library cache lock instance lock (G=namespace)',
 'LH','Library cache lock instance lock (H=namespace)',
 'LI','Library cache lock instance lock (I=namespace)',
 'LJ','Library cache lock instance lock (J=namespace)',
 'LK','Library cache lock instance lock (K=namespace)',
 'LL','Library cache lock instance lock (L=namespace)',
 'LM','Library cache lock instance lock (M=namespace)',
 'LN','Library cache lock instance lock (N=namespace)',
 'LO','Library cache lock instance lock (O=namespace)',
 'LP','Library cache lock instance lock (P=namespace)',
 'LS','Log start/log switch enqueue lock',
 'PA','Library cache pin instance lock (A=namespace)',
 'PB','Library cache pin instance lock (B=namespace)',
 'PC','Library cache pin instance lock (C=namespace)',
 'PD','Library cache pin instance lock (D=namespace)',
 'PE','Library cache pin instance lock (E=namespace)',
 'PF','Library cache pin instance lock (F=namespace)',
 'PG','Library cache pin instance lock (G=namespace)',
 'PH','Library cache pin instance lock (H=namespace)',
 'PI','Library cache pin instance lock (I=namespace)',
 'PJ','Library cache pin instance lock (J=namespace)',
 'PL','Library cache pin instance lock (K=namespace)',
 'PK','Library cache pin instance lock (L=namespace)',
 'PM','Library cache pin instance lock (M=namespace)',
 'PN','Library cache pin instance lock (N=namespace)',
 'PO','Library cache pin instance lock (O=namespace)',
 'PP','Library cache pin instance lock (P=namespace)',
 'PQ','Library cache pin instance lock (Q=namespace)',
 'PR','Library cache pin instance lock (R=namespace)',
 'PS','Library cache pin instance lock (S=namespace)',
 'PT','Library cache pin instance lock (T=namespace)',
 'PU','Library cache pin instance lock (U=namespace)',
 'PV','Library cache pin instance lock (V=namespace)',
 'PW','Library cache pin instance lock (W=namespace)',
 'PX','Library cache pin instance lock (X=namespace)',
 'PY','Library cache pin instance lock (Y=namespace)',
 'PZ','Library cache pin instance lock (Z=namespace)',
 'QA','Row cache instance lock (A=cache)',
 'QB','Row cache instance lock (B=cache)',
 'QC','Row cache instance lock (C=cache)',
 'QD','Row cache instance lock (D=cache)',
 'QE','Row cache instance lock (E=cache)',
 'QF','Row cache instance lock (F=cache)',
 'QG','Row cache instance lock (G=cache)',
 'QH','Row cache instance lock (H=cache)',
 'QI','Row cache instance lock (I=cache)',
 'QJ','Row cache instance lock (J=cache)',
 'QL','Row cache instance lock (K=cache)',
 'QK','Row cache instance lock (L=cache)',
 'QM','Row cache instance lock (M=cache)',
 'QN','Row cache instance lock (N=cache)',
 'QO','Row cache instance lock (O=cache)',
 'QP','Row cache instance lock (P=cache)',
 'QQ','Row cache instance lock (Q=cache)',
 'QR','Row cache instance lock (R=cache)',
 'QS','Row cache instance lock (S=cache)',
 'QT','Row cache instance lock (T=cache)',
 'QU','Row cache instance lock (U=cache)',
 'QV','Row cache instance lock (V=cache)',
 'QW','Row cache instance lock (W=cache)',
 'QX','Row cache instance lock (X=cache)',
 'QY','Row cache instance lock (Y=cache)',
 'QZ','Row cache instance lock (Z=cache)',
 'AE','Media recovery',
 '????') Lockt
 from GV$LOCK L,
 GV$SESSION S,
 SYS.USER$ U1,
 SYS.OBJ$ T1
 where L.SID = S.SID
 AND L.INST_ID = S.INST_ID
 and T1.OBJ# = decode(L.ID2,0,L.ID1,1)
 and U1.USER# = T1.OWNER#
 and S.TYPE != 'BACKGROUND'
 AND L.SID = :SID
 AND L.INST_ID = :INST_ID
 order by 1,3,2,5

SQL Informations

SELECT
 INST_ID,
 LAST_ACTIVE_TIME,
 IS_REOPTIMIZABLE,
 IS_RESOLVED_ADAPTIVE_PLAN,
 SQL_PLAN_BASELINE,
 EXECUTIONS,
 LOADED_VERSIONS,
 DISK_READS,
 DIRECT_WRITES,
 BUFFER_GETS,APPLICATION_WAIT_TIME,
 CONCURRENCY_WAIT_TIME,
 CLUSTER_WAIT_TIME,
 USER_IO_WAIT_TIME,
 PLSQL_EXEC_TIME,
 ROWS_PROCESSED,
 SERVICE,
 FULL_PLAN_HASH_VALUE,
 MODULE,
 REMOTE,
 IM_SCANS,
 SQL_ID,
 SQL_FULLTEXT
 FROM GV$SQL
 where sql_id=:SQL_ID
 AND INST_ID = :INST_ID

Process Informations

select * from gv$process
 where inst_id = :INST_ID
 and ADDR = :PROCESS_ADDRESS

Optimizer Session Environment

SELECT * FROM GV$SES_OPTIMIZER_ENV
 WHERE INST_ID = :INST_ID
 AND SID = :SID

Parallel Processes

select * from gV$PX_SESSION gps
 where gps.inst_id = :INST_ID
 and gps.sid = :SID
 and gps.serial# = :SERIAL

Client Connection Informations

select * from GV$SESSION_CONNECT_INFO
 WHERE INST_ID = :INST_ID
 AND SID = :SID

Shared Cursors

SELECT *
 FROM GV$SQL_SHARED_CURSOR
 WHERE SQL_ID = :SQL_ID
 AND INST_ID = :INST_ID

Temporary Segments Usage

SELECT * FROM GV$TEMPSEG_USAGE
 WHERE SESSION_ADDR = :SADDR
 AND INST_ID = :INST_ID

Open Cursors

SELECT sid ,
 sql_text,
 LAST_SQL_ACTIVE_TIME,
 USER_NAME,
 SQL_ID,
 CURSOR_TYPE,
 COUNT(*) AS "OPEN CURSORS"
 FROM gv$open_cursor
 WHERE SID = :SID
 and INST_ID = :INST_ID
 GROUP BY sid ,
 sql_text,
 LAST_SQL_ACTIVE_TIME,
 USER_NAME,
 SQL_ID,
 CURSOR_TYPE

Cached Cursors Stats

SELECT
 cach.value cache_hits,
 prs.value all_parses,
 prs.value - cach.value sess_cur_cache_not_used
 FROM
 Gv$sesstat cach,
 Gv$sesstat prs,
 Gv$statname nm1,
 Gv$statname nm2
 WHERE
 cach.statistic# = nm1.statistic#
 AND nm1.name = 'session cursor cache hits'
 AND prs.statistic# = nm2.statistic#
 AND nm2.name = 'parse count (total)'
 AND prs.sid = cach.sid
 AND PRS.INST_ID = CACH.INST_ID
 AND NM1.INST_ID = NM2.INST_ID
 AND PRS.INST_ID = NM2.INST_ID
 AND cach.sid = :SID
 AND CACH.INST_ID = :INST_ID

After you are happy with your new tool, you may want to save it and copy to different computers or send it by mail to collegues. Just right-click on your report and Save As… . On the other hand, to import a new report, just right-clink on User Defined Reports and Open Report choosing the xml file saved from another SQL Developer.

Hope you like it! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *