Re: Join question!

You don't say how the join is performed. An index on user.user_id may help in conjuction with a first rows hint e.g.

     select /*+ first_rows */
       c.customer_name,
      a.f_name agent_fname,

Because you are querying all rows in the customer table, I would expect a hash or sort/merge join to be used, in which case increasing hash_area_size and/or sort_area_size may greatly improve performance i.e.

alter session set hash_area_size=1048576;
alter session set sort_area_size=1048576;
alter session set sort_area_retained_size=1048576;

Additionally, I have found that setting the table to cache can improve performance on repeated full table scans if you have plenty of memory e.g. alter table customer cache;

tshen <tshen01_at_hotmail.com> wrote in message news:RVk68.29228$B94.3929023_at_news02.optonline.net...
> hi,
>
> here is the SQL:
>
> select
> c.customer_name,
> a.f_name agent_fname,
> a.l_name agent_lname,
> b.f_name manager_fname,
> b.l_name manager_lname,
> d.l_name owner_lname,
> d.f_name owner_fname
> from
> customer c,
> user a,
> user b,
> user d
> where c.agent_id = a.user_id(+)
> and c.manager_id = b.user_id(+)
> and c.owner_id = d.user_id(+)
>
>
>
> table user is about 300,000 rows, execution plan shows there are 3
> full table scan for
> table "user" and 1 full table scan for table "customer", is there
any
> way to make the performance better?
>
> Another question? our DB only about 700M size, and the machine is
> very powerful, all the tables are pined in RAM, although we have a lot of
> SQL like above which are bad SQL, but since the machine is powerful and
> dedicated to oracle, it should not be very that bad performance, and the
CPU
> idle time sometime is 0%?
>
>
>
Received on Fri Feb 01 2002 - 00:08:58 CST