Re: very slow query on dba_jobs_running...

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)