Oracle10G New Features Part II

Oracle10G Enhancements Blog Part II

This is the second of two blogs on general Oracle10G features.  As I said in the first blog, consider these two to be the first of many blogs on Oracle10G, so we'll keep them at a high level.  We have a very active Oracle10G implementation project underway here at Giant Eagle.  Rest assured I will be keeping you up to date with all of the latest tidbits of information as we move forward.

Automatic SGA Management
Oracle has simplified the management of the Oracle SGA.  In previous releases, database administrators allocated chunks of memory to the different caches (data buffer, shared pool, large pool, java pool etc.) by setting their associated parameters in the database's parameter file.

Oracle10G has reduced the number of memory allocation parameters to two, one for the SGA and one for the PGA.  Oracle10G will divide the memory resources among the different SGA memory areas and change these allocations dynamically based on application workload changes.   For more detailed information, refer to my blog on Oracle10G Hidden Secrets.

During our initial Oracle10G beta testing, we found that allowing Oracle to control the memory allocations for our SGA did provide some benefits.  But members of my team have enough experience to set the individual parameters correctly on their own.  They are a knowledgeable bunch and enthusiastic about learning Oracle10G.  If I could only generate that same level of enthusiasm in them to attend my staff meetings, I'd be OK.   We will be testing automatic SGA management more thoroughly in the future so stay tuned.

Automatic Workload Repository (AWR)
The Automatic Workload Repository collects performance statistics (and the SQL text itself) for all SQL statements executed in the database.  It is a historical performance datawarehouse that stores SQL statement CPU, memory and I/O resource consumption. AWR runs by default and Oracle states that it does not add a noticeable level of overhead.   The information in this repository is used as input for the toolsets discussed later in this blog.   This historical information will help administrators finally answer questions like "my program ran long two days ago, can you fix it?"

A new background server process (MMON) takes snapshots of the in-memory database statistics (much like STATSPACK) and stores this information in the repository.  MMON also provides Oracle10G with a server initiated alert feature, which notifies database administrators of potential problems (out of space, max extents reached, performance thresholds, etc.). 

It looks like the days of us manually configuring STATSPACK to run every hour may be over.  What I like about AWR is that if you run out of space in the tablespace, Oracle begins to age out old information.   I've been paged too many times on STATSPACK tablespaces running out of space to not be very enthused about this particular 10G feature.  I used to have my Intro to Oracle students repeat over and over "We administer by day so we don't get paged at night."  

Automatic Database Diagnostic Monitor (ADDM)
The Automatic Database Diagnostic Monitor analyzes the information contained in the Automatic Workload Repository every 30 minutes to pinpoint problems and provide automated recommendations to DBAs.  If ADDM requires additional information to make a decision, it will activate other advisories to gather more information.  ADDM's output includes a plethora of reports, charts, graphs, hearbeats and related visual aids.  

ADDM can also be manually activated from OEM or the command line to provide users with a top-down analysis of performance bottlenecks and their associated resolutions.

SQL Access Advisor and SQL Tuning Advisor
These two new advisories will help Oracle DBAs with the "fine art" of SQL tuning.  In the past SQL tuning could be defined more of an art than as a science.  Hopefully, these two new advisories will put the science back into the SQL tuning process. 

The SQL Access Advisor uses the Automated Workload Repository to provide recommendations on creating Oracle objects (additional indexes, materialized views, etc.)  to increase the performance of poorly performing SQL statements.  

The SQL Tuning Advisor also uses the Automated Workload Repository to capture and identify high resource consuming SQL statements.  An intelligent analyzer is then used to assist administrators in tuning the offending SQL statements.   The SQL Tuning Advisor will be especially beneficial to administrators who support third-party applications. 

In previous releases, once the administrator identified the canned application's poorly performing SQL, the third-party vendor was contacted to change the SQL code and the changed code then implemented in test and finally the production environment to effect the tuning change.  Anyone who has experience with third-party application vendors knows that this is often a time consuming (if not impossible) process. 

The SQL Tuning Advisor uses the Oracle10G cost based optimizer to rewrite the poorly performing SQL and create a SQL profile, which is stored in the data dictionary.  Each time the poorly performing SQL statement executes, the rewritten statement stored in the data dictionary is used in its place.  No vendor assistance required!  

Working with third-party application vendors is usually painful to begin with.   The less I need them to tune (in most cases) the better.  I've found that members of my unit are better tuners than most vendor DBAs to begin with.   In their defense, it is sometimes impossible for a vendor to tune their database to match each individual customer's access requirements.

Transportable Tablespace
In previous releases, the transportable tablespace feature could only be used to transfer data to databases running on the same operating system. In Oracle10G, Oracle has enhanced the transportable tablespace feature to allow the tablespace to be transferred to databases running on different operating systems.  Once again, please refer to my blog on hidden Oracle secrets for more information on the cross-platform transportable transportable tablespace feature.

Data Pump
Comparing the performance of Oracle Export and Import to Data Pump is like comparing the performance of a VW to a Ferrari.  Stories of Export and Import executions running for hours (and sometime days) are commonplace.  During my career as a high-priced Oracle consultant (and before the transportable tablespace feature was available), one of my fellow consultants was an "Export Expert".  He had the reputation of being one of the few DBAs to get Exports requiring execution times measured in days to successfully execute.

Oracle has finally created a new utility, called Data Pump, to increase the performance of data transfers.  Oracle states that Data Pump's performance on data retrieval is 60% faster than Export and 15 to 20 times faster on data input than Import.  In addition, Oracle states that Data Pump is able to take advantage of parallel processing to increase performance.  Hopefully, this will alleviate the performance issues related to transferring data between Oracle databases.  I'm sure my old consultant buddy is resting easier.   Of course he no longer has the Export "badge of honor" to wear anymore.

Oracle Streams Database Transfer
Oracle10G also utilizes the cross-platform feature of transportable tablespaces to allow administrators to move or copy entire databases from one platform to another.  The database can be detached, moved to another server, attached and the workload moved with one command.  The primary use of this feature will be to load balance the different servers in a grid environment.

Conclusion
As stated previously, the intent of this blog was to highlight a few of the interesting new features in Oracle10G.  In future blogs, I'll delve a little deeper into some of these enhancements as well as keep you apprised of new developments in Oracle10G.  Feel free to respond back with comments, corrections and additions.   Thanks for reading.