This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Nov 19, 2012 10:41 AM by Richard Foote RSS

Too much Logical IOs on a locally partition index !!

872581 Newbie
Currently Being Moderated
Hi, all.

The database is 11.2.0.3 on a linux machine.

I issued a query and found that 0 row was retrived but logical ios on "IN_BPMDNPMTACT_DX1" index is over 25000 blocks.

I would like to know where 25006 block io on the index came from. (operation ID = 12)

- the number of partition is 6.
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Starts | E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |      1 |          |       |       |      0 |00:00:00.08 |   25006 |       |       |      |
|   1 |  NESTED LOOPS                 |                    |      1 | 00:07:35 |       |       |      0 |00:00:00.08 |   25006 |       |       |      |
|   2 |   VIEW                        | VW_NSO_1           |      1 | 00:07:34 |       |       |      0 |00:00:00.08 |   25006 |       |       |      |
|   3 |    HASH UNIQUE                |                    |      1 | 00:07:34 |       |       |      0 |00:00:00.08 |   25006 |  1036K|  1036K|      |
|   4 |     HASH GROUP BY             |                    |      1 | 00:07:34 |       |       |      0 |00:00:00.08 |   25006 |   792K|   792K|      |
|*  5 |      FILTER                   |                    |      1 |          |       |       |      0 |00:00:00.08 |   25006 |       |       |      |
|   6 |       VIEW                    |                    |      1 | 00:06:16 |       |       |      0 |00:00:00.08 |   25006 |       |       |      |
|*  7 |        COUNT STOPKEY          |                    |      1 |          |       |       |      0 |00:00:00.08 |   25006 |       |       |      |
|   8 |         VIEW                  |                    |      1 | 00:06:16 |       |       |      0 |00:00:00.08 |   25006 |       |       |      |
|*  9 |          SORT ORDER BY STOPKEY|                    |      1 | 00:06:16 |       |       |      0 |00:00:00.08 |   25006 |  1024 |  1024 |      |
|  10 |           PARTITION RANGE ALL |                    |      1 | 00:02:10 |     1 |     6 |      0 |00:00:00.08 |   25006 |       |       |      |
|* 11 |            COUNT STOPKEY      |                    |      6 |          |       |       |      0 |00:00:00.08 |   25006 |       |       |      |
|* 12 |             INDEX RANGE SCAN  | IN_BPMDNPMTACT_DX1 |      6 | 00:02:10 |     1 |     6 |      0 |00:00:00.08 |   25006 |       |       |      |
|  13 |       PARTITION RANGE ALL     |                    |      0 | 00:00:01 |     1 |     6 |      0 |00:00:00.01 |       0 |       |       |      |
|* 14 |        INDEX RANGE SCAN       | IN_BPMDNPMTACT_DX2 |      0 | 00:00:01 |     1 |     6 |      0 |00:00:00.01 |       0 |       |       |      |
|  15 |   TABLE ACCESS BY USER ROWID  | IN_BPMDNPMTACT     |      0 | 00:00:01 | ROWID | ROWID |      0 |00:00:00.01 |       0 |       |       |      |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

The following is the result of analyze index validate structure.

------------


HEIGHT     BLOCKS     NAME     PARTITION_NAME     LF_ROWS     LF_BLKS     BR_ROWS     BR_BLKS     DEL_LF_ROWS
4     182424     IN_BPMDNPMTACT_DX1     P201208     21655066     173118     173117     592     1790326
3     28304     IN_BPMDNPMTACT_DX1     P201209     4019084     27256     27255     73     0
3     29304     IN_BPMDNPMTACT_DX1     P201210     4186481     28465     28464     74     0
3     32704     IN_BPMDNPMTACT_DX1     P201211     2813299     32019     32018     121     0

-----------------------------
Thanks in advance.

Best Regards.
  • 1. Re: Too much Logical IOs on a locally partition index !!
    rp0428 Guru
    Currently Being Moderated
    >
    I issued a query and found that 0 row was retrived but logical ios on "IN_BPMDNPMTACT_DX1" index is over 25000 blocks.
    >
    Query? What query? I don't see any query.
    >
    I would like to know where 25006 block io on the index came from. (operation ID = 12)
    >
    Not sure what you are asking. The blocks are in the index. Oracle queries the index using a range scan looking for rows that meet your undisclosed query criteria and doesn't find any.

    I can do a select from a table with no indexes and a full table scan will read every block whether any rows are returned or not.

    So what is it you are asking?
  • 2. Re: Too much Logical IOs on a locally partition index !!
    872581 Newbie
    Currently Being Moderated
    Thanks for your reply.

    "25006" blocks were read (operation ID=12), but "A-rows" is "0".
    The index has 6 partitioned index as you see in the plan.

    ----------------------
    My expectation is as followings :
    1. (operation ID = 12) starts "6" for each partitioned index. (pstart : 1, pstop:6)

    2. expected block io on one partioned index segment is
    a. index segment header block : 1 block
    b. index root block : 1 block
    c. index branch block : 2~ 3 block
    d. index leaf block : zero block

    therefore, a+b+c+d = 1+1+3+0= 5 block io is necessary in order to find out there is no row for a where predicate.
    (A-rows is zero --**)

    --> 5 block for one partition, and thus, 5*6 partitioned ( about 30 index block io is necessary for operation ID=12)


    This is my expectation.
    -------------------------


    As you see in the above plan, however, 25006 block IO happened for operation ID=12.

    I wonder why oracle need 25006 block io in order to find out there is no row for the where predicate.

    Thanks in advance.

    Best Regards.
  • 3. Re: Too much Logical IOs on a locally partition index !!
    rp0428 Guru
    Currently Being Moderated
    >
    I wonder why oracle need 25006 block io in order to find out there is no row for the where predicate.
    >
    Query? What query? I don't see any query.

    Predicate? What predicate? I don't see any predicate.

    Index DDL that shows the columns in the index and the column order? I don't see any index DDL.
    >
    --> 5 block for one partition, and thus, 5*6 partitioned ( about 30 index block io is necessary for operation ID=12)

    This is my expectation.
    >
    Your expectation is clashing with Oracle's reality. But we can't see Oracle's reality for these reasons
    >
    Query? What query? I don't see any query.

    Predicate? What predicate? I don't see any predicate.

    Index DDL that shows the columns in the index and the column order? I don't see any index DDL.
    >
    The only part of Oracle's reality that you posted is the number of blocks and this
    >
    INDEX RANGE SCAN
    >
    So you should ask yourself: how many blocks must Oracle read to do the ENTIRE RANGE SCAN? Gee, perhaps that depends on how many keys have to be looked at and how many keys fit into each block (key size). Unfortunately, as noted above, that is a part of Oracle's reality that we can't see from here.
  • 4. Re: Too much Logical IOs on a locally partition index !!
    872581 Newbie
    Currently Being Moderated
    sorry for missing query and thanks for your reply.


    IN_BPMDNPMTACT_DX1 index column order (PROC_RSLT, SEQ, TOP_SA_ID ,IA_ID)

    PROC_RSLT --> NUMBER(2)
    SEQ --> NUMBER(19)
    TOP_SA_ID --> VARCHAR2(11 BYTE)
    IA_ID --> VARCHAR2(11 BYTE)

    Query is as follows :
       SELECT /*+ rowid(a) */
              a.top_sa_id, a.ia_id, a.seq, a.pr_flag, a.proc_rslt
         FROM hoho.in_bpmdnpmtact a
        WHERE a.ROWID IN (
                 SELECT   SUBSTR (MIN (LPAD (x.seq, 15, '0') || x.rid), -18) rid
                     FROM (SELECT top_sa_id, ia_id, seq, rid
                             FROM (SELECT   /* index(x IN_BPMDNPMTACT_DX1) */x.top_sa_id, x.ia_id, x.seq,
                                            x.ROWID rid
                                       FROM hoho.in_bpmdnpmtact x
                                      WHERE x.proc_rslt = 1 AND x.seq > 0
                                   ORDER BY x.seq)
                            WHERE ROWNUM <= 1000) x
                    WHERE NOT EXISTS (
                             SELECT /*+ no_unnest index(y IN_BPMDNPMTACT_DX2) */
                                    1
                               FROM hoho.in_bpmdnpmtact y
                              WHERE x.top_sa_id = y.top_sa_id
                                AND x.ia_id = y.ia_id
                                AND y.proc_rslt IN (2, 94, 95, 96, 97, 98, 99)
                          )
                 GROUP BY x.top_sa_id, x.ia_id)
    Thanks in advance.
    Best Regards.
  • 5. Re: Too much Logical IOs on a locally partition index !!
    rp0428 Guru
    Currently Being Moderated
    So there are a few things that your code shows.

    You are using three different hints. Why? hints should generally be used only for troubleshooting to find out WHY an execution plan is being used and what the effect would be if a different plan was used. In production code there should not be any hints. If your hints are being used in production code it is likely a sign that there is a problem with your query that needs to be addressed.

    Of course there are exceptions to the 'no hints' rule. The common exceptions are for bulk processing when you use the APPEND hint and for remote queries where you might use a DRIVING_SITE hint.

    Then there is your use of the 'rowid' hint.
    SELECT /*+ rowid(a) */
    You are using a hint that was deprecated in Oracle 10g and SHOULD NOT be used. See my reply at the end of this thread
    Re: what is means ? /*+ ROWID (dda) */

    So looking at the code the predicate section is basically this
    WHERE x.proc_rslt = 1 AND x.seq > 0
                                   ORDER BY x.seq)
                            WHERE ROWNUM <= 1000) x
                    WHERE NOT EXISTS (
                             SELECT /*+ no_unnest index(y IN_BPMDNPMTACT_DX2) */
                                    1
                               FROM hoho.in_bpmdnpmtact y
                              WHERE x.top_sa_id = y.top_sa_id
                                AND x.ia_id = y.ia_id
                                AND y.proc_rslt IN (2, 94, 95, 96, 97, 98, 99)
    That shows a discrete set of PROC_RSLT values and that is the leading column of your index.
    >
    IN_BPMDNPMTACT_DX1 index column order (PROC_RSLT, SEQ, TOP_SA_ID ,IA_ID)
    >
    So now you should try to answer that querion of my that you didn't answer
    >
    So you should ask yourself: how many blocks must Oracle read to do the ENTIRE RANGE SCAN? Gee, perhaps that depends on how many keys have to be looked at and how many keys fit into each block (key size).
    >
    One thought experiment to use is to EXAGGERATE things. Oracle has decided to do an INDEX RANGE SCAN. The leading column in the index is PROC_RSLT. So let's exaggerate and say that EVERY row has a PROC_RSLT value of 1. Oracle would have to read EVERY INDEX BLOCK to scan all of those values and check SEQ > 0 so how many blocks would that be? Naturally your < ROWNUM clause will artificially limit that.

    Get the idea? You have a 'PROC_RSLT IN (2, 94, 95, 96, 97, 98, 99)' clause that means Oracle needs to look for those values also.

    There could be ANY NUMBER of blocks that have a value of 1 and seq > 0. That is why your expectation is far off the mark.

    If you do some testing you can get a better idea how many values there really are for your PROC_RSLT values. Just query the count of records for the values in your predicate. Do a count of '1's and a separate count of the '2, 94, ...' list.

    The fact that you don't get any rows possibly means you have a conflict between the PROC_RSLT = 1 part and the NOT EXISTS part but I have no way of knowing. Zero rows might be the correct result for your query. Odds are though that your < ROWNUM clause that limits the first part of the query to 1000 rows is what causes the zero rows in the result set since the NOT EXISTS may filter all of those rows out.

    Remove the ROWID hint since it is deprecated. And, if I were you, I would remove the other hints as well and see what the result is. It's possible that your query logic is not even correct so that problem, if it exists, needs to be fixed first. It matters not how fast a query is if it doesn't return the correct results.

    After you confirm that your query actually returns the correct results then you can see if there are any performance issues that need to be addressed.
  • 6. Re: Too much Logical IOs on a locally partition index !!
    872581 Newbie
    Currently Being Moderated
    Thanks for your reply.

    I simplified my question.

    The count for proc_rslt=1 is "0", as followings.
       
    
    select count(*) from hoho.in_bpmdnpmtact t where proc_rslt=1 ;
    
      COUNT(*)
    ----------
             0
    
    SELECT x.top_sa_id, x.ia_id, x.seq,
           x.ROWID rid
      FROM hoho.in_bpmdnpmtact x
     WHERE x.proc_rslt = 1 AND x.seq > 0;
     
     
    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name               | Starts | E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                    |      1 |          |       |       |      0 |00:00:00.07 |   25006 |
    |   1 |  PARTITION RANGE ALL|                    |      1 | 00:02:10 |     1 |     6 |      0 |00:00:00.07 |   25006 |
    |*  2 |   INDEX RANGE SCAN  | IN_BPMDNPMTACT_DX1 |      6 | 00:02:10 |     1 |     6 |      0 |00:00:00.07 |   25006 |
    ----------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("X"."PROC_RSLT"=1 AND "X"."SEQ">0)
    I still do not understand why 25006 block read is necessary for "where proc_rslt=1" predicate.

    The number of partition is 6 and the index is b*tree based, and the height for each partitioned index is 3 or 4.

    Thanks in advance.
    Best Regards.
  • 7. Re: Too much Logical IOs on a locally partition index !!
    rp0428 Guru
    Currently Being Moderated
    >
    I still do not understand why 25006 block read is necessary for "where proc_rslt=1" predicate.
    >
    Are the stats up to date? What do the stats look like for that table and index? It looks like Oracle doesn't know that there are no index entries that match that predicate and that the buffers (logical reads) figure is an estimate and not the actual.

    Can you add the /*+ gather_plan_statistics */ hint to the query and do a trace to see what the actual io is?

    You might also try the 'ALLSTATS ALL'.
  • 8. Re: Too much Logical IOs on a locally partition index !!
    872581 Newbie
    Currently Being Moderated
    Thanks for your reply.

    The following is after gathering statistics.

    The block reads is the same.
     exec dbms_stats.gather_table_stats(ownname=>'HOHO',tabname=>'IN_BPMDNPMTACT',estimate_percent=>10, degree=>32 , method_opt=>'FOR ALL COLUMNS SIZE 1',Granularity=>'ALL',cascade=>TRUE);
    
    
     SELECT  /*+ gather_plan_statistics    */ x.top_sa_id, x.ia_id, x.seq, x.ROWID rid
     FROM hoho.in_bpmdnpmtact x
     WHERE x.proc_rslt = 1 AND x.seq > 0
     
    SELECT * 
    FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED ALLSTATS -PROJECTION -ROWS -COST -BYTES -ALIAS LAST'));
    
    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name               | Starts | E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                    |      1 |          |       |       |      0 |00:00:00.08 |   25006 |
    |   1 |  PARTITION RANGE ALL|                    |      1 | 00:02:10 |     1 |     6 |      0 |00:00:00.08 |   25006 |
    |*  2 |   INDEX RANGE SCAN  | IN_BPMDNPMTACT_DX1 |      6 | 00:02:10 |     1 |     6 |      0 |00:00:00.08 |   25006 |
    ----------------------------------------------------------------------------------------------------------------------
    
    ********************************************************************************  
    
    
      SELECT  /*+ gather_plan_statistics    */ y.top_sa_id, y.ia_id, y.seq, y.ROWID rid
     FROM hoho.in_bpmdnpmtact y
     WHERE y.proc_rslt = 1 AND y.seq > 0
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      0.07       0.07          0      25006          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.07       0.07          0      25006          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 112
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             0          0          0  PARTITION RANGE ALL PARTITION: 1 6 (cr=25006 pr=0 pw=0 time=73499 us cost=33902 size=207286309 card=4410347)
             0          0          0   INDEX RANGE SCAN IN_BPMDNPMTACT_DX1 PARTITION: 1 6 (cr=25006 pr=0 pw=0 time=73483 us cost=33902 size=207286309 card=4410347)(object id 792619)
    
    
    ********************************************************************************  
    I am very confused about 20056 block reads.

    Thanks in advance.
    Best Regards.

    Edited by: 869578 on 2012. 11. 18 오후 7:46
  • 9. Re: Too much Logical IOs on a locally partition index !!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    What is the actual execution plan and buffer count for "select count(*) from hoho.in_bpmdnpmtact t where proc_rslt=1 ;" ?

    What is the partition key for the table ?

    Hemant K Chitale
  • 10. Re: Too much Logical IOs on a locally partition index !!
    872581 Newbie
    Currently Being Moderated
    Thanks for your reply.

    The partition key is "trigger_date" (monthly ranged partitioned) .

    The plans for count is as belows :

    The block reads is 20056 (same).
    select count(*) from hoho.in_bpmdnpmtact t where proc_rslt=1
    
    Plan hash value: 111172958
    
    -----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name               | Starts | E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
    -----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |                    |      1 |          |       |       |      1 |00:00:00.08 |   25006 |
    |   1 |  SORT AGGREGATE      |                    |      1 |          |       |       |      1 |00:00:00.08 |   25006 |
    |   2 |   PARTITION RANGE ALL|                    |      1 | 00:02:10 |     1 |     6 |      0 |00:00:00.08 |   25006 |
    |*  3 |    INDEX RANGE SCAN  | IN_BPMDNPMTACT_DX1 |      6 | 00:02:10 |     1 |     6 |      0 |00:00:00.08 |   25006 |
    -----------------------------------------------------------------------------------------------------------------------
    Thanks in advance.
    Best regards.
  • 11. Re: Too much Logical IOs on a locally partition index !!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    So, searching for the "1" in the index seems to take time. What are the distinct values of PROC_RSLT and counts for each value ? Are the values all integers ? Were there entries as "1" earlier and having been deleted since then ?
    My guess is that Oracle is re-reading blocks for a consistent read. The tablespace is built with segment space management AUTO and there has been a curious pattern of inserts and deletes to the index.


    Hemant K Chitale
  • 12. Re: Too much Logical IOs on a locally partition index !!
    871018 Explorer
    Currently Being Moderated
    Probably your index is containing many empty blocks, blocks containing only deleted index entries.
    Check it
    analyze index IN_BPMDNPMTACT_DX1 validate structure;
    select lf_rows, del_lf_rows from index_stats;
    and do treedump.
    You can see something like this:
    ----- begin tree dump
    branch: 0x405e912 67496210 (0: nrow: 14, level: 3)
       branch: 0x32067894 839284884 (-1: nrow: 37, level: 2)
          branch: 0x6ecbd82f 1858852911 (-1: nrow: 26, level: 1)
             leaf: 0x5363dd92 1399053714 (-1: nrow: 28 rrow: 0)
             leaf: 0x7f472d2a 2135371050 (0: nrow: 20 rrow: 0)
             leaf: 0x5363dd91 1399053713 (1: nrow: 0 rrow: 0)
             leaf: 0x5363dd94 1399053716 (2: nrow: 29 rrow: 0)
             leaf: 0x5363dd93 1399053715 (3: nrow: 30 rrow: 0)
             leaf: 0x5363db51 1399053137 (4: nrow: 10 rrow: 0)
             leaf: 0x5363db52 1399053138 (5: nrow: 0 rrow: 0)
             leaf: 0x5363db54 1399053140 (6: nrow: 0 rrow: 0)
             leaf: 0x5363db53 1399053139 (7: nrow: 30 rrow: 0)
             leaf: 0x5363db57 1399053143 (8: nrow: 31 rrow: 0)
    ...
    "rrow: 0" are "empty" blocks.

    Alexander Anokhin
    http://alexanderanokhin.wordpress.com

    Edited by: Alexander Anokhin on 18.11.2012 22:41
  • 13. Re: Too much Logical IOs on a locally partition index !!
    872581 Newbie
    Currently Being Moderated
    Thanks for your reply.

    The tablespace is built with segment space management AUTO , as you said.

    And there is no consistent-read on this table,(I mean CR copies from undo) because it is on a development environment and there are no other active sessions in it.

    -----------------------

    The initial value of proc_rslt is 1 when it is inserted.

    And then, proc_rslt is updated in the following process, based on the business process.

    (1 -> 0) or
    (1 -> 99 -> 0) or
    (1 -> 99 -> 59 -> 0) or
    ...

    Initially ,proc_rslt is 1 and finally proc_rslt is 0.

    -- ************************************************************
    
     
    IN_BPMDNPMTACT_DX1 index column order (PROC_RSLT, SEQ, TOP_SA_ID ,IA_ID)
    
    PROC_RSLT --> NUMBER(2) 
    SEQ --> NUMBER(19) 
    TOP_SA_ID --> VARCHAR2(11 BYTE) 
    IA_ID --> VARCHAR2(11 BYTE) 
     
    -- ************************************************************
    
    select /*+ full(t) parallel(t 16) */proc_rslt,count(*)
     from hoho.in_bpmdnpmtact t
     group by proc_rslt
     order by proc_rslt
     
     PROC_RSLT   COUNT(*)
    ---------- ----------
             0   23583117
             2     181680
            56     239514
            58          1
            59    4381933
            96     964810
            98          9
            99    1532540
    
    8 rows selected.
    
    -- ************************************************************
    Thanks in advance.
    Best Regards.
  • 14. Re: Too much Logical IOs on a locally partition index !!
    872581 Newbie
    Currently Being Moderated
    Thanks for your reply.

    The following is the result of analyze index ... validate structure.
    HEIGHT     BLOCKS     NAME     PARTITION_NAME     LF_ROWS     LF_BLKS     BR_ROWS     BR_BLKS     DEL_LF_ROWS
    4     182424     IN_BPMDNPMTACT_DX1     P201208     21655066     173118     173117     592     1790326
    3     28304     IN_BPMDNPMTACT_DX1     P201209     4019084     27256     27255     73     0
    3     29304     IN_BPMDNPMTACT_DX1     P201210     4186481     28465     28464     74     0
    3     32704     IN_BPMDNPMTACT_DX1     P201211     2813299     32019     32018     121     0
     
    Thanks in advance.
    Best Regards.
1 2 Previous Next

Legend

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