Oracle Scratchpad

August 15, 2013

MV Refresh

Filed under: Bugs,CBO,Infrastructure,Materialized view,Oracle,Statistics — Jonathan Lewis @ 6:12 pm BST Aug 15,2013

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


  1. Good catch. The “no_write” should likely be “no_rewrite”.

    Comment by Josef — August 15, 2013 @ 6:29 pm BST Aug 15,2013 | Reply

  2. Without looking at the MOS article, I’m guessing that a reasonable workaround would be setting QUERY_REWRITE_ENABLED = FALSE for the session before calling DBMS_STATS.

    Comment by Jason Bucata — August 15, 2013 @ 8:11 pm BST Aug 15,2013 | Reply

    • Jason,

      I’d guess that that should work too – but it shouldn’t be up to the application programmer or DBA to make sure the environment has been tweaked in “unexpected” ways to allow a built-in to work.

      Comment by Jonathan Lewis — August 16, 2013 @ 9:17 am BST Aug 16,2013 | Reply

  3. I guess, just too many good blog articles:

    Comment by stefanz — August 15, 2013 @ 9:11 pm BST Aug 15,2013 | Reply

    • Stefan,

      Thanks – especially for the “good”.
      I usually put the URL into a script once I’ve blogged it, but I obviously forgot that time.
      I was thinking about adding the SQL to create the data – but now I don’t need to.

      Comment by Jonathan Lewis — August 16, 2013 @ 9:20 am BST Aug 16,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by