Re: Please help tuning a query

From: Ryan <rgaffuri_at_cox.net>
Date: Wed, 07 May 2003 21:27:27 GMT
Message-ID: <3Teua.36540$g41.3163796@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? Received on Wed May 07 2003 - 16:27:27 CDT