Re: SQL Tuning Question

Morning Lisa,

On 27/02/12 22:45, Lisa Hu wrote:
> Dear all,
> I know it’s not a Toad related issue, but would like to ask experts’
> help in tuning an Update process.One Update suddenly ran 2 hours vs.
> normally it runs 10 + minutes.To my knowledge, nothing was changed (of
> course).

I've had a similar problem on 10g myself, but it was not related to the 
SQL or any changes, in fact, it was quite a pain to track down, however, 
just in case it's relevant:

* The problem wasn't always seen, only on occasions;
* The database is in archive log mode;
* The archives are written to the Flash Recovery Area (FRA);
* The parameter DB_RECOVER_FILE_DEST_SIZE is set (too small!!!);
* The database is backed up nightly by RMAN;
* RMAN has an archive log deletion policy set to "backed up three times";
* RMAN backs up to disc outside the FRA;
* The code affected runs at night;

Because the recover file dest size parameter was set too small, 
archiving hung on occasions during the code run as the code was creating 
a large number of archived logs.

The archiver stopped, wrote a message to the alert log and the job hung.

After a while, RMAN kicked in and after the backups, did some tidying up 
of old archived logs thus freeing space in the FRA.

The freed space took the usage in FRA down below the recover file dest 
size setting and so the archiver un-hung and the job completed.

Everyone was looking at the SQL for problems, at table row counts, stats 
etc etc - it was only when I was looking in the alert log for something 
else that I noticed these alerts and on checking  the times and dates 
saw the connection!

We had an alerting system that monitored the alert log for problems but 
it was missing this one somehow. (I don't particularly like or rate our 
monitoring system for this and similar reasons that I will not go in to 
here!)

Interesting one that one though! Once we increased the "broken" setting, 
it just worked - and no more problems since.


> Following is the SQL and execution plan.The DB is in 10g.Any
> help will be greatly appreciated.I am planning to rebuild the indexes
> and then gather new stats just for sake of better performance.

I agree on the stats front, but rebuilding indexes? That almost never 
brings any performance benefits. As soon as you build them, Oracle will 
start splitting blocks etc as the index is used. Oracle is pretty good 
at maintaining indexes.

-- 
Cheers,
Norm. [TeamT]


------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/toad/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/toad/join
    (Yahoo! ID required)

<*> To change settings via email:
    [email protected] 
    [email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/