Your friendly custom session browser
2018-01-03Every 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! 🙂