SQL performance tip of the day 11g

Hi all !

After all the big changes we’re now fully back to our posts ! Today’s post will focus on an SQL performance tip. A couple of days ago I just came across with a performance problem regarding a join between a table and a collection. Apparently the Oracle compiler doesn’t properly collect statistics for collections. To demonstrate how I addressed problem we will be using the HR schema from Oracle 11g, so here we goooooo 🙂

1st let’s us create the collection type table_number with

create or replace type table_number as table of number(6) 

2nd Try running the command “set autotrace on” from your command window in SQL Plus. If you ran into trouble with a message like “Cannot set autotrace” . Please setup autotrace like in this tutorial. One of the scripts there must be run with the sys user.  If you forgot the sys user password, you can reset it using this tip. The autotrace facility only works with SQL Plus and therefore we will stick to this program for this example.

3rd Run the query below in SQL PLUS

select e.* from

employees e

where e.employee_id  in (select *FROM TABLE(CAST(table_number(100,110) as table_number ) ));

The expected result would be

As you can see by the query above, the compiler produces a full table scan on table employees, processing 107 rows. This is not very efficient… What about if our table had 10.000 rows?! Just because we join it with a collection should we suffer the pain of a f.t.s. ?! The answer is : NO WAYYY!!!

4th Let us issue a similar query to the above but using the /*+ opt_estimate(table e rows=1) */ oracle hint.

select /*+ opt_estimate(table e rows=1) */ e.*

from    employees e
 where e.employee_id  in (select *    FROM TABLE(CAST(table_number(100,110) as table_number ) ));

The result would be

As you can see, the number of processed rows decreased drastically from 107 to 1 !!! There are other alternatives to this hint usage so feel free to comment on them !