21-Jan-2011 (Friday): A half a day with sheela for an 11g "kick-off" meeting.
This feature captures the actual database workload on the production system and replays it on the test system.
(OEM to collect baseline vs. Database Replay)
Performance Analyzer allows you to evaluate the impact of a change such as database upgrade by identifying the SQL statements impacted by the upgrade and measuring their performance divergence.
SQL Plan Management
SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.
Questions For Oracle:
- Does 11g installs a new version of the optimizer? (SQL Plan Management: http://download.oracle.com/docs/html/E10819_02/preup.htm#BABHAFAH)
- What is the Pre-Upgrade Information Tool?
COMPUTE STATISTICS and ESTIMATE STATISTICS Clauses
In earlier releases, the ANALYZE...COMPUTE STATISTICS and ANALYZE...ESTIMATE STATISTICS clauses could be used to start or stop the collection of statistics on an index. These clauses have been made obsolete. Oracle Database 11g Release 1 (11.1) automatically collects statistics during index creation and rebuild. These clauses are no longer supported and using them causes errors.
PLSQL_DEBUG is deprecated.
You should use PLSQL_OPTIMIZE_LEVEL instead. A deprecation warning is issued if PLSQL_DEBUG is used. This may effect T.O.D.E and/or SqlDeveloper.
Starting with Oracle Database 11g Release 1 (11.1), JOB_QUEUE_PROCESSES has changed from a basic initialization parameter to a non-basic initialization parameter. Most databases should be required to have only basic parameters set to run properly and efficiently.
In earlier Oracle Database releases, DBMS_JOB and DBMS_SCHEDULER shared the same job coordinator, and its behavior was controlled by the JOB_QUEUE_PROCESSES parameter. Now DBMS_JOB and DBMS_SCHEDULER work without setting this initialization parameter. You can still set it if you want, but you are no longer required to set it.
The range of supported values for JOB_QUEUE_PROCESSES is still 0to1000. If it is set to 0, then DBMS_SCHEDULER jobs run and DBMS_JOB jobs do not run. The number of slave processes created for DBMS_SCHEDULER jobs is autotuned based on the load of the computer.
If JOB_QUEUE_PROCESSES is set to a value in the range 1to1000, then both DBMS_JOB jobs and DBMS_SCHEDULER jobs run, and the number of slave processes created for these jobs is autotuned with an additional restriction that the total number of slave processes is capped at the value of JOB_QUEUE_PROCESSES.
After upgrading from a release prior to Oracle Database 10g Release 2 (10.2), the CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade.
Pro*C/C++ applications running against a new Oracle Database 11g instance should be tested to ensure that there are no problems with the new software.
Subtle changes in the new Oracle Database 11g release, such as data types, data in the data dictionary (additional rows in the data dictionary, object type changes, and so on) can have an effect all the way up to the front-end application, even if the application is not directly connected to a new Oracle Database 11g instance.
If the connection between two components involves Net8 or Oracle Net Services, then those connections should also be tested and stress tested.
Pre-Upgrade Information Tool
After you have installed Oracle Database 11g Release 2 (11.2) and any required patches, you should analyze your database before upgrading it to the new release. This is done by running the Pre-Upgrade Information Tool. This is a required step if you are upgrading manually, otherwise the catupgrd.sql script will terminate with errors. Running the Pre-Upgrade Information Tool is also recommended if you are upgrading with DBUA, so that you can preview the items that DBUA checks.
When upgrading to the new Oracle Database 11g release, optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.
To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. As of Oracle Database 10g Release 1 (10.1), Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, you can enter the following:
Any invalid SYS/SYSTEM objects found prior to upgrading the database will be stored in the table named registry$sys_inv_objs. Any invalid non-SYS/SYSTEM objects found prior to upgrading the database will be stored in registry$nonsys_inv_objs. After the upgrade, run ORACLE_HOME/rdbms/admin/utluiobj.sql to identify any new invalid objects due to the upgrade.
Purging the Database Recycle Bin
Our SDCDWHS gives ora-600 when trying this
Use the PURGE statement to remove items and their associated objects from the recycle bin and release their storage space:
It is REQUIRED that the recycle bin be empty during the upgrade to avoid possible ORA-00600 errors as well as to minimize the upgrade time.