Re: 8i - Joining with Temporary Tables

In article <gm%85.21855$Yb7.237346_at_zombie.newscene.com>,   dsad_at_hotmail.com (dsad) wrote:
>
> if the temp tables are so small why not have them always cached?
>

if they are small -- they will be cached. this isn't about where they are but rather this is about getting the optimizer to understand that they are small (or big) and developing the correct plan accordingly. Whether they were totally cached or totally on disk doesn't matter if the query plan is totally wrong to begin with ;)

> In article <8jpqsq$9vq$1_at_nnrp1.deja.com>, Thomas J. Kyte
 <tkyte_at_us.oracle.com>
> wrote:
> >In article
> ><D43DA1219E435E3F.F35185795A6051FA.0BC5404355AAB71E_at_lp.airnews.net>,
> > djkucera_at_sympatico.ca (Dylan Kucera) wrote:
> >> >You just gave me an idea. what about dbms_stats? we can analyze
 the
> >> >table (but that doesn't have any effect -- it'll come back with
 NULL
> >> >num_rows and such) but we can write the stats we believe will be
 true
> >> >into the DD using dbms_stats. The following:
> >>
> >> ..snip...
> >>
> >> >Perhaps Dylan (i've cc'ed him) can try this out and let us know.
 He
> >> >can set the number of rows to what he believes is the correct
 amount
> >> >using dbms_stats after creating the tempporary table and the CBO
 should
> >> >have a better go of it.
> >>
> >> Canada Day weekend or not, you guys are so great for helping me
 out,
 I went in
> >> to the office to try this out.
> >>
> >> Well, it was worth it. I did the dbms_stats.set_table_stats on my
 four
> >> temporary tables, then ran my monster join. The optimizer picked
 an
> >> appropriate plan and returned with the results almost instantly.
> >>
> >> As per my original description, most of my temporary tables will
 contain very
> >> few records: much of the time 1, some of the time 10, and
 occationally 100.
> >> A temporary table in my application should always fall into one of
 those
> >> 3 buckets, so I expect to be able to set_table_stats once and be
 done
 with it.
> >> I am a little confused about the parameters though, I used 1, 1,
 256
 for
> >> numrows, numblks and avgrlen respectively. Can you suggest a more
 appropriate
> >> set of numbers? Can you help me with the last 2 parameters in the
 case of the
> >> 100 record temporary table?
> >>
> >
> >numrows should be set to the expected total number of rows in the
 table.
> >numblks should be the total number of database blocks you expect.
> >avgrlen should be the average row width of your row.
> >
> >so, how to figure?
> >
> >numrows would be set in your case to 1, 10 or 100.
> >
> >numblks would be the number of blocks that would be read in a full
> >scan. these are the blocks below the high water mark. In this case,
> >with a temporary table, this should be simply the
> >number_rows/((DB_BLOCK SIZE - FIXED_OVERHEAD)/average_row_width )
> >
> >lets say you expected 1,000 rows in the temp table.
> >you have a block size of 8k.
> >The average row was expected to be 500 bytes.
> >
> >You have maybe somewhere between 7,500-8,000 bytes of space on a
 block,
> >you'll get about 15-16 rows/block. It'll take about 67 blocks to
 hold
> >this data. Another way to arrive at this number could be:
> >
> >o fill up the temp table in a session with good representative data.
> >o count blocks by rowid
> >
> >for example:
> >
> >ops$tkyte_at_8i> insert into temptable select rownum, object_name from
> >all_objects where rownum < 1000;
> >
> >999 rows created.
> >
> >Thats my "representative data" -- you would run a real query on your
> >system using real data...
> >
> >ops$tkyte_at_8i> select count( distinct dbms_rowid.rowid_block_number
> >(rowid) ) from temptable;
> >
> >COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
> >---------------------------------------------------
> > 333
> >
> >That shows I'll scan about 333 blocks in a full scan on this one
 (that
> >can be confirmed with SQL_TRACE, the high water mark for the temp
 table
> >is the number of blocks used in your session). Thats what I would
 set
> >numblks to.
> >
> >
> >The last one, avgrlen is pretty easy. put in the average row width
 you
> >expect. Perhaps the easiest way to get this again is the tried and
> >true "fill the table and see what it says" trick. Take that same
> >representative data set above we just figured was 333 blocks and
 query:
> >
> >select avg(vsize(COL1)+vsize(COL2)+....+vsize(COLN)) from temptable;
> >
> >that'll give you the avgrlen for the row for a good representative
> >result set.
> >
> >Thats the best we can do for this I believe.
> >
> >It should be noted that you can use dbms_stats.set_index_stats as
 well
> >to influence the choice of what index on the temporary table should
 be
> >used. this routine would allow you to set how many entries are in an
> >index, how selective it is and so on. If you run into issues with
 the
> >wrong index being choosen with CBO -- try using this routine to give
 us
> >more "best guess" data to go on.
> >
> >
> >> Tom, feel free to post something about this on your web page if you
 think it's
> >> important enough. I hope someone at Oracle will read some of this
> >and
> >
> >I probably will....
> >
> >> consider improving the optimizer's treatment of temporary tables.
> >>
> >> Thanks very much!!!
> >> DK.
> >>
> >
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.