Recommendation for gather stats for big database where it is not completed in scheduled window in oracle 11g.
If we have any clone database of production, We can run gather stats on that database ,export from clone database and import gather stats on Production database
Enviroment
Clone Database = Source Database
Production Dataase = Target Server (Target Database )
Summary
On Source
1. Gather stats for schema TEST_DBA
2. Create table(NEW_STATS_NOV2017 ) to store statistics
3. Store schema stats to table STATS_TABLE
4. Export the table STATS_TABLE using datapump or exp
5. Transfer the dump to target server
On Target
1. Delete the stats before import on target server
2. Import using impdp or imp
3. Importing stats into same schema dbms_stats
4. Importing into different schema
1) Create dynamic sql for statistics and run on clone database
select 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','||''''|| table_name||''''||',CASCADE=>TRUE, DEGREE=>10, ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'');' from dba_tables where owner in ('TEST_DBA')
and last_analyzed < sysdate - 8
2. Create Stat table in clone database .
EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','NEW_STATS_NOV2017','USERS');
3. Store new stats in stat table on clone database.
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('TEST_DBA','NEW_STATS_NOV2017',STATOWN=>'SYS');
4. Take export backup of stat table from clone database.
exp tables=NEW_STATS_NOV2017 file=NEW_STATS_NOV2017.dmp log=_EXP_NEW_STATS_NOV2017.log recordlength=64445
5. FTP export backup from clone database(Source Database) server to production server(Targe) in binary mode.
On Target
----------------------------------------------------------------------------------------------------
1. Create another stats table in production database on Saturday any time
EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','OLD_STATS_NOV2017','USERS');
----------------------------------------------------------------------------------------------------
2. Export existing stats in new stat table on production database.
(To take backup of existing stats)
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('TEST_DBA','OLD_STATS_NOV2017',STATOWN=>'SYS');
----------------------------------------------------------------------------------------------------
3. Import new stats table on production database from export backup file.
imp full=y file=NEW_STATS_NOV2017.dmp log=IMP_NEW_STATS_NOV2017.dmp
----------------------------------------------------------------------------------------------------
4. Import stats from new stats table on production database.
(Stop listener and put database in restricted mode)
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('TEST_DBA','NEW_STATS_NOV2017',STATOWN=>'SYS');
----------------------------------------------------------------------------------------------------
5. Take stats of SYS schema in production server.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
============END=============================================
SQL>
SQL> show parameter pending
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
SQL>
exec dbms_stats.set_schema_prefs('PILOT_DBA', 'PUBLISH', 'false');
select 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','||''''|| table_name||''''||',CASCADE=>TRUE, DEGREE=>10, ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'');' from dba_tables
where owner in ('TEST_DBA')
and last_analyzed < sysdate - 8
or
dbms_stats.gather_schema_stats
As earlier mentioned, statistics for a table are published immediately by default, so we use the DBMS_STATS package to change this default behaviour.
SQL> exec dbms_stats.set_table_prefs('SH','SALES','PUBLISH','FALSE');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'SALES' ) FROM DUAL;
DBMS_STATS.GET_PREFS('PUBLISH','SH','SALES')
----------------------------------------------------------------------------------------------
FALSE
EXEC dbms_stats.gather_schema_stats('PILOT_DBA', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, degree =>DBMS_STATS.DEFAULT_DEGREE, granularity => 'ALL', CASCADE => TRUE);
SELECT TABLE_NAME,PARTITION_NAME ,LAST_ANALYZED FROM DBA_TAB_PENDING_STATS;
alter session set OPTIMIZER_USE_PENDING_STATISTICS=TRUE;
or
exec dbms_stats.publish_pending_stats('QUEST',null);
if issue,pending area could be discarded
alter session set OPTIMIZER_USE_PENDING_STATISTICS=FALSE;
2. Create Stat table in TestDB server.
EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','NEW_STATS_NOV2017','USERS');
3. Export new stats in stat table.
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('TEST_DBA','NEW_STATS_NOV2017',STATOWN=>'SYS');
4. Take export backup of stat table.
exp tables=NEW_STATS_NOV2017 file=NEW_STATS_NOV2017.dmp log=_EXP_NEW_STATS_NOV2017.log recordlength=64445
5. FTP export backup from trdprd server to production server(milldale) in binary mode.
----------------------------------------------------------------------------------------------------
6. Create another stat table in production server(camprd) on saturday any time
EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','OLD_STATS_NOV2017','USERS');
----------------------------------------------------------------------------------------------------
7. Export existing stats in new stat table. (To take backup of existing stats)
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('TEST_DBA','OLD_STATS_NOV2017',STATOWN=>'SYS');
----------------------------------------------------------------------------------------------------
8. Import new stat table from export backup file.
imp full=y file=NEW_STATS_NOV2017.dmp log=IMP_NEW_STATS_NOV2017.dmp
----------------------------------------------------------------------------------------------------
9. Import stats from new stat table. (Stop listener and put database in restricted mode)
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('TEST_DBA','NEW_STATS_NOV2017',STATOWN=>'SYS');
----------------------------------------------------------------------------------------------------
10. Take stats of SYS schema in production server.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
=======================final==================
1-check current setting of statistic preference statistics at datbase level
show parameter optimizer_use_pending_statistics
2) sets the preference for the PUBLISH parameter to false (default=true) for
pilot_dba schema.So that the database won’t automatically publish new the statistics
where as It will write them to the 'pending' area, not directly to the data dictionary.
exec dbms_stats.set_schema_prefs('PILOT_DBA', 'PUBLISH', 'false');
or
alter session set OPTIMIZER_USE_PENDING_STATISTICS=FALSE;
3) Gether stats either table or schema level
Table level syntax
select 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','||''''|| table_name||''''||',CASCADE=>TRUE, DEGREE=>10, ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'');' from dba_tables
where owner in ('TEST_DBA')
and last_analyzed < sysdate - 8
or
schema level sysntax
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME =>'TEST_DBA',CASCADE=>TRUE,DEGREE=>15, ESTIMATE_PERCENT=>100,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1);
or
exec dbms_stats.gather_schema_stats(ownname=>'TEST_DBA',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>TRUE, degree=>4);
Now 2 sets of statistics for objects in the TEST_DBA schema will be available - the current statistics still in the data dictionary and the new ones in the pending area.
4) By setting the (dynamic) initialisation parameter 'optimizer_use_pending_statistics' to 'true' the statistics in the pending area will be used
alter session set OPTIMIZER_USE_PENDING_STATISTICS=TRUE;
or
exec dbms_stats.publish_pending_stats('TEST_DBA',null);
Perform your tests by running a workload against the schema and checking the performance and the execution plans
If you’re happy with the new set of (pending) statistics, make them public by executing this statement:
5) rollback plan
if issue,pending area could be discarded
alter session set OPTIMIZER_USE_PENDING_STATISTICS=FALSE;
or
exec dbms_stats.set_schema_prefs('TEST_DBA', 'PUBLISH', 'false');
No comments:
Post a Comment