You first want to check what the default settings are. Open a terminal window and execute the following commands:

cd <sql_files_directory>
sqlplus /nolog
set echo on
@check_sales_pref

The check_sales_pref.sql code is as follows:

connect sh/sh

select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;

The default value for the SALES table is 10%. In order to change a parameter or preference for the entire database you need to have dba privileges. Once connected as sysdba, you can set the STALE_PERCENT for the entire database to be 13%. Then check the stale percent again. From your terminal window, execute the following SQL script:

@change_global_pref
@check_sales_pref

The change_global_pref.sql and check_sales_pref.sql code is as follows:

connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;

Now the STALE_PERCENT for the SALES table is 13% which is also the global value. What if you only want to change the STALE_PERCENT for a single table. You can do that by using the set_table_prefs procedure. Set the STALE_PERCENT for the SALES table to 65%. Since you are only changing one table that belongs to this schema you don't need to be logged in as sysdba. Then check the stale percent again. From you terminal window, execute the following SQL scripts:

@change_table_pref
@check_sales_pref

The change_table_pref.sql and check_sales_pref.sql code is as follows:

execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;

The STALE_PERCENT for sales has changed to 65% but what about all the other tables in the schema? Check the STALE_PERCENT for the PRODUCTS table; it should be the new global default of 13%. From your terminal window, execute the following SQL script:

@check_products_pref

The check_products_pref.sql code is as follows:

select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent 
from dual;

Now you can reset everything back to the default. You first need to delete the table preference you set on SALES so that the table has the global default value. From your terminal window, execute the following SQL script:

@reset_table_prefs
@check_sales_pref

The reset_table_prefs.sql and check_sales_pref.sql code is as follows:

execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;

Now you have to reconnect as the sysdba to reset the global defaults. You don't need to remember what the default values are. You just need to set the preference value to null and that restores the "factory" default value. From your terminal window, execute the following SQL script:

@reset_global_prefs
@check_sales_pref

The reset_table_prefs.sql and check_sales_pref.sql code is as follows:

connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', null);

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;

You first want to alter the date format and reset the stats for the CUSTOMERS_OBE table. From your terminal window, execute the following script:

@reset_table_stats

The reset_table_stats.sql code is as follows:

connect sh/sh
alter session set nls_date_format='mm/dd hh24:mi:ss';

-- delete statistics
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');

Now you can review the public statistics for the CUSTOMERS_OBE table. From your terminal window, execute the following command:

@show_public_stats CUSTOMERS_OBE

The show_public_stats.sql code is as follows:

-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = '&1';

-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows, 
       leaf_blocks, distinct_keys
from user_indexes
where table_name = '&1'
order by index_name;

-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct, 
       num_nulls, density
from user_tab_columns
where table_name = '&1'
order by column_name;

And then review the pending statistics for the CUSTOMERS_OBE table. From your terminal window, execute the following command:

@show_pending_stats CUSTOMERS_OBE

The show_pending_stats.sql code is as follows:

-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;

-- indexes
select index_name, last_analyzed "analyze time", num_rows, 
       leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;

-- columns
select column_name, last_analyzed "analyze time", num_distinct, 
       num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;

Currently the CUSTOMERS_OBE table does not have any public or pending statistics. The default behavior in Oracle Database 11g is the same as before where the statistic is published as soon as the gather is complete. You can check the preferences by executing the following script from your terminal window:

@check_publish_prefs

The check_publish_prefs.sql code is as follows:

select dbms_stats.get_prefs('PUBLISH') publish from dual;

You can also check or change the publish mode at a table level. You want to check the publish preference value for the CUSTOMERS_OBE table. It should be the same as the global default. From your terminal window, execute the following SQL script:

@check_table_publish_prefs

The check_table_publish_prefs.sql code is as follows:

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;

Set the CUSTOMERS_OBE table's publish value to false. This means that any statistics gathered from now on will not be automatically published. From your terminal window, execute the following SQL script:

@set_table_publish_prefs_false

The set_table_publish_prefs_false.sql code is as follows:

exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');

Confirm that your change took effect, by executing the following script:

@check_table_publish_prefs

The check_table_publish_prefs.sql code is as follows:

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;

Now you can gather statistics on the CUSTOMERS_OBE table by executing the following script:

@gather_table_stats

The gather_table_stats.sql code is as follows:

execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');

Because the CUSTOMERS_OBE table has its publish preference set to false, there should not be any public statistics for this table after the gather statistics task. From your terminal window, execute the following script:

@show_public_stats CUSTOMERS_OBE

The show_public_stats.sql code is as follows:

-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = '&1';

-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows, 
       leaf_blocks, distinct_keys
from user_indexes
where table_name = '&1'
order by index_name;

-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct, 
       num_nulls, density
from user_tab_columns
where table_name = '&1'
order by column_name;

Now, check if you have any pending statistics. From your terminal window, execute the following script:

@show_pending_stats CUSTOMERS_OBE

The show_pending_stats.sql code is as follows:

-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;

-- indexes
select index_name, last_analyzed "analyze time", num_rows, 
       leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;

-- columns
select column_name, last_analyzed "analyze time", num_distinct, 
       num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;

You need to turn off the use of pending statistics by executing the following script:

@set_pending_stats_off

The set_pending_stats_off.sql code is as follows:

alter session set optimizer_use_pending_statistics = false;
alter session set optimizer_dynamic_sampling = 0;

Now you can get and display an explain plan for your query. From your terminal window, execute the following script:

@get_execplan

The get_execplan.sql code is as follows:

explain plan for 
select * from customers_obe where CUST_CREDIT_LIMIT=1500;

select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));

This is not a very optimal plan because it requeries an index look-up and then a single-row access of the table for every row that has a CUST_CREDIT_LIMIT=1500. Over 20% of the rows in the table have the CUST_CREDIT_LIMIT value of 1500. As the CUSTOMERS_OBE table grows, this plan will get slower and slower as the number of rows matching the query increases.

To see if the optimizer does better when it uses the statistics in the pending statistic tables, you need to set optimizer_use_pending_statistics to true by running the following script:

@set_optimizer_pending_stats_true

The set_optimizer_pending_stats_true.sql code is as follows:

alter session set optimizer_use_pending_statistics = true;

Now rerun the explain plan for our query.

@get_execplan

The get_execplan.sql code is as follows:

explain plan for 
select * from customers_obe where CUST_CREDIT_LIMIT=1500;

select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));

This plan with the pending statistics is much better. You now do a full table scan which retrieves all of the matching rows with a single pass of the table. This is the plan you want to run in production.

If the statistics prove to be acceptable, you can make them public by executing the following script:

@publish_pending_stats

The publish_pending_stats.sql code is as follows:

exec dbms_stats.publish_pending_stats(null, null);

Now if you check the public statistics for the CUSTOMERS_OBE table, you should see all of the statistics you gathered earlier. From your terminal window, execute the following script:

@show_public_stats CUSTOMERS_OBE

The show_public_stats.sql code is as follows:

-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = '&1';

-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows, 
       leaf_blocks, distinct_keys
from user_indexes
where table_name = '&1'
order by index_name;

-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct, 
       num_nulls, density
from user_tab_columns
where table_name = '&1'
order by column_name;

Then check the pending statistics for the CUSTOMERS_OBE table. From your terminal window, execute the following script:

@show_pending_stats CUSTOMERS_OBE

The show_pending_stats.sql code is as follows:

-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;

-- indexes
select index_name, last_analyzed "analyze time", num_rows, 
       leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;

-- columns
select column_name, last_analyzed "analyze time", num_distinct, 
       num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;

Notice that your pending statistics are now gone because they have been published.

You first need to delete the table preference. From your terminal window, execute the following script:

@delete_table_stats

The delete_table_stats.sql code is as follows:

exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');

Then you need to set the global value for the CUSTOMERS_OBE table to PUBLISH. From your terminal window, execute the following:

@set_global_publish

The set_global_publish.sql code is as follows:

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;