[計(jì)算機(jī)軟件及應(yīng)用]oracle數(shù)據(jù)庫性能優(yōu)化-文庫吧
2025-01-04 17:27 本頁面
【正文】 。SQL?80/20原 則優(yōu)化一般過程定位 問題提出 優(yōu) 化方案驗(yàn)證優(yōu)化方案跟蹤 實(shí)際應(yīng) 用發(fā)現(xiàn)問題Oracle 如何處理 SQL語句?Stage 1: Create a Cursor ?Stage 2: Parse the Statement?Stage 3: Bind Any Variables ?Stage 4: Run the Statement?Stage 5: Fetch Rows of a Query?Stage 6: Close the Cursor PARSERSQLOptimizerCBORBODictionaryCostbaseOptimizerRulebaseOptimizerRow SourceGeneratorExecution Planexecute ResultUser執(zhí)行計(jì)劃? 優(yōu)化器決定 SQL語句的執(zhí)行路徑，對(duì)性能至關(guān)重要。? 基于規(guī)則的優(yōu)化器 RBO (Rule Based Optimizer)? No change since oracle ,desupported in oracle 10? Stable, generate the execution plan according to fixed rules? Not necessary good? Do not support new oracle features(like IOT, partition, materialized view,…)? 基于成本的優(yōu)化器 CBO (Cost based optimizer)? Since oracle ? Support all oracle features? Better than RBO in most case, Can be bad in some case? Choose the best execution plan according to internal algorithm? Need to analyze schema to get the best execution planRBO和 CBOOracle操作優(yōu)先級(jí)v RBOPath1:SingleRowbyRowid(等 級(jí) 最高 )v RBOPath2:SingleRowbyClusterJoinv RBOPath3:SingleRowbyHashClusterKeywithUniqueorPrimaryKeyv RBOPath4:SingleRowbyUniqueorPrimaryKeyv RBOPath5:ClusteredJoinv RBOPath6:HashClusterKeyv RBOPath7:IndexedClusterKeyv RBOPath8:Composite16
教學(xué)課件相關(guān)推薦
鄂ICP備17016276號(hào)-1