SQL Tuning Advisor(STA): 使用oracle提供的程序包进行sql优化
SQL> conn scott/tiger SQL> 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> declare
l_task_id varchar2(20);
l_sql varchar2(2000); begin
l_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 sysdba
SQL> grant create materialized view to scott; SQL> conn scott/tiger SQL> 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 dollars
FROM sh.sales s
, sh.times t
WHERE s.time_id = t.time_id
AND 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_amount
FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND 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_amount
FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND 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_name
FROM sh.customers c
WHERE 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 sysdba
set echo on
select 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 sysdba
SQL> 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;