RE: Tuning Answer (Short)

>----- ------- Original Message ------- -----
>From: New DBA <new_dba_on_the_block_at_yahoo.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Thu, 18 Sep 2003 05:59:37
>
>Hi All,
>
>I'm new on this list and need help on tuning the
>following query. I hope the gurus on this list will
>help me with ideas so that I can attack the problem
>with a much wider knowledge.
>
>I'm providing you with all the details i can manage
>now, but 10046 trace data is not available since we
>don't have access to the servers. We have requested
>the client to provide us with 10046 trace data.
>
>The following query runs in 6 seconds and the
>client wants it to perform faster.
>

Getting rid of views is quite often the first step on the path to Enlightenment. Compare the number of columns you want to be returned to the number of columns in the view: is it reasonable? Obviously not. If you absolutely want a view for security reasons, create a specific one for this query, which only returns what is needed. I must say that I am feeling a bit lazy to delve deeply into something which I see very poorly formatted. But first check whence (from which TABLE) come the columns you want to be returned, as well as those which much match the search criteria you are providing. Check whether you can join the required tables together; if not add the required tables to do the links to the stew. Stir. Determine which is the most selective of values from your input. If it really is selective, check that it is properly indexed. Check also that your joins can execute relatively fast. If you have a join on poorly selective columns, read about hash joins. I am not pretending that by then you'll have a fast running query, but you'll have a sounder base for further tuning.

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).