Oracle Scratchpad

January 11, 2013

Quiz Night

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 6:41 pm BST Jan 11,2013

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.

27 Comments »

  1. 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 BST Jan 11,2013 | Reply

    • 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 BST Jan 12,2013 | Reply

  2. 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 BST Jan 11,2013 | Reply

    • 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 BST Jan 12,2013 | Reply

  3. 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 BST Jan 11,2013 | Reply

    • 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 BST Jan 12,2013 | Reply

  4. 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 BST Jan 12,2013 | Reply

    • 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 BST Jan 12,2013 | Reply

  5. 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 BST Jan 12,2013 | Reply

    • 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 BST Jan 12,2013 | Reply

      • 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 BST Jan 13,2013 | Reply

        • 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 BST 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 BST Jan 14,2013

  6. SmartScan?

    Comment by Aurelian — January 12, 2013 @ 11:25 pm BST Jan 12,2013 | Reply

    • 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 BST Jan 13,2013 | Reply

      • 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 BST Jan 14,2013 | Reply

  7. 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:

    create table  mbr2 (member_id number, f1 number,f2 number,.....f512 number,block_age_max_nbr number) ;
    
    begin
        for i in 1..8000 loop
            insert  into mbr2(member_id, block_age_max_nbr) values (1,1);
            commit;
        end loop;
    end;
    
    exec dbms_stats.gather_table_stats(user,'mbr2',method_opt => 'for all columns size 1');
    alter system flush buffer_cache;
    
    alter session set "_serial_direct_read" = always;
    select max(f512) from mbr2;
    
    Statistics:
    ---------------------------------------------------------------- ----------
    Number of read IOs issued                                                13
    buffer is not pinned count                                            16004
    consistent gets                                                       24652
    consistent gets direct                                                24622
    no work - consistent read gets                                        24640
    physical reads                                                          622
    physical reads cache                                                      0
    physical reads direct                                                   622
    session logical reads                                                 24652
    table fetch continued row                                               615
    table scan blocks gotten                                               8625
    table scan rows gotten                                               335996
    

    Comment by Vyacheslav Rasskazov — January 13, 2013 @ 3:49 am BST Jan 13,2013 | Reply

    • 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 BST Jan 13,2013 | Reply

      • 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 BST Jan 13,2013 | Reply

        • 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

          Block header dump:  0x01400981
           Object id on Block? Y
           seg/obj: 0x133f6  csc: 0xb86.4033368f  itc: 2  flg: -  typ: 1 - DATA
               fsl: 0  fnx: 0x0 ver: 0x01
           
           Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
          0x01   0x0006.01a.00001178  0x00c016bb.0290.09  C---    0  scn 0x0b86.4033368f
          0x02   0x000a.017.00000d9f  0x00c005ac.021c.2a  C---    0  scn 0x0b86.4033368e
          bdba: 0x01400981
          
          

          ITL when the rows are 264 columns

          
          Block header dump:  0x01400681
           Object id on Block? Y
           seg/obj: 0x133f5  csc: 0xb86.4032f243  itc: 18  flg: -  typ: 1 - DATA
               fsl: 0  fnx: 0x0 ver: 0x01
           
           Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
          0x01   0x0007.00d.00000e15  0x00c01c82.02dc.25  C---    0  scn 0x0b86.4032f22d
          0x02   0x0008.009.000011f2  0x00c00ab4.027e.0a  C---    0  scn 0x0b86.4032f22f
          0x03   0x0002.002.0000127d  0x00c0111a.028a.02  C---    0  scn 0x0b86.4032f231
          0x04   0x0006.01a.00001157  0x00c0168b.0290.21  C---    0  scn 0x0b86.4032f233
          0x05   0x0003.016.00001228  0x00c00b03.01eb.41  C---    0  scn 0x0b86.4032f234
          0x06   0x0005.014.00001170  0x00c00409.02d5.16  C---    0  scn 0x0b86.4032f236
          0x07   0x000a.01d.00000dac  0x00c0058f.021c.20  C---    0  scn 0x0b86.4032f237
          0x08   0x0008.01c.000011e4  0x00c00ab4.027e.0c  C---    0  scn 0x0b86.4032f239
          0x09   0x0001.014.00000df0  0x00c00512.01ed.3a  C---    0  scn 0x0b86.4032f23a
          0x0a   0x0002.00b.00001282  0x00c0111a.028a.04  C---    0  scn 0x0b86.4032f23c
          0x0b   0x0009.01e.000011bc  0x00c0033e.02a1.04  C---    0  scn 0x0b86.4032f23d
          0x0c   0x0004.001.00000d21  0x00c01636.0240.11  C---    0  scn 0x0b86.4032f23e
          0x0d   0x0005.015.0000113c  0x00c00409.02d5.18  C---    0  scn 0x0b86.4032f240
          0x0e   0x0008.012.000011ef  0x00c00ab4.027e.0e  C---    0  scn 0x0b86.4032f241
          0x0f   0x0002.010.0000127f  0x00c0111a.028a.06  C---    0  scn 0x0b86.4032f243
          0x10   0x0006.018.0000114d  0x00c0168b.0290.22  --U-    1  fsc 0x0000.4032f245
          0x11   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
          0x12   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
          bdba: 0x01400681
          
          

          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 BST 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 BST Jan 13,2013 | Reply

  8. 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 BST Jan 14,2013 | Reply

    • 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 BST Jan 14,2013 | Reply

      • 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 BST Jan 15,2013 | Reply

  9. 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 BST Jan 14,2013 | Reply

    • 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 BST Jan 14,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,267 other followers