在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

. SORT AGGREGATE

.

1

.

1

.

2

.. PX COORDINATOR

.

9

8

.

2.8%

.

3

... PX SEND QC (RANDOM)

:TQ10000

1

.

8

.

4

.... SORT AGGREGATE

.

1

.

8

.

14%

.

5

..... PX BLOCK ITERATOR

.

2M

338

.

8

2M

.

6

...... TABLE ACCESS FULL

T

2M

338

.

115

2M

.

1192 (99%)

.

85%

.

97%

sql monitor的使用(一)

相關文章