[計(jì)算機(jī)軟件及應(yīng)用]Oracle數(shù)據(jù)庫(kù)性能優(yōu)化ppt課件

1、 2021年年09月月數(shù)據(jù)庫(kù)調(diào)優(yōu)專(zhuān)題培訓(xùn)數(shù)據(jù)庫(kù)調(diào)優(yōu)專(zhuān)題培訓(xùn)培訓(xùn)目的數(shù)據(jù)庫(kù)優(yōu)化總體原那么v為什么要優(yōu)化？為什么要優(yōu)化？v優(yōu)化的范圍？?jī)?yōu)化的范圍？v誰(shuí)來(lái)優(yōu)化？誰(shuí)來(lái)優(yōu)化？v系統(tǒng)設(shè)計(jì)人員系統(tǒng)設(shè)計(jì)人員v程序開(kāi)發(fā)人員程序開(kāi)發(fā)人員v數(shù)據(jù)庫(kù)管理員數(shù)據(jù)庫(kù)管理員v系統(tǒng)管理員系統(tǒng)管理員v有哪些限制？有哪些限制？數(shù)據(jù)庫(kù)優(yōu)化總體原那么優(yōu)化普經(jīng)過(guò)程STATSPACK報(bào)告v安裝安裝Statspack的預(yù)備的預(yù)備v單獨(dú)表空間單獨(dú)表空間90M)v設(shè)置參數(shù)設(shè)置參數(shù)timed_statistics=truev在在init.ora中設(shè)置該參數(shù)，并讓其生效中設(shè)置該參數(shù)，并讓其生效vSQLalter system set timed_s

2、tatistics=true;vSTATSPACK的安裝的安裝vSQL ?/rdbms/admin/spcreate.sql;v腳本將自動(dòng)創(chuàng)建腳本將自動(dòng)創(chuàng)建PERFSTAT用戶(hù)，并提示保管用戶(hù)，并提示保管STATSPACK統(tǒng)計(jì)信息的表空間以及暫時(shí)表空間統(tǒng)計(jì)信息的表空間以及暫時(shí)表空間STATSPACK報(bào)告v性能快照的采集性能快照的采集vSQLexec statspack.snap; v統(tǒng)計(jì)報(bào)告的生成統(tǒng)計(jì)報(bào)告的生成vSQL ?/rdbms/admin/spreport.sql;v性能快照的自動(dòng)采集性能快照的自動(dòng)采集vSQL ?/rdbms/admin/spauto.sql;AWR報(bào)告報(bào)告中關(guān)注哪些

3、內(nèi)容Cache sizeLoad profileInstance efficiencyPercentagesTop timed eventTop SQLInstance Activity statistictablespace IO stats for DBfile IO stats for DBbuffer pool statistics for DBPGA Aggr Target Stats for DB Enqueue activity for DB rollback segment stats for DB rollback segment storage for DB Undo Se

4、gment Stats for DB latch activity for DB dictionary Cache stats for DB SGA memory summary for DB init.ora parameters for DB優(yōu)化普經(jīng)過(guò)程O(píng)racle 如何處置 SQL語(yǔ)句Stage 1: Create a Cursor Stage 2: Parse the StatementStage 3: Bind Any Variables Stage 4: Run the StatementStage 5: Fetch Rows of a QueryStage 6: Close th

5、e Cursor PARSERSQLOptimizerCBORBODictionaryCost-baseOptimizerRule-baseOptimizerRow SourceGeneratorExecution Planexecute ResultUser執(zhí)行方案 優(yōu)化器決議SQL語(yǔ)句的執(zhí)行途徑，對(duì)性能至關(guān)重要。 基于規(guī)那么的優(yōu)化器 RBO (Rule Based Optimizer) No change since oracle 7.3,desupported in oracle 10 Stable, generate the execution plan according to fi

6、xed rules Not necessary good Do not support new oracle features(like IOT, partition, materialized view,) 基于本錢(qián)的優(yōu)化器 CBO (Cost based optimizer) Since oracle 7.3 Support all oracle features Better than RBO in most case, Can be bad in some case Choose the best execution plan according to internal algorit

7、hm Need to analyze schema to get the best execution planRBO和CBOOracle操作優(yōu)先級(jí)v RBO Path 1: Single Row by Rowid(等級(jí)最高等級(jí)最高)v RBO Path 2: Single Row by Cluster Joinv RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Keyv RBO Path 4: Single Row by Unique or Primary Keyv RBO Path 5: Clustered

8、 Joinv RBO Path 6: Hash Cluster Keyv RBO Path 7: Indexed Cluster Keyv RBO Path 8: Composite Indexv RBO Path 9: Single-Column Indexesv RBO Path 10: Bounded Range Search on Indexed Columnsv RBO Path 11: Unbounded Range Search on Indexed Columnsv RBO Path 12: Sort Merge Joinv RBO Path 13: MAX or MIN of

9、 Indexed Columnv RBO Path 14: ORDER BY on Indexed Columnv RBO Path 15: Full Table Scan(等級(jí)最低等級(jí)最低)SQL書(shū)寫(xiě)原那么v同樣的同樣的SQLSQL只寫(xiě)一次。只寫(xiě)一次。v運(yùn)用表的別名，降低解析時(shí)間。運(yùn)用表的別名，降低解析時(shí)間。vSELECTSELECT子句中防止運(yùn)用子句中防止運(yùn)用 * * v減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)v用用WhereWhere子句交換子句交換HAVINGHAVING子句子句v盡量多運(yùn)用盡量多運(yùn)用COMMIT COMMIT v用用=替代替代 v用用Union allUnion al

10、l替代替代unionunionv盡量用盡量用EXISTSEXISTS替代替代ININ、用、用NOT EXISTSNOT EXISTS替代替代NOT INNOT INv用用EXISTSEXISTS交換交換DISTINCTDISTINCTSQL書(shū)寫(xiě)原那么v少用少用in、Or關(guān)鍵詞關(guān)鍵詞v防止在索引列上進(jìn)展計(jì)算防止在索引列上進(jìn)展計(jì)算v在條件上多加引號(hào)，防止改動(dòng)數(shù)據(jù)類(lèi)型在條件上多加引號(hào)，防止改動(dòng)數(shù)據(jù)類(lèi)型v防止在索引列上運(yùn)用防止在索引列上運(yùn)用IS NULL和和IS NOT NULL v運(yùn)用復(fù)合索引的第一個(gè)列進(jìn)展查詢(xún)運(yùn)用復(fù)合索引的第一個(gè)列進(jìn)展查詢(xún)v在索引列上慎用在索引列上慎用LIKE字句字句v在存儲(chǔ)過(guò)程中

11、慎用翻開(kāi)超越一萬(wàn)行數(shù)據(jù)的游標(biāo)在存儲(chǔ)過(guò)程中慎用翻開(kāi)超越一萬(wàn)行數(shù)據(jù)的游標(biāo)v優(yōu)先運(yùn)用優(yōu)先運(yùn)用INT而不是而不是Varchar2v優(yōu)先運(yùn)用優(yōu)先運(yùn)用Varchar2，而不是，而不是CHAR。v防止不用要的排序：防止不用要的排序：distinct、order by索引創(chuàng)建原那么v 提高查詢(xún)語(yǔ)句的效率，減慢了提高查詢(xún)語(yǔ)句的效率，減慢了DML語(yǔ)句的速度語(yǔ)句的速度v 在全表掃描和索引之間權(quán)衡在全表掃描和索引之間權(quán)衡v 在哪些列建立索引在哪些列建立索引v Where字句中援用的列字句中援用的列v Join中援用的列中援用的列v 在子表的在子表的FK上建立索引上建立索引v 防止對(duì)父表操作時(shí)鎖住子表防止對(duì)父表操作時(shí)鎖

12、住子表v 在哪些列上不要建立索引在哪些列上不要建立索引v 經(jīng)常有經(jīng)常有DML操作操作v 排它性小排它性小v Select count(1),count(distinct col_name ) from table_name索引創(chuàng)建原那么v 不建議表上的索引超越不建議表上的索引超越5個(gè)個(gè)v 可以用組合索引那么盡量用組合索引可以用組合索引那么盡量用組合索引v 組合索引的靠左原那么組合索引的靠左原那么v 對(duì)于大表，盡量利用對(duì)于大表，盡量利用local的分區(qū)索引的分區(qū)索引例子1優(yōu)化前例子1優(yōu)化后例子2優(yōu)化前例子2優(yōu)化后例子3優(yōu)化前例子3優(yōu)化后例子4優(yōu)化前例子4優(yōu)化后例子5優(yōu)化前例子5優(yōu)化后例子6優(yōu)化前

13、例子6優(yōu)化后例子7優(yōu)化前例子7優(yōu)化后1例子7優(yōu)化后2優(yōu)化普經(jīng)過(guò)程測(cè)試！測(cè)試！在適宜的環(huán)境測(cè)試！在適宜的環(huán)境測(cè)試！影響執(zhí)行方案的要素繼續(xù)改良數(shù)據(jù)庫(kù)設(shè)計(jì)的三大范式第一范式v第一范式第一范式(1NF)v定義：假設(shè)關(guān)系方式定義：假設(shè)關(guān)系方式R的每個(gè)關(guān)系的每個(gè)關(guān)系r的屬性都是不的屬性都是不可分的數(shù)據(jù)項(xiàng)，那么就稱(chēng)可分的數(shù)據(jù)項(xiàng)，那么就稱(chēng)R是第一范式的方式。是第一范式的方式。v簡(jiǎn)單的說(shuō)，每一個(gè)屬性都是原子項(xiàng)，不可分割。簡(jiǎn)單的說(shuō)，每一個(gè)屬性都是原子項(xiàng)，不可分割。數(shù)據(jù)庫(kù)設(shè)計(jì)的三大范式第二范式v第二范式第二范式2NFv定義：假設(shè)關(guān)系方式定義：假設(shè)關(guān)系方式R是是1NF，且每個(gè)非主屬性，且每個(gè)非主屬性完全函數(shù)依賴(lài)于候選

14、鍵，那么就稱(chēng)完全函數(shù)依賴(lài)于候選鍵，那么就稱(chēng)R是第二范式是第二范式。v簡(jiǎn)單的說(shuō)，第二范式要滿足以下的條件：首先要簡(jiǎn)單的說(shuō)，第二范式要滿足以下的條件：首先要滿足第一范式，其次每個(gè)非主屬性要完全函數(shù)依滿足第一范式，其次每個(gè)非主屬性要完全函數(shù)依賴(lài)與候選鍵，或者是主鍵。也就是說(shuō)，每個(gè)非主賴(lài)與候選鍵，或者是主鍵。也就是說(shuō)，每個(gè)非主屬性是由整個(gè)主鍵函數(shù)決議的，而不能由主鍵的屬性是由整個(gè)主鍵函數(shù)決議的，而不能由主鍵的一部分來(lái)決議。一部分來(lái)決議。數(shù)據(jù)庫(kù)設(shè)計(jì)的三大范式第三范式v第三范式第三范式3NFv定義：假設(shè)關(guān)系方式定義：假設(shè)關(guān)系方式R是是2NF，且關(guān)系方式，且關(guān)系方式RU，F(xiàn)中的一切非主屬性對(duì)任何候選關(guān)鍵字都不存中的一切非主屬性對(duì)任何候選關(guān)鍵字都不存在傳送依賴(lài)，那么稱(chēng)關(guān)系在傳送依賴(lài)，那么稱(chēng)關(guān)系R是屬于第三范式。是屬于第三范式。 v