Sunday, September 28, 2014

Document for exporting existing STATSISTICS before Gathering Statistics:

Metalink Reference:

Complete Checklist for Manual Upgrades to 10gR2
Note:316889.1

This Document is to export the existing statistics before gathering statistics. So that if we no need for the latest statistics we can delete the existing stats and import the old statistics.

This is Recommended for 4 users to run stats while Upgrading for 10g R1 to R2 . They are as follows:

Gather schema stats analyse:
======================
1. SYS
2. ODM
3. OLAPSYS
4. MDSYS

Step 1: Now take backup of existing stats for the following four users :
====================================================


1.sqlplus /as sysdba
2.spool sdict
3. grant analyze any to sys;
4.exec dbms_stats.create_stat_table('SYS','dictstattab')

Step 2: Now run the as the stats table package is created now we are going to export the existing stats for the four users by:
==================================================================================================

exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS')
exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS')
exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS')
exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS')

Step 3: To run the Gather statistics :
============================

1. sqlplus '/as sysdba'
2. spool gdict
3. grant analyze any to sys;

exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

spool off;


step 4 : OPTIONAL (ONLY WHEN WE NEED THE OLD STATISTICS
===================================================
If we want to have this existing statistics (ie) old statistics which we have before running the above gather stats , We need to do the below steps:

1. Delete the analysed schema by using :

exec dbms_stats.delete_schema_stats('SYS')

2. Import the above exported stats for that four users by

exec dbms_stats.import_schema_stats('SYS','dictstattab');

Script to check the Stale Schema

This Script will tell you for which schema we need to run the gather statistics before going for an upgrade and also for better performances.

-- - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - -

-- NAME: CHECK_STALE_STATS.SQL
-- Executed as SYS as sysdba

-- PURPOSE:
-- This script is an automated way to deal with stale statistics operations that are
-- required to be done as part of manual upgrade OR when reported by DBUA.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
--

SET FEEDBACK OFF
SET LINESIZE 250
SET SERVEROUTPUT ON

DECLARE
-- Variables declared
P_OTAB DBMS_STATS.OBJECTTAB;
MCOUNT NUMBER := 0;
P_VERSION VARCHAR2(10);
-- Cursor defined
CURSOR c1
IS
SELECT distinct schema
FROM dba_registry
ORDER by 1;

-- Beginning of the anonymous block
BEGIN
-- Verifying version from v$instance
SELECT version INTO p_version FROM v$instance;
DBMS_OUTPUT.PUT_LINE(chr(13));
-- Defining Loop 1 for listing schema which have stale stats
FOR x in c1
LOOP
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>x.schema,OPTIONS=>'LIST AUTO',OBJLIST=>p_otab);

-- Defining Loop 2 to find number of objects containing stale stats
FOR i in 1 .. p_otab.count
LOOP
IF p_otab(i).objname NOT LIKE 'SYS_%'
AND p_otab(i).objname NOT IN ('CLU$','COL_USAGE$','FET$','INDPART$',
'MON_MODS$','TABPART$','HISTGRM$',
'MON_MODS_ALL$',
'HIST_HEAD$','IN $','TAB$',
'WRI$_OPTSTAT_OPR','PUIU$DATA',
'XDB$NLOCKS_CHILD_NAME_IDX',
'XDB$NLOCKS_PARENT_OID_IDX',
'XDB$NLOCKS_RAW OKEN_IDX', 'XDB$SCHEMA_URL',
'XDBHI_IDX', 'XDB_PK_H_LINK')
THEN
-- Incrementing count for each object found with statle stats
mcount := mcount + 1;
END IF;
-- End of Loop 2
END LOOP;

-- Displays no stale statistics, if coun is 0
IF mcount!=0
THEN
-- Displays Schema with stale stats if count is greater than 0
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- ' x.schema ' schema contains stale statistics use the following to gather the statistics ''--');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
--DBMS_OUTPUT.PUT_LINE('');

-- Displays Command to be executed if schema with stale statistics is found depending on the version.
IF SUBSTR(p_version,1,5) in ('8.1.7','9.0.1','9.2.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''x.schema''',OPTIONS=>''''GATHER'''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ''''FOR ALL COLUMNS SIZE AUTO'''', CASCADE => TRUE);');
ELSIF SUBSTR(p_version,1,6) in ('10.1.0','10.2.0','11.1.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('''x.schema''',OPTIONS=>''''GATHER'''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => ''''FOR ALL COLUMNS SIZE AUTO'''', CASCADE => TRUE);');
ELSE
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('Version is 'p_version);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('-- There are no stale statistics in ' x.schema ' schema.');
DBMS_OUTPUT.PUT_LINE(chr(13));
END IF;
-- Reset count to 0.
mcount := 0;
-- End of Loop 1
END LOOP;
END;
/

SET FEEDBACK ON
-- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - - -



Note:

If it is a 9i and previous then it will say to run gather schema stats . If it is 10g and above it will tell you to run gather dictionary stats. and Usually it will always says to run SYS schema. But it is ok if you run once. But if it is suggested for some other schema other then sys then it should be ran till it is not stale.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)