Re: very slow query on dba_jobs_running...

From: KevJohnP <nospam_at_nowhere.com>
Date: Tue, 02 Dec 2003 11:27:45 +1300
Message-ID: <CfPyb.16041$VV6.352870@news.xtra.co.nz>

Yes - we had this problem too and it is optimizer related though strangely enough it happens even if you have not collected stats on SYS.

Part of the Ora bug info below. Workaround is to use RULE hint.

KJP

·             fact: Oracle Server - Enterprise Edition 9.2
·
·             symptom: Query on DBA_JOBS_RUNNING is slow
·
·             cause: This problem is described in the <Bug:2624130> - 
QUERY AGAINST
·             DBA_JOBS_RUNNING IS USING CBO EVEN WHEN NO STATISITICS

·
·

Andy Hassall wrote:

> On Mon, 1 Dec 2003 18:41:01 +0100, "Volker Hetzer" <volker.hetzer_at_ieee.org>
> wrote:
>
>

>>I've got a query "select job,sid from dba_jobs_running order by job"
>>which takes really long (10min or longer) on an idle database with at most
>>2 jobs running in parallel.
>>Does anyone else have this problem?
>>I'm using 9.2.0.4.0 on RH linux.

>
>
> (Bit of a shot in the dark since I don't know if this applies to DBA_JOBS, but
> have seen this issue affect queries on other data dictionary views):
>
> Do you have statistics on the SYS schema? Whilst this is supported in 9.2 (not
> in previous versions), there are some warnings (see Metalink for references).
> I've seen it massively degrade performance on particular data dictionary
> queries, whereas dropping the stats / going back to RBO for the query reverts
> back to the normal speed.
>
> (CBO on the data dictionary in 10g is likely to be mandatory, but hopefully
> they will have made the necessary small adjustments, based on the views in 9.2
> that don't perform well under CBO)
>
> --
> Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Received on Mon Dec 01 2003 - 16:27:45 CST