整合營銷服務商
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
看重復索引
WITH indexes AS (
SELECT
i.owner,
i.index_name,
i.table_name,
listagg(c.column_name, ', ') WITHIN GROUP (
ORDER BY
c.column_position) AS columns
FROM
dba_indexes i
JOIN dba_ind_columns c ON
i.index_name=c.index_name
GROUP BY
i.owner,
i.table_name,
i.index_name,
i.leaf_blocks )
SELECT
i.OWNER ,
i.table_name,
i.index_name AS "Deletion candidate index",
i.columns AS "Deletion candidate columns",
j.index_name AS "Existing index",
j.columns AS "Existing columns"
FROM
indexes i
JOIN indexes j ON
i.table_name=j.table_name
AND j.columns LIKE i.columns || ',%'用戶命中率
SET LINESIZE 500
COLUMN "Hit Ratio %" FORMAT 999.99
SELECT a.username "Username",
b.consistent_gets "Consistent Gets",
b.block_gets "DB Block Gets",
b.physical_reads "Physical Reads",
Round(100* (b.consistent_gets + b.block_gets - b.physical_reads) /
(b.consistent_gets + b.block_gets),2) "Hit Ratio %"
FROM v$session a,
v$sess_io b
WHERE a.sid=b.sid
AND (b.consistent_gets + b.block_gets) > 0
AND a.username IS NOT NULL;查詢碎片程度高（實際使用率小于30%）的表
可以收縮的表條件為什么block>100，因為一些很小的表，只有幾行數據實際大小很小，但是block一次性分配就是5個（11g開始默認一次性分配1M的block大小了，見create table storged的NEXT參數），5個block相對于幾行小表數據來說就相差太大了。
算法中/0.9是因為塊的pfree一般為10%，所以一個塊最多只用了90%，而且一行數據大于8KB時容易產生行鏈接，把一行分片存儲，一樣的一個塊連90%都用不滿 ，AVGROWLEN還是比較準的，比如個人實驗情況一表6個字段，一個number，其他5個都是char(100)但是實際數據都是’1111111’7位，AVGROWLEN顯示依然為513 。
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) "理論大小M",
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) "實際大小M",
round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
(BLOCKS * 8192 / 1024 / 1024),
3) * 100 || '%' "實際使用率%"
FROM DBA_TABLES
where blocks > 100
and owner='TXPROD'
and (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
(BLOCKS * 8192 / 1024 / 1024) < 0.3
order by (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
(BLOCKS * 8192 / 1024 / 1024) desc查詢索引碎片的比例
索引刪除行數除以索引總行數的百分比>30%即認為索引碎片大，也就是需要重建的索引
select name,
del_lf_rows,
lf_rows,
round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) || '%' frag_pct
from index_stats
where round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) > 30;集群因子clustering_factor高的表
集群因子越接近塊數越好，接近行數則說明索引列的列值相等的行分布極度散列，可能不走索引掃描而走全表掃描 ：
方法一
/* Formatted on 2019/11/6 10:02:07 (QP5 v5.326) */
SELECT tab.table_name,
tab.blocks,
tab.num_rows,
ind.index_name,
ind.clustering_factor,
ROUND (
NVL (ind.clustering_factor, 1)
/ DECODE (tab.num_rows, 0, 1, tab.num_rows),
3)
* 100
|| '%'
"集群因子接近行數"
FROM user_tables tab, user_indexes ind
WHERE tab.table_name=ind.table_name
AND tab.owner='TXPROD'
AND tab.blocks > 100
AND NVL (ind.clustering_factor, 1)
/ DECODE (tab.num_rows, 0, 1, tab.num_rows) BETWEEN 0.35
AND 3方法二
/* Formatted on 2019/11/6 10:03:16 (QP5 v5.326) */
SELECT tab.owner,
tab.table_name,
tab.blocks,
tab.num_rows,
ind.index_name,
ind.clustering_factor,
ROUND (
NVL (ind.clustering_factor, 1)
/ DECODE (tab.num_rows, 0, 1, tab.num_rows),
3)
* 100
|| '%'
"集群因子接近行數"
FROM dba_tables tab, dba_indexes ind
WHERE tab.table_name=ind.table_name
AND tab.OWNER=ind.OWNER
AND tab.owner NOT IN ('SYS',
'SYSTEM',
'WMSYS',
'DBSNMP',
'CTXSYS',
'XDB',
'ORDDATA',
'SYSMAN',
'CATALOG',
'APEX_030200',
'MDSYS',
'OLAPSYS',
'EXFSYS')
AND tab.blocks > 100
AND NVL (ind.clustering_factor, 1)
/ DECODE (tab.num_rows, 0, 1, tab.num_rows) BETWEEN 0.35
AND 3根據sid查spid或根據spid查sid
SELECT s.sid,
s.serial#,
p.spid,
s.terminal,
s.LOGON_TIME,
s.status,
s.PROGRAM,
s.CLIENT_IDENTIFIER,
s.machine,
s.action,
s.MODULE,
s.PROCESS "客戶端機器進程號",
s.osuser
FROM v$session s, v$process p
WHERE s.paddr=p.addr AND s.sid=XX OR p.spid=YY根據sid查看具體的sql語句，不要加條件v$session.status=’ ACTIVE’，比如toad對同一數據庫開兩個連接會話，都執行了一些語句，其中一個窗口查詢select * from v$session時會發現另一個窗口在v$session.status是INACTIVE，并不代表另一個窗口沒有執行過sql語句，而當前窗口是active狀態，對應的sql_id對應的語句就是select * from v$session而不是之前執行過的sql語句，ACTIVE表示當前正在執行sql。
一個sid可能執行過很多個sql，所以有時需要的sql通過如下查不到是正常的，比如查詢到某死鎖源sid，通過如下查詢可能只是個select語句，而真正引起死鎖的sql卻查不到，是因為可能這個sid持續了很長時間，這個sid之前執行的一些sql在v$sql可能已經被清除了。
方法一
/* Formatted on 2019/11/6 10:04:20 (QP5 v5.326) */
SELECT username,
sid,
SERIAL#,
LOGON_TIME,
status,
PROGRAM,
CLIENT_IDENTIFIER,
machine,
action,
PROCESS "客戶端機器進程號",
osuser,
sql_text
FROM v$session a, v$sqltext_with_newlines b
WHERE DECODE (a.sql_hash_value, 0, prev_hash_value, sql_hash_value)= b.hash_value
AND a.sid=&sid
ORDER BY piece;方法二
/* Formatted on 2019/11/6 10:04:35 (QP5 v5.326) */
SELECT username,
sid,
SERIAL#,
LOGON_TIME,
status,
sql_fulltext,
PROGRAM,
CLIENT_IDENTIFIER,
machine,
a.action,
PROCESS "客戶端機器進程號",
osuser
FROM v$session a, v$sql b
WHERE DECODE (a.sql_hash_value, 0, prev_hash_value, sql_hash_value)= b.hash_value
AND a.sid=&sid如果上面語句執行太慢，則按如下兩步
/* Formatted on 2019/11/6 10:05:18 (QP5 v5.326) */
SELECT sql_hash_value,
prev_hash_value,
username,
sid,
SERIAL#,
LOGON_TIME,
status,
PROGRAM,
CLIENT_IDENTIFIER,
machine,
action,
PROCESS "客戶端機器進程號",
osuser
FROM v$session
WHERE sid=&sid;
SELECT sql_fulltext
FROM v$sql
WHERE hash_value=XX;--XX為上面 sqlhashvalue，如果 sqlhashvalue為0，則XX為上面 prevhashvalue
根據spid查詢具體的sql語句(不要加條件v$session.status=’ ACTIVE’,比如toad對同一數據庫開兩個連接會話，都執行了一些語句，其中一個窗口查詢select * from v$session時會發現另一個窗口在v$session.status是INACTIVE，并不代表另一個窗口沒有執行過sql語句，而當前窗口是active狀態，對應的sql_id對應的語句就是select * from v$session而不是之前執行過的sql語句，ACTIVE表示當前正在執行sql。)
/* Formatted on 2019/11/6 10:05:33 (QP5 v5.326) */
SELECT ss.SID,
ss.SERIAL#,
ss.LOGON_TIME,
pr.SPID,
sa.SQL_FULLTEXT,
ss.machine,
ss.TERMINAL,
ss.PROGRAM,
ss.USERNAME,
ss.CLIENT_IDENTIFIER,
ss.action,
ss.PROCESS "客戶端機器進程號",
ss.STATUS,
ss.OSUSER,
ss.status,
ss.last_call_et,
sa.sql_text
FROM v$process pr, v$session ss, v$sql sa
WHERE pr.ADDR=ss.PADDR
AND DECODE (ss.sql_hash_value, 0, prev_hash_value, sql_hash_value)= sa.hash_value
AND pr.spid=&spid查看歷史session_id的SQL來自哪個IP
查看trace文件名就可以知道spid,trace文件里面有sid和具體sql，如果trace存在incident，那trace就看不到具體sql，但是可以在incident文件中看到具體的sql，如DWora17751.trc中17751就是spid，里面有這樣的內容Incident 115 created, dump file: /XX/incident/incdir115/DWora17751i115.trc，那么在DWora17751_i115.trc就可以看到具體的sql語句)
DBora29349.trc中出現
*** SESSION ID:(5057.12807) 2016-10-26 14:45:52.726 通過表V$ACTIVESESSIONHISTORY來查
select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807 查詢上面的machine的IP
select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.machine='localhost' 通過上面的spid在oracle服務器上執行netstat -anp |grep spid即可
[oracle@dwdb trace]$ netstat -anp |grep 17630 tcp 210 0 192.168.64.228:11095 192.168.21.16:1521 ESTABLISHED 17630/oracleDB tcp 0 0 ::ffff:192.168.64.228:1521 ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB出現兩個，說明來自220，連接了228數據庫服務器，但是又通過228服務器的dblink去連接了16服務器
查詢死鎖堵塞的會話sid
最簡單的一個SQL
select * from V$SESSION_BLOCKERS select * from dba_waiters;最常用的一個SQL
select sid,status,LOGON_TIME,sql_id,blocking_session "死鎖直接源",FINAL_BLOCKING_SESSION "死鎖最終源",event,seconds_in_wait "會話鎖住時間_S",LAST_CALL_ET "會話持續時間_S" from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID';可以把兩者SID放入v$session，發現LOGONTIME字段FINALBLOCKING_SESSION比SID要早
BLOCKINGSESSION:Session identifier of the blocking session. This column is valid only if BLOCKINGSESSIONSTATUS has the value VALID. FINALBLOCKINGSESSION:Session identifier of the blocking session. This column is valid only if FINALBLOCKINGSESSIONSTATUS has the value VALID.
如果遇到RAC環境，一定要用gv$來查，并且執行alter system kill session 'sid,serial#'要到RAC對應的實例上去執行
把上面被堵塞會話的sid代入如下語句，可以發現鎖住的對象和對象的哪一行(如果sid是堵塞源的會話，則 rowwaitobj#=-1，表示鎖持有者，就是死鎖源了 )
select s.sid,s.username,d.owner,d.object_name,s.row_wait_obj#,s.row_wait_row#,s.row_wait_file#,s.row_wait_block# from v$session s,dba_objects d where s.row_wait_obj#=d.object_id and s.sid in(XX,XX);查詢鎖住的DDL對象
sql
select d.session_id,s.SERIAL#,d.name from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid;查詢超過兩個小時的不活動會話
select s.sid,s.serial#,p.spid,s.LOGON_TIME,s.LAST_CALL_ET,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.terminal,s.action,s.PROCESS "客戶端機器進程號",s.osuser from v$session s,v$process p where s.paddr=p.addr and s.sid in (select sid from v$session where machine<>&DB服務器名稱 and status='INACTIVE' and sql_id is null and LAST_CALL_ET>7200);查詢堵塞別的會話超過30分鐘且自身是不活動的會話
select count(ss.SID),ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER from v$session ss group by ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER having count(ss.SID)>10;查詢當前正在執行的sql
SELECT s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_text FROM v$process,v$session s,v$sql WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value and s.STATUS='ACTIVE';查詢正在執行的SCHEDULER_JOB sql
select owner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process where session_id=sid and paddr=addr;查詢正在執行的dbms_job
select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process where a.sid=b.sid and paddr=addr;查詢一個會話session、process平均消耗多少PGA內存，查看下面avgusedM值
/* Formatted on 2019/11/6 10:06:48 (QP5 v5.326) */
SELECT ROUND (SUM (pga_used_mem) / 1024 / 1024, 0)
total_used_M,
ROUND (SUM (pga_used_mem) / COUNT (1) / 1024 / 1024, 0)
avg_used_M,
ROUND (SUM (pga_alloc_mem) / 1024 / 1024, 0)
total_alloc_M,
ROUND (SUM (pga_alloc_mem) / COUNT (1) / 1024 / 1024, 0)
avg_alloc_M
FROM v$process;TOP 10 執行次數排序
/* Formatted on 2019/11/6 10:07:07 (QP5 v5.326) */
SELECT *
FROM ( SELECT executions,
username,
PARSING_USER_ID,
sql_id,
sql_text
FROM v$sql, dba_users
WHERE user_id=PARSING_USER_ID
ORDER BY executions DESC)
WHERE ROWNUM <=5;TOP 10 物理讀排序（消耗IO排序，即最差性能SQL、低效SQL排序）
/* Formatted on 2019/11/6 10:06:06 (QP5 v5.326) */
SELECT *
FROM ( SELECT DISK_READS,
username,
PARSING_USER_ID,
sql_id,
ELAPSED_TIME / 1000000,
sql_text
FROM v$sql, dba_users
WHERE user_id=PARSING_USER_ID
ORDER BY DISK_READS DESC)
WHERE ROWNUM <=5;注意：不要使用DISK_READS/ EXECUTIONS來排序，因為任何一條語句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最后一次執行時間距離現在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數據塊，去數據塊里找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool。
TOP 10 邏輯讀排序（消耗內存排序）
select * from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc) where rownum <=5;注意：不要使用BUFFER_GETS/ EXECUTIONS來排序，因為任何一條語句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最后一次執行時間距離現在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數據塊，去數據塊里找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool）
TOP 10 CPU排序(單位秒=cpu_time/1000000)
select * from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text from v$sql,dba_users where user_id=PARSING_USER_ID order by CPU_TIME/1000000 desc) where rownum <=5;注意：不要使用CPU_TIME/ EXECUTIONS來排序，因為任何一條語句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最后一次執行時間距離現在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數據塊，去數據塊里找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool。
查詢等待事件
select event,sum(decode(wait_time,0,0,1)) "之前等待次數", sum(decode(wait_time,0,1,0)) "正在等待次數",count(*) from v$session_wait group by event order by 4 desc;查詢當前等待事件對應的對象
select distinct wait_class#,wait_class from v$session_wait_class order by 1;以上sql發現wait_class#=6的是空閑等待
select * from(select sid,event,p1text,p1,p2text,p2,p3text,p3,WAIT_TIME,SECONDS_IN_WAIT,wait_class# from v$session_wait where wait_class# <> 6 order by wait_time desc)where rownum <=10;能查出等待的對象是否來自數據文件（如果以上查到p1text是file#或file number）
select * from(select owner,segment_name,segment_type,block_id,bytes from dba_extents where file_id=p1 and block_id<p2 order="" by="" block_id="" desc)where rownum<2把上面第二個sql結果的p1、p2值代入上述sql的fileid、blockid
通過AWR的top sql或v$sql.sql_text查看是否有該對象的語句，檢查該語句的執行計劃就可以查出問題所在。
查詢當前正在消耗臨時空間的sql語句
方法一：
/* Formatted on 2019/11/6 10:08:27 (QP5 v5.326) */
SELECT DISTINCT
se.username,
se.sid,
su.blocks * TO_NUMBER (RTRIM (p.VALUE)) / 1024 / 1024 AS space_G,
su.tablespace,
sql_text
FROM V$TEMPSEG_USAGE su,
v$parameter p,
v$session se,
v$sql s
WHERE p.name='db_block_size'
AND su.session_addr=se.saddr
AND su.sqlhash=s.hash_value
AND su.sqladdr=s.address
AND se.STATUS='ACTIVE'方法二：
/* Formatted on 2019/11/6 10:09:13 (QP5 v5.326) */
SELECT v$sql.sql_id,
v$sql.sql_fulltext,
swa.TEMPSEG_SIZE / 1024 / 1024 TEMPSEG_M,
swa.*
FROM v$sql_workarea_active swa, v$sql
WHERE swa.sql_id=v$sql.sql_id AND swa.NUMBER_PASSES > 0查詢因PGA不足而使用臨時表空間的最頻繁的10條SQL語句
/* Formatted on 2019/11/6 10:07:40 (QP5 v5.326) */
SELECT *
FROM ( SELECT OPERATION_TYPE,
ESTIMATED_OPTIMAL_SIZE,
ESTIMATED_ONEPASS_SIZE,
SUM (OPTIMAL_EXECUTIONS) optimal_cnt,
SUM (ONEPASS_EXECUTIONS) AS onepass_cnt,
SUM (MULTIPASSES_EXECUTIONS) AS mpass_cnt,
s.sql_text
FROM V$SQL_WORKAREA swa, v$sql s
WHERE swa.sql_id=s.sql_id
GROUP BY OPERATION_TYPE,
ESTIMATED_OPTIMAL_SIZE,
ESTIMATED_ONEPASS_SIZE,
sql_text
HAVING SUM (ONEPASS_EXECUTIONS + MULTIPASSES_EXECUTIONS) > 0
ORDER BY SUM (ONEPASS_EXECUTIONS) DESC)
WHERE ROWNUM < 10查詢正在消耗PGA的SQL
select s.sql_text, sw.EXPECTED_SIZE, sw.ACTUAL_MEM_USED,sw.NUMBER_PASSES, sw.TEMPSEG_SIZE from v$sql_workarea_active sw, v$sql s where sw.sql_id=s.sql_id;查詢需要使用綁定變量的sql，10G以后推薦第二種
注意：任何一條執行過的語句不管執行了幾次在V$SQL中都只有一條記錄，V$SQL中會記錄執行了幾次。兩條一模一樣的語句但是在不同的schema下執行的兩種結果，如select * from t1.test在sye、system下執行則V$SQL只有一條記錄(誰先執行則PARSINGSCHEMANAME顯示誰)。如在sys和system都執行select * from test則V$SQL中有兩條記錄，兩條記錄的CHILDNUMBER和PARSINGSCHEMA_NAME不一樣。
同一個用戶下執行一樣的語句如果大小寫不一樣或加了hint的話則會出現多個V$SQL記錄，說明V$SQL對應的sql語句必須一模一樣，如果alter system flush sharedpool（主站慎用）后再執行一樣的語句，發現語句在V$SQL中的SQLID和HASHVALUE與之前的一樣，說明SQLID和HASHVALUE應該是oracle自己的一套算法來的，只是根據sql語句內容來進行轉換，sql語句不變則SQLID和HASH_VALUE也不變。
第一種
select * from ( select count(*),sql_id, substr(sql_text,1,40) from v$sql group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10;第二種
count(1)>10表示類語句運行了10次以上 select sql_id, FORCE_MATCHING_SIGNATURE, sql_text from v$SQL where FORCE_MATCHING_SIGNATURE in (select /*+ unnest */ FORCE_MATCHING_SIGNATURE from v$sql where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) > 10)查看數據文件可用百分比
dbafreespace并不會包含所有fileid，如果該數據文件滿了，則 dbafreespace.fileid沒有該數據文件，所以以下sql中 a.fileid=b.fileid的條件過濾后是不會有所有file_id的
/* Formatted on 2019/11/6 10:09:31 (QP5 v5.326) */
SELECT b.file_id,
b.tablespace_name,
b.file_name,
b.AUTOEXTENSIBLE,
ROUND (b.MAXBYTES / 1024 / 1024 / 1024, 2) || 'G'
"文件最大可用總容量",
ROUND (b.bytes / 1024 / 1024 / 1024, 2) || 'G'
"文件總容量",
ROUND ((b.bytes - SUM (NVL (a.bytes, 0))) / 1024 / 1024 / 1024, 2)
|| 'G'
"文件已用容量",
ROUND (SUM (NVL (a.bytes, 0)) / 1024 / 1024 / 1024, 2) || 'G'
"文件可用容量",
ROUND (SUM (NVL (a.bytes, 0)) / (b.bytes), 2) * 100 || '%'
"文件可用百分比"
FROM dba_free_space a, dba_data_files b
WHERE a.file_id=b.file_id
GROUP BY b.tablespace_name,
b.file_name,
b.file_id,
b.bytes,
b.AUTOEXTENSIBLE,
b.MAXBYTES
ORDER BY b.tablespace_name;--如下為標準版
/* Formatted on 2019/11/6 10:10:23 (QP5 v5.326) */
SELECT b.file_id,
b.tablespace_name,
b.file_name,
b.AUTOEXTENSIBLE,
ROUND (b.MAXBYTES / 1024 / 1024 / 1024, 2) || 'G'
"文件最大可用總容量",
ROUND (b.bytes / 1024 / 1024 / 1024, 2) || 'G'
"文件當前總容量",
ROUND ((b.bytes - SUM (NVL (a.bytes, 0))) / 1024 / 1024 / 1024, 2)
|| 'G'
"文件當前已用容量",
ROUND (
( DECODE (AUTOEXTENSIBLE, 'NO', b.BYTES, b.MAXBYTES)
+ SUM (NVL (a.bytes, 0))
- b.bytes)
/ 1024
/ 1024
/ 1024,
2)
|| 'G'
"文件可用容量",
ROUND (
( DECODE (AUTOEXTENSIBLE, 'NO', b.BYTES, b.MAXBYTES)
+ SUM (NVL (a.bytes, 0))
- b.bytes)
/ (DECODE (AUTOEXTENSIBLE, 'NO', b.BYTES, b.MAXBYTES)),
2)
* 100
|| '%'
"文件可用百分比"
FROM dba_free_space a, dba_data_files b
WHERE a.file_id=b.file_id
GROUP BY b.tablespace_name,
b.file_name,
b.file_id,
b.bytes,
b.AUTOEXTENSIBLE,
b.MAXBYTES
ORDER BY DECODE (AUTOEXTENSIBLE, 'NO', b.BYTES, b.MAXBYTES)
+ SUM (NVL (a.bytes, 0))
- b.bytes;查看數據庫文件的實際總量，單位G
SELECT /*+ USE_MERGE(C,B) */ a.datafile_size + b.tempfile_size - c.free_size
FROM (SELECT SUM(bytes / 1024 / 1024 / 1024) datafile_size
FROM dba_data_files) a,
(SELECT SUM(bytes / 1024 / 1024 / 1024) tempfile_size
FROM dba_temp_files) b,
(SELECT SUM(bytes / 1024 / 1024 / 1024) free_size
FROM dba_free_space) c查看表空間可用百分比（ dbafreespace不會包含所有tablespace，如果一個表空間的數據文件都滿了，則這個表空間不會出現在dbafreespace中 ）
select b.tablespace_name,
a.maxsize max_M,
a.total total_M,
b.free free_M,
round((b.free / a.total) * 100) "% Free"
from (select tablespace_name,
sum(bytes / (1024 * 1024)) total,
sum(MAXBYTES / (1024 * 1024)) maxsize
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free
from dba_free_space
group by tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
order by "% Free";--如下為標準版
select b.tablespace_name,
a.maxsize max_M,
a.total total_M,
b.free free_M,
round(((a.maxsize + b.free - a.total) / a.maxsize) * 100) "% Free"
from (select tablespace_name,
sum(bytes / (1024 * 1024)) total,
sum((decode(AUTOEXTENSIBLE, 'NO', BYTES, MAXBYTES)) /
(1024 * 1024)) maxsize
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free
from dba_free_space
group by tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
order by "% Free";查看臨時表空間使用率
方法一
/* Formatted on 2019/11/6 10:13:28 (QP5 v5.326) */
SELECT temp_used.tablespace_name,
ROUND (total),
used,
ROUND (total - used) AS "Free",
ROUND (NVL (total - used, 0) * 100 / total, 1) "Free percent"
FROM ( SELECT tablespace_name, SUM (bytes_used) / 1024 / 1024 used
FROM GV$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
( SELECT tablespace_name,
SUM (DECODE (autoextensible, 'YES', MAXBYTES, bytes))
/ 1024
/ 1024
total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name=temp_total.tablespace_name;方法二
/* Formatted on 2019/11/6 10:13:40 (QP5 v5.326) */
SELECT a.tablespace_name,
ROUND (a.BYTES / 1024 / 1024)
total_M,
ROUND (a.bytes / 1024 / 1024 - NVL (b.bytes / 1024 / 1024, 0))
free_M,
ROUND (b.bytes / 1024 / 1024)
used,
ROUND (b.using / 1024 / 1024)
using
FROM ( SELECT tablespace_name,
SUM (DECODE (autoextensible, 'YES', MAXBYTES, bytes))
bytes
FROM dba_temp_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name,
SUM (bytes_cached) bytes,
SUM (bytes_used) using
FROM v$temp_extent_pool
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name(+);真實使用率：
SELECT D.tablespace_name,
SPACE "SUM_SPACE(M)",
blocks "SUM_BLOCKS",
used_space "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - used_space "FREE_SPACE(M)"
FROM (SELECT tablespace_name,
Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name=F.tablespace(+)
AND D.tablespace_name in ('TEMP', 'TEMP1')查詢undo表空間使用情況
select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status;查詢使用undo比較多的SQL
select *from ( select maxqueryid, round(sum(undoblks )*8/1024) consumed_size_MB from v$undostat group by maxqueryid order by consumed_size_MB desc ) where rownum<10;估計undo需要多大
/* Formatted on 2019/11/6 10:14:09 (QP5 v5.326) */
SELECT (UR * (UPS * DBS/1024/1024)) AS "Mb"
FROM (SELECT MAX (tuned_undoretention) AS UR FROM v$undostat),
(SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS
FROM v$undostat
WHERE undoblks=(SELECT MAX (undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name=(SELECT UPPER (VALUE)
FROM v$parameter
WHERE name='undo_tablespace'));產生undo的當前活動會話是哪些
方法一
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name, a.value, d.used_urec, d.used_ublk FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d WHERE a.statistic#=b.statistic# AND a.inst_id=c.inst_id AND a.sid=c.sid AND c.inst_id=d.inst_id AND c.saddr=d.ses_addr AND b.name='undo change vector size' AND a.value>0 ORDER BY a.value DESC;方法二
select s.sid,s.serial#,s.sql_id,v.usn,r.status, v.rssize/1024/1024 mbfrom dba_rollback_segs r, v$rollstat v,v$transaction t,v$session sWhere r.segment_id=v.usn and v.usn=t.xidusn and t.addr=s.taddrorder by 6 desc;查看ASM磁盤組使用率
select name,round(total_mb/1024) "總容量",round(free_mb/1024) "空閑空間",round((free_mb/total_mb)*100) "可用空間比例" from gv$asm_diskgroup;統計每個用戶使用表空間率
SELECT c.owner "用戶",
a.tablespace_name "表空間名",
total / 1024 / 1024 "表空間大小M",
free / 1024 / 1024 "表空間剩余大小M",
(total - free) / 1024 / 1024 "表空間使用大小M",
Round((total - free) / total, 4) * 100 "表空間總計使用率 %",
c.schemas_use / 1024 / 1024 "用戶使用表空間大小M",
round((schemas_use) / total, 4) * 100 "用戶使用表空間率 %"
FROM (SELECT tablespace_name, Sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name, Sum(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b,
(Select owner, Tablespace_Name, Sum(bytes) schemas_use
From Dba_Segments
Group By owner, Tablespace_Name) c
WHERE a.tablespace_name=b.tablespace_name
and a.tablespace_name=c.Tablespace_Name
order by "用戶", "表空間名";查看閃回區\快速恢復區空間使用率
select sum(percent_space_used)||'%' "已使用空間比例" from V$RECOVERY_AREA_USAGE 或 select round(100*(a.space_used/space_limit),2)||'%' "已使用空間比例",a.* from v$recovery_file_dest a;查看僵死進程，分兩種（一種是會話不在的，另一種是會話標記為killed的但是會話還在的）
alter system kill session一執行則session即標記為KILLED，但是如果會話產生的數據量大則這個kill可能會比較久，在這個過程中session標記為KILLED但是這個會話還在V$session中，則V$session.paddr還在，所以可以匹配到V$process.addr，所以process進程還在；當kill過程執行完畢，則這個會話即不在V$session中
會話不在的
select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18);會話還在的，但是會話標記為killed
select * from v$process where addr in (select paddr from v$session where status='KILLED');再根據上述結果中的SPID通過如下命令可以查看到process的啟動時間
ps auxw|head -1;ps auxw|grep SPID32. 查看行遷移或行鏈接的表
select * From dba_tables where nvl(chain_cnt,0)<>0chain_cnt ：Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table.
*數據緩沖區命中率（百分比小于90就要加大db_cache_size） *
SELECT a.VALUE + b.VALUE logical_reads,
c.VALUE phys_reads,
round(100 * (1 - c.value / (a.value + b.value)), 2) || '%' hit_ratio
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads';方法二
SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads, round(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';共享池命中率（百分比小于90就要加大sharedpoolsize）
以下兩者可以根據個人理解運用
select sum(pinhits)/sum(pins)*100 from v$librarycache;
select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;查詢歸檔日志切換頻率
select sequence#,
to_char(first_time, 'yyyymmdd_hh24:mi:ss') firsttime,
round((first_time - lag(first_time) over(order by first_time)) * 24 * 60,
1) minutes
from v$log_history
where first_time > sysdate - 3
order by first_time, minutes;或
SELECT TO_CHAR(first_time, 'MM/DD') DAY,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) H23,
COUNT(*) TOTAL
FROM (SELECT ROWNUM RN, FIRST_TIME
FROM V$LOG_HISTORY
WHERE first_time > sysdate - 18
and FIRST_TIME > ADD_MONTHS(SYSDATE, -1)
ORDER BY FIRST_TIME)
GROUP BY TO_CHAR(first_time, 'MM/DD')
ORDER BY MIN(RN);查詢lgwr進程寫日志時每執行一次lgwr需要多少秒，在state是waiting的情況下，某個等待編號seq#下，secondsinwait達多少秒，就是lgwr進程寫一次IO需要多少秒
select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'查詢沒有索引的表
Select table_name from user_tables where table_name not in (select table_name from user_indexes) Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)查詢一個AWR周期內的平均session數、OS平均負載、平均db time、平均每秒多少事務
select to_char(max(BEGIN_TIME), 'yyyy-mm-dd hh24:mi') ||
to_char(max(end_time), '--hh24:mi') time,
snap_id,
trunc(sum(case metric_name
when 'Session Count' then
average
end),
2) sessions,
trunc(sum(case metric_name
when 'Current OS Load' then
average
end),
2) OS_LOAD,
(trunc(sum(case metric_name
when 'Database Time Per Sec' then
average
end),
2) / 100) *
(ceil((max(end_time) - max(BEGIN_TIME)) * 24 * 60 * 60)) Database_Time_second,
trunc(sum(case metric_name
when 'User Transaction Per Sec' then
average
end),
2) User_Transaction_Per_Sec
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;--Database Time Per Sec對應值的單位是百分一秒/每秒 --(/100)(ceil((max(end_time)-max(BEGIN_TIME))246060))是代表每個snap周期內的總秒數，oracle 兩個時間相減默認的是天數,2460*60 為相差的秒數 --這個SQL查到的DB TIME比較準確，和awr上面的db time比較一致
查詢產生熱塊較多的對象
x$bh .tch(Touch)表示訪問次數越高，熱點快競爭問題就存在
SELECT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
(SELECT *
FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) b
WHERE e.relative_fno=b.dbarfil
AND e.block_id <=b.dbablk
AND e.block_id + e.blocks > b.dbablk;手工創建快照的語句
exec dbms_workload_repository.create_snapshot; AWR設置每隔30分鐘收集一次報告，保留14天的報告
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>14*24*60, interval=>30); select * from dba_hist_wr_control;AWR基線查看和創建
select * from dba_hist_baseline; exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id=>7550,end_snap_id=>7660,baseline_name=>'am_baseline');導出AWR報告的SQL語句
select * from dba_hist_snapshot select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid)) select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));導出最新ADDM的報告（需要sys用戶）
select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks where task_id=( select max(t.task_id) from dba_advisor_tasks t, dba_advisor_log l where t.task_id=l.task_id and t.advisor_name='ADDM' and l.status='COMPLETED' ); select task_id,task_name,description from dba_advisor_tasks order by 1 desc select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks where task_id=XX 查詢某個SQL的執行計劃
select * from table(dbms_xplan.display_cursor('sql_id',0,' advanced '));上面的0表示v$sql.childnumber為0，如果一個sqlid在v$sql中有多行說明有多個childnumber，要看哪兒childnumber的執行計劃，就寫哪個的值，比如要看child_number為2的執行計劃，就把上面sql的0改為2 。
官方文檔對display_cursor這個函數的說明里面沒有advanced這個參數值，只有BASIC、TYPICAL、ALL這幾個，不過實踐中發現advanced這個參數值顯示的內容比這幾個參數值顯示的都多。
select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));創建xplan包，再執行
SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN; SQL> grant execute on sys.xplan to public;查詢Rman的配置信息
SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;查詢Rman備份集詳細信息（未過期的，過期并已刪除的查不到）
SELECT B.RECID BackupSet_ID,
A.SET_STAMP,
DECODE(B.INCREMENTAL_LEVEL,
'',
DECODE(BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1,
'Incr-1級',
0,
'Incr-0級',
B.INCREMENTAL_LEVEL) "Type LV",
B.CONTROLFILE_INCLUDED "包含CTL",
DECODE(A.STATUS,
'A',
'AVAILABLE',
'D',
'DELETED',
'X',
'EXPIRED',
'ERROR') "STATUS",
A.DEVICE_TYPE "Device Type",
A.START_TIME "Start Time",
A.COMPLETION_TIME "Completion Time",
A.ELAPSED_SECONDS "Elapsed Seconds",
A.BYTES / 1024 / 1024 / 1024 "Size(G)",
A.COMPRESSED,
A.TAG "Tag",
A.HANDLE "Path"
FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
WHERE A.SET_STAMP=B.SET_STAMP
AND A.DELETED='NO'
ORDER BY A.COMPLETION_TIME DESC;查詢Rman備份進度
SELECT SID, SERIAL#, opname,ROUND(SOFAR/TOTALWORK*100)||'%' "%_COMPLETE", TRUNC(elapsed_seconds/60) || ':' || MOD(elapsed_seconds,60) elapsed, TRUNC(time_remaining/60) || ':' || MOD(time_remaining,60) remaining, CONTEXT,target,SOFAR, TOTALWORK FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK !=0 AND SOFAR <> TOTALWORK; 查詢執行過全表掃描的sql語句的SQLID和sqlfulltext
select s.sid,s.serial#,s.inst_id,s.sql_id,s.username,s.target,s.ELAPSED_SECONDS,s.START_TIME,s.LAST_UPDATE_TIME,v.sql_fulltext from gv$session_longops s,gv$sql v where s.OPNAME='Table Scan' and s.SQL_PLAN_OPERATION='TABLE ACCESS' and s.SQL_PLAN_OPTIONS='FULL' and s.sql_id=v.sql_id order by s.LAST_UPDATE_TIME desc 查詢死事務需要多長的回滾時間
X$KTUXE：[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)X$KTUXE表的一個重要功能是，可以獲得無法通過v$transaction來觀察的死事務信息，當一個數據庫發生異常中斷，或者進行延遲事務恢復時，數據庫啟動后，無法通過V$TRANSACTION來觀察事務信息，但是X$KTUXE可以幫助我們獲得這些信息。該表中的KTUXECFL代表了事務的Flag標記，通過這個標記可以找到那些Dead事務：
SQL> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL; KTUXECFL COUNT(*) ------------------------ ---------- DEAD 1 NONE 2393 KTUXESIZ用來記錄事務使用的回滾段塊數，可以通過觀察這個字段來評估恢復進度,例如如下事務回滾經過測算需要大約3小時：
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL='DEAD'; ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ ---------------- ---------- ---------- ---------- ---------- FFFFFFFF7D07B91C 10 39 2567412 1086075
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL='DEAD'; ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ ---------------- ---------- ---------- ---------- ---------- FFFFFFFF7D07B91C 10 39 2567412 1086067
SQL> declare l_start number; l_end number; begin select ktuxesiz into l_start from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39; dbms_lock.sleep(60); select ktuxesiz into l_end from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39; dbms_output.put_line('time_H:'|| round(l_end/(l_start -l_end)/60,2)); end; /
time_H:3 把XXX用戶下面的某些YYY表賦權給user,XXX\YYY要大寫
set serveroutput on --XXX要大寫 declare tablename varchar2(200); begin for x IN (SELECT * FROM dba_tables where owner='XXX' and table_name like '%YYY%') loop tablename:=x.table_name; dbms_output.put_line('GRANT SELECT ON XXX.'||tablename||' to user'); EXECUTE IMMEDIATE 'GRANT SELECT ON XXX.'||tablename||' TO user'; end loop; end; Oracle查出一個用戶具有的所有系統權限和對象權限
系統權限（和用戶自己查詢select * from session_privs的結果一致）
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='用戶名' UNION ALL SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='用戶名');對象權限（和用戶自己查詢select * FROM TABLE_PRIVILEGES where GRANTEE='當前用戶'的結果一致）
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='用戶名' UNION ALL SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='用戶名');查詢某個用戶擁有的角色
select * from dba_role_privs where GRANTEE='用戶名'; 查詢擁有DBA角色權限的用戶
select * from dba_role_privs where GRANTED_ROLE='DBA'; 查詢某個角色擁有的系統權限
select * from ROLE_SYS_PRIVS where role='角色名' 清除某個SQL的執行計劃
Exec DBMS_SHARED_POOL.PURGE('v$sqlarea.ADDRESS,v$sqlarea.HASH_VALUE','c') 查詢密碼是否有過期限制，默認是180天，一般修改為unlimited
select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD%'; ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED 查詢和修改隱含參數（必須在sysdba權限下操作）
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx=b.indx and a.ksppinm like '%_small_table_threshold%' alter system set "_small_table_threshold"=value scope=both sid='*'; 不加sid則說明在默認在RAC的所有實例中修改 需要注意的是一定要加上雙引號, 另外引號內不能有空格, 只能包含參數的名字
評估PGA該設置多少
select PGA_TARGET_FOR_ESTIMATE from (select * from V$PGA_TARGET_ADVICE where ESTD_OVERALLOC_COUNT=0 order by 1) where rownum=1; 評估SGA該設置多少
select SGA_SIZE from (select * from V$SGA_TARGET_ADVICE where ESTD_DB_TIME_FACTOR=1 order by 1) where rownum=1; 查看shared pool還剩多少
select * from v$sgastat where name='free memory' and pool='shared pool'; 統計所有表的容量大小(含分區字段、LOB字段) 一般先執行select distinct SEGMENTTYPE from dbasegments where owner<>'SYS' and tablespacename<>'SYSAUX'查看到所有的segmenttype
如下SQL就足夠了
SELECT owner, table_name, TRUNC(sum(bytes) / 1024 / 1024) AS "SIZE(MB)"
FROM (SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type='TABLE'
UNION ALL
SELECT s.segment_name table_name, pt.owner, s.bytes
FROM dba_segments s, dba_part_tables pt
WHERE s.segment_name=pt.table_name
AND s.owner=pt.owner
AND s.segment_type='TABLE PARTITION'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name=i.index_name
AND s.owner=i.owner
AND s.segment_type='INDEX'
UNION ALL
SELECT pi.table_name, pi.owner, s.bytes
FROM dba_part_indexes pi, dba_segments s
WHERE s.segment_name=pi.index_name
AND s.owner=pi.owner
AND s.segment_type='INDEX PARTITION'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name=l.segment_name
AND s.owner=l.owner
AND s.segment_type='LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name=l.index_name
AND s.owner=l.owner
AND s.segment_type='LOBINDEX'
union all
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name=l.segment_name
AND s.owner=l.owner
AND s.segment_type='LOB PARTITION')
GROUP BY owner, table_name
HAVING SUM(bytes) / 1024 / 1024 > 10
ORDER BY SUM(bytes) desc查看當前會話的SID
select * from V$MYSTAT where rownum<2 查詢某個SID的某個統計信息，比如consistent gets一致性讀
select A.SID,A.STATISTIC#,A.VALUE SID_VALUE,B.NAME,B.VALUE ALL_SID_VALUE from V$SESSTAT A ,V$SYSSTAT B where A.STATISTIC#=B.STATISTIC# and A.SID=1187 and B.NAME='consistent gets' V$SYSSTAT統計整個DB的統計信息，V$SYSSTAT已經取代了V$STATNAME，并且多了VALUE這一列 V$SESSTAT統計每個用戶的統計信息 查詢某個SID的某個等待事件的信息，比如log file sync
select A.SID,A.EVENT,C.NAME,C.PARAMETER1,C.PARAMETER2,C.PARAMETER3, A.TIME_WAITED SID_TIMEWAITED,B.TIME_WAITED ALL_SID_TIMEWAITED,A.TOTAL_WAITS SID_TOTALWAITS,B.TOTAL_WAITS ALL_SID_TOTALWAITS from V$SESSION_EVENT A ,V$SYSTEM_EVENT B,V$EVENT_NAME C where A.EVENT=B.EVENT and A.EVENT=C.NAME and A.SID=1 and C.NAME='log file sync'V$SESSION_EVENT描述每個用戶的等待事件信息 V$SYSTEM_EVENT描述整個DB等待事件信息 V$EVENT_NAME描述等待事件信本身的信息(比如 V$ACTIVE_SESSION_HISTORY的P1TEXT、P2TEXT、P2TEXT匹配 V$EVENT_NAME的PARAMETER1、PARAMETER2、PARAMETER3)
RAC跨節點殺會話
alter system kill session 'SID,serial#,@1' --殺掉1節點的進程 alter system kill session 'SID,serial#,@2' --殺掉2節點的進程 Truncate 分區的SQL
ALTER TABLE table_name TRUNCATE PARTITION p1 DROP STORAGE UPDATE GLOBAL INDEXES; Drop分區的SQL
ALTER TABLE table_name DROP PARTITION p1 UPDATE GLOBAL INDEXES; DATAGUARD主備延遲多少時間的查詢方法 備 庫
select value from v$dataguard_stats where name='apply lag'或 備庫
sqlplus>select ceil((sysdate-next_time)*24*60) "M" from v$archived_log where applied='YES' AND SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE applied='YES'); 查看某個包或存儲過程是否正在被調用,如果如下有結果，則此時不能編譯，否則會鎖住
select * from V$DB_OBJECT_CACHE where pin>0 and name=upper('XX')查詢數據庫打補丁的記錄
select * from dba_registry_history;查詢某表的索引字段的distinct行數和CLUSTERING_FACTOR信息
select a.table_name,
a.index_name,
b.COLUMN_NAME,
a.blevel,
a.distinct_keys,
A.CLUSTERING_FACTOR,
A.NUM_ROWS,
trunc((a.distinct_keys / A.NUM_ROWS), 2) * 100 || '%' "distinct%",
trunc((a.CLUSTERING_FACTOR / A.NUM_ROWS), 2) * 100 || '%' "CLUSTERING_FACTOR%"
from DBA_IND_STATISTICS a, DBA_IND_COLUMNS b
where a.table_name='XX'
and a.INDEX_NAME=b.index_name
order by 5 desc查詢某表的所有字段的distinct行數
select a.table_name,
b.num_rows,
a.column_name,
a.data_type,
a.data_length,
a.num_distinct,
trunc((a.num_distinct / b.num_rows), 2) * 100 || '%'
from dba_TAB_COLS a, dba_tables b
where a.table_name='XX'
and a.table_name=b.table_name
order by 6 desc查詢5G以上空閑空間可以進行收縮的數據文件
者介紹
梁敬彬，福富研究院副理事長、公司唯一四星級內訓師，國內一線知名數據庫專家，在數據庫優化和培訓領域有著豐富的經驗。多次應邀擔任國內外數據庫大會的演講嘉賓，在業界有著廣泛的影響力。著有多本暢銷書籍，代表作有《收獲，不止Oracle》。文末將有梁老師的新書《收獲，不止SQL優化》大彩蛋哦~
SQL優化是一個復雜的工程，首先要講究從整體到局部。今天我們首先學習關于數據庫整體優化都有哪些性能工具，接著分析這些工具的特點，并結合案例進行探索，最后再進行總結和思考。
總體學習思路如下圖所示：
都有哪些性能工具
這里首先要分成兩部分：一種是不同調優場景的分析，可分為單純場景的優化和復雜場景的優化；而另一種是基于這些場景的工具應用，就是針對單純場景的優化手段和復雜場景的優化手段。
1、不同調優場景分析
我們繼續探討，單純是有多單純呢？哦，其實可以理解為無菌真空實驗室里的實驗。比如一條SQL很慢，原因是未走高效的索引查詢而走全表掃描，加個索引就快了，執行速度從10s變成了0.1s；或者一條SQL執行速度被優化到1s左右，邏輯讀控制在50個左右，應該就已經OK。這就是單純的環境，我們差不多無須再考慮優化了。
那啥是復雜呢？那就是，剛才那個語句加了索引后，本應該從10s變成0.1s，結果還是10s，甚至變成30s了，這是咋回事呢？原來，現在系統是整體出問題了，數據庫主機資源耗盡，啥語句都跑不快的。
還有那個邏輯讀在50左右的SQL，如果一天執行幾百幾千萬次，這要是能將邏輯讀降低一點，得省多少的邏輯讀啊。原來復雜環境真的很復雜，要考慮SQL本身沒問題而是被環境影響，還要考慮SQL的執行頻率，判斷其調優價值與調優空間，這些在單純的環境里，是不用考慮的。
2、不同場景對應工具
接下來，我們說說這兩種場景對應的工具的使用。關于局部分析調優工具，這個其實就是在說SQL的執行計劃了，這是SQL優化最重要的手段之一，通過分析執行計劃，我們可以知道SQL語句的訪問路徑，知道它慢在哪里，從而進行SQL優化。由于在隨后的章節中我們會詳細介紹執行計劃相關知識，這里就不再細述了。
關于整體的調優工具，這里我們先撇開主機、網絡、存儲等層面的因素，暫時從數據庫的整體層面入手。主要工具有AWR、ASH、ADDM、AWRDD這四個工具。其中AWR是關注數據庫的整體性能的報告；ASH是數據庫中的等待事件與哪些SQL具體對應的報告；ADDM是Oracle給出的一些建議；而AWRDD是Oracle針對不同時段的性能的一個比對報告，比如今天早上9點系統很慢，而昨天這個時候很正常，很多人就想知道今天早上9點和昨天早上9點有什么不同，于是就有了這個報告。
整體分析調優是必需的，那么我們對此的學習也有規律可循。首先是獲取系統整體信息的手段，一般通過報告和日志獲取。好比破案一樣，這就是收集證據的階段。接下來要找到蛛絲馬跡，那就是如何發現問題。在本書中就是需要關注提取到的這些報告的哪些要點、哪些關鍵字，具體流程圖如下：
整體性能工具的要點
現代人對健康都比較重視，每年都會進行健康體檢。其實數據庫性能工具的應用（報告獲取和關注要點）和體檢是非常類似的。
1. 報告的獲取
Oracle性能報告分成AWR、ASH、ADDM、AWRDD和AWRSQRPT這5個類型。
什么？這么多，好復雜啊，記也記不住，我不想聽不想聽！
別急，你只要去醫院體檢過，你就能聽懂。
Really?
我們去醫院體檢，最終會得到一份體檢報告，往往能看到很多總體性指標，這些指標會判斷你是否健康。沒毛病最好，萬一有毛病，報告里要進一步判斷是什么毛病，是高血壓，還是骨質增生，還是胃有毛病……這就是現實中的體檢報告。
而Oracle提供的一種性能收集和分析工具，它能提供一個時間段內整個系統資源使用情況的報告，這個報告里有很多總體性指標來判斷系統是否健康。沒毛病最好，萬一有毛病，問題出在什么模塊，是日志切換過于頻繁，還是硬解析過大，還是某些SQL相關等待事件在耗資源……這就是AWR報告。這樣看來，體檢報告和AWR報告非常類似。
假設體檢報告說你有胃病，很可能只告訴你胃有問題，卻無法告訴你具體啥毛病，因為你手上的體檢報告不會詳細到擁有你胃部所有相關指標。你要得到這些指標需要做進一步信息收集，那就是胃鏡。同樣假設你的數據庫是SQL相關等待事件問題，AWR報告很可能只告訴你有這個問題而無法告訴你是哪些SQL引發的。你要得到這些指標，想了解具體某些SQL和相關等待事件的對應需要做進一步的信息收集，那就是ASH報告。看來對比胃鏡和ASH報告，二者也非常類似。
剛才說的胃病，或許是醫生告訴你的，因為上面有很多指標你無法讀懂，這時如果你能拿到一張醫生的病歷卡記錄，這里沒有指標，只有白底黑字用文字描述的病情，告訴你要如何治療，那你一定會看得很明白。同樣假設，如果將含各種晦澀的指標的數據庫體檢報告用一些白底黑字的文字代替，用文字直接說明數據庫遇到了什么問題，告訴你該如何去優化，那新手一定會看得很明白，這就是ADDM報告。看來病歷卡記錄和ADDM報告，二者也非常類似。
假如你在一年前也做過體檢，并將報告帶到了醫院，負責任的醫生就一定會讓你將舊的體檢報告也提供給他。他會認真地比對兩張報告，查看他關注的健康指標是否有異常波動，這些波動對醫生很有參考意義，往往預示著病情的發展趨勢。好了，別緊張，這只是比喻。
假設你有系統新舊兩個時段的兩份AWR報告，負責任的DBA一定會讓你將舊的AWR報告也提供給他。他會認真地比對兩份報告，查看他關注的數據庫指標是否有異常波動，這些波動對DBA很有參考意義，往往預示著數據庫性能瓶頸的發展趨勢。Oracle提供了一個工具能夠將兩個時段的AWR報告合并，并能方便地顯示出比對信息，這個工具就是AWRDD。看來醫生分析前后兩次體檢報告的動作和AWRDD報告比起來，兩者也非常類似。
大家知道做胃鏡是一件很麻煩的事(類似ASH報告)，如果沒毛病就沒必要讓我們遭這罪。可萬一體檢報告無情地告訴你胃有毛病，甚至是醫生分析你前后兩次體檢報告（類似ADDM）后告訴你胃病在加速中，你被迫無奈只好去做胃鏡了。做完后醫生發現你胃部有大量息肉，卻無法判斷這些息肉是否為良性。于是還要做進一步的檢查，這就是活檢。不要緊張，平時注意健康生活就好。同樣ASH報告判斷出某些SQL有問題，卻無法得到執行計劃等更詳細的信息，只能依靠AWRSQRPT去獲取這些信息。看來活檢和AWRSQRPT報告比起來，兩者也非常類似。
最后恭喜你，活檢報告顯示未產生癌變，只要好好治療，注意身體，胃就能恢復健康！看本書的讀者們，你們都是IT人士，生活無規律加班熬夜者居多，一定要注意身體哦！
對了，還有一件最重要的事沒交代。大家似乎搞懂了Oracle五大性能報告，可是這些好東西在哪里才能得到呢？別著急，后續章節馬上就會告訴你如何獲取這五大性能報告。
2. 報告的關注點
如果患者拿著有各種晦澀指標的體檢報告來到門診請教醫生，他一定會關注各種指標來判斷患者具體是什么毛病。同樣你也會對Oracle的性能報告中的各種指標進行關注來判斷數據庫出了什么毛病。兩者非常類似，關注不同的指標，都是為了施救，前者救人，后者救數據庫。
聽起來是不是很激動，恨不得馬上就要開始當救庫英雄了！別急，接下來還要告訴你關注什么，然后在案例中讓你感受一下什么叫救庫英雄。
特別提醒：
這里有一個特別值得注意的地方，那就是性能報告的采樣時間。Oracle默認是每小時產生一個采樣點，你可以收集每個小時的性能報告。我們對此要敏感，比如你的性能故障是發生在今天早上7點～8點。然后系統自動恢復了，你獲取一張8點～9點的性能報告來查問題，就毫無意義了。
3、五大性能報告的獲取1AWR的獲取與說明
獲取AWR報告的方式有兩種：一種是直接獲取方式，調后臺腳本awrrpt.sql來獲取，執行方式一般是在sqlplus下執行@?/rdbms/admin/awrrpt.sql；
另一種則是通過調用命令包，獲取dbms_workload_repository這個包的awr_report_html程序，用SQL命令的形式輸出內容。
Select output from table(dbms_workload_repository.awr_report_html
(v_dbid, v_instance_number,v_min_snap_id,v_max_snap_id))
（1）直接獲取
試驗1（未使用批量提交）：
接下來通過提示就可以生成AWR報告了，具體步驟略去，詳情請掃本章最后的二維碼。
試驗2（單機下，正確使用批量提交）：
接下來通過提示就可以生成awr報告了，具體步驟略去。
（2）通過調用命令包獲取
直接調用工具包的方式，特別適合用在程序自動獲取報告的場景。
注：其中977587123是數據庫的主機標識，可以在數據庫的數據字典中查到，1是標識實例，如果是RAC，就有1和2兩個，單機就只有1。1920和1921是兩個斷點時間，比如9點和10點之間。
2ASH的獲取與說明
獲取ASH報告的方式也有兩種：一種是直接獲取方式，調后臺腳本ashrpt.sql來獲取，執行方式一般是在sqlplus下執行@?/rdbms/admin/ashrpt.sql；另一種則是通過調用命令包，獲取dbms_workload_repository這個包的ash_report_html程序。用SQL命令的形式輸出內容。
select output from table(dbms_workload_
repository.ash_report_html( dbid,inst_num,l_btime,l_etime)
（1）直接獲取
說明：
如果你是一路回車，就是獲取最近5分鐘的ASH報告。
如果你根據Oldest ASH sample available 時間，然后回車，選擇的是目前可收集的最長ASH運行情況。
你可以選擇Oldest ASH sample available和Latest ASH sample available之間時間，然后輸入時長，比如30表示30分鐘，取你要取的任何時段的ASH報告。
ASH報告的獲取不同于AWR的地方在于，快照之間有無重啟動作不影響報告的獲取。
ASH報告可以直接手工獲取，比如select output from table(dbms_workload_ repository.ash_report_html( dbid,inst_num,l_btime,l_etime)。
（2）通過調用命令包獲取
直接調用工具包的方式，特別適合用在程序自動獲取報告的場景。
注：其中977587123是數據庫的主機標識，可以在數據庫的數據字典中查到，1是標識實例，如果是RAC，就有1和2兩個，單機就只有1。SYSDATE-30/1440,SYSDATE-1/1440 分別是開始時間和結束時間。
3ADDM的獲取與說明
獲取ADDM報告的方式也有兩種，一種是直接獲取方式：調后臺腳本addmrpt.sql來獲取，執行方式一般是在sqlplus下執行@?/rdbms/admin/addmrpt.sql。另一種則是通過調用命令包的方式獲取：調用dbms_workload_repository這個包的addm_report_html程序。用SQL命令的形式輸出內容。
- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name=> 'ADDM',
task_name=> 'MYADDM',
task_desc=> 'MYADDM');
（1）直接獲取
@?/rdbms/admin/addmrpt.sql
具體執行過程略去。
（2）通過調用命令包獲取
注：直接調用工具包的方式，適合用在自動獲取報告的場景。
4AWRDD的獲取與說明
獲取AWRDD報告一般是用直接獲取的方式，這個腳本的交互部分需要輸入要進行對比的兩個awr報告的begin snap_id與end snap_id，然后輸入對比結果報告的名稱，這里就不詳細介紹了，請讀者自行試驗完成。
直接獲取：
@?/rdbms/admin/awrddrpt.sql
具體略去。
5AWRSQ獲取與說明
獲取AWRSQRPT報告的關鍵之處在于，交互部分要輸入所要分析的SQL的SQL_ID，這是關鍵之處。而這個SQL_ID可以從AWR報告中獲取。
以上5個報告的獲取本身并不難，操作一遍就會了，筆者也會再提供在線操作視頻，讓大家實際體會一遍。現在關鍵在于，要明白這5個報告的作用和相互之間的區別，搞懂這些，調優之路就算完成過半了。當然，接下來如何分析讀懂這五大報告的關鍵指標就非常重要了，有一些指標你必須關注，否則你就當不了“醫生”了。
4、五大報告關注的要點1AWR的關注點
AWR報告是五大報告中最全面最重要的一個報告，它的相關指標也顯得格外重要。這里我們列出DB Time、load_profile、efficiency percentages、top 5 events、SQL Statistics、Segment_statistics這6個指標入手分析。
（1）AWR關注點1之DB Time
DB Time這個指標主要用來判斷當前系統有沒有遇到相關瓶頸，是否較為繁忙導致等待時長很長。一般來說，Elapsed時間乘以CPU個數的時間如果結果大于DB Time，我們認為系統壓力不大，反之則壓力較大。如下例子中，60.11×64=3847.04<5990.6，說明系統現在還是比較繁忙的。
（2）AWR關注點2之load_profile
load_profile這個指標主要用來展現當前系統的一些指示性能的總體參數，比如經典的Redo size就是用來顯示平均每秒的日志尺寸和平均每個事務的日志尺寸，結合Transactions這個每秒事務數的指標，就可以分析出當前事務的繁忙程度。
下圖中顯示每秒有6777.1個事務數，這在現實中幾乎不可能，現實中的運營商系統一般在200上下比較正常，超過1000就屬于非常繁忙了。
把上圖和下面的圖進行比較，就非常明顯了，下圖顯示每秒有0.6個事務，平均每個事務產生的日志尺寸是7位數。這說明系統是一個提交不頻繁的處理大任務事件的系統。而上圖的尺寸是3位數。這里非常容易看出，這是一個提交非常頻繁且每個事務都非常小的密集提交系統。
（3）AWR關注點3之efficiency percentages
efficiency percentages是一些命中率指標，其中Buffer Hit、Library Hit等都表示SGA（System global area）的命中率。在下圖中Soft Parse指標表示共享池的軟解析率，在OLTP系統中如果該指標低于90%應當引起你的注意，這表示存在未使用綁定變量的情況。我們通過比對兩個報告，可以看出明顯差異，如下面系列圖所示。
報告1（未有效地使用綁定變量，產生大量硬解析的場景）。
報告2（有效地使用綁定變量，進行綁定變量優化后的場景）。
（4）AWR關注點4之top 5 events
等待事件是衡量數據庫整體優化情況的重要指標，通過觀察Top 5 Timed Foreground Events模塊的Event和%DB time兩列，可以非常直觀地看出當前數據庫面臨的主要等待事件是什么。下圖兩個例子分別告訴我們數據庫面臨鎖等待和日志切換等待的情形。
（5）AWR關注點5 之SQL Statistics
SQL Statistics分別從幾個維度來羅列出TOP的SQL，這是一種簡單粗暴但有效的方法。看看執行時長，直接拿出來優化一般都是對的做法。
（6）AWR關注點6 之Segment Statistics
使用Segment Statistics指標進行尋找和判斷，也是一個非常直接的優化手段。當我們知道繁忙落在數據庫的那個表段是索引段時，優化就變得相對簡單了，比如最簡單粗暴的方法就是對表和索引進行數據清理和瘦身。
2ASH的關注點
ASH是啥？哦，有人想起來了，胃鏡。
完成了ASH報告的獲取后，打開獲得的ASH報告，其實對于該報告可關注的東西非常直接，就是看看哪些SQL和哪些等待事件是相關聯的。
如下圖所示：
3ADDM的關注點
ADDM是啥？哦，是醫生的門診報告。
由于這是Oracle的一些分析建議，所以ADDM的閱讀非常簡單，基本上從FINDING 1、FINDING 2順序往下看就可以了。一般是從數據庫整體配置和局部SQL兩方面給出建議。我們看看都能明白，如下圖所示：
整體性的建議
局部SQL建議
4AWRDD的關注點
AWRDD是啥？哦，是醫生在看你前后兩次體檢報告，在比較指標的變化。其實這個關注點很簡單，基本上就是AWR關注什么，AWRDD就關注什么，沒什么特別的，簡單列舉如下。
（1）AWRDD關注點1 之不同時期load profile的比較
（2）AWRDD關注點2 之不同時期等待事件的比較
（3）AWRDD關注點3 之不同時期TOP SQL的比較
5AWRSQRPT的關注點
AWRSQRPT是啥？哦，有人想起來了，活檢。別打顫！
其實沒啥，就是看看AWR和ASH里看不到的東西。都有啥呢？比如執行計劃的相關細節，關于執行計劃我們會在后面詳細說明。這里要特別注意一點，Oracle的執行計劃可能會隨著環境的變化而變化，會隨著數據的變化而變化，因此可能會產生多個執行計劃，這個AWRSQRPT就會出現多個執行計劃。具體詳見下面系列圖。
（1）Plan statistics
（2）Execution Plan
（3）是否有多個執行計劃
案例的分享與交流
說了這么多，我們來看幾個相關案例，體會使用工具進行整體優化的重要性。
1、和并行等待有關的案例
這是來自某政府系統的一個平臺的案例，請看下圖，這是AWR報告的Top 5 Timed Events的展現，可以看出當前數據庫的等待事件主要是PX Deq相關的等待，這屬于濫用并行等待導致系統資源緊張的一個案例。
該案例暴露出的問題比想象中更嚴重，因為該系統的不少表和索引的屬性被設置了并行度，這導致所有對這些表和索引的訪問都成了并行訪問。后續解決思路就是將表和索引的并行屬性去掉。將一些需要并行處理的大任務進行時間切割，確認部分大任務是可以放在凌晨業務低峰期執行的，就設置了并行的Hint任務，讓部分SQL在夜間并行執行，大部分SQL在白天正常執行，從而系統恢復正常，業務也能順利開展。
2、和熱塊競爭有關的案例
接下來我們再看一個案例，這是某運營商的系統，從AWR報告的Top 5 Timed Events等待事件主要是gc buffer busy來看，當前系統主要等待事件是熱塊競爭的等待。
等待事件對應的SQL主要有哪些，我們其實可以通過對應時間段的ASH報告分析出來，比如下圖就是和AWR的對應。
將AWR報告和ASH報告結合起來看，往往可以找出具體需要優化的SQL。在本案例中，我們發現兩個節點共同訪問一些對象導致熱塊競爭。后續通過一系列改造，讓不同的業務跑在不同的節點上，從而避免了兩個節點訪問同一個對象，問題得以緩解。
3、和日志等待有關的案例
這是一個典型的案例，從Transactions達到800多，可以看出事務非常繁忙，再從Per Transaction才1000左右，可以看出每個事務非常小。這說明了系統存在事務未批量提交的情況。這種情況一般出現在循環中，把提交寫到循環里面的情況。后續通過排查，發現果真是如此原因。
接下來的log file switch(checkpoint incomplete) 和log file sync的相關等待正是由于日志切換過于頻繁導致的等待，這正是如前所述，未批量提交導致。
4、新疆某系統的前臺優化
如下是新疆某運營商的優化案例，我們通過Top 5 Timed Events等待事件發現了瓶頸主要在IO。接下來我們迅速到Tablespace IO Stats模塊去查看，如下圖所示：
果然是有點問題。這個AV RD(MS)項表示平均一次物理讀花費的時間（單位為ms）。有一種說法是， AV RD(MS)大于7就說明系統有嚴重的IO問題，其中BOSSWG_PERF_DATA居然達到了47，這說明當前的存儲IO存在瓶頸。后續通過改善存儲解決了問題。
5、浙江某系統的調優案例
這個案例來自浙江某生產系統，我們通過Top 5 Timed Events等待事件發現了瓶頸主要在gc buffer busy等待事件，這和新疆某系統的前臺優化案例類似。不過AWR報告非常強大，你通過各個細節都可以很有收獲，從而找到解決問題的方法，比如你此時直接定位到Segments by Global Cache Buffer Busy模塊，如下圖所示：
通過觀察segments by global cache buffer busy的對象，我們找到了相關需要優化的表。最后我們結合業務，通過對該表瘦身、增加分區、避免兩個節點同時訪問的方案，優化了對應SQL的性能。
總結
End.
來源：公眾號“DBAplus社群”
運行人員：中國統計網小編（微信號：itongjilove）
微博ID：中國統計網
中國統計網，是國內最早的大數據學習網站，公眾號：中國統計網
http://www.itongji.cn
AWR 是 Oracle 10g 版本 推出的新特性， 全稱叫Automatic Workload Repository-自動負載信息庫 AWR 是通過對比兩次快照(snapshot)收集到的統計信息，來生成報表數據，生成的報表包括多個部分。
前面已經對awr報告的WORKLOAD REPOSITORY report部分做了介紹，下面根據生產環境對Report Summary部分的一些關鍵參數做分析，內容比較多，大家盡量耐心看完，還是有點用的。
這里主要顯示SGA中每個區域的大小（在AMM改變它們之后），可用來與初始參數值比較。
Buffer Cache：最終目的就是盡可能的減少磁盤I/O以便快速的讀或寫。也不是越大越好，如果Buffer Cache過大，會造成大的LRU 列表和 dirty list，引發邏輯讀的過程消耗CPU量高。同時大的Buffer Cache也會增加DBWn 進程的負擔。
shared pool主要包括library cache和dictionary cache。
library cache用來存儲最近解析（或編譯）后SQL、PL/SQL和Java classes等。 dictionary cache用來存儲最近引用的數據字典。
發生在library cache或dictionary cache的cache miss代價要比發生在buffer cache的代價高得多。因此shared pool的設置要確保最近使用的數據都能被cache。
我們可以看到shared pool一直收縮，在shrink過程中一些row cache 對象被lock住可能導致前臺row cache lock等解析等待，最好別讓shared pool shrink。如果這里shared pool一直在grow，那說明shared pool原有大小不足以滿足需求(可能是大量硬解析)，結合后面的解析信息和SGA breakdown來一起診斷問題。
這里可以看到生產的硬解析每秒為102，硬解析為7.5，問題不大，但是Logons每秒3.3，表明可能有爭用問題，下面針對每個指標具體分析：
這里主要顯示數據庫負載概況，將之與基線數據比較才具有更多的意義，如果每秒或每事務的負載變化不大，說明應用運行比較穩定。
單個的報告數據只說明應用的負載情況，絕大多數據并沒有一個所謂“正確”的值，Logons大于每秒1~2個、Hard parses大于每秒100、全部parses超過每秒300表明可能有爭用問題。
上述所有指標 的目標均為100%，即越大越好，在少數bug情況下可能超過100%或者為負值。
80%以上 %Non-Parse CPU
90%以上 Buffer Hit%, In-memory Sort%, Soft Parse%
95%以上 Library Hit%, Redo Nowait%, Buffer Nowait%
98%以上 Latch Hit%
這里主要顯示Oracle關鍵指標的內存命中率及其它數據庫實例操作的效率。其中Buffer Hit Ratio 也稱Cache Hit Ratio，
Library Hit ratio也稱Library Cache Hit ratio。
在一個使用直接讀執行大型并行查詢的DSS環境，20%的Buffer Hit Ratio是可以接受的，而這個值對于一個OLTP系統是完全不能接受的。
根據多年的經驗，對于OLTP系統，Buffer Hit Ratio理想應該在90%以上。
Buffer Nowait表示在內存獲得數據的未等待比例。在緩沖區中獲取Buffer的未等待比率
Buffer Nowait的這個值一般需要大于99%。否則可能存在爭用
buffer hit 高速緩存命中率，反應物理讀和緩存命中間的糾結，表示進程從內存中找到數據塊的比率，監視這個值是否發生重大變化比這個值本身更重要
Redo NoWait表示在LOG緩沖區獲得BUFFER的未等待比例。如果太低（可參考90%閥值），考慮增加LOG BUFFER。
當redo buffer達到1M時，就需要寫到redo log文件，所以一般當redo buffer設置超過1M，不太可能存在等待buffer空間分配的情況。
library hit表示Oracle從Library Cache中檢索到一個解析過的SQL或PL/SQL語句的比率，當應用程序調用SQL或存儲過程時，
Oracle檢查Library Cache確定是否存在解析過的版本，如果存在，Oracle立即執行語句；如果不存在，Oracle解析此語句，并在Library Cache中為它分配共享SQL區。
Latch Hit：Latch是一種保護內存結構的鎖，可以認為是SERVER進程獲取訪問內存數據結構的許可。
要確保Latch Hit>99%，否則意味著Shared Pool latch爭用，可能由于未共享的SQL，或者Library Cache太小，可使用綁定變更或調大Shared Pool解決。
要確保>99%，否則存在嚴重的性能問題。
Parse CPU to Parse Elapsd：解析實際運行時間/(解析實際運行時間+解析中等待資源時間)，越高越好。即：解析實際運行時間/(解析實際運行時間+解析中等待資源時間)。如果該比率為100%，意味著CPU等待時間為0，沒有任何等待。
Non-Parse CPU ：SQL實際運行時間/(SQL實際運行時間+SQL解析時間)，太低表示解析消耗時間過多。
計算公式為：% Non-Parse CPU=round(100*1-PARSE_CPU/TOT_CPU),2)。如果這個值比較小，表示解析消耗的CPU時間過多。
Soft Parse：軟解析的百分比（softs/softs+hards），近似當作sql在共享區的命中率
In-memory Sort：在內存中排序的比率，如果過低說明有大量的排序在臨時表空間中進行。
Execute to Parse：是語句執行與分析的比例，如果要SQL重用率高，則這個比例會很高。該值越高表示一次解析后被重復執行的次數越多。
Memory Usage %：對于一個已經運行一段時間的數據庫來說，共享池內存使用率，應該穩定在75%-90%間，
如果太小，說明Shared Pool有浪費，而如果高于90，說明共享池中有爭用，內存不足。
這個數字應該長時間穩定在75%～90%。如果這個百分比太低，表明共享池設置過大，帶來額外的管理上的負擔，從而在某些條件下會導致性能的下降。如果這個百分率太高，會使共享池外部的組件老化，如果SQL語句被再次執行，這將使得SQL語句被硬解析。這里顯示生產環境比率低于75%，沒有充分利用shared pool。
SQL with executions>1：執行次數大于1的sql比率，如果此值太小，說明需要在應用中更多使用綁定變量，避免過多SQL解析。
在一個趨向于循環運行的系統中，必須認真考慮這個數字。在這個循環系統中，在一天中相對于另一部分時間的部分時間里執行了一組不同的SQL語句。
在共享池中，在觀察期間將有一組未被執行過的SQL語句，這僅僅是因為要執行它們的語句在觀察期間沒有運行。只有系統連續運行相同的SQL語句組，這個數字才會接近100%。
Memory for SQL w/exec>1：執行次數大于1的SQL消耗內存的占比。
這是與不頻繁使用的SQL語句相比，頻繁使用的SQL語句消耗內存多少的一個度量。
這個數字將在總體上與% SQL with executions>1非常接近，除非有某些查詢任務消耗的內存沒有規律。
在穩定狀態下，總體上會看見隨著時間的推移大約有75%～85%的共享池被使用。如果Statspack報表的時間窗口足夠大到覆蓋所有的周期，
執行次數大于一次的SQL語句的百分率應該接近于100%。這是一個受觀察之間持續時間影響的統計數字。可以期望它隨觀察之間的時間長度增大而增大。
總結：通過ORACLE的實例有效性統計數據，我們可以獲得大概的一個整體印象，但是不能由此來確定數據運行的性能。當前性能問題的確定，我們主要還是依靠后面介紹的等待事件來確認。
可以這樣理解兩部分的內容，hit統計幫助我們發現和預測一些系統將要產生的性能問題，這樣可以做到未雨綢繆。而wait事件，就是表明當前數據庫已經出現了性能問題需要解決，所以是亡羊補牢的性質。
后面會分享更多DBA方面的內容，感興趣的朋友可以關注下！！
*請認真填寫需求信息，我們會在24小時內與您取得聯系。
