在Oracle数据库出现性能问题时,使用Oracle本身的工具包,给出合理的调优建议是比较省力的做法。
下面两种包的介绍及具体做法。
SQL Tuning Advisor 粒度最小的调整工具.
将一条或多条SQL语句做为输入内容,分析所有访问路径,然后生成改进SQL语句的建议,并提出索引,物化视图和分区方案来提高应用程序的总体性能。
另外,在维护窗口中,Oracle也会针对Automatic Workload Repository(AWR) 来确定和记录的高负载语句自动化运行SQL Tuning Advisor
SQL Access Advisor
它涉及工作量中所有SQL语句,并提出索引,特化视图和分区方案来提高应用程序的总体性能。
小区别:
SQL Tuning Advisor 分别调整每条SQL语句(我之前的博文已经介绍这种方式如何使用)
SQL Access Advisor 同时调整所有SQL语句
SQL Access Advisor:
使用 dbms_advisor.quick_tune来对单个SQL语句进行调整
它的执行很像SQL Tuning Advisor,但执行的分析层次却深入得多,生成的建议也更多。
1. 指定一个存放输出文件的目录
1 2 | --输出文件目录 create directory tune_scripts as '/demo/tune_scripts' ; |
2.采集,分析并给出建议
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | declare v_task_name VARCHAR2( 255 ); v_sql_stmt VARCHAR2( 4000 ); v_tune_sql_filename VARCHAR2( 255 ); v_cfile_directory VARCHAR2( 255 ); begin v_sql_stmt := 'select * from demo.txcl t where t.id = :1 ' ; v_task_name := 'tune_demo_txcl' ; v_tune_sql_filename := 'tune_demo_txcl.sql' ; v_cfile_directory := 'TUNE_SCRIPTS' ; dbms_stats.gather_table_stats( ownname => 'DEMO' , tabname => 'TXCL' , cascade => true ); --dbms_advisor.reset_task(v_task_name); --分析并得到建议 dbms_advisor.quick_tune(DBMS_ADVISOR.SQLACCESS_ADVISOR, v_task_name, v_sql_stmt); --将建议输出到指定文件中 -- select * from dba_advisor_actions where task_name = 'tune_demo_txcl' ; -- select * user_advisor_sqla_wk_stmts where task_name = 'tune_demo_txcl' ; dbms_advisor.create_file( dbms_advisor.get_task_script(v_task_name), v_cfile_directory, v_tune_sql_filename ); --删除任务 dbms_advisor.delete_task(v_task_name); exception when others then dbms_output.put_line(sqlerrm); end;
|
常见错误:
ORA-13600: error encountered in Advisor
QSM-00794: the statement can not be stored due to a violation of the invalid table reference filter
原因是dbms_advisor.quick_tune不支持为SYS和SYSTEM两个用户的表的顾问功能,所能要分析的表不要用这两个用户建。
官网相关文档:
https://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm#PFGRF94881