Re: dba_jobs_running faster as rule based?

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Tue, 19 Aug 2003 20:43:30 GMT
Message-ID: <3F428BEE.7020007@nospam_netscape.net>

This issue is explained in some detail in Metalink notes 240058.1 and in bug 2624130.

The definition of DBA_JOBS_RUNNING is:

select v.SID, v.id2 JOB, j.FAILURES,

     LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,
     THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,
     j.field1 INSTANCE

   from sys.job$ j, v$lock v
   where v.type = 'JQ' and j.job (+)= v.id2

Tanel is right. The query uses CBO because v$lock uses ORDERED and USE_NL hints. In fact, the workaround provided by Oracle in Note 240058.1 is to use a /*+ rule */ hint when querying dba_jobs_running, exactly as the original poster did.

I was a bit surprised that DBA_JOBS_RUNNING, which is supposed to be a static data dictionary view (from the DBA_ prefix), actually does a join with v$lock which is a dynamic performance view and is more expensive to run (requires latches to access and can cause contention.)

• Dave

RR wrote:
> 9r2, linux
>
> Consider the following:
>
> SQL>select * from dba_jobs_running;
> SID Jb# F LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
> ---------- ---- --- --------- -------- --------- -------- ----------
> 23 402 0 19-AUG-03 09:46:55 19-AUG-03 09:54:39 0
> 1 row selected.
> Elapsed: 00:00:06.00
>
> SQL>select /*+ rule */ * from dba_jobs_running;
> SID Jb# F LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
> ---------- ---- --- --------- -------- --------- -------- ----------
> 23 402 0 19-AUG-03 09:46:55 19-AUG-03 09:54:39 0
> 1 row selected.
> Elapsed: 00:00:00.00
>
> Can anyone provide any insight into why the rule hint is so much
> faster here? I assumed based on all that I've heard, that v9 was
> basically rule-based free, yet here the hint provides a huge
> performance boost.
>
> Do I need to analyze the sys schema?
>
> Thanks all!
>
> Rick
Received on Tue Aug 19 2003 - 15:43:30 CDT