Who's online

There are currently 0 users and 47 guests online.

Recent comments

MV Refresh

Here’s a funny little problem I came across some time ago when setting up some materialized views. I have two tables, orders and order_lines, and I’ve set up materialized view logs for them that allow a join materialized view (called orders_join) to be fast refreshable. Watch what happens if I refresh this view just before gathering stats on the order_lines table.

I have a little script that start with “set echo on”, then calls two packaged procedures, one to refresh the join view, the other to collect stats on the order_lines table; here’s the output from that script:

SQL> begin
  2          dbms_mview.refresh(
  3                  list    => 'test_user.orders_join',
  4                  method  => 'F'
  5          );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin
  2          dbms_stats.gather_table_stats(
  3                  ownname          => user,
  4                  tabname          =>'ORDER_LINES',
  5                  method_opt       => 'for all columns size 1',
  6                  cascade          => true
  7          );
  8  end;
  9  /
ERROR at line 1:
ORA-01760: illegal argument for function
ORA-06512: at "SYS.DBMS_STATS", line 33859
ORA-06512: at line 2

The stats gathering has failed (on one of the indexes, though that’s not immediately obvious) because the SQL statement generated by the procedure has gone through a query rewrite that takes it to the orders_join table – which won’t support some of the funny internal function used by Oracle to collect index stats. Here’s the SQL as generated (with a little bit of formatting):

		no_parallel_index(t, "ORL_PK")  dbms_stats cursor_sharing_exact use_weak_name_resl
		dynamic_sampling(0) no_monitoring no_substrb_pad  no_expand index(t,"ORL_PK")
	count(*) as nrw,
	count(distinct sys_op_lbid(82196,'L',t.rowid)) as nlb,
	null as ndk,
	sys_op_countchg(substrb(t.rowid,1,15),1) as clf
where	"ORDER_ID" is not null
or	"LINE_ID" is not null

Because of the particular way I happen to have defined my materialized view, this query gets rewritten if the view is “fresh” – which makes the hints inapplicable – and neither the sys_op_count_chg() nor the sys_op_lbid() functions are valid unless you’re doing an index fast full scan.

At first sight the fix to this problem would simply appear to require the addition of a no_rewrite() hint to the query – but maybe there’s some reason why this isn’t viable. Of course, you’d have to be a little unlucky to hit this bug – it takes a combination of timing and unlucky data to trigger it; but if you’re using join views with referential integrity all over the place you may be slightly more susceptible than average.

This bug is repeatable in 12c – and looks like the problem referenced in MOS note 317254.1 (and don’t ask which version it was first reported against!)