Re: very slow query on dba_jobs_running...

Hi!

DBA_JOBS_RUNNING is based on JOB$ and V$LOCK view. In recent versions, V$LOCK is one of the views which has ORDERED and USE_NL hints coded into it. This means CBO, but since you don't have statistics on your OBJ$ table, CBO might choose inefficient execution plan. In my test environment, for example, it picks cartesian join between enque resource table x$ksqrs (with 992) entries and JOB$, probably because it thinks that there are little rows in JOB$. If you got 50 rows for example, it already has to join 49600 rows to rest of the tables using nested loops (v$lock is based on 5-6 fixed tables).

Try either one of these:

1. select /*+ RULE */ * from dba_jobs_running;
2. analyze obj$ table regularily (note that analyzing DD is supported in 9.2)
3. set your optimizer_dynamic_sampling greater than 1.

Tanel.

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:bqfuff$4c8$1_at_news.fujitsu-siemens.com...
> Hi!
> 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.
>
> Lots of Greetings!
> Volker
Received on Tue Dec 02 2003 - 06:23:28 CST