This discussion is archived
5 Replies Latest reply: Jan 16, 2012 5:57 AM by CharlesHooper RSS

physical reads

905632 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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.

Legend

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