Document Type | Technical Information
Category | Monitoring/Inspection
Applicable Product Version | Tibero7
Document Number | TMOTI060
Overview
This is a script to check executed DML Queries.
However, DML Queries from a much earlier time may not be retrievable.
Method
How to Apply and Run the Script
Upload the attached dml_view_1216.tar file to the server, then apply it using the tar command.
[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
Run the script without any additional parameters.
[tibero7_kb]tibero@ENT-ROCKY8:/tibero/user/kyobin_jeon/test/DML_VIEW> sh dml_view.sh
Explanation of the Script Screen
This script consists of two menus. (The EXIT menu will not be explained separately.)
------------------------------------------------------------------
1. DML Search
2. DML Query View
x. EXIT
------------------------------------------------------------------
Choose the Number or Command :
Explanation of DML Search
To check DML, enter the time range you want to query.
The input format is 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 To check the query corresponding to the SQL_ID in the above result screen, run the 'DML Query View' menu.
Explanation of DML Query View
Copy the SQL_ID of the query you want to check, then enter it on the 'DML Query View' screen.
------------------------------------------------------------------
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