Back again in Munich after a short weekend at home, I am now teaching an 11g New Features course. As it happens frequently, one of the students already attended a previous course with me being the instructor. It is always nice to see friendly and somehow familiar looking faces in the auditorium 🙂

One of the minor but nevertheless interesting 11g New Features is the possiblity to defer the publishing of gathered optimizer statistics for productive usage. One reason for doing so might be that you want to make sure, you have gathered all the statistics of your (very big) segments, before publishing them as a whole. Another possible reason might be that you suspect the new statistics could cause bad performance and therefore, you first want to test them before using them productively. The following little demonstration, I have developed for the present course:

---------------------------
-- Demo Pending Statistics
---------------------------
connect system/oracle
drop user psuser cascade;
create user psuser identified by psuser
default tablespace users quota unlimited on users;
grant create session,create table to psuser;
create table psuser.customers
 (cust_id number,name varchar2(10));
create table psuser.products
 (prod_id number,product varchar2(10));
create table psuser.sales
 (cust_id number ,prod_id number,
  buy_date date, price number (10,2));
insert into psuser.customers values (1,'John Doe');
insert into psuser.products values (42,'Oracle EE');
insert into psuser.products values (43,'Oracle SE');
insert into psuser.sales values (1,42,sysdate,9999.98);
insert into psuser.sales values (1,43,sysdate,999.98);
insert into psuser.sales values (1,43,sysdate,998.98);
commit;
-----------------------------------------
-- Statistics for the psuser schema will
-- not be published immediately:
-----------------------------------------
exec dbms_stats.set_schema_prefs('psuser','publish','false')
exec dbms_stats.gather_schema_stats('psuser')
select table_name,num_rows from dba_tables
where owner='PSUSER';
select table_name,num_rows from dba_tab_pending_stats
where owner='PSUSER';
---------------------------------------------------
-- The gathered Statistics are not being used yet:
---------------------------------------------------
connect psuser/psuser
set autotrace on explain
select name,product,price from psuser.sales
 natural join psuser.products
 natural join psuser.customers;
---------------------------
-- We may first test them:
---------------------------
connect system/oracle
alter session set optimizer_use_pending_statistics=true;
set autotrace on explain
select name,product,price from psuser.sales
 natural join psuser.products
 natural join psuser.customers;
----------------------------------------------
-- After successful testing, we publish them:
----------------------------------------------
exec dbms_stats.publish_pending_stats('psuser',null)