Gathering and Publishing Statistics Independently

Purpose

The goal of this tutorial is to show you how to change preferences, and gather and publish statistics independently.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Changing Global and Table Statistic Preferences
 Gathering Pending Statistics
 Testing Pending Statistics
 Publishing Pending Statistics
 Resetting Statistics and Preferences
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Prior to Oracle Database 11g, when you gathered optimizer statistics, the statistics were automatically published once the gather was completed. Now, in Oracle Database 11g, you can separate statistic gathering from statistic publishing. By allowing you to separate these processes you now have an opportunity to test the newly gathered statistics before they are published. In this OBE tutorial, you check what public and pending statistics exist for a given table and how you can gather statistics without publishing them and finally how to publish pending stats. In addition, you test pending statistics before you make them public to ensure they are acceptable.

Back to Topic List

Prerequisites

Before starting this tutorial, you should first complete the following steps:

1. Install Oracle Database 11g and make sure the SH user is unlocked.
2. Download and unzip the stats.zip file into a working directory.
3.

You need to create and load the data for the CUSTOMERS_OBE table. Open a terminal window and execute the following:

                                 
                                   
cd <sql_files_directory>
                                                                
                                   
imp sh/sh file= customers_obe.dmp log=imp.log full=y
                                
                              


Back to Topic List

Changing Global and Table Statistic Preferences

In this topic, you change the default preferences or parameter settings for gathering statistics. One of the parameters that you may be concerned about is the STALE_PERCENT. STALE_PERCENT determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be re-gathered. Perform the following steps:

1.

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;

 

2.

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;

 

3.

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;

 

4.

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 tab le; 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;

 

5.

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;

 

6.

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;

 

Back to Topic List

Gathering Pending Statistics

In this topic, you check what public and pending statistics exist for a given table and how you can gather statistics without publishing them. Perform the following steps:

1.

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');

 

2.

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;

 

3.

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;

 

4.

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;

 

5.

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;

 

6.

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');

 

7.

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;

 

8.

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');

 

9.

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;

 

10.

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;

 

Back to Topic List

Testing Pending Statistics

All the statistics on the CUSTOMERS_OBE table are pending. If you wanted to test the new statistics, you could export them from the pending statistics table and import them into a test system. If they were found to be unacceptable, you could simply delete them from the pending statistics tables without affecting production. Perform the following steps:

1.

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;

 

2.

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.

 

3.

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;

 

4.

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.

 

Back to Topic List

Publishing Pending Statistics

In this topic, you can publish pending stats. Perform the following steps:

1.

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);

 

2.

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;

 

3.

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.

 

Back to Topic List

Resetting Statistics and Preferences

To reset the statistics and preferences from this OBE tutorial, perform the following steps:

1.

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');

 

2.

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;

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Change global and table statistic preferences
 Gather pending statistics
 Test pending statistics
 Publish pending statistics
 Reset statistics and preferences

Back to Topic List

 Place your cursor on this icon to hide all screenshots.

 

 

 



In-Memory Replay Banner