Re: Please help tuning a query

"Ryan" <rgaffuri_at_cox.net> wrote in message news:3Teua.36540$g41.3163796_at_news1.east.cox.net...
>
> "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message
> news:8tribv00oec00lk7lagkcepia92uf103jd_at_4ax.com...
> > On 7 May 2003 12:49:51 -0700, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:
> >
> > >I have a staging table and a production table. I need to update my
> > >production table based based on what is in the staging table. So I
> > >have the following cursor.
> > >
> > >According to Guy Harrison's book running a cursor and then updating
> > >based on the rowid is supposed to be faster, however, I think the CBO
> > >is writing bad plan.
> > >
> > >I just used dbms_stats with estimate=20 and Cascade=>true on each of
> > >the tables.
> > >
> > >select b.col1,
> > > b.col2,
> > > b.etc
> > >from stage_Schema.tab s, prod_schema.tab p
> > >where s.col2 = p.col2
> > > and s.col3 = p.col3
> > > and so on...
> > >
> > >All of the primary key columns are joined. So I think the CBO should
> > >do a nested loop join with a fast full index scan. Each table has
> > >exactly the same number of rows.
> > >
> > >Here is my explain plan. Why is it using a sort merge? The update with
> > >the where exists is actually alot faster.
> > >
> > >Operation Object Name Rows Bytes Cost
> > >SELECT STATEMENT Hint=CHOOSE 2 M 102385 MERGE JOIN
> > > 2 M 398 M 102385 INDEX FULL SCAN PK_IND_TAB 2 M 97 M 23494
> > > SORT JOIN 2 M 298 M 74520 TABLE ACCESS
> > >FULL TAB 2 M 298 M 4371
> > >
> > >Sorry about the formatting. All the columns in the join are part of
> > >the primary key. I can force an index scan on the inner table, but I
> > >cant force a nested loop join???
> >
> >
> > You don't have any criteria on either table. So how do you expect it
> > wouldn't choose a full table scan?
> > Also: did you change optimizer_index_cost_adj and
> > optimizer_index_caching from the defaults, which are lousy and almost
> > always result in full table scan?
> > Finally: why do you think the 'update with where exists is actually a
> > lot faster'? You are aware the CBO tries to reduce the number of
> > I/O's, it doesn't reduce the number of seconds elapsed?
> >
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
> Update with where exists is supposed to be slower according to the tuning
> book Im using.
>
> Specs on the tables. Both are essentially identical. Same columns. Same
> primary keys. In this case same number of rows. One of them however, has a
> non-uniqie index on one of its primary key columns.
>
> I think it should use a nested loop join by doing a fast full index scan.
>
> Not familiar with the following parameters. Ill ask our dbas about them
> tomorrow.
>
> optimizer_index_cost_adj
> optimizer_index_caching
>
> What are the defaults? So I can check to see if we have them.
>
> Sorry about the messy query plan. I posted from the web.
>
> Operation Object
> Name Rows Bytes Cost
> SELECT STATEMENT Hint=CHOOSE 2M
> 102385
> MERGE JOIN
> 2 M 398 M 102385
> INDEX FULL SCAN PK_IND_TAB 2 M
> 97 M 23494
> SORT JOIN
> 2 M 298 M 74520
> TABLE ACCESS FULL TAB 2 M
> 298 M 4371
>
> could someone help me intepret this? Why is it doing both a 'merge join'
and
> a 'sort join' and what is the difference? I know what a sort-merge join
is?
>
>
>
>

is there anyway to get that plan to line up so people can read it? Im not using tabs. Just the space bar? Received on Wed May 07 2003 - 16:33:05 CDT