Last week I published some code that demonstrated how a fast refresh of an aggregate materialized view could fail because of the internal use of the sys_op_map_nonnull() function. In some ways it was an easy problem to explain because all you had to do was trace the call to refresh and see what was going on. Today’s bug is completely different – it’s virtually impossible to see the connection between the failure and its cause. Here (as a cut-n-paste) is an example of what happens when the bug appears:
execute dbms_stats.gather_schema_stats(user) * ERROR at line 1: ORA-01760: illegal argument for function ORA-06512: at "SYS.DBMS_STATS", line 13336 ORA-06512: at "SYS.DBMS_STATS", line 13682 ORA-06512: at "SYS.DBMS_STATS", line 13760 ORA-06512: at "SYS.DBMS_STATS", line 13719 ORA-06512: at line 1
Now, when a called to dbms_stats fails, where are you supposed to look ? Since I had very few objects in the schema I tried collecting table stats for each object in turn (with cascade set to true) and found that the call to gather_table_stats() failed for just one of the tables; then I gathered stats on the table with cascade set to false and found that the call succeeded, so I finished off with a simple call to gather_index_stats on the one index on the table – and that failed.
In my case I had a clue about what to try next. I was running a test case for fast refresh of materialized join view. So I dropped the materialized view and all the calls to gather stats succeeded. So – a call to gather index stats on one of the indexes of a table involved in a materialized join view fails, but only after you’ve done a fast refresh on the view !
This was just an oddity that appeared while I was testing the basic options for non-trival fast refreshes, so I never got around to looking into it properly and didn’t send an SR and test case to Oracle, but if anyone wants to examine the problem, here’s a simple test case (with some of my debug code and minor variations stripped out).
create table orders( order_id number(10) not null, customer_id number(10) not null, store_id number(10) not null, order_date date not null, sales_rep number(10) not null, total_value number(8,2), total_tax number(8,2), total_discount number(8,2), constraint ord_pk primary key (order_id) ); create table order_lines( order_id number(10) not null, line_id number(4) not null, stock_code number(10) not null, line_quantity number(5) not null, line_value number(8,2), line_tax number(8,2), line_discount number(8,2), constraint orl_pk primary key (order_id, line_id), constraint orl_fk_ord foreign key (order_id) references orders ); create materialized view log on orders with rowid, primary key ( customer_id, store_id, order_date, sales_rep, total_value, total_tax, total_discount ) including new values ; create materialized view log on order_lines with rowid, primary key ( stock_code, line_quantity, line_value, line_tax, line_discount ) including new values ; create materialized view orders_join refresh fast on demand enable query rewrite as select ord.rowid ord_rowid, orl.rowid orl_rowid, ord.order_id, orl.line_id, ord.store_id, ord.customer_id, ord.order_date, orl.stock_code, orl.line_quantity from orders ord, order_lines orl where orl.order_id = ord.order_id ; prompt ======================================== prompt A call to gather schema stats works here prompt ======================================== execute dbms_stats.gather_schema_stats(user) prompt ============== prompt Load some data prompt ============== insert into orders( order_id, customer_id, store_id, order_date, sales_rep, total_value, total_tax, total_discount ) select rownum, trunc(dbms_random.value(1,5)), trunc(dbms_random.value(1,3)), trunc(sysdate), 1,1,1,1 from all_objects where rownum <= 5 ; insert into order_lines( order_id, line_id, stock_code, line_quantity, line_value, line_tax, line_discount ) with line_ct as ( select --+ materialize rownum id from dual connect by rownum user, tabname =>'ORDER_LINES', method_opt => 'for all columns size 1', cascade => true ); end; / prompt ============================== prompt Refresh the materialized views prompt ============================== begin dbms_mview.refresh( list => 'test_user.orders_join', method => 'F' ); end; / prompt ========================================= prompt A call to gather schema stats fails here prompt on many versions of Oracle up to 184.108.40.206 prompt ========================================= execute dbms_stats.gather_schema_stats(user) begin dbms_stats.gather_table_stats( ownname => user, tabname =>'ORDER_LINES', method_opt => 'for all columns size 1', cascade => true ); end; / begin dbms_stats.gather_table_stats( ownname => user, tabname =>'ORDER_LINES', method_opt => 'for all columns size 1', cascade => false ); end; / begin dbms_stats.gather_index_stats( ownname => user, indname =>'ORL_PK' ); end; /
I got the same behaviour on 10.2.0.3, 220.127.116.11 and 18.104.22.168 (although the line numbers in the PL/SQL error stack vary with version, of course).
Resolution of this problem is left as an exercise to the reader, but if I had to investigate further I think I’d start by checking MOS for anything similar, then sending in an SR with the demo, then run the code with sql_trace set to level 4 to see if any unexpected values were being passed in as bind variables to the various bits of recursive SQL called by dbms_stats.