Oracle性能诊断视图总结

最新推荐文章于 2021-04-16 02:25:45 发布

congjiaqing0635

最新推荐文章于 2021-04-16 02:25:45 发布

阅读量177

点赞数

文章标签：数据库操作系统

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.