CBO influences
in [Oracle]
|
From: Jonathan Lewis on 10 Sep 2005 16:35 "DA Morgan" <damorgan(a)psoug.org> wrote in message news:1126374364.66827(a)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
From: DA Morgan on 10 Sep 2005 19:43 Jonathan Lewis wrote: > "DA Morgan" <damorgan(a)psoug.org> wrote in message > news:1126374364.66827(a)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. SHUTDOWN IMMEDIATE STARTUP statistics in sys.aux_stats$ unchanged col name format a30 col value format a30 SELECT name, value FROM v$parameter WHERE name LIKE '%file_multiblock%'; NAME VALUE ------------------------------ ------------------------------ db_file_multiblock_read_count 16 So, no, MBRC is not the multiblock read count. > 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. Not sure I understand precisely what to look at. All entries in x$kcfio are 0 except as follows: 1* SELECT DISTINCT addr, kcfiosbr, kcfiombr FROM x$kcfio SQL> / ADDR KCFIOSBR KCFIOMBR -------- ---------- ---------- 206034FC 2828 106 2060361C 26 0 2060373C 105 15 2060385C 1 0 2060397C 1 0 20603A9C 5 2 20603BBC 1 0 20603DFC 1 0 I presume these are the columns to which you refer. Thanks. -- Daniel A. Morgan http://www.psoug.org damorgan(a)x.washington.edu (replace x with u to respond)
From: Jonathan Lewis on 11 Sep 2005 02:44 "DA Morgan" <damorgan(a)psoug.org> wrote in message news:1126395746.244667(a)yasure... > Jonathan Lewis wrote: >> "DA Morgan" <damorgan(a)psoug.org> wrote in message >> news:1126374364.66827(a)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. > > SHUTDOWN IMMEDIATE > > STARTUP > > statistics in sys.aux_stats$ unchanged > > col name format a30 > col value format a30 > > SELECT name, value > FROM v$parameter > WHERE name LIKE '%file_multiblock%'; > > NAME VALUE > ------------------------------ ------------------------------ > db_file_multiblock_read_count 16 > > So, no, MBRC is not the multiblock read count. > >> 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. > > Not sure I understand precisely what to look at. > All entries in x$kcfio are 0 except as follows: > > 1* SELECT DISTINCT addr, kcfiosbr, kcfiombr FROM x$kcfio > SQL> / > > ADDR KCFIOSBR KCFIOMBR > -------- ---------- ---------- > 206034FC 2828 106 > 2060361C 26 0 > 2060373C 105 15 > 2060385C 1 0 > 2060397C 1 0 > 20603A9C 5 2 > 20603BBC 1 0 > 20603DFC 1 0 > > I presume these are the columns to which you refer. > > Thanks. > -- > Daniel A. Morgan > http://www.psoug.org > damorgan(a)x.washington.edu > (replace x with u to respond) My snapshot code on filestats (9i onwards) uses these columns for timing KCFIOPYR phyrds, KCFIOPBR phyblkrd, KCFIOPRT readtim, KCFIOSBR singleblkrds, KCFIOSBT singleblkrdtim, KCFIOMBR multiblkrds, KCFIOMBT multiblkrdtim, You will probably find that phyrds does not quite match singleblkrds + multiblkrds My initial checks indicated that: MBRC = (approx) (phyblkrd - singleblkrds )/multiblkrds (oracle records only an integer for MBRC) mreadtim and sreadtim are the obvious divisions. NB Just because a restart says a parameter has value X, that doesn't mean that the session had that value when you ran the test. But I'll trust you ;) I'll take a look at the oddity when I have a little time. At present I'm working on checking the final layout of the book. -- 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
From: DA Morgan on 11 Sep 2005 20:03 Jonathan Lewis wrote: > I'll take a look at the oddity when I have a little time. > At present I'm working on checking the final layout > of the book. Thanks. From where I'm sitting you completing your book is a far higher priority. Any chance of getting a copy in November at UKOUG? -- Daniel A. Morgan http://www.psoug.org damorgan(a)x.washington.edu (replace x with u to respond)
From: Jonathan Lewis on 12 Sep 2005 02:04 The plan is to have a few boxes couriered in from the printer for the event. The schedule is pretty tight, though. (See the URL in the sig) -- 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 "DA Morgan" <damorgan(a)psoug.org> wrote in message news:1126483366.219427(a)yasure... > > From where I'm sitting you completing your book is a far > higher priority. Any chance of getting a copy in November > at UKOUG? > -- > Daniel A. Morgan > http://www.psoug.org > damorgan(a)x.washington.edu > (replace x with u to respond)
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: TIMEOUT ON CONTROL FILE ENQUEUE Next: ORA-12518 Listener could not hand off connection |