REM ***** gsp.sql *****
REM ***** This is used to get SPID from SID.
col username format a30
col machine format a20
col program format a40
accept _sid prompt ‘Enter Oracle Session ID ->’
select a.sid, b.pid, b.spid, a.username,a.program,a.machine
from v$session a,V$process b
where a.paddr = b.addr
and a.sid = &_sid
/
REM ***** gsq.sql
REM ***** The details of a session for a given system process id (sid)
DEFINE sid=’&1′
column machine format a16
column osuser format a16
column sid format 9999
column sql_address format a10
column sql_hash_value format 9999999999
column serial# format 999999
column sql_text format a64
select a.sid,
a.serial#,
a.machine,
a.osuser,
a.sql_address,
a.sql_hash_value,
a.username
from v$session a
where sid =’&sid’
/
select sql_text from v$sqltext c,v$session a
where a.sid =’&sid’
and hash_value = a.sql_hash_value
and address = a.sql_address
order by piece
/
REM ***** gsid.sql
REM ***** Get SID from SPID
col username format a30
col machine format a20
col program format a40
accept spid prompt ‘Enter Unix Process ID ->’
select a.sid, b.pid, b.spid, a.username,a.program,a.machine
from v$session a,V$process b
where a.paddr = b.addr
and b.spid = &spid
/
REM gcu.sql
REM Get Curent USN
column start_dt format a20
set lines 132
select /*+ ALL_ROWS */ a.usn, a1.name, a.extents, a.hwmsize, a.status usn_status,
to_char(b.start_date, ‘DD-MON-RRRR:HH24:MI:SS’) start_dt, b.status tx_status,
c.sid, c.sql_id, b.used_ublk, b.used_urec,
d.sql_text
from v$rollstat a, v$rollname a1, v$transaction b, v$session c, v$sqlarea d
where a.usn = b. xidusn
and a.usn = a1.usn
and b.ses_addr = c.saddr
and c.sql_id = d.sql_id;
REM *****wait.sql
REM *****Returns the information about current wait events.
set pages 2000
set lines 132
column p1text format a18
column event format a35
column WT format 9999
column SW format 9999
select sid, event,p1text,p1raw,p1,p2,p3,seconds_in_Wait “SW”,Wait_time “WT”
from v$session_wait
where event not in(
‘KXFQ: Dequeue Range Keys – Slave’,
‘KXFQ: Dequeuing samples’,
‘KXFQ: kxfqcls – consumer closing TQ’,
‘KXFQ: kxfqdeq – dequeue from specific qref’,
‘KXFQ: kxfqdeq – normal deqeue’,
‘KXFX: Execution Message Dequeue – Slave’,
‘KXFX: Message Fragment Dequeue – Slave’,
‘KXFX: Parse Reply Dequeue – Query Coord’,
‘Null event’,
‘PL/SQL lock timer’,
‘Parallel Query Idle Wait – Slaves’,
‘Replication Dequeue’,
‘SQL*Net message from client’,
‘buffer deadlock’,
‘dispatcher timer’,
‘io done’,
‘pipe get’,
‘pmon timer’,
‘rdbms ipc message’,
‘rdbms ipc message block’,
‘rdbms ipc reply’,
‘slave wait’,
‘smon timer’,
‘virtual circuit status’)
/
REM ***** eplan10g.sql
REM ***** explain plan statement.
–truncate table plan_table;
set lines 132
set echo on
delete from plan_table where statement_id=’pvm’;
commmit;
EXPLAIN PLAN SET STATEMENT_ID = ‘pvm’ FOR
select * from browse_dataset where id=:”SYS_B_0″;
— syntax for viewing information from plan_table
SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position)
“Query Plan”
FROM plan_table
START WITH id = 0 and statement_id = ‘pvm’
CONNECT BY PRIOR id = parent_id AND statement_id =’pvm’;
select * from table(dbms_xplan.display());