问题描述
团队，
正在阅读Oracle 12c @ 中的实时操作监视器 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/monitoring-database-operations.html#GUID-DC573FB7-40C5-4D6D-BE81-EF356900B444
这里是一个来自本地数据库的快速演示。
连接到:
甲骨文数据库12c企业版版本12.2.0.1.0-64位生产
现在切换回演示模式，并像这样结束操作。
当试图为整个数据库操作id = 2生成SQL监视器报告时，只报告了一个sql-id (通常是来自scott schema的最后执行的sql)，而不是两个sql的，请告知。
不知道为什么在我们之前的后续行动中没有回应，这有助于我们理解。
https://asktom.oracle.com/pls/apex/asktom.search?tag=how-can-i-track-the-execution-of-plsql-and-sql#9539966000346890225
正在阅读Oracle 12c @ 中的实时操作监视器 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/monitoring-database-operations.html#GUID-DC573FB7-40C5-4D6D-BE81-EF356900B444
这里是一个来自本地数据库的快速演示。
连接到:
甲骨文数据库12c企业版版本12.2.0.1.0-64位生产
demo@ORA12C> select sid,serial#
2 from v$session
3 where username ='SCOTT';
SID SERIAL#
---------- ----------
398 31583
demo@ORA12C> variable x number
demo@ORA12C> exec :x := dbms_sql_monitor.begin_operation(dbop_name=>'MY_DEMO2',session_id=>398,session_serial=>31583);
PL/SQL procedure successfully completed.
demo@ORA12C> print x
X
----------
2
Then switched to SCOTT schema and ran these two queries.
select /*+ monitor */ owner,object_type,count(*)
from big_Table
group by owner,object_type
order by 3 desc;
select /*+ monitor */ owner,max(object_id),count(*)
from big_Table
group by owner
order by 3 desc
fetch first 5 rows only ;
现在切换回演示模式，并像这样结束操作。
demo@ORA12C> exec dbms_sql_monitor.end_operation(dbop_name=>'MY_DEMO2',dbop_eid=>:x); PL/SQL procedure successfully completed.
当试图为整个数据库操作id = 2生成SQL监视器报告时，只报告了一个sql-id (通常是来自scott schema的最后执行的sql)，而不是两个sql的，请告知。
demo@ORA12C> set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feedback off demo@ORA12C> select dbms_sql_monitor.report_sql_monitor(dbop_exec_id=>2,type=>'text',report_level=>'ALL') from dual; SQL Monitoring Report SQL Text ------------------------------ select /*+ monitor */ owner,max(object_id),count(*) from big_Table group by owner order by 3 desc fetch first 5 rows only Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SCOTT (398:31583) SQL ID : 0p8wjxam3tns9 SQL Execution ID : 16777216 Execution Started : 02/04/2019 15:56:23 First Refresh Time : 02/04/2019 15:56:23 Last Refresh Time : 02/04/2019 15:56:25 Duration : 2s Service : ORA12c Program : sqlplus.exe Fetch Calls : 2 Global Stats =========================================================================== | Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | =========================================================================== | 1.71 | 0.28 | 1.33 | 0.10 | 2 | 18822 | 599 | 147MB | =========================================================================== SQL Plan Monitoring Details (Plan Hash Value=1236154803) ============================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) | ============================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +2 | 1 | 5 | | | . | | | | 1 | SORT ORDER BY | | 5 | 6474 | 1 | +2 | 1 | 5 | | | 2048 | | | | 2 | VIEW | | 5 | 6473 | 1 | +2 | 1 | 5 | | | . | | | | 3 | WINDOW SORT PUSHED RANK | | 907K | 6473 | 1 | +2 | 1 | 5 | | | 2048 | | | | 4 | HASH GROUP BY | | 907K | 6473 | 1 | +2 | 1 | 19 | | | 6MB | | | | 5 | TABLE ACCESS FULL | BIG_TABLE | 907K | 4009 | 2 | +1 | 1 | 1M | 599 | 147MB | . | | | ============================================================================================================================================================= demo@ORA12C>
不知道为什么在我们之前的后续行动中没有回应，这有助于我们理解。
https://asktom.oracle.com/pls/apex/asktom.search?tag=how-can-i-track-the-execution-of-plsql-and-sql#9539966000346890225
专家解答
我不认为这是意图，即，它不像SQL trace那样捕获所有 * details *。更重要的是，我们正在定义 * composite * 操作，并且我们希望 * 该 * 的执行统计信息。
我认为您看到最后一次操作的原因是参数验证中的一个缺点。因为您缺少dbop_name，所以我认为它只是进入默认操作 (即，最后执行的数据库操作)。
例如
如果我现在运行一些其他的东西，完全不相关...
您可以看到输出与标准report_sql_monitor调用没有什么不同，即发生的最后一个db操作。
我认为您看到最后一次操作的原因是参数验证中的一个缺点。因为您缺少dbop_name，所以我认为它只是进入默认操作 (即，最后执行的数据库操作)。
例如
SQL> variable exec_id number;
SQL> begin
2 :exec_id := dbms_sql_monitor.begin_operation ( dbop_name => 'demo' );
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select :exec_id from dual;
:EXEC_ID
----------
3
1 row selected.
SQL>
SQL> select /*+ monitor */ max(owner), min(object_id) from t
2 where object_id > 0
3 and log(object_id,2) > 0;
MAX(OWNER)
--------------------------------------------------------------------------------------------------------------------------------
MIN(OBJECT_ID)
--------------
XDB
2
1 row selected.
SQL>
SQL> select /*+ monitor */ max(x), max(object_id)
2 from
3 ( select owner, object_id, stddev(object_id) over ( partition by owner order by created ) as x
4 from t
5 );
MAX(X) MAX(OBJECT_ID)
---------- --------------
32629.3453 102702
1 row selected.
SQL>
SQL> begin
2 dbms_sql_monitor.end_operation ( dbop_name => 'demo', dbop_eid => :exec_id );
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT dbop_name, dbop_exec_id, status
2 FROM v$sql_monitor
3 where dbop_name = 'demo';
DBOP_NAME DBOP_EXEC_ID STATUS
------------------------------ ------------ -------------------
demo 3 DONE
1 row selected.
SQL>
SQL> set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feedback off
SQL> select dbms_sql_monitor.report_sql_monitor(dbop_name => 'demo',dbop_exec_id=>:exec_id ,type=>'text',report_level=>'ALL') from dual;
SQL Monitoring Report
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : MCDONAC (128:42919)
DBOP Name : demo
DBOP Execution ID : 3
First Refresh Time : 03/11/2019 11:55:53
Last Refresh Time : 03/11/2019 11:58:16
Duration : 143s
Module/Action : SQL*Plus/-
Service : pdb1
Program : sqlplus.exe
Global Stats
====================================================================================
| Elapsed | Cpu | IO | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
====================================================================================
| 142 | 137 | 4.81 | 0.21 | 1 | 11285 | 3GB | 2140 | 386MB |
====================================================================================
SQL> select dbms_sql_monitor.report_sql_monitor_list(dbop_name => 'demo', type=>'text',report_level=>'ALL') from dual;
SQL Monitoring List
=====================
===================================================================================================================================================================================
| Status | Duration | Inst Id | SQL Id or DBOP Name | Exec Id | Start | User | Module/Action | Dop | DB Time | IOs | SQL Text |
===================================================================================================================================================================================
| DONE | 143s | 1 | demo | 3 | 03/11/2019 11:55:53 | MCDONAC | SQL*Plus/- | | 142s | 13425 | |
===================================================================================================================================================================================
SQL>
SQL>
如果我现在运行一些其他的东西，完全不相关...
SQL> select /*+ monitor*/ * from scott.emp;
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> select dbms_sql_monitor.report_sql_monitor(dbop_exec_id=>:exec_id ,type=>'text',report_level=>'ALL') from dual;
SQL Monitoring Report
SQL Text
------------------------------
select /*+ monitor*/ * from scott.emp
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : MCDONAC (128:42919)
SQL ID : gwqz99dzxkzdd
SQL Execution ID : 16777216
Execution Started : 03/11/2019 12:05:31
First Refresh Time : 03/11/2019 12:05:31
Last Refresh Time : 03/11/2019 12:05:31
Duration : .001259s
Module/Action : SQL*Plus/-
Service : pdb1
Program : sqlplus.exe
Fetch Calls : 2
Global Stats
=================================================================
| Elapsed | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=================================================================
| 0.00 | 0.00 | 0.00 | 2 | 7 | 2 | 49152 |
=================================================================
SQL Plan Monitoring Details (Plan Hash Value=3956160932)
========================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
========================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | |
| 1 | TABLE ACCESS FULL | EMP | 14 | 3 | 1 | +0 | 1 | 0 | 2 | 49152 | | |
========================================================================================================================================
您可以看到输出与标准report_sql_monitor调用没有什么不同，即发生的最后一个db操作。
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
