Re: dba_jobs_running faster as rule based?

On 19 Aug 2003 10:05:18 -0700, RR <rickraster_at_hotmail.com> 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
>

You need to do several runs of each statement. As it looks here, you can't tell anything, because the first select would have cached the relevant dictionary tables, and the second would have merely pulled it out of the cache.

You need to do speed tests about three or four times in succession, discard the best and the worst, and then take an average of the others, before being able to say 'statement X takes Y seconds'.

9i isn't rule-based free. That's going to be 10G's job. But yes, you can analyze the SYS schema in 9i without the huge dramas that would ensue in earlier versions, although it's still not exactly recommended. But personally, the data dictionary is a law unto itself, and is best left that way. It's not as if (I hope) you spend a lifetime querying it.

Regards
HJR Received on Tue Aug 19 2003 - 15:03:42 CDT