Replies to This Discussion

Permalink Reply by Jared Perla on July 5, 2013 at 3:07pm

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,

Permalink Reply by Barn Mitchel on July 5, 2013 at 3:07pm

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,

Permalink Reply by Jared Perla on July 5, 2013 at 3:08pm

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

Permalink Reply by Jared Perla on July 5, 2013 at 3:50pm

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,