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

1、 2021年年09月月數(shù)據(jù)庫調(diào)優(yōu)專題培訓數(shù)據(jù)庫調(diào)優(yōu)專題培訓培訓目的數(shù)據(jù)庫優(yōu)化總體原那么v為什么要優(yōu)化？為什么要優(yōu)化？v優(yōu)化的范圍？優(yōu)化的范圍？v誰來優(yōu)化？誰來優(yōu)化？v系統(tǒng)設(shè)計人員系統(tǒng)設(shè)計人員v程序開發(fā)人員程序開發(fā)人員v數(shù)據(jù)庫管理員數(shù)據(jù)庫管理員v系統(tǒng)管理員系統(tǒng)管理員v有哪些限制？有哪些限制？數(shù)據(jù)庫優(yōu)化總體原那么優(yōu)化普經(jīng)過程STATSPACK報告v安裝安裝Statspack的預(yù)備的預(yù)備v單獨表空間單獨表空間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腳本將自動創(chuàng)建腳本將自動創(chuàng)建PERFSTAT用戶，并提示保管用戶，并提示保管STATSPACK統(tǒng)計信息的表空間以及暫時表空間統(tǒng)計信息的表空間以及暫時表空間STATSPACK報告v性能快照的采集性能快照的采集vSQLexec statspack.snap; v統(tǒng)計報告的生成統(tǒng)計報告的生成vSQL ?/rdbms/admin/spreport.sql;v性能快照的自動采集性能快照的自動采集vSQL ?/rdbms/admin/spauto.sql;AWR報告報告中關(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)過程Oracle 如何處置 SQL語句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語句的執(zhí)行途徑，對性能至關(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,) 基于本錢的優(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)先級v RBO Path 1: Single Row by Rowid(等級最高等級最高)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(等級最低等級最低)SQL書寫原那么v同樣的同樣的SQLSQL只寫一次。只寫一次。v運用表的別名，降低解析時間。運用表的別名，降低解析時間。vSELECTSELECT子句中防止運用子句中防止運用 * * v減少訪問數(shù)據(jù)庫的次數(shù)減少訪問數(shù)據(jù)庫的次數(shù)v用用WhereWhere子句交換子句交換HAVINGHAVING子句子句v盡量多運用盡量多運用COMMIT COMMIT v用用=替代替代 v用用Union allUnion al

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

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

12、住子表v 在哪些列上不要建立索引在哪些列上不要建立索引v 經(jīng)常有經(jīng)常有DML操作操作v 排它性小排它性小v Select count(1),count(distinct col_name ) from table_name索引創(chuàng)建原那么v 不建議表上的索引超越不建議表上的索引超越5個個v 可以用組合索引那么盡量用組合索引可以用組合索引那么盡量用組合索引v 組合索引的靠左原那么組合索引的靠左原那么v 對于大表，盡量利用對于大表，盡量利用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)過程測試！測試！在適宜的環(huán)境測試！在適宜的環(huán)境測試！影響執(zhí)行方案的要素繼續(xù)改良數(shù)據(jù)庫設(shè)計的三大范式第一范式v第一范式第一范式(1NF)v定義：假設(shè)關(guān)系方式定義：假設(shè)關(guān)系方式R的每個關(guān)系的每個關(guān)系r的屬性都是不的屬性都是不可分的數(shù)據(jù)項，那么就稱可分的數(shù)據(jù)項，那么就稱R是第一范式的方式。是第一范式的方式。v簡單的說，每一個屬性都是原子項，不可分割。簡單的說，每一個屬性都是原子項，不可分割。數(shù)據(jù)庫設(shè)計的三大范式第二范式v第二范式第二范式2NFv定義：假設(shè)關(guān)系方式定義：假設(shè)關(guān)系方式R是是1NF，且每個非主屬性，且每個非主屬性完全函數(shù)依賴于候選

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