資源描述：
《oracle綁定變量在c#net中的應(yīng)用及意義》由會(huì)員上傳分享，免費(fèi)在線(xiàn)閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫(kù)。
1、1.Whatareboundvariables?Bindvariables(bind,variable):Select*from,EMP,where,empno=:empno;Theuserisinplaceholdersinthequery,itwilltellOracleIwillthenprovideavalueforthevariable,nowneedtocreateaprogram,butmyactualexecutiontime,willprovidetheactualvalueshouldbeused〃?Essentially,itisanalternativevaria
2、blethatreplacesconstantsintheSQLstatement.BindingvariablescanmakeeachcommitSQLstatementexactlythesame?Two.Whydoyouusebindvariables?Usingboundvariablescanreducehardparsingandoptimizetheuseofsharedpools?InOracle,therearetwooptionalparsingprocessesforacommittedSQLstatement,onecalledhardparsing,andon
3、eiscalledsoftparsing?WhenaSQLstatementissubmitted,Oraclewillfirstcheckwhetherthesharedbufferpoolhasexactlythesamestatement,andifso,simplyperformasoftanalysis,oryou'11havetoperformahardanalysis.Ahardparserequiresmanysteps,suchasanalysis,analysis,securitycheck,establishmentofexecutionpath,optimizat
4、ionofaccessplan,andsoon.LargeamountsofCPUandresourcesareconsumed.Forexample,tofindachildnumbered001,thefollowingtwoformsofwritingcanbeachieved:Select*fromt_childwherech訂did二'001';//donotusebindvariables?Select*fromtchildwherechildid二childid;//usebindvariables?However,theactualapplicationisoftenin
5、quiriesforchildrennumbered001,onceaftermaynotbeused;thenyoumayinquireaboutchildren'002',andthenquery'003'andsoon.Thus,eachqueryisanewquery,requiringhardparsing;Whilethesecondqueryprovidesabindingvariable:childid,itsvalueisprovidedinthequeryexecution,thequeryafteracompiledqueryschemeisstoredinthes
6、haredpool,canbeusedtoretrieveandreuse;inperformanceandscalability,thedifferenceishuge,evenisamazing;Withoutbindingvariables,theconditionsforeachqueryvary,resultingintoomanySQLstatementsinthesharedpool,lowreusability,speedinguptheagingofSQLstatements,andresultinginsharedpoolfragmentation.Thenumber
7、ofsharedSQLstatementsdifferentpoolishuge,accordingtotheLRUprinciple,somestatementofagingwaseventuallyclearedsothatthesharedpool;theshared_pool_sizehitrate,thesharedpoolmorefragmented,lackofavailablememoryspace?Inordert
