| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Merge Join (Cartesian)
"Ryan" <rgaffuri_at_cox.net> wrote in message
news:p1mpa.150034$yh1.9694675_at_news1.east.cox.net...
>
> "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
> news:Ejkpa.14$vW3.101_at_news.oracle.com...
> > Costing formulas include number of bytes processed by each rowsource.
More
> > columns means more bytes to process. Therefore, adding a column
certainly
> > disturbs all the plan costing analysis performed by optimizer. If you
are
> > really interested, you can capture 10053 trace in both cases and compare
> the
> > diffs.
> you use tkprof to do that right? what tool do you use to analyze trace
file?
> I haven't dug into this yet.
No, I didn't mean tkprof. 10053 trace is a dump of optimizer desisions, not sql execution trace (aka 10046). I meant just plain text difference of the trace files, ehich could be done with unix diff utility, or any GUI based diff. When you add one more column, then 10053 trace is changed, but changed very little.
> > > using ordered is a real
> > > pain when you have a big join. its not always that easy to get the
table
> > > orders correct? anything you can do?
> >
> > But you know the "correct" join order already, right?
> how am i supposed to know the correct join order. If Im doing a 15 table
> join, Ive noticed that its sometimes counter-intuitive. You think the
larger
> table should be the inner table, but its often the opposite(or the other
way
> around, I forget). I found this to be very tedious. Ive found that I can
> rarely ever improve on join order over what oracle does.
In case when you have "good" plan and "bad" plan, as in the message beginning the thread, you can just try imposing join order from the good plan. Tedious, I agree, but can you suggest better alternative?
In case if you don't know the good plan, you need to start from the other end: getting statistics per each rowsource and identify hotspots. For example, sometimes it is easy to see a nested loop where inner rowsource is called excessively so that the most of the execution time bottleneck comes from that part. Then, you just hint a differnt join method in order to verify if you can eliminate bottleneck that way.
This strategy applies since 9.2 only, unfortunately. You have to guesswork in earlier versions, for exaple by executing smaller queries and measuring the performance. Tedious again. SQL Optimization is not an easy topic!
(Arguably, SQL optimization is the only part of RDBMS that has justification to it's complexity. Unlike segment, extent management that is, thanks god, eventually automated)
> cardinality hint? this is one I do not remember seeing. Ill have to check
it
> out. Its not in any of the tuning books Ive seen and Ive read several. or
> maybe I just dont remember it....
You didn't see it because it's not documented.
explain plan for
select /*+ cardinality(emp 10000000) cardinality(emp dept 10) */
count(*) from emp, dept where emp.deptno = dept.deptno;
PLAN_TABLE_OUTPUT
| 0 | SELECT STATEMENT | | 1 | 6 | 2 | | 1 | SORT AGGREGATE | | 1 | 6 | | | 2 | NESTED LOOPS | |*>10<* | 60 | 2 | | 3 | TABLE ACCESS FULL| EMP |*>10M<*| 28M| 2 | |* 4 | INDEX UNIQUE SCAN| SYS_C0035080 | 1 | 3 | | --------------------------------------------------------------------
> > Well, using hints is not easy, but, hey, there is room for oracle
> > improvement!
> so it makes a cartesian join? i thought the optimizer was supposed to be
> pretty good by now. does it still do that in 8i and 9i?
Depends what you "good" definition is. It's certainly getting better. Again, optimization is a really complex problem. Which is true for every RDBMS on the market.
In that particular case, optimizer must be more conservative about choosing a very dangerous join method based solely upon some cardinality estimations. Received on Tue Apr 22 2003 - 21:35:06 CDT
 |
 |