10 Replies Latest reply: Dec 10, 2012 5:01 PM by rp0428 RSS

    more physical reads than blocks (in this partition)

    901531
      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
          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
            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
              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
                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
                  >
                  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
                    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
                      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
                        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
                          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
                            >
                            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.