Use instances of SQL Tuning Advisor in Oracle

Before oracle10g, it would be a hassle to optimize an SQL statement, but the SQL Tuning Advisor tool, introduced in oracle10g this release, would significantly reduce the workload of SQL tuning, but to use SQL Tuning Advisor, Make sure your optimizer is a CBO model.

1. First you need to create a user bamboo for tuning and give advisor to the user you created

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. Create the user to do the test of the 2 tables, the large table inserted 5 million data, small table inserted 100,000 data, its creation method as follows

Sql> CREATE TABLE BigTable (ID number (), 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 (), 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;