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...
1) 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.
2) 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@netfli To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
p.com> cc:
Sent by: Subject: SQL tuning help
[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
