V$SQL_MONITOR 5秒起一秒一次，保留一分钟 OEM工作原理
----------OEM sql monitoring 的sql-------
GOAL
How to view the same information from a target database without going through OEM performance to monitor SQL performance:
OEM > targets > databases >select a target database> click on performance. sql monitoring>
SOLUTION
Queries are derived from the view V$SQL_MONITOR for OEM, and inbuilt query.
Use query something like below:
select sid, sql_id, sql_exec_id, to_char(sql_exec_start,'DD-Mon-YY HH24:MI:SS') sql_exec_start, sql_plan_hash_value plan_hash_value,
elapsed_time buffer_gets, disk_reads
from v$sql_monitor
where sql_id like nvl('&sql_id',sql_id)
----- purge records from v$Sql_monitor
Why does one database have more records in V$Sql_monitor compared to another database, and how to purge records from v$Sql_monitor?
SOLUTION
There are two important parameters which control the number of records in V$Sql_Monitor:
1. _sqlmon_max_plan: Default = 20 per CPU(Default). This parameter controls how many SQLs can be monitored which means that at any point of time "select distinct(sql_id) from v$SQL_MONITOR does not return more than 20 * Num.of.Cpu on the server where this DB is hosted"
2. _sqlmon_max_planlines: Default = 300(Default). When a statement exceeds that many lines, it does not get monitored by SQL Monitor, so if length of query is more than 300 lines, it will not be monitored
SQL monitor has a mechanism to recycle entries that correspond to old SQL executions, as long as there is not enough memory to allocate new ones. If the Database SGA size is quite large, then the code that recycles them does not kick in. This results in entries which keep adding up in V$sql_monitor.
There is currently no workaround to reduce the number of records(purge records) in v$sql_monitor other than bouncing the DB. If you have more records in V$sql_monitor than you see many rows for each sql_id, plan_hash_value combination is that we also use a timestamp (execution start time) in each row. So if a SQL is executed multiple times, it is expected that you will see many rows for that SQL in v$sql_monitor.
As a side note it is now possible to flush entries from v$sql_monitor with the following command taken from Document 2792268.1:
alter session set events 'immediate sqlmon_dump(level=23)';
------生成 dbms_sqltune.report_sql_monitor
GOAL
This document explains how to enable Explain Plan, SQL tuning, Statistics and Tuning Advisor from the OWB mapping editor for SQL within the mapping.
SOLUTION
For analysis of set based SQL from the design environment, there were some changes in Warehouse Builder 10.2.0.3 that included the ability to obtain access explain plans/statistics/tuning advisor from within the mapping editor for SQL within the mapping.
In order to utilize statistics and SQL Tuning, the following additional privileges are required :
- For statistics the following must be granted to the schema the mapping is being deployed to:
SQL> connect SYS as SYDBA
SQL>grant select on V_$SQL_PLAN to <TARGET_SCHEMA>;
SQL>grant select on V_$SQL_PLAN_STATISTICS_ALL to <TARGET_SCHEMA>;
SQL>grant select on V_$SQL to <TARGET_SCHEMA>;
- For SQL Tuning the advisor role is required:
SQL> grant advisor to <target_schema>;
If runtime analysis of SQL is required, the Oracle Database Release 11g has introduced a new performance view called v$sql_monitor. The v$sql_monitor view can be used for near real-time monitoring of SQL statements.
Please note that view can be used when the Enterprise Manager Tuning Pack is licensed. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.
In Oracle Database Release 11g the way to get output in a readable format is through PL/SQL or grid control:
variable my_rept clob;
begin
:my_rept := dbms_sqltune.report_sql_monitor() ;
end ;
/
print :my_rept
Full documentation for v$sql_monitor can be found in the Database Reference and the Database Performance Tuning Guide.
-------并行的 "Servers Requested" and "Servers Allocated" 显示bug
When viewing details of a particular query, (which is executed high number of times), from SQL Monitor, very high values are seen for "Servers Requested" and "Servers Allocated" in the "Parallel Execution Details".
For example:
SH@dw23> SET LONG 1000000
SH@dw23> SET LONGCHUNKSIZE 1000000
SH@dw23> SET LINESIZE 1000
SH@dw23> SET PAGESIZE 0
SH@dw23> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => <SQL ID>, type => 'TEXT',report_level=>'ALL') AS report FROM dual;
SQL Monitoring Report
SQL Text
------------------------------
SELECT * FROM (SELECT /*+ parallel(4) */ a.column1, b.column2....<rest of the query>
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (510:56699)
SQL ID : 62ujw625atm4d
SQL Execution ID : 16777218
Execution Started : 01/04/2024 11:59:45
First Refresh Time : 01/04/2024 11:59:45
Last Refresh Time : 01/04/2024 12:00:00
Duration : 15s
Module/Action : sqlplus@.... (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@.... (TNS V1-V3)
DOP Downgrade : 3%
Fetch Calls : 5
Global Stats
=====================================================================================
| Elapsed | Cpu | IO | PL/SQL | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=====================================================================================
| 6.80 | 0.58 | 0.00 | 0.00 | 6.21 | 5 | 59121 | 3 | 24576 |
=====================================================================================
Parallel Execution Details (DOP=4 , Servers Requested=10600 , Servers Allocated=10296)
^^^^ ^^^^
.............
The Degree of Parallelism (DOP) however is set correctly to 4, as chosen in the PARALLEL Hint for the query.
The DOP downgrade is also listed as just 3% but we see:
Servers Requested=10600
Servers Allocated=10296
This document explains why the values for "Servers Requested" and "Servers Allocated" are shown as such high values.
CHANGES
CAUSE
This was investigated in an internal / unpublished Bug 11898399 - V$SQL_MONITOR REPORTS WRONG NUMBER OF PQ SLAVE SERVERS VALUE
DEV clarified that this is an expected behaviour. In the SQL Monitor report or v$sql_monitor view, the two columns - PX_SERVERS_REQUESTED (Servers Requested) and PX_SERVERS_ALLOCATED (Servers Allocated) are used to track how many PQ severs have been allocated in total. Each time a parallelizer row source executes, it potentially allocates new slaves and these slaves are potentially released during the next execution of this parallelizer.
Hence, these stats will keep increasing with each execution.
SOLUTION
The high values do not imply that these many PX slaves have been allocated for "each" execution. The difference between the servers allocated and requested is not high and there is also no high DOP downgrade seen, in the above case.
To monitor the PX executions, you can also use the steps listed provided in:
How to Monitor the Parallel Statement Queue (Doc ID 1684985.1)
Script To Monitor Parallel Queries/SQL (Doc ID 457857.1)
-------------------------EM13c, EM12c: How to Obtain The Data For The "Top Activity" Graph from SQL query ? (Doc ID 2635069.1)
GOAL
To get the Top Activity data for a specific target database using SQL query
SOLUTION
The following query can be used by Enterprise Manager (EM) Cloud Control Agent to fetch data for Top Activity page:
SELECT sql_id, sql_exec_id, session_id, session_serial, status, round((last_refresh - sql_exec_start )*24*60*60) duration, elapsed_time, cpu_time, user_io_wait_time, cluster_wait_time, application_wait_time, other_wait_time, nvl(dop,0) dop, inst_count FROM ( SELECT mo5.* FROM ( SELECT mo4.sql_id sql_id, mo4.sql_exec_start sql_exec_start, mo4.sql_exec_id sql_exec_id, (select sq.sql_text from v$sql sq where sq.sql_id = mo4.sql_id and rownum = 1) sql_fulltext, mo4.sql_plan_hash_value plan_hash_value, max(mo4.status) status, max(case when mo4.px_qcsid is null then mo4.sid else null end) session_id, max(case when mo4.px_qcsid is null then mo4.session_serial# else null end) session_serial, max(mo4.first_refresh_time) first_refresh, max(mo4.last_refresh_time) last_refresh, count(distinct mo4.inst_id) inst_count, max(case when mo4.px_server_group = 1 and mo4.px_server_set = 1 then mo4.px_server# else NULL end) dop, sum(mo4.fixed_elapsed_time) elapsed_time, sum(mo4.cpu_time) cpu_time, sum(mo4.application_wait_time) application_wait_time, sum(mo4.cluster_wait_time) cluster_wait_time, sum(mo4.user_io_wait_time) user_io_wait_time, sum(mo4.other_wait_time) other_wait_time FROM (SELECT mo1.*, case when (nvl(mo1.cpu_time, 0)+ nvl(mo1.application_wait_time,0)+ nvl(mo1.cluster_wait_time, 0)+ nvl(mo1.user_io_wait_time, 0)) > nvl(mo1.elapsed_time, 0) then (nvl(mo1.cpu_time, 0)+ nvl(mo1.application_wait_time,0)+ nvl(mo1.cluster_wait_time, 0)+ nvl(mo1.user_io_wait_time, 0)) else mo1.elapsed_time end fixed_elapsed_time, case when (nvl(mo1.cpu_time, 0)+ nvl(mo1.application_wait_time,0)+ nvl(mo1.cluster_wait_time, 0)+ nvl(mo1.user_io_wait_time, 0)) > nvl(mo1.elapsed_time, 0) then 0 else mo1.elapsed_time - (nvl(mo1.cpu_time, 0)+ nvl(mo1.application_wait_time,0)+ nvl(mo1.cluster_wait_time, 0)+ nvl(mo1.user_io_wait_time, 0)) end other_wait_time FROM (select * from gv$sql_monitor mo0 where mo0.INST_ID = userenv('INSTANCE') or PX_QCINST_ID is not null) mo1, (SELECT distinct mo2.sql_id, mo2.sql_exec_start, mo2.sql_exec_id FROM v$sql_monitor mo2 WHERE mo2.px_qcsid is null ) mo3 WHERE mo1.sql_id = mo3.sql_id AND mo1.sql_exec_start = mo3.sql_exec_start AND mo1.sql_exec_id = mo3.sql_exec_id) mo4 GROUP BY mo4.sql_id, mo4.sql_exec_start, mo4.sql_exec_id, mo4.sql_plan_hash_value ORDER BY last_refresh desc, mo4.sql_id ) mo5 WHERE mo5.sql_fulltext is not null ORDER BY mo5.elapsed_time desc) WHERE ROWNUM <= 5;
-------------sql_id will only show the top level user cursor ( if any). 难表述
GOAL
In 11.2, SQL_ID column of V$session view doesn't have any values for some sessions, Why is that happening?
SOLUTION
From 10.2 onwards sql_id will only show the top level user cursor ( if any).
Sometimes,Session does a plsql rpc call ie a client ( eg forms client) can request a plsql object to be executed at the server. In this case there is no top level sql and as such we don't see the sql_id in v$session.
Currently, Oracle does not store the current sql thru any v$ views. The only way to find that is thru event 10046.
This is covered in Unpublished bug 5528670: "NULL VALUES FOR SQL_ID, SQL_HASH_VALUE, SQL_ADDRESS AND OTHERS IN V$SESSION", Which was closed as "Not a Bug".
------并行session 某个session 空闲超过30分钟
SYMPTOMS
- Running DBMS_SQLTUNE.REPORT_SQL_MONITOR
For example:SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SESSION_ID=>&1,
SESSION_SERIAL=>&2,
INST_ID=>&3,
report_level=>'ALL',
type =>'ACTIVE') as report - Query is running in parallel (PX)
- After 30 minutes V$SQL_MONITOR.STATUS shows DONE(ERROR) even though the query is still running
CHANGES
CAUSE
This was filed under Bug 13523091 INCORRECT STATUS IN V$SQL_MONITOR WITH PARALLEL QUERY and closed as not a bug
The DONE(ERROR) occurs when one of the parallel slaves has been inactive for more than 30 minutes.
When MMON finds out that an entry that has active plan is idle for more than 30 minutes, it marks the entry as possible for reclamation with potential error. It is marked as error because there could have been an error in this plan that MMON was unable to detect,
considering that it is spending such a long time (30 minutes) as "idle".
If there are no other errors during the execution the monitor report will be correct and will contain all the usual information.
The error does not indicate that SQL monitoring failed, nor does it indicate that the monitored SQL failed.
Following are the possible statuses for V$SQL_MONITOR.STATUS:
- QUEUED - SQL statement is queued
- EXECUTING - SQL statement is still executing
- DONE (ERROR) - Execution terminated with an error
- DONE (FIRST N ROWS) - Execution terminated by the application before all rows were fetched
- DONE (ALL ROWS) - Execution terminated and all rows were fetched
- DONE - Execution terminated (parallel execution)
SOLUTION
This is expected behavior for parallel queries where one (or more) of the slaves are inactive for more than 30 minutes.
---How to monitor long running operations i.e "insert", "move", "create index" etc (Doc ID 2821878.1) 监控并行session 进度
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
GOAL
Monitor the long running jobs i.e. insert, move, create index etc
SOLUTION
1/- Identify the size of "table" / "table partition" participating in operation
SQL> select /* parallel 4 */ count(*) from demo partition(future);
COUNT(*)
----------
64000000
SQL>
2/- Identify the SQL_ID of the interested running job
set lines 200
col sql_text format a110
select sql_id,substr(sql_text,0,110) sql_text from v$sql_monitor where status='EXECUTING';
Example:
SQL> set lines 200
SQL> col sql_text format a110
SQL> select sql_id,substr(sql_text,0,110) sql_text from v$sql_monitor where status='EXECUTING';
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------------------------------------
2x3k24vzu66sz insert /* parallel 4 */ into t13 select /* parallel 4 */* from demo partition(future)
3/- Update the below script with
-- Out put of the query 1
-- running SQL_ID
select distinct to_char(a.sql_exec_start,'DD/MM/YYYY HH24:MI:SS') start_time ,
plan_parent_id,
plan_operation "Operation",
trunc(elapsed_time/1000000,0) "TimeElapsed(seconds)",a.output_rows "No of rows processed",
trunc((a.output_rows/<OUTPUT from QUERY 1>)*100,2) "Progress%"
from v$sql_plan_monitor a ,v$sql_monitor b
where a.status not like '%DONE%'
and a.key = b.key
and a.output_rows <>0
and b.sql_id='<SQL_ID from Query 2>'
order by plan_operation;
Example:
SQL> select distinct to_char(a.sql_exec_start,'DD/MM/YYYY HH24:MI:SS') start_time ,plan_parent_id,
plan_operation "Operation",
trunc(elapsed_time/1000000,0) "TimeElapsed(seconds)",a.output_rows "No of rows processed",
trunc((a.output_rows/64000000)*100,2) "Progress%"
from v$sql_plan_monitor a ,v$sql_monitor b
where a.status not like '%DONE%'
and a.key = b.key
and a.output_rows <>0
and b.sql_id='2x3k24vzu66sz'
order by plan_operation;
START_TIME PLAN_PARENT_ID Operation TimeElapsed(seconds) No of rows processed Progress%
------------------- -------------- ------------------------------ -------------------- -------------------- ----------
16/11/2021 11:47:25 1 PARTITION RANGE 77 23216816 36.27
16/11/2021 11:47:25 2 TABLE ACCESS 77 23216816 36.27
SQL> /
START_TIME PLAN_PARENT_ID Operation TimeElapsed(seconds) No of rows processed Progress%
------------------- -------------- ------------------------------ -------------------- -------------------- ----------
16/11/2021 11:47:25 1 PARTITION RANGE 83 25125915 39.25
16/11/2021 11:47:25 2 TABLE ACCESS 83 25125915 39.25
SQL> /
START_TIME PLAN_PARENT_ID Operation TimeElapsed(seconds) No of rows processed Progress%
------------------- -------------- ------------------------------ -------------------- -------------------- ----------
16/11/2021 11:47:25 1 PARTITION RANGE 191 57981661 90.59
16/11/2021 11:47:25 2 TABLE ACCESS 191 57981661 90.59
SQL>
GOAL
All active SQL statements are not seen in OEM 13c - SQL Monitoring while it is shown in V$session.
SOLUTION
Not all SQL will be 'monitored' in SQL Monitoring
This is expected behavior. If the SQL takes less than 5 seconds to execute, it will not be seen in v$sql_monitor:
Oracle Database Online Documentation 11g Release 1 (11.1)
Database Administration
The sql statistics for SQL command execution can be monitored by using the V$SQL_MONITOR and V$SQL_PLAN_MONITOR views. After monitoring is initiated, an entry is added to the V$SQL_MONITOR dynamic performance view. These statistics are refreshed in near real time as the command executes, generally once every second. 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.
So your SQL may be aged out before you look at it. Remember that OEM information is not real-time. It is collected by the agents and then batched up and eventually shown on the OEM screens.
Things to remember
1. Also make sure statistics_level =ALL or TYPICAL and control_management_pack_access = DIAGNOSTIC+TUNING
2. If a specific SQL needs to be monitored, use the monitoring hint /*+ MONITOR */
Why does v$session contains information about all sessions unlike SQL Monitoring
V$sessions is used by the system to track all sessions so it must contain information about all sessions. It is also real-time, as the system has to rely on it. The monitoring views and tables are for monitored SQLs. This is not all sessions. Secondly monitoring information is not kept for ever. It will be aged out.
Thirdly OEM will always have a lag in it because it has to collect the information batch it up and then present it. OEM (Cloud Control) is NOT real time, like v$session.
Example scenario where the SQL is not shown
11:00:00 SQL starts and takes 20 seconds. Some information is in V$session as this process is active. No information is in any monitored tables. Nothing is shown by OEM. Agent collects information.
11:00:20 It will be collected by monitoring (as it is over 5 seconds).
11:01:20 One minute later the SQL has aged out from monitoring (worst case scenario). If the statement was in the top 20 for the snapshot interval it will go into the history but it wasn't so it does not go into the history.----top20 才去历史
11:15:00 The agent makes another collection of information to see what is being monitored. The SQL monitoring information is not there. Nothing relating to the statement is sent to OEM.
In the scenario above, the test SQL will not show in the monitored SQL in OEM. After 2 minutes it will also not be present in the Monitoring tables. At the time it was running, the information will be present in v$session.
This is the purpose of the "monitoring" hints --to force monitoring of a SQL statement even if it is below the 5 seconds.
原文地址：https://blog.csdn.net/jnrjian/article/details/140420645
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！