SQL Tuning Advisor instance

It is troublesome to optimize an SQL statement before Oracle 10 Gb. However, the SQL Tuning Advisor tool launched in Oracle 10 Gb can greatly reduce the workload of SQL optimization, however, to use SQL Tuning Advisor, make sure that your optimizer is in the CBO mode.

1. Create a user bamboo for optimization and grant the advisor to the created user.

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 two tables for test. Insert 5 million data records into the large table and 0.1 million data records into the small table. The creation method is as follows:
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;
 
3. perform an equijoin query on bigtable and smalltable, and track the execution plan.
SQL> select a. id, a. name, B. id, B. name from bigtable a, smalltable B where a. id = B. id and a. id = 40000;
 
ID NAME
----------------------------------------------------------------------------------------------------
40000 test40000 40000 test40000
 

Execution Plan
----------------------------------------------------------
Plan hash value: 1703851322
 
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
---------------------------------------------------------------------------------
| 0 | select statement | 839 | 106K | 3656 (5) | 00:00:44 |
| * 1 | hash join | 839 | shard k | 3656 (5) | 00:00:44 |
| * 2 | table access full | SMALLTABLE | 5 | 325 | 71 (3) | 00:00:01 |
| * 3 | table access full | BIGTABLE | 173 | 11245 | 3584 (5) | 00:00:44 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id ):
---------------------------------------------------
 
1-access ("A". "ID" = "B". "ID ")
2-filter ("B". "ID" = 40000)
3-filter ("A". "ID" = 40000)
 
Note
-----
-Dynamic sampling used for this statement
 
Statistics
----------------------------------------------------------
9 recursive cballs
0 db block gets
16151 consistent gets
11469 physical reads
0 redo size
588 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
When you are familiar with the execution plan, we can see that the SQL Execution is very slow. The two tables scan the whole table, and the physical read is 11469. Based on the optimization experience, create indexes for the IDs of the two tables to reduce the physical reads during queries. Let's look at the suggestions that oracle can provide through the optimizer?

1 2 3 Next Page