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,

Views: 90

Replies to This Discussion

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,

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,

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

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,

RSS

Oracle Jobs in US

© 2021   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service