Re: very slow query on dba_jobs_running...

"Tanel Poder" <change_to_my_first_name_at_integrid.info> schrieb im Newsbeitrag news:3fcc8441$1_1_at_news.estpak.ee...
> 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;
Works great. That's what I'm going to use.

> 2) analyze obj$ table regularily (note that analyzing DD is supported in
> 9.2)

Didn't try it. (Sorry, but that would be too big a change to introduce "just so".)

> 3) set your optimizer_dynamic_sampling greater than 1.
Didn't make a difference with any setting between 0 and 10.

Lots of Greetings and thanks!
Volker Received on Tue Dec 02 2003 - 06:44:56 CST