Oracle Scratchpad

August 15, 2013

MV Refresh

Filed under: Bugs,CBO,Infrastructure,Oracle,Statistics — Jonathan Lewis @ 6:12 pm GMT 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>
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>
SQL>
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  /
begin
*
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):


select
	/*+
		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
from 	"TEST_USER"."ORDER_LINES" t
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!)

6 Comments »

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

    Comment by Josef — August 15, 2013 @ 6:29 pm GMT 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 GMT 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 GMT Aug 16,2013 | Reply

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

    http://jonathanlewis.wordpress.com/2012/09/02/bugs-3/

    Comment by stefanz — August 15, 2013 @ 9:11 pm GMT 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 GMT Aug 16,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,430 other followers