Dynamic Performance Views

OS Statistics 作業系統統計

v$osstat 對檢測資料庫伺服器是否有其它應用在消耗 CPU 特別有用 .

This information is especially useful to find out whether there are other applications consuming CPU on the database server. For that purpose, you need to compare the CPU utilization reported by time model statistics with the BUSY_TIME statistic . If they ’re close to each other, you know that most of the CPU is consumed by the database instance you’re connected to.

SQL> select * from v$osstat where stat_name='BUSY_TIME';

STAT_NAME VALUE OSSTAT_ID COMMENTS CUM CON_ID

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

BUSY_TIME 85843975 2 Time (centi-secs) that CPUs have been in the busy state YES

一些統計資訊是執行以來累計的 , 但象 CPU, 記憶體等固定的 .

SQL> SELECT stat_name, to_char(value), comments FROM v$osstat WHERE stat_name LIKE '%MEMORY_BYTES';

STAT_NAME TO_CHAR(VALUE) COMMENTS

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

PHYSICAL_MEMORY_BYTES 134719299584 Physical memory size in bytes

FREE_MEMORY_BYTES 23215419392 Physical free memory in bytes

INACTIVE_MEMORY_BYTES 8830464000 Physical inactive memory in bytes

SQL> SELECT stat_name, to_char(value), comments FROM v$osstat WHERE stat_name LIKE '%CPU%';

STAT_NAME TO_CHAR(VALUE) COMMENTS

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

NUM_CPUS 16 Number of active CPUs

RSRC_MGR_CPU_WAIT_TIME 150 Time (centi-secs) processes spent in the runnable state waiting

NUM_CPU_CORES 16 Number of CPU cores

NUM_CPU_SOCKETS 1 Number of physical CPU socketsType of cloud database instance

Time Model Statistics 時間模型（時間上的統計）

V$SESS_TIME_MODEL 統計在各種處理上花了多少時間。

V$SESS_TIME_MODEL displays the session-accumulated time for various operations.

The purpose of time model statistics is to show the amount of time spent performing key operations like opening new sessions, parsing SQL statements, and processing calls with one of the engines (SQL, PL/SQL, Java and OLAP) provided by Oracle Database.

下面的例子統計 session 42 花在各項操作上的時間。

WITH db_time AS (SELECT sid, value FROM v$sess_time_model WHERE sid = 42 AND stat_name = 'DB time') SELECT ses.stat_name AS statistic, round(ses.value / 1E6, 3) AS seconds, round(ses.value / nullif(tot.value, 0) * 1E2, 1) AS "%" FROM v$sess_time_model ses, db_time tot

WHERE ses.sid = tot.sid AND ses.stat_name <> 'DB time' AND ses.value > 0 ORDER BY ses.value DESC;

STATISTIC SECONDS %

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

sql execute elapsed time 99.437 97.3

DB CPU 4.46 4.4

parse time elapsed 0.308 0.3

connection management call elapsed time 0.004 0.0

PL/SQL execution elapsed time 0.000 0.0

repeated bind elapsed time 0.000 0.0

各項操作關係樹，父樹時間包含了子樹時間

1) background elapsed time

2) background cpu time

1) DB time

2) DB CPU

2) connection management call elapsed time

2) sequence load elapsed time

2) sql execute elapsed time

2) parse time elapsed

3) hard parse elapsed time

4) hard parse (sharing criteria) elapsed time

5) hard parse (bind mismatch) elapsed time

3) failed parse elapsed time

4) failed parse (out of shared memory) elapsed time

2) PL/SQL execution elapsed time

2) inbound PL/SQL rpc elapsed time

2) PL/SQL compilation elapsed time

2) Java execution elapsed time

Wait Classes and Wait Events 等待事件（時間上的統計）

V$SESSION_WAIT_CLASS V$SESSION_EVENT

基於時間模型，我們能統計各項處理上花了多少時間，且消耗了多少 CPU 時間。如果兩個值相等，表示資料庫沒有經歷任何等待事件（像磁碟 I/O, 網路或鎖） , 如果兩個值相差很大，就需要知道伺服器程式消耗在什麼等待事件上（如以上例子，只有 4.4% 在 CPU 時間上）。

有 1800 多個等待事件，可以把他們歸為 13 類（如下查詢）。

SQL> SELECT wait_class, count(*) FROM v$event_name GROUP BY rollup(wait_class) ORDER BY wait_class;

。。。。

V$SESSION_WAIT_CLASS

透過等待類檢視 v$session_wait_class ，我們能統計出消耗在各大類等待事件上的時間。

SELECT wait_class, round(time_waited, 3) AS time_waited,

round(1E2 * ratio_to_report(time_waited) OVER (), 1) AS "%"

FROM (

SELECT sid, wait_class, time_waited / 1E2 AS time_waited

FROM v$session_wait_class

WHERE total_waits > 0

UNION ALL

SELECT sid, 'CPU', value / 1E6

FROM v$sess_time_model

WHERE stat_name = 'DB CPU'

) WHERE sid = 42 ORDER BY 2 DESC;

WAIT_CLASS TIME_WAITED %

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

Idle 154.77 60.2

User I/O 96.99 37.7

CPU 4.46 1.7

（在時間模型裡 CPU 值是 4.4 ，這裡只有 1.7% 是因為 Idle 等待不包含在時間模型統計裡）

Commit 0.85 0.3

Network 0.04 0.0

Configuration 0.03 0.0

Concurrency 0.02 0.0

Application 0.01 0.0

V$SESSION_EVENT

v$session_wait_class 只是統計大類等待事件的統計，如需要知道時間具體消耗在什麼等待事件上，需要查詢 V$SESSION_EVENT 檢視。

SELECT event, round(time_waited, 3) AS time_waited, round(1E2 * ratio_to_report(time_waited) OVER (), 1) AS "%" FROM ( SELECT sid, event, time_waited_micro / 1E6 AS time_waited FROM v$session_event WHERE total_waits > 0 UNION ALL SELECT sid, 'CPU', value / 1E6 FROM v$sess_time_model WHERE stat_name = 'DB CPU' ) WHERE sid = 42 ORDER BY 2 DESC;

EVENT TIME_WAITED %

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

SQL*Net message from client 154.790 60.2

db file sequential read 96.125 37.4

CPU 4.461 1.7

log file sync 0.850 0.3

read by other session 0.734 0.3

db file parallel read 0.135 0.1

SQL*Net message to client 0.044 0.0

cursor: pin S 0.022 0.0

enq: TX - row lock contention 0.011 0.0

Disk file operations I/O 0.001 0.0

latch: In memory undo latch 0.001 0.0

透過 v$session_event ，我們知道 37.4% User I/O 是花在 db file sequential read 等待事件上。 DB Time 只有 39.8%(100-60.2) ， 60.2% 是在 idle wait event (SQL*Net message from client). 這表示 60.2% 的時間資料庫等待應用程式釋出任務；其它時間主要花在磁碟 I/O 操作上 disk I/O operations that read a single block ( db file sequential read ) ；其它等待事件和 CPU 利用率上可以忽略不計。

計算平均等待時間：

SELECT time_waited_micro/total_waits/1E3 AS avg_wait_ms FROM v$system_event

WHERE event = 'db file sequential read';

AVG_WAIT_MS

-----------

9.52927176

等待時間分佈柱狀圖

SQL> SELECT wait_time_milli, wait_count, 100*ratio_to_report(wait_count) OVER () AS "%"

FROM v$event_histogram

WHERE event = 'db file sequential read';

WAIT_TIME_MILLI WAIT_COUNT %

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

1 348528 3.27

2 293508 2.75

4 1958584 18.37

8 4871214 45.70

16 2106649 19.76

32 635484 5.96

64 284040 2.66

128 143030 1.34

256 18041 0.17

512 588 0.01

1024 105 0.00

2048 1 0.00

******************************************************************************************

v$session,v$session_wait,v$session_wait_history,v$active_session_history

******************************************************************************************

1 、 v$session: 連線將產生會話，當前會話的資訊儲存在 v$session 中，連線斷開後消失；

2 、 v$session_wait: 記錄當前正在等的事件或最後一次的等待事件；

3 、 v$session_wait_history ：儲存每個活動 session 在 v$session_wait 中最近 10 次的等待事件 ;

4 、 ASH(v$active_session_history): 在記憶體中儲存，每秒從 v$session_wait 中取樣一次 ( 等待會話每秒的快照 )

(It displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn't belong to the Idle wait class.)

5 、 AWR ： ASH 資料每小時將資料取樣寫入存到磁碟中 AWR(dba_hist..) ，預設保留 7 天，每小時一次

System and Session Statistics

V$SYSSTAT V$SESSTAT

除了時間模型和等待事件 , Oracle 資料庫也統計其它一些操作處理的次數或者處理的資料量 .

下面統計從資料庫啟動來 , 提交次數 , logon 次數 , in-memory sort 量

SQL> SELECT name, value FROM v$sysstat name IN ('logons cumulative', 'user commits', 'sorts (memory)');

NAME VALUE

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

logons cumulative 1422

user commits 1298103

sorts (memory) 770169

下面的查詢統計I/O操作處理的資料量

SQL> SELECT name, value

2 FROM v$sysstat

3 WHERE name LIKE 'physical % total bytes';

NAME VALUE

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

physical read total bytes 9.1924E+10

physical write total bytes 4.2358E+10

Current Sessions Status 當前會話狀態

V$SESSION V$SESSION_WAIT

V$SESSION(10g, 11g ， 12c) 整合了 V$SESSION_WAIT 裡的欄位（如 p1 、 p2 、 p3 等）

v$session_wait 檢視中的 p1 、 p2 、 p3 表示等待事件的具體含義。如果 Wait Event 是 db file scattered read ，那麼 p1=file_id/p2=block_id/p3=blocks ，然後透過 DBA_extents 即可確定出熱點物件。如果是 latch free 的話，那麼 p2 為閂鎖號，它指向 v$latch 。

In addition to the v$session view, there are other dynamic performance views that are specialized in providing specific information. For example, v$session_wait provides only columns related to wait events, and v$session_blockers provides only columns related to blocked sessions.

Active Session History 會話歷史

v$session 只提供了當前 session 的狀態，為了分析，需要知道過去一段時間的情況， active session history (ASH) V$ACTIVE_SESSION_HISTORY 顯示資料庫中的取樣會話活動。 ASH 每秒從 v$session 中取快照，存在 V$ACTIVE_SESSION_HISTORY 中，並收集所有活動會話的等待資訊。若 ASH 資料被重新整理到磁碟，則需要從 DBA_HIS_ACTIVE_SESS_HISTORY 檢視中查詢相關資訊。 V$ACTIVE_SESSION_HISTORY 類似 SQL Trace, 但好的是 V$ACTIVE_SESSION_HISTORY 總是可用的 .

可用透過給sample_time設定條件,來查詢某段時間.

例子1:

比如下面的語句查詢'2018-12-31 14:10:30'到15:10這10分鐘DB Time最高的10條語句.

SELECT activity_pct,db_time,sql_id FROM

(SELECT round(100 * ratio_to_report(count(*)) OVER (), 1) AS activity_pct,

count(*) AS db_time, sql_id FROM v$active_session_history

WHERE sample_time BETWEEN to_timestamp('2018-12-31 14:10:30', 'YYYY-MM-DD HH24:MI:SS')

AND to_timestamp('2018-12-31 15:10:30', 'YYYY-MM-DD HH24:MI:SS')

AND sql_id IS NOT NULL

GROUP BY sql_id ORDER BY count(*) DESC

) WHERE rownum <= 10;

ACTIVITY_PCT DB_TIME SQL_ID

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

47.7 41 g6px76dmjv1jy

8.1 7 6hnhqahphpk8n

4.7 4 19qzxmgkk0mv9

3.5 3 16pgyra4xxuh9

3.5 3 196mqnmxgxpv1

3.5 3 8tvh9uyc6gmup

2.3 2 771ba8nfchrad

2.3 2 c85zupw0dgrm4

2.3 2 ghxaptwduxu9u

2.3 2 c3rvcbu8r3zx8

例子2:

過去10分鐘DB Time最高的10條語句

SELECT activity_pct,db_time,sql_id FROM

(SELECT round(100 * ratio_to_report(count(*)) OVER (), 1) AS activity_pct,

count(*) AS db_time, sql_id FROM v$active_session_history

WHERE sample_time > sysdate-10/(24 * 60)

AND sql_id IS NOT NULL

GROUP BY sql_id ORDER BY count(*) DESC

) WHERE rownum <= 10;

其它的一些例子:

用法舉例：查詢最近一分鐘內，最消耗 CPU 的 sql 語句

SELECT sql_id, count (*), round( count (*) / sum( count (*)) over(), 2 ) pctload
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate – 1 / ( 24 * 60 )
AND session_type <> 'BACKGROUND’
AND session_state = 'ON CPU’
GROUP BY sql_id
ORDER BY count (*) desc ;

用法舉例：查詢最近一分鐘內，最消耗 I/O 的 sql 語句

SELECT ash.sql_id, count (*)
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVT
WHERE ash.sample_time > sysdate -1/(24*60)
AND ash.session_state = 'WAITING’
AND ash.event_id = evt.event_id
AND evt.wait_class = 'USER I/O’
GROUP BY ash.sql_id
ORDER BY count (*) desc ;

用法舉例：查詢最近一分鐘內，最消耗 CPU 的 session

SELECT session_id, count (*)
FROM V$ACTIVE_SESSION_HISTORY
WHERE session_state = 'ON CPU’
AND sample_time > sysdate -1/(24*60)
GROUP BY session_id
ORDER BY count (*) desc ;

用法舉例：查詢最近一分鐘內，最消耗資源的 sql 語句

SELECT ash.sql_id,
sum (decode(ash.session_state, 'ON CPU’ ,1,0)) “CPU” ,
sum (decode(ash.session_state, 'WAITING’ ,1,0)) -
sum (decode(ash.session_state, 'WAITING’ ,decode(en.wait_class, 'USER I/O’ ,1,0),0)) “WAIT” ,
sum (decode(ash.session_state, 'WAITING’ ,decode(en.wait_class, 'USER I/O’ ,1,0),0)) “IO” ,
sum (decode(ash.session_state, 'ON CPU’ ,1,1)) “TOTAL”
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN
WHERE SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)
GROUP BY ash.sql_id
ORDER BY sum (decode(ash.session_state, 'ON CPU’ ,1,1)) desc ;

用法舉例：查詢最近一分鐘內，最消耗資源的 session

SELECT ash.session_id,ash.session_serial#,ash.user_id,ash.program,
sum (decode(ash.session_state, 'ON CPU’ ,1,0)) “CPU” ,
sum (decode(ash.session_state, 'WAITING’ ,1,0)) -
sum (decode(ash.session_state, 'WAITING’ ,decode(en.wait_class, 'USER I/O’ ,1,0),0)) “WAITING” ,
sum (decode(ash.session_state, 'WAITING’ ,decode(en.wait_class, 'USER I/O’ ,1,0),0)) “IO” ,
sum (decode(ash.session_state, 'ON CPU’ ,1,1)) “TOTAL”
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN
WHERE en.event# = ash.event# and ash.sample_time > sysdate -1/(24*60)
GROUP BY ash.session_id,ash.user_id,ash.session_serial#,ash.program
ORDER BY sum (decode(ash.session_state, 'ON CPU’ ,1,1))

ASH Report

也可以用ash report建立ASH報告

$ORACLE_HOME/rdbms/admin/ashrpt.sql or ashrpti.sql

SQL> @?/rdbms/admin/ashrpt.sql

Enter 'html' for an HTML report, or 'text' for plain text

Enter value for report_type: text

Enter value for begin_time: 02/12/14 22:12:30

Enter duration in minutes starting from begin time:

Enter value for duration: 5

The default report file name is ashrpt_1_0212_2217.txt. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

下圖是報告的例子:

SQL Statement Statistics

Information about cursors associated to SQL statements is available at the parent and child level through the v$sqlarea and v$sql views , 也可以用 v$sqlstats , 且v$sqlstats儲存時間長些, 遊標可能已經溢位了library cache, 但在v$sqlstats還可以查詢到.下面是v$sql裡的主要資訊.

The identification of the cursor ( address , hash_value , sql_id and child_number ).

The type of the SQL statement associated to the cursor ( command_type ) and the text of the SQL

statement (the first 1,000 characters in sql_text and the full text in sql_fulltext ).

The service used to open the session that hard parsed the cursor ( service ), the schema used

for the hard parse ( parsing_schema_name and parsing_schema_id ), and the session attributes

that were in place during the hard parse ( module and action ).

If the SQL statement was executed from PL/SQL, the ID of the PL/SQL program and the line

number where the SQL statement is located ( program_id and program_line# ).

The number of hard parses that took place ( loads ), how many times the cursor was

invalidated ( invalidations ), when the first and last hard parses took place ( first_load_time

and last_load_time ), the name of the stored outline category ( outline_category ), SQL

profile ( sql_profile ), SQL patch ( sql_patch ), SQL plan baseline ( sql_plan_baseline )

used during the generation of the execution plan, and the hash value of the execution plan

associated to the cursor ( plan_hash_value ).

The number of parse, execution, and fetch calls ( parse_calls , executions , and fetches ) that

have been carried out and how many rows were processed ( rows_processed ). For queries,

how many times all rows were fetched ( end_of_fetch_count ).

The amount of DB time used for the processing ( elapsed_time ), how much of it has been

spent on CPU ( cpu_time ) or waiting for events belonging to the Application, Concurrency,

Cluster and User I/O wait classes ( application_wait_time , concurrency_wait_time ,

cluster_wait_time , and user_io_wait_time ), and how much processing has been done

by the PL/SQL engine and Java virtual machine ( plsql_exec_time and java_exec_time ).

All values are expressed in microseconds.

The number of logical reads, physical reads, direct writes, and sorts that have been carried out ( buffer_gets , disk_reads , direct_writes , and sorts ).

Real-time Monitoring 實時效能監控

the Tuning Pack option must be licensed. In addition, real-time monitoring is only available from 11.1 onward. If the control_management_pack_access initialization isn ’t set to diagnostic+tuning , real-time monitoring is disabled.

實時監控和ASH目的相似.

ASH 為active sessions提供歷史分析,統計資訊.

real-time monitoring 為遊標(SQL語句)提供歷史分析,統計資訊.

1. For executions that consume at least 5 seconds of combined CPU and disk I/O time

2. For executions that use parallel processing

3. For SQL statements that explicitly enable real-time monitoring by specifying the monitor hint (it ’s also possible to explicitly disable it with the no_monitor hint)

透過 v$sql_monitor 檢視可以檢視哪些操作被監控了或正在被監控 , 或者用 dbms_sqltune package, report_sql_monitor_list function.

SELECT dbms_sqltune.report_sql_monitor(sql_id => '5kwfj03dc3dp1', type => 'active') FROM dual

(重要)關於效能的幾個主要動態檢視

相關文章