在oracle10g之前,想要优化一个sql语句是比较麻烦,但是在oracle10g这个版本推出的SQL Tuning Advisor这个工具,能大大减少sql调优的工作量,不过要想使用SQL Tuning Advisor,一定要保证你的 优化器是CBO模式。
1.首先需要创建一个用于调优的用户bamboo,并授予advisor给创建的用户
SQL> create user bamboo identified by bamboo;
User created.
SQL> grant connect,resource to bamboo;
Grant succeeded.
SQL> grant advisor to bamboo;
Grant succeeded.
2.创建用户做测试的2张表,大表里面插入500万条数据,小表里面插入10万条数据,其创建方法如下
SQL> create table bigtable (id number(10),name varchar2(100));
Table created.
SQL> begin
2 for i in 1..5000000 loop
3 insert into bigtable values(i,'test'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commti;
SQL> create table smalltable (id number(10),name varchar2(100));
Table created.
SQL> begin
2 for i in 1..100000 loop
3 insert into smalltable values(i,'test'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commti;