Re: very slow query on dba_jobs_running...

"Daniel Morgan" <damorgan_at_x.washington.edu> schrieb im Newsbeitrag news:1070327926.415067_at_yasure...
> Volker Hetzer wrote:
>
> > 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?
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 Received on Tue Dec 02 2003 - 06:34:16 CST