This discussion is archived
9 Replies Latest reply: Jul 10, 2013 7:21 AM by Stefan Jager RSS

Performance problem with GotoDate

aschilling Newbie
Currently Being Moderated
hi everyone!

we're facing a performance issue when looking at historical data based on GotoDate().
The following points apply to our case:
- the problem only appears for one specific table, it's not a global problem (at least we currently only found this table to cause problems)
- the problem only appears when using GotoDate(). When using GotoSavepoint(), performance is as expected.
- Explain plan is the same for both Savepoints and GotoDate() (which is probably as expected, as only the session context changes?)

While a select takes < 0.1s when working on LATEST or any Savepoint it takes something between 30s and 150s when working on a date.
Any hints what could cause that problem? The table contains base data, thus it changes rarely.
Unfortunately the data in there is quite important and connected to several other tables and processes within our application. So for our customer analyzing historical data is currently extremely slow.

kind regards,

Andreas
  • 1. Re: Performance problem with GotoDate
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Andreas,

    All of the queries on the versioned table would share the same SQL plan since it is a single view being referenced. The only difference should be the data that is being accessed. However, when viewing data from a savepoint(including LATEST) a large part of the plan will not be needed as the context will control which of the cases is currently being performed. When using GotoDate, a self-join back to the base table is needed in order to return the appropriate rows. Most likely the optimizer is choosing an inefficient method for this join. Make sure that all of the statistics on that table and the WMSYS schema are up to date. Also, which history option is being used for the table? Is the SQL returning all rows or just a subset?

    It's typically difficult to diagnose performance problems in a forum setting, especially without the SQL plan being used and table statistics. If you can provide any additional details that would be helpful, otherwise a SR might be in order.

    Regards,
    Ben
  • 2. Re: Performance problem with GotoDate
    aschilling Newbie
    Currently Being Moderated
    Hi Ben,

    thanks for the hints so far.
    I checked the "last analyzed" state of the tables and it seems legit to me.
    Is it enough when tables have last been analyzed after a change to them? Because many of the tables containing base data that rarely ever changes haven't been analyzed in the last time, but also noone changed them.
    I'll ask the DBAs whether they are sure to have periodic statistics generation enabled, though it seems so.

    Concerning your other questions: the history option is VIEW_WO_OVERWRITE and we do not use valid time support. The SQL we executed did return all rows, but it also didn't affect the performance if we didn't. The table contains only few data, approx. 150 rows.

    kind regards,

    Andreas
  • 3. Re: Performance problem with GotoDate
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    There's no need to reanalyze tables if they haven't changed. If there is only 150 rows, then something else might be happening. Even the worst plan shouldn't take that long for a table with that few number of rows. I would suggest looking at a detailed trace to see where all of the time is being spent.

    Regards,
    Ben
  • 4. Re: Performance problem with GotoDate
    aschilling Newbie
    Currently Being Moderated

    Hi Ben,

     

    it's been a while since we've been looking into this issue, but it's still there. We finally got a trace, maybe that already helps to see where something might go wrong?

    What we were doing to reproduce the problem was this (some names changed as they contain customer information):

    execute dbms_wm.gotoworkspace('TestWorkspace');
    execute dbms_wm.gotodate('02082013120000', 'mmddyyyyhh24miss', null, false);
    select * from parameter; 

     

    And here is the trace for the relevant part:

    select *

    from

    parameter


     

     

    call     count       cpu    elapsed       disk      query    current        rows

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

    Parse        1      0.00       0.00          0          0          0           0

    Execute      1      0.00       0.00          0          0          0           0

    Fetch        2    187.07     208.21          0     258015          0         256

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

    total        4    187.07     208.21          0     258015          0         256

     

    Misses in library cache during parse: 0

    Optimizer mode: ALL_ROWS

    Parsing user id: 56 

     

    Rows     Row Source Operation

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

        256  FILTER  (cr=258015 pr=0 pw=0 time=29778073 us)

       8299   TABLE ACCESS FULL PARAMETER_LT (cr=249 pr=0 pw=0 time=49908 us)

        141   FILTER  (cr=1034 pr=0 pw=0 time=14769 us)

        486    INDEX RANGE SCAN WM$NEXTVER_TABLE_NV_INDX (cr=966 pr=0 pw=0 time=7736 us)(object id 49175)

          0    TABLE ACCESS BY INDEX ROWID WM$WORKSPACES_TABLE (cr=0 pr=0 pw=0 time=0 us)

          0     INDEX UNIQUE SCAN WM$WORKSPACES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 48982)

          0    TABLE ACCESS BY INDEX ROWID WM$WORKSPACES_TABLE (cr=0 pr=0 pw=0 time=0 us)

          0     INDEX UNIQUE SCAN WM$WORKSPACES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 48982)

         11    TABLE ACCESS BY INDEX ROWID WM$VERSION_TABLE (cr=68 pr=0 pw=0 time=906 us)

         30     INDEX UNIQUE SCAN WM$VERSION_PK (cr=38 pr=0 pw=0 time=447 us)(object id 48985)

         23   VIEW  WM$TABLE_PARVERS_VIEW (cr=227 pr=0 pw=0 time=5410 us)

         23    UNION-ALL  (cr=227 pr=0 pw=0 time=4864 us)

         17     FILTER  (cr=118 pr=0 pw=0 time=1478 us)

         17      INDEX UNIQUE SCAN MODIFIED_TABLES_PK (cr=118 pr=0 pw=0 time=741 us)(object id 49006)

          0       TABLE ACCESS BY INDEX ROWID WM$WORKSPACES_TABLE (cr=0 pr=0 pw=0 time=0 us)

          0        INDEX UNIQUE SCAN WM$WORKSPACES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 48982)

          0       TABLE ACCESS BY INDEX ROWID WM$WORKSPACES_TABLE (cr=0 pr=0 pw=0 time=0 us)

          0        INDEX UNIQUE SCAN WM$WORKSPACES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 48982)

          0      TABLE ACCESS BY INDEX ROWID WM$WORKSPACES_TABLE (cr=0 pr=0 pw=0 time=0 us)

          0        INDEX UNIQUE SCAN WM$WORKSPACES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 48982)

          0      TABLE ACCESS BY INDEX ROWID WM$WORKSPACES_TABLE (cr=0 pr=0 pw=0 time=0 us)

          0        INDEX UNIQUE SCAN WM$WORKSPACES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 48982)

          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)

          0      TABLE ACCESS BY INDEX ROWID WM$VERSION_TABLE (cr=0 pr=0 pw=0 time=0 us)

          0       INDEX RANGE SCAN WM$VERSION_PK (cr=0 pr=0 pw=0 time=0 us)(object id 48985)

          0      INDEX UNIQUE SCAN MODIFIED_TABLES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49006)

          0   INDEX UNIQUE SCAN MODIFIED_TABLES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49006)

          0   VIEW  WM$TABLE_WS_PARVERS_VIEW (cr=0 pr=0 pw=0 time=0 us)

          0    UNION-ALL  (cr=0 pr=0 pw=0 time=0 us)

          0     INDEX UNIQUE SCAN MODIFIED_TABLES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49006)

          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)

          0      TABLE ACCESS BY INDEX ROWID WM$VERSION_TABLE (cr=0 pr=0 pw=0 time=0 us)

          0       INDEX RANGE SCAN WM$VERSION_PK (cr=0 pr=0 pw=0 time=0 us)(object id 48985)

          0      INDEX UNIQUE SCAN MODIFIED_TABLES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49006)

          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)

          0    TABLE ACCESS BY INDEX ROWID PARAMETER_LT (cr=0 pr=0 pw=0 time=0 us)

          0     BITMAP CONVERSION TO ROWIDS (cr=0 pr=0 pw=0 time=0 us)

          0      BITMAP AND  (cr=0 pr=0 pw=0 time=0 us)

          0       BITMAP CONVERSION FROM ROWIDS (cr=0 pr=0 pw=0 time=0 us)

          0        INDEX RANGE SCAN PARAMETER_PKI$ (cr=0 pr=0 pw=0 time=0 us)(object id 89769)

          0       BITMAP CONVERSION FROM ROWIDS (cr=0 pr=0 pw=0 time=0 us)

          0        SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us)

          0         INDEX RANGE SCAN PARAMETER_TI$ (cr=0 pr=0 pw=0 time=0 us)(object id 89768)

          0    VIEW  WM$TABLE_WS_PARVERS_VIEW (cr=0 pr=0 pw=0 time=0 us)

          0     UNION ALL PUSHED PREDICATE  (cr=0 pr=0 pw=0 time=0 us)

          0      INDEX UNIQUE SCAN MODIFIED_TABLES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49006)

          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)

          0       TABLE ACCESS BY INDEX ROWID WM$VERSION_TABLE (cr=0 pr=0 pw=0 time=0 us)

          0        INDEX RANGE SCAN WM$VERSION_PK (cr=0 pr=0 pw=0 time=0 us)(object id 48985)

          0       INDEX UNIQUE SCAN MODIFIED_TABLES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49006)

          0   INDEX UNIQUE SCAN MODIFIED_TABLES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 49006)

     

     

    Elapsed times include waiting on following events:

      Event waited on                             Times   Max. Wait  Total Waited

      ----------------------------------------   Waited  ----------  ------------

      SQL*Net message to client                       2        0.00          0.00

      SQL*Net more data to client                     9        0.00          0.00

      SQL*Net message from client                     2        5.58          5.58

      library cache lock                              1        0.00          0.00

    ********************************************************************************

     

     

    If I interprete the output correctly, filtering the rows takes ages. I do not understand why that is, though.

     

    EDIT: well, now I see the optimizer mode ALL_ROWS. Does that mean system statistics are missing and the CBO can't work correctly? A quick try-out using

    select /*+ opt_param('optimizer_index_cost_adj',20) */ * from parameter;

    shows alot better performance (~0.8s for selecting the 256 rows)

     

    Kind regards,

     

      Andreas

  • 5. Re: Performance problem with GotoDate
    Ben Speckhard Pro
    Currently Being Moderated

    Hi Andreas,

     

    Without being able to reproduce this, I would say it is due to the 'bitmap conversion from rowids' part of the plan.  Why it's taking as long as it is on such a small table is hard to know for sure.  The bitmap conversion part is only executed after either GotoDate or GotoSavepoint is executed.  The optimizer_index_cost_adj hint most likely changed the plan in a way that removed the bitmap conversion from the plan.  Is that correct?  Not quite sure why it would be used in the first place.  First time I have seen that.

     

    Regards,

    Ben

  • 6. Re: Performance problem with GotoDate
    aschilling Newbie
    Currently Being Moderated

    hi Ben!

     

    thanks for the hint. Yes, it's the bitmap conversion thing that's responsible for the bad performance.

    If I disable bitmap plans temporarily (setting _b_tree_bitmap_plans=false for the session) I also get the desired performance.

    Some quick research told me, that "bitmap conversion from rowid" can hurt if the table is very small, which would be one possible explanation in our case as the table actually IS small. Also, it seems bitmap conversion can also appear even when no bitmap indices are defined.

    Of course we could set that session parameter whenever we perform queries with either a savepoint or an instant set but then the next question would be whether that might impact queries against other tables in a bad manner...

     

    Regards,

     

    Andreas

  • 7. Re: Performance problem with GotoDate
    Stefan Jager Journeyer
    Currently Being Moderated

    If it's only 256 rows, and it is not going to grow significantly in the near future, you might be better of dropping the index completely and force an FTS. That's what I usually do on small lookup tables. Of course it depends a bit on how much history is in there, so what the total amount of records in the _LT table is, and whether WM can deal with no index.

     

    aschilling wrote:

    "bitmap conversion from rowid" can hurt if the table is very small

    Even a "normal" index can hurt if the table is small: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4433887271030 is an interesting read on this subject.

     

    Regards,

    Stefan

  • 8. Re: Performance problem with GotoDate
    Ben Speckhard Pro
    Currently Being Moderated

    Hi,

     

    It's possible it would impact performance of other queries if it's set at the session level.  Only way to know is to try to find any other queries that would be using that parameter.  One possibility would be to set it on and off before and after the sql is executed.  Otherwise, I would raise an issue with the optimizer to see why that path is being chosen in the first place.

     

    Workspace Manager doesn't support dropping of indexes on the underlying _LT tables.  Only the dropping of user defined indexes during a beginDDL/commitDDL session is supported.  The internal indexes need to be there or you would get errors involving the metadata that we store.

     

    Regards,

    Ben

  • 9. Re: Performance problem with GotoDate
    Stefan Jager Journeyer
    Currently Being Moderated

    Hi Ben,

    BenSpeckhard wrote:

    Workspace Manager doesn't support dropping of indexes on the underlying _LT tables.

    The internal indexes need to be there or you would get errors involving the metadata that we store

    I was kind of afraid something like that would be the case ... at least it's good to know for sure.

     

    Cheers,

    Stefan

Legend

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