Bugs

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 <= 10
)
select
	ord.order_id,
	lct.id,
	trunc(dbms_random.value(1,10)),
	100 * lct.id,
	100 * lct.id,
	100 * lct.id,
	100 * lct.id
from
	line_ct	lct,
	orders	ord
;

commit;


prompt	========================================
prompt	A call to gather schema stats works here
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;
/

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 11.2.0.3
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, 11.1.0.7 and 11.2.0.3 (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.