Oracle效能診斷檢視總結

Oracle的事件分為等待時間和診斷事件兩類，熟練這兩類事件在無工具診斷會有很大的幫助。

一、Oracle等待事件的分類

透過如下查詢可以看，如下的wait_class是由

SQL> select wait_class#, wait_class_id, wait_class

2 from v$event_name

3 group by wait_class#, wait_class_id, wait_class;

WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS

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

10 2396326234 Scheduler

4 3875070507 Concurrency

8 1740759767 User I/O

0 1893977003 Other

6 2723168908 Idle

1 4217450380 Application

2 3290255840 Configuration

11 3871361733 Cluster

3 4166625743 Administrative

9 4108307767 System I/O

7 2000153315 Network

5 3386400367 Commit

所有等待事件名可以透過如下查詢得到：

select event#, name, parameter1, parameter2, parameter3

from v$event_name

order by name;

[@more@]

常用到診斷及效能相關檢視及關聯關係如下：

v$process

作業系統的程式

v$session --&gtv$session_wait

v$system_event

v$sesion_envent

v$sql

v$sql_text

v$lock

v$locked_object

v$latch_children

v$bh

SQL> desc v$process;

Name Type Nullable Default Comments

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

ADDR RAW(8) Y

PID NUMBER Y

SPID VARCHAR2(12) Y

USERNAME VARCHAR2(15) Y

SERIAL# NUMBER Y

TERMINAL VARCHAR2(30) Y

PROGRAM VARCHAR2(48) Y

TRACEID VARCHAR2(255) Y

BACKGROUND VARCHAR2(1) Y

LATCHWAIT VARCHAR2(16) Y

LATCHSPIN VARCHAR2(16) Y

PGA_USED_MEM NUMBER Y

PGA_ALLOC_MEM NUMBER Y

PGA_FREEABLE_MEM NUMBER Y

PGA_MAX_MEM NUMBER Y

Oracle10g以來，v$session已經包含足夠多的資訊，如下標黑部分所示，正確理解這些欄位及其關聯的相關檢視和表對應急診斷及效能最佳化很有幫助：

SQL> desc v$session;

Name Type Nullable Default Comments

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

SADDR RAW(8) Y

SID NUMBER Y

SERIAL# NUMBER Y

AUDSID NUMBER Y

PADDR RAW(8) Y

USER# NUMBER Y

USERNAME VARCHAR2(30) Y

...

STATUS VARCHAR2(8) Y

SCHEMANAME VARCHAR2(30) Y

OSUSER VARCHAR2(30) Y

PROCESS VARCHAR2(12) Y

MACHINE VARCHAR2(64) Y

PORT NUMBER Y

TERMINAL VARCHAR2(30) Y

PROGRAM VARCHAR2(48) Y

TYPE VARCHAR2(10) Y

SQL_ADDRESS RAW(8) Y

SQL_HASH_VALUE NUMBER Y

SQL_ID VARCHAR2(13) Y

SQL_CHILD_NUMBER NUMBER Y

PREV_SQL_ADDR RAW(8) Y

PREV_HASH_VALUE NUMBER Y

...

CURRENT_QUEUE_DURATION NUMBER Y

CLIENT_IDENTIFIER VARCHAR2(64) Y

BLOCKING_SESSION_STATUS VARCHAR2(11) Y

BLOCKING_INSTANCE NUMBER Y

BLOCKING_SESSION NUMBER Y

SEQ# NUMBER Y

EVENT# NUMBER Y

EVENT VARCHAR2(64) Y

P1TEXT VARCHAR2(64) Y

P1 NUMBER Y

P1RAW RAW(8) Y

P2TEXT VARCHAR2(64) Y

P2 NUMBER Y

P2RAW RAW(8) Y

P3TEXT VARCHAR2(64) Y

P3 NUMBER Y

P3RAW RAW(8) Y

WAIT_CLASS_ID NUMBER Y

WAIT_CLASS# NUMBER Y

WAIT_CLASS VARCHAR2(64) Y

WAIT_TIME NUMBER Y

SECONDS_IN_WAIT NUMBER Y

STATE VARCHAR2(19) Y

SERVICE_NAME VARCHAR2(64) Y

SQL_TRACE VARCHAR2(8) Y

SQL_TRACE_WAITS VARCHAR2(5) Y

SQL_TRACE_BINDS VARCHAR2(5) Y

ECID VARCHAR2(64) Y

v$process和v$session 透過v$session.paddr=v$process.addr 具體查詢語句如下：

select s.SID,s.username ,s.status ,p.PGA_ALLOC_MEM,s.OSUSER,s.MACHINE,s.PROCESS

from v$session s,v$process p

where p.addr=s.paddr

and s.username is not null

and p.spid=&ospid

在增加v$sql檢視，可以查到某個程式所對應的

select s.SID,s.username ,s.status ,q.sql_text,q.sql_id from v$session s,v$process p,v$sql q

where p.addr=s.paddr　

and s.sql_id=q.sql_id

and p.spid=&pid

如上基於曹作業系統PID的來診斷一般用於在OS上透過TOP命令檢視佔用資源較高的Oracle程式後的診斷思路。

如果瓶頸時期已經過了或想了解資料庫啟動以來的總體執行情況，可以利用如下查詢語句。

例項總體執行情況：

select a.WAIT_CLASS,a.EVENT,a.TOTAL_WAITS,a.TIME_WAITED,a.TOTAL_TIMEOUTS,a.TIME_WAITED_MICRO, c.startup_time

from v$system_event a,

v$instance c

where a.WAIT_CLASS <> 'Idle'

order by a.time_waited desc

例項回話級別的執行情況：

select SE.Sid, S.Username, SE.Event,

SE.Total_Waits, SE.Time_Waited, SE.Average_Wait

from V$SESSION S, V$SESSION_EVENT SE

where S.Username is not null

and SE.Sid = S.Sid

and SE.WAIT_CLASS <> 'Idle'

--and S.Status = 'ACTIVE'

and SE.Event not like '%SQL*Net%'

order by time_waited

近期曾經長生較長等待的事件：

select SW.Sid, S.Username, SW.Event, SW.Wait_Time,

SW.State, SW.Seconds_In_Wait SEC_IN_WAIT

from V$SESSION S, V$SESSION_WAIT SW

where S.Username is not null

and SW.Sid = S.Sid

and SW.Event not like '%SQL*Net%'

order by SW.Wait_Time Desc;

檢視正在產生等待的session:

select event, count(*) sessions from v$session

where state='WAITING'

and event not like '%SQL*Net%'

and USERNAME is not null

group by event

order by 2 desc;

select s.SID,s.USERNAME,s.EVENT,s.WAIT_TIME,s.SECONDS_IN_WAIT,s.LAST_CALL_ET,s.STATE,

s.BLOCKING_SESSION,s.BLOCKING_SESSION_STATUS

from v$session s

where s.USERNAME is not null

and s.STATUS = 'ACTIVE'

and s.EVENT not like '%SQL*Net%'

order by s.SECONDS_IN_WAIT;

定位具體session後，可以根據SID的定位瓶頸資源，在Oracle10g中v$session已經包含了v$session_waite的資訊：

select Sid, Event, P1text, P1, P2text, P2, P3text, P3

from V$SESSION_WAIT

where Sid between &sid1 and &sid2

and Event not like '%SQL%'

and Event not like '%rdbms%';

透過如下查詢，可以定位具體的物件名稱，型別及表空間：

select Owner, Segment_Name, Segment_Type, Tablespace_Name

from DBA_EXTENTS

where File_Id = &FileId_In

and &BlockId_In between Block_Id and Block_Id + Blocks - 1;

如下為根據PID定位具體SQL的完整查詢：

SELECT /*+ ORDERED */

sql_text

FROM v$sqltext a

WHERE (a.hash_value, a.address) IN (

SELECT DECODE (sql_hash_value,

0, prev_hash_value,

sql_hash_value

),

DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)

FROM v$session b

WHERE b.paddr = (SELECT addr

FROM v$process c

WHERE c.spid = TO_NUMBER ('&pid', 'xxxx')))

ORDER BY piece ASC

針對enqueue（lock）類事件引起的文件，可以將v$session結合v$locked_object和v$lock檢視，如下為簡單的查詢語句：

select lo.oracle_username,

lo.object_id,

s.sid,

s.serial#,

s.seconds_in_wait

from v$locked_object lo, v$session s

where lo.session_id = s.sid

order by seconds_in_wait

也可以透過以上的OBJECTID和USER_OBJECTS（ALL_OBJECTS、DBA_OBJECTS）的OBJECTS_ID關聯，得到鎖的是哪個物件；

select b.owner, b.object_name, a.session_id, a.locked_mode

from v$locked_object a, dba_objects b

where b.object_id = a.object_id;

針對Lach類的等待事件，透過如下查詢可以得到較嚴重Lach事件：

select name, gets, misses, immediate_gets, immediate_misses, sleeps

from v$latch

order by sleeps;

SELECT name, sleeps * sleeps / (misses-spin_gets) impact

FROM v$latch

WHERE sleeps > 0

order by impact;

或如果如上v$session 或v$session_waite上查詢到，當前最嚴重的等待為lach類的事件，則按event名稱可以進一步進行分析。v$latch_children的addr欄位和x$bh的hladdr進行相互關聯可以定位熱點塊：

select addr,name, gets, misses, immediate_gets, immediate_misses, sleeps

from v$latch_children

where name = 'cache buffers chains' ----假定等待為cache buffers chains

根據以上得到addr，確定具體的熱點塊：

SELECT hladdr, tch, ts#, dbarfil, dbablk, class, DECODE (state, 2, 'shared current',

3, 'cr version', 1, 'exclusive current', 0)

FROM sys.x$bh

WHERE hladdr IN ('addr') AND tch > 100 ----addr處替代以上查詢結果對應的addr值

ORDER BY tch, hladdr, dbablk;

SELECT segment_name, segment_type, owner,

tablespace_name

FROM sys.dba_extents

WHERE file_id = 7

AND &blockid between block_id and

(block_id + (blocks-1));

二、Oracle診斷事件的分類

診斷事件主要分如下4類：

Immediate Dump Events

alter session set events 'immediate trace name systemstate level 8';

alter session set events 'immediate trace name controlf level 10';

On Error Dump Events

Change Behavior Events

Process Trace Events

SQL> alter session set events '10046 trace name context forever, level 1';

Event levels for event code 10046 are

• 1 Enable SQL statement tracing (the default if no level is specified)

• 4 As level 1 plus bind variable information

• 8 As level 1 plus wait event statistics

• 12 As level 1 plus bind variables plus wait statistics (highest level)

How to Use Trace Event 10046

How to Trace Your Own Session

alter session set timed_statistics = true;

alter session set max_dump_file_size = unlimited;

-- To enable the trace event 10046 in Oracle 7.3 onwards

alter session set events ‘10046 trace name context forever, level 8’;

-- Run your SQL script or program to trace wait event information

-- To turn off the tracing:

alter session set events ‘10046 trace name context off’;

How to Trace Someone Else’s Session

• Use the oradebug facility. You need to know the session's OS process ID (SPID) or Oracle process ID (PID). You can look them up in the V$PROCESS view. Assuming you know the name of the user you want to trace:
  select s.username,
  p.spid os_process_id,
  p.pid oracle_process_id
  from v$session s, v$process p
  where s.paddr = p.addr
  and s.username = upper(‘&user_name’);
  Now use SQL*Plus to connect as sysdba and issue following commands:
  alter system set timed_statistics = true;
  oradebug setospid 12345;
  -- 12345 is the OS process id for the session
  oradebug unlimit;
  oradebug event 10046 trace name context forever, level 8;
  -- Let the session execute SQL script
  -- or program for some amount of time
  
  -- To turn off the tracing:
  oradebug event 10046 trace name context off;
  
  In Oracle Database 10g Release 1 you can use DBMS_MONITOR package procedures to enable tracing based on the SID, service name, module, or action. The action-based tracing empowers a DBA to trace a specific business function. There is a little catch to this: the procedure requires that the DBA know the module and action names.

• Use the DBMS_MONITOR package to enable tracing for session 1234 and serial# 56789 as shown below:
  exec dbms_monitor.session_trace_enable( -
  session_id => 1234, -
  serial_num => 56789, -
  waits => true, -
  binds => true);
  -- Let the session execute SQL script or
  -- program for some amount of time
  
  -- To turn off the tracing:
  exec dbms_monitor.session_trace_disable( -
  session_id => 1234, -
  serial_num => 56789);
  These procedures look exactly like the ones from DBMS_SUPPORT package. We recommend that you use DBMS_MONITOR package procedures in Oracle Database 10g Release 1.