幾個(gè)簡(jiǎn)單的步驟大幅提高Oracle性能 我優(yōu)化數(shù)據(jù)庫(kù)的三板斧
10年積累的成都網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問(wèn)題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先做網(wǎng)站后付款的網(wǎng)站建設(shè)流程，更有鄒城免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
數(shù)據(jù)庫(kù)優(yōu)化的討論可以說(shuō)是一個(gè)永恒的主題 資深的Oracle優(yōu)化人員通常會(huì)要求提出性能問(wèn)題的人對(duì)數(shù)據(jù)庫(kù)做一個(gè)statspack 貼出數(shù)據(jù)庫(kù)配置等等 還有的人認(rèn)為要抓出執(zhí)行最慢的語(yǔ)句來(lái)進(jìn)行優(yōu)化 但實(shí)際情況是 提出疑問(wèn)的人很可能根本不懂執(zhí)行計(jì)劃 更不要說(shuō)statspack了 而我認(rèn)為 數(shù)據(jù)庫(kù)優(yōu)化 應(yīng)該首先從大的方面考慮 網(wǎng)絡(luò) 服務(wù)器硬件配置 操作系統(tǒng)配置 Oracle服務(wù)器配置 數(shù)據(jù)結(jié)構(gòu)組織 然后才是具體的調(diào)整 實(shí)際上網(wǎng)絡(luò) 硬件等往往無(wú)法決定更換 應(yīng)用程序一般也無(wú)法修改 因此應(yīng)該著重從數(shù)據(jù)庫(kù)配置 數(shù)據(jù)結(jié)構(gòu)上來(lái)下手 首先讓數(shù)據(jù)庫(kù)有一個(gè)良好的配置 然后再考慮具體優(yōu)化某些過(guò)慢的語(yǔ)句 我在給我的用戶系統(tǒng)進(jìn)行優(yōu)化的過(guò)程中 總結(jié)了一些基本的 簡(jiǎn)單易行的辦法來(lái)優(yōu)化數(shù)據(jù)庫(kù) 算是我的三板斧 呵呵 不過(guò)請(qǐng)注意 這些不一定普遍使用 甚至有的會(huì)有副作用 但是對(duì)OLTP系統(tǒng) 基于成本的數(shù)據(jù)庫(kù)往往行之有效 不妨試試 （注 附件是Burleson寫(xiě)的用來(lái)報(bào)告數(shù)據(jù)庫(kù)性能等信息的腳本 本文用到）
一．設(shè)置合適的SGA
常常有人抱怨服務(wù)器硬件很好 但是Oracle就是很慢 很可能是內(nèi)存分配不合理造成的 ( )假設(shè)內(nèi)存有 M 這通常是小型應(yīng)用 建議Oracle的SGA大約 M 其中 共享池（SHARED_POOL_SIZE）可以設(shè)置 M到 M 根據(jù)實(shí)際的用戶數(shù) 查詢等來(lái)定 數(shù)據(jù)塊緩沖區(qū)可以大致分配 M M i下需要設(shè)置DB_BLOCK_BUFFERS DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于數(shù)據(jù)塊緩沖區(qū)大小 i 下的數(shù)據(jù)緩沖區(qū)可以用db_cache_size來(lái)直接分配
( )假設(shè)內(nèi)存有 G Oracle 的SGA可以考慮分配 M 共享池分配 M到 M 數(shù)據(jù)緩沖區(qū)分配 M到 M
( )內(nèi)存 G SGA可以考慮分配 G 共享池 M到 M 剩下的給數(shù)據(jù)塊緩沖區(qū)
( )內(nèi)存 G以上 共享池 M到 M就足夠啦 再多也沒(méi)有太大幫助 (Biti_rainy有專述)數(shù)據(jù)緩沖區(qū)是盡可能的大 但是一定要注意兩個(gè)問(wèn)題 一是要給操作系統(tǒng)和其他應(yīng)用留夠內(nèi)存 二是對(duì)于 位的操作系統(tǒng) Oracle的SGA有 G的限制 有的 位操作系統(tǒng)上可以突破這個(gè)限制 方法還請(qǐng)看Biti的大作吧
二．分析表和索引 更改優(yōu)化模式
Oracle默認(rèn)優(yōu)化模式是CHOOSE 在這種情況下 如果表沒(méi)有經(jīng)過(guò)分析 經(jīng)常導(dǎo)致查詢使用全表掃描 而不使用索引 這通常導(dǎo)致磁盤I/O太多 而導(dǎo)致查詢很慢 如果沒(méi)有使用執(zhí)行計(jì)劃穩(wěn)定性 則應(yīng)該把表和索引都分析一下 這樣可能直接會(huì)使查詢速度大幅提升 分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令 對(duì)于少于 萬(wàn)的表 可以考慮分析整個(gè)表 對(duì)于很大的表 可以按百分比來(lái)分析 但是百分比不能過(guò)低 否則生成的統(tǒng)計(jì)信息可能不準(zhǔn)確 可以通過(guò)DBA_TABLES的LAST_ANALYZED列來(lái)查看表是否經(jīng)過(guò)分析或分析時(shí)間 索引可以通過(guò)DBA_INDEXES的LAST_ANALYZED列
下面通過(guò)例子來(lái)說(shuō)明分析前后的速度對(duì)比 （表CASE_GA_AJZLZ大約有 萬(wàn)數(shù)據(jù) 有主鍵）首先在SQLPLUS中打開(kāi)自動(dòng)查詢執(zhí)行計(jì)劃功能 (第一次要執(zhí)行\(zhòng)RDBMS\ADMIN\utlxplan sql來(lái)創(chuàng)建PLAN_TABLE這個(gè)表)
SQL SET AUTOTRACE ON SQLSET TIMING ON
通過(guò)SET AUTOTRACE ON 來(lái)查看語(yǔ)句的執(zhí)行計(jì)劃 通過(guò)SET TIMING ON 來(lái)查看語(yǔ)句運(yùn)行時(shí)間
SQL select count(*) from CASE_GA_AJZLZ; COUNT(*) 已用時(shí)間: : : Execution Plan SELECT STATEMENT Optimizer=CHOOSE SORT (AGGREGATE) TABLE ACCESS (FULL) OF CASE_GA_AJZLZ ……………………
請(qǐng)注意上面分析中的TABLE ACCESS(FULL) 這說(shuō)明該語(yǔ)句執(zhí)行了全表掃描 而且查詢使用了 秒 這時(shí)表還沒(méi)有經(jīng)過(guò)分析 下面我們來(lái)對(duì)該表進(jìn)行分析
SQL *** yze table CASE_GA_AJZLZ pute statistics;
表已分析 已用時(shí)間: : : 然后再來(lái)查詢
SQL select count(*) from CASE_GA_AJZLZ; COUNT(*) 已用時(shí)間: : : Execution Plan SELECT STATEMENT Optimizer=FIRST_ROWS (Cost= Card= ) SORT (AGGREGATE) INDEX (FAST FULL SCAN) OF PK_AJZLZ (UNIQUE) (Cost= Card= ) …………………………
請(qǐng)注意 這次時(shí)間僅僅用了 秒！這要?dú)w功于INDEX(FAST FULL SCAN) 通過(guò)分析表 查詢使用了PK_AJZLZ索引 磁盤I/O大幅減少 速度也大幅提升！下面的實(shí)用語(yǔ)句可以
用來(lái)生成分析某個(gè)用戶的所有表和索引 假設(shè)用戶是GAXZUSR
SQL set pagesize SQL spool d:\ *** yze_tables sql; SQL select *** yze table ||owner|| ||table_name|| pute statistics; from dba_tables where owner= GAXZUSR ; SQL spool off SQL spool spool d:\ *** yze_indexes sql; SQL select *** yze index ||owner|| ||index_name|| pute statistics; from dba_indexes where owner= GAXZUSR ; SQL spool off SQL @d:\ *** yze_tables sql SQL @d:\ *** yze_indexes sql
解釋 上面的語(yǔ)句生成了兩個(gè)sql文件 分別分析全部的GAXZUSR的表和索引 如果需要按照百分比來(lái)分析表 可以修改一下腳本 通過(guò)上面的步驟 我們就完成了對(duì)表和索引的分析 可以測(cè)試一下速度的改進(jìn)啦 建議定期運(yùn)行上面的語(yǔ)句 尤其是數(shù)據(jù)經(jīng)過(guò)大量更新
當(dāng)然 也可以通過(guò)dbms_stats來(lái)分析表和索引 更方便一些 但是我仍然習(xí)慣上面的方法 因?yàn)槌晒εc否會(huì)直接提示出來(lái)
另外 我們可以將優(yōu)化模式進(jìn)行修改 optimizer_mode值可以是RULE CHOOSE FIRST_ROWS和ALL_ROWS 對(duì)于OLTP系統(tǒng) 可以改成FIRST_ROWS 來(lái)要求查詢盡快返回結(jié)果 這樣即使不用分析 在一般情況下也可以提高查詢性能 但是表和索引經(jīng)過(guò)分析后有助于找到最合適的執(zhí)行計(jì)劃
三．設(shè)置cursor_sharing=FORCE 或SIMILAR
這種方法是 i才開(kāi)始有的 oracle 不支持 通過(guò)設(shè)置該參數(shù) 可以強(qiáng)制共享只有文字不同的語(yǔ)句解釋計(jì)劃 例如下面兩條語(yǔ)句可以共享
SQL SELECT * FROM MYTABLE WHERE NAME= tom SQL SELECT * FROM MYTABLE WHERE NAME= turner
這個(gè)方法可以大幅降低緩沖區(qū)利用率低的問(wèn)題 避免語(yǔ)句重新解釋 通過(guò)這個(gè)功能 可以很大程度上解決硬解析帶來(lái)的性能下降的問(wèn)題 個(gè)人感覺(jué)可根據(jù)系統(tǒng)的實(shí)際情況 決定是否將該參數(shù)改成FORCE 該參數(shù)默認(rèn)是exact 不過(guò)一定要注意 修改之前 必須先給ORACLE打補(bǔ)丁 否則改之后oracle會(huì)占用 %的CPU 無(wú)法使用 對(duì)于ORACLE i 可以設(shè)置成SIMILAR 這個(gè)設(shè)置綜合了FORCE和EXACT的優(yōu)點(diǎn) 不過(guò)請(qǐng)慎用這個(gè)功能 這個(gè)參數(shù)也可能帶來(lái)很大的負(fù)面影響！
四．將常用的小表 索引釘在數(shù)據(jù)緩存KEEP池中
內(nèi)存上數(shù)據(jù)讀取速度遠(yuǎn)遠(yuǎn)比硬盤中讀取要快 據(jù)稱 內(nèi)存中數(shù)據(jù)讀的速度是硬盤的 倍！如果資源比較豐富 把常用的小的 而且經(jīng)常進(jìn)行全表掃描的表給釘內(nèi)存中 當(dāng)然是在好不過(guò)了 可以簡(jiǎn)單的通過(guò)ALTER TABLE tablename CACHE來(lái)實(shí)現(xiàn) 在ORACLE i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP) 一般來(lái)說(shuō) 可以考慮把 數(shù)據(jù)塊之內(nèi)的表放在keep池中 當(dāng)然要根據(jù)內(nèi)存大小等因素來(lái)定 關(guān)于如何查出那些表或索引符合條件 可以使用本文提供的access sql和access_report sql 這兩個(gè)腳本是著名的Oracle專家 Burleson寫(xiě)的 你也可以在讀懂了情況下根據(jù)實(shí)際情況調(diào)整一下腳本 對(duì)于索引 可以通過(guò)ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)來(lái)釘在KEEP池中
將表定在KEEP池中需要做一些準(zhǔn)備工作 對(duì)于ORACLE i 需要設(shè)置DB_KEEP_CACHE_SIZE 對(duì)于 i 需要設(shè)置buffer_pool_keep 在 i中 還要修改db_block_lru_latches 該參數(shù)默認(rèn)是 無(wú)法使用buffer_pool_keep 該參數(shù)應(yīng)該比 * *CPU數(shù)量少 但是要大于 才能設(shè)置DB_KEEP_CACHE_BUFFER buffer_pool_keep從db_block_buffers中分配 因此也要小于db_block_buffers 設(shè)置好這些參數(shù)后 就可以把常用對(duì)象永久釘在內(nèi)存里
五．設(shè)置optimizer_max_permutations
對(duì)于多表連接查詢 如果采用基于成本優(yōu)化(CBO) ORACLE會(huì)計(jì)算出很多種運(yùn)行方案
從中選擇出最優(yōu)方案 這個(gè)參數(shù)就是設(shè)置oracle究竟從多少種方案來(lái)選擇最優(yōu) 如果設(shè)置太大 那么計(jì)算最優(yōu)方案過(guò)程也是時(shí)間比較長(zhǎng)的 Oracle 和 i默認(rèn)是 建議改成 對(duì)于 i 已經(jīng)默認(rèn)是 了
六．調(diào)整排序參數(shù)
( ) SORT_AREA_SIZE:默認(rèn)的用來(lái)排序的SORT_AREA_SIZE大小是 K 通常顯得有點(diǎn)小 一般可以考慮設(shè)置成 M（ ） 這個(gè)參數(shù)不能設(shè)置過(guò)大 因?yàn)槊總€(gè)連接都要分配同樣的排序內(nèi)存
lishixinzhi/Article/program/Oracle/201311/18879
問(wèn) oracle進(jìn)程內(nèi)存占用一直增加 達(dá)到 G左右的時(shí)候就會(huì)連接失敗 監(jiān)聽(tīng)進(jìn)程死掉 或者CPU達(dá)到 % 如何解決?
Peak Wong
Oracle性能調(diào)優(yōu)一直是一個(gè)很有意思的命題 增強(qiáng)硬件配置是一種方法 但我們平時(shí)遇到的最多的問(wèn)題是如何在沒(méi)辦法增強(qiáng)硬件配置的情況下 將數(shù)據(jù)庫(kù)性能優(yōu)化 這里給出一個(gè)思維流程 希望對(duì)各位有益
PATCH是否都打了 ORACLE系統(tǒng)內(nèi)存參數(shù)是否太大 超出OS的MEMORY
查查是不是程序沒(méi)有關(guān)閉連接導(dǎo)致連接數(shù)不斷上升引起的 你是什么操作系統(tǒng)?
服務(wù)器都作了什么設(shè)置呢?比如sga的分配 是什么情況呢?
要進(jìn)行調(diào)優(yōu) 及參數(shù)設(shè)置
啟動(dòng) Enterprise Management Console 以SYS/**** as SYSDBA身份進(jìn)入系統(tǒng)
ORACLE i調(diào)優(yōu)只涉及如下幾個(gè)參數(shù)
a) processes = ;
b) open_links = ;
c)open_cursors = ;
d)sessions= ;
e) parallel_automatic_tuning=true
f) undo_retention=
g) undo_management=AUTO
請(qǐng)確保在 SPFILE 中保存 在Oracle i缺省的啟動(dòng)參數(shù)是spfile 不要用pfile文件啟動(dòng)數(shù)據(jù)庫(kù)
物理內(nèi)存大于 G以上的通用設(shè)置:
啟動(dòng) Enterprise Management Console 以SYS/**** as SYSDBA身份進(jìn)入系統(tǒng)
配置SGA和PGA大小方法如下
物理內(nèi)存大于 G以上的通用設(shè)置
中文名 參數(shù)名 參數(shù)值 設(shè)置方法
SGA的最大大小 Sga_max_size M 例程 配置 內(nèi)存項(xiàng)卡
日志緩沖區(qū) Log_buffer 例程 配置 一般信息選項(xiàng)卡 所有初始化參數(shù)
大型池 Large_pool_size M 例程 配置 內(nèi)存項(xiàng)卡
Java池 Java_pool_size M 例程 配置 一般信息選項(xiàng)卡 所有初始化參數(shù)
共享池 Shared_pool_size M 例程 配置 內(nèi)存項(xiàng)卡
數(shù)據(jù)緩沖區(qū)高速緩存 Db_cache_size M 例程 配置 內(nèi)存項(xiàng)卡
Keep池 Db_keep_cache_size M 例程 配置 一般信息選項(xiàng)卡 所有初始化參數(shù)
Pga自動(dòng)管理 workarea_size_policy AUTO 例程 配置 一般信息選項(xiàng)卡 所有初始化參數(shù)
總計(jì)pga目標(biāo) pga_aggregate_target M 例程 配置 內(nèi)存項(xiàng)卡
說(shuō)明:
此內(nèi)存設(shè)置不包含在數(shù)據(jù)庫(kù)服務(wù)器上的其它應(yīng)用程序的物理內(nèi)存的大小 如果有其它的應(yīng)用程序 可以參照下面的計(jì)算: sga_max_size+ pga_aggregate_target+應(yīng)用程序物理內(nèi)存+OS物理內(nèi)存 = 系統(tǒng)物理內(nèi)存* % 如果服務(wù)器上只有Oracle服務(wù)器 在 G以上物理內(nèi)存的服務(wù)器上Oracle內(nèi)存參數(shù)都可以參照上面的設(shè)置 如果服務(wù)器上有其它的應(yīng)用 而服務(wù)器總的物理內(nèi)存大于 請(qǐng)自己計(jì)算后再選擇的方案
sga_max_size+ pga_aggregate_target = G 在 bit操作系統(tǒng)上有這個(gè)限制
lishixinzhi/Article/program/Oracle/201311/17386
Oracle性能優(yōu)化基本方法包括一下幾個(gè)步驟 包括
）設(shè)立合理的Oracle性能優(yōu)化目標(biāo)
）測(cè)量并記錄當(dāng)前的Oracle性能
）確定當(dāng)前Oracle性能瓶頸（Oracle等待什么 哪些SQL語(yǔ)句是該等待事件的成分）
）把等待事件記入跟蹤文件
）確定當(dāng)前的OS瓶頸
）優(yōu)化所需的成分（應(yīng)用程序 數(shù)據(jù)庫(kù) I/O 爭(zhēng)用 OS等）
）跟蹤并實(shí)施更改控制過(guò)程
）測(cè)量并記錄當(dāng)前性能
）重復(fù)步驟 到 直到滿足優(yōu)化目標(biāo)
下面來(lái)一一詳述
設(shè)立合理的Oracle性能優(yōu)化目標(biāo)
重點(diǎn) 關(guān)于設(shè)立目標(biāo)的最重要的一點(diǎn)是它們必須是可量化和可達(dá)到的
方法 目標(biāo)必須是當(dāng)前性能和所需性能的的陳述形式的語(yǔ)句
測(cè)量并記錄當(dāng)前Oracle性能重點(diǎn)
）需要在峰值活動(dòng)時(shí)間獲得當(dāng)前系統(tǒng)性能快照
）關(guān)鍵是要在出現(xiàn)性能問(wèn)題的時(shí)間段內(nèi)采集信息
）必須在合理的時(shí)間段上采集 一般在峰值期間照幾個(gè)為期 分鐘的快照
確定當(dāng)前Oracle性能瓶頸重點(diǎn) 從Oracle 等待接口v$system_event v$session_event和v$session_wait中獲得等待事件 進(jìn)而找出影響性能的對(duì)象和sql語(yǔ)句 方法如下
)首先 利用v$system_event視圖執(zhí)行下面的查詢查看數(shù)據(jù)庫(kù)中某些常見(jiàn)的等待事件
select * from v$system_event
where event in ( buffer busy waits
db file sequential read
db file scattered read
enqueue
free buffer waits
latch free
log file parallel write
log file sync );
)接著 利用下面對(duì)v$session_event和v$session視圖進(jìn)行的查詢 研究具有對(duì)上面顯示的內(nèi)容有貢獻(xiàn)的等待事件的會(huì)話
select se sid s username se event se total_waits se time_waited se average_wait
from v$session s v$session_event se
where s sid = se sid
and se event not like SQL*Net%
and s status = ACTIVE
and s username is not null;
)使用下面查詢找到與所連接的會(huì)話有關(guān)的當(dāng)前等待事件 這些信息是動(dòng)態(tài)的 為了查看一個(gè)會(huì)話的等待最多的事件是什么 需要多次執(zhí)行此查詢
select sw sid s username sw event sw wait_time sw state sw seconds_in_wait SEC_IN_WAIT
from v$session s v$session_wait sw
where s sid = sw sid
and sw event not like SQL*Net%
and s username is not null
order by sw wait_time desc;
)查詢會(huì)話等待事件的詳細(xì)信息
select sid event p text p p text p p text p
from v$session_wait
where sid beeen and
and event not like %SQL%
and event not like %rdbms% ;
)利用P P 的信息 找出等待事件的相關(guān)的段
select owner segment_name segment_type tablespace_name
from dba_extents
where file_id = fileid_in
and blockid_in beeen block_id and block_id + blocks ;
)獲得操作該段的sql語(yǔ)句
select sid getsqltxt(sql_hash_value sql_address)
from v$session
where sid = sid_in;
)getsqltxt函數(shù)
)至此已經(jīng)找到影響性能的對(duì)象和sql語(yǔ)句 可以有針對(duì)性地優(yōu)化
把等待事件記入跟蹤文件
重點(diǎn) 如果在跟蹤系統(tǒng)上的等待事件時(shí) 由于某種原因遇到了麻煩 則可以將這些等待事件記入一個(gè)跟蹤文件 方法如下
)對(duì)于當(dāng)前會(huì)話
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events trace name context forever level ;
)執(zhí)行應(yīng)用程序 然后在USER_DUMP_DEST指出的目錄中找到跟蹤文件
)查看文件中以詞WAIT開(kāi)始的所有行
)對(duì)于其它的會(huì)話
)確定會(huì)話的進(jìn)程ID（SPID） 下面的查詢識(shí)別出名稱以A開(kāi)始的所有用戶的會(huì)話進(jìn)程ID
select S Username P Spid from V$SESSION S V$PROCESS P
where S PADDR = P ADDR and S Username like A% ;
)以sysdba進(jìn)入sqlplus執(zhí)行
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
oradebug setospid
oradebug unlimit
oradebug event trace name context forever level X /* Where X = ( ) */
)跟蹤某個(gè)時(shí)間間隔得會(huì)話應(yīng)用程序
)在USER_DUMP_DEST 的值指出的目錄中利用SPID查看跟蹤文件
)查看文件中以詞WAIT開(kāi)始的所有行
確定當(dāng)前OS瓶頸 ）Windows NT上的監(jiān)控
使用控制面板 〉管理工具 〉性能即可
）UNIX上的監(jiān)控
使用通用性的工具 包括sar iostat cpustat mpstat netstat top osview等
．Oracle性能優(yōu)化所需的成分（應(yīng)用程序 數(shù)據(jù)庫(kù) I/O 爭(zhēng)用 OS等）
．跟蹤并實(shí)施更改控制過(guò)程
．測(cè)量并記錄當(dāng)前Oracle性能
lishixinzhi/Article/program/Oracle/201311/18709
在User Schema 上 用人工指定方式指定 Tmp 的tablespace 換句話說(shuō)要人工定義一個(gè)tmp 的tablespace 給user schema ——為colm v這個(gè)user單獨(dú)開(kāi)一個(gè)臨時(shí)表空間 個(gè)人認(rèn)為調(diào)整之后效果不會(huì)有很明顯的變化臨時(shí)表空間的作用是當(dāng)sql語(yǔ)句中出現(xiàn)order by group by（也就是需要排序的時(shí)候） 如果排序的數(shù)據(jù)比較少 就會(huì)在內(nèi)存中排序 排序的數(shù)據(jù)量很大時(shí) oracle會(huì)把排序的任務(wù)放在臨時(shí)表空間中完成 內(nèi)存中排序（邏輯排序）比在磁盤上排序（物理排序）理論上快 倍！所以臨時(shí)表空間很大并不是一件好事情 說(shuō)明sql中存在大量排序的動(dòng)作 sql語(yǔ)句需要優(yōu)化！??? 所以深圳那里需要看一下臨時(shí)表空間的大小！
當(dāng)然johanna的意見(jiàn)也會(huì)有一定的作用 臨時(shí)表空間獨(dú)立出來(lái)之后 就不會(huì)和sys system等一系列oracle 內(nèi)部使用臨時(shí)表空間的操作產(chǎn)生資源競(jìng)爭(zhēng)！
請(qǐng)檢查SQL 使用到的where 條件是否均有定index ? 除此以外 檢查使用到的where 條件最好以index 之順序來(lái)寫(xiě)——這部分比較復(fù)雜 也是性能最關(guān)鍵的地方 幾乎所有的oracle專家都認(rèn)為 %以上的性能調(diào)整都和sql語(yǔ)句優(yōu)化有關(guān) 需要check所有的sql語(yǔ)句where后面的條件是否有用到index的必要 ? 工程量比較浩大！需要一個(gè)個(gè)小心謹(jǐn)慎的check！
把index 做一次 dbms_stats dbms stats 是oracle內(nèi)部的api 可以對(duì)index table進(jìn)行分析 收集統(tǒng)計(jì)信息 這樣oracle優(yōu)化器就會(huì)有一個(gè)最佳的選擇 使性能達(dá)到最佳方法如下
SQLselect tt table_name tt num_rows tt blocks tt empty_blocks tt avg_row_len from dba_tables tt where tt owner= COLMTEST ; SQL select ttt index_name ttt num_rows ttt distinct_keys ttt avg_leaf_blocks_per_key ttt clustering_factor from dba_indexes ttt where ttt owner= COLMTEST ;
執(zhí)行上述兩條命令之后會(huì)發(fā)現(xiàn)除了table_name和index_name 其余列的統(tǒng)計(jì)信息都是不完全的
SQLexecute dbms_stats gather_schema_stats（ownname = COLMTEST cascade=true） ?
執(zhí)行完dbms_stats 再調(diào)用上述兩句語(yǔ)句 會(huì)發(fā)現(xiàn)所有的列基本上都已經(jīng)被填充！
PS 執(zhí)行統(tǒng)計(jì)比較慢 相當(dāng)于所有table index都掃描一遍的時(shí)間 COLMTEST 改一下另外 對(duì)單個(gè)表執(zhí)行統(tǒng)計(jì)分析的語(yǔ)句如下
EXECUTE dbms_stats gather_table_stats (ownname= citic tabname= col_cust_id estimate_percent= cascade=true)
做過(guò)以上處理之后 再看情形 ? 再依情況放參數(shù)
——參數(shù)暫時(shí)不用調(diào)整！
建議做一個(gè)STATSPACK通過(guò)Statspack我們可以很容易的確定Oracle數(shù)據(jù)庫(kù)的瓶頸所在 記錄數(shù)據(jù)庫(kù)性能狀態(tài) 迅速了解數(shù)據(jù)庫(kù)運(yùn)行狀況
方法如下
安裝Statspack安裝Statspack擁有SYSDBA（connect / as sysdba）權(quán)限的用戶登陸 需要在本地安裝或者通過(guò)telnet登陸到服務(wù)器 ——客戶端登錄不可以
必要條件 先創(chuàng)建名稱為perfstat的表空間 至少 M
在那臺(tái)oracle數(shù)據(jù)庫(kù)上用colmv 登錄SQL*PLUS 然后輸入SQL connect sys/sys@（你們那里的sid） as sysdba SQL alter system set timed_statistics = true System altered——使用statspack收集統(tǒng)計(jì)信息時(shí)建議將該值設(shè)置為 TRUE 否則收集的統(tǒng)計(jì)信息大約只能起到 %的作用
SQL @C \oracle\ora \rdbms\admin\spcreate sql 輸入 perfstat_password 的值 ? perfstat輸入default_tablespace的值 ? perfstat輸入temporary_tablespace 的值 ? temp
NOTE SPCPKG plete Please check spcpkg lis for any errors ——需要出現(xiàn)上述語(yǔ)句才算成功 否則請(qǐng)查看 lis文件并執(zhí)行 進(jìn)行重建SQL @C \oracle\ora \rdbms\admin\spdrop sql SQL @C \oracle\ora \rdbms\admin\spcreate sql
查看文件夾會(huì)產(chǎn)生三個(gè)文件C \oracle\ora \bin spcpkg lis spctab lis spcusr lis
——從下面開(kāi)始都可以在客戶端SQL_PLUS進(jìn)行操作 手動(dòng)執(zhí)行STATSPACK收集統(tǒng)計(jì)信息SQL show user USER為 PERFSTAT SQL execute statspack snap ???? ——快照
然后需要經(jīng)過(guò) 個(gè)小時(shí)（跑批需要包含在里面） 再執(zhí)行SQL execute statspack snap ???? ——快照
最后生成STATSPACK調(diào)整報(bào)告
SQL @C:\oracle\ora \rdbms\admin\spreport sql;
Current Instance ~~~~~~~~~~~~~~~~ ? ?? DB Id??? DB Name????? Inst Num Instance ? COLM??????????????? colm ? Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ?? DB Id??? Inst Num DB Name????? Instance???? Host ? ??????? COLM???????? colm???????? STEVENHUANG ? Using? for database Id Using????????? for instance number ? Completed Snapshots ? ??????????????????????? ???Snap??????????????????? Snap Instance???? DB Name???????? Id?? Snap Started??? Level Comment colm???????? COLM?????????? 月 : ???? ??????????????????????????? ???? ???????????????????????????? ??????????? 月 : ???? ??????????????????????????????? ??? Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 輸入 begin_snap 的值:? 輸入 end_snap 的值:? End?? Snapshot Id specified: ? Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_ _ ? To use this name press return to continue otherwise enter an alternative 輸入 report_name 的值:? report txt ? End of Report
查看產(chǎn)生的report 文檔C \oracle\ora \bin\report txt
lishixinzhi/Article/program/Oracle/201311/17298
1、使用兩邊加‘%’號(hào)的查詢，Oracle是不通過(guò)索引的，所以查詢效率很低。
例如：select count(*) from lui_user_base t where t.user_name like '%cs%';
2、like '...%'和 like'%...'雖然走了索引，但是效率依然很低。
3、有人說(shuō)使用如下sql，他的效率提高了10倍，但是數(shù)據(jù)量小的時(shí)候
select count(*) from lui_user_base where rowid in (select rowid from lui_user_base t where t.user_name like '%cs%')
我拿100w跳數(shù)據(jù)做了測(cè)試，效果一般，依然很慢，原因：
select rowid from lui_user_base t where t.user_name like '%cs%' ? 這條sql執(zhí)行很快，那是相當(dāng)?shù)目?，但是放到select count(*) from lui_user_base where rowid in（）里后，效率就會(huì)變的很慢了。
4、select count(*) from lui_user_base t where instr(t.user_name,'cs') 0
這種查詢效果很好，速度很快，推薦使用這種。因?yàn)槲覍?duì)oracle內(nèi)部機(jī)制不是很懂，只是對(duì)結(jié)果做了一個(gè)說(shuō)明。
5、有人說(shuō)了用全文索引，我看了，步驟挺麻煩，但是是個(gè)不錯(cuò)的方法，留著備用：
對(duì)cmng_custominfo 表中的address字段做全文檢索：
1，在oracle9201中需要?jiǎng)?chuàng)建一個(gè)分詞的東西：
BEGIN
ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');
--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); 不用
end;
2，創(chuàng)建全文檢索：
CREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');
3，查詢時(shí)候，使用：
select * from cmng_custominfo where contains (address, '金色新城')1;
4，需要定期進(jìn)行同步和優(yōu)化：
同步：根據(jù)新增記錄的文本內(nèi)容更新全文搜索的索引。
begin
ctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');
end;
優(yōu)化：根據(jù)被刪除記錄清除全文搜索索引中的垃圾
begin
ctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');
end;
5，采用job做步驟4中的工作：
1)該功能需要利用oracle的JOB功能來(lái)完成
因?yàn)閛racle9I默認(rèn)不啟用JOB功能，所以首先需要增加ORACLE數(shù)據(jù)庫(kù)實(shí)例的JOB配置參數(shù):
job_queue_processes=5
重新啟動(dòng)oracle數(shù)據(jù)庫(kù)服務(wù)和listener服務(wù)。
2)同步 和 優(yōu)化
--同步 sync:
variable jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');',
SYSDATE, 'SYSDATE + (1/24/4)');
commit;
END;
--優(yōu)化
variable jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');
commit;
END;
其中， 第一個(gè)job的SYSDATE + (1/24/4)是指每隔15分鐘同步一次，第二個(gè)job的SYSDATE + 1是每隔1天做一次全優(yōu)化。具體的時(shí)間間隔，可以根據(jù)應(yīng)用的需要而定。
6，索引重建
重建索引會(huì)刪除原來(lái)的索引，重新生成索引，需要較長(zhǎng)的時(shí)間。
重建索引語(yǔ)法如下：
ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;
據(jù)網(wǎng)上一些用家的體會(huì)，oracle重建索引的速度也是比較快的，有一用家這樣描述：
Oracle 的全文檢索建立和維護(hù)索引要比ms sql server都要快得多，筆者的65萬(wàn)記錄的一個(gè)表建立索引只需要20分鐘，同步一次只需要1分鐘。
因此，也可以考慮用job的辦法定期重建索引。
實(shí)際上 為了保證ORACLE數(shù)據(jù)庫(kù)運(yùn)行在最佳的性能狀態(tài)下 在信息系統(tǒng)開(kāi)發(fā)之前就應(yīng)該考慮數(shù)據(jù)庫(kù)的優(yōu)化策略 優(yōu)化策略一般包括服務(wù)器操作系統(tǒng)參數(shù)調(diào)整 ORACLE數(shù)據(jù)庫(kù)參數(shù)調(diào)整 網(wǎng)絡(luò)性能調(diào)整 應(yīng)用程序SQL語(yǔ)句分析及設(shè)計(jì)等幾個(gè)方面 其中應(yīng)用程序的分析與設(shè)計(jì)是在信息系統(tǒng)開(kāi)發(fā)之前完成的
分析評(píng)價(jià)ORACLE數(shù)據(jù)庫(kù)性能主要有數(shù)據(jù)庫(kù)吞吐量 數(shù)據(jù)庫(kù)用戶響應(yīng)時(shí)間兩項(xiàng)指標(biāo) 數(shù)據(jù)庫(kù)吞吐量是指單位時(shí)間內(nèi)數(shù)據(jù)庫(kù)完成的SQL語(yǔ)句數(shù)目 數(shù)據(jù)庫(kù)用戶響應(yīng)時(shí)間是指用戶從提交SQL語(yǔ)句開(kāi)始到獲得結(jié)果的那一段時(shí)間 數(shù)據(jù)庫(kù)用戶響應(yīng)時(shí)間又可以分為系統(tǒng)服務(wù)時(shí)間和用戶等待時(shí)間兩項(xiàng) 即
數(shù)據(jù)庫(kù)用戶響應(yīng)時(shí)間＝系統(tǒng)服務(wù)時(shí)間 ＋ 用戶等待時(shí)間
上述公式告訴我們 獲得滿意的用戶響應(yīng)時(shí)間有兩個(gè)途徑 一是減少系統(tǒng)服務(wù)時(shí)間 即提高數(shù)據(jù)庫(kù)的吞吐量 二是減少用戶等待時(shí)間 即減少用戶訪問(wèn)同一數(shù)據(jù)庫(kù)資源的沖突率
性能優(yōu)化包括如下幾個(gè)部分
ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化之一 調(diào)整數(shù)據(jù)結(jié)構(gòu)的設(shè)計(jì)
這一部分在開(kāi)發(fā)信息系統(tǒng)之前完成 程序員需要考慮是否使用ORACLE數(shù)據(jù)庫(kù)的分區(qū)功能 對(duì)于經(jīng)常訪問(wèn)的數(shù)據(jù)庫(kù)表是否需要建立索引等
ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化之二 調(diào)整應(yīng)用程序結(jié)構(gòu)設(shè)計(jì)
這一部分也是在開(kāi)發(fā)信息系統(tǒng)之前完成 程序員在這一步需要考慮應(yīng)用程序使用什么樣的體系結(jié)構(gòu) 是使用傳統(tǒng)的Client/Server兩層體系結(jié)構(gòu) 還是使用Browser/Web/Database的三層體系結(jié)構(gòu) 不同的應(yīng)用程序體系結(jié)構(gòu)要求的數(shù)據(jù)庫(kù)資源是不同的
ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化之三 調(diào)整數(shù)據(jù)庫(kù)SQL語(yǔ)句
應(yīng)用程序的執(zhí)行最終將歸結(jié)為數(shù)據(jù)庫(kù)中的SQL語(yǔ)句執(zhí)行 因此SQL語(yǔ)句的執(zhí)行效率最終決定了ORACLE數(shù)據(jù)庫(kù)的性能 ORACLE公司推薦使用ORACLE語(yǔ)句優(yōu)化器（Oracle Optimizer）和行鎖管理器（row level manager）來(lái)調(diào)整優(yōu)化SQL語(yǔ)句
ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化之四 調(diào)整服務(wù)器內(nèi)存分配
內(nèi)存分配是在信息系統(tǒng)運(yùn)行過(guò)程中優(yōu)化配置的 數(shù)據(jù)庫(kù)管理員可以根據(jù)數(shù)據(jù)庫(kù)運(yùn)行狀況調(diào)整數(shù)據(jù)庫(kù)系統(tǒng)全局區(qū)（SGA區(qū)）的數(shù)據(jù)緩沖區(qū) 日志緩沖區(qū)和共享池的大小 還可以調(diào)整程序全局區(qū)（PGA區(qū)）的大小 需要注意的是 SGA區(qū)不是越大越好 SGA區(qū)過(guò)大會(huì)占用操作系統(tǒng)使用的內(nèi)存而引起虛擬內(nèi)存的頁(yè)面交換 這樣反而會(huì)降低系統(tǒng)
ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化之五 調(diào)整硬盤I/O 這一步是在信息系統(tǒng)開(kāi)發(fā)之前完成的
數(shù)據(jù)庫(kù)管理員可以將組成同一個(gè)表空間的數(shù)據(jù)文件放在不同的硬盤上 做到硬盤之間I/O負(fù)載均衡
ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化之六 調(diào)整操作系統(tǒng)參數(shù)
例如 運(yùn)行在UNIX操作系統(tǒng)上的ORACLE數(shù)據(jù)庫(kù) 可以調(diào)整UNIX數(shù)據(jù)緩沖池的大小 每個(gè)進(jìn)程所能使用的內(nèi)存大小等參數(shù)
lishixinzhi/Article/program/Oracle/201311/17687
重慶分公司
重慶分公司
