Re: CBO influences

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1126374364.66827_at_yasure...
>
> SQL> SELECT pname, pval1
> 2 FROM aux_stats$
> 3 WHERE sname = 'SYSSTATS_MAIN';
>
> PNAME PVAL1
> ------------------------------ ----------
> CPUSPEED 413
> CPUSPEEDNW 450.609
> IOSEEKTIM 10
> IOTFRSPEED 4096
> MAXTHR 43008
> MBRC 8
> MREADTIM 5.121
> SLAVETHR
> SREADTIM 6.636
>
> 9 rows selected.
>
>

Odd.

It's interesting to note that your mreadtim is less than your sreadtim, and your MBRC looks
suspiciously as if it might be your db_file_multiblock_read_count, which would tend to suggest (in a busy production system at least) that nearly everything you were doing was at least a full scan, and possibly a parallel scan.

If you access x$kcfio - which is where the read stats come from, and check the columns (names I can't remember, but they'll be obvious) for sblk reads and mblk reads and mblk read counts, and sblk read times and mblk read times just before the start, and just after the stop, this should tell you the figures that the code is using to calculate the mbrc, sreadtim and mreadtim - check if what's captured in x$kcfio makes sense.

To make the stats happen (if they can) I would create a single large table (larger than the cache) with one index, and run three separate attempts:

1. Design the index to access every 4th block, and do a forced index range scan across the whole table.
2. Do a serial full tablescan
3. Do a parallel sort group by, but on a column which doesn't aggregate very well so that you have to send a lot of traffic from slave to slave. And try to query v$px_sesstat whilst it's going on to capture stats about the volume of data moving across the layers of slaves.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005