문서유형ㅣ기술정보
분야ㅣ 모니터링/점검
적용제품버전ㅣ Tibero7
문서번호ㅣTMOTI060
개요
수행된 DML Query를 확인하기 위한 Script입니다.
다만, 시간이 많이 지난 시점의 DML Query는 확인되지 않을 수 있습니다.
방법
Script 적용 및 수행 방법
첨부된 dml_view_1216.tar 파일을 서버에 업로드한 후, tar 명령을 사용하여 적용합니다.
[tibero7_kb]tibero@ENT-ROCKY8:/tibero/user/kyobin_jeon/test> tar -xvf dml_view_1216.tar
DML_VIEW/
DML_VIEW/sql/
DML_VIEW/sql/8_sql_plan_7.sql
DML_VIEW/dml_view.sh
Script는 별도의 parameter 없이 실행합니다.
[tibero7_kb]tibero@ENT-ROCKY8:/tibero/user/kyobin_jeon/test/DML_VIEW> sh dml_view.sh
Script 화면 설명
본 Script는 2개의 메뉴로 구성되어 있습니다. (EXIT 메뉴는 별도 설명하지 않습니다.)
------------------------------------------------------------------
1. DML Search
2. DML Query View
x. EXIT
------------------------------------------------------------------
Choose the Number or Command :
DML Search 설명
DML을 확인하기 위해 조회하고자 하는 시간 범위를 입력하면 됩니다.
입력값의 형식(Format)은 YYYYMMDDHH24MISS입니다.
------------------------------------------------------------------
1. DML Search
2. DML Query View
x. EXIT
------------------------------------------------------------------
Choose the Number or Command : 1
------------------------------------------------------------------
search date input.....
(input date format -> YYYYMMDDHH24MISS)
start date : 20251216102000
end date : 20251217000000
------------------------------------------------------------------
Session altered.
PLAN_HASH_VALUE SQL_ID COMMAND_TYPE CHILD_NUMBER FIRST_LOAD_TIME LAST_ACTIVE_TIME ELAPSED_TIME
--------------- ------------- ------------ ------------ ------------------------- ------------------------- ------------
287381922 67pkv6vwkn0ym 1 857 20251216105431 20251216110112 15424
392577947 0rxmgdn014b5a 1 823 20251216102406 20251216110046 979
629491925 5h70xvf97ghq2 1 860 20251216105505 20251216105701 1325
816406137 74ucztyhpwdru 1 831 20251216102455 20251216110048 653
816406137 a66rh096yn0w1 1 833 20251216102455 20251216110048 635
816406137 bbna71dx1gjfd 1 836 20251216102455 20251216110048 658
907513943 dhdbtdxgm8b0s 1 837 20251216102455 20251216110048 715
935352627 0zc5j61ay105d 1 832 20251216102455 20251216110048 1404
1026807392 91juka9kghp9k 1 844 20251216104430 20251216104746 17650
4238698534 0hsnwrw3pcakt 4 130 20251216111438 20251216153351 1943 위 수행 결과 화면에서 SQL_ID에 해당하는 Query를 확인하려면 'DML Query View' 메뉴를 수행합니다.
DML Query View 설명
확인하고자 하는 Query의 SQL_ID를 복사한 후 'DML Query View' 화면에 입력합니다.
------------------------------------------------------------------
1. DML Search
2. DML Query View
x. EXIT
------------------------------------------------------------------
Choose the Number or Command : 2
==========
SQL PLAN
==========
INPUT SQL_ID(ex: sql_id/sql_child_number ) : 0hsnwrw3pcakt/130
## SQL Info
----------------------------------------------------------------------------------------------------
SQL_ID CHILD_NUMBER HASH_VALUE PLAN_HASH_VALUE Gets/Exec Elap/Exec(ms) EXECUTIONS
------------- ------------ ---------- --------------- ---------- ------------- ----------
0hsnwrw3pcakt 130 123087449 4238698534 15 .635 1
## SQL TEXT
----------------------------------------------------------------------------------------------------
delete from "SYS"."SYS_NOTICE_Q_TABLE" p where not exists (selec
t 1 from "SYS"."AQ$_SYS_NOTICE_Q_TABLE_I" i where p.msgid = i.ms
gid)
.----------------.
| Execution Plan |
+-----------------------------------------------------------------------------------------
1 DELETE:SYS_NOTICE_Q_TABLE(Cost:5,%%CPU:0,Card:1)
2 TABLE ACCESS (ROWID):SYS_NOTICE_Q_TABLE(Cost:5,%%CPU:0,Card:1)
3 HASH JOIN (ANTI)(Cost:4,%%CPU:0,Card:1)
4 INDEX (FULL):AQ$_SYS_NOTICE_Q_TABLE_I(Cost:2,%%CPU:0,Card:1)
5 INDEX (FULL) DESCENDING:AQ$_SYS_NOTICE_Q_TABLE_I_PK(Cost:2,%%CPU:0,Card:1)
+-----------------------------------------------------------------------------------------
| Execution Stats |
+-----------------------------------------------------------------------------------------
1 DELETE: SYS_NOTICE_Q_TABLE (Time:0. ms, Rows:0, Starts:0)
2 TABLE ACCESS (ROWID): SYS_NOTICE_Q_TABLE (Time:0. ms, Rows:0, Starts:0)
3 HASH JOIN (ANTI) (Time:0. ms, Rows:0, Starts:0)
4 INDEX (FULL): AQ$_SYS_NOTICE_Q_TABLE_I (Time:0. ms, Rows:0, Starts:0)
5 INDEX (FULL) DESCENDING: AQ$_SYS_NOTICE_Q_TABLE_I_PK (Time:0. ms, Rows:0, Starts:0)
.------------------.
| DBMS_XPLAN (ALL) |
+-----------------------------------------------------------------------------------------
SQL ID : 0hsnwrw3pcakt
CHILD NUMBER : 130
HASH VALUE : 123087449
PLAN HASH VALUE: 4238698534
EXECUTIONS : 1
FETCHES : 0
LOADED AT : 2025/12/16 11:14:38
TOT ELAPSED TIME: 00:00:00.0006
AVG ELAPSED TIME: 00:00:00.0006
TOT BUFFER GETS: 15
AVG BUFFER GETS: 15
SQL :
delete from "SYS"."SYS_NOTICE_Q_TABLE" p where not exists (select 1 from "SYS"."AQ$_SYS_NOTICE_Q_TABLE_I" i where p.msgid = i.msgid)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts | Used Mem | Temp. Read | Temp. Write| Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | DELETE | SYS_NOTICE_Q_TABLE | 5 (0)| 1 | 0 |00:00:00.0000 | 0 | 0 | 0K | 0 | 0 | 0 |
| 2 | TABLE ACCESS (ROWID) | SYS_NOTICE_Q_TABLE | 5 (0)| 1 | 0 |00:00:00.0000 | 0 | 0 | 0K | 0 | 0 | 0 |
| 3 | HASH JOIN (ANTI) | | 4 (0)| 1 | 0 |00:00:00.0000 | 0 | 0 | 0K | 0 | 0 | 0 |
| 4 | INDEX (FULL) | AQ$_SYS_NOTICE_Q_TABLE_I | 2 (0)| 1 | 0 |00:00:00.0000 | 0 | 0 | 0K | 0 | 0 | 0 |
| 5 | INDEX (FULL) DESCENDING | AQ$_SYS_NOTICE_Q_TABLE_I_PK | 2 (0)| 1 | 0 |00:00:00.0000 | 0 | 0 | 0K | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 - access: ("P"."MSGID" = "I"."MSGID") (0.002)
Outlines
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
No Outline Created