效能分析大全

pentium發表於2019-01-03

效能分析

動態檢視的 3 大型別

Current State Views  Counter/Accumulator Views  Information Views

 

Table 24-1  Current State Views （當前狀態檢視）

Fixed View

Description

Locks   currently held/requested on the instance

Sessions/processes   holding a latch

Cursors   opened by sessions on the instance

Sessions   currently connected to the instance

Different   resources sessions are currently waiting for

 

Table 24-2  Summary Since Session Startup （累計檢視）

Fixed View

Description

Object   level statistics in shared pool

File   level summary of the I/O activity

Latch   activity summary

Latch   activity for child latches

Namespace   level summary for shared pool

Summary   of the current memory use of the library cache, by library cache object type

Resource   usage summary for your own session

Rollback   segment activity summary

Data   dictionary activity summary

User-friendly   DBA view for real-time monitoring of segment-level statistics

High-efficiency   view for real-time monitoring of segment-level statistics

Session-level   summary of all the waits for current sessions

Session-level   summary of resource usage since session startup

Simulation   of the shared pool's LRU list mechanism

Child   cursor details for V$SQLAREA

Shared   pool details for statements/anonymous blocks

Summary   of resource usage

Instance   wide summary of resources waited for

Histogram   of undo usage. Each row represents a 10-minute interval.

Break   down of buffer waits by block class

 

Table 24-3  Information Views

Fixed View

Description

Advisory   information collected by MTTR advisory, when FAST_START_MTTR_TARGET is set

Parameters   values for your session

Instance   wide parameter values

Server   processes (background and foreground)

Statistics   property view for segment-level statistics

Execution   plan for cursors that were recently executed

Execution   statistics of each operation in the execution plan

Concatenates   information in V$SQL_PLAN with execution   statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA

SQL   text of statements in the shared pool

Status   of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter

V$lock

Example 24-11 Finding the Sessions Holding the Lock

 

如果 TYPE 是 TM ， id1 表示 db_objects 的 object_id

  

Find the (ID1, ID2, type) for sessions waiting for a lock (LMODE=0).

Find the session holding the lock (REQUEST=0) for that ID1, ID2, type.

SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type

FROM V$LOCK

 WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)

 ORDER BY id1,request

 



SID          ID1        ID2      LMODE    REQUEST TY

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

1237       196705     200493          6          0 TX <- Lock Holder

 1256      196705     200493          0          6 TX <- Lock Waiter

 1176      196705     200493          0          6 TX <- Lock Waiter

938        589854     201352          6          0 TX <- Lock Holder

 1634      589854     201352          0          6 TX <- Lock Waiter

 

Example 24-12 Finding the Statements being Executed by These Sessions

  

SELECT sid, sql_hash_value

FROM V$SESSION

WHERE SID IN (1237,1256,1176,938,1634);

 



SID  SQL_HASH_VALUE

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

  938      2078523611 <-Holder

 1176      1646972797 <-Waiter

 1237      3735785744 <-Holder

 1256      1141994875 <-Waiter

 1634      2417993520 <-Waiter

 

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

以下語句顯示session sid 131的時間花在哪

WITH db_time AS (SELECT sid, value FROM v$sess_time_model WHERE sid = 131 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          %
---------------------------------------------------------------- ---------- ----------
DB CPU                                                                    .042      132.5
connection management call elapsed time                .016       51.2
sql execute elapsed time                                            .005       14.1
parse time elapsed                                                      .001        1.8


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  總是可用的 .

 

過去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;

 

查詢最近10分鐘內，最消耗CPU的sql語句

SELECT sql_id, count(*), round(count(*)/sum(count(*)) over(), 2) pctload

 FROM V$ACTIVE_SESSION_HISTORY

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

   AND session_type <>  'BACKGROUND'

   AND session_state = 'ON CPU'

GROUP BY sql_id

 ORDER BY count(*) desc;

ASH Report

也可以用 ash report 建立 ASH 報告檢視一段時間資料庫主要事件。

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

檢視一段時間系統的效能 , 經歷了什麼等待事件 ( 類似與上面的 ASH) 。

第一步：

create table sys_b(event varchar2(64),time_waited number,total_waits number);

create table sys_e(event varchar2(64),time_waited number,total_waits number);

 

第二步：

insert into sys_b select event,time_waited,total_waits from v$system_event;

 

第三步：

執行使用者應用。。。（或者在高峰時期，需要診斷問題的時期）。

 

第四步：

insert into sys_e select event,time_waited,total_waits from v$system_event;

 

create table sys_dif as select e.event,e.time_waited - b.timed_waited time_waited,e.total_waits - b.total_waits total_waits from sys_b b,sys_e e where b.event=e.event;

 

select event "wait event",time_waited "time waited",time_waited / (select sum(time_waited) from sys_dif) "%time waited",total_waits "waits",total_waits / (select sum(total_waits) from sys_dif) "%waited"

from sys_dif order by 3 desc;

 

drop table sys_dif;

drop table sys_d;

drop table sys_e;

 

檢查發生了多少全表掃描

SELECT name, value FROM v$sysstat WHERE name LIKE '%table scans%';

table scans (long tables) 和 table scans (short tables) 和 full table scan 相關。如果 table scans (long tables) 比較高，調整應用或加索引。

監控全表掃描，並且檢視估計的時間等資訊

SELECT sid, serial#, opname, TO_CHAR(start_time,’HH24:MI:SS’)AS “START”,

(sofar/totalwork)*100 AS “PERCENT_COMPLETE” FROM v$session_longops;

 

SID SERIAL# OPNAME START PERCENT_COMPLETE

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

8 219 TABLE SCAN 13:00:09 48.98098

v$session_longops 檢視顯示執行時間超過 6 秒的的各種操作的情況，包括很多備份、恢復、收集統計資訊、資料查詢等。

找出消耗 30,000 位元組 Program Global Area (PGA) 記憶體的會話

select s.sid,username,name,value from v$statname n, v$session s, v$sesstat t where s.sid=t.sid and n.statistic#=t.statistic# and s.type='USER' and s.username is not null and n.name='session pga memory' and t.value > 30000;

查詢前十條效能差的 sql

SELECT * FROM (SELECT PARSING_USER_ID, EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE ROWNUM<10;

檢視佔用 CPU 資源很高的 Oracle 程式究竟是在做什麼操作，使用如下 SQL 語句：

Ø   select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process where $sqlarea.address=v$session.sql_address and v$sqlarea.hash_value=v$session.sql_hash_value and v$session.paddr=v$process.addr and v$process.spid in (PID);

 

Ø   SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='&spid' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece

  

Ø  REM getsql.sql

REM author eygle

REM 在windows上,已知程式ID,得到當前正在執行的語句

REM 在windows上,程式ID為16進位制,需要轉換,在UNIX直接為10進位制

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 /

 

根據遊標查詢正在執行的語句

select user_name,sql_text from v$open_cursor

where sid in (select sid from (select sid,serial#,username,program

from v$session where status='ACTIVE'))

 

用 trace 跟蹤會話

SQL> exec dbms_system.set_sql_trace_in_session(17,178,true);

PL/SQL procedure successfully completed

SQL> exec dbms_system.set_sql_trace_in_session(17,178,false);

PL/SQL procedure successfully completed

 

C:\>tkprof D:\oracle\admin\e3\udump\e3_ora_2292.trc d:\trace.txt

 

 

 

db file scattered read 、 db file sequential read wait event

db file scattered read 表示把資料讀進不連續的記憶體 buffer cache, 表示一個全表掃描

找到哪個會話正在發生 I/O 等以及正在執行的 SQL 語句

  

SELECT s.sid, s.sql_address, s.sql_hash_value FROM V$SESSION s, V$SESSION_WAIT w WHERE w.event LIKE 'db file%read'

AND w.sid = s.sid ;

 

根據 V$SESSION_WAIT (p1 and p2 columns) 從 DBA_EXTENTS 定位物件 

SELECT owner, segment_name FROM DBA_EXTENTS

WHERE file_id = <&p1>

AND <&p2> BETWEEN block_id AND block_id + blocks - 1;

 

db file sequential read 與 db file scattered read 不同的是讀到連續的記憶體中去，通常表示讀一塊

  • db file sequential read (single      block read into one SGA buffer)
  • db file scattered read (multiblock read into many discontinuous SGA buffers)
  • direct read (single or multiblock read into the PGA,      bypassing the SGA)
  • Figure 22-1 Scattered Read, Sequential      Read, and Direct Path Read

Enqueue 表示序列化訪問資料庫資源的佇列鎖，該事件表示會話正在等一個其它會話佔有的鎖

此時 V$SESSION_WAIT 裡引數含義：

  • P1 - Lock TYPE (or name) and MODE
  • P2 - Resource identifier ID1 for the lock
  • P3 - Resource identifier ID2 for the lock

V$LOCK 裡的對應：

  • V$LOCK.ID1 = P2
  • V$LOCK.ID2 = P3

找到鎖和鎖的持有者：

request>0 表示發生了 event enqueue 等待 

SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode, request, type  FROM V$LOCK

WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;

 

free buffer waits 表示在 cache 裡沒有空閒的 buffer 了，且引發了寫髒資料

可能的引起：

  • The I/O system is slow.
  • There are resources it is waiting for, such as      latches.
  • The buffer cache is so small that DBWR spends most      of it's time cleaning out buffers for server processes.
  • The buffer cache is so big that one DBWR process is      not enough to free enough buffers in the cache to satisfy requests.

latch free

Latch 是低階別內部鎖，被用來保護記憶體結構，當一個服務程式試圖得到一個 latch 但沒有成功，引發 latch free 事件

 

此時 V$SESSION_WAIT 裡引數含義：

  • P1 - Address of the latch
  • P2 - Latch number
  • P3 - Number of times process has already slept,      waiting for the latch 
找出正在發生等待的latch

SELECT n.name, SUM(w.p3) Sleeps FROM V$SESSION_WAIT w, V$LATCHNAME n WHERE w.event = `latch free' AND w.p2 = n.latch# GROUP BY n.name;

 

 

鎖的例子

Example I

We want to change the Schedule from Tele-meeting to Tele-conference. We will lock such rows for Update so that other users cannot update them. 

declare

    v_schedule varchar2(200);

    cursor my_cur is

         select replace(schedule, 'Tele-meeting', 'Tele-conference')

               from room_bookings

               for update of schedule;

begin

          open my_cur;

          loop

                   fetch my_cur into v_schedule;

                   exit when my_cur%NOTFOUND;

                  update room_bookings set schedule = v_schedule

                              where current of my_cur;

         end loop;

         close my_cur;

end;

/

Example II

Open two SQL sessions and in each session issue the following

Session I 

SQL>     SELECT SCHEDULE

           FROM room_bookings

           WHERE booking_code = 'B00090'

           FOR UPDATE OF schedule;

Session II 

SQL>     update room_bookings

set schedule='Test'

where booking_code='B00090';

The transaction in this session will fail to move ahead. This is because in Session I, the row has been locked. The transaction is Session II will move ahead if we issue the COMMIT or ROLLBACK command.

Session I 

SQL>     SELECT SCHEDULE

           FROM room_bookings

           WHERE booking_code = 'B00090'

           FOR UPDATE OF schedule;

 



ROOM_ SCHEDULE                                      BOOKING_CD

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

R0020    Booked for Tele-conference scheduled on 16/07/2003  B00090

 



SQL> rollback;

 



Rollback complete.

 



SQL>

Session II 

SQL>     update room_bookings

set schedule='Test'

where booking_code='B00090';

1 row updated.

v$session_wait 欄位說明： 

state4種狀態：

waiting: 會話正在等待這個事件

waited unknow time: timed_statistics=false, so 不能得到相關時間資訊

waited short time: 發生了等待，但等待時間非常小，so 不記錄

waited known time: 當會話等待到了等待的source，狀態從waiting進入waited known time.

 



wait_time,該欄位的值取決state:

state＝waiting,該欄位值忽略

state=waited unknown time,該欄位值忽略

state=waited short time,該欄位值忽略

state=waited known time,wait_time的值就是實際等待時間。看到這個值比較難，如果會話開始等待1個source，state將再次變成waiting,這個欄位的值again no userful!

 



seconds_in_wait,該欄位的值取決state:

state＝waiting,該值是實際等待時間，單位是秒

state=waited unknown time,該欄位值忽略

state=waited short time,該欄位值忽略

state=waited known time,該欄位值忽略

檢視自系統啟動來的系統詳細資訊（但檢視 1 段時間的系統效能變化情況才能更好找到問題） 

select event "wait event",time_waited "time waited",time_waited/(select sum(time_waited) from v$system_event) "%time waited",total_waits "waits",total_waits/(select sum(total_waits) from v$system_event) "%waited" from v$system_event order by 3 desc;

 

用 V$BH 檢視錶來顯示資料庫裡每個物件型別在資料緩衝區裡資料塊的數量，就可以看到資料緩衝區裡最常用的表，以及它們所消耗的記憶體量

select owner,object_name,count(1) "number of buffer",(count(1)/(select count(*) from v$bh))*100 "%data buffer" from dba_objects o,v$bh bh where o.object_id = bh.objd and o.owner not in('SYS','SYSTEM','AURORA$JIS$UTILITY$') group by owner, object_name order by count(1) desc;

 

What’s in the Database Buffer Cache? 聯合 v$bh 和 object_name

select o.owner,o.object_type,o.object_name,count(b.objd),count(b.objd)/(select count(*) from v$bh)*100 "%buffer" from v$bh b,dba_objects o where b.objd=o.object_id and o.owner not in('SYS','SYSTEM') group by o.owner,o.object_type,o.object_name order by count(b.objd) desc

 

group by o.owner,o.object_type,o.object_name ， count(b.objd) 表示在 v$bh 裡有多少該物件的不同塊在 buffer 裡，同一個物件在 buffer cache 裡可能不同的塊被 cache 在 buffer 裡。 V$bh 裡的每一行代表一塊，所以 select count(*) from v$bh 表示整個 buffer 有多少。

cache buffers chains

The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).

 

找出 10 條等待最多的 latch ：

select * from (select ADDR,LATCH#,GETS,MISSES,SLEEPS from V$LATCH_CHILDREN where name like 'cache buffers cha%' and MISSES>0 and sleeps>0 order by MISSES desc) where rownum<10;

 

ADDR         LATCH#       GETS     MISSES     SLEEPS

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

217C59D8         97    1189858         57          1

217A5098         97     304127         57          1

217E37A8         97     226257         47          1

21771FF8         97     318259         47          2

217C5128         97     261580         46          1

217A8D68         97     201213         46          1

217E14E8         97     182655         43          1

217DACA8         97     208325         43          1

217D4468         97     229606         43          1

  

SELECT file#, dbablk, class, state, TCH  FROM X$BH WHERE HLADDR='address of latch';

 

X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block ！


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14377/viewspace-2288205/，如需轉載，請註明出處，否則將追究法律責任。

相關文章