Want to post your scripts here in this blog ? Email it to snojha@gmail.com. You will be rewarded for your scripts if selected by our experts.

Wednesday, May 20, 2009

Find the tables/indexes that have stale statistics in a database

Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’
is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is
enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set
to BASIC table monitoring is disabled. In 9i one can enable/disable table monitoring by
calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/

SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE


Note: To find schema level stats that are stale one can call
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);

Detailed report of all invalid objects in the database.

SET LINESIZE 145
SET PAGESIZE 9999

clear columns
clear breaks
clear computes

column owner format a25 heading 'Owner'
column object_name format a30 heading 'Object Name'
column object_type format a20 heading 'Object Type'
column status format a10 heading 'Status'

break on owner skip 2 on report
compute count label "" of object_name on owner
compute count label "Grand Total: " of object_name on report

SELECT
owner
, object_name
, object_type
, status
FROM dba_objects
WHERE status <> 'VALID'
ORDER BY owner, object_name
/

Truncating Listener Log File

$cp $ORACLE_HOME/network/log/listener.log $ORACLE_HOME/network/log/listener.log_`date +"%Y%m%d"`

$cat /dev/null > $ORACLE_HOME/network/log/listener.log

$nohup compress $ORACLE_HOME/network/log/listener.log_`date +"%Y%m%d"` &

Refreshing stale statistics using the DBMS_STATS package and the GATHER AUTO option.

BEGIN
DBMS_STATS.gather_schema_stats (
ownname => USER,
cascade => TRUE,
options => 'GATHER AUTO');
END;
/

The results of the statistics refresh are evident in the %_TABLES views, as shown below.

SELECT table_name,
num_rows,
avg_row_len
FROM user_tables;

TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
DEPARTMENTS 1000 21

To automate the gathering of statistics, simply schedule a job to the call the DBMS_STATS package. The example below refreshes stale statistics for for schema "TIM" at midnight every day.

SET SERVEROUTPUT ON
DECLARE
l_job BINARY_INTEGER;
BEGIN
DBMS_JOB.submit (
job => l_job,
what => 'BEGIN DBMS_STATS.gather_schema_stats (' ||
'ownname => ''TIM'', cascade => TRUE, options => ''GATHER AUTO''); END;',
next_date => SYSDATE,
interval => '/* 1 Day Interval */ TRUNC(SYSDATE) + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || TO_CHAR(l_job));
END;
/

Alternatively we could set monitoring on for all database tables, with the exception of those in the SYS schema, and use the following procedure:

DBMS_STATS.gather_database_stats(cascade => TRUE, options => 'GATHER AUTO');

Since only those tables with stale or empty stats are processed the overhead on the system is greatly reduced.

Note. In Oracle 10g, gathering stale statistics for the database once a day happens by default.

Statistics on Tables, Indexes and Columns

Statistics on tables, indexes, and columns are stored in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view (USER, ALL, or DBA). These DBA_* views include the following:

* DBA_TABLES
* DBA_OBJECT_TABLES
* DBA_TAB_STATISTICS
* DBA_TAB_COL_STATISTICS
* DBA_TAB_HISTOGRAMS
* DBA_INDEXES
* DBA_IND_STATISTICS
* DBA_CLUSTERS
* DBA_TAB_PARTITIONS
* DBA_TAB_SUBPARTITIONS
* DBA_IND_PARTITIONS
* DBA_IND_SUBPARTITIONS
* DBA_PART_COL_STATISTICS
* DBA_PART_HISTOGRAMS
* DBA_SUBPART_COL_STATISTICS
* DBA_SUBPART_HISTOGRAMS

Oracle 10g Data Dictionary Table Statistics

To collect statistics on dictionary objects, execute the following statements. The collection process can be done in different formats.

Format (1)
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

Format (2)
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

Format (3)
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

As seen in the above examples, the gather_schema_stats procedure accepts the sys argument to perform statistics collections. In case of the gather_database_stats procedure, there is a new argument gather_sys whose default value is FALSE. When needed, you can set it to TRUE and execute the procedure.

There is another procedure, delete_dictionary_stats, which allows you to remove data dictionary stats.

When you execute the gather_dictionary_stats procedure, it gathers statistics from the SYS and SYSTEM schemas, as well as any other schemas that are related, such as OUTLN or DBSNMP schemas.