An Oracle Instructor’s Guide to Oracle10g

Oracle recently announced its latest database product, Oracle10g, at this year’s Oracle Open World. Most of the fanfare on this release centers on the grid capabilities of Oracle’s latest offering (hence the “g” on Oracle10g). But as with Oracle9i, Oracle10g contains enhancements in virtually all areas of the database server, resulting in an Oracle database with improvements in scalability, availability, performance, manageability, multimedia datatype support, and functionality.

This article covers just a small subset of Oracle10g features. It is not intended to be an all-inclusive document, but rather a high level overview of some of the more important (and hopefully, interesting) changes contained in this release. Consider this the first of many articles on Oracle10g, so we’ll keep this one at the 30,000-foot level. In future articles we’ll take an in-depth look at each of the features described in this article and new features as they are announced. One of the benefits of being an Oracle instructor is having access to up-to-the-minute information on Oracle’s latest product sets. As Oracle distributes more information on Oracle10g features, I’ll make sure to keep you informed.

Grid Computing

Let’s get started with a brief overview of Oracle Grid Computing. Oracle describes grid computing as “separate groups of users dynamically sharing computer resources across high-speed networks to meet changing computational needs.” Grid environments utilize farms of low-cost computer servers in a shared environment. Oracle uses its Real Application Cluster technology (discussed below) along with the 10g Database and 10g Application Server to create the shared computing infrastructure required to implement grid computing.

The optimal grid environment uses an interconnected server farm with each server containing one to four CPUs. The servers are connected to a shared disk system using network-attached storage (NAS) or a storage area network (SAN) technologies as the connectivity mechanism. High-speed network connections between the hardware servers themselves and from the hardware servers to the shared disk system allow end-users and administrators to view the environment as a single application architecture (refer to Figure 1).

Real Application Cluster enhancements in Oracle10g allow hardware servers to be seamlessly added to an application requiring more resources for a particular time period. Conversely, when hardware resource requirements are low, the extra computing horsepower can be easily freed and used by other applications in the grid.

Oracle10g enhances Oracle Enterprise Manager functionality to manage the grid with a single interface. Oracle’s Grid Control is a web-enabled toolset that allows administrators to group hardware platforms, databases, and application server installations and manage them as a single entity. Administrators are able to call other utilities (Data Pump, Transportable Tablespace, Oracle’s new job scheduler) from within Grid Control to modify, monitor and tune databases throughout the grid.

Grid Control also simplifies grid administration by automating the installation, configuration and cloning of Application Server 10g and Database 10g implementations across multiple nodes. Grid Control monitoring views the entire grid as a single unit and provides drill down capabilities to identify problems with individual components.

Automatic Storage Management (ASM)

Oracle10g provides its own disk storage management system. Database administrators are no longer required to use hardware vendor or third-party disk volume managers to provide striping and mirroring functionality. ASM manages the raw disks within the Oracle database architecture. Administrators are able to assign disks to disk groups, which can then be striped and/or mirrored to provide high performance and high availability. During tablespace creation, the administrator assigns the tablespace datafile to a disk group. This differs from previous Oracle releases which required that datafiles be assigned to the individual disks themselves.

Interestingly enough, Oracle’s default stripe size is one megabyte. This differs from most disk storage management systems, which often utilize 32K or 64K stripe sizes. Oracle found that one-megabyte stripes on disks provided a very high level of data transfer and best met the needs of disk intensive applications. One can only assume that advancements in disk storage technology have allowed Oracle to access the data in one-megabyte chunks and not drive disk utilization to unacceptable levels.

Administrators provide disk mirroring by creating failure groups. The DBA creates the appropriate number of failure groups to accommodate the data requiring disk fault tolerance. ASM’s mirroring capability ranges from the mirroring of individual datafiles to entire disk arrays, providing administrators with a high level of flexibility when creating fault-tolerant disk subsystems. The data is duplicated on separate disks in one-megabyte mirror “chunks.”

Administrators can choose from the following mirroring options in ASM:

• External – no mirroring
• Normal – data is mirrored on two separate disks. This is the default setting.
• High Redundancy – data is mirrored on there separate disks providing three-way mirroring capabilities.

ASM requires its own instance, which identifies the various disk groups and files during instance startup. The ASM instance then mounts the disks under its control and creates an extent map, which is passed to the database instances. ASM does not perform the I/O for the database instances; it is only used to manage the various disk groups under its control. ASM is only activated when individual datafiles are created or dropped or disks are added and removed from the disk groups. When new disks are added or removed from the disk group, ASM automatically rebalances the files contained in the disk group while the database is open and functioning.

ASM is able to balance the I/O for multiple databases across all managed devices providing load balancing for multiple applications. In Oracle10g Grid implementations, ASM is able to reassign disks from one node to another providing additional load balancing capabilities.

Oracle Enterprise Manager (OEM) for Oracle10g and the Database Configuration Assisstant (DBCA) have been updated to allow administrators to configure and manage databases using ASM.

Real Application Clusters (RAC)

One of RAC’s requirements is that clustering software (sometimes called clusterware) be used to connect the hardware platforms together. This underlying clustering software was purchased either from the hardware vendor or a third-party clustering software provider. RAC is installed on top of the cluster environment and works in conjunction with the underlying clustering software to allow the application programs to view the multiple instances as a single entity.

One of the problems using previous release of RAC was identifying exactly whose software it was causing the problem in the first place. Was it the RAC software or was it the clustering software provided by the hardware or third party vendor? Oracle10g solves this problem by providing its own clustering software called Portable Clusterware. Portable Clusterware can now be used in place of the hardware or third-party vendor’s clustering software.

Flashback Database

How many times have database recoveries been performed because of incorrect changes made to database data? A user deletes or updates “one too many rows” by mistake and the result is a time-consuming and error prone process to restore and recover the database to a point-in-time before the error occurred. Oracle Education states that point-in-time recoveries are responsible for the majority of DBA errors and the resulting unrecoverable databases.

A common question in the Oracle DBA2 backup and recovery class is “Why can’t I just roll the database back to remove unwanted changes instead of restoring the database from a backup and applying the redo logs to roll forward to a point in time before the error occurred?” The question was invariably followed by “Don’t you think that would be easier?” The answer was always the same “Yes, it would be easier, but the Oracle database doesn’t have that capability.”

Until Oracle10g that is… Oracle10g’s Flashback Database feature provides a new tool in the DBA’s recovery toolbox. Flashback Database allows the DBA to “roll back” a table, set of tables or the entire database to a previous point-in-time.

A flashback log is used to capture old versions of changed data blocks. During Flashback execution, the Oracle database restores the old versions of the data blocks to their original locations which allows the database to be rolled back to a previous point-in-time. Oracle provides the following example in its sales collateral to highlight how simple this new Flashback Database Feature is:

SQL> flashback database to ‘2:05 PM’;

Compare that to previous point-in-time recovery procedures, which required the entire database to be restored to a previous backup and then rolled forward using archived and online redo log files to replay the changes to a point-in-time before the error occurred.

Flashback Backup

Oracle10g also uses the flashback logs to provide the mechanism for its Flashback Backup feature. Flashback Backup allows administrators to take a base level backup and then perform nightly incremental backups to roll the backup database forward to the current point-in-time. Since the changed blocks are recorded by the database, a full scan of all of the database blocks is no longer required to keep the backup database synchronized with its production counterpart.

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 modify these allocations dynamically based on application workload changes.

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 article. 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.).

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, heartbeats 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 Tuning Advisor

Oracle’s latest advisor will help Oracle DBAs with the “fine art” of SQL tuning. In the past SQL tuning could be defined as more of an art than as a science. Administrators required extensive tuning experience before they could be described as “expert SQL tuners.” Oracle claims to have embedded hundreds of year’s worth of tuning experience into the SQL Tuning Advisor. Hopefully, this new advisor will help put the science back into the SQL tuning process.

The SQL Tuning Advisor 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 tuning advisor sends the SQL statement being analyzed to the Automatic Tuning Optimizer to perform the following in-depth analyses:

• Statistics Analysis – the utility checks for stale or missing statistics, which may have a detrimental effect on the query’s optimization.
• SQL Profiling – reviews past executions of the SQL statement to provide further information for recommendations.
• Access Path Analysis – determines if additional objects (indexes, materialized views) can be created to improve the statement’s performance.
• SQL Structure Analysis – reviews the SQL statement’s coding structure to determine if it can be altered to increase performance.

The Automatic Tuning Advisor uses the Oracle optimizer to make its recommendations. Unlike run-time optimization, which focuses on quick optimization, Automatic Tuning Advisor calls to the optimizer are not limited by time constraints. As a result, queries tuned by the advisor have a much better chance of having a finely tuned optimization plan created.

The SQL Tuning Advisor will be very 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 implemented in test and finally in production to implement 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!

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.

Data Pump

Describing the Oracle Export and Import utilities as slow is like saying the Titanic sprung a small leak. Stories of Export and Import executions running for hours (and sometime days) are commonplace. Oracle has finally created a new data movement 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.

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.

Oracle Scheduler

Oracle10g introduces a robust job scheduler to facilitate job execution in grid computing. But, you don’t have to run a grid to take advantage of the benefits the Oracle Scheduler offers. Users are able to create complex job streams that execute PL/SQL, Java and C programs. Conditional logic can be used to interrogate job return codes and provide job stream branching capabilities. Jobs can also be grouped into job classes, which can then be given a predefined set of computing resources by Oracle’s Database Resource Manager to provide job prioritization.

Conclusion

As stated previously, the intent of this article was to highlight a few of the interesting new features in Oracle10g. Many of the features that were designed to support the grid architecture will also be beneficial in non-grid environments. In future articles, I’ll delve a little deeper into some of these enhancements as well as keep you