Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    5

    Question Unanswered: understanding the reason behind disk IO

    I am trying to understand the reason for disk IO with my test database on Oracle 11g.

    Details of the table:
    SQL> desc test;
    Name Null? Type
    --------------------------------------- -------- ----------------------------
    A NOT NULL NUMBER(38)
    B NOT NULL NUMBER(38)
    C NOT NULL CHAR(120)
    D NOT NULL CHAR(60)


    Number of Rows : 2100000

    Size of the Table : ~500MB

    Buffer details:
    Total System Global Area 3657945088 bytes
    Fixed Size 2112040 bytes
    Variable Size 486540760 bytes
    Database Buffers 3154116608 bytes
    Redo Buffers 15175680 bytes

    Data buffers is set to 3008MB, hence I am assuming the table should be completely cached. But when I do the following query

    select * from test where D='ball';

    there is a lot of disk IO happening...

    Any help would be great!!

    Thanks,
    Nanja

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1. Does it do lot of IO the second time you do the query? The first time you run it will load the table into the cache.

    2. Dont forget there are other things which might be in the cache i.e. the system tables etc.

    3. Do you have indexes on the table as they will also use the cache.

    Alan

  3. #3
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    In addition to Alan's questions:

    1. What type of server is this running on, how much physical memory is there on the server?

    2. What is the OS and version?

    3. What else is running on the server?

    4. What data are you using to determine there is a lot of disk I/O? Can you quantify how much "a lot of disk I/O" is?

    5. Just curious, how long does the query take to run?

  4. #4
    Join Date
    May 2008
    Posts
    5

    Talking

    Thanks for your responses ....Issue is resolved now.

    I think the issue was with IO chunk size by setting the "db_read_multiblock_read_count parameter=32"

    For the same value 10g works fine in the same system setup, but somehow 11g does table scan over index.

    To answer some questions for anyways, IO used to happen for consequent select queries..It was taking about 6 seconds everytime and disk IO was ~450 MB of data.

    Thanks again,
    Nanja

  5. #5
    Join Date
    Jan 2008
    Posts
    27
    Hey nanjappan,

    Quote: I think the issue was with IO chunk size by setting the "db_read_multiblock_read_count parameter=32"

    Did you mean "db_file_multiblock_read_count"?
    Regards,

    divroro12

  6. #6
    Join Date
    May 2008
    Posts
    5
    hey divroro12,

    Thanks for pointing out ...that was my typo.

    I meant "db_file_multiblock_read_count".

    Thanks again,
    Nanja

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •