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时被启用。
11g中通过以上v$SQL_MONITOR和V$SQL_PLAN_MONITOR视图，我们可以很方便地实时找出系统中可能引起性能问题的SQL语句。此外SQL监视也集成到了DBMS_AUTOTUNE包中，DBMS_SQLTUNE.REPORT_SQL_MONITOR()过程可以帮助我们高效地找出实时系统中的性能问题SQL:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
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
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
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!
|
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277563
918
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
