1 2 Previous Next 18 Replies Latest reply: Jul 28, 2013 4:18 AM by Stefan Koehler Go to original post RSS
      • 15. Re: Reading Null to fulfill db_file_multiblock_read_count
        jjk

        StefanKoehler wrote:

         

        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.

         

        The query was hard-parsed and hence the 35 recursive calls & 331 consistent gets: doesn't that justify ?

        I ran the query multiple times, so that first physical IO will then move to consistent read. So, now that means, that the FTS is going for 91 blocks

         

        SQL> set lines 500 pages 50000
        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 |    23   (0)| 00:08:13 |
        |   1 |  SORT AGGREGATE    |                      |     1 |            |          |
        |   2 |   TABLE ACCESS FULL| MI_XCT_TSR_XEF       | 30150 |    23   (0)| 00:08:13 |
        -----------------------------------------------------------------------------------
        
        Statistics
        ----------------------------------------------------------
                 40  recursive calls
                  0  db block gets
                155  consistent gets
                 90  physical reads
                  0  redo size
                528  bytes sent via SQL*Net to client
                525  bytes received via SQL*Net from client
                  2  SQL*Net roundtrips to/from client
                  5  sorts (memory)
                  0  sorts (disk)
                  1  rows processed
        
        SQL> /
        
          COUNT(*)
        ----------
             30150
        
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 1963492958
        -----------------------------------------------------------------------------------
        | Id  | Operation          | Name                 | Rows  | Cost (%CPU)| Time     |
        -----------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT   |                      |     1 |    23   (0)| 00:08:13 |
        |   1 |  SORT AGGREGATE    |                      |     1 |            |          |
        |   2 |   TABLE ACCESS FULL| MI_XCT_TSR_XEF       | 30150 |    23   (0)| 00:08:13 |
        -----------------------------------------------------------------------------------
        
        Statistics
        ----------------------------------------------------------
                  0  recursive calls
                  0  db block gets
                 91  consistent gets
                  0  physical reads
                  0  redo size
                528  bytes sent via SQL*Net to client
                525  bytes received via SQL*Net from client
                  2  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
                  1  rows processed
        
        SQL> /
        
          COUNT(*)
        ----------
             30150
        
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 1963492958
        -----------------------------------------------------------------------------------
        | Id  | Operation          | Name                 | Rows  | Cost (%CPU)| Time     |
        -----------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT   |                      |     1 |    23   (0)| 00:08:13 |
        |   1 |  SORT AGGREGATE    |                      |     1 |            |          |
        |   2 |   TABLE ACCESS FULL| MI_XCT_TSR_XEF       | 30150 |    23   (0)| 00:08:13 |
        -----------------------------------------------------------------------------------
        
        Statistics
        ----------------------------------------------------------
                  0  recursive calls
                  0  db block gets
                 91  consistent gets
                  0  physical reads
                  0  redo size
                528  bytes sent via SQL*Net to client
                525  bytes received via SQL*Net from client
                  2  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
                  1  rows processed
        
        

         

         

        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.


        Yeah, I missed to put an assumption to the case, that HWM points to last block untill which all the blocks are filled with data.

        btw, what else is the use of dict. views like DBA_TABLES other than to provide information collected using DBMS_STATS ?

        • 16. Re: Reading Null to fulfill db_file_multiblock_read_count
          Jonathan Lewis

          The effects vary depending on whether you use "create as select" or "create, insert as select", and there seems to be a special case (bug ?) when CTAS generates a single block of data. Then there's the issue of whether you're using uniform extents or system managed extent sizing and the oddities of small tables - which you are working with if you have a table that is smaller than the db_file_multiblock_read_count.  You can also see variations due to the value of the Oracle process id generating the data - which also means the effects vary depending on whether the data is inserted by a single process or multiple (concurrently connected) processes.

           

          You also have to be very clear about what you mean when you talk about the table being X blocks - is this the actual number of blocks with data in, the number of formatted blocks, or the number of blocks reported in user_tables.blocks, or "blocks - empty_blocks"  after gathering table stats.

           

          Bottom line - your colleague is wrong, but it's easy to produce a demonstration which just happens to hit a case where it looks as if he's right.

           

          Regards

          Jonathan Lewis

          Not on Twitter: @jloracle

          • 17. Re: Reading Null to fulfill db_file_multiblock_read_count
            Lalit Kumar B

            Jonathan -

             

            Completely agreed.

             

            Regarding the Oracle process id - it is a very important factor to be considered. As I mentioned in my previous post, in a RAC environment things will vary even more. Each node will be doing a multiblock read.

             

            X Blocks - I think OP is referring to the data blocks that hold the data for that table. Not the empty blocks.

             

            The bottomline is perfect! Once again the Oracle golden words "It depends".

             

            Hope OP's question is answered by now. Deeper info would just mess up the things.

            • 18. Re: Reading Null to fulfill db_file_multiblock_read_count
              Stefan Koehler

              Hi jjk,

               

              > The query was hard-parsed and hence the 35 recursive calls & 331 consistent gets: doesn't that justify ?

              Justify what? You wanted to know (original quote) "Actually, the test was to see how much physical/logical IO is performed so as to get an idea on total blocks read" ... and this can not be get from the summary. The summary includes the work (logical and physical I/O), that is done by the recursive and non-recursive (main query) SQLs. You can not split it up manually - just by using these stats.

               

              I ran the query multiple times, so that first physical IO will then move to consistent read. So, now that means, that the FTS is going for 91 blocks

              First you did not mention that and secondly you see on your own what i mean. The main query causes only 91 consistent gets and the other 240 consistent gets were caused by the recursive queries (for parsing, ddic access and so on). So how do you know how many physical reads (from the 90) were caused by the main query? Maybe Oracle also needed to read some blocks of the dictionary tables or indexes from disk, etc..

               

              > Yeah, I missed to put an assumption to the case, that HWM points to last block untill which all the blocks are filled with data.

              Which HWM and what is your "last" filled block? Below you have mentioned that you are using LMT and ASSM - so you need to be much more specific. How have you verified that the HWM (and which HWM) points to the last "filled" block (whatever the definition is)?

               

              > btw, what else is the use of dict. views like DBA_TABLES other than to provide information collected using DBMS_STATS ?

              ?? You said "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." ... No, you don't ..  you rely up to 100 percent on BLOCKS in DBA_TABLES for your observations / assumptions (and these are only statistics - not necessarily the real world). How can it be that you perform 91 logical I/Os by a FTS, if you only have 88 physical data blocks and your stats are that what you think? (e.g. except chained rows over several blocks) What about LHWM and HHWM (as you are using LMT and ASSM)? Are these 88 blocks below the LHWM or does it include the blocks between LHWM and HHWM as well and how accurate is this statistic in the end at all?


              ... and at last you still have the open question "How many blocks were read by a single I/O request?".


              Regards

              Stefan

              1 2 Previous Next