一、前言 2
二、ORACLE數據庫優(yōu)化概述 2
1、內存等參數配置的優(yōu)化 3
2、減少物理讀寫(xiě)的優(yōu)化 4
3、批量重復操作的SQL語(yǔ)句及大表操作的優(yōu)化 4
二、ORACLE數據庫優(yōu)化方案 5
1、內存等Oracle系統參數配置 5
2、使用索引 6
3、表分區 8
4、Procedure優(yōu)化 9
5、其他改造 10
6、維護作業(yè)計劃 10
三、ORACLE數據庫優(yōu)化前后比較 10
1、批量重復的SQL語(yǔ)句執行性能 10
2、一些單次、不常用的操作的語(yǔ)句執行性能 11
四、參考 11
1、常用的優(yōu)化工具 11
2、參考文獻 12
一、前言
隨著(zhù)實(shí)際項目的啟動(dòng)，實(shí)際項目中使用的 Oracle數據庫經(jīng)過(guò)一段時(shí)間的運行，在線(xiàn)保存的數據量和業(yè)務(wù)處理的數據量在逐漸增大，最初的Oracle設置，與現在實(shí)際需要的運行性能有一定差距，需要進(jìn)行一些優(yōu)化調整。
本文將結合本人實(shí)際維護經(jīng)驗，相應地提出實(shí)際項目數據處理的一些優(yōu)化方法，以供參考。
適用于Oracle 9i。
二、Oracle數據庫優(yōu)化概述
Oracle數據庫的優(yōu)化，針對不同的應用，會(huì )有側重點(diǎn)不同的優(yōu)化方法，根據我們實(shí)際項目的應用特點(diǎn)，我們主要關(guān)心的是每次事務(wù)執行完成的時(shí)間長(cháng)短。
從Oracle數據庫本身的特點(diǎn)，我們可以把優(yōu)化工作劃分為初始優(yōu)化設置，微優(yōu)化。
在初始優(yōu)化設置時(shí)，我們只能根據硬件情況，估計業(yè)務(wù)運行的情況，綜合經(jīng)驗，給出一種經(jīng)驗設置，大體上來(lái)說(shuō)，這種經(jīng)驗設置離滿(mǎn)足優(yōu)化需求的目標不是很遠。
在完成了初始優(yōu)化設置后，經(jīng)過(guò)一段時(shí)間的業(yè)務(wù)運行，已可開(kāi)始收集實(shí)際運行環(huán)境的性能數據，此時(shí)，就可以對各種Oracle性能指標、各種關(guān)心的事務(wù)操作進(jìn)行性能評估，然后進(jìn)行微優(yōu)化了。
Oracle優(yōu)化，不是一個(gè)一蹴而就的工作，也不是一個(gè)一勞永逸的工作，需要定期維護，定期觀(guān)察，在發(fā)現性能瓶頸時(shí)及時(shí)進(jìn)行調整。Oracle總是存在性能瓶頸的，不使用、不操作的數據庫總是最快的，在解決當前瓶頸后，總是會(huì )有另一個(gè)瓶頸出現，所以在優(yōu)化前，我們需要確定一個(gè)優(yōu)化目標，我們的目標是滿(mǎn)足我們的應用性能要求就可以了。
Oracle優(yōu)化，涉及的范圍太廣泛，包含的有主機性能，內存使用性能，網(wǎng)絡(luò )傳輸性能，SQL語(yǔ)句執行性能等等，從我們面向網(wǎng)管來(lái)說(shuō)，滿(mǎn)足事務(wù)執行速度性能主要表現在：
1）批量重復的SQL語(yǔ)句執行性能（主要是通過(guò)Procedure計算完成數據合并和數據匯總的性能和批量數據采集入庫的性能）；
2）一些單次、不常用的操作的語(yǔ)句執行性能（主要是GUI的非規律操作）。
根據這兩個(gè)特點(diǎn)，我們可把優(yōu)化方法歸納到3個(gè)重要方向：
1）內存等參數配置的優(yōu)化。內存優(yōu)化，是性能受益最快的地方。
2）減少物理讀寫(xiě)的優(yōu)化。內存邏輯I/O操作的時(shí)間，遠遠小于物理I/O的操作時(shí)間。
3）批量重復操作的SQL語(yǔ)句及大表操作的優(yōu)化。減少SQL執行次數，減少大表操作次數。
下面主要針對得益最大的這三個(gè)方向的優(yōu)化進(jìn)行闡述。
1、內存等參數配置的優(yōu)化
對于大多數應用來(lái)說(shuō)，最直接、最快速得到優(yōu)化收益的，肯定屬于內存的優(yōu)化。給每個(gè)Oracle內存塊分配合理的大小，可以有效的使用數據庫。通過(guò)觀(guān)察各種數據庫活動(dòng)在內存里的命中率，執行情況，我們能很快的掌握數據庫的主要瓶頸。我們從下面的一條SQL語(yǔ)句的執行步驟就可知道。
一個(gè)SQL語(yǔ)句，從發(fā)布到執行，會(huì )按順序經(jīng)歷如下幾個(gè)步驟：
1）Oracle把該SQL的字符轉換成它們的ASCII等效數字碼。
2）該ASCII數字碼被傳送給一個(gè)散列算法，生成一個(gè)散列值。
3）用戶(hù)server process查看該散列值是否在shared pool內存塊中存在。
若存在：
4）使用shared pool中緩存的版本來(lái)執行。
若不存在：
4）檢查該語(yǔ)句的語(yǔ)義正確性。
5）執行對象解析（這期間對照數據字典，檢查被引用的對象的名稱(chēng)和結構的正確性）。
6）檢查數據字典，收集該操作所引用的所有對象的相關(guān)統計數據。
7）準備執行計劃，從可用的執行計劃中選擇一個(gè)執行計劃。（包括對stored outline和materialized view的相關(guān)使用的決定）
8）檢查數據字典，確定所引用對象的安全性。
9）生成一個(gè)編譯代碼（P-CODE）。
10）執行。
這里，通過(guò)內存的合理分配，參數的合理設置，我們主要解決：
1）減少執行到第五步的可能，節約SQL語(yǔ)句解析的時(shí)間。第五步以后的執行過(guò)程，是一個(gè)很消耗資源的操作過(guò)程。
2）通過(guò)內存配置，盡可能讓SQL語(yǔ)句所做的操作和操作的數據都在內存里完成。大家都知道，從內存讀取數據的速度，要遠遠快于從物理硬盤(pán)上讀數據，一次內存排序要比硬盤(pán)排序快很多倍。
3）根據數據庫內存活動(dòng)，減少每個(gè)內存塊活動(dòng)的響應時(shí)間，充分利用每個(gè)內存塊，減少內存latch爭用發(fā)生的次數。
2、減少物理讀寫(xiě)的優(yōu)化
無(wú)論如何配置Oracle數據庫，我們的網(wǎng)管系統，每小時(shí)周期性的都會(huì )有新數據被處理，就會(huì )發(fā)生物理讀寫(xiě)，這是避免不了的。
減少物理讀寫(xiě)的優(yōu)化，一般所用的方法有：
1） 增加內存data buffer的大小，盡可能讓數據庫操作的數據都能在內存里找到，不需要進(jìn)行物理讀寫(xiě)操作。
2） 通過(guò)使用索引，避免不必要的全表掃描。
3） 大表物理分區，Oracle具有很好的分區識別功能，減少數據掃描范圍。
上述3個(gè)方法，是從整體上改善數據庫物理I/O性能最明顯的3個(gè)方法。能非?？焖俚臏p少數據庫在物理I/O，最直接的反應是數據庫事務(wù)執行時(shí)間能能以數量級為單位減少。
其他的一些減少物理讀寫(xiě)的優(yōu)化方法，比如使用materialized view，Cluster等方法；還有一些分散I/O的方法，比如 Oracle日志文件不與數據文件放在一個(gè)物理硬盤(pán)，數據熱點(diǎn)文件物理I/O分開(kāi)等等方法，就目前我們的網(wǎng)管系統而言，能得到的效果不是很明顯，在網(wǎng)管系統中，為了不增加數據庫維護的復雜性，不推薦使用。
3、批量重復操作的SQL語(yǔ)句及大表操作的優(yōu)化
批量重復執行的SQL語(yǔ)句，一般出現在每個(gè)周期時(shí)間內的數據批量入庫的insert語(yǔ)句，和數據合并、匯總的周期性select、delete、insert操作。
我們需要注意以下幾點(diǎn)：
1） 減少不必要的SQL語(yǔ)句執行和SQL語(yǔ)句的執行次數。
每條SQL語(yǔ)句執行，都會(huì )消費系統資源，都有執行時(shí)間。減少不必要的SQL語(yǔ)句執行和減少SQL語(yǔ)句的執行次數，自然能減少業(yè)務(wù)執行時(shí)間。需要根據業(yè)務(wù)流程，重新設計數據處理的代碼。此方法主要適用于procedure執行的數據合并、匯總。
2） 這些SQL語(yǔ)句，由于每個(gè)SQL語(yǔ)句都要執行很多次，應該盡量讓該SQL的散列值在shared pool內存塊中存在。也就是使用動(dòng)態(tài)SQL，避免SQL硬解析。
可通過(guò)Oracle參數的設置，和動(dòng)態(tài)SQL語(yǔ)句的應用，通過(guò)綁定變量的方式，減少SQL語(yǔ)句的解析次數。
3） 減少大表的操作，確保在一次事務(wù)中，同類(lèi)操作只對大表執行一次。主要在數據合并和數據匯總的pprocedure和數據采集時(shí)出現。
- procedure：在我們的應用中，cell數據和traffic數據，以及l(fā)ink，linkset,trunkgroup等數據，都屬于大數據量業(yè)務(wù)，對他們的每次操作，執行的時(shí)間都不短（即使做了表分區，減少數據掃描范圍，操作的響應時(shí)間還是大于一個(gè)同類(lèi)大小的普通表）。在多次執行的一個(gè)procedure中，通過(guò)使用臨時(shí)表的方式，把多次大表操作，改造成一次大表操作，能極大的縮短業(yè)務(wù)執行時(shí)間。
- 數據采集：如果是普通的每條insert語(yǔ)句完成一條數據入庫，為了保證數據的唯一性，每次入庫的時(shí)候，都會(huì )判斷該條數據是否存在數據庫，造成對表不必要的多次操作。對于批量數據入庫，我們推薦使用sqlldr的方法進(jìn)行數據拷貝，能極大的提高數據入庫時(shí)間。
二、Oracle數據庫優(yōu)化方案
1、內存等Oracle系統參數配置
Oracle 的parameter參數，分動(dòng)態(tài)參數和靜態(tài)參數，靜態(tài)參數需要重新啟動(dòng)數據庫才能生效，動(dòng)態(tài)參數不需要重新啟動(dòng)數據庫即可生效。
Oracle 9i可以使用spfile的特性，使用alter system set 參數名=參數值 scope=both[spfile];的方法進(jìn)行修改。也可以直接修改pfile。
以下給出了網(wǎng)管Oracle 數據庫重點(diǎn)關(guān)注的parameter的初始優(yōu)化設置。
最大可使用的內存SGA總和
靜態(tài)參數sga_max_size=物理內存的大小減1.5G
Shared pool
動(dòng)態(tài)參數shared_pool_size= 600 ~ 800 M
靜態(tài)參數shared_pool_reserved_size= 300 M
動(dòng)態(tài)參數open_cursors= 400 ~ 600
靜態(tài)參數cursor_space_for_time= TRUE
靜態(tài)參數session_cached_cursors= 60 ~ 100
動(dòng)態(tài)參數cursor_sharing= SIMILAR
Data buffer
動(dòng)態(tài)參數db_cache_advice= READY
動(dòng)態(tài)參數db_cache_size
動(dòng)態(tài)參數Db_keep_cache_size
動(dòng)態(tài)參數db_recycle_cache_size
（sga_max_size大小，除了分配給所有非data buffer的size，都分配給data buffer）
Sga other memory
動(dòng)態(tài)參數large_pool_size= 50 M
靜態(tài)參數java_pool_size= 100 M
動(dòng)態(tài)參數log_buffer= 3 M
Other memory
動(dòng)態(tài)參數sort_area_size= 3 M
靜態(tài)參數sort_area_retained_size= 0.5 M
靜態(tài)參數pga_aggregate_target= 800 M
動(dòng)態(tài)參數workarea_size_policy= AUTO
磁盤(pán)I/O配置
靜態(tài)參數sql_trace= FALSE
動(dòng)態(tài)參數timed_statistics= true
動(dòng)態(tài)參數db_file_multiblock_read_count= 16
靜態(tài)參數dbwr_io_slaves= 0
靜態(tài)參數db_writer_processes= 3
靜態(tài)參數undo_management= AUTO
動(dòng)態(tài)參數undo_retention= 7200
2、使用索引
我們初步定義，表數據超過(guò)1000行的表，我們都要求使用索引。（不區分事務(wù)操作的數據在表數據中所占的比例）
索引所包含的字段不超過(guò)4個(gè)。
檢查SQL語(yǔ)句是否使用了索引，我們使用execute plan來(lái)看，獲得explain的方法，我們通過(guò)SQL*PLUS工具，使用如下命令進(jìn)行查看：
set autotrace on
set autotrace traceonly explain
set timing on
或通過(guò)SQL*PLUS trace，然后查看user_dump_dest下的跟蹤文件，使用tkprof工具格式化后閱覽。
alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
SELECT p.spid,s.username FROM v$session s,v$process p WHERE s.audsid=USERENV('sessionid') AND s.paddr = p.addr;
使用方法示例：
DBserver% sqlplus perf/perf
SQL*Plus: Release 9.2.0.6.0 - Production on Mon Oct 17 14:32:29 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from perf_sdcch_nn where start_time = (select max(start_time) from perf_sdcch_nn);
COUNT(*)
----------
638
Elapsed: 00:00:00.80
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IDX02_PERF_SDCCH_NN' (NON-UNIQUE)
(Cost=2 Card=1495 Bytes=11960)
3 2 SORT (AGGREGATE)
4 3 INDEX (FULL SCAN (MIN/MAX)) OF 'IDX02_PERF_SDCCH_NN'
(NON-UNIQUE) (Cost=1 Card=3852090 Bytes=30816720)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
492 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
從上面的示例我們可以看到，該SQL語(yǔ)句執行花了0.8秒，select語(yǔ)句和inline view，都使用了名為'IDX02_PERF_SDCCH_NN 的索引。物理讀為0，redo log size為0，沒(méi)有生成REDO日志。
3、表分區
在網(wǎng)管數據庫里，比較突出的大表有小區表和告警表。
性能表，使用范圍分區。
以時(shí)間點(diǎn)start_time為范圍分區字段。
告警表，使用range-hash的混合分區和范圍分區。
范圍分區以時(shí)間點(diǎn)starttime為分區字段，混合分區增加ALARMNUMBER為字段的hash子分區。
同時(shí)，創(chuàng )建本地分區索引。
范圍分區示范：
drop table part_mid_cell_traf;
create table part_mid_cell_traf;
(
NE_ID CHAR(16) NOT NULL,
....................
TCHSEIZE FLOAT(126)
)
partition by range(start_time)
(
partition mid_cell_traf_051225 values less than (to_date('2005-12-25 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace perf_partition42,
partition mid_cell_traf_051230 values less than (to_date('2005-12-30 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace perf_partition43,
partition mid_cell_traf_error values less then (maxvalue) tablespace perf_partition44
)
enable row movement;
CREATE INDEX local_mid_cell_traf ON part_mid_cell_traf (ne_id,cell_id,start_time,stop_time)
LOCAL
(
partition l_ind1_mid_cell_traf_051225 tablespace perf_partition42,
partition l_ind1_mid_cell_traf_051230 tablespace perf_partition43,
partition l_ind1_mid_cell_traf_error tablespace perf_partition44
);
Range-hash混合分區示范：
create table part_ALARMTEXTDATA;
(
ALARMNUMBER VARCHAR2(16) NOT NULL,
......
SERIAL NUMBER(38) NOT NULL
)
partition by range (STARTTIME)
subpartition by hash(ALARMNUMBER)
subpartition template
(
subpartition atd01 tablespace alarm_partition01,
subpartition atd02 tablespace alarm_partition02,
subpartition atd03 tablespace alarm_partition03,
)
(
partition ALARMTEXTDATA_051225 values less than (to_date('2005-12-25 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition ALARMTEXTDATA_051230 values less than (to_date('2005-12-30 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition ALARMTEXTDATA_error values less then (maxvalue)
)
enable row movement;
CREATE INDEX local_ALARMTEXTDATA ON ALARMTEXTDATA (STARTTIME,ALARMNUMBER,SERIAL)
LOCAL
(
partition l_ind1_atd_050910 tablespace alarm_partition02,
partition l_ind1_atd_050915 tablespace alarm_partition03,
partition l_ind1_atd_050920 tablespace alarm_partition04,
);
4、Procedure優(yōu)化
1）取消地市一級的Procedure，只保留其上層調用Procedure，并保持參數輸入方法，調用方法不變。
2）確保大表數據查詢(xún)操作只有1次，確保大表數據刪除只有一次。
3）確保單條SQL語(yǔ)句執行已優(yōu)化。
4）減少SQL執行次數。
5、其他改造
修改表存儲參數，提前預先分配extents。
修改表空間存儲參數（采集表空間所用塊設置為大塊，比如32k一個(gè)塊；修改ptcfree,pctused,pctincrease等）。
避免使用唯一索引和非空約束。
創(chuàng )建合理的索引。
各模塊SQL語(yǔ)句優(yōu)化，比如使用提示固定索引等。
確認每一條歷史數據刪除語(yǔ)句已優(yōu)化和刪除方法。
臨時(shí)表的使用。
6、維護作業(yè)計劃
表分析（包含確定具體的表的分析方法，分區表分析方法，索引分析方法）。
空間回收維護（包括確定HWM，回收多余分配給表的塊，合并數據塊碎片等）。
索引維護（包括定期重建索引，索引使用情況監視等）。
歷史數據刪除檢查（檢查保存的數據是否符合要求，檢查歷史數據刪除方法是否正確-比如批量刪除提交的方法等）。
全庫性能分析和問(wèn)題報告及優(yōu)化（比如使用statspack進(jìn)行性能趨勢分析，檢查有問(wèn)題的SQL或事務(wù)，確定當前系統等待的top 5事件等等）。
表數據keep,default及reclye（比如把一些常用的配置表固定在內存里等）。
數據庫參數核查（防止數據庫參數被修改，定期對系統配置參數進(jìn)行比較）。
日志文件分析（定期檢查Oracle生成的日志文件，定期備份、刪除）。
硬盤(pán)空間維護（定期對Oracle 對象使用的空間情況進(jìn)行監視）。
三、Oracle數據庫優(yōu)化前后比較
1、批量重復的SQL語(yǔ)句執行性能
根據網(wǎng)元數量，各地的執行的完成時(shí)間有所區別。
用于數據合并和匯總的Procedure的計算性能
通過(guò)statspack的周期性采集數據，我們可以使用以下語(yǔ)句，計算我們想統計的Procedure的執行情況：
SELECT TO_CHAR(sn.snap_time,'yyyy-mm-dd hh24:mi:ss') AS snap_time,s.disk_reads,
s.buffer_gets,s.elapsed_time/1000000 AS elapsedtime
FROM
(SELECT hash_value,sql_text,address,last_snap_id
FROM STATS$SQLTEXT WHERE piece = 0 AND sql_text LIKE '%&sqltext_key%') t,
(SELECT address,hash_value,snap_id,sql_text,disk_reads,executions,
buffer_gets,rows_processed,elapsed_time
FROM STATS$SQL_SUMMARY) s,STATS$SNAPSHOT sn
WHERE s.hash_value = t.hash_value
AND s.address = t.address
AND s.snap_id = t.last_snap_id
AND sn.snap_id = s.snap_id;
比如，我們以perfstat用戶(hù)執行該SQL，輸入“to_comp”，可以觀(guān)察到數據庫里保存的有的to_comp存儲過(guò)程的執行時(shí)間，我們發(fā)現，其執行時(shí)間，從優(yōu)化前的幾千秒，最后穩定在優(yōu)化后的幾十秒。
注：to_comp是整體調用執行一次所有網(wǎng)元的數據合并和匯總的procedure。
用于小區分析數據的Procedure的計算性能
使用上面的方法，我們一樣可以知道，小區分析的procedure執行，從優(yōu)化前的約幾千秒，最后穩定在優(yōu)化后的幾十秒。
批量數據采集入庫性能
使用bcp，能從以前約15分鐘，減少到約4分鐘。
2、一些單次、不常用的操作的語(yǔ)句執行性能
GUI上的性能數據查詢(xún)，告警數據查詢(xún)，響應時(shí)間都極快，幾乎不再出現長(cháng)時(shí)間等待響應的情況。
四、參考
1、常用的優(yōu)化工具
statspack
sql*plus
TOAD
2、參考文獻
Oracle online document Oracle company release
bigboar 發(fā)表于:2006.02.15 16:12
（###）
聯(lián)系客服
