This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jul 28, 2013 2:18 AM by StefanKoehler RSS

Reading Null to fulfill db_file_multiblock_read_count

jjk Explorer
Currently Being Moderated

Hi everyone,

 

I was just dicussing with my team mate and came to know an interesting fact which I'd like to get clarified.

 

The fact that I was told and which I couldn't digest was:

Assumption:

db_file_multiblock_read_count=16

table emp is spread across 2 database blocks:

 

Now, upon firing:

 

select * from emp;

 

the database would not just fetch 2 db blocks pertaining to emp table (assuming the table is being read/scanned for the first time ever) but also 14 other null blocks so as to make the read call for 16 (as per dbfmrc) blocks.

 

I'm aware of the various facts that prevent Oracle from reading the full DBFMRC # of blocks, but in case where a tablescan requires blocks < DBFMRC, will it really read additional "null blocks" to make the total upto DBFMRC ? If yes, why so ? and where do the null blocks come from ?

 

 

I ran a small test to verify but didn't see any null reads happening

 

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
5 rows selected.
SQL> show parameter multiblock
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128
SQL>
SQL> select owner, table_name , blocks from dba_tables where owner='FH' and table_name='MI_XCT_TSR_XEF';
OWNER                          TABLE_NAME                         BLOCKS
------------------------------ ------------------------------ ----------
FLEXPROD_HOST                  MI_XCT_TSR_XEF                         88
SQL> set autotrace on exp stat
SQL> select /* parse_fresh */ count(*) from fh.MI_XCT_TSR_XEF;
  COUNT(*)
----------
     30150
Execution Plan
----------------------------------------------------------
Plan hash value: 1963492958
----------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Rows  | Cost (%CPU)| Time  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                      |     1 |    24   (0)| 00:08:34 |
|   1 |  SORT AGGREGATE    |                      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MI_XCT_TSR_XEF       | 30150 |    24   (0)| 00:08:34 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         35  recursive calls
          0  db block gets
        331  consistent gets
         89  physical reads
       6564  redo size
        528  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

I've been thru many blogs, asktom pages, but wasn't able to find the answers.

  • 1. Re: Reading Null to fulfill db_file_multiblock_read_count
    Lalit Kumar B Explorer
    Currently Being Moderated

    jjk wrote:

     

    Hi everyone,

     

    Assumption:

    db_file_multiblock_read_count=16

    table emp is spread across 2 database blocks:

     

    Now, upon firing:

     

    select * from emp;

     

    the database would not just fetch 2 db blocks pertaining to emp table (assuming the table is being read/scanned for the first time ever) but also 14 other null blocks so as to make the read call for 16 (as per dbfmrc) blocks.

     

     

    That is completely incorrect assumption.If you specify db_file_multiblock_read_count=16,

    You are telling Oracle to read 16 blocks at a time, that is pretty good. But in reality, you don't really ever read 16 when you do a multiblock IO - you do less than 16. So, while the query was costed with a read count of 16, it is really only doing less than that. Also some out of the 16 blocks you would have read are already in the buffer cache, Oracle won't read them from disk again - so the 16 blocks read actually does not happen.

     

    And in a RAC environment things changes even more. Each node will be doing a multiblock read. None of it would be 16 in your case.

  • 2. Re: Reading Null to fulfill db_file_multiblock_read_count
    jjk Explorer
    Currently Being Moderated

    Thanks Lalit,

     

    As I mentioned, I'm aware of the various cases wherein Oracle wont read all of DBFRMC blocks (e.g. end of extent, blocks already in cache etc) but the question is, assuming none of those cases occure and Oracle can read all of DBFRMC blocks in one call, will it have any more blocks than those from table (like he mentioned, null blocks) if the table has way lesser blocks than DBFRMC, says just 2 blocks againt DBFRMC of 128.

     

    I too want to prove this wrong to my team mate, but just gathering enough proofs for that

  • 3. Re: Reading Null to fulfill db_file_multiblock_read_count
    rp0428 Guru
    Currently Being Moderated

    I was just dicussing with my team mate and came to know an interesting fact which I'd like to get clarified.

     

    The fact that I was told and which I couldn't digest was:

     

    There is no such 'fact'. Ask your 'team mate' to backup their statement with proof and provide that proof here.

     

    They made the statement; they need to back it up. They can't just say 'prove me wrong'.

  • 4. Re: Reading Null to fulfill db_file_multiblock_read_count
    Lalit Kumar B Explorer
    Currently Being Moderated

    MAY or may NOT.

     

    Depends. You are going other way round.

     

    I know it this way - If your dbfmrc is 16 and your default block size is 8k - Oracle will attempt to read 128k at a time. That may or may not be 16 blocks. If you have a 4k block size tablespace, Oracle will attempt to read 128k - but that will mean 32 blocks - not 16.

  • 5. Re: Reading Null to fulfill db_file_multiblock_read_count
    StefanKoehler Explorer
    Currently Being Moderated

    Hi jjk,

    well there is a lot of wrong information or assumptions and a lot of information is just missing.

     

    1. >= Oracle 10g R2 the hidden parameter "_db_file_exec_read_count" is used for I/O requests ( http://kerryosborne.oracle-guy.com/2010/01/autotuned-db_file_multiblock_read_count/ )
    2. A FTS does not only fetch data from the "used data blocks" - it scans through the whole segment (key word HHWM or LHWM in ASSM tablespaces or the usual HWM)

     

    > I ran a small test to verify but didn't see any null reads happening

    I can see nothing in your small test related to that issue at all. You just have posted a SQL statement and an autotrace output with 35 recursive calls and a summary. No SQL trace which proves how many blocks are requested / fetched by the SQL on your particular table MI_XCT_TSR_XEF.

     

    Regards

    Stefan

  • 6. Re: Reading Null to fulfill db_file_multiblock_read_count
    jjk Explorer
    Currently Being Moderated

    Hi Stefan,

     

    Actually, the test was to see how much physical/logical IO is performed so as to get an idea on total blocks read (assuming that, if at all there are any null blocks reads to make the read call upto DBFMRC or that _under_score_parameter, the same might be reported), since we are trying to see if there any "null blocks" read apart from the required blocks. And in my test, I can see that for a table of 88 blocks, the physical IO of 89 blocks was done which means that no extra "null" blocks were read.

     

    I thought since autotrace would tell me the number of physical/logical IO, that would be sufficient to get an idea of actual IO.

    Btw, can you elaborate more on what's the information/assumption that's wrong and missing.

  • 7. Re: Reading Null to fulfill db_file_multiblock_read_count
    jgarry Guru
    Currently Being Moderated

    You might also consider how many blocks are created by the initial extent.  DBA_SEGMENTS

    Could this be affected by LMT autoallocate or uniform?

  • 8. Re: Reading Null to fulfill db_file_multiblock_read_count
    StefanKoehler Explorer
    Currently Being Moderated

    Hi jjk,

     

    > Actually, the test was to see how much physical/logical IO is performed so as to get an idea on total blocks read, since we are trying to see if there any "null blocks" read apart from the required blocks. And in my test, I can see that for a table of 88 blocks, the physical IO of 89 blocks was done which means that no extra "null" blocks were read.

    Yes, i get your idea behind this test, but unfortunately the test case is meaningless. You have performed 89 physical reads and 331 consistent gets by running your SQL and 35 recursive SQLs. How do you know which SQL (including the recursive ones) has performed how much physical I/O or logical I/O? It is just a summary - nothing more.

     

    You think, that your tables consists of 88 blocks (btw. this information in DBA_TABLES is just based on DBMS_STATS), but how many of these blocks are really read by a FTS (once again think about "HHWM or LHWM in ASSM tablespaces or the usual HWM"). I can create a segment that consists of 10.000 blocks, but reads only one or two blocks by a FTS.

     

    > I thought since autotrace would tell me the number of physical/logical IO, that would be sufficient to get an idea of actual IO.

    Nope - not in your scenario (and what you want to achieve with this test).

     

    > Btw, can you elaborate more on what's the information/assumption that's wrong and missing.

    • SQL Trace is missing for that particular main query
    • The corresponding hidden parameter value is missing
    • The information about the tablespace and its (segment) space management is missing

     

    Regards

    Stefan

  • 9. Re: Reading Null to fulfill db_file_multiblock_read_count
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    jjk wrote:

     

    Hi everyone,

     

    I was just dicussing with my team mate and came to know an interesting fact which I'd like to get clarified.

     

    The fact that I was told and which I couldn't digest was:

    Assumption:

    db_file_multiblock_read_count=16

    table emp is spread across 2 database blocks:

     

    Now, upon firing:

     

    1. select * from emp; 

     

    the database would not just fetch 2 db blocks pertaining to emp table (assuming the table is being read/scanned for the first time ever) but also 14 other null blocks so as to make the read call for 16 (as per dbfmrc) blocks.

     

    This sounds as if it's a garbled understanding of how some features of ASSM work when compared to freelist management.

     

    If you create the temp table in an ASSM tablespace, and then insert one row into it, then Oracle will format 16 blocks consecutive blocks selected in a "pseudo-random" way based on your process id. Although you may know that the data is all contained within a single block, Oracle will have to read all 16 blocks if it does a tablescan of that table because those blocks have been formatted and are available for use. Under freelist management Oracle would have inserted the row into the first data block of the table and, though I'd have to check it to make sure, it might only have formatted that one block (as the table grows from "tiny" it formats 5 blocks at a time, but I have a vague memory that the first few blocks are treated as a special case )

     

    If you collect stats on the table with ASSM, though, you'll find that Oracle does something funny setting the BLOCKS and EMPTY_BLOCKS information - though the anomalies are only really noticeable for small tables.

     

    Regards

    Jonathan Lewis

    Now on twitter: @jloracle

  • 10. Re: Reading Null to fulfill db_file_multiblock_read_count
    Lalit Kumar B Explorer
    Currently Being Moderated

    JonathanLewis wrote:

     

    If you create the temp table in an ASSM tablespace, and then insert one row into it, then Oracle will format 16 blocks consecutive blocks selected in a "pseudo-random" way based on your process id. Although you may know that the data is all contained within a single block, Oracle will have to read all 16 blocks if it does a tablescan of that table because those blocks have been formatted and are available for use.

     

    Regards

    Jonathan Lewis

    Now on twitter: @jloracle

     

    Hi Jonathan,

    In MSSM, all the blocks under HWM are read and processed since all the blocks ae formatted and kept safe. However, in ASSM, only the blocks under Low-HWM are read and processed, because blocks between Low-HWM and HWM may or may not be formatted(usually unformatted). So unless all the blocks are formatted, in a tablescan it should not read and process all the blocks.

     

    In your above quote, you mentioned, In ASSM, Oracle has to read all 16 blocks since they are formatted. However, not all the 16 blocks would be under Low-HWM, a few of them between Low-HWM and HWM would be in unknown/unformatted state. Please correct me if I have not understood correctly.

  • 11. Re: Reading Null to fulfill db_file_multiblock_read_count
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    LaitKumarB,

     

    Your description of ASSM is incomplete, and my description of what can happen is correct (although I meant EMP table, not TEMP table).

     

    As a general principle ALL the blocks below the Low HWM are formatted (although it's possible that some of them may still be empty.

     

    Some of the blocks between the Low HWM and the High HWM will be formatted - and they will be formatted in batches of 16 at a time, which is why I made the point that if you insert ONE row Oracle will format 16 blocks somewhere in the table, and a tablescan will have to scan all 16 of them. The batches of 16 will be selected on a 16-block boundary, relative to the start of the extent.

     

    As far as tablescans are concerned, Oracle will aim to do the maximum db_file_multiblock_read_count up to the Low HWM, and will then keep checking the space management blocks to check which batches of 16 blocks are formatted between the Low and High HWM. In fact, the last time I checked (quite some time ago) it scanned them 16 blocks at a time, even when batches of 16 blocks were adjacent.

     

    NOTE: there are a couple of boundary cases, of course. Extents with less than 16 block supplies an obvious example; another example is when the batch of 16 blocks includes the (already formatted) space management blocks so the number of data blocks formatted (and scanned) is 16 - "number of space management blocks".

     

    Here's a partial block dump (probably completely messed up) of the bitmap block from a demonstration on 11.1.0.7 of inserting one row into a table in a tablespace with 1MB uniform extents (which results in 2 L1 bitmaps at the start of the first extent followed by an L2 bitmap and then the segment header. This is the key content of the first L1 bitmap:

     

     

      --------------------------------------------------------

      DBA Ranges :

      --------------------------------------------------------

       0x01400080  Length: 64     Offset: 0    

     

       0:Metadata   1:Metadata   2:Metadata   3:Metadata

       4:unformatted   5:unformatted   6:unformatted   7:unformatted

       8:unformatted   9:unformatted   10:unformatted   11:unformatted

       12:unformatted   13:unformatted   14:unformatted   15:unformatted

       16:75-100% free   17:75-100% free   18:75-100% free   19:75-100% free

       20:75-100% free   21:75-100% free   22:75-100% free   23:75-100% free

       24:75-100% free   25:75-100% free   26:75-100% free   27:75-100% free

       28:75-100% free   29:75-100% free   30:75-100% free   31:75-100% free

       32:unformatted   33:unformatted   34:unformatted   35:unformatted

       36:unformatted   37:unformatted   38:unformatted   39:unformatted

       40:unformatted   41:unformatted   42:unformatted   43:unformatted

       44:unformatted   45:unformatted   46:unformatted   47:unformatted

       48:unformatted   49:unformatted   50:unformatted   51:unformatted

       52:unformatted   53:unformatted   54:unformatted   55:unformatted

       56:unformatted   57:unformatted   58:unformatted   59:unformatted

       60:unformatted   61:unformatted   62:unformatted   63:unformatted

      --------------------------------------------------------

     

    Note how blocks 16 - 31 have been formatted as I inserted 1 row.

     

    By checking the various piece of information about the high water marks, Oracle will HAVE to scan 16 - 31 to read the one row, but it won't (must not) try scan blocks 4 - 15.

     

    I've not included the dump showing the LHWM and HHWM, because it's actually rather messier than the simple description that's often given of how it works and how it's stored.

     

     

    Regards

    Jonathan Lewis

    Now on Twitter: @jloracle

  • 12. Re: Reading Null to fulfill db_file_multiblock_read_count
    Lalit Kumar B Explorer
    Currently Being Moderated

    JonathanLewis wrote:

     

     

     

    Note how blocks 16 - 31 have been formatted as I inserted 1 row.

     

     

    Exactly, because inserting a row would advance the HWM to allocate a group of blocks to hold the rows. And then the data could be inserted randomly in any of the blocks between the LHWM and HWM. In case of an FTS, all blocks below LHWM + the formatted blocks between LHWM and HWM will be read. Oracle reads the bitmap block to know the location of the LHWM for this task.

     

    Thanks for the clarification. That was short and good. Appreciate it.

     

    Yes, I mentioned just a part of ASSM details, else my question about LHWM and HWM would have been lost in all those details. However, you finally added almost everything to it. Crystal clear.

    Hope OP has understood for what we have made efforts to explain.

  • 13. Re: Reading Null to fulfill db_file_multiblock_read_count
    StefanKoehler Explorer
    Currently Being Moderated

    Hi,

    as i previously mentioned - the OP has not provided enough information about his environment and his particular test case. The assumption, that he is / was using ASSM is pretty close and so the behavior is as expected, but currently we are not sure (Oracle has so many little dirty secrets ).

     

    However in addition Tanel Poder and Stephen Haisley have published two papers / presentations about the ASSM / Free-List Management and some internals in the past.

     

    Regards

    Stefan

  • 14. Re: Reading Null to fulfill db_file_multiblock_read_count
    jjk Explorer
    Currently Being Moderated

    Thanks everyone for making efforts to explain the situation.

     

    like they say, devil's in the details

     

    I'll just clarify my case, the discussion that we (me & my team mate had):

    our assumptions were,

    - A table,newly created, spans across X blocks (X blocks, in case the data has been sprayed across multiple blocks even for lesser rows as Jonathan mentioned)

    - HWM points at Xth block (no empty blocks in between)

    - LMT & ASSM

    - DBFMRC = 3X (to make it larger than the # of blocks consumed by table)

     

    What I couldn't understand/digest and prove/convince him wrong was:

    The FTS on the above table would read

    1. X blocks from the table +

    2. 3X-X # of additional blocks (to make the read call of 3X blocks, as per DBFMRC)

     

     

    I clearly understand the case that Johnathan has mentioned above, and that actually is an expected behaviour: to scan all the used blocks durig FTS.

    The problem is, his thinking that DBFMRC is the mandatory & driving force for # of blocks to be read in single read call during FTS, even if it requires lesser than that.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points