1. 規範磁盤陣列
RAID 10比RAID5更適用於OLTP系統，RAID10先鏡像磁盤，再對其進行分段，由於對數據的小規模訪問會比較頻繁，所以對OLTP適用。而RAID5,優勢在於能夠充分利用磁盤空間，並且減少陣列的總成本。但是由於陣列發出壹個寫入請求時，必須改變磁盤上已修改的塊，需要從磁盤上讀取“奇偶校驗”塊，並且使用已修改的塊計算新的奇偶校驗塊，然後把數據寫入磁盤，且會限制吞吐量。對性能有所影響，RAID5適用於OLAP系統。
2. 數據文件分布
分離下面的東西,避免磁盤競爭
SYSTEM表空間 TEMPORARY表空間 UNDO表空間 聯機重做日誌（放在最快的磁盤上） 操作系統磁盤 ORACLE安裝目錄 經常被訪問的數據文件 索引表空間 歸檔區域（應該總是與將要恢復的數據分離）例：
/: System /u01: Oracle Software /u02: Temporary tablespace, Control file1 /u03: Undo Segments, Control file2 /u04: Redo logs, Archive logs, Control file4 /u05: System, SYSAUX tablespaces /u06: Data1 ,control file3 /u07: Index tablespace /u08: Data2通過下列語句查詢確定IO問題
select name ,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file# order by readtim desc;
3. 增大日誌文件
u 增大日誌文件的大小，從而增加處理大型INSERT,DELETE,UPDATE操作的比例
查詢日誌文件狀態
select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#
查詢日誌切換時間
select b.RECID,to_char(b.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') start_time,a.RECID,to_char(a.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.FIRST_TIME-b.FIRST_TIME)*25)*60,2) minutes
from v$log_history a ,v$log_history b
where a.RECID=b.RECID+1
order by a.FIRST_TIME desc
增大日誌文件大小，以及對每組增加日誌文件（壹個主文件、壹個多路利用文件）
u 增大LOG_CHECKPOINT_INTERVAL參數，現已不提倡使用它
如果低於每半小時切換壹次日誌，就增大聯機重做日誌大小。如果處理大型批處理任務時頻繁進行切換，就增大聯機重做日誌數目。
alter database add logfile member ‘/log.ora’ to group 1;
alter database drop logfile member ‘/log.ora’;
4. UNDO表空間
修改三個初始參數：
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=CLOUDSEA_UNDO
UNDO_RETENTION=<#of minutes>
5. 不要在系統表空間中執行排序
二、 初始化參數調優
32位的尋址最大支持應該是2的32次方，就是4G大小。但實際中32位系統（XP，windows2003等MS32位系統, ubuntu等linux32 位系統）要能利用4G內存，都是采用內存重映射技術。需要主板及系統的支持。如果關閉主板BIOS的重映射功能，系統將不能利用4G內存，可能只達3.5G.而在windows下看到的壹般為3.25G。所以SGA設置為內存的40%，但不能超過3.25G
1. 重要初始化參數
l SGA_MAX_SIZE
l SGA_TARGET
l PGA_AGGREGATE_TARGET
l DB_CACHE_SIZE
l SHARED_POOL_SIZE
2. 調整DB_CACHE_SIZE來提高性能
它設定了用來存儲和處理內存中數據的SGA區域大小，從內存中取數據比磁盤快10000倍以上
根據以下查詢出數據緩存命中率
select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets',value,0)) con_gets,
(1- (sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)) ) ))*100 Hitratio
from v$sysstat;
壹個事務處理程序應該保證得到95%以上的命中率，命中率從90%提高到98%可能會提高500%的性能，ORACLE正在通過CPU或服務時間與等待時間來分析系統性能，不太重視命中率，不過現在的庫緩存和字典緩存仍將命中率作為基本的調整方法。
在調整DB_CACHE_SIZE時使用V$DB_CACHE_ADVICE
select size_for_estimate, estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where name = 'DEFAULT';
如果查詢的命中率過低，說明缺少索引或者索引受到限制，通過V$SQLAREA視圖查詢執行緩慢的SQL
3. 設定DB_BLOCK_SIZE來反映數據讀取量大小
OLTP壹般8K
OLAP壹般16K或者32K
4. 調整SHARED_POOL_SIZE以優化性能
正確地調整此參數可以同等可能地***享SQL語句，使得在內存中便能找到使用過的SQL語句。為了減少硬解析次數，優化對***享SQL區域的使用，需盡量使用存儲過程、使用綁定變量
保證數據字典緩存命中率在95%以上
select ((1- sum(getmisses)/(sum(gets)+sum(getmisses)))*100) hitratio
from v$rowcache
where gets+getmisses <>0;
如果命中率小於 99％，就可以考慮增加shared pool 以提高library cache 的命中率
SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;
通常規則是把它定為DB_CACHE_SIZE大小的50%－150%，在使用了大量存儲過程或程序包，但只有有限內存的系統裏，最後分配為150%。在沒有使用存儲過程但大量分配內存給DB_CACHE_SIZE的系統裏，這個參數應該為10%－20%
5. 調整PGA_AGGREGATE_TARGET以優化對內存的應用
u OLTP ：totalmemory*80%*20%
u DSS: totalmemory*80%*50%
6. 25個重要初始化參數
DB_CACHE_SIZE：分配給數據緩存的初始化內存 SGA_TARGET：使用了自動內存管理，則設置此參數。設置為0可禁用它 PGA_AGGREGATE_TARGET：所有用戶PGA軟內存最大值 SHARED_POOL_SIZE：分配給數據字典、SQL和PL／SQL的內存 SGA_MAX_SIZE：SGA可動態增長的最大內存 OPTIMIZER_MODE： CURSOR_SHARING：把字面SQL轉換成帶綁定變更的SQL，可減少硬解析開銷 OPTIMIZER_INDEX_COST_ADJ：索引掃描成本和全表掃描成本進行調整，設定在1－10間會強制頻繁地使用索引，保證索引可用性 QUERY_REWRITE_ENABLED：用於啟用具體化視圖和基於函數的索引功能 DB_FILE_MULTIBLOCK_READ_COUNT：對於全表掃描，為了更有效執行IO，此參數可在壹次IO中讀取多個塊 LOG_BUFFER：為內存中沒有提交的事務分配緩沖區（非動態參數） DB_KEEP_CACHE_SIZE：分配給KEEP池或者額外數據緩存的內存 DB_RECYCLE_CACHE_SIZE： DBWR_IO_SLAVES：如果沒有異步IO，參數等同於DB_WRITER_PROCESSES模擬異步IO而分配的從SGA到磁盤的寫入器數。如果有異步IO，則使用DB_WRITER_PROCESSES設置多個寫程序，在DBWR期間更快地寫出臟塊 LARGE_POOL_SIZE：分配給大型PLSQL或其他壹些很少使用的ORACLE選項LARGET池的總塊數 STATISTICS_LEVEL：啟用顧問信息，並可選擇提供更多OS統計信息來改進優化器決策。默認：TYPICAL JAVA_POOL_SIZE：為JVM使用的JAVA存儲過程所分配的內存 JAVA_MAX_SESSIONSPACE_SIZE：跟蹤JAVA類的用戶會話狀態所用內存上限 MAX_SHARED_SERVERS：當使用***享服務器時的***享服務器上限 WORKAREA_SIZE_POLICY：啟用PGA大小自動管理 FAST_START_MTTR_TARGET：完成壹次崩潰恢復的大概時間／S LOG_CHECKPOINT_INTERVAL：檢查點頻率 OPEN_CURSORS：指定了保存用戶語句的專用區域大小，如此設置過高會導致ORA-4031 DB_BLOCK_SIZE：數據庫默認塊大小 OPTIMIZER_DYNAMIC_SAMPLING：控制動態抽樣查詢讀取的塊數量，對正在使用全局臨時表的系統非常有用三、 SQL調優1. 使用提示
1.1 改變執行路徑
通過OPTIMIZER_MODE參數指定優化器使用方法，默認ALL_ROWS
ALL_ROWS 可得最佳吞吐量執行查詢所有行 FIRST_ROWS(n) 可使優化器最快檢索出第壹行:select /*+ FIRST_ROWS(1) */ store_id,… from tbl_store
1.2 使用訪問方法提示
允許開發人員改變訪問的實際查詢方式，經常使用INDEX提示
CLUSTER 強制使用集群 FULL HASH INDEX 語法：/*+ INDEX (TABLE INDEX1,INDEX2….) */ COLUMN 1,….當不指定任何INDEX時，優化器會選擇最佳的索引
SELECT /*+ INDEX */ STORE_ID FROM TBL_STORE
INDEX_ASC 8I開始默認是升序，所以與INDEX同效 INDEX_DESC INDEX_COMBINE 用來指定多個位圖索引，而不是選擇其中最好的索引 INDEX_JOIN 只需訪問這些索引，節省了重新檢索表的時間 INDEX_FFS 執行壹次索引的快速全局掃描，只處理索引，不訪問具體表 INDEX_SS INDEX_SSX_ASC INDEX_SS_DESC NO_INDEX NO_INDEX_FFS NO_INDEX_SS1.3 使用查詢轉換提示
對於數據倉庫非常有幫助
FACT MERGE NO_EXPAND 語法：/*+ NO_EXPAND */ column1,…保證OR組合起的IN列表不會陷入困境，/*+ FIRST_ROWS NO_EXPAND */
NO_FACT NO_MERGE NO_QUERY_TRANSFORMATION NO_REWRITE NO_STAR_TRANSFORMATION NO_UNSET REWRITE STAR_TRANSFORMATION UNSET USE_CONCAT1.4 使用連接操作提示
顯示如何將連接表中的數據合並在壹起，可用兩提示直接影響連接順序。LEADING指定連接順序首先使用的表，ORDERED告訴優化器基於FROM子句中的表順序連接這些表，並使用第壹個表作為驅動表（最行訪問的表）
ORDERED語法：/*+ ORDERED */ column 1,….
訪問表順序根據FROM後的表順序來
LEADING語法：/*+ LEADING(TABLE1) */ column 1,….
類似於ORDER，指定驅動表
NO_USE_HASH NO_USE_MERGE NO_USE_NL USE_HASH前提足夠的HASH_AREA_SIZE或PGA_AGGREGATE_TARGET通常可以為較大的結果集提供最佳的響應時間
USE_MERGE USE_NL 通常可以以最快速度返回壹個行 USE_NL_WITH_INDEX1.5 使用並行執行
NO_PARALLEL NO_PARALLEL_INDEX PARALLEL PARALLEL_INDEX PQ_DISTRIBUTE1.6 其他提示
APPEND 不會檢查當前所用塊中是否有剩余空間，而直接插入到表中，會直接將數據添加到新的塊中。 CACHE 會將全表掃描全部緩存到內存中，這樣可直接在內存中找到數據，不用在磁盤上查詢 CURSOR_SHARING_EXACT DRIVING_SITE DYNAMIC_SAMPLING MODEL_MIN_ANALYSIS NOAPPEND NOCACHE NO_PUSH_PRED NO_PUSH_SUBQ NO_PX_JOIN_FILTER PUSH_PRED PUSH_SUBQ 強制先執行子查詢，當子查詢很快返回少量行時，這些行可以用於限制外部查詢返回行數，可極大地提高性能例：select /*+PUSH_SUBQ */ emp.empno,emp.ename
From emp,orders
where emp.deptno=(select deptno from dept where loc=’1’)
PX_JOIN_FILTER QB_NAME2. 調整查詢
2.1 在V$SQLAREA中選出最占用資源的查詢
HASH_VALUE：SQL語句的Hash值。
ADDRESS：SQL語句在SGA中的地址。
PARSING_USER_ID：為語句解析第壹條CURSOR的用戶
VERSION_COUNT：語句cursor的數量
KEPT_VERSIONS：
SHARABLE_MEMORY：cursor使用的***享內存總數
PERSISTENT_MEMORY：cursor使用的常駐內存總數
RUNTIME_MEMORY：cursor使用的運行時內存總數。
SQL_TEXT：SQL語句的文本（最大只能保存該語句的前1000個字符）。
MODULE,ACTION：用了DBMS_APPLICATION_INFO時session解析第壹條cursor時信息
SORTS: 語句的排序數
CPU_TIME: 語句被解析和執行的CPU時間
ELAPSED_TIME: 語句被解析和執行的***用時間
PARSE_CALLS: 語句的解析調用(軟、硬)次數
EXECUTIONS: 語句的執行次數
INVALIDATIONS: 語句的cursor失效次數
LOADS: 語句載入(載出)數量
ROWS_PROCESSED: 語句返回的列總數
select b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,a.SQL_TEXT
from v$sqlarea a ,dba_users b
where a.PARSING_USER_ID=b.user_id and a.DISK_READS>100 order by a.DISK_READS desc;
2.2 在V$SQL中選出最占用資源的查詢
與V$SQLAREA類似
select * from
(select sql_text,rank() over (order by buffer_gets desc) as rank_buffers,to_char(100*ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sql)
where rank_buffers <11
2.3 確定何時使用索引
當查詢條件只需要返回很少的行（受限列）時，則需要建立索引，不同的版本中這個返回要求不同V5:20% V7:7% V8i,V9i:4% V10g: 5%
查看表上的索引
select a.table_name,a.index_name,a.column_name,a.column_position,a.table_owner
from dba_ind_columns a
where a.table_owner='CLOUDSEA'
修正差的索引，可使用提示來限制很差的索引，如INDEX，FULL提示 在SELECT 和WHERE中的列使用索引如: select name from tbl where no=?
建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(….)
對於系統中很關鍵的查詢，可以考慮建立此類連接索引
在壹個表中有多個索引時可能出現麻煩，使用提示INDEX指定使用索引 使用索引合並，使用提示INDEX_JOIN 基於函數索引，由於使用了函數造成查詢很慢.必須基於成本的優化模式,參數：QUERY_REWRITE_ENALED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED (OR ENFORCED)
create index test on sum(test);
2.4 在內存中緩存表
將常用的相對小的表緩存到內存中，但註意會影響到嵌套循環連接上的驅動表
alter table tablename cache;
2.5 使用EXISTS 與嵌套子查詢 代替IN
SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’);
(方法壹: 高效)
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’
(方法二: 最高效)
SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
四、 使用STATSPACK和AWR報表調整等待和閂鎖
1. 10GR2裏的腳本
在$ORACLE_HOME/RDBMS/ADMIN下
Spcreate.sql 通過調用spcusr.sql spctab.sql 和spcpkg.sql創建STATSPACK環境，使用SYSDBA運行它
Spdrop.sql 調用sptab.sql和spdusr.sql刪除整個STATSPACK環境，使用SYSDBA運行它
Spreport.sql 這是生成報表的主要腳本，由PERFSTAT用戶運行
Sprepins.sql 為指定的數據庫和實例生成實例報表
Sprepsql.sql 為指定的SQL散列值生成SQL報表
Sprsqins.sql 為指定的數據庫和實例生成SQL報表
Spauto.sql 使用DBMS_JOB自動進行統計數據收集（照相）
Sprepcon.sql 配置SQLPLUS變量來設置像閾值這樣的內容的配置文件
Spurge.sql 刪除給定數據庫實例壹定範圍內的快照ID，不刪除基線快照
Sptrunc.sql 截短STATSPACK表裏所有性能數據
五、 執行快速系統檢查1. 緩沖區命中率
查詢緩沖區命中率
select (1 - (sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio"
from v$sysstat;