SQL Tuning Advisor(STA): 使用oracle提供的程序包进行sql优化
SQL> conn scott/tigerSQL> create table manual_sta(id varchar2(10), name varchar2(128));SQL> insert into manual_sta select object_id, object_name from dba_objects;SQL> commit;
在id列上创建一个索引，并收集统计信息。
SQL> create index idx_manual_sta on manual_sta(id);SQL> exec dbms_stats.gather_table_stats(user,'manual_sta',cascade=>true);
调用STA对SQL语句进行调优:
SQL> declarel_task_id varchar2(20);l_sql varchar2(2000);beginl_sql := 'select * from manual_sta where id=2000';l_task_id := dbms_sqltune.create_tuning_task( sql_text=>l_sql, user_name=>'SCOTT', scope=>'COMPREHENSIVE', time_limit=>30, task_name=>'MANUAL_STA');end;/
执行sql调优任务。
SQL> begin dbms_sqltune.execute_tuning_task('MANUAL_STA');end;/ 显示调优结果
SQL> set serveroutput on size 999999;SQL> set long 99999999;SQL> select dbms_sqltune.report_tuning_task('MANUAL_STA') from dual; 显示结果中会建议建一个函数索引。
删除任务：
SQL> exec dbms_sqltune.drop_tuning_task('MANUAL_STA'); 物化视图
SQL> conn /as sysdbaSQL> grant create materialized view to scott;SQL> conn scott/tigerSQL> create table e as select * from emp;SQL> create table d as select * from dept;SQL> drop materialized view log on e;SQL> drop materialized view log on d;SQL> drop materialized view mv_e_d;SQL> create materialized view log on e with rowid(empno, ename, job, mgr, hiredate, sal, comm, deptno) including new values;SQL> create materialized view log on d with rowid(deptno, dname, loc) including new values;SQL> create materialized view mv_e_d build immediate refresh complete on commit as select a.dname, sum(b.sal) total_sal, count(*) c_sal from d a, e b where a.deptno=b.deptno group by a.dname;
SQL Acess Advisor(SAA)
1. 创造数据库负载
vi /u01/app/oracle/temptest/1.sql
SELECT /* QueryDW 1*/t.calendar_month_desc,sum(s.amount_sold) AS dollarsFROM sh.sales s, sh.times tWHERE s.time_id = t.time_idAND s.time_id between TO_DATE('01-JAN-2000', 'DD-MON-YYYY')AND TO_DATE('01-JUL-2000', 'DD-MON-YYYY')GROUP BY t.calendar_month_desc; vi /u01/app/oracle/temptest/2.sql
SELECT /* QueryDW 2 */ch.channel_class, c.cust_city, t.calendar_quarter_desc,SUM(s.amount_sold) sales_amountFROM sh.sales s, sh.times t, sh.customers c, sh.channels chWHERE s.time_id = t.time_idAND s.cust_id = c.cust_idAND s.channel_id = ch.channel_idAND c.cust_state_province = 'CA'AND ch.channel_desc in ('Internet','Catalog')AND t.calendar_quarter_desc IN ('1999-01','1999-02')GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc; vi /u01/app/oracle/temptest/3.sql
SELECT /* QueryDW 3 */ch.channel_class, c.cust_city, t.calendar_quarter_desc,SUM(s.amount_sold) sales_amountFROM sh.sales s, sh.times t, sh.customers c, sh.channels chWHERE s.time_id = t.time_idAND s.cust_id = c.cust_idAND s.channel_id = ch.channel_idAND c.cust_state_province = 'CA'AND ch.channel_desc in ('Internet','Catalog')AND t.calendar_quarter_desc IN ('1999-03','1999-04')GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc; vi /u01/app/oracle/temptest/4.sql
SELECT /* QueryDW 4 */ c.country_id, c.cust_city, c.cust_last_nameFROM sh.customers cWHERE c.country_id in (52790, 52798)ORDER BY c.country_id, c.cust_city, c.cust_last_name;
执行以上4个脚本：
SQL> conn sh/sh@/u01/app/oracle/temptest/1.sql@/u01/app/oracle/temptest/2.sql@/u01/app/oracle/temptest/3.sql@/u01/app/oracle/temptest/4.sql
2. 创建sql调优集：
vi /u01/app/oracle/temptest/sts.sql
connect / as sysdbaset echo onselect sql_text from v$sql where sql_text like '%QueryDW%';BEGIN dbms_sqltune.drop_sqlset('MY_STS_WORKLOAD');END;/DECLARE sqlsetname VARCHAR2(30); sqlsetcur dbms_sqltune.sqlset_cursor;BEGIN sqlsetname := 'MY_STS_WORKLOAD'; dbms_sqltune.create_sqlset(sqlsetname, 'Access Advisor data'); OPEN sqlsetcur FOR SELECT VALUE(P) FROM TABLE( dbms_sqltune.select_cursor_cache( 'sql_text like ''SELECT /* QueryDW%''', NULL, NULL, NULL, NULL, NULL, null) ) P;dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);END;/ 执行以上的脚本：
@/u01/app/oracle/temptest/sts.sql
此脚本创建了一个sql调优集：MY_STS_WORKLOAD，此集中包含了第一步执行的sql语句
3. 进入OEM查看调用SAA来对调优集：MY_STS_WORKLOAD中的sql语句进行调优
4. 清除实验环境：
SQL> connect / as sysdbaSQL> exec dbms_sqltune.drop_sqlset('MY_STS_WORKLOAD');SQL> drop materialized view log on sh.customers;SQL> drop materialized view log on sh.channels;SQL> drop materialized view log on sh.times;SQL> drop materialized view log on sh.sales;