整合營(yíng)銷服務(wù)商
電腦端+手機(jī)端+微信端=數(shù)據(jù)同步管理
免費(fèi)咨詢熱線：
電腦端+手機(jī)端+微信端=數(shù)據(jù)同步管理
免費(fèi)咨詢熱線：
看重復(fù)索引
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;查詢碎片程度高（實(shí)際使用率小于30%）的表
可以收縮的表?xiàng)l件為什么block>100，因?yàn)橐恍┖苄〉谋恚挥袔仔袛?shù)據(jù)實(shí)際大小很小，但是block一次性分配就是5個(gè)（11g開始默認(rèn)一次性分配1M的block大小了，見create table storged的NEXT參數(shù)），5個(gè)block相對(duì)于幾行小表數(shù)據(jù)來說就相差太大了。
算法中/0.9是因?yàn)閴K的pfree一般為10%，所以一個(gè)塊最多只用了90%，而且一行數(shù)據(jù)大于8KB時(shí)容易產(chǎn)生行鏈接，把一行分片存儲(chǔ)，一樣的一個(gè)塊連90%都用不滿 ，AVGROWLEN還是比較準(zhǔn)的，比如個(gè)人實(shí)驗(yàn)情況一表6個(gè)字段，一個(gè)number，其他5個(gè)都是char(100)但是實(shí)際數(shù)據(jù)都是’1111111’7位，AVGROWLEN顯示依然為513 。
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) "理論大小M",
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) "實(shí)際大小M",
round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
(BLOCKS * 8192 / 1024 / 1024),
3) * 100 || '%' "實(shí)際使用率%"
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查詢索引碎片的比例
索引刪除行數(shù)除以索引總行數(shù)的百分比>30%即認(rèn)為索引碎片大，也就是需要重建的索引
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高的表
集群因子越接近塊數(shù)越好，接近行數(shù)則說明索引列的列值相等的行分布極度散列，可能不走索引掃描而走全表掃描 ：
方法一
/* 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
|| '%'
"集群因子接近行數(shù)"
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
|| '%'
"集群因子接近行數(shù)"
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根據(jù)sid查spid或根據(jù)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 "客戶端機(jī)器進(jìn)程號(hào)",
s.osuser
FROM v$session s, v$process p
WHERE s.paddr=p.addr AND s.sid=XX OR p.spid=YY根據(jù)sid查看具體的sql語句，不要加條件v$session.status=’ ACTIVE’，比如toad對(duì)同一數(shù)據(jù)庫(kù)開兩個(gè)連接會(huì)話，都執(zhí)行了一些語句，其中一個(gè)窗口查詢select * from v$session時(shí)會(huì)發(fā)現(xiàn)另一個(gè)窗口在v$session.status是INACTIVE，并不代表另一個(gè)窗口沒有執(zhí)行過sql語句，而當(dāng)前窗口是active狀態(tài)，對(duì)應(yīng)的sql_id對(duì)應(yīng)的語句就是select * from v$session而不是之前執(zhí)行過的sql語句，ACTIVE表示當(dāng)前正在執(zhí)行sql。
一個(gè)sid可能執(zhí)行過很多個(gè)sql，所以有時(shí)需要的sql通過如下查不到是正常的，比如查詢到某死鎖源sid，通過如下查詢可能只是個(gè)select語句，而真正引起死鎖的sql卻查不到，是因?yàn)榭赡苓@個(gè)sid持續(xù)了很長(zhǎng)時(shí)間，這個(gè)sid之前執(zhí)行的一些sql在v$sql可能已經(jīng)被清除了。
方法一
/* Formatted on 2019/11/6 10:04:20 (QP5 v5.326) */
SELECT username,
sid,
SERIAL#,
LOGON_TIME,
status,
PROGRAM,
CLIENT_IDENTIFIER,
machine,
action,
PROCESS "客戶端機(jī)器進(jìn)程號(hào)",
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 "客戶端機(jī)器進(jìn)程號(hào)",
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如果上面語句執(zhí)行太慢，則按如下兩步
/* 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 "客戶端機(jī)器進(jìn)程號(hào)",
osuser
FROM v$session
WHERE sid=&sid;
SELECT sql_fulltext
FROM v$sql
WHERE hash_value=XX;--XX為上面 sqlhashvalue，如果 sqlhashvalue為0，則XX為上面 prevhashvalue
根據(jù)spid查詢具體的sql語句(不要加條件v$session.status=’ ACTIVE’,比如toad對(duì)同一數(shù)據(jù)庫(kù)開兩個(gè)連接會(huì)話，都執(zhí)行了一些語句，其中一個(gè)窗口查詢select * from v$session時(shí)會(huì)發(fā)現(xiàn)另一個(gè)窗口在v$session.status是INACTIVE，并不代表另一個(gè)窗口沒有執(zhí)行過sql語句，而當(dāng)前窗口是active狀態(tài)，對(duì)應(yīng)的sql_id對(duì)應(yīng)的語句就是select * from v$session而不是之前執(zhí)行過的sql語句，ACTIVE表示當(dāng)前正在執(zhí)行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 "客戶端機(jī)器進(jìn)程號(hào)",
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來自哪個(gè)IP
查看trace文件名就可以知道spid,trace文件里面有sid和具體sql，如果trace存在incident，那trace就看不到具體sql，但是可以在incident文件中看到具體的sql，如DWora17751.trc中17751就是spid，里面有這樣的內(nèi)容Incident 115 created, dump file: /XX/incident/incdir115/DWora17751i115.trc，那么在DWora17751_i115.trc就可以看到具體的sql語句)
DBora29349.trc中出現(xiàn)
*** 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服務(wù)器上執(zhí)行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出現(xiàn)兩個(gè)，說明來自220，連接了228數(shù)據(jù)庫(kù)服務(wù)器，但是又通過228服務(wù)器的dblink去連接了16服務(wù)器
查詢死鎖堵塞的會(huì)話sid
最簡(jiǎn)單的一個(gè)SQL
select * from V$SESSION_BLOCKERS select * from dba_waiters;最常用的一個(gè)SQL
select sid,status,LOGON_TIME,sql_id,blocking_session "死鎖直接源",FINAL_BLOCKING_SESSION "死鎖最終源",event,seconds_in_wait "會(huì)話鎖住時(shí)間_S",LAST_CALL_ET "會(huì)話持續(xù)時(shí)間_S" from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID';可以把兩者SID放入v$session，發(fā)現(xiàn)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環(huán)境，一定要用gv$來查，并且執(zhí)行alter system kill session 'sid,serial#'要到RAC對(duì)應(yīng)的實(shí)例上去執(zhí)行
把上面被堵塞會(huì)話的sid代入如下語句，可以發(fā)現(xiàn)鎖住的對(duì)象和對(duì)象的哪一行(如果sid是堵塞源的會(huì)話，則 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對(duì)象
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;查詢超過兩個(gè)小時(shí)的不活動(dòng)會(huì)話
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 "客戶端機(jī)器進(jìn)程號(hào)",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服務(wù)器名稱 and status='INACTIVE' and sql_id is null and LAST_CALL_ET>7200);查詢堵塞別的會(huì)話超過30分鐘且自身是不活動(dòng)的會(huì)話
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;查詢當(dāng)前正在執(zhí)行的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';查詢正在執(zhí)行的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;查詢正在執(zhí)行的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;查詢一個(gè)會(huì)話session、process平均消耗多少PGA內(nèi)存，查看下面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 執(zhí)行次數(shù)排序
/* 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來排序，因?yàn)槿魏我粭l語句不管執(zhí)行幾次都會(huì)耗邏輯讀和cpu，可能不會(huì)耗物理讀（遇到LRU還會(huì)耗物理讀，LRU規(guī)則是執(zhí)行最不頻繁的且最后一次執(zhí)行時(shí)間距離現(xiàn)在最久遠(yuǎn)的就會(huì)被交互出buffer cache），是因?yàn)閎uffer cache存放的是數(shù)據(jù)塊，去數(shù)據(jù)塊里找行一定會(huì)消耗cpu和邏輯讀的。Shared pool執(zhí)行存放sql的解析結(jié)果，sql執(zhí)行的時(shí)候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool。
TOP 10 邏輯讀排序（消耗內(nèi)存排序）
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來排序，因?yàn)槿魏我粭l語句不管執(zhí)行幾次都會(huì)耗邏輯讀和cpu，可能不會(huì)耗物理讀（遇到LRU還會(huì)耗物理讀，LRU規(guī)則是執(zhí)行最不頻繁的且最后一次執(zhí)行時(shí)間距離現(xiàn)在最久遠(yuǎn)的就會(huì)被交互出buffer cache），是因?yàn)閎uffer cache存放的是數(shù)據(jù)塊，去數(shù)據(jù)塊里找行一定會(huì)消耗cpu和邏輯讀的。Shared pool執(zhí)行存放sql的解析結(jié)果，sql執(zhí)行的時(shí)候只是去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來排序，因?yàn)槿魏我粭l語句不管執(zhí)行幾次都會(huì)耗邏輯讀和cpu，可能不會(huì)耗物理讀（遇到LRU還會(huì)耗物理讀，LRU規(guī)則是執(zhí)行最不頻繁的且最后一次執(zhí)行時(shí)間距離現(xiàn)在最久遠(yuǎn)的就會(huì)被交互出buffer cache），是因?yàn)閎uffer cache存放的是數(shù)據(jù)塊，去數(shù)據(jù)塊里找行一定會(huì)消耗cpu和邏輯讀的。Shared pool執(zhí)行存放sql的解析結(jié)果，sql執(zhí)行的時(shí)候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool。
查詢等待事件
select event,sum(decode(wait_time,0,0,1)) "之前等待次數(shù)", sum(decode(wait_time,0,1,0)) "正在等待次數(shù)",count(*) from v$session_wait group by event order by 4 desc;查詢當(dāng)前等待事件對(duì)應(yīng)的對(duì)象
select distinct wait_class#,wait_class from v$session_wait_class order by 1;以上sql發(fā)現(xiàn)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;能查出等待的對(duì)象是否來自數(shù)據(jù)文件（如果以上查到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把上面第二個(gè)sql結(jié)果的p1、p2值代入上述sql的fileid、blockid
通過AWR的top sql或v$sql.sql_text查看是否有該對(duì)象的語句，檢查該語句的執(zhí)行計(jì)劃就可以查出問題所在。
查詢當(dāng)前正在消耗臨時(shí)空間的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不足而使用臨時(shí)表空間的最頻繁的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以后推薦第二種
注意：任何一條執(zhí)行過的語句不管執(zhí)行了幾次在V$SQL中都只有一條記錄，V$SQL中會(huì)記錄執(zhí)行了幾次。兩條一模一樣的語句但是在不同的schema下執(zhí)行的兩種結(jié)果，如select * from t1.test在sye、system下執(zhí)行則V$SQL只有一條記錄(誰先執(zhí)行則PARSINGSCHEMANAME顯示誰)。如在sys和system都執(zhí)行select * from test則V$SQL中有兩條記錄，兩條記錄的CHILDNUMBER和PARSINGSCHEMA_NAME不一樣。
同一個(gè)用戶下執(zhí)行一樣的語句如果大小寫不一樣或加了hint的話則會(huì)出現(xiàn)多個(gè)V$SQL記錄，說明V$SQL對(duì)應(yīng)的sql語句必須一模一樣，如果alter system flush sharedpool（主站慎用）后再執(zhí)行一樣的語句，發(fā)現(xiàn)語句在V$SQL中的SQLID和HASHVALUE與之前的一樣，說明SQLID和HASHVALUE應(yīng)該是oracle自己的一套算法來的，只是根據(jù)sql語句內(nèi)容來進(jìn)行轉(zhuǎn)換，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表示類語句運(yùn)行了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)查看數(shù)據(jù)文件可用百分比
dbafreespace并不會(huì)包含所有fileid，如果該數(shù)據(jù)文件滿了，則 dbafreespace.fileid沒有該數(shù)據(jù)文件，所以以下sql中 a.fileid=b.fileid的條件過濾后是不會(huì)有所有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'
"文件最大可用總?cè)萘?#34;,
ROUND (b.bytes / 1024 / 1024 / 1024, 2) || 'G'
"文件總?cè)萘?#34;,
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;--如下為標(biāo)準(zhǔn)版
/* 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'
"文件最大可用總?cè)萘?#34;,
ROUND (b.bytes / 1024 / 1024 / 1024, 2) || 'G'
"文件當(dāng)前總?cè)萘?#34;,
ROUND ((b.bytes - SUM (NVL (a.bytes, 0))) / 1024 / 1024 / 1024, 2)
|| 'G'
"文件當(dāng)前已用容量",
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;查看數(shù)據(jù)庫(kù)文件的實(shí)際總量，單位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不會(huì)包含所有tablespace，如果一個(gè)表空間的數(shù)據(jù)文件都滿了，則這個(gè)表空間不會(huì)出現(xiàn)在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";--如下為標(biāo)準(zhǔn)版
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";查看臨時(shí)表空間使用率
方法一
/* 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(+);真實(shí)使用率：
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;估計(jì)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'));產(chǎn)生undo的當(dāng)前活動(dòng)會(huì)話是哪些
方法一
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) "總?cè)萘?#34;,round(free_mb/1024) "空閑空間",round((free_mb/total_mb)*100) "可用空間比例" from gv$asm_diskgroup;統(tǒng)計(jì)每個(gè)用戶使用表空間率
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 "表空間總計(jì)使用率 %",
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 "用戶", "表空間名";查看閃回區(qū)\快速恢復(fù)區(qū)空間使用率
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;查看僵死進(jìn)程，分兩種（一種是會(huì)話不在的，另一種是會(huì)話標(biāo)記為killed的但是會(huì)話還在的）
alter system kill session一執(zhí)行則session即標(biāo)記為KILLED，但是如果會(huì)話產(chǎn)生的數(shù)據(jù)量大則這個(gè)kill可能會(huì)比較久，在這個(gè)過程中session標(biāo)記為KILLED但是這個(gè)會(huì)話還在V$session中，則V$session.paddr還在，所以可以匹配到V$process.addr，所以process進(jìn)程還在；當(dāng)kill過程執(zhí)行完畢，則這個(gè)會(huì)話即不在V$session中
會(huì)話不在的
select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18);會(huì)話還在的，但是會(huì)話標(biāo)記為killed
select * from v$process where addr in (select paddr from v$session where status='KILLED');再根據(jù)上述結(jié)果中的SPID通過如下命令可以查看到process的啟動(dòng)時(shí)間
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.
*數(shù)據(jù)緩沖區(qū)命中率（百分比小于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）
以下兩者可以根據(jù)個(gè)人理解運(yùn)用
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進(jìn)程寫日志時(shí)每執(zhí)行一次lgwr需要多少秒，在state是waiting的情況下，某個(gè)等待編號(hào)seq#下，secondsinwait達(dá)多少秒，就是lgwr進(jìn)程寫一次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)查詢一個(gè)AWR周期內(nèi)的平均session數(shù)、OS平均負(fù)載、平均db time、平均每秒多少事務(wù)
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對(duì)應(yīng)值的單位是百分一秒/每秒 --(/100)(ceil((max(end_time)-max(BEGIN_TIME))246060))是代表每個(gè)snap周期內(nèi)的總秒數(shù)，oracle 兩個(gè)時(shí)間相減默認(rèn)的是天數(shù),2460*60 為相差的秒數(shù) --這個(gè)SQL查到的DB TIME比較準(zhǔn)確，和awr上面的db time比較一致
查詢產(chǎn)生熱塊較多的對(duì)象
x$bh .tch(Touch)表示訪問次數(shù)越高，熱點(diǎn)快競(jìng)爭(zhēng)問題就存在
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;手工創(chuàng)建快照的語句
exec dbms_workload_repository.create_snapshot; AWR設(shè)置每隔30分鐘收集一次報(bào)告，保留14天的報(bào)告
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>14*24*60, interval=>30); select * from dba_hist_wr_control;AWR基線查看和創(chuàng)建
select * from dba_hist_baseline; exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id=>7550,end_snap_id=>7660,baseline_name=>'am_baseline');導(dǎo)出AWR報(bào)告的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));導(dǎo)出最新ADDM的報(bào)告（需要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 查詢某個(gè)SQL的執(zhí)行計(jì)劃
select * from table(dbms_xplan.display_cursor('sql_id',0,' advanced '));上面的0表示v$sql.childnumber為0，如果一個(gè)sqlid在v$sql中有多行說明有多個(gè)childnumber，要看哪兒childnumber的執(zhí)行計(jì)劃，就寫哪個(gè)的值，比如要看child_number為2的執(zhí)行計(jì)劃，就把上面sql的0改為2 。
官方文檔對(duì)display_cursor這個(gè)函數(shù)的說明里面沒有advanced這個(gè)參數(shù)值，只有BASIC、TYPICAL、ALL這幾個(gè)，不過實(shí)踐中發(fā)現(xiàn)advanced這個(gè)參數(shù)值顯示的內(nèi)容比這幾個(gè)參數(shù)值顯示的都多。
select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));創(chuàng)建xplan包，再執(zhí)行
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備份集詳細(xì)信息（未過期的，過期并已刪除的查不到）
SELECT B.RECID BackupSet_ID,
A.SET_STAMP,
DECODE(B.INCREMENTAL_LEVEL,
'',
DECODE(BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1,
'Incr-1級(jí)',
0,
'Incr-0級(jí)',
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備份進(jìn)度
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; 查詢執(zhí)行過全表掃描的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 查詢死事務(wù)需要多長(zhǎng)的回滾時(shí)間
X$KTUXE：[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)X$KTUXE表的一個(gè)重要功能是，可以獲得無法通過v$transaction來觀察的死事務(wù)信息，當(dāng)一個(gè)數(shù)據(jù)庫(kù)發(fā)生異常中斷，或者進(jìn)行延遲事務(wù)恢復(fù)時(shí)，數(shù)據(jù)庫(kù)啟動(dòng)后，無法通過V$TRANSACTION來觀察事務(wù)信息，但是X$KTUXE可以幫助我們獲得這些信息。該表中的KTUXECFL代表了事務(wù)的Flag標(biāo)記，通過這個(gè)標(biāo)記可以找到那些Dead事務(wù)：
SQL> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL; KTUXECFL COUNT(*) ------------------------ ---------- DEAD 1 NONE 2393 KTUXESIZ用來記錄事務(wù)使用的回滾段塊數(shù)，可以通過觀察這個(gè)字段來評(píng)估恢復(fù)進(jìn)度,例如如下事務(wù)回滾經(jīng)過測(cè)算需要大約3小時(shí)：
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表賦權(quán)給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查出一個(gè)用戶具有的所有系統(tǒng)權(quán)限和對(duì)象權(quán)限
系統(tǒng)權(quán)限（和用戶自己查詢select * from session_privs的結(jié)果一致）
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='用戶名');對(duì)象權(quán)限（和用戶自己查詢select * FROM TABLE_PRIVILEGES where GRANTEE='當(dāng)前用戶'的結(jié)果一致）
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='用戶名');查詢某個(gè)用戶擁有的角色
select * from dba_role_privs where GRANTEE='用戶名'; 查詢擁有DBA角色權(quán)限的用戶
select * from dba_role_privs where GRANTED_ROLE='DBA'; 查詢某個(gè)角色擁有的系統(tǒng)權(quán)限
select * from ROLE_SYS_PRIVS where role='角色名' 清除某個(gè)SQL的執(zhí)行計(jì)劃
Exec DBMS_SHARED_POOL.PURGE('v$sqlarea.ADDRESS,v$sqlarea.HASH_VALUE','c') 查詢密碼是否有過期限制，默認(rèn)是180天，一般修改為unlimited
select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD%'; ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED 查詢和修改隱含參數(shù)（必須在sysdba權(quán)限下操作）
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則說明在默認(rèn)在RAC的所有實(shí)例中修改 需要注意的是一定要加上雙引號(hào), 另外引號(hào)內(nèi)不能有空格, 只能包含參數(shù)的名字
評(píng)估PGA該設(shè)置多少
select PGA_TARGET_FOR_ESTIMATE from (select * from V$PGA_TARGET_ADVICE where ESTD_OVERALLOC_COUNT=0 order by 1) where rownum=1; 評(píng)估SGA該設(shè)置多少
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'; 統(tǒng)計(jì)所有表的容量大小(含分區(qū)字段、LOB字段) 一般先執(zhí)行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查看當(dāng)前會(huì)話的SID
select * from V$MYSTAT where rownum<2 查詢某個(gè)SID的某個(gè)統(tǒng)計(jì)信息，比如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統(tǒng)計(jì)整個(gè)DB的統(tǒng)計(jì)信息，V$SYSSTAT已經(jīng)取代了V$STATNAME，并且多了VALUE這一列 V$SESSTAT統(tǒng)計(jì)每個(gè)用戶的統(tǒng)計(jì)信息 查詢某個(gè)SID的某個(gè)等待事件的信息，比如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描述每個(gè)用戶的等待事件信息 V$SYSTEM_EVENT描述整個(gè)DB等待事件信息 V$EVENT_NAME描述等待事件信本身的信息(比如 V$ACTIVE_SESSION_HISTORY的P1TEXT、P2TEXT、P2TEXT匹配 V$EVENT_NAME的PARAMETER1、PARAMETER2、PARAMETER3)
RAC跨節(jié)點(diǎn)殺會(huì)話
alter system kill session 'SID,serial#,@1' --殺掉1節(jié)點(diǎn)的進(jìn)程 alter system kill session 'SID,serial#,@2' --殺掉2節(jié)點(diǎn)的進(jìn)程 Truncate 分區(qū)的SQL
ALTER TABLE table_name TRUNCATE PARTITION p1 DROP STORAGE UPDATE GLOBAL INDEXES; Drop分區(qū)的SQL
ALTER TABLE table_name DROP PARTITION p1 UPDATE GLOBAL INDEXES; DATAGUARD主備延遲多少時(shí)間的查詢方法 備 庫(kù)
select value from v$dataguard_stats where name='apply lag'或 備庫(kù)
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'); 查看某個(gè)包或存儲(chǔ)過程是否正在被調(diào)用,如果如下有結(jié)果，則此時(shí)不能編譯，否則會(huì)鎖住
select * from V$DB_OBJECT_CACHE where pin>0 and name=upper('XX')查詢數(shù)據(jù)庫(kù)打補(bǔ)丁的記錄
select * from dba_registry_history;查詢某表的索引字段的distinct行數(shù)和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行數(shù)
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以上空閑空間可以進(jìn)行收縮的數(shù)據(jù)文件
者介紹
梁敬彬，福富研究院副理事長(zhǎng)、公司唯一四星級(jí)內(nèi)訓(xùn)師，國(guó)內(nèi)一線知名數(shù)據(jù)庫(kù)專家，在數(shù)據(jù)庫(kù)優(yōu)化和培訓(xùn)領(lǐng)域有著豐富的經(jīng)驗(yàn)。多次應(yīng)邀擔(dān)任國(guó)內(nèi)外數(shù)據(jù)庫(kù)大會(huì)的演講嘉賓，在業(yè)界有著廣泛的影響力。著有多本暢銷書籍，代表作有《收獲，不止Oracle》。文末將有梁老師的新書《收獲，不止SQL優(yōu)化》大彩蛋哦~
SQL優(yōu)化是一個(gè)復(fù)雜的工程，首先要講究從整體到局部。今天我們首先學(xué)習(xí)關(guān)于數(shù)據(jù)庫(kù)整體優(yōu)化都有哪些性能工具，接著分析這些工具的特點(diǎn)，并結(jié)合案例進(jìn)行探索，最后再進(jìn)行總結(jié)和思考。
總體學(xué)習(xí)思路如下圖所示：
都有哪些性能工具
這里首先要分成兩部分：一種是不同調(diào)優(yōu)場(chǎng)景的分析，可分為單純場(chǎng)景的優(yōu)化和復(fù)雜場(chǎng)景的優(yōu)化；而另一種是基于這些場(chǎng)景的工具應(yīng)用，就是針對(duì)單純場(chǎng)景的優(yōu)化手段和復(fù)雜場(chǎng)景的優(yōu)化手段。
1、不同調(diào)優(yōu)場(chǎng)景分析
我們繼續(xù)探討，單純是有多單純呢？哦，其實(shí)可以理解為無菌真空實(shí)驗(yàn)室里的實(shí)驗(yàn)。比如一條SQL很慢，原因是未走高效的索引查詢而走全表掃描，加個(gè)索引就快了，執(zhí)行速度從10s變成了0.1s；或者一條SQL執(zhí)行速度被優(yōu)化到1s左右，邏輯讀控制在50個(gè)左右，應(yīng)該就已經(jīng)OK。這就是單純的環(huán)境，我們差不多無須再考慮優(yōu)化了。
那啥是復(fù)雜呢？那就是，剛才那個(gè)語句加了索引后，本應(yīng)該從10s變成0.1s，結(jié)果還是10s，甚至變成30s了，這是咋回事呢？原來，現(xiàn)在系統(tǒng)是整體出問題了，數(shù)據(jù)庫(kù)主機(jī)資源耗盡，啥語句都跑不快的。
還有那個(gè)邏輯讀在50左右的SQL，如果一天執(zhí)行幾百幾千萬次，這要是能將邏輯讀降低一點(diǎn)，得省多少的邏輯讀啊。原來復(fù)雜環(huán)境真的很復(fù)雜，要考慮SQL本身沒問題而是被環(huán)境影響，還要考慮SQL的執(zhí)行頻率，判斷其調(diào)優(yōu)價(jià)值與調(diào)優(yōu)空間，這些在單純的環(huán)境里，是不用考慮的。
2、不同場(chǎng)景對(duì)應(yīng)工具
接下來，我們說說這兩種場(chǎng)景對(duì)應(yīng)的工具的使用。關(guān)于局部分析調(diào)優(yōu)工具，這個(gè)其實(shí)就是在說SQL的執(zhí)行計(jì)劃了，這是SQL優(yōu)化最重要的手段之一，通過分析執(zhí)行計(jì)劃，我們可以知道SQL語句的訪問路徑，知道它慢在哪里，從而進(jìn)行SQL優(yōu)化。由于在隨后的章節(jié)中我們會(huì)詳細(xì)介紹執(zhí)行計(jì)劃相關(guān)知識(shí)，這里就不再細(xì)述了。
關(guān)于整體的調(diào)優(yōu)工具，這里我們先撇開主機(jī)、網(wǎng)絡(luò)、存儲(chǔ)等層面的因素，暫時(shí)從數(shù)據(jù)庫(kù)的整體層面入手。主要工具有AWR、ASH、ADDM、AWRDD這四個(gè)工具。其中AWR是關(guān)注數(shù)據(jù)庫(kù)的整體性能的報(bào)告；ASH是數(shù)據(jù)庫(kù)中的等待事件與哪些SQL具體對(duì)應(yīng)的報(bào)告；ADDM是Oracle給出的一些建議；而AWRDD是Oracle針對(duì)不同時(shí)段的性能的一個(gè)比對(duì)報(bào)告，比如今天早上9點(diǎn)系統(tǒng)很慢，而昨天這個(gè)時(shí)候很正常，很多人就想知道今天早上9點(diǎn)和昨天早上9點(diǎn)有什么不同，于是就有了這個(gè)報(bào)告。
整體分析調(diào)優(yōu)是必需的，那么我們對(duì)此的學(xué)習(xí)也有規(guī)律可循。首先是獲取系統(tǒng)整體信息的手段，一般通過報(bào)告和日志獲取。好比破案一樣，這就是收集證據(jù)的階段。接下來要找到蛛絲馬跡，那就是如何發(fā)現(xiàn)問題。在本書中就是需要關(guān)注提取到的這些報(bào)告的哪些要點(diǎn)、哪些關(guān)鍵字，具體流程圖如下：
整體性能工具的要點(diǎn)
現(xiàn)代人對(duì)健康都比較重視，每年都會(huì)進(jìn)行健康體檢。其實(shí)數(shù)據(jù)庫(kù)性能工具的應(yīng)用（報(bào)告獲取和關(guān)注要點(diǎn)）和體檢是非常類似的。
1. 報(bào)告的獲取
Oracle性能報(bào)告分成AWR、ASH、ADDM、AWRDD和AWRSQRPT這5個(gè)類型。
什么？這么多，好復(fù)雜啊，記也記不住，我不想聽不想聽！
別急，你只要去醫(yī)院體檢過，你就能聽懂。
Really?
我們?nèi)メt(yī)院體檢，最終會(huì)得到一份體檢報(bào)告，往往能看到很多總體性指標(biāo)，這些指標(biāo)會(huì)判斷你是否健康。沒毛病最好，萬一有毛病，報(bào)告里要進(jìn)一步判斷是什么毛病，是高血壓，還是骨質(zhì)增生，還是胃有毛病……這就是現(xiàn)實(shí)中的體檢報(bào)告。
而Oracle提供的一種性能收集和分析工具，它能提供一個(gè)時(shí)間段內(nèi)整個(gè)系統(tǒng)資源使用情況的報(bào)告，這個(gè)報(bào)告里有很多總體性指標(biāo)來判斷系統(tǒng)是否健康。沒毛病最好，萬一有毛病，問題出在什么模塊，是日志切換過于頻繁，還是硬解析過大，還是某些SQL相關(guān)等待事件在耗資源……這就是AWR報(bào)告。這樣看來，體檢報(bào)告和AWR報(bào)告非常類似。
假設(shè)體檢報(bào)告說你有胃病，很可能只告訴你胃有問題，卻無法告訴你具體啥毛病，因?yàn)槟闶稚系捏w檢報(bào)告不會(huì)詳細(xì)到擁有你胃部所有相關(guān)指標(biāo)。你要得到這些指標(biāo)需要做進(jìn)一步信息收集，那就是胃鏡。同樣假設(shè)你的數(shù)據(jù)庫(kù)是SQL相關(guān)等待事件問題，AWR報(bào)告很可能只告訴你有這個(gè)問題而無法告訴你是哪些SQL引發(fā)的。你要得到這些指標(biāo)，想了解具體某些SQL和相關(guān)等待事件的對(duì)應(yīng)需要做進(jìn)一步的信息收集，那就是ASH報(bào)告。看來對(duì)比胃鏡和ASH報(bào)告，二者也非常類似。
剛才說的胃病，或許是醫(yī)生告訴你的，因?yàn)樯厦嬗泻芏嘀笜?biāo)你無法讀懂，這時(shí)如果你能拿到一張醫(yī)生的病歷卡記錄，這里沒有指標(biāo)，只有白底黑字用文字描述的病情，告訴你要如何治療，那你一定會(huì)看得很明白。同樣假設(shè)，如果將含各種晦澀的指標(biāo)的數(shù)據(jù)庫(kù)體檢報(bào)告用一些白底黑字的文字代替，用文字直接說明數(shù)據(jù)庫(kù)遇到了什么問題，告訴你該如何去優(yōu)化，那新手一定會(huì)看得很明白，這就是ADDM報(bào)告。看來病歷卡記錄和ADDM報(bào)告，二者也非常類似。
假如你在一年前也做過體檢，并將報(bào)告帶到了醫(yī)院，負(fù)責(zé)任的醫(yī)生就一定會(huì)讓你將舊的體檢報(bào)告也提供給他。他會(huì)認(rèn)真地比對(duì)兩張報(bào)告，查看他關(guān)注的健康指標(biāo)是否有異常波動(dòng)，這些波動(dòng)對(duì)醫(yī)生很有參考意義，往往預(yù)示著病情的發(fā)展趨勢(shì)。好了，別緊張，這只是比喻。
假設(shè)你有系統(tǒng)新舊兩個(gè)時(shí)段的兩份AWR報(bào)告，負(fù)責(zé)任的DBA一定會(huì)讓你將舊的AWR報(bào)告也提供給他。他會(huì)認(rèn)真地比對(duì)兩份報(bào)告，查看他關(guān)注的數(shù)據(jù)庫(kù)指標(biāo)是否有異常波動(dòng)，這些波動(dòng)對(duì)DBA很有參考意義，往往預(yù)示著數(shù)據(jù)庫(kù)性能瓶頸的發(fā)展趨勢(shì)。Oracle提供了一個(gè)工具能夠?qū)蓚€(gè)時(shí)段的AWR報(bào)告合并，并能方便地顯示出比對(duì)信息，這個(gè)工具就是AWRDD。看來醫(yī)生分析前后兩次體檢報(bào)告的動(dòng)作和AWRDD報(bào)告比起來，兩者也非常類似。
大家知道做胃鏡是一件很麻煩的事(類似ASH報(bào)告)，如果沒毛病就沒必要讓我們?cè)膺@罪。可萬一體檢報(bào)告無情地告訴你胃有毛病，甚至是醫(yī)生分析你前后兩次體檢報(bào)告（類似ADDM）后告訴你胃病在加速中，你被迫無奈只好去做胃鏡了。做完后醫(yī)生發(fā)現(xiàn)你胃部有大量息肉，卻無法判斷這些息肉是否為良性。于是還要做進(jìn)一步的檢查，這就是活檢。不要緊張，平時(shí)注意健康生活就好。同樣ASH報(bào)告判斷出某些SQL有問題，卻無法得到執(zhí)行計(jì)劃等更詳細(xì)的信息，只能依靠AWRSQRPT去獲取這些信息。看來活檢和AWRSQRPT報(bào)告比起來，兩者也非常類似。
最后恭喜你，活檢報(bào)告顯示未產(chǎn)生癌變，只要好好治療，注意身體，胃就能恢復(fù)健康！看本書的讀者們，你們都是IT人士，生活無規(guī)律加班熬夜者居多，一定要注意身體哦！
對(duì)了，還有一件最重要的事沒交代。大家似乎搞懂了Oracle五大性能報(bào)告，可是這些好東西在哪里才能得到呢？別著急，后續(xù)章節(jié)馬上就會(huì)告訴你如何獲取這五大性能報(bào)告。
2. 報(bào)告的關(guān)注點(diǎn)
如果患者拿著有各種晦澀指標(biāo)的體檢報(bào)告來到門診請(qǐng)教醫(yī)生，他一定會(huì)關(guān)注各種指標(biāo)來判斷患者具體是什么毛病。同樣你也會(huì)對(duì)Oracle的性能報(bào)告中的各種指標(biāo)進(jìn)行關(guān)注來判斷數(shù)據(jù)庫(kù)出了什么毛病。兩者非常類似，關(guān)注不同的指標(biāo)，都是為了施救，前者救人，后者救數(shù)據(jù)庫(kù)。
聽起來是不是很激動(dòng)，恨不得馬上就要開始當(dāng)救庫(kù)英雄了！別急，接下來還要告訴你關(guān)注什么，然后在案例中讓你感受一下什么叫救庫(kù)英雄。
特別提醒：
這里有一個(gè)特別值得注意的地方，那就是性能報(bào)告的采樣時(shí)間。Oracle默認(rèn)是每小時(shí)產(chǎn)生一個(gè)采樣點(diǎn)，你可以收集每個(gè)小時(shí)的性能報(bào)告。我們對(duì)此要敏感，比如你的性能故障是發(fā)生在今天早上7點(diǎn)～8點(diǎn)。然后系統(tǒng)自動(dòng)恢復(fù)了，你獲取一張8點(diǎn)～9點(diǎn)的性能報(bào)告來查問題，就毫無意義了。
3、五大性能報(bào)告的獲取1AWR的獲取與說明
獲取AWR報(bào)告的方式有兩種：一種是直接獲取方式，調(diào)后臺(tái)腳本awrrpt.sql來獲取，執(zhí)行方式一般是在sqlplus下執(zhí)行@?/rdbms/admin/awrrpt.sql；
另一種則是通過調(diào)用命令包，獲取dbms_workload_repository這個(gè)包的awr_report_html程序，用SQL命令的形式輸出內(nèi)容。
Select output from table(dbms_workload_repository.awr_report_html
(v_dbid, v_instance_number,v_min_snap_id,v_max_snap_id))
（1）直接獲取
試驗(yàn)1（未使用批量提交）：
接下來通過提示就可以生成AWR報(bào)告了，具體步驟略去，詳情請(qǐng)掃本章最后的二維碼。
試驗(yàn)2（單機(jī)下，正確使用批量提交）：
接下來通過提示就可以生成awr報(bào)告了，具體步驟略去。
（2）通過調(diào)用命令包獲取
直接調(diào)用工具包的方式，特別適合用在程序自動(dòng)獲取報(bào)告的場(chǎng)景。
注：其中977587123是數(shù)據(jù)庫(kù)的主機(jī)標(biāo)識(shí)，可以在數(shù)據(jù)庫(kù)的數(shù)據(jù)字典中查到，1是標(biāo)識(shí)實(shí)例，如果是RAC，就有1和2兩個(gè)，單機(jī)就只有1。1920和1921是兩個(gè)斷點(diǎn)時(shí)間，比如9點(diǎn)和10點(diǎn)之間。
2ASH的獲取與說明
獲取ASH報(bào)告的方式也有兩種：一種是直接獲取方式，調(diào)后臺(tái)腳本ashrpt.sql來獲取，執(zhí)行方式一般是在sqlplus下執(zhí)行@?/rdbms/admin/ashrpt.sql；另一種則是通過調(diào)用命令包，獲取dbms_workload_repository這個(gè)包的ash_report_html程序。用SQL命令的形式輸出內(nèi)容。
select output from table(dbms_workload_
repository.ash_report_html( dbid,inst_num,l_btime,l_etime)
（1）直接獲取
說明：
如果你是一路回車，就是獲取最近5分鐘的ASH報(bào)告。
如果你根據(jù)Oldest ASH sample available 時(shí)間，然后回車，選擇的是目前可收集的最長(zhǎng)ASH運(yùn)行情況。
你可以選擇Oldest ASH sample available和Latest ASH sample available之間時(shí)間，然后輸入時(shí)長(zhǎng)，比如30表示30分鐘，取你要取的任何時(shí)段的ASH報(bào)告。
ASH報(bào)告的獲取不同于AWR的地方在于，快照之間有無重啟動(dòng)作不影響報(bào)告的獲取。
ASH報(bào)告可以直接手工獲取，比如select output from table(dbms_workload_ repository.ash_report_html( dbid,inst_num,l_btime,l_etime)。
（2）通過調(diào)用命令包獲取
直接調(diào)用工具包的方式，特別適合用在程序自動(dòng)獲取報(bào)告的場(chǎng)景。
注：其中977587123是數(shù)據(jù)庫(kù)的主機(jī)標(biāo)識(shí)，可以在數(shù)據(jù)庫(kù)的數(shù)據(jù)字典中查到，1是標(biāo)識(shí)實(shí)例，如果是RAC，就有1和2兩個(gè)，單機(jī)就只有1。SYSDATE-30/1440,SYSDATE-1/1440 分別是開始時(shí)間和結(jié)束時(shí)間。
3ADDM的獲取與說明
獲取ADDM報(bào)告的方式也有兩種，一種是直接獲取方式：調(diào)后臺(tái)腳本addmrpt.sql來獲取，執(zhí)行方式一般是在sqlplus下執(zhí)行@?/rdbms/admin/addmrpt.sql。另一種則是通過調(diào)用命令包的方式獲取：調(diào)用dbms_workload_repository這個(gè)包的addm_report_html程序。用SQL命令的形式輸出內(nèi)容。
- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name=> 'ADDM',
task_name=> 'MYADDM',
task_desc=> 'MYADDM');
（1）直接獲取
@?/rdbms/admin/addmrpt.sql
具體執(zhí)行過程略去。
（2）通過調(diào)用命令包獲取
注：直接調(diào)用工具包的方式，適合用在自動(dòng)獲取報(bào)告的場(chǎng)景。
4AWRDD的獲取與說明
獲取AWRDD報(bào)告一般是用直接獲取的方式，這個(gè)腳本的交互部分需要輸入要進(jìn)行對(duì)比的兩個(gè)awr報(bào)告的begin snap_id與end snap_id，然后輸入對(duì)比結(jié)果報(bào)告的名稱，這里就不詳細(xì)介紹了，請(qǐng)讀者自行試驗(yàn)完成。
直接獲取：
@?/rdbms/admin/awrddrpt.sql
具體略去。
5AWRSQ獲取與說明
獲取AWRSQRPT報(bào)告的關(guān)鍵之處在于，交互部分要輸入所要分析的SQL的SQL_ID，這是關(guān)鍵之處。而這個(gè)SQL_ID可以從AWR報(bào)告中獲取。
以上5個(gè)報(bào)告的獲取本身并不難，操作一遍就會(huì)了，筆者也會(huì)再提供在線操作視頻，讓大家實(shí)際體會(huì)一遍。現(xiàn)在關(guān)鍵在于，要明白這5個(gè)報(bào)告的作用和相互之間的區(qū)別，搞懂這些，調(diào)優(yōu)之路就算完成過半了。當(dāng)然，接下來如何分析讀懂這五大報(bào)告的關(guān)鍵指標(biāo)就非常重要了，有一些指標(biāo)你必須關(guān)注，否則你就當(dāng)不了“醫(yī)生”了。
4、五大報(bào)告關(guān)注的要點(diǎn)1AWR的關(guān)注點(diǎn)
AWR報(bào)告是五大報(bào)告中最全面最重要的一個(gè)報(bào)告，它的相關(guān)指標(biāo)也顯得格外重要。這里我們列出DB Time、load_profile、efficiency percentages、top 5 events、SQL Statistics、Segment_statistics這6個(gè)指標(biāo)入手分析。
（1）AWR關(guān)注點(diǎn)1之DB Time
DB Time這個(gè)指標(biāo)主要用來判斷當(dāng)前系統(tǒng)有沒有遇到相關(guān)瓶頸，是否較為繁忙導(dǎo)致等待時(shí)長(zhǎng)很長(zhǎng)。一般來說，Elapsed時(shí)間乘以CPU個(gè)數(shù)的時(shí)間如果結(jié)果大于DB Time，我們認(rèn)為系統(tǒng)壓力不大，反之則壓力較大。如下例子中，60.11×64=3847.04<5990.6，說明系統(tǒng)現(xiàn)在還是比較繁忙的。
（2）AWR關(guān)注點(diǎn)2之load_profile
load_profile這個(gè)指標(biāo)主要用來展現(xiàn)當(dāng)前系統(tǒng)的一些指示性能的總體參數(shù)，比如經(jīng)典的Redo size就是用來顯示平均每秒的日志尺寸和平均每個(gè)事務(wù)的日志尺寸，結(jié)合Transactions這個(gè)每秒事務(wù)數(shù)的指標(biāo)，就可以分析出當(dāng)前事務(wù)的繁忙程度。
下圖中顯示每秒有6777.1個(gè)事務(wù)數(shù)，這在現(xiàn)實(shí)中幾乎不可能，現(xiàn)實(shí)中的運(yùn)營(yíng)商系統(tǒng)一般在200上下比較正常，超過1000就屬于非常繁忙了。
把上圖和下面的圖進(jìn)行比較，就非常明顯了，下圖顯示每秒有0.6個(gè)事務(wù)，平均每個(gè)事務(wù)產(chǎn)生的日志尺寸是7位數(shù)。這說明系統(tǒng)是一個(gè)提交不頻繁的處理大任務(wù)事件的系統(tǒng)。而上圖的尺寸是3位數(shù)。這里非常容易看出，這是一個(gè)提交非常頻繁且每個(gè)事務(wù)都非常小的密集提交系統(tǒng)。
（3）AWR關(guān)注點(diǎn)3之efficiency percentages
efficiency percentages是一些命中率指標(biāo)，其中Buffer Hit、Library Hit等都表示SGA（System global area）的命中率。在下圖中Soft Parse指標(biāo)表示共享池的軟解析率，在OLTP系統(tǒng)中如果該指標(biāo)低于90%應(yīng)當(dāng)引起你的注意，這表示存在未使用綁定變量的情況。我們通過比對(duì)兩個(gè)報(bào)告，可以看出明顯差異，如下面系列圖所示。
報(bào)告1（未有效地使用綁定變量，產(chǎn)生大量硬解析的場(chǎng)景）。
報(bào)告2（有效地使用綁定變量，進(jìn)行綁定變量?jī)?yōu)化后的場(chǎng)景）。
（4）AWR關(guān)注點(diǎn)4之top 5 events
等待事件是衡量數(shù)據(jù)庫(kù)整體優(yōu)化情況的重要指標(biāo)，通過觀察Top 5 Timed Foreground Events模塊的Event和%DB time兩列，可以非常直觀地看出當(dāng)前數(shù)據(jù)庫(kù)面臨的主要等待事件是什么。下圖兩個(gè)例子分別告訴我們數(shù)據(jù)庫(kù)面臨鎖等待和日志切換等待的情形。
（5）AWR關(guān)注點(diǎn)5 之SQL Statistics
SQL Statistics分別從幾個(gè)維度來羅列出TOP的SQL，這是一種簡(jiǎn)單粗暴但有效的方法。看看執(zhí)行時(shí)長(zhǎng)，直接拿出來優(yōu)化一般都是對(duì)的做法。
（6）AWR關(guān)注點(diǎn)6 之Segment Statistics
使用Segment Statistics指標(biāo)進(jìn)行尋找和判斷，也是一個(gè)非常直接的優(yōu)化手段。當(dāng)我們知道繁忙落在數(shù)據(jù)庫(kù)的那個(gè)表段是索引段時(shí)，優(yōu)化就變得相對(duì)簡(jiǎn)單了，比如最簡(jiǎn)單粗暴的方法就是對(duì)表和索引進(jìn)行數(shù)據(jù)清理和瘦身。
2ASH的關(guān)注點(diǎn)
ASH是啥？哦，有人想起來了，胃鏡。
完成了ASH報(bào)告的獲取后，打開獲得的ASH報(bào)告，其實(shí)對(duì)于該報(bào)告可關(guān)注的東西非常直接，就是看看哪些SQL和哪些等待事件是相關(guān)聯(lián)的。
如下圖所示：
3ADDM的關(guān)注點(diǎn)
ADDM是啥？哦，是醫(yī)生的門診報(bào)告。
由于這是Oracle的一些分析建議，所以ADDM的閱讀非常簡(jiǎn)單，基本上從FINDING 1、FINDING 2順序往下看就可以了。一般是從數(shù)據(jù)庫(kù)整體配置和局部SQL兩方面給出建議。我們看看都能明白，如下圖所示：
整體性的建議
局部SQL建議
4AWRDD的關(guān)注點(diǎn)
AWRDD是啥？哦，是醫(yī)生在看你前后兩次體檢報(bào)告，在比較指標(biāo)的變化。其實(shí)這個(gè)關(guān)注點(diǎn)很簡(jiǎn)單，基本上就是AWR關(guān)注什么，AWRDD就關(guān)注什么，沒什么特別的，簡(jiǎn)單列舉如下。
（1）AWRDD關(guān)注點(diǎn)1 之不同時(shí)期load profile的比較
（2）AWRDD關(guān)注點(diǎn)2 之不同時(shí)期等待事件的比較
（3）AWRDD關(guān)注點(diǎn)3 之不同時(shí)期TOP SQL的比較
5AWRSQRPT的關(guān)注點(diǎn)
AWRSQRPT是啥？哦，有人想起來了，活檢。別打顫！
其實(shí)沒啥，就是看看AWR和ASH里看不到的東西。都有啥呢？比如執(zhí)行計(jì)劃的相關(guān)細(xì)節(jié)，關(guān)于執(zhí)行計(jì)劃我們會(huì)在后面詳細(xì)說明。這里要特別注意一點(diǎn)，Oracle的執(zhí)行計(jì)劃可能會(huì)隨著環(huán)境的變化而變化，會(huì)隨著數(shù)據(jù)的變化而變化，因此可能會(huì)產(chǎn)生多個(gè)執(zhí)行計(jì)劃，這個(gè)AWRSQRPT就會(huì)出現(xiàn)多個(gè)執(zhí)行計(jì)劃。具體詳見下面系列圖。
（1）Plan statistics
（2）Execution Plan
（3）是否有多個(gè)執(zhí)行計(jì)劃
案例的分享與交流
說了這么多，我們來看幾個(gè)相關(guān)案例，體會(huì)使用工具進(jìn)行整體優(yōu)化的重要性。
1、和并行等待有關(guān)的案例
這是來自某政府系統(tǒng)的一個(gè)平臺(tái)的案例，請(qǐng)看下圖，這是AWR報(bào)告的Top 5 Timed Events的展現(xiàn)，可以看出當(dāng)前數(shù)據(jù)庫(kù)的等待事件主要是PX Deq相關(guān)的等待，這屬于濫用并行等待導(dǎo)致系統(tǒng)資源緊張的一個(gè)案例。
該案例暴露出的問題比想象中更嚴(yán)重，因?yàn)樵撓到y(tǒng)的不少表和索引的屬性被設(shè)置了并行度，這導(dǎo)致所有對(duì)這些表和索引的訪問都成了并行訪問。后續(xù)解決思路就是將表和索引的并行屬性去掉。將一些需要并行處理的大任務(wù)進(jìn)行時(shí)間切割，確認(rèn)部分大任務(wù)是可以放在凌晨業(yè)務(wù)低峰期執(zhí)行的，就設(shè)置了并行的Hint任務(wù)，讓部分SQL在夜間并行執(zhí)行，大部分SQL在白天正常執(zhí)行，從而系統(tǒng)恢復(fù)正常，業(yè)務(wù)也能順利開展。
2、和熱塊競(jìng)爭(zhēng)有關(guān)的案例
接下來我們?cè)倏匆粋€(gè)案例，這是某運(yùn)營(yíng)商的系統(tǒng)，從AWR報(bào)告的Top 5 Timed Events等待事件主要是gc buffer busy來看，當(dāng)前系統(tǒng)主要等待事件是熱塊競(jìng)爭(zhēng)的等待。
等待事件對(duì)應(yīng)的SQL主要有哪些，我們其實(shí)可以通過對(duì)應(yīng)時(shí)間段的ASH報(bào)告分析出來，比如下圖就是和AWR的對(duì)應(yīng)。
將AWR報(bào)告和ASH報(bào)告結(jié)合起來看，往往可以找出具體需要優(yōu)化的SQL。在本案例中，我們發(fā)現(xiàn)兩個(gè)節(jié)點(diǎn)共同訪問一些對(duì)象導(dǎo)致熱塊競(jìng)爭(zhēng)。后續(xù)通過一系列改造，讓不同的業(yè)務(wù)跑在不同的節(jié)點(diǎn)上，從而避免了兩個(gè)節(jié)點(diǎn)訪問同一個(gè)對(duì)象，問題得以緩解。
3、和日志等待有關(guān)的案例
這是一個(gè)典型的案例，從Transactions達(dá)到800多，可以看出事務(wù)非常繁忙，再?gòu)腜er Transaction才1000左右，可以看出每個(gè)事務(wù)非常小。這說明了系統(tǒng)存在事務(wù)未批量提交的情況。這種情況一般出現(xiàn)在循環(huán)中，把提交寫到循環(huán)里面的情況。后續(xù)通過排查，發(fā)現(xiàn)果真是如此原因。
接下來的log file switch(checkpoint incomplete) 和log file sync的相關(guān)等待正是由于日志切換過于頻繁導(dǎo)致的等待，這正是如前所述，未批量提交導(dǎo)致。
4、新疆某系統(tǒng)的前臺(tái)優(yōu)化
如下是新疆某運(yùn)營(yíng)商的優(yōu)化案例，我們通過Top 5 Timed Events等待事件發(fā)現(xiàn)了瓶頸主要在IO。接下來我們迅速到Tablespace IO Stats模塊去查看，如下圖所示：
果然是有點(diǎn)問題。這個(gè)AV RD(MS)項(xiàng)表示平均一次物理讀花費(fèi)的時(shí)間（單位為ms）。有一種說法是， AV RD(MS)大于7就說明系統(tǒng)有嚴(yán)重的IO問題，其中BOSSWG_PERF_DATA居然達(dá)到了47，這說明當(dāng)前的存儲(chǔ)IO存在瓶頸。后續(xù)通過改善存儲(chǔ)解決了問題。
5、浙江某系統(tǒng)的調(diào)優(yōu)案例
這個(gè)案例來自浙江某生產(chǎn)系統(tǒng)，我們通過Top 5 Timed Events等待事件發(fā)現(xiàn)了瓶頸主要在gc buffer busy等待事件，這和新疆某系統(tǒng)的前臺(tái)優(yōu)化案例類似。不過AWR報(bào)告非常強(qiáng)大，你通過各個(gè)細(xì)節(jié)都可以很有收獲，從而找到解決問題的方法，比如你此時(shí)直接定位到Segments by Global Cache Buffer Busy模塊，如下圖所示：
通過觀察segments by global cache buffer busy的對(duì)象，我們找到了相關(guān)需要優(yōu)化的表。最后我們結(jié)合業(yè)務(wù)，通過對(duì)該表瘦身、增加分區(qū)、避免兩個(gè)節(jié)點(diǎn)同時(shí)訪問的方案，優(yōu)化了對(duì)應(yīng)SQL的性能。
總結(jié)
End.
來源：公眾號(hào)“DBAplus社群”
運(yùn)行人員：中國(guó)統(tǒng)計(jì)網(wǎng)小編（微信號(hào)：itongjilove）
微博ID：中國(guó)統(tǒng)計(jì)網(wǎng)
中國(guó)統(tǒng)計(jì)網(wǎng)，是國(guó)內(nèi)最早的大數(shù)據(jù)學(xué)習(xí)網(wǎng)站，公眾號(hào)：中國(guó)統(tǒng)計(jì)網(wǎng)
http://www.itongji.cn
AWR 是 Oracle 10g 版本 推出的新特性， 全稱叫Automatic Workload Repository-自動(dòng)負(fù)載信息庫(kù) AWR 是通過對(duì)比兩次快照(snapshot)收集到的統(tǒng)計(jì)信息，來生成報(bào)表數(shù)據(jù)，生成的報(bào)表包括多個(gè)部分。
前面已經(jīng)對(duì)awr報(bào)告的WORKLOAD REPOSITORY report部分做了介紹，下面根據(jù)生產(chǎn)環(huán)境對(duì)Report Summary部分的一些關(guān)鍵參數(shù)做分析，內(nèi)容比較多，大家盡量耐心看完，還是有點(diǎn)用的。
這里主要顯示SGA中每個(gè)區(qū)域的大小（在AMM改變它們之后），可用來與初始參數(shù)值比較。
Buffer Cache：最終目的就是盡可能的減少磁盤I/O以便快速的讀或?qū)憽Ｒ膊皇窃酱笤胶茫绻鸅uffer Cache過大，會(huì)造成大的LRU 列表和 dirty list，引發(fā)邏輯讀的過程消耗CPU量高。同時(shí)大的Buffer Cache也會(huì)增加DBWn 進(jìn)程的負(fù)擔(dān)。
shared pool主要包括library cache和dictionary cache。
library cache用來存儲(chǔ)最近解析（或編譯）后SQL、PL/SQL和Java classes等。 dictionary cache用來存儲(chǔ)最近引用的數(shù)據(jù)字典。
發(fā)生在library cache或dictionary cache的cache miss代價(jià)要比發(fā)生在buffer cache的代價(jià)高得多。因此shared pool的設(shè)置要確保最近使用的數(shù)據(jù)都能被cache。
我們可以看到shared pool一直收縮，在shrink過程中一些row cache 對(duì)象被lock住可能導(dǎo)致前臺(tái)row cache lock等解析等待，最好別讓shared pool shrink。如果這里shared pool一直在grow，那說明shared pool原有大小不足以滿足需求(可能是大量硬解析)，結(jié)合后面的解析信息和SGA breakdown來一起診斷問題。
這里可以看到生產(chǎn)的硬解析每秒為102，硬解析為7.5，問題不大，但是Logons每秒3.3，表明可能有爭(zhēng)用問題，下面針對(duì)每個(gè)指標(biāo)具體分析：
這里主要顯示數(shù)據(jù)庫(kù)負(fù)載概況，將之與基線數(shù)據(jù)比較才具有更多的意義，如果每秒或每事務(wù)的負(fù)載變化不大，說明應(yīng)用運(yùn)行比較穩(wěn)定。
單個(gè)的報(bào)告數(shù)據(jù)只說明應(yīng)用的負(fù)載情況，絕大多數(shù)據(jù)并沒有一個(gè)所謂“正確”的值，Logons大于每秒1~2個(gè)、Hard parses大于每秒100、全部parses超過每秒300表明可能有爭(zhēng)用問題。
上述所有指標(biāo) 的目標(biāo)均為100%，即越大越好，在少數(shù)bug情況下可能超過100%或者為負(fù)值。
80%以上 %Non-Parse CPU
90%以上 Buffer Hit%, In-memory Sort%, Soft Parse%
95%以上 Library Hit%, Redo Nowait%, Buffer Nowait%
98%以上 Latch Hit%
這里主要顯示Oracle關(guān)鍵指標(biāo)的內(nèi)存命中率及其它數(shù)據(jù)庫(kù)實(shí)例操作的效率。其中Buffer Hit Ratio 也稱Cache Hit Ratio，
Library Hit ratio也稱Library Cache Hit ratio。
在一個(gè)使用直接讀執(zhí)行大型并行查詢的DSS環(huán)境，20%的Buffer Hit Ratio是可以接受的，而這個(gè)值對(duì)于一個(gè)OLTP系統(tǒng)是完全不能接受的。
根據(jù)多年的經(jīng)驗(yàn)，對(duì)于OLTP系統(tǒng)，Buffer Hit Ratio理想應(yīng)該在90%以上。
Buffer Nowait表示在內(nèi)存獲得數(shù)據(jù)的未等待比例。在緩沖區(qū)中獲取Buffer的未等待比率
Buffer Nowait的這個(gè)值一般需要大于99%。否則可能存在爭(zhēng)用
buffer hit 高速緩存命中率，反應(yīng)物理讀和緩存命中間的糾結(jié)，表示進(jìn)程從內(nèi)存中找到數(shù)據(jù)塊的比率，監(jiān)視這個(gè)值是否發(fā)生重大變化比這個(gè)值本身更重要
Redo NoWait表示在LOG緩沖區(qū)獲得BUFFER的未等待比例。如果太低（可參考90%閥值），考慮增加LOG BUFFER。
當(dāng)redo buffer達(dá)到1M時(shí)，就需要寫到redo log文件，所以一般當(dāng)redo buffer設(shè)置超過1M，不太可能存在等待buffer空間分配的情況。
library hit表示Oracle從Library Cache中檢索到一個(gè)解析過的SQL或PL/SQL語句的比率，當(dāng)應(yīng)用程序調(diào)用SQL或存儲(chǔ)過程時(shí)，
Oracle檢查L(zhǎng)ibrary Cache確定是否存在解析過的版本，如果存在，Oracle立即執(zhí)行語句；如果不存在，Oracle解析此語句，并在Library Cache中為它分配共享SQL區(qū)。
Latch Hit：Latch是一種保護(hù)內(nèi)存結(jié)構(gòu)的鎖，可以認(rèn)為是SERVER進(jìn)程獲取訪問內(nèi)存數(shù)據(jù)結(jié)構(gòu)的許可。
要確保Latch Hit>99%，否則意味著Shared Pool latch爭(zhēng)用，可能由于未共享的SQL，或者Library Cache太小，可使用綁定變更或調(diào)大Shared Pool解決。
要確保>99%，否則存在嚴(yán)重的性能問題。
Parse CPU to Parse Elapsd：解析實(shí)際運(yùn)行時(shí)間/(解析實(shí)際運(yùn)行時(shí)間+解析中等待資源時(shí)間)，越高越好。即：解析實(shí)際運(yùn)行時(shí)間/(解析實(shí)際運(yùn)行時(shí)間+解析中等待資源時(shí)間)。如果該比率為100%，意味著CPU等待時(shí)間為0，沒有任何等待。
Non-Parse CPU ：SQL實(shí)際運(yùn)行時(shí)間/(SQL實(shí)際運(yùn)行時(shí)間+SQL解析時(shí)間)，太低表示解析消耗時(shí)間過多。
計(jì)算公式為：% Non-Parse CPU=round(100*1-PARSE_CPU/TOT_CPU),2)。如果這個(gè)值比較小，表示解析消耗的CPU時(shí)間過多。
Soft Parse：軟解析的百分比（softs/softs+hards），近似當(dāng)作sql在共享區(qū)的命中率
In-memory Sort：在內(nèi)存中排序的比率，如果過低說明有大量的排序在臨時(shí)表空間中進(jìn)行。
Execute to Parse：是語句執(zhí)行與分析的比例，如果要SQL重用率高，則這個(gè)比例會(huì)很高。該值越高表示一次解析后被重復(fù)執(zhí)行的次數(shù)越多。
Memory Usage %：對(duì)于一個(gè)已經(jīng)運(yùn)行一段時(shí)間的數(shù)據(jù)庫(kù)來說，共享池內(nèi)存使用率，應(yīng)該穩(wěn)定在75%-90%間，
如果太小，說明Shared Pool有浪費(fèi)，而如果高于90，說明共享池中有爭(zhēng)用，內(nèi)存不足。
這個(gè)數(shù)字應(yīng)該長(zhǎng)時(shí)間穩(wěn)定在75%～90%。如果這個(gè)百分比太低，表明共享池設(shè)置過大，帶來額外的管理上的負(fù)擔(dān)，從而在某些條件下會(huì)導(dǎo)致性能的下降。如果這個(gè)百分率太高，會(huì)使共享池外部的組件老化，如果SQL語句被再次執(zhí)行，這將使得SQL語句被硬解析。這里顯示生產(chǎn)環(huán)境比率低于75%，沒有充分利用shared pool。
SQL with executions>1：執(zhí)行次數(shù)大于1的sql比率，如果此值太小，說明需要在應(yīng)用中更多使用綁定變量，避免過多SQL解析。
在一個(gè)趨向于循環(huán)運(yùn)行的系統(tǒng)中，必須認(rèn)真考慮這個(gè)數(shù)字。在這個(gè)循環(huán)系統(tǒng)中，在一天中相對(duì)于另一部分時(shí)間的部分時(shí)間里執(zhí)行了一組不同的SQL語句。
在共享池中，在觀察期間將有一組未被執(zhí)行過的SQL語句，這僅僅是因?yàn)橐獔?zhí)行它們的語句在觀察期間沒有運(yùn)行。只有系統(tǒng)連續(xù)運(yùn)行相同的SQL語句組，這個(gè)數(shù)字才會(huì)接近100%。
Memory for SQL w/exec>1：執(zhí)行次數(shù)大于1的SQL消耗內(nèi)存的占比。
這是與不頻繁使用的SQL語句相比，頻繁使用的SQL語句消耗內(nèi)存多少的一個(gè)度量。
這個(gè)數(shù)字將在總體上與% SQL with executions>1非常接近，除非有某些查詢?nèi)蝿?wù)消耗的內(nèi)存沒有規(guī)律。
在穩(wěn)定狀態(tài)下，總體上會(huì)看見隨著時(shí)間的推移大約有75%～85%的共享池被使用。如果Statspack報(bào)表的時(shí)間窗口足夠大到覆蓋所有的周期，
執(zhí)行次數(shù)大于一次的SQL語句的百分率應(yīng)該接近于100%。這是一個(gè)受觀察之間持續(xù)時(shí)間影響的統(tǒng)計(jì)數(shù)字。可以期望它隨觀察之間的時(shí)間長(zhǎng)度增大而增大。
總結(jié)：通過ORACLE的實(shí)例有效性統(tǒng)計(jì)數(shù)據(jù)，我們可以獲得大概的一個(gè)整體印象，但是不能由此來確定數(shù)據(jù)運(yùn)行的性能。當(dāng)前性能問題的確定，我們主要還是依靠后面介紹的等待事件來確認(rèn)。
可以這樣理解兩部分的內(nèi)容，hit統(tǒng)計(jì)幫助我們發(fā)現(xiàn)和預(yù)測(cè)一些系統(tǒng)將要產(chǎn)生的性能問題，這樣可以做到未雨綢繆。而wait事件，就是表明當(dāng)前數(shù)據(jù)庫(kù)已經(jīng)出現(xiàn)了性能問題需要解決，所以是亡羊補(bǔ)牢的性質(zhì)。
后面會(huì)分享更多DBA方面的內(nèi)容，感興趣的朋友可以關(guān)注下！！
*請(qǐng)認(rèn)真填寫需求信息，我們會(huì)在24小時(shí)內(nèi)與您取得聯(lián)系。
