Subject: Re: SQL Statement
References: <32EE4489.39E7@UWOADMIN.UWO.CA>

>Can anyone tell me why this is happening and what can be done to rectify
>this problem...

>I've got the following SQL...

>       TABLE_A.COL_1 IN
>	     WHERE TABLE_B.COL_2 = 'VAR_2' AND
>		   TABLE_B.COL_3 = 'VAR_3' AND
>		   TABLE_B.COL_4 >= SYSDATE);

>Both Table_A and Table_B are 100,000+ rows!

>When I execute this, it works just fine up to a point!  If the
>sub-select returns less then ~8000 rows, the primary select excutes very
>quickly.  However, if the sub-select returns more than ~8000 rows, the
>primary select goes into a sweep.  The DBA's don't know why this is
>happening!  Is there something at the ORACLE side that needs to be set
>(eg. buffer sizes?, temp space?, indexes?).  I've also noticed that if I
>execute the sub-select on its own and return less than ~8000 rows and
>then re-execute right after, the results come back instantly.  However,
>if the sub-select returns more then ~8000 rows and I re-execute it, it
>takes the same amount of time as the original select (ie. the original
>results are not held in the buffer).

>Any ideas???


>Paul Ferrie
>Advancement Services
>The University of Western Ontario

Paul Ferrie,
There are a few things you could check...first, if you are using the cost-based
optimizer, be sure that the statistics are current:


Next, check the "Hit Ratio" of your database block buffers. Issue the following:

select round(100*(a.value + b.value - c.value) /
       (a.value + b.value))
from   sys.v_$sysstat a,
       sys.v_$sysstat b,
       sys.v_$sysstat c
where  a.statistic# = 37 and
       b.statistic# = 38 and
       c.statistic# = 39;

This will tell you the hit ratio: if it is below 80% you should increase your
"db_block_buffers" initialization parameter. The amount of which to increase is
not within the scope of this email, but basically keep increasing it until your
hit ratio improves.

The reason that you were having improved performance the second time you execute
the statement is that the records are cached into memory, which is much quicker
than reading from disk.

Also, the query performance would rely on what columns TABLE_B.INDEX_1 
comprises. You may also want to place an index on TABLE_A.COL_1 to avoid a
full-table scan of TABLE_A. This will depend on the uniqueness and skew of
the data within that column, but based on what you've indicated I feel the
index will help.

Good luck to you,

-Ari Kaplan
Independent Oracle DBA Consultant

<-> Visit my Web Page:                          <->
<->             email:                    <->

Back to Ari Kaplan's Home Page idth="16" height="18">Back to Ari Kaplan's Home Page