Oracle英文版培训课件之Data Warehouse：Les13

UsingtheSQLAccessAdvisorObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:UsetheSQLAccessAdvisortooptimizeyour

entireworkloadUsetheEnterpriseManagerSQLAccessAdvisorWizardUsetheDBMS_ADVISORproceduresWhatDoestheSQLAccessAdvisorDo?Recommendsthepropersetofmaterializedviews,materializedviewlogs,andindexesforagivenworkloadRecommendsbitmap,function-based,andB-treeindexesRecommendshowtooptimizematerializedviewssothattheycanbefastrefreshableandtakeadvantageofgeneralqueryrewriteSQLAccessAdvisor:OverviewWorkloadSQLAccessAdvisorSolutionComponentofoptimizerProvides

implementationscriptNoexpertise

requiredDBAWhatindexesandmaterializedviewsdoIneedtooptimizemy

entireworkload?FullNotesPageSQLAccessAdvisor:UsageModelIndexesMaterialized

viewsMaterialized

viewlogsSQLAccessAdvisorUser-definedHypotheticalSQLcacheFilteroptionsSTSWorkloadPossibleRecommendationsRecommendationComprehensiveLimitedAddnewindexontableormaterializedview.YESYESDropanunusedindex.YESNOModifyanexistingindexbychangingtheindextype.YESNOModifyanexistingindexbyaddingcolumnsattheend.YESYESAddanewmaterializedview.YESYESDropanunusedmaterializedview.YESNOAddanewmaterializedviewlog.YESYESModifyanexistingmaterializedviewlogtoaddnewcolumnsorclauses.YESYESUsingtheSQLAccessAdvisorInvoketheSQLAccessAdvisorWizardinEnterpriseManager.UseproceduresintheDBMS_ADVISORpackage.SQLAccessAdvisorRepositoryContainsallinformationneededandgeneratedbytheSQLAccessAdvisorPartofthedatabasedictionaryPrivilegesNeededtoUse

theSQLAccessAdvisorADVISORsystemprivilegeSELECTprivilegesontablestargetedformaterializedviewanalysisInvokingtheSQLAccessAdvisorWizardSQLAccessAdvOptions.gifSettingInitialOptionsSpecifyingtheWorkloadSourceSpecifyingRecommendationOptionsSpecifyingRecommendationOptionsFullNotesPageViewingResultsReviewingRecommendationsUsingtheDBMS_ADVISORPackage1. Createataskanddefineparameters.2. Definetheworkload.3. Generatetherecommendations.4. Viewandimplementtherecommendations.SQLAccessAdvisorProcedureFlowSQLWkld

objectSQLAccess

taskCREATE_TASKDELETE_TASKUPDATE_TASK…CREATE_SQLWKLDDELETE_SQLWKLDQUICK_TUNESET_TASK_PARAMETERSET_SQLWKLD_PARAMETERRESET_TASKRESET_SQLWKLDIMPORT_SQLWKLD…ADD_SQLWKLD_STAT…DELETE_SQLWKLD_STAT…UPDATE_SQLWKLD_STAT…ADD_SQLWKLD_REFDELETE_SQLWKLD_REFEXECUTE_TASKINTERRUPT/CANCEL_TASKMARK_RECOMMENDATIONUPDATE_REC_ATTRIBUTESGET_TASK_REPORTCreate/managetasksanddata.Gatherandmanageworkload.Preparetasks.Prepareandanalyzedata.ScriptsCreatingaTaskDefineparametersforthetaskusingDBMS_ADVISOR.SET_TASK_PARAMETER.CreateataskusingDBMS_ADVISOR.CREATE_TASK.VARIABLEtask_idNUMBER;VARIABLEtask_nameVARCHAR2(255);EXECUTE:task_name:='MYTASK';EXECUTEDBMS_ADVISOR.CREATE_TASK('SQLAccessAdvisor',:task_id,:task_name);SettingSQLAccessAdvisorParametersSetparametersusingtheDBMS_ADVISORprocedures:SET_TASK_PARAMETER

SET_SQLWKLD_PARAMETERTypesofparameters:WorkloadfilteringparametersTaskconfigurationparametersSchemaattributesparametersUsingTemplatesCreatedby:SettingthetemplateattributewhencreatingthetaskUsingtheUPDATE_TASK_ATTRIBUTEprocedureSpecifiedwhenusingCREATE_TASK:VARIABLEtemplate_idNUMBER;VARIABLEtemplate_nameVARCHAR2(255);EXECUTE:template_name:='MY_TEMPLATE';EXECUTEDBMS_ADVISOR.CREATE_TASK('SQLAccessAdvisor',:template_id,-:template_name,is_template=>'TRUE');SettingTemplateParametersUseDBMS_ADVISOR.SET_TASK_PARAMETERtosettemplateparameters:EXECUTEDBMS_ADVISOR.SET_TASK_PARAMETER(-:template_name,'INDEX_NAME_TEMPLATE',-'SH_IDX$$_<SEQ>');EXECUTEDBMS_ADVISOR.SET_TASK_PARAMETER(-:template_name,'MVIEW_NAME_TEMPLATE',-'SH_MV$$_<SEQ>');UsingaTemplatetoCreateaTaskSpecifythetemplatewhenusingDBMS_ADVISOR.CREATE_TASK:VARIABLEtask_idNUMBER;VARIABLEtask_nameVARCHAR2(255);EXECUTE:task_name:='MYTASK';EXECUTEDBMS_ADVISOR.CREATE_TASK('SQLAccessAdvisor',:task_id,-:task_name,template=>'MY_TEMPLATE');DefiningtheWorkloadCreatedusingDBMS_ADVISOR.CREATE_SQLWKLD

LinkedtoataskusingDBMS_ADVISOR.ADD_SQLWKLD_REFDefiningWorkloadContentsSQLTuningSetsUser-definedworkloadsSQLcacheworkloadsHypotheticalworkloadsOracleDatabase9iSummaryAdvisorworkloadsImportingaSQLTuningSetVARIABLEsqlsetnameVARCHAR2(30);VARIABLEworkload_nameVARCHAR2(30);VARIABLEsaved_stmtsNUMBER;VARIABLEfailed_stmtsNUMBER;EXECUTE:sqlsetname:='MY_STS_WORKLOAD';EXECUTE:workload_name:='MY_WORKLOAD';EXECUTEDBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);EXECUTEDBMS_ADVISOR.IMPORT_SQLWKLD_STS(:workload_name,-:sqlsetname,'NEW',1,:saved_stmts,:failed_stmts);LoadingUser-DefinedWorkloadsVARIABLEsaved_stmtsNUMBER;VARIABLEfailed_stmtsNUMBER;EXECUTEDBMS_ADVISOR.IMPORT_SQLWKLD_USER(-'MYWORKLOAD','NEW','SH','USER_WORKLOAD',:saved_stmts,:failed_stmts);LoadingSQLCacheWorkloadsVARIABLEsaved_stmtsNUMBER;VARIABLEfailed_stmtsNUMBER;EXECUTEDBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE(-'MYWORKLOAD','APPEND',2,:saved_stmts,:failed_stmts);UsingHypotheticalWorkloadsVARIABLEworkload_nameVARCHAR2(255);VARIABLEsaved_stmtsNUMBER;VARIABLEfailed_stmtsNUMBER;EXECUTE:workload_name:='SCHEMA_WKLD';EXECUTEDBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);EXECUTEDBMS_ADVISOR.SET_SQLWKLD_PARAMETER(:workload_name,-VALID_TABLE_LIST,'SH');EXECUTEDBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA(-:workload_name,'NEW',2,:saved_stmts,:failed_stmts);AddingSQLStatementstoaWorkloadVARIABLEsql_textVARCHAR2(400);EXECUTE:sql_text:='SELECTAVG(amount_sold)FROMsales';EXECUTEDBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(-'MYWORKLOAD','MONTHLY','ROLLUP',priority=>1,executions=>10,-username=>'SH',sql_text=>:sql_text);RemovingWorkloadsEXECUTEDBMS_ADVISOR.DELETE_SQLWKLD('MYWORKLOAD');LinkingtheWorkloadtoaTaskEXECUTEDBMS_ADVISOR.ADD_SQLWKLD_REF('MYTASK','MYWORKLOAD');GeneratingRecommendationsGeneraterecommendationsbyusingDBMS_ADVISOR.EXECUTE_TASK.RecommendationsarestoredintheSQLAccessAdvisorRepository.ViewingandImplementingRecommendationsUsecatalogviews:DBA/USER_ADVISOR_RECOMMENDATIONS

Generateascriptbyusing:DBMS_ADVISOR.GET_TASK_SCRIPTSQLAccessAdvisorActionsCREATE|DROP|RETAIN

MATERIALIZED

VIEWCREATE|ALTER|RETAIN

MATERIALIZED

VIEW

LOGCREATE|DROP|RETAIN

INDEXGATHER

STATS

GeneratingSQLScriptsGenerateaCLOBcontainingthescriptfortherecommendations:SavetheAdvisorscriptCLOBtoafile:EXECUTEDBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'),-'ADVISOR_RESULTS','advscript.sql');CREATEDIRECTORYADVISOR_RESULTSAS'/mydir';GRANTREADONDIRECTORYADVISOR_RESULTSTOPUBLIC;GRANTWRITEONDIRECTORYADVISOR_RESULTSTOPUBLIC;PerformingaQuickTuneVARIABLEtask_nameVARCHAR2(255);VARIABLEsql_stmtVARCHAR2(4000);EXECUTE:sql_stmt:='SELECTCOUNT(*)FROMcustomersWHEREcust_state_province=''CA''';EXECUTE:task_name:='MY_QUICKTUNE_TASK';EXECUTEDBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,-:task_name,:sql_stmt);TuneasingleSQLstatementbyusingtheQUICK_TUNEprocedure:TuningMaterializedViewsforFastRefreshandQueryRewriteDBMS_MVIEW.EXPLAIN_MVIEWprocedure:AdviseswhetheramaterializedviewisfastrefreshableoreligibleforgeneralqueryrewriteDBMS_MVIEW.EXPLAIN_REWRITEprocedure:AdviseswhetherqueryrewritewilloccurDBMS_ADVISOR.TUNE_MVIEWprocedure:AdviseshowtooptimizeyourCREATEMATERIALIZEDVIEWstatementUsingDBMS_ADVISOR.TUNE_MVIEWTUNE_MVIEWtakestwoinputparameters:TASK_NAME:Auser-providedtaskidentifierusedtoaccesstheoutputresultsMV_CREATE_STMT:AcompleteCREATE

MATERIALIZED

VIEWstatementDBMS_ADVISOR.TUNE_MVIEW(task_nameINOUTVARCHAR2,mv_create_stmtIN[CLOB|VARCHAR2])TUNE_MVIEWOutputResultsImplementation:Forimplementingmaterialized