Check session level details :
set lines 200 pages 1000
col USERNAME for a20
col MACHINE for a20
col PROGRAM for a30
col MODULE for a30
col ACTION for a25
select SID,SERIAL#,USERNAME,MACHINE,PROGRAM,SQL_ID,MODULE,ACTION from v$session;
SQL> select sql_text from v$sql where sql_id='cbdw3jv9azwbu';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select SID,SERIAL#,USERNAME,MACHINE,PROGRAM,SQL_ID,MODULE,ACTION from v$session
SQL> select sql_text from v$sql where sql_id='g0aywfbqsj573';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from dba_source
SQL>
col SQL_TEXT for a30
select a.SID,a.SERIAL#,b.SQL_TEXT from v$session a,v$sql b where a.SQL_ID=b.SQL_ID and a.SQL_ID='g0aywfbqsj573';
SQL Text of particular sql_id
SQL> select a.SID,a.SERIAL#,b.SQL_TEXT from v$session a,v$sql b where a.SQL_ID=b.SQL_ID and a.SQL_ID='g0aywfbqsj573';
SID SERIAL# SQL_TEXT
---------- ---------- ------------------------------
60 36460 select * from dba_source
122 34561 select * from dba_source
125 58351 select * from dba_source
SQL>
Long Ops details sessions
1)Rman Backup and Restore
2)Parallel Query(Large chunks)
3)Recovery ( Crash and Media )
4)Large Full Table scans
5)Sorting
6)Stats job
7)If the table scan exceeds 10,000 formatted blocks
8)operation is greater than 6 seconds
Exception where view does not record
1)Index long operations(long time)
col SERIAL# for a20
col OPNAME for a20
col START_TIME for a30
col LAST_UPDATE_TIME for a30
col TIME_REMAINING for a30
col ELAPSED_SECONDS for a30
col MESSAGE for a30
col SQL_ID for a30
col USERNAME for a30
select SID,SERIAL#,OPNAME,START_TIME,LAST_UPDATE_TIME,ELAPSED_SECONDS,MESSAGE,SQL_ID,USERNAME,100 * (SOFAR/TOTALWORK) PCT from v$session_longops where SOFAR >0 and TOTALWORK >0;
76 1601 SYS srv6.example.com sqlplus@srv6.example.com (TNS g0aywfbqsj573 sqlplus@srv6.example.com (TNS
V1-V3) V1-V3)
77 47626 SYS DESKTOP-FIGDCKD SQL Developer SQL Developer
78 4270 SYS srv6.example.com sqlplus@srv6.example.com (TNS cbdw3jv9azwbu sqlplus@srv6.example.com (TNS
V1-V3) V1-V3)
79 52224 SOE1 DESKTOP-FIGDCKD SQL Developer 1d030zjx7k2kd SQL Developer
84 9473 srv6.example.com oracle@srv6.example.com (W005) KTSJ KTSJ Slave
85 20361 SYS srv6.example.com oracle@srv6.example.com (P001) g0aywfbqsj573 sqlplus@srv6.example.com (TNS
V1-V3)
86 15017 srv6.example.com oracle@srv6.example.com (TT04)
90 16629 SYS srv6.example.com oracle@srv6.example.com (P000) g0aywfbqsj573 sqlplus@srv6.example.com (TNS
V1-V3)
59 rows selected.
SQL> col sid for a20
SQL> col SERIAL# for a20
col OPNAME for a20
col START_TIME for a30
col LAST_UPDATE_TIME for a30
col TIME_REMAINING for a30
col ELAPSED_SECONDS for a30
col MESSAGE for a30
col SQL_ID for a30
col USERNAME for a30
select SID,SERIAL#,OPNAME,START_TIME,LAST_UPDATE_TIME,ELAPSED_SECONDS,MESSAGE,SQL_ID,USERNAME,100 * (SOFAR/TOTALWORK) PCT from v$session_longops where SOFAR >0 and TOTALWORK >0;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
no rows selected
Explain plan of SQL _ID
SQL> select * from table(dbms_xplan.display_cursor('1d030zjx7k2kd',null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1d030zjx7k2kd, child number 0
-------------------------------------
select * from CUSTOMERS
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 823 (100)| |
| 1 | TABLE ACCESS FULL| CUSTOMERS | 161K| 17M| 823 (1)| 00:00:01 |
-------------------------------------------------------------------------------
13 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('g0aywfbqsj573',null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g0aywfbqsj573, child number 0
-------------------------------------
select * from dba_source
Plan hash value: 3543585337
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 20000 | 41M| 1 (100)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX PARTITION LIST ALL | | 20000 | 41M| 1 (100)| 00:00:01 | 1 | 2 | Q1,00 | PCWC | |
| 4 | EXTENDED DATA LINK FULL| INT$DBA_SOURCE | 20000 | 41M| 1 (100)| 00:00:01 | | | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------
16 rows selected.
Gather stats of schema table
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SOE1','CUSTOMERS');
PL/SQL procedure successfully completed.
SQL>
select SID,SERIAL#,USERNAME,MACHINE,PROGRAM,SQL_ID,MODULE,ACTION from v$session where username='SOE1';
select SID,SERIAL#,OPNAME,START_TIME,LAST_UPDATE_TIME,ELAPSED_SECONDS,MESSAGE,SQL_ID,USERNAME from v$session_longops where username='SOE1';
SQL> select SID,SERIAL#,SQL_ID,PROGRAM,TERMINAL,ACTION,MODULE from v$session where audsid=sys_context('USERENV','SESSIONID');
----------------------------------------------------------------------------------------------------------------------
Session SID details
SQL> select sid from v$mystat where rownum <2;
SID
----------
78
SID SERIAL# SQL_ID
---------- ---------- -------------
PROGRAM TERMINAL
------------------------------------------------ ------------------------------
ACTION
----------------------------------------------------------------
MODULE
----------------------------------------------------------------
76 28355 1w1p87znsbt7c
sqlplus@srv6.example.com (TNS V1-V3) pts/2
SQL*Plus
SQL>
SQL> select sql_text from v$sql where sql_id='1w1p87znsbt7c';
SQL_TEXT
--------------------------------------------------------------------------------
select SID,SERIAL#,SQL_ID,PROGRAM,TERMINAL,ACTION,MODULE from v$session where au
dsid=sys_context('USERENV','SESSIONID')
SQL>
----------------------------------------------------------------------------------------------------------
Top >> PID >> SPID
SQL> select se.sid,se.SERIAL#,se.SQL_ID,se.MACHINE,se.TERMINAL,se.MODULE,se.ACTION from v$session se inner join v$process pr on (pr.ADDR=se.PADDR) where pr.spid='13281';
SID SERIAL# SQL_ID
---------- ---------- -------------
MACHINE
----------------------------------------------------------------
TERMINAL
------------------------------
MODULE
----------------------------------------------------------------
ACTION
----------------------------------------------------------------
32 24650
srv6.example.com
UNKNOWN
SID SERIAL# SQL_ID
---------- ---------- -------------
MACHINE
----------------------------------------------------------------
TERMINAL
------------------------------
MODULE
----------------------------------------------------------------
ACTION
----------------------------------------------------------------
Blocking SID
SQL> select sid,serial#,MACHINE,program,sql_id,command,blocking_session from v$session where blocking_session is not null;
SQL> lock table soe1.customers in exclusive mode;
Table(s) Locked.
SQL> update soe1.customers set CUST_FIRST_NAME='VARUN' WHERE CUSTOMER_ID=39652358;
SQL> col MACHINE for a40
SQL> SET LINES 200
SQL> select sid,serial#,MACHINE,program,sql_id,command,blocking_session from v$session where blocking_session is not null;
SID SERIAL# MACHINE PROGRAM SQL_ID COMMAND BLOCKING_SESSION
---------- ---------- ---------------------------------------- ------------------------------------------------ ------------- ---------- ----------------
76 28355 srv6.example.com sqlplus@srv6.example.com (TNS V1-V3) bwgxdy0n3dtq9 6 78
SQL> select sid as blocked,serial#,MACHINE,program,sql_id,command,blocking_session as blocker from v$session where blocking_session is not null;
BLOCKED SERIAL# MACHINE PROGRAM SQL_ID COMMAND BLOCKER
---------- ---------- ---------------------------------------- ------------------------------------------------ ------------- ---------- ----------
76 28355 srv6.example.com sqlplus@srv6.example.com (TNS V1-V3) bwgxdy0n3dtq9 6 78
SQL>
No comments:
Post a Comment