4. Tibero Monitoring

4.5. Session Info

4.5.1. Sql Information

DBMS_XPLAN Package 를 통해 plan을 확인할 수 있습니다.

Package	Command
DBMS_XPLAN	SQL> set pagesize 120SQL> set lines 200SQL> set pages 0SQL> 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

☞ [Tibero] Tibero 운영 가이드 for beginner (9)”에서 계속됩니다.