4. Tibero Monitoring
4.5. Session Info
4.5.1. Sql Information
DBMS_XPLAN Package 를 통해 plan을 확인할 수 있습니다.
|
Package
|
Command
|
|
DBMS_XPLAN
|
SQL> set pagesize 120
SQL> set lines 200
SQL> set pages 0
SQL> select * from table(dbms_xplan.display_cursor(,,'ALL'));
|
|
Example
set pagesize 120
set lines 200
set pages 0
select * from table(dbms_xplan.display_cursor('2pq5dar43cfwv',2284,'ALL'))
/
SQL ID : 2pq5dar43cfwv
HASH VALUE : 3358997403
PLAN HASH VALUE: 3505442370
EXECUTIONS : 2
FETCHES : 2
LOADED AT : 2018/12/28 01:24:02
TOT ELAPSED TIME: 00:00:00.8126
AVG ELAPSED TIME: 00:00:00.4063
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
----------
| ID | Operation | Name | Cost (%CPU) | Cards
| Rows | Elaps. Time | CR Gets | Starts | Used Mem | Temp. Read | Temp. Write|
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
----------
| 1 | ORDER BY (SORT) | | 251 (5.18)| 4
| 0 |00:00:00.0000 | 0 | 1 | 0K | 0 | 0 |
| 2 | UNION ALL | | 251 (5.18)| 4 |
0 |00:00:00.0000 | 0 | 1 | 0K | 0 | 0 |
| 3 | UNION ALL | | 187 (4.28)| 3 |
0 |00:00:00.0000 | 0 | 1 | 0K | 0 | 0 |
| 4 | INDEX JOIN | | 98 (4.08)| 1 |
0 |00:00:00.0000 | 0 | 1 | 0K | 0 | 0 |
| 5 | INDEX JOIN | | 96 (4.17)| 1 |
0 |00:00:00.0000 | 0 | 1 | 0K | 0 | 0 |
| 6 | INDEX JOIN | | 94 (4.26)| 1 |
0 |00:00:00.0000 | 0 | 1 | 0K | 0 | 0 |
| 7 | TABLE ACCESS (ROWID) | _DD_IDX | 29 (0)| 1
| 0 |00:00:00.0000 | 0 | 1 | 0K | 0 | 0 |
...
Predicate Information
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
----------
6 - access: ((((("IDX"."OWNER_ID" = USERENV('SCHEMAID')) OR ("IDX"."OWNER_ID" = 1))
OR EXISTS (SELECT NULL
FROM (SELECT SAU2.PRIV_NO
FROM SYS._DD_SYSAUTH SAU2
START WITH SAU2.GRANTEE_ID = USERENV('SCHEMAID')
CONNECT BY PRIOR SAU2.PRIV_NO = SAU2.GRANTEE_ID
) SAU
WHERE (SAU.PRIV_NO = -483
OR SAU.PRIV_NO = -482
OR SAU.PRIV_NO = -481
...
|
4.5.2. Current Session
|
Example
set lines 160
col "Inst_ID" format 999999
col "Sid,Serial" format a10
col "Username" format a14
col "Status" format a10
col "Ipaddr" format a15
col "Logon_Time" format a18
col "Program" format a17
col SQL_ID for A24
SELECT * FROM
(
SELECT inst_id "Inst_ID"
,sid || ',' ||serial# "Sid,Serial"
,username "Username"
,status "Status"
,ipaddr "IPaddr"
,to_char(logon_time,'yy/mm/dd hh24:mi:ss') "Logon_Time"
,prog_name "Program"
--,NVL(sql_id, prev_sql_id) "SQL_ID"
,NVL(sql_id, prev_sql_id) || '/' || NVL2(sql_id, sql_child_number,
prev_child_number) "SQL_ID"
,client_pid "Client_Pid"
,pid "Wthr_Pid"
,wthr_id "Wthr_Id"
FROM gv$session
ORDER BY inst_id, sid
)
UNION ALL
SELECT null
, '[Run: ' || sum(decode(status, 'RUNNING', cnt, 0)) || ']'
, '[Tot: ' || sum(cnt) || ']'
,null ,null ,null ,null ,null ,null ,null, null
FROM
(select status
, count(*) cnt
from gv$session
group by status)
/
Inst_ID Sid,Serial Username Status IPaddr Logon_Time Program
SQL_ID Client_Pid Wthr_Pid Wthr_Id
------- ---------- -------------- ---------- --------------- ------------------ ----- ------------ ------------------------ ---------- ---------- ----------
1 118,106483 SYS RUNNING 192.168.56.101 18/12/27 23:03:10 tbsql
07tt8gz6radk0/172 11323 3316 1
1 128,113939 SYS RUNNING 18/12/28 00:01:00
JOB_SCHEDULER 5gwt5726x55by/1239 0 3317 1
[Run: 2] [Tot: 2]
|
4.6. Lock Info
4.6.1. Current Lock Info
|
Example
set linesize 200 set pagesize 50 col "User" format a15 col "Sid" format 9999 col "Object" format a35 col
"Status" format a8 col "Lock_time" format a15 col "Lock mode" format a15 col "SQL_ID" for a40
SELECT s.sess_id "Sid"
,s.status "Status"
,s.user_name "User"
,o.owner|| '.' ||o.object_name "Object"
,FLOOR((sysdate - vt.start_time)*24) || ':'||
LPAD(FLOOR(MOD((sysdate - vt.start_time)*1440, 60)),2,0) ||':'||
LPAD(FLOOR(MOD((sysdate - vt.start_time)*86400,60)),2,0) AS "Lock_time"
,DECODE(lmode, 0, '[0]', 1, '[1]Row-S(RS)', 2, '[2]Row-X(RX)', 3, '[3]Shared(S)',
4, '[4]S/Row-S(SRX)', 5, '[5]Exclusive(X)', 6, '[6]PIN', TO_CHAR (lmode) ) "Lock mode"
--,NVL(s.sql_id, s.prev_sql_id) "SQL_ID"
,NVL(s.sql_id, s.prev_sql_id) || '/' || NVL2(s.sql_id, s.sql_child_number,
s.prev_child_number) "SQL_ID"
FROM vt_wlock l,
vt_session s,
dba_objects o ,
vt_transaction vt
WHERE l.type='WLOCK_DML'
AND l.sess_id = s.vtr_tid
AND l.id1 = o.object_id (+)
AND l.sess_id = vt.sess_id order by "Lock_time" DESC
/
Sid Status User Object Lock_time Lock mode SQL_ID
------ -------------- ---------- ------------------- -------------- ------------------ ----------------
118 RUNNING TIBERO TIBERO.TEST 0:25:53 [2]Row-X(RX) 3bnau9uxgcf9k/13
|
4.6.2. Hierarchical Lock Info(TAC)
|
Example
set linesize 150
set pagesize 100
col "Sid-Path" for a50
alter session set _inline_with_query=n;
with ttl as (select * from gv$lock, dual)
select path "Sid-Path"
--, lev "Level"
--, isleaf
, type
, id1
, id2
, lmode
, requested
from (
select substr(sys_connect_by_path('('||nvl(inst_id, 0)||')'||sess_id, '/'), 2) path
,level lev
--,connect_by_isleaf as isleaf
--,connect_by_iscycle as iscycle
, l.*
from ttl l
start with lmode > 0 and requested =0
connect by
prior type = type
and prior id1 = id1
and prior id2 = id2
and prior requested != requested
--and prior nvl(inst_id,0)||prior sess_id != nvl(inst_id, 0)||sess_id
and requested > 0
and level < 3
--order siblings by thr_id
) t
where lev = 2
order by type, path
/
Sid-Path TYPE ID1 ID2 LMODE REQUESTED
-------------------- ------------------ --------- ----- ----------- -------------------
(1)118/(2)118 WLOCK_TX 393269 63 0 1
|
'Admin, 환경설정' 카테고리의 다른 글
| [Tibero] Tibero 운영 가이드 for beginner (10) (0) | 2023.05.15 |
|---|---|
| [Tibero] Tibero 운영 가이드 for beginner (9) (0) | 2023.05.12 |
| [Tibero] Tibero 운영 가이드 for beginner (7) (0) | 2023.05.12 |
| [Tibero] Tibero 운영 가이드 for beginner (6) (0) | 2023.05.12 |
| [Tibero] Tibero 운영 가이드 for beginner (5) (0) | 2023.05.12 |