Re: SQL tuning help

Sergei,

When the query is running try to collect some stats, especially session waits, from v$session_wait and see where the waits are happening.

Or you could do this from command line

alter session set event '10046 trace name context forever, level 8'; << your query>>
alter session set event '10046 trace name context off';

This will produce a trace file in user_dump_dest directory. Tkprof that fiel to see the explain plans and all, see if everything is as per expectation.
>From the raw trace file you could see the wait events occuring and where
they occur.

My guess is you have seen buffer busy waits on most cases. Increase the initrans, maxtrans, freelist and freelist groups parameter of the indexes used in this query and rebuild them. This will alleviate several problems.

Did someone chaneg the optimizer_goal? Did you have RULE before and CHOOSE now?

If you use RULE, did someone analyzed any of the tables, including SYS owner tables?

HTH Arup Nanda
www.proligence.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, November 26, 2002 1:54 PM

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

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