Forum Stats

  • 3,839,099 Users
  • 2,262,455 Discussions
  • 7,900,863 Comments

Discussions

Indexes gone bad

2»

Answers

  • Ginola
    Ginola Member Posts: 18 Red Ribbon
    edited Mar 4, 2018 2:40PM
    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 ofselect * 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!

  • Ginola
    Ginola Member Posts: 18 Red Ribbon
    edited Mar 4, 2018 2:43PM
    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

  • Unknown
    edited Mar 4, 2018 8:34PM
    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.

    John Thorton
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Mar 5, 2018 2:08AM
    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 ofselect * 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.

  • Moshe K
    Moshe K Member Posts: 3
    edited Mar 5, 2018 2:46AM

    Hi Dear,

    More suggestion or Provided on Index our Friends.

    Here i am providing some more point, i am expecting it may help you.

    •       Before inserting into table must have table statistics[gather statistics]
    •      As per your plain table info, it is hitting on Hash join; So better change the table positions [mean use small table and compare with large table]
    •      As per the observation almost all indexes are Bit map indexes. daily your inserting records and deleting records so i am expecting table have transaction information. when you have transaction info then use functional based index.
    •      Index scanning on Range wise, have a look on partition.
    • i have not find any composite index, so no matter on composite index.
    • if multiple user are using same table, use parallel hint.
    • you can also use index hint in statement.
    • you can use order of join hint in statement.
    • how you are inserting records, mean fetching from how many tables used any temporary table inside, if not use with clause and fetch the records. if chance use bulk collection .
    • used views - why using view how  its populating data and what purpose[recommended to use temporary tables]. 
    • check the table space. if chance use different tables space for index.

    Thanks in advance

    Regards

    Moshe.K

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond
    edited Mar 5, 2018 7:09AM

    Which version of Oracle ?

    How did you generate these execution plans ? It looks as if they came from the AWR - given the absence of any predicate markers (*) on the lines of the plan. At some point you need to pull a plan from memory with the complete predicate section if you want better information about what Oracle is doing.

    Checking the two plans, there's a huge overlap in straetgy, but one easily spotted significant difference:

    At line 108 of the bad plan you have a full tablescan of Organization returning an estimated 5,756 rows.

    At line 83 of the good plan a full tablescan of Organization has an estimate of 58577 rows.

    It may be this difference in estimates that has resulted in Oracle re-arranging the order of some of the query blocks and drive a nested loop from organization.

    The scale of the change in estimate suggests that there may be a histogram on some column(s) in the predicate(s) used against Organization, and as time passes Oracle default histograms can just get unlucky and miss an important popular value.

    PLEASE NOTE - just because there's an obvious point of difference between the two plans that doesn't mean it's the source of the difference in performance. It's possible that the difference in estimates is a result of different predicates being usable because the table access order has changed.  In part that's why it's so important to see the predicate detail from the actual run-time plan.

    Regards

    Jonathan Lewis

    UDPATE:  added "good" to the reference to line 83 of the plan.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond
    edited Mar 5, 2018 2:53AM

    For the benefit of anyone else who might want to read the plans, I've copied your text, started a reply, switched to the "full" editor, pasted the text, highlighted it and selected Courier New as the font. With a little luck it will survive publication.

    Bad Plan

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

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

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

    Regards

    Jonathan Lewis

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Mar 5, 2018 3:05AM
    Moshe K wrote:Hi Dear,More suggestion or Provided on Index our Friends.Here i am providing some more point, i am expecting it may help you. Before inserting into table must have table statistics[gather statistics]  As per your plain table info, it is hitting on Hash join; So better change the table positions [mean use small table and compare with large table] As per the observation almost all indexes are Bit map indexes. daily your inserting records and deleting records so i am expecting table have transaction information. when you have transaction info then use functional based index. Index scanning on Range wise, have a look on partition.i have not find any composite index, so no matter on composite index.if multiple user are using same table, use parallel hint.you can also use index hint in statement.you can use order of join hint in statement.how you are inserting records, mean fetching from how many tables used any temporary table inside, if not use with clause and fetch the records. if chance use bulk collection .used views - why using view how its populating data and what purpose[recommended to use temporary tables]. check the table space. if chance use different tables space for index.Thanks in advanceRegardsMoshe.K

    Please back up ALL of these statements, not one of them seems correct and/or relevant.

    Hash joins are okay to use

    Written table order is not going to play any effect (in 99% of cases)

    Bitmaps are okay to use

    How would you even determine composite indexes with what we have?

    Using parallel when there‘s multiple users?!

    I could go on.

  • Ginola
    Ginola Member Posts: 18 Red Ribbon
    edited Mar 5, 2018 6:49AM
    Jonathan Lewis wrote:Which version of Oracle ?How did you generate these execution plans ? It looks as if they came from the AWR - given the absence of any predicate markers (*) on the lines of the plan. At some point you need to pull a plan from memory with the complete predicate section if you want better information about what Oracle is doing.Checking the two plans, there's a huge overlap in straetgy, but one easily spotted significant difference: At line 108 of the bad plan you have a full tablescan of Organization returning an estimated 5,756 rows.At line 83 of the plan a full tablescan of Organization has an estimate of 58577 rows.It may be this difference in estimates that has resulted in Oracle re-arranging the order of some of the query blocks and drive a nested loop from organization.The scale of the change in estimate suggests that there may be a histogram on some column(s) in the predicate(s) used against Organization, and as time passes Oracle default histograms can just get unlucky and miss an important popular value.PLEASE NOTE - just because there's an obvious point of difference between the two plans that doesn't mean it's the source of the difference in performance. It's possible that the difference in estimates is a result of different predicates being usable because the table access order has changed. In part that's why it's so important to see the predicate detail from the actual run-time plan.RegardsJonathan Lewis

    I've found out tthat we did a change on Organization a couple of weeks ago. It was an added column on the table, that was added by an alter table add column. One of our developer was afraid that it would mess up statistics, so he dropped the table and recreated it with the column added in the create script. After that he gathered stats on the table. Maybe there was something in that operation that messed up the plan.

    It looks like the query now uses a plan with a higher estimate of appx. 55000 rows in Organization .

    Thanks so much for the help.

    I will now read through the FAQ before posting another performance tuning issue 

  • [Deleted User]
    [Deleted User] Posts: 0 Silver Trophy
    edited Mar 5, 2018 6:55AM

    That would have been my first and foremost question: What has changed? Something must have, because Oracle does not change it's behavior just for the fun of it. So figure out what has changed, how it has changed things and what impact those changes (might) have. 

This discussion has been closed.