显示sql的真实执行情况

V$SQL_MONITOR


one and only one entry for each SQL, it is added:
when a SQL statement runs parallel
when a SQL has consumed at least 5 seconds of CPU or I/O time, specified in hidden parameter _sqlmon_threshold, default 5
When a hint 'monitor' is added in a SQL, eg select /*+ monitor */ sysdate from dual;

statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second.
It is kept in V$SQL_MONITOR for at least one minute. pecified in hidden parameter _sqlmon_recycle_time, default 60


Select Sql_Id,Sql_Exec_Start,
Round(Cpu_Time/1000000,1) As Cpu_Time,
Round(User_Io_Wait_Time/1000000,1) As Io_Wtime,
FETCHES,BUFFER_GETS,DISK_READS, DIRECT_WRITES
from V$SQL_MONITOR;




col name for a25
col value for a8
col describ for a80
set linesize 120
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%sqlmon%'
order by x.ksppinm;

NAME VALUE DESCRIB
------------------------- -------- --------------------------------------------------------------------------------
_sqlmon_binds_xml_format default format of column binds_xml in [G]V$SQL_MONITOR
_sqlmon_max_plan 80 Maximum number of plans entry that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines 300 Number of plan lines beyond which a plan cannot be monitored
_sqlmon_recycle_time 60 Minimum time (in s) to wait before a plan entry can be recycled
_sqlmon_threshold 5 CPU/IO time threshold before a statement is monitored. 0 is disabled


到底是怎么存数据的？？
alter system set "_sqlmon_max_plan"=5 scope=both;
alter system set "_sqlmon_recycle_time"=3600 scope=both;

SQL> SELECT /*+ PARALLEL(a,4) */ COUNT(*) FROM test.test as a;
SQL> select /*+ monitor */ sysdate from dual;

SYSDATE
--------
15:44:32

KEY SQL_ID SQL_EXEC CPU_TIME IO_WTIME FETCHES BUFFER_GETS DISK_READS DIRECT_WRITES
---------- ------------- -------- ---------- ---------- ---------- ----------- ---------- -------------
2.5770E+10 9f7ttbh69hxxu 15:39:14 0 0 1 0 0 0
3.4360E+10 9f7ttbh69hxxu 15:39:15 0 0 1 0 0 0
4294967299 9f7ttbh69hxxu 15:39:16 0 0 1 0 0 0
1.7180E+10 9f7ttbh69hxxu 15:39:17 0 0 1 0 0 0
8589934597 9f7ttbh69hxxu 15:39:19 0 0 1 0 0 0

实验结论：
1 同一个SQL,执行多次会有多条记录
2. 同一SQL的同一次执行只有一条记录，但只要没结束，每秒刷新一次统计信息
3 参数_sqlmon_max_plan规定可以存多少条记录（我测试的结果，要重新启动数据库才真正生效）
4 参数_sqlmon_recycle_time指定每条记录至少存活多少时间，如果3的限制已经满了，但无淘汰的记录，就不会加入新的记录，丢失了
5 有3,4得知，缺省值都太小，实际运用要适当调大
6 并行查询，每个process都有一个记录



显示最后一个sql的真实执行情况

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

spool sqlmon_active.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off


查看某sql的执行情况


SET LINESIZE 200
COLUMN sql_text FORMAT A80

SELECT sql_id, status, sql_text
FROM v$sql_monitor
WHERE username =user;


SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL c:\report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id =>'16m4z8yh20w4u',
type =>'HTML',
report_level =>'ALL') AS report
FROM dual;
SPOOL OFF





常用参数说明：

type:
Report format, 'TEXT' by default. Can be 'TEXT', 'HTML' or 'XML'.

report_level
Level of detail for the report, either 'BASIC', 'TYPICAL' or 'ALL'. Individual report sections can also be enabled/disabled by using a +/-. Several sections are defined: 'PLAN', 'PARALLEL', 'SESSIONS', 'INSTANCE', and 'SQL_TEXT'. For example, use 'BASIC +PARALLEL' to show the basic report with

an additional section reporting parallel information. Or use 'ALL -PLAN -INSTANCE' for a complete report excluding plan detail and instance information.

sql_id

SQL_ID for which monitoring information should be displayed. Use NULL (the default) to report on the last statement monitored by Oracle.