『壹』 如何提高oracle的查詢速度
幾個簡單的步驟大幅提高Oracle性能--我優化資料庫的三板斧。
資料庫優化的討論可以說是一個永恆的主題。資深的Oracle優化人員通常會要求提出性能問題的人對資料庫做一個statspack，貼出資料庫配置等等。還有的人認為要抓出執行最慢的語句來進行優化。但實際情況是，提出疑問的人很可能根本不懂執行計劃，更不要說statspack了。而我認為，資料庫優化，應該首先從大的方面考慮：網路、伺服器硬體配置、操作系統配置、Oracle伺服器配置、數據結構組織、然後才是具體的調整。實際上網路、硬體等往往無法決定更換，應用程序一般也無法修改，因此應該著重從資料庫配置、數據結構上來下手，首先讓資料庫有一個良好的配置，然後再考慮具體優化某些過慢的語句。我在給我的用戶系統進行優化的過程中，總結了一些基本的，簡單易行的辦法來優化資料庫，算是我的三板斧，呵呵。不過請注意，這些不一定普遍使用，甚至有的會有副作用，但是對OLTP系統、基於成本的資料庫往往行之有效，不妨試試。（註：附件是Burleson寫的用來報告資料庫性能等信息的腳本，本文用到）
一．設置合適的SGA
常常有人抱怨伺服器硬體很好，但是Oracle就是很慢。很可能是內存分配不合理造成的。(1)假設內存有512M，這通常是小型應用。建議Oracle的SGA大約240M，其中：共享池（SHARED_POOL_SIZE）可以設置60M到80M，根據實際的用戶數、查詢等來定。數據塊緩沖區可以大致分配120M-150M，8i下需要設置DB_BLOCK_BUFFERS，DB_BLOCK_BUFFER*DB_BLOCK_SIZE等於數據塊緩沖區大小。9i 下的數據緩沖區可以用db_cache_size來直接分配。
(2)假設內存有1G，Oracle 的SGA可以考慮分配500M：共享池分配100M到150M，數據緩沖區分配300M到400M。
(3)內存2G，SGA可以考慮分配1.2G，共享池300M到500M，剩下的給數據塊緩沖區。
(4)內存2G以上：共享池300M到500M就足夠啦，再多也沒有太大幫助；(Biti_rainy有專述)數據緩沖區是盡可能的大，但是一定要注意兩個問題：一是要給操作系統和其他應用留夠內存，二是對於32位的操作系統，Oracle的SGA有1.75G的限制。有的32位操作系統上可以突破這個限制，方法還請看Biti的大作吧。
二．分析表和索引，更改優化模式
Oracle默認優化模式是CHOOSE，在這種情況下，如果表沒有經過分析，經常導致查詢使用全表掃描，而不使用索引。這通常導致磁碟I/O太多，而導致查詢很慢。如果沒有使用執行計劃穩定性，則應該把表和索引都分析一下，這樣可能直接會使查詢速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。對於少於100萬的表，可以考慮分析整個表，對於很大的表，可以按百分比來分析，但是百分比不能過低，否則生成的統計信息可能不準確。可以通過DBA_TABLES的LAST_ANALYZED列來查看錶是否經過分析或分析時間，索引可以通過DBA_INDEXES的LAST_ANALYZED列。
下面通過例子來說明分析前後的速度對比。（表CASE_GA_AJZLZ大約有35萬數據，有主鍵）首先在sqlPLUS中打開自動查詢執行計劃功能。(第一次要執行\RDBMS\ADMIN\utlxplan.sql來創建PLAN_TABLE這個表)
SQL> SET AUTOTRACE ON
SQL>SET TIMING ON
通過SET AUTOTRACE ON 來查看語句的執行計劃，通過SET TIMING ON 來查看語句運行時間。
SQL> seleCT count(*) from CASE_GA_AJZLZ;
COUNT(*)
----------
346639
已用時間: 00: 00: 21.38
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'CASE_GA_AJZLZ'
……………………
請注意上面分析中的TABLE ACCESS(FULL)，這說明該語句執行了全表掃描。而且查詢使用了21.38秒。這時表還沒有經過分析。下面我們來對該表進行分析：
SQL> analyze table CASE_GA_AJZLZ compute statistics;
表已分析。已用時間: 00: 05: 357.63。然後再來查詢：
SQL> select count(*) from CASE_GA_AJZLZ;
COUNT(*)
----------
346639
已用時間: 00: 00: 00.71
Execution Plan
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=351 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PK_AJZLZ' (UNIQUE) (Cost=351
Card=346351)
…………………………
請注意，這次時間僅僅用了0.71秒！這要歸功於INDEX(FAST FULL SCAN)。通過分析表，查詢使用了PK_AJZLZ索引，磁碟I/O大幅減少，速度也大幅提升！下面的實用語句可以用來生成分析某個用戶的所有表和索引，假設用戶是GAXZUSR：
SQL> set pagesize 0
SQL> spool d:\analyze_tables.sql;
SQL> select 'analyze table '||owner||'.'||table_name||'
compute statistics;' from dba_tables where owner='GAXZUSR';
SQL> spool off
SQL> spool spool d:\analyze_indexes.sql;
SQL> select 'analyze index '||owner||'.'||index_name||'
compute statistics;' from dba_indexes where owner='GAXZUSR';
SQL> spool off
SQL> @d:\analyze_tables.sql
SQL> @d:\analyze_indexes.sql
解釋：上面的語句生成了兩個sql文件，分別分析全部的GAXZUSR的表和索引。如果需要按照百分比來分析表，可以修改一下腳本。通過上面的步驟，我們就完成了對表和索引的分析，可以測試一下速度的改進啦。建議定期運行上面的語句，尤其是數據經過大量更新。
當然，也可以通過dbms_stats來分析表和索引，更方便一些。但是我仍然習慣上面的方法，因為成功與否會直接提示出來。
另外，我們可以將優化模式進行修改。optimizer_mode值可以是RULE、CHOOSE、FIRST_ROWS和ALL_ROWS。對於OLTP系統，可以改成FIRST_ROWS，來要求查詢盡快返回結果。這樣即使不用分析，在一般情況下也可以提高查詢性能。但是表和索引經過分析後有助於找到最合適的執行計劃。
三．設置cursor_sharing=FORCE 或SIMILAR
這種方法是8i才開始有的，oracle805不支持。通過設置該參數，可以強制共享只有文字不同的語句解釋計劃。例如下面兩條語句可以共享：
SQL> SELECT * FROM MYTABLE WHERE NAME='tom'
SQL> SELECT * FROM MYTABLE WHERE NAME='turner'
這個方法可以大幅降低緩沖區利用率低的問題，避免語句重新解釋。通過這個功能，可以很大程度上解決硬解析帶來的性能下降的問題。個人感覺可根據系統的實際情況，決定是否將該參數改成FORCE。該參數默認是exact。不過一定要注意，修改之前，必須先給ORACLE打補丁，否則改之後oracle會佔用100%的CPU,無法使用。對於ORACLE9i，可以設置成SIMILAR，這個設置綜合了FORCE和EXACT的優點。不過請慎用這個功能，這個參數也可能帶來很大的負面影響！
四．將常用的小表、索引釘在數據緩存KEEP池中
內存上數據讀取速度遠遠比硬碟中讀取要快，據稱，內存中數據讀的速度是硬碟的14000倍！如果資源比較豐富，把常用的小的、而且經常進行全表掃描的表給釘內存中，當然是在好不過了。可以簡單的通過ALTER TABLE tablename CACHE來實現，在ORACLE8i之後可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP)。一般來說，可以考慮把200數據塊之內的表放在keep池中，當然要根據內存大小等因素來定。關於如何查出那些表或索引符合條件，可以使用本文提供的access.sql和access_report.sql。這兩個腳本是著名的Oracle專家 Burleson寫的，你也可以在讀懂了情況下根據實際情況調整一下腳本。對於索引，可以通過ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)來釘在KEEP池中。
將表定在KEEP池中需要做一些准備工作。對於ORACLE9i 需要設置DB_KEEP_CACHE_SIZE，對於8i，需要設置buffer_pool_keep。在8i中，還要修改db_block_lru_latches，該參數默認是1，無法使用buffer_pool_keep。該參數應該比2*3*CPU數量少，但是要大於1，才能設置DB_KEEP_CACHE_BUFFER。buffer_pool_keep從db_block_buffers中分配，因此也要小於db_block_buffers。設置好這些參數後，就可以把常用對象永久釘在內存里。
五．設置optimizer_max_permutations
對於多表連接查詢，如果採用基於成本優化(CBO)，ORACLE會計算出很多種運行方案，從中選擇出最優方案。這個參數就是設置oracle究竟從多少種方案來選擇最優。如果設置太大，那麼計算最優方案過程也是時間比較長的。Oracle805和8i默認是80000，8建議改成2000。對於9i，已經默認是2000了。
六．調整排序參數
(1) SORT_AREA_SIZE:默認的用來排序的SORT_AREA_SIZE大小是32K，通常顯得有點小，一般可以考慮設置成1M（1048576）。這個參數不能設置過大，因為每個連接都要分配同樣的排序內存。
(2) SORT_MULTIBLOCK_READ_COUNT:增大這個參數可以提高臨時表空間排序性能，該參數默認是2，可以改成32來對比一下排序查詢時間變化。注意，這個參數的最大值與平台有關系。
『貳』 面試題：oracle資料庫優化
oracle資料庫優化的話主要有以下幾個方面（我接觸過的，可能不全面）：
1 查詢語句的優化，這個主要是根據語句和資料庫索引的情況，結合查詢計劃的分析結果，對性能較低的查詢語句進行重寫，在執行查詢前執行表分析語句也可以算這里；
2 數據結構優化，這個包括根據實際的應用中業務邏輯，對資料庫的結構進行重新設計，或者創建相關索引里提高查詢效率；
3 資料庫設置優化，這方面主要是調整資料庫和數據結構的相關參數提高應用訪問系統的效率；
4 存儲結構優化，在數據量較大的情況下，可以考慮通過資料庫的存儲結構進行優化，比如對數據進行partition，將數據存儲在磁碟陣列伺服器上等。
我的經驗有限，以上是部分建議
『叄』 oracle 10 g 和oracle 11 g 區別在哪
11g是adg，活動的備庫，10g日是dg。
『肆』 oracle資料庫優化有哪些方法
你最好買一本專門講ORACLE性能優化的書，好好看看
1、調整資料庫伺服器的性能
Oracle資料庫伺服器是整個系統的核心，它的性能高低直接影響整個系統的性能，為了調整Oracle資料庫伺服器的性能，主要從以下幾個方面考慮：
1.1、調整操作系統以適合Oracle資料庫伺服器運行
Oracle資料庫伺服器很大程度上依賴於運行伺服器的操作系統，如果操作系統不能提供最好性能，那麼無論如何調整，Oracle資料庫伺服器也無法發揮其應有的性能。
1.1.1、為Oracle資料庫伺服器規劃系統資源
據已有計算機可用資源, 規劃分配給Oracle伺服器資源原則是：盡可能使Oracle伺服器使用資源最大化,特別在Client/Server中盡量讓伺服器上所有資源都來運行Oracle服務。
1.1.2、調整計算機系統中的內存配置
多數操作系統都用虛存來模擬計算機上更大的內存，它實際上是硬碟上的一定的磁碟空間。當實際的內存空間不能滿足應用軟體的要求時，操作系統就將用這部分的磁碟空間對內存中的信息進行頁面替換，這將引起大量的磁碟I/O操作，使整個伺服器的性能下降。為了避免過多地使用虛存，應加大計算機的內存。
1.1.3、為Oracle資料庫伺服器設置操作系統進程優先順序
不要在操作系統中調整Oracle進程的優先順序，因為在Oracle資料庫系統中，所有的後台和前台資料庫伺服器進程執行的是同等重要的工作，需要同等的優先順序。所以在安裝時，讓所有的資料庫伺服器進程都使用預設的優先順序運行。
1.2、調整內存分配
Oracle資料庫伺服器保留3個基本的內存高速緩存，分別對應3種不同類型的數據：庫高速緩存，字典高速緩存和緩沖區高速緩存。庫高速緩存和字典高速緩存一起構成共享池，共享池再加上緩沖區高速緩存便構成了系統全程區(SGA)。SGA是對資料庫數據進行快速訪問的一個系統全程區，若SGA本身需要頻繁地進行釋放、分配，則不能達到快速訪問數據的目的，因此應把SGA放在主存中，不要放在虛擬內存中。內存的調整主要是指調整組成SGA的內存結構的大小來提高系統性能，由於Oracle資料庫伺服器的內存結構需求與應用密切相關，所以內存結構的調整應在磁碟I/O調整之前進行。
1.2.1、庫緩沖區的調整
庫緩沖區中包含私用和共享SQL和PL/SQL區，通過比較庫緩沖區的命中率決定它的大小。要調整庫緩沖區，必須首先了解該庫緩沖區的活動情況，庫緩沖區的活動統計信息保留在動態性能表v$librarycache數據字典中，可通過查詢該表來了解其活動情況，以決定如何調整。
Select sum(pins),sum(reloads) from v$librarycache;
Pins列給出SQL語句，PL/SQL塊及被訪問對象定義的總次數；Reloads列給出SQL 和PL/SQL塊的隱式分析或對象定義重裝載時在庫程序緩沖區中發生的錯誤。如果sum(pins)/sum(reloads) ≈0，則庫緩沖區的命中率合適；若sum(pins)/sum(reloads)>1, 則需調整初始化參數 shared_pool_size來重新調整分配給共享池的內存量。
1.2.2、數據字典緩沖區的調整
數據字典緩沖區包含了有關資料庫的結構、用戶、實體信息。數據字典的命中率，對系統性能影響極大。數據字典緩沖區的使用情況記錄在動態性能表v$librarycache中，可通過查詢該表來了解其活動情況，以決定如何調整。
Select sum(gets),sum(getmisses) from v$rowcache;
Gets列是對相應項請求次數的統計；Getmisses 列是引起緩沖區出錯的數據的請求次數。對於頻繁訪問的數據字典緩沖區，sum(getmisses)/sum(gets)<10%～15%。若大於此百分數，則應考慮增加數據字典緩沖區的容量，即需調整初始化參數shared_pool_size來重新調整分配給共享池的內存量。
1.2.3、緩沖區高速緩存的調整
用戶進程所存取的所有數據都是經過緩沖區高速緩存來存取，所以該部分的命中率，對性能至關重要。緩沖區高速緩存的使用情況記錄在動態性能表v$sysstat中，可通過查詢該表來了解其活動情況，以決定如何調整。
Select name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads');
dbblock gets和consistent gets的值是請求數據緩沖區中讀的總次數。physical reads的值是請求數據時引起從盤中讀文件的次數。從緩沖區高速緩存中讀的可能性的高低稱為緩沖區的命中率，計算公式：
Hit Ratio=1-(physical reds/(dbblock gets+consistent gets))
如果Hit Ratio<60%～70%，則應增大db_block_buffers的參數值。db_block_buffers可以調整分配給緩沖區高速緩存的內存量，即db_block_buffers可設置分配緩沖區高速緩存的數據塊的個數。緩沖區高速緩存的總位元組數=db_block_buffers的值*db_block_size的值。db_block_size 的值表示數據塊大小的位元組數，可查詢 v$parameter 表：
select name,value from v$parameter where name='db_block_size';
在修改了上述資料庫的初始化參數以後，必須先關閉資料庫，在重新啟動資料庫後才能使新的設置起作用。
『伍』 如何設置使oracle10g性能最優 性能調優 步驟
一、 磁碟方面調優
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_SS
1.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_CONCAT
1.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_INDEX
1.5 使用並行執行
Ø NO_PARALLEL
Ø NO_PARALLEL_INDEX
Ø PARALLEL
Ø PARALLEL_INDEX
Ø PQ_DISTRIBUTE
1.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_NAME
2. 調整查詢
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和spsr.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;
『陸』 Oracle資料庫中，基於等待事件的性能優化和基於命中率的性能調優方法之間有什麼區別
基於命中率的性能調優方法
如果HIS系統長期以來在業務忙碌的時候運行緩慢，這時候可以指導維護人員生產AWR報告，發現Cache Hit Ratio只有67%，AWR報告Top5 wait主要為db file sequence read和db file scattered read。檢查SGA Buffer Cache配置，只有478M。這時候只需要增加Buffer Cache（例如增加到2G）性能問題就會消失。
不過基於命中率調優的方法也具有一定的局限性
1.基於命中率的技術瑞然反映了Oracle內部效率問題，但這些命中率與使用資料庫的應用的性能關系不大；
2.命中率分析方法通過全局平均和模糊了個體，而大部分性能問題都是基於個體的。
基於等待事件借口的性能優化
優點：
1.OWI方法是快速解決orale性能的最有效方式；
2.OWI方法優化降低了性能優化人員的能力需求。
缺點：
1.OWI事實上並不是從業務（流程）的角度看問題，而是從CPU的角度看問題；
2.解決復雜性能問題有局限性。
OWI分析方法本質上和基於等待時間的調優方法是類似的，都缺乏從整體業務流程出發的概念。但是OWI的好處就是，簡單、有效、快速。
具體的優化方法可以進入OTPUB學習網了解詳情！
『柒』 如何使用 REORG 和 RUNSTATS 命令優化資料庫性能
1、調整數據結構的設計。這一部分在開發信息系統之前完成，程序員需要考慮是否使用ORACLE資料庫的分區功能，對於經常訪問的資料庫表是否需要建立索引等。
2、2、調整應用程序結構設計。這一部分也是在開發信息系統之前完成，程序員在這一步需要考慮應用程序使用什麼樣的體系結構，是使用傳統的Client/Server兩層體系結構，還是使用Browser/Web/Database的三層體系結構。不同的應用程序體系結構要求的資料庫資源是不同的。
3、3、調整資料庫SQL語句。應用程序的執行最終將歸結為資料庫中的SQL語句執行，因此SQL語句的執行效率最終決定了ORACLE資料庫的性能。ORACLE公司推薦使用ORACLE語句優化器（Oracle Optimizer）和行鎖管理器（row-level manager）來調整優化SQL語句。
4、4、調整伺服器內存分配。內存分配是在信息系統運行過程中優化配置的，資料庫管理員可以根據資料庫運行狀況調整資料庫系統全局區（SGA區）的數據緩沖區、日誌緩沖區和共享池的大小；還可以調整程序全局區（PGA區）的大小。需要注意的是，SGA區不是越大越好，SGA區過大會佔用操作系統使用的內存而引起虛擬內存的頁面交換，這樣反而會降低系統。
5、5、調整硬碟I/O，這一步是在信息系統開發之前完成的。資料庫管理員可以將組成同一個表空間的數據文件放在不同的硬碟上，做到硬碟之間I/O負載均衡。
6、6、調整操作系統參數，例如：運行在UNIX操作系統上的ORACLE資料庫，可以調整UNIX數據緩沖池的大小，每個進程所能使用的內存大小等參數。
實際上，上述資料庫優化措施之間是相互聯系的。ORACLE資料庫性能惡化表現基本上都是用戶響應時間比較長，需要用戶長時間的等待。但性能惡化的原因卻是多種多樣的，有時是多個因素共同造成了性能惡化的結果，這就需要資料庫管理員有比較全面的計算機知識，能夠敏感地察覺到影響資料庫性能的主要原因所在。另外，良好的資料庫管理工具對於優化資料庫性能也是很重要的。
ORACLE資料庫性能優化工具
常用的資料庫性能優化工具有：
1、1、ORACLE資料庫在線數據字典，ORACLE在線數據字典能夠反映出ORACLE動態運行情況，對於調整資料庫性能是很有幫助的。
2、2、操作系統工具，例如UNIX操作系統的vmstat，iostat等命令可以查看到系統系統級內存和硬碟I/O的使用情況，這些工具對於管理員弄清出系統瓶頸出現在什麼地方有時候很有用。
3、3、SQL語言跟蹤工具（SQL TRACE FACILITY），SQL語言跟蹤工具可以記錄SQL語句的執行情況，管理員可以使用虛擬表來調整實例，使用SQL語句跟蹤文件調整應用程序性能。SQL語言跟蹤工具將結果輸出成一個操作系統的文件，管理員可以使用TKPROF工具查看這些文件。
4、4、ORACLE Enterprise Manager（OEM），這是一個圖形的用戶管理界面，用戶可以使用它方便地進行資料庫管理而不必記住復雜的ORACLE資料庫管理的命令。
5、5、EXPLAIN PLAN——SQL語言優化命令，使用這個命令可以幫助程序員寫出高效的SQL語言。
ORACLE資料庫的系統性能評估
信息系統的類型不同，需要關注的資料庫參數也是不同的。資料庫管理員需要根據自己的信息系統的類型著重考慮不同的資料庫參數。
『捌』 資料庫性能優化有哪些措施
1、調整數據結構的設計。這一部分在開發信息系統之前完成，程序員需要考慮是否使用ORACLE資料庫的分區功能，對於經常訪問的資料庫表是否需要建立索引等。
2、調整應用程序結構設計。這一部分也是在開發信息系統之前完成，程序員在這一步需要考慮應用程序使用什麼樣的體系結構，是使用傳統的Client/Server兩層體系結構，還是使用Browser/Web/Database的三層體系結構。不同的應用程序體系結構要求的資料庫資源是不同的。
3、調整資料庫SQL語句。應用程序的執行最終將歸結為資料庫中的SQL語句執行，因此SQL語句的執行效率最終決定了ORACLE資料庫的性能。ORACLE公司推薦使用ORACLE語句優化器（Oracle Optimizer）和行鎖管理器（row-level manager）來調整優化SQL語句。
4、調整伺服器內存分配。內存分配是在信息系統運行過程中優化配置的，資料庫管理員可以根據資料庫運行狀況調整資料庫系統全局區（SGA區）的數據緩沖區、日誌緩沖區和共享池的大小；還可以調整程序全局區（PGA區）的大小。需要注意的是，SGA區不是越大越好，SGA區過大會佔用操作系統使用的內存而引起虛擬內存的頁面交換，這樣反而會降低系統。
5、調整硬碟I/O，這一步是在信息系統開發之前完成的。資料庫管理員可以將組成同一個表空間的數據文件放在不同的硬碟上，做到硬碟之間I/O負載均衡。
6、調整操作系統參數，例如：運行在UNIX操作系統上的ORACLE資料庫，可以調整UNIX數據緩沖池的大小，每個進程所能使用的內存大小等參數。
資料庫(Database)是按照數據結構來組織、存儲和管理數據的倉庫，它產生於距今六十多年前，隨著信息技術和市場的發展，特別是二十世紀九十年代以後，數據管理不再僅僅是存儲和管理數據，而轉變成用戶所需要的各種數據管理的方式。資料庫有很多種類型，從最簡單的存儲有各種數據的表格到能夠進行海量數據存儲的大型資料庫系統都在各個方面得到了廣泛的應用。
在信息化社會，充分有效地管理和利用各類信息資源，是進行科學研究和決策管理的前提條件。資料庫技術是管理信息系統、辦公自動化系統、決策支持系統等各類信息系統的核心部分，是進行科學研究和決策管理的重要技術手段。
在經濟管理的日常工作中，常常需要把某些相關的數據放進這樣的「倉庫」，並根據管理的需要進行相應的處理。
例如，企業或事業單位的人事部門常常要把本單位職工的基本情況(職工號、姓名、年齡、性別、籍貫、工資、簡歷等)存放在表中，這張表就可以看成是一個資料庫。有了這個"數據倉庫"我們就可以根據需要隨時查詢某職工的基本情況，也可以查詢工資在某個范圍內的職工人數等等。這些工作如果都能在計算機上自動進行，那我們的人事管理就可以達到極高的水平。此外，在財務管理、倉庫管理、生產管理中也需要建立眾多的這種"資料庫"，使其可以利用計算機實現財務、倉庫、生產的自動化管理。
(8)oracle資料庫性能調整與優化擴展閱讀
資料庫，簡單來說是本身可視為電子化的文件櫃--存儲電子文件的處所，用戶可以對文件中的數據進行新增、截取、更新、刪除等操作。
資料庫指的是以一定方式儲存在一起、能為多個用戶共享、具有盡可能小的冗餘度的特點、是與應用程序彼此獨立的數據集合。
在經濟管理的日常工作中，常常需要把某些相關的數據放進這樣的"倉庫"，並根據管理的需要進行相應的處理。
例如，企業或事業單位的人事部門常常要把本單位職工的基本情況(職工號、姓名、年齡、性別、籍貫、工資、簡歷等)存放在表中，這張表就可以看成是一個資料庫。有了這個"數據倉庫"我們就可以根據需要隨時查詢某職工的基本情況，也可以查詢工資在某個范圍內的職工人數等等。這些工作如果都能在計算機上自動進行，那我們的人事管理就可以達到極高的水平。此外，在財務管理、倉庫管理、生產管理中也需要建立眾多的這種"資料庫"，使其可以利用計算機實現財務、倉庫、生產的自動化管理。