Oracle Database Discussions

Here is a simple test case that illustrates a blocking issue we have with queries running against a table used in a view creation while the stats job is running on that table. Note the key is that the view create needs to start first and take more time than the stats job. It seems queries hang that are accessing the view when stats tries to update something in the library cache. Is this normal, and if so is it documented that you can't run stats and create a view at the same time?

Test case:

create table junker as select * from all_objects;
alter table junker add CONSTRAINT JUNKER_PK PRIMARY KEY (OWNER, OBJECT_NAME, OBJECT_ID)

create table junker1 as select * from all_objects;
alter table junker1 add CONSTRAINT JUNKER1_PK PRIMARY KEY (OWNER, OBJECT_NAME, OBJECT_ID)


drop materialized view junker1;
create materialized view junker1 as
select a.* from junker a, junker1 b where a.owner = b.owner


++/
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'myschema'
,TabName => 'junker'
,Estimate_Percent => 5
,Degree => 1
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/

select * from junker where owner='SYS' and object_name='ICOL$'

Why does the last query hang until stats is done or the view create is done? It this explained in the documentation?