Using Plan StabilityWord文件下载.docx

Using Plan StabilityWord文件下载.docx

《Using Plan StabilityWord文件下载.docx》由会员分享，可在线阅读，更多相关《Using Plan StabilityWord文件下载.docx（16页珍藏版）》请在冰豆网上搜索。

Planstabilitypreventscertaindatabaseenvironmentchangesfromaffectingtheperformancecharacteristicsofapplications.Suchchangesincludechangesinoptimizerstatistics,changestotheoptimizermodesettings,andchangestoparametersaffectingthesizesofmemorystructures,suchasSORT_AREA_SIZEandBITMAP_MERGE_AREA_SIZE.Planstabilityismostusefulwhenyoucannotriskanyperformancechangesinanapplication.

Planstabilitypreservesexecutionplansinstoredoutlines.AnoutlineisimplementedasasetofoptimizerhintsthatareassociatedwiththeSQLstatement.Iftheuseoftheoutlineisenabledforthestatement,Oracleautomaticallyconsidersthestoredhintsandtriestogenerateanexecutionplaninaccordancewiththosehints.

OraclecancreateapublicorprivatestoredoutlineforoneorallSQLstatements.Theoptimizerthengeneratesequivalentexecutionplansfromtheoutlineswhenyouenabletheuseofstoredoutlines.YoucangroupoutlinesintocategoriesandcontrolwhichcategoryofoutlinesOracleusestosimplifyoutlineadministrationanddeployment.

TheplansOraclemaintainsinstoredoutlinesremainconsistentdespitechangestoasystem'

sconfigurationorstatistics.UsingstoredoutlinesalsostabilizesthegeneratedexecutionplaniftheoptimizerchangesinsubsequentOraclereleases.

Note:

Ifyoudevelopapplicationsformassdistribution,thenyoucanusestoredoutlinestoensurethatallcustomersaccessthesameexecutionplans.

UsingHintswithPlanStability

ThedegreetowhichplanstabilitycontrolsexecutionplansisdictatedbyhowmuchtheOraclehintmechanismcontrolsexecutionplans,becauseOracleuseshintstorecordstoredplans.

Thereisaone-to-onecorrespondencebetweenSQLtextanditsstoredoutline.Ifyouspecifyadifferentliteralinapredicate,thenadifferentoutlineapplies.Toavoidthis,replaceliteralsinapplicationswithbindvariables.

SeeAlso:

OraclecanallowsimilarstatementstoshareSQLbyreplacingliteralswithsystem-generatedbindvariables.ThisworkswithplanstabilityiftheoutlinewasgeneratedusingtheCREATE_STORED_OUTLINESparameter,nottheCREATEOUTLINEstatement.Also,theoutlinemusthavebeencreatedwiththeCURSOR_SHARINGparametersettoSIMILAR,andtheparametermustalsosettoSIMILARwhenattemptingtousetheoutline.SeeChapter 

7,"

MemoryConfigurationandUse"

formoreinformation.

Planstabilityreliesonpreservingexecutionplansatapointintimewhenperformanceissatisfactory.Inmanyenvironments,however,attributesfordatatypessuchasdatesorordernumberscanchangerapidly.Inthesecases,permanentuseofanexecutionplancanresultinperformancedegradationovertimeasthedatacharacteristicschange.

Thisimpliesthattechniquesthatrelyonpreservingplansindynamicenvironmentsaresomewhatcontrarytothepurposeofusingqueryoptimization.Queryoptimizationattemptstoproduceexecutionplansbasedonstatisticsthataccuratelyreflectthestateofthedata.Thus,youmustbalancetheneedtocontrolplanstabilitywiththebenefitobtainedfromtheoptimizer'

sabilitytoadjusttochangesindatacharacteristics.

HowOutlinesUseHints

Anoutlineconsistsprimarilyofasetofhintsthatisequivalenttotheoptimizer'

sresultsfortheexecutionplangenerationofaparticularSQLstatement.WhenOraclecreatesanoutline,planstabilityexaminestheoptimizationresultsusingthesamedatausedtogeneratetheexecutionplan.Thatis,Oracleusestheinputtotheexecutionplantogenerateanoutline,andnottheexecutionplanitself.

OraclecreatestheUSER_OUTLINESandUSER_OUTLINE_HINTSviewsintheSYStablespacebasedondataintheOL$andOL$HINTStables,respectively.DirectmanipulationoftheOL$,OL$HINTS,andOL$NODEStablesisprohibited.

YoucanembedhintsinSQLstatements,butthishasnoeffectonhowOracleusesoutlines.OracleconsidersaSQLstatementthatyourevisedwithhintstobedifferentfromtheoriginalSQLstatementstoredintheoutline.

StoringOutlines

OraclestoresoutlinedataintheOL$,OL$HINTS,andOL$NODEStables.Unlessyouremovethem,Oracleretainsoutlinesindefinitely.

Theonlyeffectoutlineshaveoncachingexecutionplansisthattheoutline'

scategorynameisusedinadditiontotheSQLtexttoidentifywhethertheplanisincache.ThisensuresthatOracledoesnotuseanexecutionplancompiledunderonecategorytoexecuteaSQLstatementthatOracleshouldcompileunderadifferentcategory.

EnablingPlanStability

Settingsforseveralparameters,especiallythoseendingwiththesuffix_ENABLED,mustbeconsistentacrossexecutionenvironmentsforoutlinestofunctionproperly.Theseparametersare:

∙QUERY_REWRITE_ENABLED

∙STAR_TRANSFORMATION_ENABLED

∙OPTIMIZER_FEATURES_ENABLE

UsingSuppliedPackagestoManageStoredOutlines

TheDBMS_OUTLNandDBMS_OUTLN_EDITpackageprovidesproceduresusedformanagingstoredoutlinesandtheiroutlinecategories.

UsersneedtheEXECUTE_CATALOG_ROLEroletoexecuteDBMS_OUTLN,butpublichasexecuteprivilegesonDBMS_OUTLN_EDIT.TheDBMS_OUTLN_EDITpackageisaninvoker'

srightspackage.

SomeoftheusefulDBMS_OUTLNandDBMS_OUTLN_EDITproceduresare:

∙CLEAR_USED-Clearsspecifiedoutline

∙DROP_BY_CAT-Dropsoutlinesthatbelongtoaspecifiedcategory

∙UPDATE_BY_CAT-Changesthecategoryofoutlinesinonespecifiedcategorytoanewspecifiedcategory

∙EXACT_TEXT_SIGNATURES-Computesanoutlinesignatureaccordingtoanexacttextmatchingscheme

∙GENERATE_SIGNATURE-GeneratesasignatureforthespecifiedSQLtext

∙PL/SQLPackagesandTypesReferencefordetailedinformationonusingDBMS_OUTLNpackageprocedures

∙PL/SQLPackagesandTypesReferencefordetailedinformationonusingDBMS_OUTLN_EDITpackageprocedures

CreatingOutlines

OraclecanautomaticallycreateoutlinesforallSQLstatements,oryoucancreatethemforspecificSQLstatements.Ineithercase,theoutlinesderivetheirinputfromtheoptimizer.

OraclecreatesstoredoutlinesautomaticallywhenyousettheinitializationparameterCREATE_STORED_OUTLINEStotrue.Whenactivated,OraclecreatesoutlinesforallcompiledSQLstatements.YoucancreatestoredoutlinesforspecificstatementsusingtheCREATEOUTLINEstatement.

Whencreatingoreditingaprivateoutline,theoutlinedataiswrittentoglobaltemporarytablesintheSYSTEMschema.ThesetablesareaccessiblewiththeOL$,OL$HINTS,andOL$NODESsynonyms.

YoumustensurethatschemasinwhichoutlinesaretobecreatedhavetheCREATEANYOUTLINEprivilege.Otherwise,despitehavingturnedontheCREATE_STORED_OUTLINEinitializationparameter,youwillnotfindoutlinesinthedatabaseafteryouruntheapplication.

Also,thedefaultsystemtablespacecanbecomeexhaustediftheCREATE_STORED_OUTLINESinitializationparameterisenabledandtherunningapplicationhasanabundanceofliteralSQLstatements.Ifthishappens,usetheDBMS_OUTLN.DROP_UNUSEDproceduretoremovethoseliteralSQLoutlines.

∙OracleDatabaseSQLReferenceformoreinformationontheCREATEOUTLINEstatement

∙PL/SQLPackagesandTypesReferenceformoreinformationontheDBMS_OUTLNandDBMS_OUTLN_EDITpackages

∙"

MovingfromRBOtotheQueryOptimizer"

forinformationonmovingfromtherule-basedoptimizertothequeryoptimizer

∙OracleEnterpriseManagerConceptsforinformationontheOutlineManagementandOutlineEditortools,whichletyoucreate,edit,delete,andmanagestoredoutlineswithaneasy-to-usegraphicalinterface

UsingCategoryNamesforStoredOutlines

Outlinescanbecategorizedtosimplifythemanagementtask.TheCREATEOUTLINEstatementallowsforspecificationofacategory.TheDEFAULTcategoryischosenifunspecified.Likewise,theCREATE_STORED_OUTLINESinitializationparameterletsyouspecifyacategoryname,wherespecifyingtrueproducesoutlinesintheDEFAULTcategory.

IfyouspecifyacategorynameusingtheCREATE_STORED_OUTLINESinitializationparameter,thenOracleassignsallsubsequentlycreatedoutlinestothatcategoryuntilyouresetthecategoryname.Settheparametertofalsetosuspendoutlinegeneration.

IfyousetCREATE_STORED_OUTLINEStotrue,orifyouusetheCREATEOUTLINEstatementwithoutacategoryname,thenOracleassignsoutlinestothecategorynameofDEFAULT.

UsingandEditingStoredOutlines

Whenyouactivatetheuseofstoredoutlines,Oraclealwaysusesthequeryoptimizer.Thisisbecauseoutlinesrelyonhints,andtobeeffective,mosthintsrequirethequeryoptimizer.

TousestoredoutlineswhenOraclecompilesaSQLstatement,setthesystemparameterUSE_STORED_OUTLINEStotrueortoacategoryname.IfyousetUSE_STORED_OUTLINEStotrue,thenOracleusesoutlinesinthedefaultcategory.IfyouspecifyacategorywiththeUSE_STORED_OUTLINESparameter,thenOracleusesoutlinesinthatcategoryuntilyouresettheparametertoanothercategorynameoruntilyoususpendoutlineusebysettingUSE_STORED_OUTLINEStofalse.IfyouspecifyacategorynameandOracledoesnotfindanoutlineinthatcategorythatmatchestheSQLstatement,thenOraclesearchesforanoutlineinthedefaultcategory.

Ifyouwanttouseaspecificoutlineratherthanalltheoutlinesinacategory,usetheAL