mysql v$sql_显示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.