SQL虎の巻ーセッション調査

 インスタンス、SID、サービス名

--SQLPLUS
show parameter service_names
show parameter db_name
show parameters db_domain

--ディクショナリで引く

--インスタンス名
select instance_name from v$instance;
--DBID,サービス名
SELECT DBID,NAME AS SERVICE_NAMES,DB_UNIQUE_NAME,CURRENT_SCN,LOG_MODE  FROM V$DATABASE ;
--RACに属するSIDを全て表示
SELECT THREAD#,INSTANCE,STATUS,ENABLED FROM V$THREAD;
 

---

セッション確認

select
    sid||','||SERIAL#
   ,USERNAME
   ,STATUS
   ,SCHEMANAME
   ,OSUSER
   ,MACHINE
   ,TERMINAL
   ,PROGRAM
   ,SQL_ID
   ,LOGON_TIME
FROM V$SESSION
where SCHEMANAME NOT IN('SYS','DBSNMP')
ORDER BY LOGON_TIME
;

---

select
   SCHEMANAME
  ,Status
  ,Count(SCHEMANAME) As cnt
FROM V$SESSION
where SCHEMANAME NOT IN('SYS','DBSNMP')
Group by 
   SCHEMANAME
  ,Status
ORDER BY SCHEMANAME,Status
;
 

---

ロングSQL

 

select 
    Vse.sid||Vse.serial# as s_id
--   ,Vse.status
   ,Vse.schemaname
   ,to_char(Vse.sql_exec_start,'yyyymmdd hh24:mi:ss') as Exec_start
   ,(sysdate-Vse.sql_exec_start) * 1440*60 as Exec_time
   ,Vse.sql_id
   ,substr(vs.sql_text,1,40) sql_txt
   ,Vse.osuser
   ,Vse.module
   ,Vse.machine
   ,Vse.program
from V$SESSION Vse,V$SQL vs
WHERE
    Vse.status='ACTIVE'
and Vse.sql_id =vs.sql_id
ORDER by Vse.sql_exec_start

;

---

 ロックを起こしているセッションのSQLを表示 

Select V$SQLAREA.SQL_TEXT,V$SQLAREA.ADDRESS
 From V$SQLAREA, V$SESSION, V$LOCK
 Where V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
 And V$SESSION.SID = V$LOCK.SID
 And V$LOCK.TYPE IN ('TX','TM') ;

---

 ロックのセッション、ユーザ、オブジェクト及びプログラム名を表示

Select V$SESSION.SID, DBA_OBJECTS.OBJECT_NAME, V$SESSION.OSUSER, V$SESSION.PROGRAM
From V$LOCKED_OBJECT
Left Join DBA_OBJECTS on V$LOCKED_OBJECT.OBJECT_ID = DBA_OBJECTS.OBJECT_ID
Left Join V$SESSION ON V$LOCKED_OBJECT.SESSION_ID = V$SESSION.SID
Order By V$SESSION.SID, DBA_OBJECTS.OBJECT_NAME;

---

待機セッションのブロッカーと待機時間の確認

select SAMPLE_TIME, SESSION_ID, EVENT, TIME_WAITED, BLOCKING_SESSION
  from V$ACTIVE_SESSION_HISTORY
 where SESSION_ID=140
   and SAMPLE_TIME > SYSTIMESTAMP - 70/60/24
 order by 1;

---

--SQL確認

set PAGES 1000 LINES 150
col EVENT for a30
col USERNAME for a8
select T1.SID, T1.USERNAME, T1.SQL_ID, T2.SQL_TEXT
  from V$SESSION T1, V$SQLTEXT T2
 where T1.SQL_ID = T2.SQL_ID and SID in (140, 2991)
 ;

待機セッションの確認(今)

    select
        sid
       ,SERIAL# 
       ,username
       ,status 
       ,machine
       ,program
       ,sql_address
       ,sql_hash_value
       ,state
       ,event
       ,SEQ#
       ,seconds_in_wait
       ,WAIT_TIME_MICRO
    from v$session   
    where 
    type != 'BACKGROUND'
    and state ='WAITING'
    and username is not NULL
    and event not like 'SQL*Net message%'
    ORDER BY seconds_in_wait DESC
;

SQL_HANDLE to SQL_ID

SELECT *
FROM
  (SELECT t.SQL_HANDLE,
    t.SIGNATURE,
    t.SQL_TEXT,
    s.SQL_ID
  FROM SYS.SQL$TEXT t,
    DBA_HIST_SQLSTAT s
  WHERE t.SQL_HANDLE = 'SQL_d804b630101fbd61'
  AND t.SIGNATURE    = s.FORCE_MATCHING_SIGNATURE
  )
WHERE rownum = 1 ;

SQL_ID to SQL_HANDLE

SELECT *
FROM
  (SELECT t.SQL_HANDLE,
    t.SIGNATURE,
    t.SQL_TEXT,
    s.SQL_ID
  FROM SYS.SQL$TEXT t,
    DBA_HIST_SQLSTAT s
  WHERE s.SQL_ID = '2rn125rkxz274'
  AND t.SIGNATURE    = s.FORCE_MATCHING_SIGNATURE
  )
WHERE rownum = 1 ;

Library Chache lockのロックオブジェクトを特定する(12c)

sysで実行

※12c辺りからp1の値が変わったらしい…

※これで捕捉出来た事は無い。信憑性を調査中…

 

 SELECT
 kglhdobj
 ,kglnaown "Owner"
 , kglnaobj "Object"
 FROM x$kglob
  WHERE
  kglhdobj in (
  select
HEXTORAW(lpad(to_char(p1,'FMXXXXXXXXXXXXXXXX'),16,0))
from V$ACTIVE_SESSION_HISTORY
 where event='library cache lock'
 group by p1
  ) ;

 

LongSessionを調べる

set line 500
col OPNAME form A32
SELECT OPNAME,SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE
OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
;