In Oracle 11g you now can modify gather_database_stats default values with set_global_prefs, set_database_prefs, set_schema_prefs, set_table_prefs in the dbms_stats package.

An even greater new feature is that in Oracle Database 11g, you can separate statistic gathering from statistic publishing.

This allows you to control when new statistics are published. Everybody must have run in to this once, that queries ran differently because part of the statistics where just refreshed or being gathered.

TEST STEPS modifying preferences

About this subject Oracle wrote some Oracle By Example (OBE) scripts available at:

http://otnbeta.us.oracle.com/db11gr1/trng/obes/index.htm

This really speeded up the testing of this new feature.
Get the custemers_obe.dmp file and import it under the SH Schema.

imp sh/sh file= customers_obe.dmp log=imp.log full=y

Import: Release 11.1.0.3.0 - Beta on Thu Mar 29 13:04:10 2007

Copyright (c) 1982, 2006, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.3.0 - Beta
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V11.01.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SH’s objects into SH
. importing SH’s objects into SH
. . importing table “CUSTOMERS_OBE” 630 rows imported
Import terminated successfully without warnings.

SQL> connect sh/sh

CHECK Default Settings of STALE_PERCENT. Note that 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 regathered

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

STALE_PERCENT
——————————————————————————–
10

To change the default setting for the database do:
execute dbms_stats.set_global_prefs(’STALE_PERCENT’, ‘13′);

PL/SQL procedure successfully completed.

And Check:
SQL> connect sh/sh
select dbms_stats.get_prefs(’STALE_PERCENT’, ‘SH’, ‘SALES’) stale_percent
from dual;

STALE_PERCENT
——————————————————————————–
13

11g allows to change this parameter even on a table by table basis.

For example set the STALE_PERCENT for the SALES table to 65%.
execute dbms_stats.set_table_prefs(’SH’, ‘SALES’, ‘STALE_PERCENT’, ‘65′);

PL/SQL procedure successfully completed.

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

STALE_PERCENT
——————————————————————————–
65

Note that another table the product table still uses 13%.

SQL> select dbms_stats.get_prefs(’STALE_PERCENT’, ‘SH’, ‘PRODUCTS’) stale_percent
from dual;

STALE_PERCENT
——————————————————————————–
13

If you want to 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. Execute the following SQL script:

execute dbms_stats.delete_table_prefs(’SH’, ‘SALES’, ‘STALE_PERCENT’);

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

STALE_PERCENT
——————————————————————————–
13

Now you have to reconnect as system or sysdba to reset the global defaults. You don’t need to remember what the default values are, you simple need to set the preference value to null and that restores the “factory” default value.

Execute the following SQL script:

SQL> execute dbms_stats.set_global_prefs(’STALE_PERCENT’, null);

PL/SQL procedure successfully completed.

SQL> conn sh/sh
Connected.
SQL> select dbms_stats.get_prefs(’STALE_PERCENT’, ‘SH’, ‘SALES’) stale_percent
from dual;

STALE_PERCENT
——————————————————————————–
10

###

TEST STEPS Gathering Private Statistics

You first want to alter the date format and reset the stats for the CUSTOMERS_OBE table.

Execute the following commands:
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.
With the following command:

select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = ‘CUSTOMER_OBE’;

no rows selected

– indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows,
leaf_blocks, distinct_keys
from user_indexes
where table_name = ‘CUSTOMER_OBE’
order by index_name;

no rows selected

– columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct,
num_nulls, density
from user_tab_columns
where table_name = ‘CUSTOMER_OBE’
order by column_name;

COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH

15 rows selected.

Now review the private statistics for the CUSTOMERS_OBE table.

select table_name, last_analyzed “analyze time”, num_rows, blocks, avg_row_len
from user_tab_private_stats
where table_name = ‘CUSTOMER_OBE’ and partition_name is null;

– indexes
select index_name, last_analyzed “analyze time”, num_rows,
leaf_blocks, distinct_keys
from user_ind_private_stats
where table_name = ‘CUSTOMER_OBE’ and partition_name is null
order by index_name;

select column_name, last_analyzed “analyze time”, num_distinct,
num_nulls, density
from user_col_private_stats
where table_name = ‘CUSTOMER_OBE’ and partition_name is null
order by column_name;

Now 3 times no rows selected.

Currently the CUSTOMERS_OBE table does not have any public or private statistics.

NOTE THAT 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:

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

PUBLISH
——————————————————————————–
TRUE

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.

Check this with:

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

PUBLISH
——————————————————————————–
TRUE

Set the CUSTOMERS_OBE tables publish value to false. This means that any statistics gathered from now on
will not be automatically published. Execute the following SQL script:

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

PL/SQL procedure successfully completed.

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

PUBLISH
——————————————————————————–
FALSE

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

execute dbms_stats.gather_table_stats(’SH’, ‘CUSTOMERS_OBE’);

PL/SQL procedure successfully completed.

Since 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.

Check with:

– tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = ‘CUSTOMER_OBE’;

TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
—————————— ————– ———- ———- ———–
CUSTOMERS_OBE

– indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows,
leaf_blocks, distinct_keys
from user_indexes
where table_name = ‘CUSTOMER_OBE’
order by index_name;

INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
———————— ————– ———- ———– ————-
OBE_CUST_CRED_LMT_IND

– columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct,
num_nulls, density
from user_tab_columns
where table_name = ‘CUSTOMER_OBE’
order by column_name;

COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH

15 rows selected.

But now check to see if you have private statistics.

– tables
select table_name, last_analyzed “analyze time”, num_rows, blocks, avg_row_len
from user_tab_private_stats
where table_name = ‘CUSTOMER_OBE’ and partition_name is null;

TABLE_NAME analyze time NUM_ROWS BLOCKS AVG_ROW_LEN
—————————— ————– ———- ———- ———–
CUSTOMERS_OBE 03/29 13:33:01 630 12 137.646032

– indexes
select index_name, last_analyzed “analyze time”, num_rows,
leaf_blocks, distinct_keys
from user_ind_private_stats
where table_name = ‘CUSTOMER_OBE’ and partition_name is null
order by index_name;

INDEX_NAME analyze time NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
———————— ————– ———- ———– ————-
OBE_CUST_CRED_LMT_IND 03/29 13:33:02 630 2 8

– columns
select column_name, last_analyzed “analyze time”, num_distinct,
num_nulls, density
from user_col_private_stats
where table_name = ‘CUSTOMER_OBE’ and partition_name is null
order by column_name;

COLUMN_NAME analyze time NUM_DISTINCT NUM_NULLS DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID 03/29 13:32:59 19 0 .052631579
CUST_CITY 03/29 13:32:59 300 0 .003333333
CUST_CREDIT_LIMIT 03/29 13:32:59 8 0 .125
CUST_EMAIL 03/29 13:32:59 400 0 .0025
CUST_FIRST_NAME 03/29 13:32:59 450 0 .002222222
CUST_GENDER 03/29 13:32:59 2 0 .5
CUST_ID 03/29 13:32:59 630 0 .001587302
CUST_INCOME_LEVEL 03/29 13:32:59 12 0 .083333333
CUST_LAST_NAME 03/29 13:32:59 400 0 .0025
CUST_MAIN_PHONE_NUMBER 03/29 13:32:59 630 0 .001587302
CUST_MARITAL_STATUS 03/29 13:32:59 2 234 .5
CUST_POSTAL_CODE 03/29 13:32:59 301 0 .003322259
CUST_STATE_PROVINCE 03/29 13:32:59 120 0 .008333333
CUST_STREET_ADDRESS 03/29 13:32:59 630 0 .001587302
CUST_YEAR_OF_BIRTH 03/29 13:32:59 66 0 .015151515

15 rows selected.

All of the statistics on the CUSTOMERS_OBE table are private. If you wanted to test the new statistics
you could export them from the private statistics table and import them into a test system.
If they were found to be unacceptable you could simply delete them from the private statistics tables
without effecting production.

To test a query without using the private statistics do:
alter session set optimizer_private_statistics = false;
alter session set optimizer_dynamic_sampling = 0;

Now you can get and display an explain plan for your query.

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

Explained.

set linesize 140
select plan_table_output plan from table(dbms_xplan.display(’plan_table’,null,’serial’))
/

PLAN
——————————————————————————————————————————————–
Plan hash value: 278193547

———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 208 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS_OBE | 1 | 208 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUSTOMERS_OBE | 1 | | 1 (0)| 00:00:01 |
———————————————————————————————

Predicate Information (identified by operation id):
—————————————————

PLAN
——————————————————————————————————————————————–

2 - access(”CUST_CREDIT_LIMIT”=1500)

This is not a very optimal plan since 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 OBE_CUSTOMERS 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 private statistic tables, you need to set
optimizer_private_statistics to true by running the following script:

alter session set optimizer_private_statistics = true;

Session altered.

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

Explained.

PLAN
——————————————————————————————————————————————–
Plan hash value: 520139036

———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 79 | 10586 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 79 | 10586 | 4 (0)| 00:00:01 |
———————————————————————————–

Predicate Information (identified by operation id):
—————————————————

PLAN
——————————————————————————————————————————————–

1 - filter(”CUST_CREDIT_LIMIT”=1500)

13 rows selected.

The OBE Guide states that this plan with the private 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. We can make them public by executing the following script:

exec dbms_stats.publish_private_stats(null, null);

PL/SQL procedure successfully completed.

Now if you check the public statistics for the CUSTOMERS_OBE table you should see all of the statistics you gathered earlier.

– tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = ‘CUSTOMERS_OBE’;

TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
—————————— ————– ———- ———- ———–
CUSTOMERS_OBE 03/29 13:33:01 630 12 137

– indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows,
leaf_blocks, distinct_keys
from user_indexes
where table_name = ‘CUSTOMERS_OBE’
order by index_name;

INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
—————————— ————– ———- ———– ————-
OBE_CUST_CRED_LMT_IND 03/29 13:33:02 630 2 8

– columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct,
num_nulls, density
from user_tab_columns
where table_name = ‘CUSTOMERS_OBE’
order by column_name;

COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID 03/29 13:32:59 19 0 .052631579
CUST_CITY 03/29 13:32:59 300 0 .003333333
CUST_CREDIT_LIMIT 03/29 13:32:59 8 0 .125
CUST_EMAIL 03/29 13:32:59 400 0 .0025
CUST_FIRST_NAME 03/29 13:32:59 450 0 .002222222
CUST_GENDER 03/29 13:32:59 2 0 .5
CUST_ID 03/29 13:32:59 630 0 .001587302
CUST_INCOME_LEVEL 03/29 13:32:59 12 0 .083333333
CUST_LAST_NAME 03/29 13:32:59 400 0 .0025
CUST_MAIN_PHONE_NUMBER 03/29 13:32:59 630 0 .001587302
CUST_MARITAL_STATUS 03/29 13:32:59 2 234 .5
CUST_POSTAL_CODE 03/29 13:32:59 301 0 .003322259
CUST_STATE_PROVINCE 03/29 13:32:59 120 0 .008333333
CUST_STREET_ADDRESS 03/29 13:32:59 630 0 .001587302
CUST_YEAR_OF_BIRTH 03/29 13:32:59 66 0 .015151515

15 rows selected.

Then check the private statistics for the CUSTOMERS_OBE table. From your terminal window, execute the following script:
– tables
select table_name, last_analyzed “analyze time”, num_rows, blocks, avg_row_len
from user_tab_private_stats
where table_name = ‘CUSTOMERS_OBE’ and partition_name is null;

– indexes
select index_name, last_analyzed “analyze time”, num_rows,
leaf_blocks, distinct_keys
from user_ind_private_stats
where table_name = ‘CUSTOMERS_OBE’ 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_private_stats
where table_name = ‘CUSTOMERS_OBE’ and partition_name is null
order by column_name;

3 times no rows selected

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

#######
Conclusion: In 11g Enhanced Optimizer Statistics Maintenance is possible.
The default behaviour is the same as in 10g, publish automatically.

Beware to document / monitor private statistics areas. They could have a big impact on the explain plan and
on the performance of the queries on those objects.

Popularity: 264 points