This discussion is archived
10 Replies Latest reply: Dec 10, 2012 3:01 PM by rp0428 RSS

more physical reads than blocks (in this partition)

901531 Newbie
Currently Being Moderated
Hi all,

I have table hash-partitioned on an indexed column "id" which is non-unique and part of my primary key. Inside each partition, rows with the same id are located close to each other which is done by dbms_redefinition reorg using orderby_cols. The intention is to reduce the amount of physical reads since there are no queries that don't filter on the id column.

What I'm seeing is a lot of physical reads though. The first partition has roughly 80K rows, an average row length of 347, block size of 8K and compression ... resulting in 821 blocks. And when (after flushing buffered cache and shared pool) submit a query that filters on "id" only and results is 106 selected rows, I see roughly 1400 physical reads.

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 36782 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION HASH SINGLE | | 106 | 36782 | 3 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| XXX | 106 | 36782 | 3 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | XXXXX | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ID"=49743)


Statistiken
----------------------------------------------------------
22243 recursive calls
0 db block gets
66651 consistent gets
1404 physical reads
0 redo size
10933 bytes sent via SQL*Net to client
299 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
150 sorts (memory)
0 sorts (disk)
106 rows processed


I was hoping to see around 10-15 physical reads. And I don't understand how it can be 1400 physical reads with only 821 blocks in this partition. Can somebody explain this behavior to me?

thanks in advance,
Dirk
  • 1. Re: more physical reads than blocks (in this partition)
    sb92075 Guru
    Currently Being Moderated
    drabbit wrote:
    Hi all,

    I have table hash-partitioned on an indexed column "id" which is non-unique and part of my primary key. Inside each partition, rows with the same id are located close to each other which is done by dbms_redefinition reorg using orderby_cols. The intention is to reduce the amount of physical reads since there are no queries that don't filter on the id column.

    What I'm seeing is a lot of physical reads though. The first partition has roughly 80K rows, an average row length of 347, block size of 8K and compression ... resulting in 821 blocks. And when (after flushing buffered cache and shared pool) submit a query that filters on "id" only and results is 106 selected rows, I see roughly 1400 physical reads.

    --------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 106 | 36782 | 3 (0)| 00:00:01 | | |
    | 1 | PARTITION HASH SINGLE | | 106 | 36782 | 3 (0)| 00:00:01 | 1 | 1 |
    | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| XXX | 106 | 36782 | 3 (0)| 00:00:01 | 1 | 1 |
    |* 3 | INDEX RANGE SCAN | XXXXX | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
    --------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - access("ID"=49743)


    Statistiken
    ----------------------------------------------------------
    22243 recursive calls
    0 db block gets
    66651 consistent gets
    1404 physical reads
    0 redo size
    10933 bytes sent via SQL*Net to client
    299 bytes received via SQL*Net from client
    9 SQL*Net roundtrips to/from client
    150 sorts (memory)
    0 sorts (disk)
    106 rows processed


    I was hoping to see around 10-15 physical reads. And I don't understand how it can be 1400 physical reads with only 821 blocks in this partition. Can somebody explain this behavior to me?

    thanks in advance,
    Dirk
    SQL_TRACE trace file content will show every physical read that was done.
    I trust Oracle to accurately count reality.

    how will your life change when you know what accounts for the difference between 1400 & 800?
  • 2. Re: more physical reads than blocks (in this partition)
    901531 Newbie
    Currently Being Moderated
    I also trust Oracle. But I don't trust in myself when it comes to Oracle.

    I must assume that I'm doing something wrong and can't figure it out. Oracle is doing 100x the amount of physical reads I'm hoping for. That is a significant performance loss I'm trying to prevent. The partitioned and reorged table is an attempt to increase performance. But when my physical reads increase from roughly 40 (in the regular table I haven't mentioned so far with the same data) to 1400, then this is a show stopper. I'm trying to find the error I made. The 800 vs. 1400 is only an indicator I mention to help readers come up with an idea about what I did wrong.

    Dirk
  • 3. Re: more physical reads than blocks (in this partition)
    sb92075 Guru
    Currently Being Moderated
    drabbit wrote:
    I also trust Oracle. But I don't trust in myself when it comes to Oracle.
    The physical read count will change (decrease) if you run the test again, again.
  • 4. Re: more physical reads than blocks (in this partition)
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Dirk,

    there are several possible cause for excessive I/O, including:

    1) row chaining/migration
    2) concurrent activities (Oracle needs to additionally read from UNDO to provide read-consistency)
    3) cache warm-up

    if you look in the raw trace file, then for each physical read you will see file and block ids together with the number of blocks read -- using this information it's very straightforward to establish which object is being read (e.g. http://dioncho.wordpress.com/2009/07/06/object-name-from-file-and-block/). And if you're on 11g, you don't even have to do that since the trace file dumps object_id.

    Best regards,
    Nikolay
  • 5. Re: more physical reads than blocks (in this partition)
    rp0428 Guru
    Currently Being Moderated
    >
    And when (after flushing buffered cache and shared pool)
    >
    I don't believe that flushing would normally be performed when conducting tests like this.

    And some of these numbers look high also
    >
    66651 consistent gets
    1404 physical reads
    . . .
    150 sorts (memory)
    >
    Why 150 sorts? Why 66651 consistent gets?

    Post the actual query you are using, the table and index ddl and results of running query without having flushed.
  • 6. Re: more physical reads than blocks (in this partition)
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    >
    What I'm seeing is a lot of physical reads though. The first partition has roughly 80K rows, an average row length of 347, block size of 8K and compression ... resulting in 821 blocks. And when (after flushing buffered cache and shared pool) submit a query that filters on "id" only and results is 106 selected rows, I see roughly 1400 physical reads.
    missed this bit the first time -- flushing buffered cache really isn't a good idea if you're on 11g, because Oracle will try to warm-up the buffer cache, causing extra reads. Probably not that many, but it's hard to tell exactly.

    Best regards,
    Nikolay
  • 7. Re: more physical reads than blocks (in this partition)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    drabbit wrote:
    What I'm seeing is a lot of physical reads though. The first partition has roughly 80K rows, an average row length of 347, block size of 8K and compression ... resulting in 821 blocks. And when (after flushing buffered cache and shared pool) submit a query that filters on "id" only and results is 106 selected rows, I see roughly 1400 physical reads.
    --------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |       |   106 | 36782 |     3   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION HASH SINGLE             |       |   106 | 36782 |     3   (0)| 00:00:01 |     1 |     1 |
    |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| XXX   |   106 | 36782 |     3   (0)| 00:00:01 |     1 |     1 |
    |*  3 |    INDEX RANGE SCAN                | XXXXX |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
    --------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    3 - access("ID"=49743)
    
    
    Statistiken
    ----------------------------------------------------------
    22243  recursive calls
    0  db block gets
    66651  consistent gets
    1404  physical reads
    0  redo size
    10933  bytes sent via SQL*Net to client
    299  bytes received via SQL*Net from client
    9  SQL*Net roundtrips to/from client
    150  sorts (memory)
    0  sorts (disk)
    106  rows processed
    To me the most significant statistic is the recursive calls. Even for a partitioned table with (say) a few thousand partitions this seems very high for a simple parse and execute call which ends up accessing a known single partition by index.

    Test strategies - re-run the query immediately to see if you get similar results, re-run the query ONLY after a flush buffer_cache (not shared_pool).
    It may be that most of the work done relates to optimisation, or to calls to (e.g.) an embedded pl/sql function. The behaviour of the repeated executions may give you a clue about this.
    (And, if neither option helps, then enabling trace and checking the trace file should help).

    Regards
    Jonathan Lewis
  • 8. Re: more physical reads than blocks (in this partition)
    901531 Newbie
    Currently Being Moderated
    thanks all for the help. Apparently flushing the shared_pool caused all the extra work. By just flushing the buffer_cache, the results look realistic, and most of all: very good.

    Thanks again for the help. I keep learning. Apparently the first select after flushing the shared_pool on a partitioned table causes lots of physical reads, recursive calls, sorts, etc. . The first select on the non-partitioned equivalent table didn't cause that.

    Dirk
  • 9. Re: more physical reads than blocks (in this partition)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    drabbit wrote:
    thanks all for the help. Apparently flushing the shared_pool caused all the extra work. By just flushing the buffer_cache, the results look realistic, and most of all: very good.
    I know this is months old, but I wrote a blog item to explain the problem in detail when the final comment was made - then forgot to link back to it, and it might be a useful reference for other people.

    http://jonathanlewis.wordpress.com/2012/07/13/recursive-sql/

    Regards
    Jonathan Lewis
  • 10. Re: more physical reads than blocks (in this partition)
    rp0428 Guru
    Currently Being Moderated
    >
    I know this is months old, but I wrote a blog item to explain the problem in detail when the final comment was made - then forgot to link back to it, and it might be a useful reference for other people.
    >
    Very nice, succinct test case and result summary. One for the toolkit. Thanks for posing the reminder and link.

    Hopefully the OP will see your update and provide some confirming information about the numbers of partitions and indexes.

Legend

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