10g Upgrade – issues with DBMS_STATS package

Last week, one of our clients production database was upgraded from 9i to 10g.  Since then they were facing lot of performance problems.  They cannot pin point the problem at a particular place, so the problem seems to be “overall”.  I did not have access to the production box, all I had was an AWR report generated during the time of performance degradation.  When I dug into the report, I found that the instance performance is generally ok.  The problem was with most of the application queries – too many physical reads.

I remember I read an article from Jonathan Lewis on his blog regarding performance degradation after 10g upgrade.  I quickly went there and did a search and found out this URL http://jonathanlewis.wordpress.com/2007/02/02/10g-upgrade/, excellently explaining the change in the behaviour of dbms_stats package.  In short.

“If you didn’t specify a method_opt in your scripts under 9i you were not generating histograms; but under 10g you will be collecting histograms on any columns that Oracle thinks might be suitable candidates.” 

I then checked the AWR report to see if there was anything recorded regarding DBMS_STATS, indeed I found out that they were using defualt method_opt

dbms_stats.gather_table_stats(
OwnName => 'ADMIN',tabname=>'T2838',estimate_percent => dbms_stats.auto_sample_size,
Granularity => 'DEFAULT',Degree => 4)

Which means they were generating histograms, but on 9i the same script would not have generated them.  This could be having a major impact on generating execution plans on 10g.  I replied them this, providing the Jonathan Lewis’ URL.

Later, they did reset the METHOD_OPT to 9i levels.

exec dbms_stats.set_param('METHOD_OPT', 'FOR ALL COLUMNS SIZE 1');

I am waiting to hear from them on whether there is any improvement in performance.

Note:  I later found Richard Foote has also written an excellent article on this : http://richardfoote.wordpress.com/2008/01/04/dbms_stats-method_opt-default-behaviour-changed-in-10g-be-careful/

Database refresh using cold backup

Sometime, you will be asked to refresh an UAT database using an coldbackup of production one.  Here are the steps :

1.  Prepare controlfile creation script.  This can be done by

alter database backup controlfile to trace as '/export/home/oracle/ctlfile.sql';

2.  View the edit ctlfile.sql, so that it should contain the following

CREATE CONTROLFILE SET DATABASE "UATDB" RESETLOGS NOARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 1
  MAXLOGHISTORY 453
LOGFILE
  GROUP 1 '/u01/oradata/UATDB/redo01.log' SIZE 200M,
  GROUP 2 '/u02/oradata/UATDB/redo02.log' SIZE 200M,
  GROUP 3 '/u03/oradata/UATDB/redo03.log' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/UATDB/system01.dbf',
  '/u01/oradata/UATDB/undotbs01.dbf',
  '/u01/oradata/UATDB/users01.dbf',
  '/u01/oradata/UATDB/data01.dbf',
  '/u01/oradata/UATDB/tools01.dbf',
  '/u01/oradata/UATDB/perfstat01.dbf',
  '/u01/oradata/UATDB/sysaux01.dbf'
CHARACTER SET WE8ISO8859P1
;

3. Gernerate script to create TEMP tablespace

set long 2000
select DBMS_METADATA.GET_DDL('TABLESPACE','TEMP') from dual;

4. Shutdown UATDB cleanly

shutdown immediate

5. Copy the cold backup files from production to UAT box.

6. Start the database in nomount state
 
startup nomount

7. Run the create control file script

@/export/home/oracle/ctlfile.sql

8. Open the database with resetlogs

alter database open resetlogs;

9. Create temp tablespace from the script generated from step 3:

CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE
'/u01/oradata/UATDB/TEMP01.dbf' SIZE 5242880000
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576