Oracle常用效能監控語句解析
轉載地址：http://www.cnblogs.com/preftest/archive/2010/11/14/1876856.html
1、監控等待事件
select event,sum(decode(wait_time,0,0,1)) prev, sum(decode(wait_time,0,1,0)) curr,count(*)
from v$session_wait
group by event order by 4;
Lock wait occur when a session attempts to acquire a lock that is already held by another session. A session will be blocked until the blocking session releases the lock. Locks are designed to ensure data integrity by limiting simultaneous data access.
Multi-user database locking generally consists of two levels: exclusive locks and share locks. You want to watch out for exclusive locks (that is, TX) as they prohibit resource sharing. For example, the first transaction that exclusively locks a resource is the only one that can alter the resource (except for the DBA) until the exclusive lock is released. Share locks, unlike exclusive locks, allow a resource to be shared.
Deadlocking is commonly seen in multi-user systems. It typically occurs when all the hung users are waiting to access a table that another user has locked. This situation causes a deadlock, because each user (transaction) is waiting for resources to be freed by the other user (the blocker). Often, many developers attempt to update the same table and many users attempting to update or select from the same table.
Most locking issues are application-specific and can be addressed by tuning the concurrency logic in the application.
也可利用v$system_event檢視執行下面的查詢檢視資料庫中某些常見的等待事件：
select * from v$system_event
where event in ('buffer busy waits',
'db file sequential read',
'db file scattered read',
'enqueue',
'free buffer waits',
'latch free',
'log file parallel write',
'log file sync',
'enq: TX - row lock contention');
接著，利用下面對v$session_event和v$session檢視進行的查詢，研究具有對上面顯示的內容有貢獻的等待事件的會話：
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.sid = se.sid
and se.event not like 'SQL*Net%'
and s.status = 'ACTIVE'
and s.username is not null;
還可以組合v$session和v$session_wait檢視進行查詢：
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.sid = sw.sid
and sw.event not like 'SQL*Net%'
and s.username is not null
order by sw.wait_time desc;
查詢具體會話等待事件的詳細資訊
select sid,event,p1text,p1,p2text,p2,p3text,p3
from v$session_wait
where sid between &1 and &2
and event not like '%SQL%'
and event not like '%rdbms%';
在查出會話執行了什麼SQL語句發生等待事件：
select s1.sid,s1.event,s2.sql_text
from v$session s1,v$sql s2
where s1.sid = &sid_in
and s1.event in('enq: TX - row lock contention')
and s1.SQL_ID = s2.sql_id ;
2、監控表空間的I/O比例：
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f,dba_data_files df
where f.file#=df.file_id
診斷：
If the number of physical block reads is significantly higher than the number of physical reads, this is an indication that the indexes on these tables may need to be reviewed, or there may be full table scans being performed on the tables within the tablespace. In general, if the number of block reads is equal to the number of reads, the tables in the tablespace were being accessed by a ROWID, requiring the database to read only one data block.
If one of the data files is getting a majority of the reads and writes, you may be able to improve performance by creating multiple data files on seperate disks or by striping the data file across multiple disks.
3、查詢是否有長時間的操作
同時滿足以下幾個條件，操作資訊才會出現在V$SESSION_LONGOPS中：
1）、操作是以下幾種操作之一
# Table scan;
# Index Fast Full Scan;
# Hash join;
# Sort/Merge;
# Sort Output;
# Rollback;
# Gather Table's Index Statistics
2）、操作時間大於6秒
3）、讀取的block數目大於一定量
如果是TABLE FULL SCAN，讀取的block數目至少大於10000
如果是Index Fast Full Scan，讀取的block數目至少大於1000
其他操作讀取block的數目不明
實驗：
create table tt as select * from all_objects;
commit;
Set timing on;
select * from tt order by 1,2,3,4;
用以下語句找出長時間操作的SQL語句：
select longops.sid,longops.elapsed_seconds,longops.opname,sql.sql_text from
v$session_longops longops , v$sql sql where longops.elapsed_seconds>6 and longo
ps.sql_id=sql.sql_id;
或者：
SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME
;
調整PGA最佳化排序：
首先檢視Oracle的v$pga_target_advice：
SELECT ROUND(pga_target_for_estimate/1024/1024) AS target_mb,
estd_pga_cache_hit_percentage AS hit_ratio,
estd_overalloc_count
FROM v$pga_target_advice
ORDER BY target_mb;
然後調整PGA
alter system set pga_aggregate_target=150M;
在OLTP系統中，典型PGA記憶體設定應該是總記憶體的較小部分（例如20％），剩下80％分配給SGA。
OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
在DSS系統中，由於會執行一些很大的查詢，典型的PGA記憶體最多分配70％的記憶體。
DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
Oracle的排序操作：
伺服器首先在sort_area_size指定大小的記憶體區域裡排序，如果所需的空間超過sort_area_size，排序會在臨時表空間裡進行。在專用伺服器模式下，排序空間在PGA中，在共享伺服器模式下，排序空間在UGA中。如果沒有建立large pool，UGA處於shared pool中，如果建立了large pool，UGA就處於large pool中，而PGA不在sga中，它是與每個程式對應單獨存在的。
PGA：program global area,為單個程式（伺服器程式或後臺程式）儲存資料和控制資訊的記憶體區域。PGA與程式一一對應，且只能被起對應的程式讀寫，PGA在使用者登入資料庫建立會話的時候建立。
排序診斷1:
Select * from v$sysstat where name like '%sort%';
--Sort(disk):要求IO去臨時表空間的排序數目
--Sort(memory)：完全在memory中完成的排序數目
--Sort(rows)：被排序的行數合計
Sort(disk)/ Sort(memory)<5%, 如果超過5%，增加sort_area_size的值(調整PGA)。
SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)*100 ratio
FROM v$sysstat disk,v$sysstat mem
WHERE mem.NAME='sorts (memory)' AND disk.NAME='sorts (disk)';
*排序操作需要大量CPU時間和記憶體
Oracle的排序過程分析：
當待排序資料集不是很大時，伺服器在記憶體（排序區）完成排序操作，如果排序需要更過的記憶體空間，伺服器將進行如下處理：
（1）將資料分成多個小的集合，對每一集合進行排序
（2）伺服器向磁碟申請臨時空間，將排好序的中間結果寫入臨時段，再對另外的整合進行排序。
（2）在所有的集合均排好序後，伺服器再將它們進行合併得到最終的結果，如果排序區尺寸太小，合併無法一次完成時，將分多次進行。
排序診斷2（監控臨時表空間的使用情況及其配置）：
Select tablespace_name,current_users,total_extents,
used_extents,extent_hits,max_used_blocks,max_sort_blocks
FROM v$sort_segment;
CURRENT_USERS: Number of active users
TOTAL_EXTENTS: Total number of extents
USED_EXTENTS: Extents currently allocated to sorts
EXTENT_HITS: Number of times an unused extent was found in the pool
MAX_USED_BLOCKS: Maximum number of used blocks
MAX_SORT_BLOCKS: Maximum number of blocks used by an individual sort
臨時表空間的配置：
A、initial/next設定為sort_area_size的整數倍，允許額外的一個block作為segment的header
B、pctincrease=0
C、基於不同的排序需要建立多個臨時表空間
D、將臨時表空間檔案分散到多個磁碟上
4、臨時表空間使用的監控
如果大量排序操作發生，就有可能動用到臨時表空間
透過動態效能檢視v$sort_usage還可以查詢使用排序段的使用者與會話資訊：
select username,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;
從segfile#可以找到動用了哪個表空間：
select tm.file# Fnum ,tf.tfafn AFN,tm.name FName from v$tempfile tm,x$kcctf tf where tm.file# = tf.tfnum;
參考：
如果程式中使用了臨時的LOB型別變數,Oracle會分配臨時空間,如果併發很高,初始區很大,那麼資料庫可能產生嚴重的TEMP表空間的不足問題.
可以透過測試輕易再現這種狀況,在多個Session中執行如下程式碼:
declare
A CLOB;
BEGIN
A:='ABC';
DBMS_LOCK.SLEEP(120);
END;
查詢v$sort_usage檢視,可以獲得臨時表空間的使用情況（哪個使用者、哪個Session因為什麼原因使用了多少臨時表空間）:
select s.username, s.sid, u.tablespace, u.contents, u.segtype,
round(u.blocks*8192/1024/1024,2) MB
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = 'TEMPORARY'
order by MB DESC ;
SEGTYPE=SORT的是因為排序而用到臨時表空間的。
SEGTYPE=LOB_DATA是因為使用了臨時的LOB型別變數而用到臨時表空間的。
參考：
實驗：
SQL> select max(sid) from v$mystat;
MAX(SID)
----------
45
SQL> DECLARE
2 a clob;
3 BEGIN
4 dbms_lob.createtemporary(a, TRUE,dbms_lob.call);
5 dbms_lob.freetemporary(a);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> SELECT se.inst_id,
2 se.username username,
3 se.SID sid,
4 se.status status,
5 se.sql_hash_value,
6 se.prev_hash_value,
7 su.TABLESPACE tablespace,
8 su.segtype,
9 su.CONTENTS CONTENTS,
10 round(su.blocks * 8192 / 1024 / 1024, 2) MB
11 FROM gv$session se,
12 gv$sort_usage su
13 WHERE se.saddr=su.session_addr
14 AND se.inst_id=su.inst_id
15 ORDER BY MB;
INST_ID USERNAME SID STATUS SQL_HASH_VALUE PREV_HASH_VALUE TABLESPACE SEGTYPE CONTENTS MB
------- -------- --- ------ -------------- --------------- ---------- -------- --------- --
1 SYS 45 INACTI 317853294 317853294 TEMP LOB_DATA TEMPORARY 1
此時CLOB佔用的TEMP空間不會自動釋放，需要等待會話斷開，才能釋放。但這個空間，在本會話中，還是可以重用的，只是不供其它會話使用。
在10.2.0.3以前，只能讓會話退出，以釋放這部份空間，在10.2.0.4中當作一個BUG（Bug:5723140）來修復，但預設不啟用，需要透過設定60025事件才可以釋放這些lob的TEMP空間。
參考：
LOB型別變數：
資料庫中提供了兩種欄位型別 Blob 和 Clob 用於儲存大型字串或二進位制資料（如圖片）。 Blob 採用單位元組儲存，適合儲存二進位制資料，如圖片檔案。 Clob 採用多位元組儲存，適合儲存大型文字資料。
臨時表空間最佳化：
（一）、建立使用者時要記得為使用者建立臨時表空間。
（二）、合理設定PGA，減少臨時表空間使用的機率。
（三）、要為臨時表空間保留足夠的硬碟空間。
參考：
檢視臨時表空間佔用率：
select * from v$temp_space_header;
重建臨時表空間的方法：
Temporary tablespace是不能直接drop預設的臨時表空間的，不過我們可以透過以下方法達到。
檢視目前的Temporary Tablespace
SQL> select name from v$tempfile;
NAME
———————————————————————
D:\ORACLE\ORADATA\ORCL\TEMP01.DBF
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
建立中轉臨時表空間
create temporary tablespace TEMP1 TEMPFILE 'D:\ORACLE\ORADATA\ORCL\temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
改變預設臨時表空間 為剛剛建立的新臨時表空間temp1
alter database default temporary tablespace temp1;
刪除原來臨時表空間
drop tablespace temp including contents and datafiles;
重新建立臨時表空間
create temporary tablespace TEMP TEMPFILE 'D:\ORACLE\ORADATA\ORCL\temp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
重置預設臨時表空間為新建的temp表空間
alter database default temporary tablespace temp;
刪除中轉用臨時表空間
drop tablespace temp1 including contents and datafiles;
以上的方法只是暫時釋放了臨時表空間的磁碟佔用空間，是治標但不是治本的方法，真正的治本的方法是找出資料庫中消耗資源比較大的sql語句，然後對其進行最佳化處理。
參考：
http://lanmh.javaeye.com/blog/643676
5、監控LibraryCache
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
後者除以前者,此比率小於1%,接近0%為好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
查詢不能被充分共享利用的SQL語句（查詢LibraryCache中執行次數偏低的SQL語句）：
SELECT sql_text FROM v$sqlarea WHERE executions < 5 ORDER BY UPPER(sql_text);
查詢SQL執行次數和SQL解釋次數（hard parse），對比兩個值的差：
SELECT sql_text , parse_calls , executions FROM v$sqlarea ORDER BY parse_calls;
查詢v$librarycache檢視的Reloads值（reparsing）的值，值應該接近0，否則應該考慮調整shared pool size
invalidations的值也應該接近0
select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;
重點關注SQL的命中率：
SELECT gethitratio FROM v$librarycache WHERE namespace = 'SQL AREA';
檢視指定某條SQL語句的執行情況（執行次數、載入次數等）：
SELECT sql_text , users_executing , executions , loads FROM v$sqlarea where sql_text like 'select * from hr.tt';
*SQL語句執行過程
1).使用hash演算法得到sql語句的hash_value值
2).如果hash_value值在記憶體中，叫做命中執行軟解析
3).如果hash_value值不存在，執行硬解析
4).語法解析，檢視是否有錯誤
5).語意解析，檢視許可權是否符合
6).若有檢視，取出檢視的定義
7).進行sql語句的自動改寫，如將子查詢改寫為連線
8).選擇最優的執行計劃
9).變數繫結
10).執行執行計劃
11).返回結果給使用者
因為軟解析是從此11步驟中第9步開始的，因此軟解析比硬解析節約大量的系統開銷，應該儘量降低硬解析的次數
診斷：
1） 檢查v$librarycache中sql area的gethitratio是否超過90%，如果未超過90%，應該檢查應用程式碼，提高應用程式碼的效率：
Select gethitratio from v$librarycache where namespace='SQL AREA';
2) v$librarycache中reloads/pins的比率應該小於1％，如果大於1％，應該增加引數shared_pool_size的值:
Select sum(pins) "executions", sum(reloads) "cache misses",sum(reloads)/sum(pins) from v$librarycache;
reloads/pins>1%有兩種可能，一種是library cache空間不足，一種是sql中引用的物件不合法。
3)檢視某個session的hard parse個數：
select a.sid,a.value from v$sesstat a,v$session b ,v$statname c where a.sid=b.sid and a.statistic#=c.statistic# and a.sid = 137 and c.name='parse count (hard)';
調優方法：
1）、調整shared_pool_size
SELECT shared_pool_size_for_estimate AS pool_size,estd_lc_size,estd_lc_time_saved FROM v$shared_pool_advice;
Alter System set shared_pool_size=120M;
2）、書寫程式是儘量使用變數不要過多的使用常量
實驗：
建立表格
SQL>CREATE TABLE m(x int);
建立儲存過程proc1，使用繫結變數
SQL>CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
FOR i IN 1..10000
LOOP
Execute immediate
'INSERT INTO m VALUES(:x)' USING i;
END LOOP;
END;
/
建立儲存過程proc2，不使用繫結變數
SQL>CREATE OR REPLACE PROCEDURE proc2
AS
BEGIN
FOR i IN 1..10000
LOOP
Execute immediate
'INSERT INTO m VALUES('||i||')' ;
END LOOP;
END;
/
執行proc2和proc1，對比執行效率
SQL>SET TIMING ON
SQL> exec proc2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.93
SQL> select count(*) from m;
COUNT(*)
----------
10000
Elapsed: 00:00:00.01
SQL> TRUNCATE TABLE m;
Table truncated.
Elapsed: 00:00:01.76
SQL> exec proc1;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.85
SQL> select count(*) from m;
COUNT(*)
----------
10000
Elapsed: 00:00:00.00
3）、修改cursor_sharing引數為similar，讓類似的SQL語句不做hard parse：
有時候我們的應用程式沒有使用繫結變數，而修改程式可能有點困難，我們可能需要設定CURSOR_SHARING=SIMILAR來強制ORACLE使用繫結變數。
Show parameter cursor
Alter system set cursor_sharing=SIMILAR
參考：
http://blog.csdn.net/biti_rainy/archive/2004/07/12/39466.aspx
http://space.itpub.net/519536/viewspace-562987
實驗：
SQL> show parameter cursor_sharing
cursor_sharing string EXACT
SQL> select * from test where object_id=1;
no rows selected
SQL> select sql_text,parse_calls from v$sqlarea where sql_text like 'select * from test%';
select * from test where object_id=:"SYS_B_0" 2
select * from test where object_id=1 1
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing=similar; ----second
Session altered.
SQL> select * from test where object_id=1;
no rows selected
SQL> select sql_text,parse_calls from v$sqlarea where sql_text like 'select * from test%';
select * from test where object_id=:"SYS_B_0" 1
SQL> select * from test where object_id=2;
no rows selected
SQL> select sql_text,parse_calls from v$sqlarea where sql_text like 'select * from test%';
select * from test where object_id=:"SYS_B_0" 2
4）、大物件保留
查詢沒有儲存在library cache中的大物件：
Select * from v$db_object_cache where sharable_mem>10000 and type in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and kept='NO';
將這些物件儲存在library cache中：
Execute dbms_shared_pool.keep('package_name');
對應指令碼：dbmspool.sql
參考：
6、找使用CPU多的使用者session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
*12是cpu used by this session
再找出使用CPU多的SQL語句：
查詢指定SPID正在執行的SQL語句：
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+) AND P.spid LIKE '%&1%';
*在linux環境可以透過ps檢視程式資訊包括pid,windows中工作管理員的PID與v$process中pid不能一一對應。windows是多執行緒伺服器,每個程式包含一系列執行緒。這點於unix等不同，Unix每個Oralce程式獨立存在，在Nt上所有執行緒由Oralce程式衍生。
指定SID檢視正在執行的SQL語句：
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+) AND s.sid = '136';
7、回滾段的爭用情況：
select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;
對含有回滾段塊的緩衝區的爭用也會影響到對回滾段的爭用。這可以透過查詢動態效能表V$WAITSTAT來檢測是否存在對回滾段的爭用，例如：
SELECT class,count FROM V$WAITSTAT
WHERE class IN('system undo header','system undo block','undo header','undo block');
其中引數含義如下：
◆ system undo header：對含有SYSTEM回滾段標題塊的緩衝區的等待次數。
◆ system undo block：對含有SYSTEM回滾段非標題塊的緩衝區的等待次數。
◆ undo header：對含有非SYSTEM回滾段標題塊的緩衝區的等待次數。
◆ undo block：對含有非SYSTEM回滾段非標題塊的緩衝區的等待次數。
如果任何等待次數大於總請求數的1%，則應建立更多的回滾段來減少競爭，可以週期性地檢查這些統計數字，並將它與總的請求資料的次數作比較。總的請求資料次數可用如下語句求出:
SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN('db block gets','consistent gets');
1、監控等待事件
select event,sum(decode(wait_time,0,0,1)) prev, sum(decode(wait_time,0,1,0)) curr,count(*)
from v$session_wait
group by event order by 4;
Lock wait occur when a session attempts to acquire a lock that is already held by another session. A session will be blocked until the blocking session releases the lock. Locks are designed to ensure data integrity by limiting simultaneous data access.
Multi-user database locking generally consists of two levels: exclusive locks and share locks. You want to watch out for exclusive locks (that is, TX) as they prohibit resource sharing. For example, the first transaction that exclusively locks a resource is the only one that can alter the resource (except for the DBA) until the exclusive lock is released. Share locks, unlike exclusive locks, allow a resource to be shared.
Deadlocking is commonly seen in multi-user systems. It typically occurs when all the hung users are waiting to access a table that another user has locked. This situation causes a deadlock, because each user (transaction) is waiting for resources to be freed by the other user (the blocker). Often, many developers attempt to update the same table and many users attempting to update or select from the same table.
Most locking issues are application-specific and can be addressed by tuning the concurrency logic in the application.
也可利用v$system_event檢視執行下面的查詢檢視資料庫中某些常見的等待事件：
select * from v$system_event
where event in ('buffer busy waits',
'db file sequential read',
'db file scattered read',
'enqueue',
'free buffer waits',
'latch free',
'log file parallel write',
'log file sync',
'enq: TX - row lock contention');
接著，利用下面對v$session_event和v$session檢視進行的查詢，研究具有對上面顯示的內容有貢獻的等待事件的會話：
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.sid = se.sid
and se.event not like 'SQL*Net%'
and s.status = 'ACTIVE'
and s.username is not null;
還可以組合v$session和v$session_wait檢視進行查詢：
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.sid = sw.sid
and sw.event not like 'SQL*Net%'
and s.username is not null
order by sw.wait_time desc;
查詢具體會話等待事件的詳細資訊
select sid,event,p1text,p1,p2text,p2,p3text,p3
from v$session_wait
where sid between &1 and &2
and event not like '%SQL%'
and event not like '%rdbms%';
在查出會話執行了什麼SQL語句發生等待事件：
select s1.sid,s1.event,s2.sql_text
from v$session s1,v$sql s2
where s1.sid = &sid_in
and s1.event in('enq: TX - row lock contention')
and s1.SQL_ID = s2.sql_id ;
2、監控表空間的I/O比例：
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f,dba_data_files df
where f.file#=df.file_id
診斷：
If the number of physical block reads is significantly higher than the number of physical reads, this is an indication that the indexes on these tables may need to be reviewed, or there may be full table scans being performed on the tables within the tablespace. In general, if the number of block reads is equal to the number of reads, the tables in the tablespace were being accessed by a ROWID, requiring the database to read only one data block.
If one of the data files is getting a majority of the reads and writes, you may be able to improve performance by creating multiple data files on seperate disks or by striping the data file across multiple disks.
3、查詢是否有長時間的操作
同時滿足以下幾個條件，操作資訊才會出現在V$SESSION_LONGOPS中：
1）、操作是以下幾種操作之一
# Table scan;
# Index Fast Full Scan;
# Hash join;
# Sort/Merge;
# Sort Output;
# Rollback;
# Gather Table's Index Statistics
2）、操作時間大於6秒
3）、讀取的block數目大於一定量
如果是TABLE FULL SCAN，讀取的block數目至少大於10000
如果是Index Fast Full Scan，讀取的block數目至少大於1000
其他操作讀取block的數目不明
實驗：
create table tt as select * from all_objects;
commit;
Set timing on;
select * from tt order by 1,2,3,4;
用以下語句找出長時間操作的SQL語句：
select longops.sid,longops.elapsed_seconds,longops.opname,sql.sql_text from
v$session_longops longops , v$sql sql where longops.elapsed_seconds>6 and longo
ps.sql_id=sql.sql_id;
或者：
SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME
;
調整PGA最佳化排序：
首先檢視Oracle的v$pga_target_advice：
SELECT ROUND(pga_target_for_estimate/1024/1024) AS target_mb,
estd_pga_cache_hit_percentage AS hit_ratio,
estd_overalloc_count
FROM v$pga_target_advice
ORDER BY target_mb;
然後調整PGA
alter system set pga_aggregate_target=150M;
在OLTP系統中，典型PGA記憶體設定應該是總記憶體的較小部分（例如20％），剩下80％分配給SGA。
OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
在DSS系統中，由於會執行一些很大的查詢，典型的PGA記憶體最多分配70％的記憶體。
DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
Oracle的排序操作：
伺服器首先在sort_area_size指定大小的記憶體區域裡排序，如果所需的空間超過sort_area_size，排序會在臨時表空間裡進行。在專用伺服器模式下，排序空間在PGA中，在共享伺服器模式下，排序空間在UGA中。如果沒有建立large pool，UGA處於shared pool中，如果建立了large pool，UGA就處於large pool中，而PGA不在sga中，它是與每個程式對應單獨存在的。
PGA：program global area,為單個程式（伺服器程式或後臺程式）儲存資料和控制資訊的記憶體區域。PGA與程式一一對應，且只能被起對應的程式讀寫，PGA在使用者登入資料庫建立會話的時候建立。
排序診斷1:
Select * from v$sysstat where name like '%sort%';
--Sort(disk):要求IO去臨時表空間的排序數目
--Sort(memory)：完全在memory中完成的排序數目
--Sort(rows)：被排序的行數合計
Sort(disk)/ Sort(memory)<5%, 如果超過5%，增加sort_area_size的值(調整PGA)。
SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)*100 ratio
FROM v$sysstat disk,v$sysstat mem
WHERE mem.NAME='sorts (memory)' AND disk.NAME='sorts (disk)';
*排序操作需要大量CPU時間和記憶體
Oracle的排序過程分析：
當待排序資料集不是很大時，伺服器在記憶體（排序區）完成排序操作，如果排序需要更過的記憶體空間，伺服器將進行如下處理：
（1）將資料分成多個小的集合，對每一集合進行排序
（2）伺服器向磁碟申請臨時空間，將排好序的中間結果寫入臨時段，再對另外的整合進行排序。
（2）在所有的集合均排好序後，伺服器再將它們進行合併得到最終的結果，如果排序區尺寸太小，合併無法一次完成時，將分多次進行。
排序診斷2（監控臨時表空間的使用情況及其配置）：
Select tablespace_name,current_users,total_extents,
used_extents,extent_hits,max_used_blocks,max_sort_blocks
FROM v$sort_segment;
CURRENT_USERS: Number of active users
TOTAL_EXTENTS: Total number of extents
USED_EXTENTS: Extents currently allocated to sorts
EXTENT_HITS: Number of times an unused extent was found in the pool
MAX_USED_BLOCKS: Maximum number of used blocks
MAX_SORT_BLOCKS: Maximum number of blocks used by an individual sort
臨時表空間的配置：
A、initial/next設定為sort_area_size的整數倍，允許額外的一個block作為segment的header
B、pctincrease=0
C、基於不同的排序需要建立多個臨時表空間
D、將臨時表空間檔案分散到多個磁碟上
4、臨時表空間使用的監控
如果大量排序操作發生，就有可能動用到臨時表空間
透過動態效能檢視v$sort_usage還可以查詢使用排序段的使用者與會話資訊：
select username,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;
從segfile#可以找到動用了哪個表空間：
select tm.file# Fnum ,tf.tfafn AFN,tm.name FName from v$tempfile tm,x$kcctf tf where tm.file# = tf.tfnum;
參考：
如果程式中使用了臨時的LOB型別變數,Oracle會分配臨時空間,如果併發很高,初始區很大,那麼資料庫可能產生嚴重的TEMP表空間的不足問題.
可以透過測試輕易再現這種狀況,在多個Session中執行如下程式碼:
declare
A CLOB;
BEGIN
A:='ABC';
DBMS_LOCK.SLEEP(120);
END;
查詢v$sort_usage檢視,可以獲得臨時表空間的使用情況（哪個使用者、哪個Session因為什麼原因使用了多少臨時表空間）:
select s.username, s.sid, u.tablespace, u.contents, u.segtype,
round(u.blocks*8192/1024/1024,2) MB
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = 'TEMPORARY'
order by MB DESC ;
SEGTYPE=SORT的是因為排序而用到臨時表空間的。
SEGTYPE=LOB_DATA是因為使用了臨時的LOB型別變數而用到臨時表空間的。
參考：
實驗：
SQL> select max(sid) from v$mystat;
MAX(SID)
----------
45
SQL> DECLARE
2 a clob;
3 BEGIN
4 dbms_lob.createtemporary(a, TRUE,dbms_lob.call);
5 dbms_lob.freetemporary(a);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> SELECT se.inst_id,
2 se.username username,
3 se.SID sid,
4 se.status status,
5 se.sql_hash_value,
6 se.prev_hash_value,
7 su.TABLESPACE tablespace,
8 su.segtype,
9 su.CONTENTS CONTENTS,
10 round(su.blocks * 8192 / 1024 / 1024, 2) MB
11 FROM gv$session se,
12 gv$sort_usage su
13 WHERE se.saddr=su.session_addr
14 AND se.inst_id=su.inst_id
15 ORDER BY MB;
INST_ID USERNAME SID STATUS SQL_HASH_VALUE PREV_HASH_VALUE TABLESPACE SEGTYPE CONTENTS MB
------- -------- --- ------ -------------- --------------- ---------- -------- --------- --
1 SYS 45 INACTI 317853294 317853294 TEMP LOB_DATA TEMPORARY 1
此時CLOB佔用的TEMP空間不會自動釋放，需要等待會話斷開，才能釋放。但這個空間，在本會話中，還是可以重用的，只是不供其它會話使用。
在10.2.0.3以前，只能讓會話退出，以釋放這部份空間，在10.2.0.4中當作一個BUG（Bug:5723140）來修復，但預設不啟用，需要透過設定60025事件才可以釋放這些lob的TEMP空間。
參考：
LOB型別變數：
資料庫中提供了兩種欄位型別 Blob 和 Clob 用於儲存大型字串或二進位制資料（如圖片）。 Blob 採用單位元組儲存，適合儲存二進位制資料，如圖片檔案。 Clob 採用多位元組儲存，適合儲存大型文字資料。
臨時表空間最佳化：
（一）、建立使用者時要記得為使用者建立臨時表空間。
（二）、合理設定PGA，減少臨時表空間使用的機率。
（三）、要為臨時表空間保留足夠的硬碟空間。
參考：
檢視臨時表空間佔用率：
select * from v$temp_space_header;
重建臨時表空間的方法：
Temporary tablespace是不能直接drop預設的臨時表空間的，不過我們可以透過以下方法達到。
檢視目前的Temporary Tablespace
SQL> select name from v$tempfile;
NAME
———————————————————————
D:\ORACLE\ORADATA\ORCL\TEMP01.DBF
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
建立中轉臨時表空間
create temporary tablespace TEMP1 TEMPFILE 'D:\ORACLE\ORADATA\ORCL\temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
改變預設臨時表空間 為剛剛建立的新臨時表空間temp1
alter database default temporary tablespace temp1;
刪除原來臨時表空間
drop tablespace temp including contents and datafiles;
重新建立臨時表空間
create temporary tablespace TEMP TEMPFILE 'D:\ORACLE\ORADATA\ORCL\temp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
重置預設臨時表空間為新建的temp表空間
alter database default temporary tablespace temp;
刪除中轉用臨時表空間
drop tablespace temp1 including contents and datafiles;
以上的方法只是暫時釋放了臨時表空間的磁碟佔用空間，是治標但不是治本的方法，真正的治本的方法是找出資料庫中消耗資源比較大的sql語句，然後對其進行最佳化處理。
參考：
http://lanmh.javaeye.com/blog/643676
5、監控LibraryCache
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
後者除以前者,此比率小於1%,接近0%為好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
查詢不能被充分共享利用的SQL語句（查詢LibraryCache中執行次數偏低的SQL語句）：
SELECT sql_text FROM v$sqlarea WHERE executions < 5 ORDER BY UPPER(sql_text);
查詢SQL執行次數和SQL解釋次數（hard parse），對比兩個值的差：
SELECT sql_text , parse_calls , executions FROM v$sqlarea ORDER BY parse_calls;
查詢v$librarycache檢視的Reloads值（reparsing）的值，值應該接近0，否則應該考慮調整shared pool size
invalidations的值也應該接近0
select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;
重點關注SQL的命中率：
SELECT gethitratio FROM v$librarycache WHERE namespace = 'SQL AREA';
檢視指定某條SQL語句的執行情況（執行次數、載入次數等）：
SELECT sql_text , users_executing , executions , loads FROM v$sqlarea where sql_text like 'select * from hr.tt';
*SQL語句執行過程
1).使用hash演算法得到sql語句的hash_value值
2).如果hash_value值在記憶體中，叫做命中執行軟解析
3).如果hash_value值不存在，執行硬解析
4).語法解析，檢視是否有錯誤
5).語意解析，檢視許可權是否符合
6).若有檢視，取出檢視的定義
7).進行sql語句的自動改寫，如將子查詢改寫為連線
8).選擇最優的執行計劃
9).變數繫結
10).執行執行計劃
11).返回結果給使用者
因為軟解析是從此11步驟中第9步開始的，因此軟解析比硬解析節約大量的系統開銷，應該儘量降低硬解析的次數
診斷：
1） 檢查v$librarycache中sql area的gethitratio是否超過90%，如果未超過90%，應該檢查應用程式碼，提高應用程式碼的效率：
Select gethitratio from v$librarycache where namespace='SQL AREA';
2) v$librarycache中reloads/pins的比率應該小於1％，如果大於1％，應該增加引數shared_pool_size的值:
Select sum(pins) "executions", sum(reloads) "cache misses",sum(reloads)/sum(pins) from v$librarycache;
reloads/pins>1%有兩種可能，一種是library cache空間不足，一種是sql中引用的物件不合法。
3)檢視某個session的hard parse個數：
select a.sid,a.value from v$sesstat a,v$session b ,v$statname c where a.sid=b.sid and a.statistic#=c.statistic# and a.sid = 137 and c.name='parse count (hard)';
調優方法：
1）、調整shared_pool_size
SELECT shared_pool_size_for_estimate AS pool_size,estd_lc_size,estd_lc_time_saved FROM v$shared_pool_advice;
Alter System set shared_pool_size=120M;
2）、書寫程式是儘量使用變數不要過多的使用常量
實驗：
建立表格
SQL>CREATE TABLE m(x int);
建立儲存過程proc1，使用繫結變數
SQL>CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
FOR i IN 1..10000
LOOP
Execute immediate
'INSERT INTO m VALUES(:x)' USING i;
END LOOP;
END;
/
建立儲存過程proc2，不使用繫結變數
SQL>CREATE OR REPLACE PROCEDURE proc2
AS
BEGIN
FOR i IN 1..10000
LOOP
Execute immediate
'INSERT INTO m VALUES('||i||')' ;
END LOOP;
END;
/
執行proc2和proc1，對比執行效率
SQL>SET TIMING ON
SQL> exec proc2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.93
SQL> select count(*) from m;
COUNT(*)
----------
10000
Elapsed: 00:00:00.01
SQL> TRUNCATE TABLE m;
Table truncated.
Elapsed: 00:00:01.76
SQL> exec proc1;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.85
SQL> select count(*) from m;
COUNT(*)
----------
10000
Elapsed: 00:00:00.00
3）、修改cursor_sharing引數為similar，讓類似的SQL語句不做hard parse：
有時候我們的應用程式沒有使用繫結變數，而修改程式可能有點困難，我們可能需要設定CURSOR_SHARING=SIMILAR來強制ORACLE使用繫結變數。
Show parameter cursor
Alter system set cursor_sharing=SIMILAR
參考：
http://blog.csdn.net/biti_rainy/archive/2004/07/12/39466.aspx
http://space.itpub.net/519536/viewspace-562987
實驗：
SQL> show parameter cursor_sharing
cursor_sharing string EXACT
SQL> select * from test where object_id=1;
no rows selected
SQL> select sql_text,parse_calls from v$sqlarea where sql_text like 'select * from test%';
select * from test where object_id=:"SYS_B_0" 2
select * from test where object_id=1 1
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing=similar; ----second
Session altered.
SQL> select * from test where object_id=1;
no rows selected
SQL> select sql_text,parse_calls from v$sqlarea where sql_text like 'select * from test%';
select * from test where object_id=:"SYS_B_0" 1
SQL> select * from test where object_id=2;
no rows selected
SQL> select sql_text,parse_calls from v$sqlarea where sql_text like 'select * from test%';
select * from test where object_id=:"SYS_B_0" 2
4）、大物件保留
查詢沒有儲存在library cache中的大物件：
Select * from v$db_object_cache where sharable_mem>10000 and type in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and kept='NO';
將這些物件儲存在library cache中：
Execute dbms_shared_pool.keep('package_name');
對應指令碼：dbmspool.sql
參考：
6、找使用CPU多的使用者session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
*12是cpu used by this session
再找出使用CPU多的SQL語句：
查詢指定SPID正在執行的SQL語句：
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+) AND P.spid LIKE '%&1%';
*在linux環境可以透過ps檢視程式資訊包括pid,windows中工作管理員的PID與v$process中pid不能一一對應。windows是多執行緒伺服器,每個程式包含一系列執行緒。這點於unix等不同，Unix每個Oralce程式獨立存在，在Nt上所有執行緒由Oralce程式衍生。
指定SID檢視正在執行的SQL語句：
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+) AND s.sid = '136';
7、回滾段的爭用情況：
select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;
對含有回滾段塊的緩衝區的爭用也會影響到對回滾段的爭用。這可以透過查詢動態效能表V$WAITSTAT來檢測是否存在對回滾段的爭用，例如：
SELECT class,count FROM V$WAITSTAT
WHERE class IN('system undo header','system undo block','undo header','undo block');
其中引數含義如下：
◆ system undo header：對含有SYSTEM回滾段標題塊的緩衝區的等待次數。
◆ system undo block：對含有SYSTEM回滾段非標題塊的緩衝區的等待次數。
◆ undo header：對含有非SYSTEM回滾段標題塊的緩衝區的等待次數。
◆ undo block：對含有非SYSTEM回滾段非標題塊的緩衝區的等待次數。
如果任何等待次數大於總請求數的1%，則應建立更多的回滾段來減少競爭，可以週期性地檢查這些統計數字，並將它與總的請求資料的次數作比較。總的請求資料次數可用如下語句求出:
SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN('db block gets','consistent gets');
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28371090/viewspace-1172623/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle效能監控常用命令Oracle
- Oracle日常效能檢視常用語句Oracle
- Oracle 資料庫監控SQL語句Oracle資料庫SQL
- 效能監控方面的一些SQL語句SQL
- 監控oracle的觸發器語句(轉)Oracle觸發器
- ORACLE常用語句：Oracle
- 常用oracle語句Oracle
- Oracle-監控sql語句的過載率OracleSQL
- 監控oracle效能的一些常用查詢Oracle
- Oracle常用監控SQLOracleSQL
- AIX常用的效能監控命令AI
- 監控 SQL Server 的執行狀況--常用檢測語句SQLServer
- oracle常用SQL語句OracleSQL
- Oracle-常用監控SQLOracleSQL
- Oracle常用監控SQL(轉）OracleSQL
- oracle 常用語句彙總Oracle
- 常用的oracle基本語句Oracle
- Oracle DBA常用監控指令碼Oracle指令碼
- Linux 常用系統效能監控命令Linux
- oracle資料庫常用語句Oracle資料庫
- 【LOB】Oracle lob管理常用語句Oracle
- Oracle - 表相關常用操作語句Oracle
- Oracle常用的查詢語句Oracle
- Oracle維護常用SQL語句OracleSQL
- oracle監控資料泵匯入和匯出的sql語句OracleSQL
- ORACLE監控DML語句的一些資料的彙總Oracle
- 效能測試常用Oracle語句，這10個果斷收藏了！Oracle
- 指令碼：監控當前活動的語句指令碼
- 幾個常用的linux效能監控命令Linux
- ORACLE UPDATE 語句語法與效能分析Oracle
- ORACLE：使用tcpdump 監控客戶端發過來的所有SQL語句OracleTCP客戶端SQL
- Oracle使用審計監控使用者執行過的SQL語句OracleSQL
- Oracle 許可權常用語句【轉】Oracle
- ORACLE DBA常用語句和指令碼Oracle指令碼
- Oracle常用維護語句總結Oracle
- 監控使用高cpu的sql語句指令碼SQL指令碼
- oracle DBA 常用監控指令碼1(轉)Oracle指令碼
- 常用的4個伺服器效能監控命令伺服器