TA的每日心情 | 开心
2023-8-9 11:05 |
|---|
|
GoalSQL Monitor is a useful feature to monitor and diagnose statements running with parallel execution plan.
The article provides the steps to generate SQL Monitor output for such a statement.
This feature is available since 11.1.
SolutionNOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
V$SQL_MONITOR view displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored.
SQL monitoring is automatically started when a SQL statement runs parallel (it uses parallel workers). Otherwise you can force to get the statement monitored by using the MONITOR hint (e.g. if you are not sure whether the statement will eventually run with parallel plan, you add the MONITOR hint to the statement, and you will get it monitored even if the statement ran with serial plan).
When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored.
For a particular SQL statement you can generate a nice output with the DBMS_SQLTUNE.report_sql_monitor procedure instead of querying the V$SQL_MONITOR view.
STEPS1./ Run the SQL of interest from your session. A good practice is to use the MONITOR hint, so you get monitoring information even if the statement eventually ran with serial execution plan.
SELECT /*+MONITOR*/ ...<rest of the SQL of interest>;
2./ Then immediately after the query of interest execute either of the following depending on your needs. This will return the last monitored SQL in your session.
OPTION A./ Get the SQL Monitoring output in text format on screen
SET LINES 2000 PAGES 0 LONG 200000 LONGC 2000
SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'), type => 'TEXT',report_level=>'ALL') AS report FROM dual;
OPTION B./ Create HTML file sql_mon_active.html with active content and more verbose information:
SET LINES 2000 PAGES 0 LONG 200000 LONGC 2000
spool sql_mon_active.html
SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'), type => 'ACTIVE',report_level=>'ALL') AS report FROM dual;
spool off
There are other arguments which you can call the DBMS_SQLTUNE.REPORT_SQL_MONITOR function e.g. SQL_ID for which monitoring information should be displayed.
For further information look up DBMS_SQLTUNE.REPORT_SQL_MONITOR function in the Database PL/SQL Packages and Types Reference.
The following examples show monitoring information for the last executed SQL in the session in text form and in active html form, the latter is similar to what one can find in the corresponding page in Enterprise Manager.
EXAMPLE FOR TEXT OUTPUT ON THE SCREEN
SQL>SELECT /*+MONITOR*/ *
2 FROM
3 (SELECT times.calendar_quarter_desc,
4 customers.cust_first_name,
5 customers.cust_last_name,
6 customers.cust_id,
7 SUM(sales.amount_sold),
8 rank() over(PARTITION BY times.calendar_quarter_desc
9 ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter
10 FROM sales, customers,times
11 WHERE sales.cust_id = customers.cust_id
12 AND times.calendar_quarter_desc = '2001-04'
13 AND times.time_id = sales.time_id
14 GROUP BY customers.cust_id,
15 customers.cust_first_name,
16 customers.cust_last_name,
17 customers.cust_id,
18 times.calendar_quarter_desc)
19 WHERE rank_within_quarter < 16;
SQL>SET ECHO OFF FEED OFF VER OFF SHOW OFF HEA OFF LINES 2000 NEWP NONE PAGES 0 LONG 200000 LONGC 2000 SQLC MIX TAB ON TRIMS ON TI OFF TIMI OFF ARRAY 100 NUMF "" SQLP SQL> SUF sql BLO . RECSEP OFF APPI OFF AUTOT
OFF
SQL>SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'), type => 'TEXT',report_level=>'ALL') AS report FROM dual;
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+MONITOR*/ * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_name, customers.cust_last_name, customers.cust_id, SUM(sales.amount_sold), rank() over(PARTITION BY times.calendar_quarter_de
sc ORDER BY SUM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers,times WHERE sales.cust_id = customers.cust_id AND times.calendar_quarter_desc = '2001-04' AND times.time_id = sales.time_id GROUP
BY customers.cust_id, customers.cust_first_name, customers.cust_last_name,
customers.cust_id, times.calendar_quarter_desc) WHERE rank_within_quarter < 16
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SH (395:391)
SQL ID : <SQL_ID>
SQL Execution ID : 16777218
Execution Started : 11/30/2013 10:24:26
First Refresh Time : 11/30/2013 10:24:26
Last Refresh Time : 11/30/2013 10:24:26
Duration : .131806s
Module/Action : SQL*Plus/-
Service : <service_name>
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 0.97 | 0.58 | 0.01 | 0.37 | 1 | 1785 | 121 | 11MB |
===========================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=16)
====================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Other | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
====================================================================================================================
| PX Coordinator | QC | | 0.13 | 0.01 | | 0.12 | 68 | | . | |
| p000 | Set 1 | 1 | 0.08 | 0.06 | 0.00 | 0.02 | 223 | 15 | 2MB | |
| p001 | Set 1 | 2 | 0.06 | 0.06 | 0.00 | 0.01 | 165 | 12 | 1MB | |
| p002 | Set 1 | 3 | 0.06 | 0.02 | 0.00 | 0.04 | 180 | 14 | 1MB | |
| p003 | Set 1 | 4 | 0.08 | 0.02 | 0.00 | 0.06 | 210 | 18 | 2MB | |
| p004 | Set 1 | 5 | 0.06 | 0.04 | 0.00 | 0.02 | 165 | 15 | 1MB | |
| p005 | Set 1 | 6 | 0.08 | 0.05 | 0.01 | 0.03 | 180 | 14 | 1MB | |
| p006 | Set 1 | 7 | 0.08 | 0.04 | 0.00 | 0.04 | 195 | 16 | 1MB | |
| p007 | Set 1 | 8 | 0.09 | 0.08 | 0.00 | 0.01 | 240 | 17 | 2MB | |
| p008 | Set 2 | 1 | 0.03 | 0.02 | | 0.01 | 3 | | . | |
| p009 | Set 2 | 2 | 0.04 | 0.04 | | | 30 | | . | |
| p010 | Set 2 | 3 | 0.03 | 0.02 | | 0.00 | 30 | | . | |
| p011 | Set 2 | 4 | 0.03 | 0.02 | | 0.00 | 27 | | . | |
| p012 | Set 2 | 5 | 0.03 | 0.03 | | 0.01 | 9 | | . | |
| p013 | Set 2 | 6 | 0.03 | 0.03 | | | 6 | | . | |
| p014 | Set 2 | 7 | 0.03 | 0.02 | | 0.01 | 27 | | . | |
| p015 | Set 2 | 8 | 0.03 | 0.03 | | 0.00 | 27 | | . | |
====================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1792038910)
=======================================================================================================================================================================
| 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 | | | | | | 17 | | | | | | |
| 1 | PX COORDINATOR | | | | | | 17 | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 56048 | 239 | | | 8 | | | | | | |
| 3 | VIEW | | 56048 | 239 | | | 8 | | | | | | |
| 4 | WINDOW SORT PUSHED RANK | | 56048 | 239 | | | 8 | | | | | | |
| 5 | PX RECEIVE | | 56048 | 239 | | | 8 | | | | | | |
| 6 | PX SEND HASH | :TQ10003 | 56048 | 239 | | | 8 | | | | | | |
| 7 | WINDOW CHILD PUSHED RANK | | 56048 | 239 | | | 8 | | | | | | |
| 8 | HASH GROUP BY | | 56048 | 239 | | | 8 | | | | | | |
| 9 | HASH JOIN | | 56048 | 137 | 1 | +0 | 8 | 0 | | | 12M | | |
| 10 | PX RECEIVE | | 55500 | 57 | 1 | +0 | 8 | 55500 | | | | | |
| 11 | PX SEND HASH | :TQ10001 | 55500 | 57 | 1 | +0 | 8 | 55500 | | | | | |
| 12 | PX BLOCK ITERATOR | | 55500 | 57 | 1 | +0 | 8 | 55500 | | | | | |
| 13 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 57 | 1 | +0 | 104 | 55500 | 121 | 11MB | | | |
| 14 | PX RECEIVE | | 56048 | 80 | | | 8 | | | | | | |
| 15 | PX SEND HASH | :TQ10002 | 56048 | 80 | | | 8 | | | | | | |
| 16 | HASH JOIN | | 56048 | 80 | 1 | +0 | 8 | 0 | | | 9M | | |
| 17 | PART JOIN FILTER CREATE | :BF0000 | 91 | 3 | 1 | +0 | 8 | 736 | | | | | |
| 18 | PX RECEIVE | | 91 | 3 | 1 | +0 | 8 | 736 | | | | | |
| 19 | PX SEND BROADCAST | :TQ10000 | 91 | 3 | 1 | +0 | 8 | 736 | | | | | |
| 20 | PX BLOCK ITERATOR | | 91 | 3 | 1 | +0 | 8 | 92 | | | | | |
| 21 | TABLE ACCESS FULL | TIMES | 91 | 3 | 1 | +0 | 53 | 92 | | | | | |
| 22 | PX BLOCK ITERATOR | | 783K | 73 | | | 8 | | | | | | |
| 23 | TABLE ACCESS FULL | SALES | 783K | 73 | | | 102 | | | | | | |
=======================================================================================================================================================================
SQL>
EXAMPLE FOR HTML FILE sql_mon_active.htmlBy executing the following immediately after the query of interest, the SQL Monitor output goes to an html file sql_mon_active.html. An example of such file can be seen here.
SET ECHO OFF FEED OFF VER OFF SHOW OFF HEA OFF LINES 2000 NEWP NONE PAGES 0 LONG 200000 LONGC 2000 SQLC MIX TAB ON TRIMS ON TI OFF TIMI OFF ARRAY 100 NUMF "" SQLP SQL> SUF sql BLO . RECSEP OFF APPI OFF AUTOT OFF
spool sql_mon_active.html
SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'), type => 'ACTIVE',report_level=>'ALL') AS report FROM dual;
spool off
|
|
发表于 2021-11-11 09:58:55