Oracle database 11gR2 upgrades: advanced tips and best practices

The Strugatsky brothers have a book where in the army the officer asks the soldiers if they are ready to attack the enemy. One of the men says yes but do we have incense? Why do we need incense, asks the army officer? Well, in case we meet the devil says the soldier.

Do I really need incense when upgrading to 11g? Let us see.

According to the 2011 IOUG Survey on Database Manageability, keeping databases at current patch levels is the primary DBA challenge. Of course, in order to have that current level you need to upgrade/patch the database.

For basic tips and best practices on how to upgrade to 11gR2, read carefully Best Practices for Upgrading to Oracle Database 11g Release 2, the issues I describe below come on top of Oracle’s document and are based on hands-on experience, I am far from advertising how simple the 11g upgrade is:

1. Control the size of the SYSAUX tablespace. It grows due to two main reasons: the CBO historical statistics do not get purged automatically and the SQL Plan Baselines consume gigabytes of disk space.

Have a look at these:

Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX [ID 9910484.8]
Bug 8553944 - SYSAUX tablespace grows [ID 8553944.8]
Bug 10279045 - Slow Statistics purging (SYSAUX grows) [ID 10279045.8]
Bug 12958182 - SYSAUX tablespace grows (mon_mods_all$ and col_usage$) [ID 12958182.8]
Bug 13632540 - SQL PLAN BASELINES GENERATES TOO MUCH DATA ON LOB SEGMENT, SYS.SYS_LOBXXX

By default the MMON performs the automatic purge that removes all history older than the older of:

* current time – statistics history retention (by default 31 days) and
* time of recent analyze in the system – 1

MMON performs the purge of the optimizer stats history automatically but it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes then it’s aborted and the stats are not purged and no trace or alert message is reported.

You will have to manually start the DBMS_STATS.PURGE_STATS procedure or schedule a job to run it on daily basis.

Handling SM/OPTSTAT component growth in SYSAUX tablespace gives an excellent overview on the problem with the historical CBO statistics.

The bigger issue is with bug 13632540. It is not fixed in 11.2.0.3 and SQL Plan Baselines consume more space than historical CBO data. If you do not have much data in the DB, you may witness the unique situation of having a database with more metadata than real application data.

2. Enable Automatic SQL Tuning, Automatic Memory Management (not on Linux of course where HugePages are enabled) and use (carefully) SQL Plan Management.

While the automated features make sense, you might wonder doesn’t the urge to use SQL Plan Management contradict with point 1 above? Yes it does, and this makes the 11g upgrade tricky. Verify you can afford some extra space in SYSAUX if you have optimizer_capture_sql_plan_baselines = TRUE. Extra means like 10-50G. It varies from database to databases.

You may create a job that runs on regular basis: delete from sys.col_usage$ c where not exists (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = c.obj#); That is the workaround.

Automatic SQL Tuning is really an underestimated feature in the Oracle database and I am surprised to see so many databases where the feature is not enabled. What I see and read are different blogs and articles on how to enable and disable the feature but almost nothing on real life experience.

For SAP users: SAP note 105047 says that “SQL Plan Management” is allowed by SAP.

3. Use DataPump to populate a fresh database, avoid script upgrade if possible. The Oracle 11gR2 Database Upgrade Guide clearly states the 6 major benefits:

3.1 Defragments the data. You can compress the imported data to improve performance.

3.2 Restructures the database. You can create new tablespaces or modify existing tables, tablespaces, or partitions to be populated by imported data.

3.3 Facilitates side-by-side testing of the old and new versions of Oracle Database because an entirely new database is created.

3.4 Enables the copying of specified database objects or users. Importing only the objects, users, and other items you need is useful for establishing a test environment for the new software on only a subset of the production data. Data Pump Export / Import provides flexible data subsetting capabilities.

3.5 Serves as a backup archive – you can use a full database export as an archive of the current database.

3.6 Enables the upgraded database to be established on an operating system or hardware platform that is different from that which is supporting the database being upgraded.Network-based Data Pump Import allows the new Oracle database to be directly loaded across the network from the old database being upgraded. Thus, no intervening dump files are required.

I cannot prove it scientifically but from my experience fresh/new databases are faster and less buggier than manually upgraded databases. It is probably the fragmentation that is a factor, the messed up data dictionary being artificially modified to a higher version, etc.

4. Read the upgrade documents in advance or involve someone who has already read them and has strong experience with database upgrades. Avoid exotic parameters in the init.ora file (unless you are Siebel, EBS, etc.) and gather workload system and fixed table statistics after the upgrade. Check also the “Master Note For Oracle Database Upgrades and Migrations” [ID 1152016.1].

You might think that tip 4 contradicts the title of this post: advanced tips and best practices. What is so advanced with reading the friendly manuals? What indeed 🙂 Note that Oracle list “Read the FRIENDLY manuals!” as Best Practice #1 in the paper above.

Here is the path diagram:

Finally, some interesting statistics from the IOUG report:

Don’t make plans, make options

An old Chinese proverb says: “When planning for a year, plant corn. When planning for a decade, plant trees. When planning for life, train and educate people.”

When new IT applications are created, how often does someone plan about the future of the data? Its growth, reorganization, what is being logged, how often unnecessary data is purged and deleted; what is being audited and how? I guess we all know the answer. Here is a picture from The Data Affordability GAP Widens – Year by Year

Building the Knowledge! That is the starting point. Andy Flower, the president of IOUG, wrote an excellent article in the January/February 2012 issue of Oracle Magazine entitled “Building the Knowledgebase”.

Andy Flower says: “30 percent of organizations have seen data grow at an annual rate of more than 25 percent, with 10 percent seeing greater than 50 percent growth. 27 percent of respondents currently have more than 100 TB of data. Nearly 1 out of 10 sites now has data stores in the petabyte range.”

1. Unnecessary data is seldom deleted, purged or archived. I have often (rather quite often) seen databases where less that 10% of the data stored is used on daily basis. Oracle AWR reports can provide you excellent information where the physical and logical reads come from and with what percentage. Here is a simple example:

2. Databases often get fragmented, and they need regular reorganization. An interesting example is a global database which was 7TB in size (now bigger I guess) with real data less than 1TB. If that’s not a classical example of database fragmentation, then please give me a better one. Datafiles had so much white space! You may use this query to check for datafile fragmentation:

set lines 256
set pages 999
col "File Name" for A47
column file_name format a40; 
column highwater format 9999999999; 
SELECT  /*+ RULE */ df.File_id, Substr(df.file_name,1,47) "File Name",
        Round(df.bytes/1024/1024,2) "Size (M)",
        Round(e.used_bytes/1024/1024) "Used (M)",
        Round(f.free_bytes/1024/1024) "Free (M)",
        round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) "HWM (M)"
FROM    dba_data_files df,
        (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) e,
        (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f,
        (SELECT file_id, max(block_id) maximum from dba_extents group by file_id) b,
        dba_extents c,
        (SELECT value db_block_size from v$parameter where name='db_block_size') d
WHERE   e.file_id (+) = df.file_id
AND     df.file_id = f.file_id (+)
AND     df.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum
ORDER BY
        df.tablespace_name, df.file_name
/

I am sorry about the RULE hint but it really runs faster with RBO.

Two key benefits of regular database reorganization are:

– better performance as data and indexes are spread over less blocks and thus less I/O and CPU are needed to access the data (and less memory of course)
– backups take less time and occupy less storage and tape (accordingly restore & recovery will be faster)

3. Databases are copied one-to-one from environment to another when data refresh is needed. Just like that. Copy and Paste. Not really, but often with the RMAN’s command “duplicate target database to ..” Have a look at what Oracle has to offer these days: the Oracle Test Data Management Pack for Oracle and non-Oracle Databases. The key benefits of the pack are:

– Reduce application storage costs dramatically through data subsetting
– Increase developer productivity through right-sized production copies
– Eliminate labor-intensive error-prone manual process to create test systems by automating application discovery, data relationship modeling, dynamic subset rules and parameter-driven execution

One really doesn’t need all the data from Production in the Development or the Test environment!

4. Data growth is often not monitored and seldom one knows which data can be deleted. I have just 3 words for this: “Oracle Cloud Control”.

A very detailed explanation of the problem is described by IBM in Control application data growth before it controls your business. Let me quote part of the executive summary:

“What are these effects? The most obvious are complexity and risk, along with storage and management costs. Data that has accumulated at a significant rate is highly distributed over multiple applications, databases and platforms creating complicated data relationships that are difficult to define, understand, control and house. Also, managing years of historical data in production databases can impact service levels and disaster recovery initiatives. Expanding online and batch processing windows, along with routine maintenance tasks, takes much more time and can reduce application availability significantly.”

Wonder why people started all of a sudden talk about Big Data?