PLSQL Optimization and Tuning文档格式.docx
- 文档编号:21050108
- 上传时间:2023-01-27
- 格式:DOCX
- 页数:78
- 大小:46.68KB
PLSQL Optimization and Tuning文档格式.docx
《PLSQL Optimization and Tuning文档格式.docx》由会员分享,可在线阅读,更多相关《PLSQL Optimization and Tuning文档格式.docx(78页珍藏版)》请在冰豆网上搜索。
PL/SQLOptimizer
PriortoOracleDatabase10g,thePL/SQLcompilertranslatedyoursourcetexttosystemcodewithoutapplyingmanychangestoimproveperformance.Now,PL/SQLusesanoptimizerthatcanrearrangecodeforbetterperformance.
Theoptimizerisenabledbydefault.Inrarecases,iftheoverheadoftheoptimizermakescompilationofverylargeapplicationstooslow,youcanlowertheoptimizationbysettingthecompilationparameterPLSQL_OPTIMIZE_LEVEL=1insteadofitsdefaultvalue2.Inevenrarercases,PL/SQLmightraiseanexceptionearlierthanexpectedornotatall.SettingPLSQL_OPTIMIZE_LEVEL=1preventsthecodefrombeingrearranged.
SeeAlso:
∙OracleDatabaseReferenceforinformationaboutthePLSQL_OPTIMIZE_LEVELcompilationparameter
∙OracleDatabaseReferenceforinformationaboutthestaticdictionaryviewALL_PLSQL_OBJECT_SETTINGS
SubprogramInlining
Oneoptimizationthatthecompilercanperformissubprograminlining.Subprograminliningreplacesasubprograminvocationwithacopyoftheinvokedsubprogram(iftheinvokedandinvokingsubprogramsareinthesameprogramunit).Toallowsubprograminlining,eitheracceptthedefaultvalueofthePLSQL_OPTIMIZE_LEVELcompilationparameter(whichis2)orsetitto3.
WithPLSQL_OPTIMIZE_LEVEL=2,youmustspecifyeachsubprogramtobeinlinedwiththeINLINEpragma:
PRAGMAINLINE(subprogram,'
YES'
)
Ifsubprogramisoverloaded,thentheprecedingpragmaappliestoeverysubprogramwiththatname.
WithPLSQL_OPTIMIZE_LEVEL=3,thePL/SQLcompilerseeksopportunitiestoinlinesubprograms.Youneednotspecifysubprogramstobeinlined.However,youcanusetheINLINEpragma(withtheprecedingsyntax)togiveasubprogramahighpriorityforinlining,andthenthecompilerinlinesitunlessotherconsiderationsorlimitsmaketheinliningundesirable.
Ifaparticularsubprogramisinlined,performancealmostalwaysimproves.However,becausethecompilerinlinessubprogramsearlyintheoptimizationprocess,itispossibleforsubprograminliningtoprecludelater,morepowerfuloptimizations.
IfsubprograminliningslowstheperformanceofaparticularPL/SQLprogram,thenusethePL/SQLhierarchicalprofiler(explainedinOracleDatabaseAdvancedApplicationDeveloper'
sGuide)toidentifysubprogramsforwhichyouwanttoturnoffinlining.Toturnoffinliningforasubprogram,usetheINLINEpragma:
NO'
TheINLINEpragmaaffectsonlytheimmediatelyfollowingdeclarationorstatement,andonlysomekindsofstatements.
WhentheINLINEpragmaimmediatelyprecedesadeclaration,itaffects:
∙Everyinvocationofthespecifiedsubprograminthatdeclaration
∙Everyinitializationvalueinthatdeclarationexceptthedefaultinitializationvaluesofrecords
WhentheINLINEpragmaimmediatelyprecedesoneofthesestatements,thepragmaaffectseveryinvocationofthespecifiedsubprograminthatstatement:
∙Assignment
∙CALL
∙Conditional
∙CASE
∙CONTINUEWHEN
∙EXECUTEIMMEDIATE
∙EXITWHEN
∙LOOP
∙RETURN
TheINLINEpragmadoesnotaffectstatementsthatarenotintheprecedinglist.
InExample12-1,ifPLSQL_OPTIMIZE_LEVEL=2,theINLINEpragmaaffectstheprocedureinvocationsp1
(1)andp1
(2),butnottheprocedureinvocationsp1(3)andp1(4).
Example12-1SpecifyingthatSubprogramIsToBeInlined
PROCEDUREp1(xPLS_INTEGER)IS...
...
PRAGMAINLINE(p1,'
);
x:
=p1
(1)+p1
(2)+17;
--These2invocationstop1areinlined
=p1(3)+p1(4)+17;
--These2invocationstop1arenotinlined
InExample12-2,ifPLSQL_OPTIMIZE_LEVEL=2,theINLINEpragmaaffectsbothfunctionsnamedp2.
Example12-2SpecifyingthatOverloadedSubprogramIsToBeInlined
FUNCTIONp2(pboolean)returnPLS_INTEGERIS...
FUNCTIONp2(xPLS_INTEGER)returnPLS_INTEGERIS...
PRAGMAINLINE(p2,'
x:
=p2(true)+p2(3);
InExample12-3,theINLINEpragmaaffectstheprocedureinvocationsp1
(1)andp1
(2),butnottheprocedureinvocationsp1(3)andp1(4).
Example12-3SpecifyingthatSubprogramIsNotToBeInlined
--These2invocationstop1mightbeinlined
Multiplepragmascanaffectthesamedeclarationorstatement.Eachpragmaappliesitsowneffecttothestatement.IfPRAGMAINLINE(subprogram,'
)andPRAGMAINLINE(identifier,'
)havethesamesubprogram,then'
overrides'
.OnePRAGMAINLINE(subprogram,'
)overridesanynumberofoccurrencesofPRAGMAINLINE(subprogram,'
),andtheorderofthesepragmasisnotimportant.
InExample12-4,thesecondINLINEpragmaoverridesboththefirstandthirdINLINEpragmas.
Example12-4PRAGMAINLINE...'
OverridesPRAGMAINLINE...'
CandidatesforTuning
ThefollowingkindsofPL/SQLcodeareverylikelytobenefitfromtuning:
∙OldercodethatdoesnottakeadvantageofnewPL/SQLlanguagefeatures.
ForinformationaboutnewPL/SQLlanguagefeatures,see"
What'
sNewinPL/SQL?
"
.
Tip:
Beforetuningoldercode,benchmarkthecurrentsystemandprofiletheoldersubprogramsthatyourprograminvokes(see"
ProfilingandTracingPL/SQLPrograms"
).WiththemanyautomaticoptimizationsofthePL/SQLoptimizer(describedin"
PL/SQLOptimizer"
),youmightseeperformanceimprovementsbeforedoinganytuning.
∙OlderdynamicSQLstatementswrittenwiththeDBMS_SQLpackage.
IfyouknowatcompiletimethenumberanddatatypesoftheinputandoutputvariablesofadynamicSQLstatement,thenyoucanrewritethestatementinnativedynamicSQL,whichrunsnoticeablyfasterthanequivalentcodethatusestheDBMS_SQLpackage(especiallywhenitcanbeoptimizedbythecompiler).Formoreinformation,seeChapter7,"
PL/SQLDynamicSQL."
∙CodethatspendsmuchtimeprocessingSQLstatements.
See"
TuneSQLStatements"
∙Functionsinvokedinqueries,whichmightrunmillionsoftimes.
TuneFunctionInvocationsinQueries"
∙Codethatspendsmuchtimeloopingthroughqueryresults.
TuneLoops"
∙Codethatdoesmanynumericcomputations.
TuneComputation-IntensivePL/SQLCode"
∙CodethatspendsmuchtimeprocessingPL/SQLstatements(asopposedtoissuingdatabasedefinitionlanguage(DDL)statementsthatPL/SQLpassesdirectlytoSQL).
CompilingPL/SQLUnitsforNativeExecution"
MinimizingCPUOverhead
∙TuneSQLStatements
∙TuneFunctionInvocationsinQueries
∙TuneSubprogramInvocations
∙TuneLoops
∙TuneComputation-IntensivePL/SQLCode
∙UseSQLCharacterFunctions
∙PutLeastExpensiveConditionalTestsFirst
TuneSQLStatements
ThemostcommoncauseofslownessinPL/SQLprogramsisslowSQLstatements.TomakeSQLstatementsinaPL/SQLprogramasefficientaspossible:
∙Useappropriateindexes.
Fordetails,seeOracleDatabasePerformanceTuningGuide.
∙Usequeryhintstoavoidunnecessaryfull-tablescans.
∙Collectcurrentstatisticsonalltables,usingthesubprogramsintheDBMS_STATSpackage.
∙AnalyzetheexecutionplansandperformanceoftheSQLstatements,using:
oEXPLAINPLANstatement
oSQLTracefacilitywithTKPROFutility
∙UsebulkSQL,asetofPL/SQLfeaturesthatminimizestheperformanceoverheadofthecommunicationbetweenPL/SQLandSQL.
Fordetails,see"
BulkSQLandBulkBinding"
TuneFunctionInvocationsinQueries
Functionsinvokedinqueriesmightrunmillionsoftimes.Donotinvokeafunctioninaqueryunnecessarily,andmaketheinvocationasefficientaspossible.
Createafunction-basedindexonthetableinthequery.TheCREATEINDEXstatement(describedinOracleDatabaseSQLLanguageReference)mighttakeawhile,butthequerycanrunmuchfasterbecausethefunctionvalueforeachrowiscached.
PL/SQLFunctionResultCache"
forinformationaboutcachingtheresultsofPL/SQLfunctions
Ifthequerypassesacolumntoafunction,thenthequerycannotuseuser-createdindexesonthatcolumn,sothequerymightinvokethefunctionforeveryrowofthetable(whichmightbeverylarge).Tominimizethenumberoffunctioninvocations,useanestedquery.Havetheinnerqueryfiltertheresultsettoasmallnumberofrows,andhavetheouterqueryinvokethefunctionforonlythoserows.
InExample12-5,thetwoqueriesproducethesameresultset,butthesecondqueryismoreefficientthanthefirst.(Intheexample,thetimesandtimedifferenceareverysmall,becausetheEMPLOYEEStableisverysmall.Foraverylargetable,theywouldbesignificant.)
Example12-5NestedQue
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- PLSQL Optimization and Tuning
![提示](https://static.bdocx.com/images/bang_tan.gif)