very slow query on dba_jobs_running...

[Volker Hetzer]

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

[Andy Hassall]

Post by Volker Hetzer
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 (***@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[KevJohnP]

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
·
·

Post by Andy Hassall

Post by Volker Hetzer
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
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)
--
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Daniel Morgan]

Post by Volker Hetzer
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

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Dec 1 17:16:37 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select job,sid from dba_jobs_running order by job;

no rows selected

SQL> set timing on
SQL> select job,sid from dba_jobs_running order by job;

no rows selected

Elapsed: 00:00:00.00
SQL> set timing off

This on a W2K box with 512MB RAM. I'd say you have a problem. What are
those jobs doing? How much of your resources are being consumed?

[Volker Hetzer]

Post by Daniel Morgan

Post by Volker Hetzer
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

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Dec 1 17:16:37 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> select job,sid from dba_jobs_running order by job;
no rows selected
SQL> set timing on
SQL> select job,sid from dba_jobs_running order by job;
no rows selected
Elapsed: 00:00:00.00
SQL> set timing off
This on a W2K box with 512MB RAM. I'd say you have a problem. What are
those jobs doing? How much of your resources are being consumed?

I just had a closer look. There was one failed job (statspack.shap) and there's one
other job doing a gather_database_stats every twenty minutes. Both jobs don't
take more than 3 or 4 seconds.
Interestingly enough, after making sure the statspack-job ran again (@sptrunc, since
the tablespace had filled up until the quota), the select took just 7.64 seconds with
no job running and 7.65s with one job (the gather_database_stats) running.
I didn't collect statistics on the system tablespace and sys.job$ doesn't have statistics
either.
Hm. Seems I have to spend some work on snapshot management.

Lots of Greetings!
Volker

[Tanel Poder]

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.

Post by Volker Hetzer
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

[Volker Hetzer]

Post by Tanel Poder
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).
1) select /*+ RULE */ * from dba_jobs_running;

Works great. That's what I'm going to use.

Post by Tanel Poder
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".)

Post by Tanel Poder
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

[Tanel Poder]

Post by Volker Hetzer

Post by Tanel Poder
1) select /*+ RULE */ * from dba_jobs_running;

Works great. That's what I'm going to use.

Awesome ;)
This "hack" can be used with several other DD views as well, like
DBA_FREE_SPACE in LMT database.

Post by Volker Hetzer

Post by Tanel Poder
3) set your optimizer_dynamic_sampling greater than 1.

Didn't make a difference with any setting between 0 and 10.

It seems that DD tables aren't sampled dynamically. I created another table
under SYS and for that it worked...

Post by Volker Hetzer
Lots of Greetings and thanks!

You're welcome,
Tanel.