一个简单的 Oracle SPA示例

简介本文通过一个示例来说明如何进行SPA。SPA即SQL Performance Analyzer按照官网的说明SQL Performance Analyzer enables you to assess the impact ofsystem changeson the response time of SQL statements.SQL性能分析器SQL Performance Analyzer使您能够评估系统变更对SQL语句响应时间的影响。这里所说的系统变更主要包括数据库升级增删索引初始化参数优化器统计信息更新所以评估前后的SQL是不变的SPA并不适用于SQL改写的情形。测试用例我们使用Oracle官方提供的HR 示例 schema测试SQL如下SELECT/* spa_demo */first_name,last_name,salaryFROMemployeesWHEREemployee_id101;这个SQL会使用表的主键---------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||||1(100)|||1|TABLEACCESSBYINDEXROWID|EMPLOYEES|1|23|1(0)|00:00:01||*2|INDEXUNIQUESCAN|EMP_EMP_ID_PK|1||0(0)||---------------------------------------------------------------------------------------------获取此SQL的sql_id本例为9jtr2nznuhn62SELECTsql_idFROMv$sqlWHEREsql_textLIKE% spa_demo %ANDsql_textNOTLIKESELECT sql_id %;SQL_ID9jtr2nznuhn62-- 确认SELECTsql_textFROMv$sqlWHEREsql_id9jtr2nznuhn62;SQL_TEXTSELECT/* spa_demo */first_name,last_name,salaryFROMemployeesWHEREemployee_id101由于后续的SPA操作我们会使用hr用户进行因此需要赋权GRANTADMINISTERSQLTUNINGSETTOhr;GRANTADVISORTOhr;SPA如何操作EXECDBMS_SQLTUNE.CREATE_SQLSET(sqlset_nametest_sqlset);DECLAREl_cursor DBMS_SQLTUNE.SQLSET_CURSOR;BEGINOPENl_cursorFORSELECTVALUE(a)FROMTABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(basic_filtersql_id 9jtr2nznuhn62,-- 使用你的SQL ID进行过滤attribute_listALL))a;DBMS_SQLTUNE.LOAD_SQLSET(sqlset_nametest_sqlset,populate_cursorl_cursor);CLOSEl_cursor;END;/ALTERINDEXEMP_EMP_ID_PK INVISIBLE;VARIABLE task_name VARCHAR2(100);EXEC:task_name :DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_nametest_sqlset);EXECDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name:task_name,execution_typeTEST EXECUTE,execution_namebefore_index);ALTERINDEXEMP_EMP_ID_PK VISIBLE;EXECDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name:task_name,execution_typeTEST EXECUTE,execution_nameafter_index);EXECDBMS_SQLPA.EXECUTE_ANALYSIS_TASK(-task_name:task_name,-execution_typeCOMPARE PERFORMANCE,-execution_namecompare_result,-execution_paramsdbms_advisor.arglist(-execution_name1,before_index,-execution_name2,after_index,-comparison_metric,buffer_gets));SETLONG1000000PAGESIZE0SELECTDBMS_SQLPA.REPORT_ANALYSIS_TASK(:task_name,HTML,TYPICAL,ALL)FROMDUAL;SELECTtask_name,statusFROMDBA_ADVISOR_TASKSWHEREOWNERSYSTEMandadvisor_nameSQL Performance Analyzer;将输出存入一个HTML即可。SPA的查看打开HTML。输出的HTML可从这里下载。在Report Summary部分结论为在使用索引后有提升不过此表很小此处只做参考Report Details部分比较了各项指标注意notes部分在SQL执行前buffer cache已经预热过。也给出了性能提升的结论。执行计划对比一个是全表扫描一个是使用索引。SPA的清理删除task然后删除 SQL tuning set。-- 此处按需调整你的OWNERSELECTtask_name,statusFROMDBA_ADVISOR_TASKSWHEREOWNERHRandadvisor_nameSQL Performance Analyzer;TASK_NAMESTATUSTASK_202 COMPLETEDEXECDBMS_SQLPA.DROP_ANALYSIS_TASK(task_nameTASK_202);EXECDBMS_SQLTUNE.DROP_SQLSET(sqlset_nametest_sqlset);然后就可以重新进行SPA了。最后记得收回赋权REVOKEADMINISTERSQLTUNINGSETFROMhr;REVOKEADVISORFROMhr;