sql monitor的使用(一)

在sql调优中，对于sql语句的实时监控显得尤为重要，如果某条sql语句的性能比较差。可能从前端的直观感觉就是执行时间比较长。
对于dba来说，可能关注的相关因素需要多一些.
1）可以通过top命令来监控sql的性能情况，查看cpu使用率较高的oracle process,然后通过查看session和process得绑定得到对应的session,然后得到对应的sql语句。
2) 如果已经过去了一段时间，而且在缓存中已经没有对应的sql语句了，可以通过awr得到一个大体的报告做分析，排查问题的大体范围，在这个基础上定位更精准的时间段，做一个ash。
3) 如果已经定位到sql_id了，想做进一步的分析，可以通过awrsqrpt来得到对应时间段的执行计划
。。。
对于执行计划的分析方式就更多了，但是oracle也提供了一些比较方便的功能集，你用或者不用，它就在那里。
sql monitor是一个实时的sql监控工具，11g里对dbms_tune做了不少的改进和提升。动态视图v$sql_monitor中有被监控的sql语句的一些明细信息。
一般对于执行时间超过5秒的sql语句，都会成为监控对象。
首先看看11g的库是否启用了这项功能

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS;
control_management_pack_access       string      DIAGNOSTIC+TUNING

然后模拟一个大查询，做个并行，表t里面有150万左右的数据，我本地的机器查询奎尼丁超过5秒。

SQL> select  /*+ parallel(8) */count(1) from t ;
   1536604

然后查看v$sql_minotor
set long 99999
set pages 0
set linesize 200
col status format a20
col username format a30
col module format a20
col program format a20
col sql_id format a20
col sql_text format a50
 select STATUS  ,  USERNAME  ,  MODULE ,   PROGRAM,    SQL_ID  ,  SQL_TEXT from v$sql_monitor 
  /
DONE (ALL ROWS)                                                                               21df1v060g0wq
DONE (ALL ROWS)                                                                               21df1v060g0wq
DONE (ALL ROWS)                                                                               21df1v060g0wq
DONE (ALL ROWS)                                                                               21df1v060g0wq
DONE (ALL ROWS)      N1                             SQL*Plus             sqlplus@rac1 (TNS V1 21df1v060g0wq         select  /*+ parallel(8) */count(1) from t
                                                                         -V3)
DONE (ALL ROWS)                                                                               21df1v060g0wq
DONE (ALL ROWS)                                                                               21df1v060g0wq
DONE (ALL ROWS)                                                                               21df1v060g0wq
DONE (ALL ROWS)                                                                               21df1v060g0wq

9 rows selected.  

下面这个功能才是重点，生成报告。
这个报告风格类似awr,ash有html,text,xml的格式类型。输入sql_id就能得到具体的报告。
col comm format a200
SELECT dbms_sqltune.report_sql_monitor(
sql_id => '21df1v060g0wq',
report_level => 'ALL',
type=>'TEXT'
) comm 
FROM dual;

展示一个文本格式的报告。
SQL Text
------------------------------
select /*+ parallel(8) */count(1) from t


Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  N1 (254:77)
 SQL ID              :  21df1v060g0wq
 SQL Execution ID    :  16777216
 Execution Started   :  07/10/2014 06:40:49
 First Refresh Time  :  07/10/2014 06:40:52
 Last Refresh Time   :  07/10/2014 06:41:06
 Duration            :  17s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@rac1 (TNS V1-V3)
 Fetch Calls         :  1


Global Stats
=========================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes |
=========================================================================================
|     114 |    1.81 |      108 |        2.67 |     1.14 |     1 |   8913 | 1193 |  68MB |
=========================================================================================


Parallel Execution Details (DOP=8 , Servers Allocated=8)
============================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  |      Wait Events      |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes |      (sample #)       |
============================================================================================================================================
| PX Coordinator | QC    |         |    3.10 |    0.04 |     0.01 |        2.67 |     0.38 |      5 |    1 |  8192 | os thread startup (3) |
| p000           | Set 1 |       1 |      14 |    0.21 |       14 |             |          |   1075 |  143 |   8MB | direct path read (13) |
| p001           | Set 1 |       2 |      14 |    0.23 |       13 |             |     0.22 |   1190 |  158 |   9MB | direct path read (12) |
| p002           | Set 1 |       3 |      14 |    0.21 |       14 |             |          |   1078 |  142 |   8MB | direct path read (12) |
| p003           | Set 1 |       4 |      14 |    0.20 |       14 |             |          |   1030 |  139 |   8MB | direct path read (14) |
| p004           | Set 1 |       5 |      14 |    0.25 |       13 |             |     0.07 |   1166 |  156 |   9MB | direct path read (13) |
| p005           | Set 1 |       6 |      14 |    0.22 |       13 |             |     0.23 |   1074 |  145 |   8MB | direct path read (13) |
| p006           | Set 1 |       7 |      14 |    0.25 |       14 |             |          |   1288 |  176 |  10MB | direct path read (14) |
| p007           | Set 1 |       8 |      14 |    0.20 |       14 |             |     0.23 |   1007 |  133 |   8MB | direct path read (13) |
============================================================================================================================================


SQL Plan Monitoring Details (Plan Hash Value=3126468333)
========================================================================================================================================================
| Id |        Operation         |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |    Activity Detail     |
|    |                          |          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |      (# samples)       |
========================================================================================================================================================
|  0 | SELECT STATEMENT         |          |         |      |         1 |    +17 |     1 |        1 |      |       |          |                        |
|  1 |   SORT AGGREGATE         |          |       1 |      |         1 |    +17 |     1 |        1 |      |       |          |                        |
|  2 |    PX COORDINATOR        |          |         |      |        17 |     +1 |     9 |        8 |      |       |     2.63 | os thread startup (3)  |
|  3 |     PX SEND QC (RANDOM)  | :TQ10000 |       1 |      |         1 |    +17 |     8 |        8 |      |       |          |                        |
|  4 |      SORT AGGREGATE      |          |       1 |      |        14 |     +4 |     8 |        8 |      |       |     0.88 | Cpu (1)                |
|  5 |       PX BLOCK ITERATOR  |          |      2M |  338 |        13 |     +5 |     8 |       2M |      |       |          |                        |
|  6 |        TABLE ACCESS FULL | T        |      2M |  338 |        14 |     +4 |   115 |       2M | 1192 |  68MB |    96.49 | Cpu (6)                |
|    |                          |          |         |      |           |        |       |          |      |       |          | direct path read (104) |
========================================================================================================================================================

文本格式的报告简单明了，但是html的报告来说更加清晰，而且还有额外的一些信息。性能瓶颈类问题，一目了然。

SQL Text

select /*+ parallel(8) */count(1) from t

Global Information:  DONE (ALL ROWS)

---

Instance ID	:	1	Buffer Gets IO Requests Database Time Wait Activity . 8913 . 1193 . . . 114s . . 100%
Session	:	N1 (254:77)
SQL ID	:	21df1v060g0wq
SQL Execution ID	:	16777216
Execution Started	:	07/10/2014 06:40:49
First Refresh Time	:	07/10/2014 06:40:52
Last Refresh Time	:	07/10/2014 06:41:06
Duration	:	17s
Module/Action	:	SQL*Plus/-
Service	:	SYS$USERS
Program	:	sqlplus@rac1 (TNS V1-V3)
Fetch Calls	:	1

Parallel Execution Details (DOP=8 , Servers Allocated=8)

PX Processes Drill-Down

Name	Type	Server#	Buffer Gets	IO Requests	Database Time	Wait Events
PX Coordinator	QC		5 (<0.1%)	1 (<0.1%)	. . . 3s (2.7%)	. 2.8%
p000	Set 1	1	. 1075 (12%)	. 143 (11%)	. . 14s (12%)	. 12%
p001	Set 1	2	. 1190 (13%)	. 158 (13%)	. . . 14s (12%)	. 11%
p002	Set 1	3	. 1078 (12%)	. 142 (11%)	. . 14s (12%)	. 11%
p003	Set 1	4	. 1030 (11%)	. 139 (11%)	. . 14s (12%)	. 13%
p004	Set 1	5	. 1166 (13%)	. 156 (13%)	. . 14s (12%)	. 12%
p005	Set 1	6	. 1074 (12%)	. 145 (12%)	. . . 14s (12%)	. 12%
p006	Set 1	7	. 1288 (14%)	. 176 (14%)	. . 14s (12%)	. 13%
p007	Set 1	8	. 1007 (11%)	. 133 (11%)	. . . 14s (12%)	. 12%

SQL Plan Monitoring Details (Plan Hash Value=3126468333)

Id		Operation	Name	Estimated  Rows	Cost	Active Period  (17s)	Execs	Rows	Memory  (Max)	Temp  (Max)	IO Requests	CPU Activity	Wait Activity
.	0	SELECT STATEMENT	.	.	.	. .	1	1	.	.		.	.
.	1	. SORT AGGREGATE	.	1	.	. .	1	1	.	.		.	.
.	2	.. PX COORDINATOR	.	.	.	. .	9	8	.	.			. 2.8%
.	3	... PX SEND QC (RANDOM)	:TQ10000	1	.	. .	8	8	.	.		.	.
.	4	.... SORT AGGREGATE	.	1	.	. .	8	8	.	.		. 14%
.	5	..... PX BLOCK ITERATOR	.	2M	338	. .	8	2M	.	.		.	.
.	6	...... TABLE ACCESS FULL	T	2M	338	. .	115	2M	.	.	. 1192 (99%)	. 85%	. 97%

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1216422/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1216422/