Re: Please help tuning a query

"Arindam" <arin_am_at_yahoo.com> wrote in message news:8217ac48.0305081106.458725bb_at_posting.google.com...
> Hi Anurag,
> I guess the simple and easy solution to ur problem is to use MERGE
> statement, which is NEW to 9i. I have personally benchmarked as
> opposed to cursors for a 22 million data and the MERGE just flies.
>
> Thanks
> Arindam
>
> ganesh_at_gtfs-gulf.com (Ganesh Raja) wrote in message
news:<a8aed4.0305080331.7dc04b1d_at_posting.google.com>...
> > "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?
> >
> >
> > Did u try doing the FTS in parallel. It will give u some performance
> > improvments. If you have more than once CPU that is.
> >
> > Regards,
> > Ganesh R

we are on 8173 and have not payed for the parallel query option. Received on Thu May 08 2003 - 15:44:52 CDT