首先，對(duì)語(yǔ)句進(jìn)行優(yōu)化，不要盡量不要寫(xiě)過(guò)于復(fù)雜的嵌套查詢，當(dāng)需要的時(shí)候，后面的子查詢數(shù)量應(yīng)為較大的數(shù)據(jù)。因?yàn)镾QL是從右至左的查詢，先查大的數(shù)據(jù)。然后建立索引，對(duì)經(jīng)常查到的條件字段 例如 時(shí)間 where 月份 = 1月 這樣的語(yǔ)句就應(yīng)該建立所以 來(lái)提高效率。對(duì)于較復(fù)雜的查詢應(yīng)建立組合索引。橫縱向切割表。對(duì)于大數(shù)據(jù)量，上百萬(wàn)條的數(shù)據(jù)可以對(duì)其進(jìn)行橫向切割。比如按時(shí)間的月份進(jìn)行切割，或者按照其他方式來(lái)切割表，達(dá)到快速查詢的目的。減少信息的檢索量。
創(chuàng)新互聯(lián)公司于2013年創(chuàng)立，先為潮州等服務(wù)建站，潮州等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為潮州企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。
幾個(gè)簡(jiǎn)單的步驟大幅提高Oracle性能--我優(yōu)化數(shù)據(jù)庫(kù)的三板斧。
數(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)存分配不合理造成的。(1)假設(shè)內(nèi)存有512M，這通常是小型應(yīng)用。建議Oracle的SGA大約240M，其中：共享池（SHARED_POOL_SIZE）可以設(shè)置60M到80M，根據(jù)實(shí)際的用戶數(shù)、查詢等來(lái)定。數(shù)據(jù)塊緩沖區(qū)可以大致分配120M-150M，8i下需要設(shè)置DB_BLOCK_BUFFERS，DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于數(shù)據(jù)塊緩沖區(qū)大小。9i 下的數(shù)據(jù)緩沖區(qū)可以用db_cache_size來(lái)直接分配。
(2)假設(shè)內(nèi)存有1G，Oracle 的SGA可以考慮分配500M：共享池分配100M到150M，數(shù)據(jù)緩沖區(qū)分配300M到400M。
(3)內(nèi)存2G，SGA可以考慮分配1.2G，共享池300M到500M，剩下的給數(shù)據(jù)塊緩沖區(qū)。
(4)內(nèi)存2G以上：共享池300M到500M就足夠啦，再多也沒(méi)有太大幫助；(Biti_rainy有專(zhuān)述)數(shù)據(jù)緩沖區(qū)是盡可能的大，但是一定要注意兩個(gè)問(wèn)題：一是要給操作系統(tǒng)和其他應(yīng)用留夠內(nèi)存，二是對(duì)于32位的操作系統(tǒng)，Oracle的SGA有1.75G的限制。有的32位操作系統(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)致磁盤(pán)I/O太多，而導(dǎo)致查詢很慢。如果沒(méi)有使用執(zhí)行計(jì)劃穩(wěn)定性，則應(yīng)該把表和索引都分析一下，這樣可能直接會(huì)使查詢速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。對(duì)于少于100萬(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大約有35萬(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(*)
----------
346639
已用時(shí)間: 00: 00: 21.38
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'CASE_GA_AJZLZ'
……………………
請(qǐng)注意上面分析中的TABLE ACCESS(FULL)，這說(shuō)明該語(yǔ)句執(zhí)行了全表掃描。而且查詢使用了21.38秒。這時(shí)表還沒(méi)有經(jīng)過(guò)分析。下面我們來(lái)對(duì)該表進(jìn)行分析：
SQL analyze table CASE_GA_AJZLZ compute statistics;
表已分析。已用時(shí)間: 00: 05: 357.63。然后再來(lái)查詢：
SQL select count(*) from CASE_GA_AJZLZ;
COUNT(*)
----------
346639
已用時(shí)間: 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)
…………………………
請(qǐng)注意，這次時(shí)間僅僅用了0.71秒！這要?dú)w功于INDEX(FAST FULL SCAN)。通過(guò)分析表，查詢使用了PK_AJZLZ索引，磁盤(pán)I/O大幅減少，速度也大幅提升！下面的實(shí)用語(yǔ)句可以用來(lái)生成分析某個(gè)用戶的所有表和索引，假設(shè)用戶是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
解釋?zhuān)荷厦娴恼Z(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
這種方法是8i才開(kāi)始有的，oracle805不支持。通過(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ì)占用100%的CPU,無(wú)法使用。對(duì)于ORACLE9i，可以設(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)比硬盤(pán)中讀取要快，據(jù)稱(chēng)，內(nèi)存中數(shù)據(jù)讀的速度是硬盤(pán)的14000倍！如果資源比較豐富，把常用的小的、而且經(jīng)常進(jìn)行全表掃描的表給釘內(nèi)存中，當(dāng)然是在好不過(guò)了?？梢院?jiǎn)單的通過(guò)ALTER TABLE tablename CACHE來(lái)實(shí)現(xiàn)，在ORACLE8i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP)。一般來(lái)說(shuō)，可以考慮把200數(shù)據(jù)塊之內(nèi)的表放在keep池中，當(dāng)然要根據(jù)內(nèi)存大小等因素來(lái)定。關(guān)于如何查出那些表或索引符合條件，可以使用本文提供的access.sql和access_report.sql。這兩個(gè)腳本是著名的Oracle專(zhuān)家 Burleson寫(xiě)的，你也可以在讀懂了情況下根據(jù)實(shí)際情況調(diào)整一下腳本。對(duì)于索引，可以通過(guò)ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)來(lái)釘在KEEP池中。
將表定在KEEP池中需要做一些準(zhǔn)備工作。對(duì)于ORACLE9i 需要設(shè)置DB_KEEP_CACHE_SIZE，對(duì)于8i，需要設(shè)置buffer_pool_keep。在8i中，還要修改db_block_lru_latches，該參數(shù)默認(rèn)是1，無(wú)法使用buffer_pool_keep。該參數(shù)應(yīng)該比2*3*CPU數(shù)量少，但是要大于1，才能設(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)的。Oracle805和8i默認(rèn)是80000，8建議改成2000。對(duì)于9i，已經(jīng)默認(rèn)是2000了。
六．調(diào)整排序參數(shù)
(1) SORT_AREA_SIZE:默認(rèn)的用來(lái)排序的SORT_AREA_SIZE大小是32K，通常顯得有點(diǎn)小，一般可以考慮設(shè)置成1M（1048576）。這個(gè)參數(shù)不能設(shè)置過(guò)大，因?yàn)槊總€(gè)連接都要分配同樣的排序內(nèi)存。
(2) SORT_MULTIBLOCK_READ_COUNT:增大這個(gè)參數(shù)可以提高臨時(shí)表空間排序性能，該參數(shù)默認(rèn)是2，可以改成32來(lái)對(duì)比一下排序查詢時(shí)間變化。注意，這個(gè)參數(shù)的最大值與平臺(tái)有關(guān)系。
暈死，你那個(gè)20130131都知道是查2013年1月份的了，你就直接='201301'就好了，substr用多了會(huì)造成查詢速度緩慢的
豎著顯示就union?all就好了，像樓上那位說(shuō)的那樣，橫著顯示這樣
select sum(case when term_mon3 then 1 else 0 end) 小于3數(shù)量,
sum(case when term_mon3 then bal else 0 end) 小于3值,
sum(case when term_mon3 and term_mon6 then 1 else 0 end) 大于3小于6數(shù)量,
sum(case when term_mon3 and term_mon6 then bal else 0 end) 大于3小于6值,
sum(case when term_mon6 and term_mon12 then 1 else 0 end) 大于6小于12數(shù)量,
sum(case when term_mon6 and term_mon12 then bal else 0 end) 大于6小于12值,
sum(case when term_mon12 then 1 else 0 end) 大于12數(shù)量,
sum(case when term_mon12 then bal else 0 end) 大于12值
from rpt_xd where substr(beg_date,1,6)= '201301'
先把A表中符合條件（A.OIF_ORDERTYPE between 'D1' and 'D2') and A.OIF_CREATETIME = '20171231000000' and A.OIF_CREATETIME = '20180130000000'的記錄篩選出來(lái)，再進(jìn)行查詢，可以用子查詢來(lái)實(shí)現(xiàn)。
重慶分公司
重慶分公司
