Oracle-L: Re: SQL tuning help

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Tue, 26 Nov 2002 14:19:38 -0800
Message-ID: <F001.0050CD02.20021126141938@fatcity.com>

Sergei,

By "hang" I'm going to assume that I can replace that with the phrase "running really slow". If it is actually "hanging" then I think a call to Oracle is in order.

I'm guessing that perhaps statistics were updated or one of the tables changed in size enough to convince the optimisor to take a different approach to the query. Try to get an explain plan for the query.

Looking at the query I have a couple of other questions...

Since the subquery is updating a single row and joins to that row using subsite_id, I see no need for the group by clause - there is only 1 subsite_id that's going to appear. If there is something I don't understand here let me know - I have seen this type of query before and questioned its significance.
It looks like the ideal indexes are metareward.fastcash(subsite_id) and metareward.fastcash(attempt), or perhaps a concatenated index with subsite_id as the leading column. What indexes currently exist? Is the query using them (hence the need for an explain plan)? Have you analyzed the tables recently?

Hopefully I have given you something to work from. To help you further we'd need the explain plan, the row counts and perhaps table/index statistics (details in user_tables and user_indexes, etc). It's difficult to suggest a hint without knowing further details. Finally how many different subsite_id's exist in fastcash and how many of those are you gathering details about? Perhaps a CTAS that calculates details for every subsite_id at once would be much faster if you are looking at most of the fastcash table anyway.

Regards,

Mark.

                                                                                                                   
                    "Sergei"                                                                                       
                    <sergei_at_netfli       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    p.com>               cc:                                                                       
                    Sent by:             Subject:     SQL tuning help                                              
                    root_at_fatcity.c                                                                                 
                    om                                                                                             
                                                                                                                   
                                                                                                                   
                    27/11/2002                                                                                     
                    05:54                                                                                          
                    Please respond                                                                                 
                    to ORACLE-L

Hello everybody,

I have the following query that runs every week.

UPDATE tmp_brian_metareward1 tmp
SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f

                             WHERE f.subsite_id = tmp.subsite_id
                               and attempt >= trunc(sysdate-1)
                               and attempt < trunc(sysdate)
                             group by tmp.subsite_id);

This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do.

Thank you
Sergei

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sergei
  INET: sergei_at_netflip.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).






<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

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).

Received on Tue Nov 26 2002 - 16:19:38 CST