Warning – this is a catch question, and I haven’t given you enough information to have any idea of the right answer; though, by telling you that I haven’t given you enough information to have any idea of the right answer, you now have some information that might help you to get closer to the right answer.
I have a simple heap table with no indexes. Immediately after flushing the buffer_cache I’ve run a query that looks ike this:
select max(column_ZZZ) from table_X;
The most significant session stats for this operation are as follows:
Name Value ---- --------- session logical reads 20,651 consistent gets 20,651 consistent gets direct 20,649 physical reads 655 physical reads cache 1 physical reads direct 654 Number of read IOs issued 6 no work - consistent read gets 20,649 table scan rows gotten 329,922 table scan blocks gotten 10,649 table fetch continued row 645 buffer is not pinned count 10,000
The instance is 11.2.0.3, so serial direct path reads have been used for the (necessary) tablescan. The tablespace is using 8KB blocks, 1MB uniform extent sizing, and manual (freelist) segment space management. The data in the table was created by a pl/sql loop of inserts with commits, there have been no updates, deletes, merges or rollbacks. Here’s the code (with one crtical detail hidden) that populated the table:
begin for i in 1..X loop insert into member(member_id, block_age_max_nbr) values (1,1); commit; end loop; end; / execute dbms_stats.gather_table_stats(user,'member',method_opt => 'for all columns size 1')
Roughly how many rows are there in the table ?
Update 13th Jan:
The answer is 10,000; the interesting observations are in this comment and the reply.
Hi, I would say 42 but let me guess:
table scan rows gotten 329,922 +- 645 .
Regards
GregG
Comment by goryszewskig — January 11, 2013 @ 7:18 pm GMT Jan 11,2013 |
GregG,
Bravely volunteering to offer the first answer that was “obviously” going to be wrong. Anyone who’s watched QI (UK TV “quiz” show chaired by Stephen Fry) will understand that the klaxon would be sounding at this point if we had one. ;)
Comment by Jonathan Lewis — January 12, 2013 @ 6:52 pm GMT Jan 12,2013 |
Who can say? You’ve run the select on a different table to the one that you showed the insert into. Also a select ‘MAX’ from a column which only has a single value will only return ‘1’.
Comment by ef — January 11, 2013 @ 7:22 pm GMT Jan 11,2013 |
ef,
I’d like to weasel my way out of that one by pointing out that I only said that the query “looks like” … but I think I really should have used the same table name.
(The column name in the example could still be okay, of course, since my insert statement specifies column names with the values() clause – maybe the column is a virtual one, or takes on a default value.) The fact that every row might have the same value (at present) doesn’t invalidate the query, though.
Comment by Jonathan Lewis — January 12, 2013 @ 6:54 pm GMT Jan 12,2013 |
I would say here is some magic with combination of chained rows, compression and /*+ append_values */. At least I can get similar distribution of the stats.
Comment by Valentin Nikotin — January 11, 2013 @ 7:58 pm GMT Jan 11,2013 |
Valentin,
The magic of chained rows certainly comes into it – though “magic” is perhaps the wrong word.
I’m not using compression, and I didn’t use the /*+ append_values() */ hint in my code for inserting data.
I’m not surprised that you can get some interestingly contradictory statistics with that combination, though – it’s probably guaranteed if you’re using ASSM.
Comment by Jonathan Lewis — January 12, 2013 @ 7:03 pm GMT Jan 12,2013 |
Hi Jonathan,
83.19 mb of table size .. rough estimate
Since, 8k blocksize uniform extent (1MB – 128 blocks)
Total blocks gotten —10649/128 = 83.19 size of segment allocated
Out of which 645 blocks are row migration
Further to that,
physical reads 655
physical reads cache 1
physical reads direct 654
table fetch continued row 645
Number of read IOs issued 6
I would be interested on pctfree, since my ASSM is manual, which enables my rows to migrate/chained and definition of table structure (I probably some custom function is included over some LONG data type)
I would estimate 645 blocks jumps, in order to fetch one particular(all) row from segment, when we calculate for overall it would around 16 or 17 (chained) rows which couldn’t able to fit,during insertion.
Comment by Pavan Kumar — January 12, 2013 @ 2:03 am GMT Jan 12,2013 |
Pavan,
I’m afraid you also score a cacophony of klaxons (for volunteering another “obvious” answer that happens to be completely wrong).
How would your prediction fit with the fact that virtually all the “consistent gets” are “consistent gets direct” ?
Comment by Jonathan Lewis — January 12, 2013 @ 7:05 pm GMT Jan 12,2013 |
Since you have flushed the buffer cache and no index is used, then how consistent gets(20,651 ) are higher then phyical reads(655)?
Comment by Yasir — January 12, 2013 @ 9:32 pm GMT Jan 12,2013 |
Yasir,
That’s a good question – especially since the consistent gets are (almost) all “consistent gets direct” AND “no work – consistent read gets”.
Comment by Jonathan Lewis — January 12, 2013 @ 10:54 pm GMT Jan 12,2013 |
And serial direct read performs segment checkpoint at the very beginning of read ;) if select was select * from table it is possible to get higher number of LIO (cr) when compared to PIO by processing few rows for every fetch (for example set arraysize in sqlplus). Unfortunateky, we havee select max(column) from table ;) . I think table has some huge varchar columns with default values, or has more than 255 columns. Obviously we have chained rows (they cannot be migrated since only inserts occured)
Comment by Pavol Babel — January 13, 2013 @ 9:01 am GMT Jan 13,2013 |
Pavol,
Interesting point here – you know how easy it is to not notice something that’s missing. (In other words, when it’s not there you don’t notice it’s not there.) In this case there was no checkpoint or, to be more accurate, there was no wait for: “enq: KO – fast object checkpoint”. I’d guess that since I’d just flushed the buffer cache, the session probably detected that there were no dirty blocks for the segment in memory, so didn’t issue a checkpoint.
Comment by Jonathan Lewis — January 13, 2013 @ 5:31 pm GMT Jan 13,2013
Jonathan,
and it is very easy not to be exact enough (I think of my post) :) . Exactly, serial direct read “can” perform segment checkpoint (I think session checked X$KCBOQH and didn’t find any blocks for the segment ).
Basically, I only wanted to underline that your query couldn’t generate extra consistent gets due parallel (DML) activity on the same table. Serial direct path read, “no work – consistent read gets” , ….
I will tomorrow check creating table with additional (at least) two varchar2(4000) columns with default values.
Comment by Pavol Babel — January 14, 2013 @ 1:30 am GMT Jan 14,2013
SmartScan?
Comment by Aurelian — January 12, 2013 @ 11:25 pm GMT Jan 12,2013 |
Aurelian,
If it had been an Exadata thing I would probably have said so (and there would have been some “cell” statistics – I try to be reasonable with the harder questions. Having said that, the type of thing that produced these statistics would probably produce some interesting side effects on Exadata.
Comment by Jonathan Lewis — January 13, 2013 @ 5:34 pm GMT Jan 13,2013 |
We can only predict that smart scan performance on tables with internally chained rows could be “slightly” slower as expected ;-)
Comment by Pavol Babel — January 14, 2013 @ 4:00 pm GMT Jan 14,2013 |
Number of rows depended from number of columns in table. In this task, it can be roughly 8000. We need create a table with more than 256 columns to enable row chaining:
Comment by Vyacheslav Rasskazov — January 13, 2013 @ 3:49 am GMT Jan 13,2013 |
Vyacheslav,
That’s basically it. I assume from the f1 .. f512 that you’ve got a total of 514 columns in the table – which means each row is in three row pieces. The variation in the statistics for chained rows (and block gets) across different versions of Oracle is extraordinary – but in this version some of the numbers are truly bizarre:
You’ve got 8,000 rows (I had 10,000, with two row pieces – 264 columns – each) in 622 blocks (of which 7 were space management – I think you’re using ASSM), but you’ve done 24,652 logical I/Os – which is (roughly) one for each block, plus 3 for each row (which seems too many). The number of tablescan blocks reported is (roughly) the real number plus one for each row in the table. More significantly the number of ROWS gotten is astonishing – how did Oracle scan 336,000 rows when you had 8,000 rows totalling 24,000 row pieces in the table.
I can’t get the figures to work well – but it looks as if we’re at the right sort of scale to think that for each row the session has “scanned” the block holding that row once for every row piece – including the initial row piece – that makes up that row (and you can see a possible three scans in the 24,000 excess consistent gets). The question is – does Oracle really do the work, or is it simply reporting incorrect figures.
It doesn’t help, of course, that the continued row figure is 615 (the number of blocks holding data) rather than the 8,000 – or even 16,000 – that would represent the extra row piece accesses).
Comment by Jonathan Lewis — January 13, 2013 @ 5:55 pm GMT Jan 13,2013 |
Jonathan,
two additional questions:
1. your example showed a value of 645 for “table fetch continued row” (and not 10000) – so I guess the statistic does not report intra-block-chaining but only the cases when the second row piece is indeed in another block?
2. if I access a column from the first row piece then the value for “table scan rows gotten” is always equal to the total number of row pieces for the given row (though there is no need to access the second/third/fourth piece)?
(that was my interpretation of some tests I made some time ago)
Martin
Comment by Martin Preiss — January 13, 2013 @ 7:24 pm GMT Jan 13,2013 |
Martin,
1. It’s a nice thought – but (in my case, at least) doesn’t explain the numbers. I actually took block dumps of 16 consecutive blocks to confirm this, and also did a “rows per block” check to corroborate the regularity of the pattern I saw. Ignoring the last two blocks in the table the “rows per block” code showed half the blocks held 15 rows and half held 16 rows – which really means 15 starting row pieces and 16 starting row pieces respectively – and the block dump showed the every block held 31 row pieces. Looking at the detail of the data blocks, the blocks came in pairs: the first block of a pair held the first and second row pieces for 15 rows, and the second row piece of a sixteenth row; the second block of a pair also held the first and second row pieces for 15 rows, but the first row piece of a sixteenth row (viz: the first half of the row corresponding to the second half from the preceding block). This means that if Oracle were counting chained rows according to your hypothesis, it should have reported 322 (or maybe 323) continued rows.
2. I agree – if Oracle doesn’t need to access columns in the second or later row pieces you don’t see any fetches for continued rows, and the numbers give you no clue that the table has rows longer than 255 columns. There is a little catch to that, though: Oracle creates the row pieces “back to front” , if your row is 264 columns (as mine were), the first row piece holds 9 columns and the second row piece holds 255.
Further to the block dump comments – here’s another anomaly that appears when the rows are internally chained. I’ve got two ITLs here, the first I created by cutting out 10 columns from my table and running the test above, the second was with the test above where I had 264 columns and multiple row pieces.
ITL when the row are 254 columns
ITL when the rows are 264 columns
The number of ITL entries for the chained row example varies between 17 and 18 depending on whether you’re looking at a block with the first row piece or the last row piece of a row that has crossed the boundary.
Comment by Jonathan Lewis — January 13, 2013 @ 9:43 pm GMT Jan 13,2013
I’ve running tests in 11.2.0.2, ASSM. Interesting that without direct path statistics for ‘table scan rows gotten’, ‘table scan blocks gotten’ are completely different.
Comment by Vyacheslav Rasskazov — January 13, 2013 @ 10:34 pm GMT Jan 13,2013 |
Jonathan,
I think statistic “buffer is not pinned count” is very interesting, too. If we have 20 000 consistent gets caused by internal row chaining, why do we have only 10 000 “buffer is not pinned count”? Does oracle know for the initial row piece that its buffer not pinned (and does not call kcbispnd function) or is consistent get statistic wrong in this particular case? We should check 10200 event, too.
Comment by Pavol Babel — January 14, 2013 @ 2:13 am GMT Jan 14,2013 |
Pavol,
I managed to resist using event 10200, but don’t let me stop you ;)
I agree, there are all sorts of interesting patterns in the numbers – I had 10,000 rows of 2 row pieces and had 20,000 “excess” gets of which 10,000 were “buffer is not pinned count” (i.e. one out of two). Vyacheslav had 8,000 rows of 3 row pieces and had 24,000 “excess” gets of which 16,000 were “buffer is not pinned count” (i.e. 2 out of 3). It must mean something – but whether it’s bad reporting or unnecessary work, or necessary work, we can’t tell. (I’m fairly sure, due to the absence of stats relating to SCNs and undo, that the excess numbers aren’t due to visiting undo blocks, though.)
Comment by Jonathan Lewis — January 14, 2013 @ 6:02 pm GMT Jan 14,2013 |
Jonathan,
OK, I was finally able to persuade myself to use event 10200 :)
I have created table t1 : (member_id, c001, c002, …. , c262, block_age_max_nbr), 10 000 rows, 23 rows per block (46 row pieces).Trace 10200 shows 47 consistent gets of every bock. 1 (every pysical read is reported as consistent get) + 23 * 2 (for every row piece) = 47.
So it seems oracle is really performing extra consistent get for every row piece, even for the “starter” row piece
Comment by Pavol Babel — January 15, 2013 @ 4:59 pm GMT Jan 15,2013 |
Pavol,
Thanks for doing the experiment and reporting the results.
Comment by Jonathan Lewis — January 15, 2013 @ 6:01 pm GMT Jan 15,2013
Jonathan,
thank you for checking my hypothesis. Looking at the result: “322 (or maybe 323)” I am thinking about the numbers: 322 + 323 = 645. Perhaps Oracle counts the chained rows twice? I think I will do some additional testing.
Comment by Martin Preiss — January 14, 2013 @ 6:12 am GMT Jan 14,2013 |
Martin,
Double-counting was a thought that crossed my mind too, then I realised that 645 is an odd number …
On the other hand, I had 646 blocks with row data in them, and even though the last block in the table only had 3 rows in it, those rows were still chained (intra-block) – so my idea that Oracle was counting each block that it visited with row chaining just once is not correct.
Comment by Jonathan Lewis — January 14, 2013 @ 6:07 pm GMT Jan 14,2013 |