Force Statistics

I had a situation where I had to “force” statistics for particular tables, this is how I did it.


#!/bin/ksh
#
# Forcing stats for specific tables
#
# --   dbms_stats.set_table_stats( 'PPTEST', 'IMAGE306', numrows=> 2000000 );
#
####################################################################################

sqlplus -s "/ as sysdba" <<EOF --begin --   dbms_stats.set_table_stats( 'PPTEST', 'ITEM301', numrows=> 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'ITEM302', numrows=> 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'ITEM303', numrows=> 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'ITEM305', numrows=> 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'ITEM306', numrows=> 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'ITEM307', numrows=> 2000000 );
--end;
--/

begin
--   dbms_stats.set_table_stats( 'PPTEST', 'IMAGE301', numrows=&gt; 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'IMAGE302', numrows=&gt; 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'IMAGE303', numrows=&gt; 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'IMAGE305', numrows=&gt; 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'IMAGE306', numrows=&gt; 2000000 );
--   dbms_stats.set_table_stats( 'PPTEST', 'IMAGE311', numrows=&gt; 2000000 );
--   dbms_stats.lock_table_stats( 'PPTEST', 'IMAGE311');
--   dbms_stats.set_table_stats( 'PPTEST', 'IMAGE312', numrows=&gt; 2000000 );
--   dbms_stats.lock_table_stats( 'PPTEST', 'IMAGE312');
--   dbms_stats.set_table_stats( 'PPTEST', 'ITEM307', numrows=&gt; 2000000 );
end;
/
--select num_rows from dba_tables where table_name='IMAGE301';
--select num_rows from dba_tables where table_name='IMAGE302';
--select num_rows from dba_tables where table_name='IMAGE303';
--select num_rows from dba_tables where table_name='IMAGE305';
--select num_rows from dba_tables where table_name='IMAGE306';
--select num_rows from dba_tables where table_name='IMAGE311';
select num_rows from dba_tables where table_name='IMAGE312';
--select num_rows from dba_tables where table_name='ITEM301';
--select num_rows from dba_tables where table_name='ITEM302';
--select num_rows from dba_tables where table_name='ITEM303';
--select num_rows from dba_tables where table_name='ITEM305';
--select num_rows from dba_tables where table_name='ITEM306';
select num_rows from dba_tables where table_name='ITEM307';

EOF

 

 

 

DBMS_STATS

This procedure sets table-related information.

Syntax
DBMS_STATS.SET_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
numrows NUMBER DEFAULT NULL,
numblks NUMBER DEFAULT NULL,
avgrlen NUMBER DEFAULT NULL,
flags NUMBER DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param(‘NO_INVALIDATE’)),
cachedblk NUMBER DEFAULT NULL,
cachehit NUMBER DEFUALT NULL,
force BOOLEAN DEFAULT FALSE);

Parameter

Description

ownname Name of the schema.
tabname Name of the table.
partname Name of the table partition in which to store the statistics. If the table is partitioned and partname is NULL, then the statistics are stored at the global table level.
stattab User statistics table identifier describing where to store the statistics. If stattab is NULL, then the statistics are stored directly in the dictionary.
statid Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).
numrows Number of rows in the table (partition).
numblks Number of blocks the table (partition) occupies.
avgrlen Average row length for the table (partition).
flags For internal Oracle use (should be left as NULL).
statown Schema containing stattab (if different than ownname).
no_invalidate If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
cachedblk The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition).
cachehit The average cache hit ratio for the segment (index/table/index partition/table partition).
force Sets the values even if statistics of the table are locked.

Usage Notes

The Optimizer uses the cached data to estimate number of cached blocks for index or statistics table access. The total cost of the operation will be combined from the I/O cost of reading not cached blocks from disk, the CPU cost of getting cached blocks from the buffer cache, and the CPU cost of processing the data.

Oracle maintains cachedblk and cachehit at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a ‘confidence factor’ for each cachehit and a cachedblk for each object. If the ‘confidence factor’ for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.

The automatic maintenance algorithm for object caching statistics assumes that there is only one major workload for the system and adjusts statistics to this workload, ignoring other “minor” workloads. If this is not the case, you must use manual mode for maintaining object caching statistics.

The object caching statistics maintenance algorithm for auto mode prevents you from using statistics in the following situations:
•When not enough data has been analyzed, such as when an object has been recently create

•When the system does not have one major workload resulting in averages not corresponding to real values.

Exceptions
•ORA-20000: Object does not exist or insufficient privileges.

•ORA-20001: Invalid input value.

•ORA-20005: Object statistics are locked.