Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!easynet-monga!easynet.net!zen.net.uk!demorgan.zen.co.uk!194.72.9.35.MISMATCH!news-peer1!btnet-feed5!btnet!news.btopenworld.com!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: CBO influences Date: Sat, 10 Sep 2005 20:35:31 +0000 (UTC) Organization: BT Openworld Lines: 87 Message-ID: References: <1126283179.991124.138480@z14g2000cwz.googlegroups.com> <1126284892.947680.217710@g44g2000cwa.googlegroups.com> <1126294364.813025.287860@o13g2000cwo.googlegroups.com> <1126306293.634107@yasure> <1126329859.958172@yasure> <1126374364.66827@yasure> NNTP-Posting-Host: host86-130-246-141.range86-130.btcentralplus.com X-Trace: nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com 1126384531 24323 86.130.246.141 (10 Sep 2005 20:35:31 GMT) X-Complaints-To: news-complaints@lists.btinternet.com NNTP-Posting-Date: Sat, 10 Sep 2005 20:35:31 +0000 (UTC) X-RFC2646: Format=Flowed; Response X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MSMail-Priority: Normal X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:251261 "DA Morgan" wrote in message news:1126374364.66827@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: a) Design the index to access every 4th block, and do a forced index range scan across the whole table. b) Do a serial full tablescan c) 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