(重要)关于性能的几个主要动态视图

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

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

转载于:http://blog.itpub.net/14377/viewspace-2287079/