mysql动态性能视图_11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR

11g中引入了新的动态性能视图V$SQL_MONITOR，该视图用以显示Oracle监视的SQL语句信息。SQL监视会对那些并行执行或者消耗5秒以上cpu时间或I/O时间的SQL语句自动启动，同时在V$SQL_MONITOR视图中产生一条记录。当SQL语句正在执行，V$SQL_MONITOR视图中的统计信息将被实时刷新，频率为每秒1次。SQL语句执行完成后，监视信息将不会被立即删除，Oracle会保证相关记录保存一分钟(由参数_sqlmon_recycle_time所控制，默认为60s)，最终这些记录都会被删除并被重用。这一新的SQL性能监视特性仅在CONTROL_MANAGEMENT_PACK_ACCESS为DIAGNOSTIC+TUNING和STATISTICS_LEVEL为ALL|TYPICAL时被启用。

SQL> conn maclean/maclean;

Connected.

SQL> show parameter control_management_pack_access

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_management_pack_access string DIAGNOSTIC+TUNING

SQL> show parameter STATISTICS_LEVEL

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string TYPICAL

SQL> select * from v$sql_monitor where username='MACLEAN';

no rows selected

/*以下语句将消耗大量资源 */

select count(*) from sys.obj$,sys.tab$,sys.col$;

........................

SQL> select key,status,sql_id,cpu_time from v$sql_monitor where username='MACLEAN';

KEY STATUS SQL_ID CPU_TIME

---------- ------------------- ------------- ----------

9191230013 EXECUTING 1tc94vh92f68b 52915539

SQL> select key,status,sql_id,cpu_time from v$sql_monitor where username='MACLEAN';

KEY STATUS SQL_ID CPU_TIME

---------- ------------------- ------------- ----------

9191230013 EXECUTING 1tc94vh92f68b 72899267

SQL> select plan_line_id, plan_operation, plan_options starts, output_rows

2 from v$sql_plan_monitor

3 where key = 919123001346;

PLAN_LINE_ID PLAN_OPERATION STARTS OUTPUT_ROWS

------------ ------------------------------ ------------------------------ -----------

0 SELECT STATEMENT 0

1 SORT AGGREGATE 0

2 MERGE JOIN CARTESIAN 4277724845

3 MERGE JOIN CARTESIAN 70256

4 TABLE ACCESS FULL 1

5 BUFFER SORT 70256

6 INDEX FAST FULL SCAN 73378

7 BUFFER SORT 4277724845

8 INDEX FAST FULL SCAN 90611

9 rows selected

/* cancel掉之前的查询语句 */

/* 针对那些我们希望特别监视的SQL语句，可以直接使用monitor提示，强制监视 */

SQL> select /*+ monitor */ * from dual where 1=2;

no rows selected

SQL> select key, status, sql_id, cpu_time

2 from v$sql_monitor

3 where username = 'MACLEAN'

4 and sql_text like '%monitor%';

KEY STATUS SQL_ID CPU_TIME

---------- ------------------- ------------- ----------

7.2155E+11 DONE (ALL ROWS) 2fr8stwgt15mw 0

/* 可以看到这里原语句的CPU_TIME不到1ms*/

/* 以下为SQL MONITOR的相关的几个隐藏参数 */

SQL> col describ for a80;

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

2 FROM SYS.x$ksppi x, SYS.x$ksppcv y

3 WHERE x.inst_id = USERENV ('Instance')

4 AND y.inst_id = USERENV ('Instance')

5 AND x.indx = y.indx

6 AND x.ksppinm LIKE '%sqlmon%'

7 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

11g中通过以上v$SQL_MONITOR和V$SQL_PLAN_MONITOR视图，我们可以很方便地实时找出系统中可能引起性能问题的SQL语句。此外SQL监视也集成到了DBMS_AUTOTUNE包中，DBMS_SQLTUNE.REPORT_SQL_MONITOR()过程可以帮助我们高效地找出实时系统中的性能问题SQL:

SQL> set long 99999;

SQL> set linesiz 300 pagesize 2000;

SQL> select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR

--------------------------------------------------------------------------------

SQL Monitoring Report

SQL Text

------------------------------

select count(*) from sys.obj$,sys.tab$,sys.col$

Global Information

------------------------------

Status : EXECUTING

Instance ID : 1

Session : MACLEAN (6:255)

SQL ID : cz2bwj0f6ayr0

SQL Execution ID : 16777216

Execution Started : 09/16/2010 14:19:43

First Refresh Time : 09/16/2010 14:19:51

Last Refresh Time : 09/16/2010 14:21:57

Duration : 135s

Module/Action : SQL*Plus/-

Service : SYS$USERS

Program : sqlplus.exe

Global Stats

=========================================

| Elapsed | Cpu | Other | Buffer |

| Time(s) | Time(s) | Waits(s) | Gets |

=========================================

| 134 | 132 | 1.82 | 437 |

=========================================

SQL Plan Monitoring Details (Plan Hash Value=4003357142)

==============================================================================================

=============================================

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Act

ivity | Activity Detail |

| | | | (Estim) | | Active(s) | Active | | (

Actual) | | (%) | (# samples) |

==================================================================================================================

=========================

| 0 | SELECT STATEMENT | | | | | | 1 |

| | | |

| -> 1 | SORT AGGREGATE | | 1 | | 127 | +8 | 1 | 0 | |

2.24 | Cpu (3) |

| -> 2 | MERGE JOIN CARTESIAN | | 808G | 477M | 127 | +8 | 1 |

2G | | | |

| -> 3 | MERGE JOIN CARTESIAN | | 14M | 9809 | 127 | +8 | 1 | 27462 | |

| |

| -> 4 | TABLE ACCESS FULL | TAB$ | 1107 | 201 | 127 | +8 | 1 |

1 | | | |

| -> 5 | BUFFER SORT | | 12815 | 9607 | 127 | +8 | 1 | 27462 | 886K |

| |

| 6 | INDEX FAST FULL SCAN | I_OBJ1 | 12815 | 9 | 1 | +8 | 1 |

73378 | | | |

| -> 7 | BUFFER SORT | | 56957 | 477M | 134 | +1 | 27462 | 2G | 1M |

97.76 | Cpu (131) |

| 8 | INDEX FAST FULL SCAN | I_COL3 | 56957 | 34 | 1 | +8 | 1 |

90611 | | | |

==================================================================================================================

That's cool!