4. Tibero Monitoring
4.7. Top SQL Info
Case 별 Top sql 을 추출해서 개선할 여지가 있는지 확인합니다.
4.7.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
/
USERNAME 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
4.7.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/EXECUTIONS/1000000,3) as “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 7cgswupkpbmz/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
4.7.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/EXECUTIONS/1000000,3) as “Elaped_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
4.8. 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/tibero6, failed retry cnt: 0
cluster1 db tac1 UP(NRML) tac, /home/tibero/tibero6, 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/tibero6, failed retry cnt: 0
cluster1 db tac2 UP(NRML) tac, /home/tibero/tibero6, 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
=====================================================================
4.9. Active Storage Info (Using TAC)
| Command |
| $ tbascmd <tas_port> |
Example
tibero@edu1[tac1]:/home/tibero> tbascmd 8029
ASCMD 6
TmaxData Corporation Copyright (c) 2008-. All rights reserved.
ASCMD> ls
DS0
ASCMD> cd DS0
current path = +DS0
ASCMD> ls
<File_name>
tac/
– Number of files found: 1
ASCMD> cd tac
current path = +DS0/tac
ASCMD> ls
<File_name>
control/
redo/
datafile/
– Number of files found: 3
ASCMD>
