tag:blogger.com,1999:blog-5124641802818980374.post5036748389197734552..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: Logical I/O - Evolution: Part 1 - BaselineUnknownnoreply@blogger.comBlogger9125tag:blogger.com,1999:blog-5124641802818980374.post-81256272234996483872011-08-01T11:09:05.604+02:002011-08-01T11:09:05.604+02:00Alexandr,
I'm glad that finally someone took ...Alexandr,<br /><br />I'm glad that finally someone took up that point - I actually expected this question much earlier.<br /><br />But I have the same problem as you outline: I've run several tests and it looked like I couldn't figure out a straightforward explanation when and why the "buffer is not pinned count" statistics get increased or not.<br /><br />In principle you could argue that it should increase each time a block is visited and it is not already pinned - but then this seems to correspond to the definition of a "logical I/O", so clearly I would expect this statistics to express something different, but it is unclear to me what exactly it is supposed to represent.<br /><br />So the term "unreliable" according to some tests seems to fit - as long as no-one is able to figure out the underlying rules this statistics is supposed to follow.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-36971627497925228052011-07-31T21:48:45.760+02:002011-07-31T21:48:45.760+02:00Hi,
Thanks for quite an interesting post.
There...Hi,<br />Thanks for quite an interesting post.<br />There's one thing I couldn't figure out from your post is the way Oracle calculates buffer not pinned count stat.<br />I've set up a similar environment (my leading table has got only one block with 10 rows; the other table has got 100k rows and 100k blocks) on my machine and tried to touch the base with that statistic but alas I can't state that I could. Although there are some findings that you and your readers might find useful.<br />I've run two types of queries, first is akin to yours t1->Idx->t2, and the second one is just t1->idx.<br />First off, seemed like the number of CR in trace is two fewer than in the corresponding stat. Second off, Oracle built CR copy of root index block and (the only) t1 table block two times each (before pinning it; from trace). Finally seems like oracle reported (not-)pinned count only for table(s) blocks, not for the index root block, moreover for t2 table (in first type of query) oracle reported not pinned count = 2 * number of actually accessed blocks (as I can see you've got pretty much the same picture as you've got 200k not pinned bufferes). <br />Looks like the not pinned count stat is not that reliable after all.Alexandr Antonovhttps://www.blogger.com/profile/16541743594136378799noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-49184845861598186282011-07-16T12:22:22.558+02:002011-07-16T12:22:22.558+02:00Randolf,
thank you for the additional details. For...Randolf,<br />thank you for the additional details. For T1 I see a SPARE1 value of 32769 in SYS.TAB$. According to Julian Dyke's Bitmap Index Internals presentation (http://www.juliandyke.com/Presentations/BitmapIndexInternals.ppt, p. 36) the 0x8000 bit is set to indicate that "minimize records_per_block" is active. Building a table T2 with connect by level <= 2 I get the same 32769; with connect by level <= 3 I get SPARE1 = 32770 (and 3 rows per block) and with your example (connect by level <= 10) SPARE1 = 32777 (and of course 10 rows per block). <br /><br />My calculator claims: 32777 - 32768 = 9 - but the result are 10 rows per block. With 32769 (- 32768 = 1) there are 2 rows per block. Even if I use bigger rows (rpad('x', 5000) - so only one row fits into the 8K block) SPARE1 remains 32769. I assume that it's not possible to get SPARE1 = 32768 and to force a one row per block filling with minimize records_per_block (if more than one row fits into a block).<br /><br />Not really important but entertaining.<br /><br />Regards<br />MartinMartin Preisshttps://www.blogger.com/profile/06388592214305009761noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-16176326991205628962011-07-15T21:44:01.201+02:002011-07-15T21:44:01.201+02:00Hi Martin,
I think there are two important things...Hi Martin,<br /><br />I think there are two important things to consider:<br /><br />1. MINIMIZE RECORDS_PER_BLOCKS scans the <b>row directory</b> of all blocks for the maximum number of row entries per block - it is not counting the current number of rows in the block. So you can end up with unexpected results due to this if you have happen to have blocks with more row entries in the row directory than actual rows. I don't think that it applies to your example though - it's just an important point to consider when dealing with this option.<br /><br />2. I thought I had read about this anomaly already in Jonathan's "Practical Oracle 8i", but I can't find the reference at present. I might confuse this with some other similar strange effect. You can check the SYS.TAB$.SPARE1 column that holds the current Hakan factor for the table - bits 32768 and higher to be ignored I think to see if this gives you a clue whether Oracle doesn't allow this to go less than two (hey, that rhymes!)<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-53853173731749140922011-07-15T19:56:42.621+02:002011-07-15T19:56:42.621+02:00Randolf,
thank you for the interesting post - to w...Randolf,<br />thank you for the interesting post - to which I have a - more or less closely related - question: when I create table t1 with only a single row (without the connect by part), the result of: alter table t1 minimize records_per_block; and the following: insert /*+ append */ into t1 ... is a segment with 50.000 blocks. The documentation (http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_3001.htm#i2125860) states: "Specify MINIMIZE to instruct Oracle Database to calculate the largest number of records in any block in the table and to limit future inserts so that no block can contain more than that number of records." So I would have expected a 100.000 block segment (with one block per row). Do you know a reason for the difference?<br /><br />Regards<br />MartinMartin Preisshttps://www.blogger.com/profile/06388592214305009761noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-10702193929292410352011-07-11T14:42:56.499+02:002011-07-11T14:42:56.499+02:00Nice & informative.
Based on your findings, I ...Nice & informative.<br />Based on your findings, I think a large DB system with a limited cache size would perform significantly better with a reduction in I/Os (logical or not). I have systems like these, so thank you for giving me an opportunity to rejuvenate them a little.Darryl Griffithshttps://www.blogger.com/profile/14606963192602668739noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-56723524398824205202011-07-08T23:02:00.125+02:002011-07-08T23:02:00.125+02:00Great analysis. Look forward to investigating your...Great analysis. Look forward to investigating your examples more in depth.<br />Thanks<br />Kyle HaileyKyle Haileyhttps://www.blogger.com/profile/13586511268045480856noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-1305925287013792372011-07-08T22:52:31.691+02:002011-07-08T22:52:31.691+02:00Excellent! Since I'm working for bank most of ...Excellent! Since I'm working for bank most of time and banks are not keen on upgrading, most of our databases are 9i and 10g (there is no business critical DB on 11g now).<br />So I had never time to investigate 11g new features like consistent gets (fast path) and the difference between nlj prefetching and nlj batching (we turned of nlj batching on some 11g DB becuase it could return wrong results under some circumstances in 11.2.0.1).<br /><br /><br />I'm looking forward to your next posts, It seems you are going to explain some of these topcis.<br /><br />Pavol BabelAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-24801659397388526912011-07-08T08:58:40.241+02:002011-07-08T08:58:40.241+02:00Very interesting post, Randolf.
Thanks!Very interesting post, Randolf.<br />Thanks!Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.com