Re: dba_jobs_running faster as rule based?

Hi!

dba_jobs_running queries v$lock and sys.job$. v$lock contains ORDERED and USE_NL hints which force use of CBO. Since no statistics are calculated (and you even can't calculate stats on x$ tables), a very inefficient plan might be generated.
For example I saw a cartesian join on x$ksqrs table which contains about thousand rows in reality, but CBO used defaults 100 rows for it (also the column on which the join was done had 120 distinct values, but CBO used default NDV of 3). In this case I believe it could be the cartesian join causing the problem (test env 9.2.0.1 on W2k).

If you use only /*+ rule */ hint when querying a view, the underlying hints inside the view are ignored, thus CBO's estimates that a cartesian join would be feasible aren't calculated either.

I think analyzing data dictionary won't help here much, because analyzing doesn't affect X$ tables and the problem comes from false default statistics from x$ tables. This issue might be fixed in newer versions of 9.2.

Tanel.

"RR" <rickraster_at_hotmail.com> wrote in message news:97b4acf3.0308190905.3d9e1535_at_posting.google.com...
> 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 - 14:28:42 CDT