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