Re: 8i - Joining with Temporary Tables

if the temp tables are so small why not have them always cached?

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.
>>
>
Received on Thu Jul 06 2000 - 00:00:00 CDT