oracle效能調優

1.記憶體調優
注：SGA中的shared pool中，系統自動優先快取datadictionary cache，對系統效能影響較大的是library cache。
1.1 library cache 調優（v$librarycache/v$sql/v$sqlarea/v$sqltext/v$db_object_cache）
判斷：要不要調整library cache？判斷條件：v$librarycache.pinhitratio|gethitratio儘量接近100%
1.1.1 儲存物件所消耗的記憶體預估：
select sum(SHARABLE_MEM) from v$db_object_cache;
1.1.2 經常執行的sql消耗的記憶體預估：
select sum(SHARABLE_MEM) from v$sqlarea where EXECUTIONS>100;
1.1.3 每個使用者開啟遊標，需要250個位元組：
select sum(250*USERS_OPENING) from v$sqlarea;
1.1.4 keep在shared pool中：
先建立dbms_shared_pool：
@?/rdbms/admin/dbmspool.sql
建立後：
execute dbms_shared_pool.keep(’package_name’);———————-將package keep
execute dbms_shared_pool.keep(’address,hash_value’);—————-將匿名的塊 keep

1.2 data dictionary cache 調優（v$rowcache）
判斷：要不要調整data dictionary cache？
判斷標準：v$rowcache.(gets-getmisses)/gets儘量接近100%
1.2.1 由於沒有單獨調整資料字典高速緩衝的引數，但是系統又優先快取data dictionary cache，只能調整shared pool的總體大小。

1.3 UGA
1.3.1 使用共享伺服器模式的時候，如沒有配置large pool，使用者會話資料（排序等等）和遊標狀態（共享sql）會在SGA。
因此如果發現一個報表系統shared pool特別大，有沒有配置large pool，請檢查是否使用了共享伺服器模式。
1.3.2 UGA預估：
SELECT ‘current session uga memory’,SUM(VALUE)/1024 size_kb FROM v$mystat a,v$statname b
WHERE a.STATISTIC#=b.STATISTIC#
AND b.NAME IN (’session uga memory’)
GROUP BY NAME
UNION ALL
SELECT decode(name,’session uga memory’,'all session uga memory’,name),SUM(VALUE)/1024 size_kb FROM v$sesstat a,v$statname b
WHERE a.STATISTIC#=b.STATISTIC#
AND b.NAME IN (’session uga memory’,’session uga memory max’)
GROUP BY NAME;

1.4 db buffer cache（V$sysstat/v$buffer_pool_statistics/v$buffer_pool/v$sysstat/v$sess_io/v$wait_stat）
1.4.1 db buffer caceh和datafile之間的互動：

——DBWn——–>
buffer cache datafile

1.4.2 db buffer cache和LRU列表：
傳入的塊從LRU的冷端複製到緩衝區中，然後該緩衝區的資料會放到LRU列表的中部，在根據情況上下浮動。

1.4.3 DB_CACHE_ADVICE off-ready-on

1.4.4 dbwr什麼時候將dirty buffer寫出：A.checkpoint 佇列到達閾值；B.LRU中顯示沒有空餘的buffer；C.logwr指示已經發出checkpoint訊號；D.表空間變化；E.Drop object;F.clean shutdown

判斷：要不要調整db cache size：命中率小於90%
判斷標準：SQL> SELECT 1 - (phy.value – lob.value – dir.value)
/ ses.value “CACHE HIT RATIO”
2 FROM v$sysstat ses, v$sysstat lob,
3 v$sysstat dir, v$sysstat phy
3 WHERE ses.name = ’session logical reads’
4 AND dir.name = ‘physical reads direct’
5 AND lob.name = ‘physical reads direct (lob)’
6 AND phy.name = ‘physical reads’;
注：因為直接讀和lob的讀取不經過buffer pool快取，因此整個physical read要減去這2項。
啟動開始有較多的物理讀，會影響buffer caceh命中率的統計

1.4.5 影響buffer cache的因素：全表掃描、程式、命中率分佈不均、可隨機訪問的大表（其實也是全表掃描）
1.4.6 keep一般為default的10%，recycle一般是default的2倍
1.4.7 空閒列表 ：A.單cpu增加空閒列表不會增加效能 B.

1.4.8 相關檢視： v$sess_io:預估recycle

1.5 log buffer
1.5.1 診斷標準：A.v$sysstat中的redo buffer allocation retries/redo entries<10% B.$session_wait中不應出現log buffer space
1.5.2 對策：加大log buffer，或者將relog轉移到效能好的磁碟上
1.5.3 深入分析，lgwr變慢的原因：lgwr之前和之後的工作是在logbuffer和redolog中，分析這2者的情況：
1.5.3.1 redolog太小，而logbuffer較大，因此需要頻繁的切換redolog，當本次切換還未完成時，又輪到下次切換，出現等待（表現為system_event中的log file switch completion%過多）
1.5.3.2 redolog太大或者redolog組不夠，當上次的logbuffer中的內容還沒完全寫入到redolog中，需要用到這塊的logbuffer了，出現等待，即redolog為高active狀態(非current和inactive，未完成checkpoint)，需要加多redolog組或者調整調整FAST_START_MTTR_TARGET，加大checkpoint頻率（表現為system_event中'log file switch (check%';檢查點未完成過多）
1.5.3.3 redolog切換不了，可能是寫archlog過慢，或者archlog空間爆滿。（表現為system_event中log file switch (arch%';archlog未完成過多）

1.6 java pool 的最佳化
判斷：要不要調整java pool：v$sgastat中java pool 的free memory 和memory in use的比例

2 IO最佳化
2.1 v$filestat
2.2 條帶化：A.raid B.資料檔案條帶化 C.alter table allocate extent
2.3 增加db_file_multiblock_read_count，減少IO次數
2.4 減少v$sysstat中的long tables（大於4 blocks的表）
2.5 關於checkpoint：
2.5.1 增量chkpt：
checkpoint佇列：1.dirty buffer：第一次資料塊被修改時就寫入chkpt佇列中。2.RBA：redolog中第一次修改資料塊的RBA也被記錄在chkpt佇列
每隔3秒做一次增量chkpt，不強制寫資料檔案，只是將chkpt佇列中的第一個（即時間至今最長的一個）RBA寫入到控制檔案，例項crash的恢復，即redolog中該RBA之後的內容。
增量chkpt發生條件：1.每隔3秒做增量chkpt，更新控制檔案；2.switch logfile，更新控制檔案和資料檔案頭
2.5.2 全chkpt：
完整檢查點：1.dirty buffer寫入到datafile ；2.clean shutdown；3.alter system checkpoint
表空間檢查點：表空間變化時（backup、offline）

3 排序最佳化
3.1 sort_area_size和sort_area_retained_size
3.2 CREATE_BITMAP_AREA_SIZE和BITMAP_MERGE_AREA_SIZE:建立索引速度有關
3.3 需要排序的操作：1.建檢索 2.維護索引 3.order by/group by/distinct/union/minus/intersect 4.Sort Merge Join 5.analyze
3.4 避免排序的操作：1.create index nosort（要求事先已經asc排序） 2.union all 3.analyze for column 4.analyze estimate
3.5 判斷：要不要調整排序區：(v$sysstat.name='sorts (disk)'/v$sysstat.name='sorts (memory)')<5% 注意：dss中該比例不適用。
3.6 對策：增加sort_area_size和PGA_AGGREGATE_TARGET

4 latch最佳化
4.1 重要的latch free
4.1.1 shared pool和library cache的：sql沒有繫結變數(檢查v$sqlarea.parse_call,v$sqlarea.EXECUTIONS)或者存在熱塊或者為共享伺服器模式但是沒有配large pool
4.1.2 db buffer cache的lru：基於db buffer cache大量的排序或者大量的index full scan和full table scan

5 undo最佳化
5.1 undo使用：（1）讀一致性 （2）dml的事務回退 （3）事務故障恢復，開啟資料庫時rollback未commit的事務
5.2 何時需要加回退段（手工管理）：
SELECT sum(waits)* 100 /sum(gets) "Ratio",
sum(waits) "Waits", sum(gets) "Gets"
FROM v$rollstat;

SELECT event, total_waits, total_timeouts
FROM v$system_event
WHERE event LIKE 'undo segment tx slot';

SELECT class, count FROM v$waitstat
WHERE class LIKE '%undo%';
5.3 大事務指定回滾段（提交後結束）：
sys@ORA11G(192.168.0.11)> set transaction use rollback segment “_SYSSMU1_1193229161$”;

事務處理集。

5.4 shared server
題外話：判斷是否shared server：
(1).show parameter shared_server 大於0為共享伺服器模式
(2).select server from v$session 看到DEDICATED為專用；看到SHARED即為共享，且shared_server_process正在對其其他服務；看到NONE為也為共享，且shared_server_process尚未對其其他服務。

5.4.1 監控檢視：V$SHARED_SERVER_MONITOR
V$DISPATCHER繁忙率=busy/(busy+idle) 等待時間=wait/totalq
V$DISPATCHER_RATE
5.4.2 調整引數：SHARED_SERVERS–共享伺服器程式的數量
DISPATCHERS—–排程程式的數量

5.5 sql調優
5.5.1 optimizer_mode:choose(預設，如果涉及的任一表有統計資訊，就用CBO的all_rows，否則使用RBO)
5.5.2 執行計劃中的資訊：行訪問方法，連線順序，連線方法，分散式事務訪問（不包含遠端節點），子查詢
5.5.3 建立儲存概要：引數檔案 CREATE_STORED_OUTLINES——————————————指定建立儲存概要的名稱（可取名為OLTP、DSS，或者TRUE、FALSE）。
引數檔案 USE_STORED_OUTLINES———————————————指定需要使用的儲存概要名稱（可取名為OLTP、DSS，或者TRUE、FALSE，在session級或system級指定後select。
引數檔案 USE_PRIVATE_OUTLINES——————————————–專用概要，僅用於當前會話，如不存在，不使用共用的概要。
create or replace outline XXX for catalog OLTP on select ……————–建立儲存概要的名稱。
5.5.4 trace:
5.5.4.1 @?/rdbms/admin/utlxplan.sql
explain plan for
@?/rdbms/admin/utlxpls.sql or @?/rdbms/admin/utlxplp.sql or select * from table(dbms_xplan.display);
5.5.4.2 alter session set sql_trace=true;
tkprof
5.5.4.3 exec dbms_session.set_sql_trace=true;
5.5.4.4 exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
5.5.4.5 set autotrace on

5.5.5 表的統計資訊包含：NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,ROW_MOVEMENT,AVG_ROW_LEN,LAST_ANALYZED (dba_tables)
5.5.6 索引的統計資訊包含：DEGREE,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS,CLUSTERING_FACTOR (dba_indexes)
5.5.7 列統計資訊包含：NUM_DISTINCT,LOW_VALUE,HIGH_VALUE(DBA_TAB_COL_STATISTICS)
5.5.8 直方圖：EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>’HR’,TABNAME=>’EMPLOYEES’, METHOD_OPT => ‘FOR COLUMNS salary SIZE 10′);
5.5.9 dbms_stats.gather_system_stats:收集資訊
dbms_stats.get_system_stats:驗證統計資訊
dbms_stats.set_system_stats:明確設定系統統計資訊

5.6 塊調優
5.6.1 將extent大小設定為5*DB_FILE_MULTIBLOCK_READ_COUNT的倍數。（原因：一個extent=5×DB_FILE_MULTIBLOCK_READ_COUNT），注：對非全表掃描，該最佳化無效。
5.6.2 HWM:以5 block遞增，
5.6.3 減少行遷移：提高pctfree

5.7 索引調優
5.7.1 重建浪費大於20%的索引:
analyze index index_name validate structure;
SELECT name, (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS wastage FROM index_stats;
5.7.2 找出未使用的索引
alter index index_name monitoring usage;
alter index index_name nomonitoring usage;
select index_name,used from v$object_usage;
5.7.3 注函式索引必須在CBO下使用，對RBO無效

6 應用調優
6.1 資料訪問型別：
6.1.1 聚簇：使用同一個實體地址存放兩個或者多個表。先建簇，再建簇表，再向簇表insert資料。分兩類：hash 和 index
6.1.2 b-tree索引：葉塊填滿後，將分成兩半，50%在舊葉塊，50%到新葉塊；分支類推，一直到根
6.1.3 點陣圖索引：lock點陣圖段
6.1.4 反向索引：為了避免降低索引高度。
6.1.5 IOT表：注：IOT表上可使用點陣圖索引，但是需要建立map。
6.1.6 分割槽表：若range 分割槽中可能含有null，需要設定maxvalue
若hash，為了資料分佈均勻，分割槽個數需設定為2的n次方。提高單鍵查詢的效率，不提高range scan的效率。
若list，需提前瞭解有多少種類
若複合，支援range-hash和range-list
6.1.7 分割槽索引：local index：與表分割槽一一對應關係;可以是list、hash、range、compsite任一種類;bitmap索引必須是local index
global index：與表分割槽可以是多對一的關係;只能是range且需設定maxvlaue;表分割槽變化後需重建。
另外的分法：
prefix index：分割槽的key為組合索引的最左端欄位;可以是唯一索引也可以是非唯一索引
non-prefix index：分割槽的key不是組合索引的最左端欄位;可以是唯一索引也可以是非唯一索引;唯一的non-prefix index條件：分割槽鍵是組合索引的子集。
注：non-prefix的global index不存在。
6.1.8 使用物化檢視