SQL> select
sid,
sql_id,
status,
to_char(sql_exec_start, 'yyyy/mm/dd hh24:mi:ss') start_time
from
v$sql_monitor
order by
status, sql_exec_start
;
SID SQL_ID STATUS START_TIME
150 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10
148 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10
404 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10
393 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10
276 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10
28 dzw98k1wbh0ty DONE (ALL ROWS) 2022/02/16 14:47:10
30 awm8fgc8hw28q DONE (ALL ROWS) 2022/02/16 14:57:02
391 awm8fgc8hw28q DONE (ALL ROWS) 2022/02/16 14:57:02
275 awm8fgc8hw28q DONE (ALL ROWS) 2022/02/16 14:57:02
152 g3bca740t9wjp EXECUTING 2022/02/16 15:27:18
10 rows selected.
レポートの表示
SQL>
set long 10000 line 400 pages 1000 tab off
col data for a400
select dbms_sqltune.report_sql_monitor(
session_id=> 152,
sql_id=> 'g3bca740t9wjp',
type=>'TEXT'
) data from dual;
DATA
SQL Monitoring Report
SQL Text
select * from t01, t02 order by 1,2,3
Global Information
Status : EXECUTING
Instance ID : 1
Session : USER01 (152:63240)
SQL ID : g3bca740t9wjp
SQL Execution ID : 16777220
Execution Started : 02/16/2022 15:41:12
First Refresh Time : 02/16/2022 15:41:16
Last Refresh Time : 02/16/2022 15:41:24
Duration : 13s
Module/Action : SQL*Plus/-
Service : pdb1
Program : sqlplus@db01 (TNS V1-V3)
Global Stats
=========================================================
| Elapsed | Cpu | IO | Buffer | Write | Write |
| Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes |
=========================================================
| 12 | 7.21 | 4.50 | 38 | 3076 | 617MB |
=========================================================
SQL Plan Monitoring Details (Plan Hash Value=1870084974)
==========================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | | (%) | (# samples) |
==========================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 9 | +4 | 1 | 0 | | | . | . | | |
| -> 1 | SORT ORDER BY | | 1G | 6M | 11 | +2 | 1 | 0 | 3076 | 617MB | 745KB | 618MB | 90.91 | Cpu (2) |
| | | | | | | | | | | | | | | direct path write temp (8) |
| -> 2 | MERGE JOIN CARTESIAN | | 1G | 548K | 9 | +4 | 1 | 27M | | | . | . | | |
| -> 3 | TABLE ACCESS FULL | T02 | 100K | 69 | 9 | +4 | 1 | 2656 | | | . | . | | |
| -> 4 | BUFFER SORT | | 10000 | 6M | 9 | +4 | 2656 | 27M | | | 424KB | . | 9.09 | Cpu (1) |
| 5 | TABLE ACCESS FULL | T01 | 10000 | 5 | 1 | +4 | 1 | 10000 | | | . | . | | |
==========================================================================================================================================================================
