1 2 Previous Next 20 Replies Latest reply on Mar 5, 2018 11:55 AM by Stefan Jager

    Indexes gone bad

    Ginola

      Hi,

       

      We have a couple of tables that are quite large (~200million rows), and each night we do delete and inserts of about 500k rows.

      And after each insert we do rebuild index on all indexes on the table.

       

      A lot of our batch jobs then run against these tables.

      But for a couple of days now, the CBO has started doing a weird behaviour. On queries it used to do FTS, it now uses indexes and vice versa. Some of the indexes are obviously not that good to use since we are experiencing exceptional bad performance.

      What can have happened? Could the table gone bad? And the indexes then look good for the CBO?

       

      Thanks in advance!

        • 1. Re: Indexes gone bad
          AndrewSayer

          2906705 wrote:

           

          Hi,

           

          We have a couple of tables that are quite large (~200million rows), and each night we do delete and inserts of about 500k rows.

          And after each insert we do rebuild index on all indexes on the table.

           

          A lot of our batch jobs then run against these tables.

          But for a couple of days now, the CBO has started doing a weird behaviour. On queries it used to do FTS, it now uses indexes and vice versa. Some of the indexes are obviously not that good to use since we are experiencing exceptional bad performance.

          What can have happened? Could the table gone bad? And the indexes then look good for the CBO?

           

          Thanks in advance!

          Rebuilds on a schedule? Yuck! Why do you replace half a million rows each night?

           

          When you build an index you gather stats in it, you do not gather stats on the table at the same time, it’s possible your index has stats that are much bigger in respect to the table stats.

           

          It would be helpful if you shared a simple query that you have seen change plan, include the full execution plan, index definition, old execution plan and the execution plan if you hint for it to use the index (Or vice versa).

           

          Tables going bad? What do you specifically mean by that? If youre Deleting and then appending lots of data constantly then I’d expect the table to be very space inefficient, you’ll have room from all the deleted rows that doesn’t ever get reused. This would make a full scan cost a lot more than you’d expect, but you’d need to have stats gathered on the table (which could happen automatically), and it implies you are using insert append, which you didn’t mention.

           

          Rather than work through a list of guesses and possible reasons, we should take the deterministic approach, this means we start with exactly what is going on (your plans and queries will tell us this) and take it from there.

          • 2. Re: Indexes gone bad
            Ginola

            Hi Andrew!

             

            Thanks for fast response!

            I do not know the history why we do delete and insert instead of a merge. But I can look into that.

            We gather stats on all tables in the schema everyday as well, so they should look OK compared to the indexes.

             

            What I mean is that it could have gone space inefficient like you said. Is it someway we could fix that? Dropping the table and recreate it?

             

            Here is the explain plan for "bad" plan:

             

            Plan hash value: 3487480137

             

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

            | Id  | Operation                                  | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

            |   0 | SELECT STATEMENT                           |                               |       |       |       |   244K(100)|          |

            |   1 |  TABLE ACCESS BY INDEX ROWID               | ACCOUNT                       |     1 |    25 |       |     5  (20)| 00:00:01 |

            |   2 |   BITMAP CONVERSION TO ROWIDS              |                               |       |       |       |            |          |

            |   3 |    BITMAP AND                              |                               |       |       |       |            |          |

            |   4 |     BITMAP INDEX SINGLE VALUE              | IDX_ACCOUNT_OBJ_SOURCE        |       |       |       |            |          |

            |   5 |     BITMAP INDEX SINGLE VALUE              | IDX_ACCOUNT_OBJ_TYPE          |       |       |       |            |          |

            |   6 |     BITMAP CONVERSION FROM ROWIDS          |                               |       |       |       |            |          |

            |   7 |      SORT ORDER BY                         |                               |       |       |       |            |          |

            |   8 |       INDEX RANGE SCAN                     | UQ_ACCOUNT                    |     5 |       |       |     2   (0)| 00:00:01 |

            |   9 |   TABLE ACCESS BY INDEX ROWID              | ACCOUNT                       |     1 |    25 |       |     5  (20)| 00:00:01 |

            |  10 |    BITMAP CONVERSION TO ROWIDS             |                               |       |       |       |            |          |

            |  11 |     BITMAP AND                             |                               |       |       |       |            |          |

            |  12 |      BITMAP INDEX SINGLE VALUE             | IDX_ACCOUNT_OBJ_SOURCE        |       |       |       |            |          |

            |  13 |      BITMAP INDEX SINGLE VALUE             | IDX_ACCOUNT_OBJ_TYPE          |       |       |       |            |          |

            |  14 |      BITMAP CONVERSION FROM ROWIDS         |                               |       |       |       |            |          |

            |  15 |       SORT ORDER BY                        |                               |       |       |       |            |          |

            |  16 |        INDEX RANGE SCAN                    | UQ_ACCOUNT                    |     5 |       |       |     2   (0)| 00:00:01 |

            |  17 |  HASH JOIN RIGHT OUTER                     |                               | 93634 |    28M|       |   244K  (1)| 00:49:00 |

            |  18 |   VIEW                                     | X_SECURITY_ACCOUNTS_V         |   434 | 12152 |       |   368   (2)| 00:00:05 |

            |  19 |    HASH GROUP BY                           |                               |   434 |  7812 |       |   368   (2)| 00:00:05 |

            |  20 |     VIEW                                   |                               |   434 |  7812 |       |   367   (2)| 00:00:05 |

            |  21 |      SORT UNIQUE                           |                               |   434 |   101K|       |   367   (2)| 00:00:05 |

            |  22 |       UNION-ALL                            |                               |       |       |       |            |          |

            |  23 |        CONNECT BY WITH FILTERING (UNIQUE)  |                               |       |       |       |            |          |

            |  24 |         CONCATENATION                      |                               |       |       |       |            |          |

            |  25 |          INLIST ITERATOR                   |                               |       |       |       |            |          |

            |  26 |           TABLE ACCESS BY INDEX ROWID      | ACCOUNT                       |     1 |    69 |       |     7   (0)| 00:00:01 |

            |  27 |            INDEX RANGE SCAN                | UQ_ACCOUNT                    |     1 |       |       |     6   (0)| 00:00:01 |

            |  28 |          INLIST ITERATOR                   |                               |       |       |       |            |          |

            |  29 |           TABLE ACCESS BY INDEX ROWID      | ACCOUNT                       |     1 |    69 |       |     7   (0)| 00:00:01 |

            |  30 |            INDEX RANGE SCAN                | UQ_ACCOUNT                    |     1 |       |       |     6   (0)| 00:00:01 |

            |  31 |         NESTED LOOPS                       |                               |    36 |  4752 |       |    34   (0)| 00:00:01 |

            |  32 |          CONNECT BY PUMP                   |                               |       |       |       |            |          |

            |  33 |          TABLE ACCESS BY INDEX ROWID       | ACCOUNT                       |    18 |  1368 |       |    10   (0)| 00:00:01 |

            |  34 |           INDEX RANGE SCAN                 | IDX_ACCOUNT_PARENT            |    18 |       |       |     2   (0)| 00:00:01 |

            |  35 |        CONNECT BY WITH FILTERING (UNIQUE)  |                               |       |       |       |            |          |

            |  36 |         CONCATENATION                      |                               |       |       |       |            |          |

            |  37 |          TABLE ACCESS BY INDEX ROWID       | ACCOUNT                       |     1 |    69 |       |     4   (0)| 00:00:01 |

            |  38 |           INDEX RANGE SCAN                 | IDX_ACCOUNT_PARENT            |     1 |       |       |     3   (0)| 00:00:01 |

            |  39 |          INLIST ITERATOR                   |                               |       |       |       |            |          |

            |  40 |           TABLE ACCESS BY INDEX ROWID      | ACCOUNT                       |    20 |  1380 |       |    47   (0)| 00:00:01 |

            |  41 |            INDEX RANGE SCAN                | IDX_ACCOUNT_PARENT            |    20 |       |       |    39   (0)| 00:00:01 |

            |  42 |         NESTED LOOPS                       |                               |   374 | 49368 |       |   261   (0)| 00:00:04 |

            |  43 |          CONNECT BY PUMP                   |                               |       |       |       |            |          |

            |  44 |          TABLE ACCESS BY INDEX ROWID       | ACCOUNT                       |    18 |  1368 |       |    10   (0)| 00:00:01 |

            |  45 |           INDEX RANGE SCAN                 | IDX_ACCOUNT_PARENT            |    18 |       |       |     2   (0)| 00:00:01 |

            |  46 |        TABLE ACCESS BY INDEX ROWID         | ACCOUNT                       |     1 |    26 |       |     4   (0)| 00:00:01 |

            |  47 |         INDEX RANGE SCAN                   | IDX_ACCOUNT_PARENT            |     1 |       |       |     3   (0)| 00:00:01 |

            |  48 |   HASH JOIN RIGHT OUTER                    |                               | 52211 |    14M|       |   244K  (1)| 00:48:55 |

            |  49 |    VIEW                                    | X_SECURITY_OBJECTS_V          |  1976 | 55328 |       |  3303   (1)| 00:00:40 |

            |  50 |     HASH GROUP BY                          |                               |  1976 | 35568 |       |  3303   (1)| 00:00:40 |

            |  51 |      VIEW                                  |                               |  1976 | 35568 |       |  3302   (1)| 00:00:40 |

            |  52 |       SORT UNIQUE                          |                               |  1976 |   482K|       |  3302   (1)| 00:00:40 |

            |  53 |        UNION-ALL                           |                               |       |       |       |            |          |

            |  54 |         CONNECT BY WITH FILTERING (UNIQUE) |                               |       |       |       |            |          |

            |  55 |          TABLE ACCESS FULL                 | ORGANIZATION                  |  1965 |   132K|       |   551   (2)| 00:00:07 |

            |  56 |          HASH JOIN                         |                               |     5 |   675 |       |  1099   (1)| 00:00:14 |

            |  57 |           CONNECT BY PUMP                  |                               |       |       |       |            |          |

            |  58 |           TABLE ACCESS FULL                | ORGANIZATION                  | 76990 |  5939K|       |   547   (1)| 00:00:07 |

            |  59 |         FILTER                             |                               |       |       |       |            |          |

            |  60 |          CONNECT BY WITH FILTERING (UNIQUE)|                               |       |       |       |            |          |

            |  61 |           TABLE ACCESS FULL                | ORGANIZATION                  |     4 |   272 |       |   548   (1)| 00:00:07 |

            |  62 |           HASH JOIN                        |                               |     1 |   124 |       |  1096   (1)| 00:00:14 |

            |  63 |            CONNECT BY PUMP                 |                               |       |       |       |            |          |

            |  64 |            TABLE ACCESS FULL               | ORGANIZATION                  | 76990 |  5112K|       |   548   (1)| 00:00:07 |

            |  65 |         INLIST ITERATOR                    |                               |       |       |       |            |          |

            |  66 |          TABLE ACCESS BY INDEX ROWID       | ORGANIZATION                  |     1 |    37 |       |     5   (0)| 00:00:01 |

            |  67 |           INDEX RANGE SCAN                 | UQ_ORGANIZATION_NATURAL       |     1 |       |       |     4   (0)| 00:00:01 |

            |  68 |    HASH JOIN                               |                               | 52211 |    12M|       |   241K  (1)| 00:48:16 |

            |  69 |     TABLE ACCESS FULL                      | CURRENCY                      |    40 |  2560 |       |     3   (0)| 00:00:01 |

            |  70 |     HASH JOIN                              |                               | 52211 |  9993K|  3072K|   241K  (1)| 00:48:16 |

            |  71 |      TABLE ACCESS FULL                     | ACCOUNT                       | 45570 |  2536K|       |   274   (1)| 00:00:04 |

            |  72 |      HASH JOIN                             |                               | 52211 |  7087K|       |   240K  (1)| 00:48:06 |

            |  73 |       TABLE ACCESS FULL                    | COMPANY                       |   112 |  1008 |       |     3   (0)| 00:00:01 |

            |  74 |       HASH JOIN RIGHT OUTER                |                               | 52211 |  6628K|       |   240K  (1)| 00:48:06 |

            |  75 |        VIEW                                |                               |    19 |   247 |       |    18  (12)| 00:00:01 |

            |  76 |         FILTER                             |                               |       |       |       |            |          |

            |  77 |          CONNECT BY WITH FILTERING         |                               |       |       |       |            |          |

            |  78 |           TABLE ACCESS BY INDEX ROWID      | ACCOUNT                       |     1 |    69 |       |     3   (0)| 00:00:01 |

            |  79 |            INDEX RANGE SCAN                | UQ_ACCOUNT                    |     1 |       |       |     2   (0)| 00:00:01 |

            |  80 |           NESTED LOOPS                     |                               |    18 |  2376 |       |    13   (0)| 00:00:01 |

            |  81 |            CONNECT BY PUMP                 |                               |       |       |       |            |          |

            |  82 |            TABLE ACCESS BY INDEX ROWID     | ACCOUNT                       |    18 |  1368 |       |    10   (0)| 00:00:01 |

            |  83 |             INDEX RANGE SCAN               | IDX_ACCOUNT_PARENT            |    18 |       |       |     2   (0)| 00:00:01 |

            |  84 |        HASH JOIN RIGHT OUTER               |                               | 52211 |  5965K|       |   240K  (1)| 00:48:06 |

            |  85 |         VIEW                               |                               |     2 |    26 |       |    30   (7)| 00:00:01 |

            |  86 |          FILTER                            |                               |       |       |       |            |          |

            |  87 |           CONNECT BY WITH FILTERING        |                               |       |       |       |            |          |

            |  88 |            TABLE ACCESS BY INDEX ROWID     | ORGANIZATION                  |     1 |    69 |       |    12   (0)| 00:00:01 |

            |  89 |             BITMAP CONVERSION TO ROWIDS    |                               |       |       |       |            |          |

            |  90 |              BITMAP AND                    |                               |       |       |       |            |          |

            |  91 |               BITMAP INDEX SINGLE VALUE    | IDX_ORGANIZATION_OBJ_TYPE     |       |       |       |            |          |

            |  92 |               BITMAP OR                    |                               |       |       |       |            |          |

            |  93 |                BITMAP INDEX SINGLE VALUE   | IDX_ORGANIZATION_OBJ_COMPANY  |       |       |       |            |          |

            |  94 |                BITMAP INDEX SINGLE VALUE   | IDX_ORGANIZATION_OBJ_COMPANY  |       |       |       |            |          |

            |  95 |                BITMAP INDEX SINGLE VALUE   | IDX_ORGANIZATION_OBJ_COMPANY  |       |       |       |            |          |

            |  96 |                BITMAP INDEX SINGLE VALUE   | IDX_ORGANIZATION_OBJ_COMPANY  |       |       |       |            |          |

            |  97 |                BITMAP INDEX SINGLE VALUE   | IDX_ORGANIZATION_OBJ_COMPANY  |       |       |       |            |          |

            |  98 |            NESTED LOOPS                    |                               |     1 |   135 |       |    15   (0)| 00:00:01 |

            |  99 |             CONNECT BY PUMP                |                               |       |       |       |            |          |

            | 100 |             TABLE ACCESS BY INDEX ROWID    | ORGANIZATION                  |     1 |    79 |       |     3   (0)| 00:00:01 |

            | 101 |              INDEX RANGE SCAN              | IDX_ORGANIZATION_PARENT       |     1 |       |       |     2   (0)| 00:00:01 |

            | 102 |         HASH JOIN                          |                               | 52211 |  5302K|       |   240K  (1)| 00:48:06 |

            | 103 |          TABLE ACCESS FULL                 | TIME                          |     9 |   171 |       |     3   (0)| 00:00:01 |

            | 104 |          NESTED LOOPS                      |                               |   634K|    51M|       |   240K  (1)| 00:48:06 |

            | 105 |           NESTED LOOPS                     |                               |   634K|    51M|       |   240K  (1)| 00:48:06 |

            | 106 |            NESTED LOOPS                    |                               |  5756 |   185K|       |   552   (1)| 00:00:07 |

            | 107 |             FAST DUAL                      |                               |     1 |       |       |     2   (0)| 00:00:01 |

            | 108 |             TABLE ACCESS FULL              | ORGANIZATION                  |  5756 |   185K|       |   550   (1)| 00:00:07 |

            | 109 |            INDEX RANGE SCAN                | IDX_FACT_PLAN_SUPP_K_COMP_ORG |    65 |       |       |    19   (0)| 00:00:01 |

            | 110 |           TABLE ACCESS BY INDEX ROWID      | FACT_PLAN_SUPP                |   110 |  5720 |       |    82   (0)| 00:00:01 |

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

             

             

            Here is the "old" better one:

             

            Plan hash value: 631574348

             

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

            | Id  | Operation                                  | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

            |   0 | SELECT STATEMENT                           |                              |       |       |       |   545K(100)|          |

            |   1 |  TABLE ACCESS BY INDEX ROWID               | ACCOUNT                      |     1 |    25 |       |     5  (20)| 00:00:01 |

            |   2 |   BITMAP CONVERSION TO ROWIDS              |                              |       |       |       |            |          |

            |   3 |    BITMAP AND                              |                              |       |       |       |            |          |

            |   4 |     BITMAP INDEX SINGLE VALUE              | IDX_ACCOUNT_OBJ_SOURCE       |       |       |       |            |          |

            |   5 |     BITMAP INDEX SINGLE VALUE              | IDX_ACCOUNT_OBJ_TYPE         |       |       |       |            |          |

            |   6 |     BITMAP CONVERSION FROM ROWIDS          |                              |       |       |       |            |          |

            |   7 |      SORT ORDER BY                         |                              |       |       |       |            |          |

            |   8 |       INDEX RANGE SCAN                     | UQ_ACCOUNT                   |     5 |       |       |     2   (0)| 00:00:01 |

            |   9 |   TABLE ACCESS BY INDEX ROWID              | ACCOUNT                      |     1 |    25 |       |     5  (20)| 00:00:01 |

            |  10 |    BITMAP CONVERSION TO ROWIDS             |                              |       |       |       |            |          |

            |  11 |     BITMAP AND                             |                              |       |       |       |            |          |

            |  12 |      BITMAP INDEX SINGLE VALUE             | IDX_ACCOUNT_OBJ_SOURCE       |       |       |       |            |          |

            |  13 |      BITMAP INDEX SINGLE VALUE             | IDX_ACCOUNT_OBJ_TYPE         |       |       |       |            |          |

            |  14 |      BITMAP CONVERSION FROM ROWIDS         |                              |       |       |       |            |          |

            |  15 |       SORT ORDER BY                        |                              |       |       |       |            |          |

            |  16 |        INDEX RANGE SCAN                    | UQ_ACCOUNT                   |     5 |       |       |     2   (0)| 00:00:01 |

            |  17 |  HASH JOIN RIGHT OUTER                     |                              |   539K|   162M|       |   545K  (2)| 01:49:03 |

            |  18 |   VIEW                                     | X_SECURITY_ACCOUNTS_V        |   434 | 12152 |       |   368   (2)| 00:00:05 |

            |  19 |    HASH GROUP BY                           |                              |   434 |  7812 |       |   368   (2)| 00:00:05 |

            |  20 |     VIEW                                   |                              |   434 |  7812 |       |   367   (1)| 00:00:05 |

            |  21 |      SORT UNIQUE                           |                              |   434 |   101K|       |   367   (1)| 00:00:05 |

            |  22 |       UNION-ALL                            |                              |       |       |       |            |          |

            |  23 |        CONNECT BY WITH FILTERING (UNIQUE)  |                              |       |       |       |            |          |

            |  24 |         CONCATENATION                      |                              |       |       |       |            |          |

            |  25 |          INLIST ITERATOR                   |                              |       |       |       |            |          |

            |  26 |           TABLE ACCESS BY INDEX ROWID      | ACCOUNT                      |     1 |    69 |       |     7   (0)| 00:00:01 |

            |  27 |            INDEX RANGE SCAN                | UQ_ACCOUNT                   |     1 |       |       |     6   (0)| 00:00:01 |

            |  28 |          INLIST ITERATOR                   |                              |       |       |       |            |          |

            |  29 |           TABLE ACCESS BY INDEX ROWID      | ACCOUNT                      |     1 |    69 |       |     7   (0)| 00:00:01 |

            |  30 |            INDEX RANGE SCAN                | UQ_ACCOUNT                   |     1 |       |       |     6   (0)| 00:00:01 |

            |  31 |         NESTED LOOPS                       |                              |    36 |  4752 |       |    34   (0)| 00:00:01 |

            |  32 |          CONNECT BY PUMP                   |                              |       |       |       |            |          |

            |  33 |          TABLE ACCESS BY INDEX ROWID       | ACCOUNT                      |    18 |  1368 |       |    10   (0)| 00:00:01 |

            |  34 |           INDEX RANGE SCAN                 | IDX_ACCOUNT_PARENT           |    18 |       |       |     2   (0)| 00:00:01 |

            |  35 |        CONNECT BY WITH FILTERING (UNIQUE)  |                              |       |       |       |            |          |

            |  36 |         CONCATENATION                      |                              |       |       |       |            |          |

            |  37 |          TABLE ACCESS BY INDEX ROWID       | ACCOUNT                      |     1 |    69 |       |     4   (0)| 00:00:01 |

            |  38 |           INDEX RANGE SCAN                 | IDX_ACCOUNT_PARENT           |     1 |       |       |     3   (0)| 00:00:01 |

            |  39 |          INLIST ITERATOR                   |                              |       |       |       |            |          |

            |  40 |           TABLE ACCESS BY INDEX ROWID      | ACCOUNT                      |    20 |  1380 |       |    47   (0)| 00:00:01 |

            |  41 |            INDEX RANGE SCAN                | IDX_ACCOUNT_PARENT           |    20 |       |       |    39   (0)| 00:00:01 |

            |  42 |         NESTED LOOPS                       |                              |   374 | 49368 |       |   261   (0)| 00:00:04 |

            |  43 |          CONNECT BY PUMP                   |                              |       |       |       |            |          |

            |  44 |          TABLE ACCESS BY INDEX ROWID       | ACCOUNT                      |    18 |  1368 |       |    10   (0)| 00:00:01 |

            |  45 |           INDEX RANGE SCAN                 | IDX_ACCOUNT_PARENT           |    18 |       |       |     2   (0)| 00:00:01 |

            |  46 |        TABLE ACCESS BY INDEX ROWID         | ACCOUNT                      |     1 |    26 |       |     4   (0)| 00:00:01 |

            |  47 |         INDEX RANGE SCAN                   | IDX_ACCOUNT_PARENT           |     1 |       |       |     3   (0)| 00:00:01 |

            |  48 |   HASH JOIN RIGHT OUTER                    |                              |   300K|    82M|       |   544K  (2)| 01:48:58 |

            |  49 |    VIEW                                    | X_SECURITY_OBJECTS_V         |  1920 | 53760 |       |  3303   (1)| 00:00:40 |

            |  50 |     HASH GROUP BY                          |                              |  1920 | 34560 |       |  3303   (1)| 00:00:40 |

            |  51 |      VIEW                                  |                              |  1920 | 34560 |       |  3302   (1)| 00:00:40 |

            |  52 |       SORT UNIQUE                          |                              |  1920 |   468K|       |  3302   (1)| 00:00:40 |

            |  53 |        UNION-ALL                           |                              |       |       |       |            |          |

            |  54 |         CONNECT BY WITH FILTERING (UNIQUE) |                              |       |       |       |            |          |

            |  55 |          TABLE ACCESS FULL                 | ORGANIZATION                 |  1909 |   128K|       |   551   (2)| 00:00:07 |

            |  56 |          HASH JOIN                         |                              |     5 |   675 |       |  1099   (1)| 00:00:14 |

            |  57 |           CONNECT BY PUMP                  |                              |       |       |       |            |          |

            |  58 |           TABLE ACCESS FULL                | ORGANIZATION                 | 76836 |  5927K|       |   547   (1)| 00:00:07 |

            |  59 |         FILTER                             |                              |       |       |       |            |          |

            |  60 |          CONNECT BY WITH FILTERING (UNIQUE)|                              |       |       |       |            |          |

            |  61 |           TABLE ACCESS FULL                | ORGANIZATION                 |     4 |   272 |       |   548   (1)| 00:00:07 |

            |  62 |           HASH JOIN                        |                              |     1 |   124 |       |  1096   (1)| 00:00:14 |

            |  63 |            CONNECT BY PUMP                 |                              |       |       |       |            |          |

            |  64 |            TABLE ACCESS FULL               | ORGANIZATION                 | 76836 |  5102K|       |   548   (1)| 00:00:07 |

            |  65 |         INLIST ITERATOR                    |                              |       |       |       |            |          |

            |  66 |          TABLE ACCESS BY INDEX ROWID       | ORGANIZATION                 |     1 |    37 |       |     5   (0)| 00:00:01 |

            |  67 |           INDEX RANGE SCAN                 | UQ_ORGANIZATION_NATURAL      |     1 |       |       |     4   (0)| 00:00:01 |

            |  68 |    HASH JOIN                               |                              |   300K|    74M|       |   541K  (2)| 01:48:18 |

            |  69 |     TABLE ACCESS FULL                      | CURRENCY                     |    40 |  2560 |       |     3   (0)| 00:00:01 |

            |  70 |     HASH JOIN RIGHT OUTER                  |                              |   300K|    56M|       |   541K  (2)| 01:48:18 |

            |  71 |      VIEW                                  |                              |    19 |   247 |       |    18  (12)| 00:00:01 |

            |  72 |       FILTER                               |                              |       |       |       |            |          |

            |  73 |        CONNECT BY WITH FILTERING           |                              |       |       |       |            |          |

            |  74 |         TABLE ACCESS BY INDEX ROWID        | ACCOUNT                      |     1 |    69 |       |     3   (0)| 00:00:01 |

            |  75 |          INDEX RANGE SCAN                  | UQ_ACCOUNT                   |     1 |       |       |     2   (0)| 00:00:01 |

            |  76 |         NESTED LOOPS                       |                              |    18 |  2376 |       |    13   (0)| 00:00:01 |

            |  77 |          CONNECT BY PUMP                   |                              |       |       |       |            |          |

            |  78 |          TABLE ACCESS BY INDEX ROWID       | ACCOUNT                      |    18 |  1368 |       |    10   (0)| 00:00:01 |

            |  79 |           INDEX RANGE SCAN                 | IDX_ACCOUNT_PARENT           |    18 |       |       |     2   (0)| 00:00:01 |

            |  80 |      HASH JOIN                             |                              |   300K|    52M|  3072K|   541K  (2)| 01:48:18 |

            |  81 |       TABLE ACCESS FULL                    | ACCOUNT                      | 45564 |  2536K|       |   274   (1)| 00:00:04 |

            |  82 |       HASH JOIN                            |                              |   300K|    36M|  2576K|   539K  (2)| 01:47:50 |

            |  83 |        TABLE ACCESS FULL                   | ORGANIZATION                 | 58577 |  1887K|       |   550   (1)| 00:00:07 |

            |  84 |        HASH JOIN                           |                              |  6984K|   619M|       |   503K  (2)| 01:40:44 |

            |  85 |         TABLE ACCESS FULL                  | COMPANY                      |   112 |  1008 |       |     3   (0)| 00:00:01 |

            |  86 |         HASH JOIN RIGHT OUTER              |                              |  6984K|   559M|       |   503K  (2)| 01:40:44 |

            |  87 |          VIEW                              |                              |     2 |    26 |       |    29   (7)| 00:00:01 |

            |  88 |           FILTER                           |                              |       |       |       |            |          |

            |  89 |            CONNECT BY WITH FILTERING       |                              |       |       |       |            |          |

            |  90 |             TABLE ACCESS BY INDEX ROWID    | ORGANIZATION                 |     1 |    69 |       |    12   (0)| 00:00:01 |

            |  91 |              BITMAP CONVERSION TO ROWIDS   |                              |       |       |       |            |          |

            |  92 |               BITMAP AND                   |                              |       |       |       |            |          |

            |  93 |                BITMAP INDEX SINGLE VALUE   | IDX_ORGANIZATION_OBJ_TYPE    |       |       |       |            |          |

            |  94 |                BITMAP OR                   |                              |       |       |       |            |          |

            |  95 |                 BITMAP INDEX SINGLE VALUE  | IDX_ORGANIZATION_OBJ_COMPANY |       |       |       |            |          |

            |  96 |                 BITMAP INDEX SINGLE VALUE  | IDX_ORGANIZATION_OBJ_COMPANY |       |       |       |            |          |

            |  97 |                 BITMAP INDEX SINGLE VALUE  | IDX_ORGANIZATION_OBJ_COMPANY |       |       |       |            |          |

            |  98 |                 BITMAP INDEX SINGLE VALUE  | IDX_ORGANIZATION_OBJ_COMPANY |       |       |       |            |          |

            |  99 |                 BITMAP INDEX SINGLE VALUE  | IDX_ORGANIZATION_OBJ_COMPANY |       |       |       |            |          |

            | 100 |             NESTED LOOPS                   |                              |     1 |   135 |       |    15   (0)| 00:00:01 |

            | 101 |              CONNECT BY PUMP               |                              |       |       |       |            |          |

            | 102 |              TABLE ACCESS BY INDEX ROWID   | ORGANIZATION                 |     1 |    79 |       |     3   (0)| 00:00:01 |

            | 103 |               INDEX RANGE SCAN             | IDX_ORGANIZATION_PARENT      |     1 |       |       |     2   (0)| 00:00:01 |

            | 104 |          HASH JOIN                         |                              |  6984K|   472M|       |   503K  (2)| 01:40:43 |

            | 105 |           NESTED LOOPS                     |                              |     9 |   171 |       |     5   (0)| 00:00:01 |

            | 106 |            FAST DUAL                       |                              |     1 |       |       |     2   (0)| 00:00:01 |

            | 107 |            TABLE ACCESS FULL               | TIME                         |     9 |   171 |       |     3   (0)| 00:00:01 |

            | 108 |           TABLE ACCESS FULL                | FACT_PLAN_SUPP               |    84M|  4207M|       |   503K  (2)| 01:40:37 |

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

            • 3. Re: Indexes gone bad
              William Robertson

              Is partitioning an option? If so, you might be able to define the partitions to match the batch (e.g. partition by business date) and then you can truncate and reload a single partition, or even use partition exchange.

               

              Just to rule it out, are you loading the data via direct path (insert /*+ append */ or sqlldr with direct = true), or just a plain insert?

              • 4. Re: Indexes gone bad
                Ginola

                Maybe it is. I need to investigate how the delete and insert look like in more detail. It sometimes depends on what parameters that are put in.

                 

                Plain insert. How does the "append" hint work?

                • 5. Re: Indexes gone bad
                  AndrewSayer

                  2906705 wrote:

                   

                  Maybe it is. I need to investigate how the delete and insert look like in more detail. It sometimes depends on what parameters that are put in.

                   

                  Plain insert. How does the "append" hint work?

                  Append hint means that instead of looking for the free space within the blocks that the table already has allocated, allocate some new extents for this data. If you are gathering statistics every day and you are appending your inserts then your stats will know your table is huge when really there's not much data - indexes would be loved and full table scans would be hated. You wouldn't fix this by dropping and recreating the table (and if you can do that, it suggests your data exists elsewhere?), you can just do an alter table move.. , then rebuild ALL the indexes on the table as they would be unusable.

                   

                  What is the result of

                  select * from dba_Tables where table_name = '<YOUR TABLE NAME>';

                  desc <your table name>

                  And in comparison to the datatype lengths, how large do you think a typical row would be (in bytes/chars)

                  We can use this to determine what size the table is in comparison to the data it is holding.

                   

                  Do you have a simpler query we could look at, the plan you posted is pretty long and will have many variables that could be in play? It'll take some patience and caffeine to read through it otherwise (I'm not saying I am strictly opposed to that, but the former would be much easier) . Also, don't forget to share the plans using a fixed width font, in the advanced editor you can chose courier new for this.

                  • 6. Re: Indexes gone bad

                    We have a couple of tables that are quite large (~200million rows), and each night we do delete and inserts of about 500k rows.

                    Deletes of WHAT rows? Inserts of WHAT rows? Are you replacing rows you just deleted?

                     

                    SHOW US the queries/statements you are using to do the deletes and inserts.

                     

                    And after each insert we do rebuild index on all indexes on the table.

                    Why? Oracle automatically updates the indexes for deletes and inserts.

                    Some of the indexes are obviously not that good to use since we are experiencing exceptional bad performance.

                    What is that statement based on?

                     

                    It is hardly ever 'obvious' that an index is not good. Oracle wouldn't be using them if it didn't think they were good.

                     

                    Don't guess. When you suspect a problem, performance or otherwise, follow the STANDARD troubleshooting steps:

                     

                    1. identify a problem/issue

                    2. confirm the problem/issue really exists

                    3. determine the cause of the problem/issue

                    4. identify possible solutions that will eliminate/mitigate the problem/issue

                    5. select a small number (2 or 3) solutions for prototyping and testing

                    6. select the 'best' solution

                     

                    Although you posted plans you haven't posted the actual queries.

                     

                    It also appears you have bitmapped indexes on the tables.

                    • 7. Re: Indexes gone bad
                      John Thorton

                      which metric at what value indicates when index goes from Good to Bad?

                      How to measure the difference between a Good index & a Bad index?

                       

                      What are specific symptom for when "table goes bad"?

                       

                      Quantify how a bad table is different from good table.

                      • 8. Re: Indexes gone bad
                        Ginola
                        Deletes of WHAT rows? Inserts of WHAT rows? Are you replacing rows you just deleted?

                         

                        We do a lot of calculations and aggregations every night, and we delete the data that are in scope for calculations and then insert. So yes, it is rows we just deleted.

                         

                         

                        SHOW US the queries/statements you are using to do the deletes and inserts.

                         

                        Why is that important? The inserts are through dynamic sql, creating lots of different queries in one batch run.

                         

                        And after each insert we do rebuild index on all indexes on the table.

                        Why? Oracle automatically updates the indexes for deletes and inserts.

                        It speeds up the deletion and insert, so we toggle them.

                         

                         

                        The queries that do the insert, are a lot of views on views. I know that the CBO have a higher risk of getting lost, but i'm not looking at redesigning the whole batchflow at this point.

                        This have run with same performance for many years.

                         

                         

                        It also appears you have bitmapped indexes on the tables.

                         

                        Is this an issue? What do you mean?

                        • 9. Re: Indexes gone bad
                          Ginola

                          John Thorton wrote:

                           

                          which metric at what value indicates when index goes from Good to Bad?

                          How to measure the difference between a Good index & a Bad index?

                           

                          What are specific symptom for when "table goes bad"?

                           

                          Quantify how a bad table is different from good table.

                          Hi John,

                           

                          I'm discussing from a performance view. Our big 200 million table and index has not been an issue before. But now a query that before took 5-15 minutes to run, now takes 4 hours. And we have not done any changes to code.

                          All the changes are from delete/insert on that table.

                           

                          What I mean from "bad" is that it does not give answer on our queries as fast as it did before.

                          • 10. Re: Indexes gone bad
                            John Thorton

                            2906705 wrote:

                             

                            John Thorton wrote:

                             

                            which metric at what value indicates when index goes from Good to Bad?

                            How to measure the difference between a Good index & a Bad index?

                             

                            What are specific symptom for when "table goes bad"?

                             

                            Quantify how a bad table is different from good table.

                            Hi John,

                             

                            I'm discussing from a performance view. Our big 200 million table and index has not been an issue before. But now a query that before took 5-15 minutes to run, now takes 4 hours. And we have not done any changes to code.

                            All the changes are from delete/insert on that table.

                             

                            What I mean from "bad" is that it does not give answer on our queries as fast as it did before.

                            Does query use Bind Variable(s)?

                             

                            How many rows deleted?

                            How many rows inserted?

                             

                            Are statistics current & valid?

                            • 11. Re: Indexes gone bad
                              Ginola
                              Append hint means that instead of looking for the free space within the blocks that the table already has allocated, allocate some new extents for this data. If you are gathering statistics every day and you are appending your inserts then your stats will know your table is huge when really there's not much data - indexes would be loved and full table scans would be hated. You wouldn't fix this by dropping and recreating the table (and if you can do that, it suggests your data exists elsewhere?), you can just do an alter table move.. , then rebuild ALL the indexes on the table as they would be unusable.

                              What I meant was doing an exp/imp of the table. We do not store the data somewhere else, sorry for not being clear.

                               

                              What is the result of

                              select * from dba_Tables where table_name = '<YOUR TABLE NAME>';

                              desc <your table name>

                              And in comparison to the datatype lengths, how large do you think a typical row would be (in bytes/chars)

                              We can use this to determine what size the table is in comparison to the data it is holding.

                              It should hold about 60-70 bytes per row.  The columns are mostly "number", can I say one number is one byte?

                               

                              Do you have a simpler query we could look at, the plan you posted is pretty long and will have many variables that could be in play? It'll take some patience and caffeine to read through it otherwise (I'm not saying I am strictly opposed to that, but the former would be much easier) . Also, don't forget to share the plans using a fixed width font, in the advanced editor you can chose courier new for this.

                              Im sorry, most of the batch queries are quite long. And they have a lot of views on views which make the plan look even more "spaghetti". Would that code make any sense to look at, when its views on views?

                              Thanks for the tip on width font!

                              • 12. Re: Indexes gone bad
                                Ginola

                                Does query use Bind Variable(s)?

                                What does that mean? Sorry, but as you can see Im a "level 1".. so this is also some learning for me

                                 

                                How many rows deleted?

                                Appx 500k.

                                 

                                How many rows inserted?

                                Appx. 500k.

                                 

                                The inserts are in high degree replacing the deleted rows. Just with new calculated data in some columns.

                                 

                                Are statistics current & valid?

                                Yes they should be. We gather stats on table everyday and rebuild index after each batchrun

                                • 13. Re: Indexes gone bad

                                  Why is that important? The inserts are through dynamic sql, creating lots of different queries in one batch run.

                                  Seriously? You don't understand why it is important to see the actual query that has a 'claimed' performance problem in order to know how to address the performance problem?

                                   

                                  I find that hard to believe.

                                   

                                  1. Your queries could be pure crappola.

                                  2. You said you are using dynamic sql which means your queries probably ARE pure crappola

                                  3. Many dynamic queries won't use bind variables and not using bind variables is a leading cause of performance problems.

                                  4. Your query could be doing 'slow by slow', row by row, inserts - another guaranteed cause of performance problems.

                                  5. Dynamic queries are often not tuned properly and often don't/can't use statistics - another guaranteed cause of performance problems.

                                  The queries that do the insert, are a lot of views on views. I know that the CBO have a higher risk of getting lost,

                                  The thing with the 'higher risk of getting lost' is the developer - not the CBO.

                                  but i'm not looking at redesigning the whole batchflow at this point.

                                  What you should be 'looking at' is finding the cause of the problem. Only then should you start looking for 'solutions'.

                                  This have run with same performance for many years.

                                  Then as John Thornton always correctly says - if performance changes it is ONLY because something else has changed.

                                   

                                  Your task, if you choose to accept it, is to determine what changed.

                                   

                                  Missing or wrong statistics are a common cause of execution plan changes.

                                   

                                  It also appears you have bitmapped indexes on the tables.

                                   

                                  Is this an issue? What do you mean?

                                  It isn't an issue since you haven't said you actually have any even though I brought it up.

                                  Are statistics current & valid?

                                  Yes they should be. We gather stats on table everyday and rebuild index after each batchrun

                                  Let me try to expound on the concept of using the forums to get help.

                                   

                                  When someone (John for instance) asks if the stats are current and valid that is YOUR CUE to actually provide DETAILED INFO about:

                                   

                                  1. WHAT stats you collect - what tables/indexes you collect them on

                                  2. HOW you collect them - what parameters are used

                                  3. WHEN you collect them - including relative to when the deletes and inserts are done.

                                   

                                  You didn't provide ANY of that info. All you said was you 'gather stats on table everyday'.

                                   

                                  The table you delete from and insert to may not even be causing the performance problem. The problem could be all of those 'views on views'. If those stats aren't correct it can cause your problem.

                                   

                                  We are trying to help you find the cause of the performance problem. We can't do that if you don't provide the info we need.

                                   

                                  You can find that info by reading the FAQ about how to post a tuning request - it includes the info needed to help you.

                                  • 14. Re: Indexes gone bad
                                    AndrewSayer

                                    2906705 wrote:

                                     

                                    Append hint means that instead of looking for the free space within the blocks that the table already has allocated, allocate some new extents for this data. If you are gathering statistics every day and you are appending your inserts then your stats will know your table is huge when really there's not much data - indexes would be loved and full table scans would be hated. You wouldn't fix this by dropping and recreating the table (and if you can do that, it suggests your data exists elsewhere?), you can just do an alter table move.. , then rebuild ALL the indexes on the table as they would be unusable.

                                    What I meant was doing an exp/imp of the table. We do not store the data somewhere else, sorry for not being clear.

                                     

                                    What is the result of

                                    select * from dba_Tables where table_name = '<YOUR TABLE NAME>';

                                    desc <your table name>

                                    And in comparison to the datatype lengths, how large do you think a typical row would be (in bytes/chars)

                                    We can use this to determine what size the table is in comparison to the data it is holding.

                                    It should hold about 60-70 bytes per row. The columns are mostly "number", can I say one number is one byte?

                                     

                                    Do you have a simpler query we could look at, the plan you posted is pretty long and will have many variables that could be in play? It'll take some patience and caffeine to read through it otherwise (I'm not saying I am strictly opposed to that, but the former would be much easier) . Also, don't forget to share the plans using a fixed width font, in the advanced editor you can chose courier new for this.

                                    Im sorry, most of the batch queries are quite long. And they have a lot of views on views which make the plan look even more "spaghetti". Would that code make any sense to look at, when its views on views?

                                    Thanks for the tip on width font!

                                    An exp/imp would reload the table in a potentially completely different order, it is not a good idea as it throws further variables in the mix - your clustering factors will change (for the worse usually) and be unrepresentative of future loads.

                                    Please copy and paste the results of the dba_tables query here, you’ve told us what you expect the values to be like, if they’re far from the truth then we have something we can look into. BTW have you discovered how this daily insert works - does it use append?

                                     

                                    If that’s the query we have to work with then fine, but could you go back and complete the missing info: there’s no predicates section, this is hugely important if we’re going to make sense of the plan. Don’t forget to include the notes section if one exists.

                                    Yes, the sql will make sense but you’ll have to include your view definitions too, please do.

                                    1 2 Previous Next