5 Replies Latest reply: Jan 16, 2012 7:57 AM by Charles Hooper RSS

    physical reads

    905632
      i have table as below
      SQL> desc xyz;
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       ID                                                 NUMBER
       NAME                                               VARCHAR2(10)
      No indexes for the table, and the statistics are gathered, the table is having 31360 blocks, which i have queried from dba_segments.But when i do the below query, the physical reads are shown more than the actual blocks, could some one tell me y it was happened like that.
      select count(distinct name) from xyz;
      
      SQL> select count(distinct name) from xyz;
      
      COUNT(DISTINCTNAME)
      -------------------
                  9999999
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2724528536
      
      --------------------------------------------------------------------------------
      ---------
      
      | Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| T
      ime     |
      
      --------------------------------------------------------------------------------
      ---------
      
      |   0 | SELECT STATEMENT     |          |     1 |     7 |       | 52253   (1)| 0
      0:10:28 |
      
      |   1 |  SORT AGGREGATE      |          |     1 |     7 |       |            |
              |
      
      |   2 |   VIEW               | VW_DAG_0 |    10M|    66M|       | 52253   (1)| 0
      0:10:28 |
      
      |   3 |    HASH GROUP BY     |          |    10M|   104M|   191M| 52253   (1)| 0
      0:10:28 |
      
      |   4 |     TABLE ACCESS FULL| XYZ      |    10M|   104M|       |  8493   (1)| 0
      0:01:42 |
      
      --------------------------------------------------------------------------------
      ---------
      
      
      
      Statistics
      ----------------------------------------------------------
              178  recursive calls
                0  db block gets
            30380  consistent gets
            52317  physical reads
                0  redo size
              436  bytes sent via SQL*Net to client
              419  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
      and also please tell why logical i/o is better considered than physical i/o, as i have seen in asktom site, but it was not so clear.
        • 1. Re: physical reads
          Mich Talebzadeh
          You stated:

          "and also please tell why logical i/o is better considered than physical i/o, as i have seen in asktom site, but it was not so clear."

          OK let me turn around and ask this question:

          What is the difference between Logical I/O and Physical I/O and more to the point what we mean by Physical I/O.

          When you want to retrieve data from the database, the fastest way to do this is if the data the query returns is already located in the buffer cache. If it is not located in cache, then Oracle process needs to perform physical I/O to retrieve the data from disk or from the file cache as case may be. Physical I/O is much slower than a cache read (Logical I/O) so we need to reduce the amount of physical I/O the database does. I am sure Tom mentioned this.

          HTH,

          Mich
          • 2. Re: physical reads
            Charles Hooper
            902629 wrote:
            i have table as below
            SQL> desc xyz;
            Name                                      Null?    Type
            ----------------------------------------- -------- ----------------------------
            ID                                                 NUMBER
            NAME                                               VARCHAR2(10)
            No indexes for the table, and the statistics are gathered, the table is having 31360 blocks, which i have queried from dba_segments.But when i do the below query, the physical reads are shown more than the actual blocks, could some one tell me y it was happened like that.
            Two typical causes for the physical reads statistic returning a value higher than the consistent gets are:
            * Index prefetch
            * Temp tablespace usage

            The execution plan that you posted suggested estimates that 191M of temp tablespace will be used. I set up a test case to demonstrate the second bullet point. First, let's create the test table and gather statistics:
            DROP TABLE T1 PURGE;
             
            CREATE TABLE T1 (
              C1 NUMBER NOT NULL,
              C2 NUMBER NOT NULL,
              C3 VARCHAR2(30) NOT NULL,
              C4 VARCHAR2(200));
             
            INSERT INTO T1
            SELECT
              MOD(ROWNUM-1, 90) * 4 C1,
              ROWNUM - 1 C2,
              TO_CHAR(ROWNUM - 1, 'RN') C3,
              LPAD('A',200,'A') C4
            FROM
              (SELECT
                 ROWNUM RN
               FROM
                 DUAL
               CONNECT BY
                 LEVEL<=1000000),
              (SELECT
                 ROWNUM RN
               FROM
                 DUAL
               CONNECT BY
                 LEVEL<=10);
             
            COMMIT;
             
            EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
             
            SET AUTOTRACE ON
            SET PAGESIZE 1000
            SET LINESIZE 120
            in the above, column C3 contains the Roman numeral value for the number in column C2, and the numbers in column C2 increase from 0 to 9,999,999.

            Let's try an initial test with the PGA_AGGREGATE_TARGET set to 2000M (we will flush the buffer cache first):
            ALTER SYSTEM FLUSH BUFFER_CACHE;
             
            SELECT
              COUNT(DISTINCT C3)
            FROM
              T1;
             
            COUNT(DISTINCTC3)
            -----------------
                         4000
             
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 405047221
             
            ---------------------------------------------------------------------------------
            | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
            ---------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT     |          |     1 |    17 |   128K (15)| 00:00:52 |
            |   1 |  SORT AGGREGATE      |          |     1 |    17 |            |          |
            |   2 |   VIEW               | VW_DAG_0 |  4000 | 68000 |   128K (15)| 00:00:52 |
            |   3 |    HASH GROUP BY     |          |  4000 | 64000 |   128K (15)| 00:00:52 |
            |   4 |     TABLE ACCESS FULL| T1       |    10M|   152M|   114K  (5)| 00:00:46 |
            ---------------------------------------------------------------------------------
             
            Statistics
            ----------------------------------------------------------
                      1  recursive calls
                      0  db block gets
                 322828  consistent gets
                 322822  physical reads
                      0  redo size
                    355  bytes sent via SQL*Net to client
                    359  bytes received via SQL*Net from client
                      2  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            Notice in the above output that the consistent gets value is greater than the physical reads value, as you would commonly expect.

            Now, let's switch to a manual work area policy, and make certain that the SORT_AREA_SIZE parameter is set at the default value of 64KB before repeating the test:
            ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
            ALTER SESSION SET SORT_AREA_SIZE=65536;
            ALTER SYSTEM FLUSH BUFFER_CACHE;
             
            SELECT
              COUNT(DISTINCT C3)
            FROM
              T1;
             
            COUNT(DISTINCTC3)
            -----------------
                         4000
             
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 405047221
             
            -----------------------------------------------------------------------------------------
            | Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
            -----------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT     |          |     1 |    17 |       |   318K  (8)| 00:02:08 |
            |   1 |  SORT AGGREGATE      |          |     1 |    17 |       |            |          |
            |   2 |   VIEW               | VW_DAG_0 |  4000 | 68000 |       |   318K  (8)| 00:02:08 |
            |   3 |    HASH GROUP BY     |          |  4000 | 64000 |   229M|   318K  (8)| 00:02:08 |
            |   4 |     TABLE ACCESS FULL| T1       |    10M|   152M|       |   114K  (5)| 00:00:46 |
            -----------------------------------------------------------------------------------------
             
            Statistics
            ----------------------------------------------------------
                    233  recursive calls
                      0  db block gets
                 322828  consistent gets
                 352249  physical reads
                      0  redo size
                    355  bytes sent via SQL*Net to client
                    359  bytes received via SQL*Net from client
                      2  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            Notice in the above output that the consistent gets value is less than the physical reads value, and that the optimizer predicted that 229M of temp tablespace would be required to execute the query.

            ---

            An unrelated side note, but I would have thought that there would be more than 4,000 Roman numeral values between the numbers 0 and 9,999,999.
            SELECT
              C3,
              COUNT(*)
            FROM
              T1
            GROUP BY
              C3
            HAVING
              COUNT(*)>1;
             
            C3                               COUNT(*)
            ------------------------------ ----------
            ###############                   9996001
             
            SELECT
              MAX(LENGTH(C3))
            FROM
              T1;
             
            MAX(LENGTH(C3))
            ---------------
                         15
            Charles Hooper
            http://hoopercharles.wordpress.com/
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 3. Re: physical reads
              905632
              Thanks Charles, it was really nice explanation.

              Two typical causes for the physical reads statistic returning a value higher than the consistent gets are:
              * Index prefetch
              * Temp tablespace usage


              Could you please explain the first bullet point ie.. Index prefetch, like above

              Edited by: 902629 on Jan 15, 2012 9:17 PM
              • 4. Re: physical reads
                905632
                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6643159615303

                i know the differences between LIO and PIO, i don't have good clarity, how the LIO is better than PIO, some theory is there that we're missing.Some body could you please share some explanation on this, possibly with any example.

                Thanks,
                Sam.
                • 5. Re: physical reads
                  Charles Hooper
                  902629 wrote:
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6643159615303

                  i know the differences between LIO and PIO, i don't have good clarity, how the LIO is better than PIO, some theory is there that we're missing.Some body could you please share some explanation on this, possibly with any example.

                  Thanks,
                  Sam.
                  Sam,

                  Take a look at Jonathan Lewis' definition of LIO: (a.k.a Logical I/O)
                  http://jonathanlewis.wordpress.com/2009/06/23/glossary/
                  Follow the provided link in the Glossary for a detailed description of consistent gets subset of logical I/O.

                  Additional information about the effects of logical I/O may be found in the book "Optimizing Oracle Performance":
                  http://books.google.com/books?id=mvJW6t7mYU0C&pg=PT345

                  In general, a logical I/O may or may not require a physical I/O, depending on whether or not the block of interest (and the undo blocks of interest) are already in the buffer cache. A physical I/O simply means that a request was made of the operating system to read (or write) a portion of an Oracle file - that request may be satisfied by the operating system's file cache memory, a RAID controller's read/write cache memory, a SAN's read/write cache memory, a cache built into the physical drive, or by actually reading/writing the data from the physical spinning disk (or non-volitile SSD memory).

                  Too many physical I/Os in a short period of time can be bad for performance, and too many logical I/Os in a short period of time can also be bad for performance. One of the goals of tuning is to reduce the number of logical I/Os, which will also tend to reduce the number of physical I/Os.

                  The above description is very brief, and could be greatly expanded, but that level of detail is likely not necessary here.

                  Charles Hooper
                  http://hoopercharles.wordpress.com/
                  IT Manager/Oracle DBA
                  K&M Machine-Fabricating, Inc.