Re: 8i - Joining with Temporary Tables

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.