Copy the cold backup files from production to UAT box.ħ. Select DBMS_METADATA.GET_DDL('TABLESPACE','TEMP') from dual ĥ. Gernerate script to create TEMP tablespace GROUP 2 '/u02/oradata/UATDB/redo02.log' SIZE 200M, GROUP 1 '/u01/oradata/UATDB/redo01.log' SIZE 200M, View the edit ctlfile.sql, so that it should contain the followingĬREATE CONTROLFILE SET DATABASE 'UATDB' RESETLOGS NOARCHIVELOG

This can be done byĪlter database backup controlfile to trace as '/export/home/oracle/ctlfile.sql' Ģ. Sometime, you will be asked to refresh an UAT database using an coldbackup of production one. Note: I later found Richard Foote has also written an excellent article on this : I am waiting to hear from them on whether there is any improvement in performance. Later, they did reset the METHOD_OPT to 9i levels.Įxec dbms_t_param('METHOD_OPT', 'FOR ALL COLUMNS SIZE 1') I replied them this, providing the Jonathan Lewis’ URL. This could be having a major impact on generating execution plans on 10g. Which means they were generating histograms, but on 9i the same script would not have generated them. OwnName => 'ADMIN',tabname=>'T2838',estimate_percent => dbms_to_sample_size, 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 “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 quickly went there and did a search and found out this URL, excellently explaining the change in the behaviour of dbms_stats package. I remember I read an article from Jonathan Lewis on his blog regarding performance degradation after 10g upgrade. The problem was with most of the application queries – too many physical reads. When I dug into the report, I found that the instance performance is generally ok. I did not have access to the production box, all I had was an AWR report generated during the time of performance degradation. They cannot pin point the problem at a particular place, so the problem seems to be “overall”. Since then they were facing lot of performance problems. Last week, one of our clients production database was upgraded from 9i to 10g.

Oracle scratchpad