9.2 has a 'gather fixed' option although I haven't tried it as yet.
"Tanel Poder" <change_to_my_first_name_at_integrid.info> wrote in message
news:3f427a6b$1_1_at_news.estpak.ee...
> 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 Fri Aug 22 2003 - 11:13:40 CDT
