문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣTibero 7.2.2
문서번호ㅣTADTI198
개요
티베로를 처음 접하는 사용자가 티베로 설치 후 운영에 필요한 기본적인 티베로 모니터링 방법에 대한 내용을 담고 있습니다. 자세한 내용은 매뉴얼을 참조합니다.
방법
Instance/Database Info
| Column | Description |
| Instance Name | 노드 별 인스턴스 이름 |
| Database Name | 데이터베이스 이름 |
| Version | 티베로 버전 정보 |
| Status | 데이터베이스 OPEN Mode |
| NLS Character | 캐릭터셋 정보(NLS_CHARACTERSET/NLS_NCHAR_CHARACTERSET) |
| Log Mode | 로그 모드(NOARCHIVELOG/ARCHIVELOG) |
| DB Create Time | DB 생성 시간 |
| DB Uptime | DB 가동 시간 |
Example
set feedback off
set linesize 150
col "Instance Name" format a15
col "Database Name" format a15
col "Version" format a25
col "Status" format a12
col "NLS Character" format a20
col "Log Mode" format a13
col "DB Create Time" format a20
col "DB Uptime" format a15
select i.instance_name "Instance Name"
, d.name "Database Name"
, v.vv "Version"
, d.open_mode "Status"
, c.cc "NLS Character"
, d.log_mode "Log Mode"
, to_char(d.db_create_date,'YYYY/MM/DD HH24:MI:SS') "DB Create Time"
, floor(xx)||'d '||floor((xx-floor(xx))*24)||'h '||floor( ((xx - floor(xx))*24 - floor((xx-floor(xx))*24) )*60 )||'m' as "DB Uptime"
from v$database d
, ( select instance_name, (sysdate-startup_time) xx
from gv$instance
) i
, ( select aggr_concat(value, ' ') vv
from v$version
where name in ('PRODUCT_MAJOR', 'PRODUCT_MINOR', 'BUILD_NUMBER', 'STABLE_VERSION')
) v
, ( select aggr_concat(value, '/') cc
from _dd_props
where name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET')
) c
/
Instance Name Database Name Version Status NLS Character Log Mode DB Create Time DB Uptime
--------------- --------------- ------------------------- ------------ -------------------- ------------- -------------------- ---------------
tibero tibero 7 2 303667 FS02_PS04 READ WRITE UTF8/UTF16 NOARCHIVELOG 2025/10/15 16:24:47 0d 0h 1m
Memory Info
Memory 영역 모니터링은 데이터베이스 성능과 밀접한 연관을 가집니다. 대표적으로 악성 sql로 인해 불필요한 Disk I/O가 많이 발생된다면 메모리 재사용률은 떨어지게 되고 DB전체 성능이 안 좋아집니다. 그 결과 낮은 Hit Ratio 결과로 보여질 것입니다. 성능 진단 TPR 또는 Top SQL Info를 통해 문제되는 sql을 찾아 Tunning 함으로 DB 전체의 성능을 향상 시킬 수 있습니다.
| Name | Description |
| MEMORY_TARGET | 인스턴스가 사용 가능 메모리 최대값 |
| TSM/SGA(Used) | 인스턴스에서 사용하는 공유 메모리 값 |
| PGA(Allocated) | 할당되어 진 PGA(세션 단위로 할당) 메모리 총 값 |
| PGA(Used) | 사용 중 인 PGA 메모리 총 값 |
Example
set linesize 130
select name, round(value/1024/1024, 2) "Size(MB)"
from v$parameters
where name = 'MEMORY_TARGET'
union all
select 'TSM/SGA(Used)' name, round(sum(used)/1024/1024, 2) "Size(MB)"
from v$sga
where name in ('FIXED MEMORY', 'SHARED POOL MEMORY')
union all
select 'PGA(Allocated)' name, round(sum(value)/1024/1024, 2) "Size(MB)"
from v$pgastat
where name in ('FIXED pga memory', 'ALLOCATED pga memory')
union all
select 'PGA(Used)' name, round(value/1024/1024, 2) "Size(MB)"
from v$pgastat
where name = 'USED pga memory (from ALLOCATED)'
/
NAME Size(MB)
---------------------------------------------------------------- ----------
MEMORY_TARGET 4096
TSM/SGA(Used) 1813.74
PGA(Allocated) 143.16
PGA(Used) 18.5
1) Buffer Hit Ratio
| Name | Description |
| Database Buffer | 데이터를 메모리에서 처리 한 확률 수치이며, 통상적으로 90% 이상이 정상이나 업무 특성 및 시점(batch수행)에 따라 낮을 수 있다. |
Example
set linesize 132
set feedback off
col "Time" format a19
SELECT TO_CHAR(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time"
,"Physical read"
,"Logical read"
,"Hit"
,CASE WHEN "Hit" > 90 then 'Good'
WHEN "Hit" between 70 and 90 then 'Average'
ELSE 'Not Good'
END as "Status"
FROM
(
SELECT pr1.value + pr2.value "Physical read"
,bg1.value + bg2.value + bg3.value "Logical read"
,ROUND( (1 - (pr1.value + pr2.value) / (bg1.value + bg2.value + bg3.value) ) * 100, 2) "Hit"
FROM v$sysstat pr1, v$sysstat pr2,
v$sysstat bg1 , v$sysstat bg2 , v$sysstat bg3
WHERE pr1.name = 'block disk read'
and pr2.name = 'multi block disk read - blocks'
and bg1.name = 'consistent block gets'
and bg2.name = 'consistent multi gets - blocks'
and bg3.name = 'current block gets'
)
/
Time Physical read Logical read Hit Status
------------------- ------------- ------------ ---------- ------------
2025/10/20 15:11:14 1590 171505 99.07 Good
2) Shared Cache Hit Ratio
| Name | Description |
| SQL(Library) Cache | 사용자가 문장을 수행할 때 SQL 과 Parse Tree, Plan 등을 저장하여 공유하는 메모리 공간이며, 통상적으로 90% 이상이 정상이나 업무 특성을 고려하여 모니터링 하여야 합니다. |
| Dictionary Cache | SYSTEM TABLESPACE 에 저장되어 있는 Data Dictionary 정보가 Shared Pool 에 상주 하는 부분으로 Tibero 데이터베이스에 저장된 모든 객체 및 이와 관련된 정보들을 저장하는 시스템 테이블들이 로딩되는 영역입니다. 유저가 실행한 SQL 문장을 파싱 할 때 Syntax 와 접근 권한 등을 체크 하면서 참조 되고 공유하는 내용이기 때문에 Shared Pool 영역에 저장됩니다. 다음에서 Hit율이 95% 이하이면 Shared Pool 사이즈를 늘려줘야만 합니다. |
Example
set linesize 132
set feedback off
col "Time" format a19
SELECT TO_CHAR(sysdate,'yyyy/mm/dd hh24:mi:ss') AS "Time"
, 'SQL(Library) Cache' AS "Name"
, hit AS "Hit(%)"
, CASE WHEN hit > 90 then 'Good'
WHEN hit between 70 and 90 then 'Average'
ELSE 'Not Good'
END AS "Status"
FROM ( SELECT gethitratio AS hit
FROM v$librarycache
WHERE namespace= 'SQL AREA' )
UNION ALL
SELECT TO_CHAR(sysdate,'yyyy/mm/dd hh24:mi:ss') AS "Time"
, 'Dictionary Cache' AS "Name"
, hit AS "Hit(%)"
, CASE WHEN hit > 90 then 'Good'
WHEN hit between 70 and 90 then 'Average'
ELSE 'Not Good'
END AS "Status"
FROM ( SELECT ROUND((1- sum(miss_cnt)/(sum(hit_cnt+miss_cnt)))*100, 2) AS hit
FROM v$rowcache )
/
Time Name Hit(%) Status
------------------- ------------------ ---------- ------------
2025/10/20 15:11:56 SQL(Library) Cache 98.9 Good
2025/10/20 15:11:56 Dictionary Cache 96.49 GoodTablespace Usage Info
테이블스페이스가 Full 이 되는 상황을 방지하도록 점검하여 Free Space 사이트 정책 기준 이하일 경우 Datafile 을 추가하도록 합니다.
| Column | Description |
| Tablespace Name | 테이블스페이스 이름 |
| Bytes(MB) | 할당 용량 |
| Used(MB) | 사용한 용량(MB) |
| Percent(%) | 사용한 용량(%) |
| Free(MB) | 남아있는 용량(MB) |
| Free(%) | 남은 용량(%) |
| MaxBytes(MB) | 최대 늘어날 수 있는 용량(MB) |
Example
set linesize 150
set pagesize 100
col "Tablespace Name" format a20
col "Bytes(MB)" format 999,999,999
col "Used(MB)" format 999,999,999
col "Percent(%)" format 9999999.99
col "Free(MB)" format 999,999,999
col "Free(%)" format 9999.99
col "MaxBytes(MB)" format 999,999,999
SELECT ddf.tablespace_name "Tablespace Name",
ddf.bytes/1024/1024 "Bytes(MB)",
(ddf.bytes - dfs.bytes)/1024/1024 "Used(MB)",
round(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2) "Percent(%)",
dfs.bytes/1024/1024 "Free(MB)",
round((1 - ((ddf.bytes - dfs.bytes) / ddf.bytes)) * 100, 2) "Free(%)",
ROUND(ddf.MAXBYTES / 1024/1024,2) "MaxBytes(MB)"
FROM
(SELECT tablespace_name, sum(bytes) bytes, sum(maxbytes) maxbytes
FROM dba_data_files
GROUP BY tablespace_name) ddf,
(SELECT tablespace_name, sum(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) dfs
WHERE ddf.tablespace_name = dfs.tablespace_name
ORDER BY ((ddf.bytes-dfs.bytes)/ddf.bytes) DESC
/
Tablespace Name Bytes(MB) Used(MB) Percent(%) Free(MB) Free(%) MaxBytes(MB)
-------------------- ------------ ------------ ----------- ------------ -------- ------------
SYSSUB 100 96 95.88 4 4.13 1,024
SYSTEM 100 95 95.25 5 4.75 1,024
UNDO 200 88 44.00 112 56.00 1,024
USR 100 0 .00 100 100.00 1,024
Object Info
1) Total Object
| Column | Description |
| OWNER | 오브젝트 소유 유저 |
| OBJECT_TYPE | 오브젝트 종류 |
| COUNT | 오브젝트 종류 별 합계 |
Example
set linesize 132
set pagesize 100
col "OWNER" format a20
SELECT owner "OWNER"
, object_type "OBJECT_TYPE"
, count(*) "COUNT"
FROM dba_objects
GROUP BY owner, object_type
ORDER BY owner, object_type
/
OWNER OBJECT_TYPE COUNT
-------------------- ----------------------- ----------
LBACSYS INDEX 30
LBACSYS PACKAGE 14
LBACSYS PACKAGE BODY 13
LBACSYS PROCEDURE 2
LBACSYS SEQUENCE 5
LBACSYS TABLE 23
LBACSYS TYPE 5
LBACSYS TYPE BODY 4
LBACSYS VIEW 50
OUTLN INDEX 1
OUTLN TABLE 4
PUBLIC SYNONYM 1213
SYS DIRECTORY 3
SYS FUNCTION 7
SYS INDEX 365
SYS INDEX PARTITION 216
SYS JAVA 1
SYS LOB 17
SYS PACKAGE 131
SYS PACKAGE BODY 72
SYS SEQUENCE 34
SYS SQL TRANSLATION PROFILE 2
SYS SYNONYM 1
SYS TABLE 546
SYS TABLE PARTITION 162
SYS TRIGGER 2
SYS TYPE 96
SYS TYPE BODY 1
SYS VIEW 338
SYSCAT VIEW 446
SYSGIS FUNCTION 1
SYSGIS INDEX 2
SYSGIS PROCEDURE 2
SYSGIS TABLE 2
SYSGIS VIEW 6
2) Invalid Object
| Column | Description |
| OWNER | 오브젝트 소유 유저 |
| OBJECT_TYPE | 오브젝트 종류 |
| Object name | 오브젝트 이름 |
| Status | 오브젝트 상태 |
| Last DDL Time | 마지막 DDL 시간 |
Example
set linesize 132
set pagesize 100
col "Owner" format a20
col "Object name" format a50
col "Last DDL Time" format a19
SELECT owner "OWNER"
, object_type "Object type"
, object_name "Object name"
, status "Status"
, to_char(last_ddl_time, 'YYYY-MM-DD HH24:MI:SS') "Last DDL Time"
FROM dba_objects
WHERE status = 'INVALID'
AND object_type != 'SYNONYM'
ORDER BY owner, object_type, object_name, status
/
OWNER Object type Object name Status Last DDL Time
-------------------- ----------------------- ---------------- ------- -------------------
TIBERO PACKAGE BODY PKG_R068_1 INVALID 2025-10-20 09:17:30
TIBERO PACKAGE BODY PKG_R068_3 INVALID 2025-10-20 10:06:18Session Info
1) sql Info
| Package | Command |
| DBMS_XPLAN | SQL> set pagesize 120 SQL> set lines 200 SQL> set pages 0 SQL> select * from table(dbms_xplan.display_cursor(<sql_id>,<sql_child_number>,'ALL')); |
Example
set lines 200
set pages 0
select * from table(dbms_xplan.display_cursor('d0ab2x2s4t2gb',289,'ALL'))
/
SQL ID : 2pq5dar43cfwv
HASH VALUE : 3358997403
PLAN HASH VALUE: 3505442370
EXECUTIONS : 2
FETCHES : 2
LOADED AT : 2025/10/20 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
...
2) current Session info
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
------- ---------- -------------- ---------- --------------- ------------------ ----------------- ------------------------ ---------- ---------- ----------
98,8027 SYS RUNNING 127.0.0.1 25/10/20 15:58:26 tbsql 6d74uhn11f76u/306 260244 4070144 1
99,7639 TIBERO READY 127.0.0.1 25/10/20 15:55:42 tbsql d0ab2x2s4t2gb/289 242197 4070144 2
[Run: 1] [Tot: 2]
Lock Info
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
----- -------- --------------- ----------------------------------- --------------- --------------- ----------------------------------------
99 RUNNING TIBERO TIBERO.T1 0:00:25 [2]Row-X(RX) 1u2v2d9tmx9f5/290
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 1Top SQL Info
Case별 Top sql 을 추출해서 개선할 여지가 있는지 확인합니다.
1) Top 10 SQL Ordered by Elapsed Time
Example
set linesize 150
col USERNAME for A20
col MODULE for A30
col "SQL_ID" for A30
select * from
(
select (select username from all_users where user_id = PARSING_USER_ID ) USERNAME,
round(ELAPSED_TIME/1000000,3) as "Elapsed_Time(s)",
EXECUTIONS,
round(BUFFER_GETS/EXECUTIONS,3) "Gets/Exec",
round(ELAPSED_TIME/EXECUTIONS/1000,3) as "Elap/Exec(ms)",
MODULE "MODULE",
sql_id|| '/' || child_number "SQL_ID"
from v$sql
where ELAPSED_TIME > 0
and EXECUTIONS > 0
order by 2 desc
) where rownum <=10
/
USERNAM Elapsed_Time(s) EXECUTIONS Gets/Exec Elap/Exec(ms) MODULE SQL_ID
------- --------------- ---------- ---------- ------------- --------------- ------------------
TIBERO 32549041 3308129 0 9839.109 ofruisvr@testap c5zmrkkwsgxdu/3541
TIBERO 57382.687 5497383 311.912 10.438 DFSRRC00@testap av5znp5xb2qrj/3603
TIBERO 31761.324 2071 384173.902 15336.226 DFSRRC00@testap 67cgswupkpbmz/5726
TIBERO 28432.942 9147609 96.272 3.108 ofrpmsvr@testap b5x3badvugzj1/3508
TIBERO 20509.201 2072 409331.333 9898.263 DFSRRC00@testap 9rhsganvbjv62/5724
TIBERO 17415.081 9149177 381 1.903 ofrpmsvr@testap 6c92rsh8m8pp6/3507
TIBERO 11783.193 3254906 4 3.62 tjclrun@testap fx6r0ap1943tk/3986
TIBERO 2713.578 20460028 4 .133 tjesmgr@testap 0g9z8sb6mjfmv/3303
TIBERO 2249.482 20460015 4.407 .11 tjesmgr@testap ghj4dms57kr04/3305
TIBERO 1864.096 3324962 34.922 .561 obmjinit@testap 2rjxmpaptavbh/3517
2) Top 10 SQL Ordered by gets
Example
set linesize 150
col USERNAME for A20
col MODULE for A30
col "SQL_ID" for A30
select * from
(
select (select username from all_users where user_id = PARSING_USER_ID ) USERNAME,
BUFFER_GETS,
EXECUTIONS,
round(BUFFER_GETS/EXECUTIONS,3) "Gets/Exec",
round(ELAPSED_TIME/1000000,3) "Elapsed_Time(s)",
MODULE "MODULE",
sql_id|| '/' || child_number "SQL_ID"
from v$sql
where ELAPSED_TIME > 0
and EXECUTIONS>0
--and rownum <=10
order by 2 desc
) where rownum <=10
/
USERNAME BUFFER_GETS EXECUTIONS Gets/Exec Elapsed_Time(s) MODULE SQL_ID
-------- ----------- ---------- ---------- --------------- --------------- ------------------
TIBERO 3485926022 9149408 381 17415.513 ofrpmsvr@testap 6c92rsh8m8pp6/3507
TIBERO 1714698025 5497383 311.912 57382.687 DFSRRC00@testap av5znp5xb2qrj/3603
TIBERO 880683250 9147840 96.272 28433.674 ofrpmsvr@testap b5x3badvugzj1/3508
TIBERO 848134523 2072 409331.333 20509.201 DFSRRC00@testap 9rhsganvbjv62/5724
TIBERO 795624151 2071 384173.902 31761.324 DFSRRC00@testap 67cgswupkpbmz/5726
TIBERO 116116534 3325046 34.922 1864.148 obmjinit@testap 2rjxmpaptavbh/3517
TIBERO 97064192 3254696 29.823 1418.548 DFSRRC00@testap d5zh6vfbh0pwk/5718
TIBERO 90176359 20460015 4.407 2249.482 tjesmgr@testap ghj4dms57kr04/3305
TIBERO 81840188 20460028 4 2713.578 tjesmgr@testap 0g9z8sb6mjfmv/3303
TIBERO 49593419 5216206 9.508 748.822 DFSRRC00@testap cp5pwh82ucbsj/3771
3) Top 10 SQL Ordered by Elap/Exec(ms)
Example
set linesize 150
col USERNAME for A20
col MODULE for A30
col "SQL_ID" for A30
select * from
(
select (select username from all_users where user_id = PARSING_USER_ID ) USERNAME,
round(ELAPSED_TIME/EXECUTIONS/1000,3) as "Elap/Exec(ms)",
EXECUTIONS,
round(BUFFER_GETS/EXECUTIONS,3) "Gets/Exec",
round(ELAPSED_TIME/1000000,3) "Elapsed_Time(s)",
MODULE "MODULE",
sql_id|| '/' || child_number "SQL_ID"
from v$sql
where ELAPSED_TIME > 0
and EXECUTIONS>0
--and rownum <=10
order by 2 desc
) where rownum <=10
/
USERNAME Elap/Exec(ms) EXECUTIONS Gets/Exec Elapsed_Time(s) MODULE SQL_ID
-------- ------------- ---------- ---------- --------------- --------------- ------------------
TIBERO 103504.307 1 2079179 103.504 tbsql@testap anghfd7dumwp8/5713
TIBERO 31362.277 7 153990 219.536 DFSRRC00@testap 9r6x6hn1u82xc/3673
TIBERO 29576.706 4 108261 118.307 DFSRRC00@testap 0mm8r6r8tqffx/3659
TIBERO 26063.614 1 383472 26.064 DFSRRC00@testap 4jhgst0pdcwu5/3931
TIBERO 25396.06 1 169190 25.396 tbsql@testap d5myu8vy6nw83/4158
TIBERO 24914.119 2 4434517 49.828 IKJEFT01@testap bfuqt3d4matw7/5094
TIBERO 23383.632 1 328421 23.384 DFSRRC00@testap 80y8r4wr0x5y0/3933
TIBERO 20195.523 1 168690 20.196 tbsql@testap d5myu8vy6nw83/4418
TIBERO 17663.778 1 235734 17.664 DFSRRC00@testap f7zy25yhds9zb/3649
TIBERO 17232.283 1 513013 17.232 tbsql@testap 5fawmardpa66b/3873
Cluster Manager Info (Using TAC)
| Target | Command |
| Local Node | $ cmrctl show |
| Remote Node | $ cmrctl show --remote <CM_SID>@<Cluster_Name> |
Example
tibero@edu1[tac1]:/home/tibero/tbinary> cmrctl show
Resource List of Node cm1
=====================================================================
CLUSTER TYPE NAME STATUS DETAIL
----------- -------- -------------- -------- ------------------------
COMMON network inter1 UP (private) 10.0.0.31/5629
COMMON network pub1 UP (public) eth1
COMMON cluster cluster1 UP inc: inter1, pub: pub1
cluster1 file cluster1:0 UP +0
cluster1 file cluster1:1 UP +1
cluster1 file cluster1:2 UP +2
cluster1 service tas UP Active Storage, Active Cluster (auto-restart: OFF)
cluster1 service tac UP Database, Active Cluster (auto-restart: OFF)
cluster1 as tas1 UP(NRML) tas, /home/tibero/tibero7, failed retry cnt: 0
cluster1 db tac1 UP(NRML) tac, /home/tibero/tibero7, failed retry cnt: 0
cluster1 vip vip1 UP tac, 192.168.56.111/255.255.255.0/192.168.56.255 (1)
failed retry cnt: 0
cluster1 vip vip2 UP(R) tac, 192.168.56.112/255.255.255.0/192.168.56.255 (2)
failed retry cnt: 0
=====================================================================
tibero@edu1[tac1]:/home/tibero/scripts/2.command> cmrctl show --remote cm2@cluster1
Resource List of Node cm2
=====================================================================
CLUSTER TYPE NAME STATUS DETAIL
----------- -------- -------------- -------- ------------------------
COMMON network inter2 UP (private) 10.0.0.32/5629
COMMON network pub2 UP (public) eth1
COMMON cluster cluster1 UP inc: inter2, pub: pub2
cluster1 file cluster1:0 UP +0
cluster1 file cluster1:1 UP +1
cluster1 file cluster1:2 UP +2
cluster1 service tas UP Active Storage, Active Cluster (auto-restart: OFF)
cluster1 service tac UP Database, Active Cluster (auto-restart: OFF)
cluster1 as tas2 UP(NRML) tas, /home/tibero/tibero7, failed retry cnt: 0
cluster1 db tac2 UP(NRML) tac, /home/tibero/tibero7, failed retry cnt: 0
cluster1 vip vip1 UP(R) tac, 192.168.56.111/255.255.255.0/192.168.56.255 (1)
failed retry cnt: 0
cluster1 vip vip2 UP tac, 192.168.56.112/255.255.255.0/192.168.56.255 (2)
failed retry cnt: 0
=====================================================================Active Storage Info (Using TAC)
| Command |
| $ tbascmd <tas_port> |
Example
tibero@edu1[tac1]:/home/tibero> tbascmd 18629
ASCMD 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
[16:07]ASCMD +> ls
+DS_DATA
+DS_REDO
- Number of diskspaces found: 2
[16:07]ASCMD +> cd +DS_DATA
[16:07]ASCMD +DS_DATA> ls
control/
DBPIWDBF/
- Number of files found: 2
[16:07]ASCMD +DS_DATA> cd DBPIWDBF
[16:08]ASCMD +DS_DATA/DBPIWDBF> ls
system001.dtf
undo001.dtf
temp001.dtf
usr001.dtf
tpr_ts.dtf
.passwd
undo101.dtf
test2_01.dtf
- Number of files found: 8
[16:08]ASCMD +DS_DATA/DBPIWDBF>