Those of you who have been following my "Oracle Instructor's Guide to Oracle9i" series of articles know that the next two topics of discussion were to be Oracle 9i Data Guard and Real Application Clusters (previously known as Standby Database and Oracle Parallel Server respectively).

Consider this article to be my first "stop the presses" feature. We just received our Oracle Instructor's Guides containing information on Oracle9i Release 2. In addition, I recently had the good fortune to become one of the first instructors to teach the "Oracle 9i Release 2 New Features for Administrators" class. For those of you who have not taken a new features class for some time, take it from your friendly Oracle instructor, the Oracle9i Release 2 class is excellent. The class was made up entirely of experienced DBAs and they all agreed that the class material was well thought out, interesting, and pertinent to their daily administrative activities.

After reviewing the Oracle Instructor's Guides and teaching the New Features class, I though it important that we temporarily deviate from my intended topics of discussion and focus on Oracle 9i Release 2 enhancements. The intent of this article (as with my others) is not to discuss each and every new feature contained in Oracle's latest release, but rather to provide readers with a quick overview of some of the more beneficial and interesting enhancements contained in the product. So, let's get started!

GRANT ANY OBJECT Privilege

In all releases prior to Oracle 9i Release 2, administrators wanting to grant object privileges on an object owned by another user were required to either:

• Log on as the object owner and grant the privilege to the grantee.
• Log on as the object owner, grant the desired privilege to their own account using the WITH GRANT OPTION clause. This would allow the administrator to then grant that privilege to others.

In Oracle9i Release 2, the GRANT ANY OBJECT privilege allows users to grant privileges on objects owned by another user. This privilege is granted to the user SYS and the DBA role by default. Saying that this feature has been a long-time coming is like saying the Titanic had a small leak.

ALTER COLUMN RENAME

Another long-awaited enhancement has finally arrived. Administrators are now able to rename columns using a simple SQL statement. The new ALTER TABLE RENAME COLUMN statement is introduced in Oracle9i Release 2 to provide column-renaming functionality. No longer are administrators required to drop and reload tables or use the DBMS_REDEFINITION stored procedure (introduced in Oracle 9i Release 1) to rename columns in Oracle. The example below shows the new statement in action:

ALTER TABLE foot.emp RENAME COLUMN salry to salary;

Flashback Query Enhancements

Oracle9i's flashback query provides users with the capability of viewing data in the past. Oracle describes this new feature as "Oracle Invents the Time Machine" in many of its advertisements. It may not be a time machine, but it does allow data to be viewed in the past and it is easy to use.

In the first release of Oracle9i, the only method available to invoke flashback query was to execute the system supplied package DBMS_FLASHBACK. Here is an example that goes back five minutes:

EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME (SYSDATE - (5/(24*60)));

The above statement sends the session back in time for the duration of that session or until the EXECUTE DBMS_FLASHBACK.DISABLE is executed. Standard date and time SQL functions are used to determine the time in the past the data will be retrieved from.

The drawback was that data could not be updated during a flashback query enabled session. Savvy programmers were able to save historical data by placing it into a cursor and then dumping the contents of the cursor into a worktable after the FLASHBACK.DISABLE procedure was executed.

In Oracle9i Release 2, the AS OF timestamp clause has been added to the SELECT statement to enable flashback query on a specific table or set of tables. Developers are able to specify the AS OF clause for a single-table, multiple-tables (joins) as well as specify different times for different tables. The AS OF timestamp clause can also be used inside INSERT or CREATE TABLE AS SELECT statements.

Here is an example of a SELECT statement using the AS OF timestamp clause:

         UPDATE emp.employee_payroll_table SET emp_salary =      
                 (SELECT emp_salary FROM emp.employee_payroll_table
                         AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
                         WHERE emp_last_name = 'FOOT')
WHERE emp_last_name = 'FOOT';

The statement above uses the SYSTIMESTAMP value with an INTERVAL function to update the emp.employee_payroll_table with data that is 24 hours old.

The AS OF timestamp clause (and its DBMS_FLASHBACK.ENABLE_AT_TIME counterpart) maps the timestamp value to a SCN value. Oracle records the SCN-TIMESTAMP mapping at five-minute intervals. This means that the time you may actually retrieve the data from could be rounded down by up to five minutes. For example, you could access a table using a timestamp that is a few minutes after the table is created and receive an error because flashback query used a SCN value that is lower than the actual time when the table was created.

Locally Managed System Tablespace

Locally managed tablespaces allow the Oracle system to automatically manage an object's extents. Locally managed tablespaces track object information in bitmap structures stored in the tablespace itself. Oracle states that locally managed tablespaces provide increases in the concurrency and speed of space operations and generally have a positive impact on application performance.

In Oracle9i Release 2, administrators are now able to create a locally managed SYSTEM tablespace. The EXTENT MANAGEMENT LOCAL clause can be used to create a locally managed SYSTEM tablespace during database creation or administrators can use the stored procedure DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL to migrate existing dictionary managed tablespaces to locally managed. Once you create or migrate to a locally managed SYSTEM tablespace, you are unable to create any dictionary managed tablespaces in the database.

Administrators must be careful when using the aforementioned package to migrate SYSTEM tablespaces from dictionary managed to locally managed. All tablespaces (except those containing UNDO segments) must be in READ ONLY mode for the migration process to successfully execute. If Oracle finds any tablespaces in READ WRITE mode, the error message states that the tablespaces must be placed in READ ONLY mode.

The problem is that if the administrator places any dictionary managed tablespaces in READ ONLY mode, they will be unable to place them in READ WRITE mode after the SYSTEM tablespace is converted to locally managed. Administrators desiring to migrate their SYSTEM tablespaces to locally managed must migrate all READ WRITE dictionary managed tablespaces to locally managed tablespaces BEFORE MIGRATING THE SYSTEM TABLESPACE.

Statistics Level Collection Parameter

Oracle9i Release 2 introduces the STATISTICS_LEVEL initialization parameter. This dynamic parameter controls all statistics collection and advisories in the database. The settings for STATISTICS_LEVEL are as follows:

• BASIC - No statistics or advisories are collected.
• TYPICAL - Buffer cache advisory, MTTR advisory, shared pool advisory, PGA target advisory, segment-level statistics and timed statistics are collected. TYPICAL is the default value.
• ALL - Everything from TYPICAL plus timed operating system statistics and row source execution statistics.

Oracle provides the V$STATISTICS_LEVEL view to provide information on the status of statistics collection and advisories set by the STATISTICS_LEVEL parameter. V$STATISTICS_LEVEL contains a row for each statistic or advisory being collected.

Performance Tuning Intelligent Advisories

Experienced DBAs often start their tuning efforts by selecting data from the V$ dynamic performance tables to view system-wide performance indicators. For those of you new to Oracle, statistics are pieces of information that the Oracle instance collects to help administrators gauge performance and identify problems. But intelligent performance advisories are a relatively new concept, so a quick definition is in order before we continue.

An advisory is a collection of information that predicts the impact a specific change would have on the item being monitored. Advisories allow administrators to simulate "what if" hypothetical scenarios to predict the impact a specific change may have on the Oracle environment.

For example, the V$SHARED_POOL_ADVICE view contains information about estimated parse time savings if a different shared pool size were used. Each row in the view contains a percentage value (ranging from 50 percent to 200 percent of the current shared pool) and the estimated performance increase or decrease associated with that percentage of change.

Oracle provides advisories for the buffer cache, MTTR (Mean Time To Recovery) and PGA target. It is important to note that not all of the advisories are activated by setting the STATISTICS_LEVEL parameter alone. Administrators wanting to activate advisories should consult the Oracle9i Performance Tuning Guide and Reference for more information.

Segment-level Performance Statistics Collection

Starting with Oracle9i Release 2, administrators are able to gather statistics at the individual segment level. Don't confuse segment-level performance statistics with optimizer statistics generated from a DBMS_STATS execution or SQL ANALYZE statement.

Segment-level performance statistics are a subset of the statistics captured at the system level. A list of the statistics captured is as follows:

• Logicial reads - buffers used for consistent and current reads
• Buffer busy waits - waits caused by a buffer that is in use
• Db block changes - changes applied to buffers
• Physical reads - number of physical reads
• Physical writes - number of physical writes
• Physical reads direct - number of direct reads
• Physical writes direct - number of direct writes
• ITL waits - waits caused by no ITL entry
• Row lock waits - waits caused by row lock contention
• Global cache cr blocks served - Real Application Cluster statistical information
• Global cache current blocks served - Real Application Cluster statistical information

When investigating performance problems, administrators would use the system-wide statistics contained in the V$ views to review performance indicators at the instance level. Segment-level performance statistics can then be used to identify specific tables and indexes that may be causing the problem. Administrators would then focus their tuning efforts on those specific objects.

Statistics are captured by setting the STATISTICS_LEVEL dynamic initialization parameter to TRUE (default) or ALL. Although a small area of the SGA is used for capturing this information, there is no significant impact on overall database performance.

Three new dynamic performance views are introduced in Oracle 9i Release 2:

• V$SEGSTAT_NAME - lists the segment statistics collected
• V$SEGSTAT - real time monitoring view of segment-level statistics
• V$SEGMENT_STATISTICS - contains all of the information in V$SEGSTAT plus segment owner, tablespace name, etc.

Optimizer Dynamic Sampling

So little is known about the inner workings of the cost-based optimizer that is often called the Oracle database's "little black box." The optimizer's job is to analyze the statement being executed and determine the most efficient access path to the data for that statement. Craig Mullins provides an excellent description of a database optimizer in The DB2 Developer's Guide: "the optimizer is equivalent to an expert system. An expert system is a standard set of rules when combined with situational data can return an expert opinion."

The cost-based optimizer uses statistics generated by the DBMS_STATS procedure or the ANALYZE statement as the situational data when creating its expert opinion on which access path to the data is most optimal. These statistics are stored in the data dictionary and describe the objects space characteristics, data uniqueness and data distribution.

The cost-based optimizer is only as good as the statistics it uses as input. Statistics collections should be run on a regular basis to ensure that the statistics are current (representative of the data being accessed). The optimizer is then able to create a highly accurate access path that is based on the least cost. If statistics are not available, the optimizer uses a simple algorithm to calculate the statistics, which often leads to "less than optimal" access paths. In other words, Oracle guesses and it is usually not a very good guesser.

Oracle 9I Release 2 introduces optimizer dynamic sampling to overcome the lack of accurate statistics on the objects being accessed. The optimizer is now able to take a sampling of the data during access path optimization. Administrators are able to activate dynamic sampling and control the size of the dynamic sample taken by using the OPTIMIZER_DYNAMIC_SAMPLING dynamic initialization parameter as a throttle.

The values for OPTIMIZER_DYNAMIC_SAMPLING range from 0 to 10 with 0 telling the cost-based optimizer to not use dynamic sampling and the value 10 telling the optimizer to sample all blocks in the table. The DYNAMIC SAMPLING hint can be used at the statement level to override the system setting defined by OPTIMZER_DYNAMIC_SAMPLING.

Oracle recommends that dynamic sampling only be used when the time required to do the sample is a small fraction of the statement's total execution time. It's a safe assumption that dynamic sampling will not be used in many OLTP systems, but it may find a home in a few decision support and data warehouse environments.

Data Segment Compression

Compressing data has been around since the inception of the database management system. Data compression reduces the amount of data being stored, reduces memory usage (more data per memory block), and increases query performance.

Data compression in Oracle9I Release 2 is performed at the block level. Administrators are able to compress entire tables and specific table partitions. The COMPRESS attribute can be specified in the CREATE TABLE, CREATE TABLESPACE and CREATE TABLESPACE …. PARTITION statements. Block compression works by eliminating repeating column values. The more repeating values the columns have the greater the compression ratio becomes.

We recently learned that compression is performed at the block level in Oracle. This means that as the number of repititious values per block increases, so does its compression ratio. Administrators are able to achieve higher compression ratios by sorting the rows on a column that has a poor cardinality (high number of repeating values). CREATE TABLE……SELECT * FROM…… ORDER BY low_cardinality_column can be used to ensure as many repeating values fall in the same block as possible. Flat file data being loaded by the Oracle load utility can be sorted using an operating system sort utility to achieve the same result.

Although UPDATE and DELETE operations can be performed on compressed data in Oracle, the performance impact the compress and uncompress operations have on those statements is yet to be determined. Oracle recommends that compressed data be highly considered for data warehouse environments because data block compression is highly optimized for direct-load operations.

Parallel DML on Non-Partitioned Tables

Parallel operations speed up DML statement execution by dividing the work among multiple child processes. Each child process executes its portion of the work under its own parallel process transaction.

Enabling parallel DML, although simple, does have several restrictions that prevent its usage in certain cases. One restriction in previous Oracle releases was that the table must be partitioned. In Oracle9i Release 2, non-partitioned tables can be accessed in parallel by parallel DML child processes. Please refer to the Oracle9i Data Warehousing Guide for more information on enabling parallel DML statements.

Administrative User Auditing

When connecting to a database using the AS SYSDBA or AS SYSOPER attributes, the administrator is actually connected as SYS behind the scenes. To test this feature, perform the following exercise:

• Log on to the database as SYS/password as SYSDBA.

SQLPLUS /NOLOG
CONNECT sys/change_on_install AS SYSDBA;

• Create an account and grant that account the SYSDBA privilege.

CREATE USER test IDENTIFIED BY test;
GRANT sysdba TO test;

• Login using the new account and activate the SYSDBA privilege as you do so.

CONNECT test/test AS SYSDBA;

• Execute the following statement to find out what account you are logged in as.

SQL> SELECT USER FROM DUAL;

USER
------------------------------
SYS

Logging in as SYS or activating SYSDBA and SYSOPER during login enables the user to perform virtually any administrative function in Oracle. Oracle9i Release 2 enables the auditing of the activities performed when a user logs in as SYS or activates the SYSDBA or SYSOPER privilege during login.

Administrators activate administrative user logging by setting the new static initialization parameter AUDIT_SYS_OPERATIONS to TRUE. This begins logging all SYS user operations to the operating system file that contains the audit trail. These actions cannot be logged to the SYS.AUD$ auditing table.

Administrators set the static initialization parameter AUDIT_FILE_DEST to specify which directory will store the auditing files. Administrators working in Microsoft Windows environments should note that AUDIT_FILE_DEST cannot be set on those platforms. By default, all audit files on Windows platforms will be sent to the APPLICATION log.

The AUDIT_TRAIL static initialization parameter will continue to be used to send other audited activities to the operating system (AUDIT_TRAIL=OS) or to the SYS.AUD$ auditing table (AUDIT_TRAIL=DB).

SYS and SYSTEM Passwords

SYS and SYSTEM account default passwords can now be set at database creation time. Two new clauses have been added to the CREATE DATABASE statement to allow administrators to set SYS and SYSTEM passwords. The example shows the two new clauses in use:

CREATE DATABASE orc1
USER sys IDENTIFIED BY oracle
USER system IDENTIFIED BY rules;

The clauses are not mandatory in this release but you can't specify just one of them. You must specify the syntax for both clauses to successfully execute the CREATE DATABASE statement.

The Database Configuration Assistant (DBCA) has also been enhanced to persuade (read that force) administrators to change SYS and SYSTEM passwords during database creation. A security panel appears during DBCA execution that asks the user to set the SYS and SYSTEM password to non-default values. An error message will be returned if you use select MANAGER for SYSTEM or CHANGE_ON_INSTALL for SYS.

Default Partition for List Partitioning

List partitioning was introduced in Oracle9i Release 1. List partitioning provides administrators with precise control over the placement of data in tablespace partitions. Each partition is defined by specifying a list of values as the partitioning key. Oracle9i Release 1 did not provide an overflow area to hold rows that have keys that are not identified in the list specifications. If an application program attempted to insert a row that had a partitioning key value that did not match any of the specifications, the row was not inserted and an Oracle error code was returned.

Oracle9i Release 2 solves this problem by allowing administrators to specify a default partition. A new DEFAULT attribute has been added to the list partition specification. The example below shows the DEFAULT attribute being specified during partitioned table creation:

        CREATE TABLE sales 
(salesid NUMBER(9999), transdate DATE, state VARCHAR2(30)…..)
PARTITION BY LIST (state)
(PARTITION region_north VALUES ('New York', 'Maine'),
         PARTITION region_south VALUES ('Florida', 'Georgia'),
 ……….
 PARTITION region_ovflw VALUES (DEFAULT));     

Range-list Composite Partitioning

Oracle's latest release offers a hybrid solution for list partitioning that is a combination of the range and list partitioning techniques. The table is first partitioned by range and then subpartitioned using the list partitioning technique. Unlike composite range-hash partitioning, each subpartition contains a logical division of the data that is specified by the DBA (as opposed to the range-hash technique where the subpartitions are selected by the hashing algorithm itself).

For example, the primary partition could be based on a date range to allow rolling window operations to occur and the second level could be based on a logical grouping (a list of states (i.e., Pennsylvania, New York, Ohio) is an good example of a logical grouping). The data would be divided according to the date range and divided again according to the state's name. The example below shows a table using the range-list composite partitioning technique:

CREATE TABLE sales 
(salesid NUMBER(9999), transdate DATE, state VARCHAR2(30)…..)
PARTITION BY RANGE (transdate)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE
(PARTITION region_north VALUES ('New York', 'Maine'),
         PARTITION region_south VALUES ('Florida', 'Georgia'))
  
        (PARTITION 2002_quarter1 values less than 
                (TO_DATE ('01-APR-2002', 'DD-MON-YYYY')),
         PARTITION 2002_quarter2 values less than……..

The CREATE TABLE partitioned tablespace above uses the SUBPARTITION TEMPLATE clause to define the subpartitions. If all subpartitions have the same definition, administrators are able to code the subpartition specification once and Oracle will apply the template specification to each main partition that does not override the template with its own subpartition specification. If the template were not used, the subpartition clause would have to be specified after each main partition specification.

Oracle Net Trace Assistant

Oracle administrators use tracing to provide a detailed description of the operations performed by Oracle's internal components. The trace information is sent to an output trace file, which can then be investigated to provide an insight into the events that are causing the problem. Oracle provides tracing mechanisms for both client and server communication programs.

The information in these trace files is sometimes so cryptic that it is unusable. Oracle9i Release 2 introduces the Oracle Net Trace assistant to help administrators decipher information contained in the trace files. The TRCASST [options] <tracefilename> command can be used to provide a more readable trace.

There are dozens of different trace command options and trace file combinations. Delving into each of them is far beyond the scope of this article. Administrators desiring to learn more about how to use the Oracle Net Trace Assistant utility should consult Chapter 17 of the Oracle9i Release 2 Net Services Administrator's Guide.

I hope you enjoyed learning about some of the new features contained in Oracle9i Release 2. As I stated previously, this article contains only a subset of the new features available in Oracle's latest release. My intent was to provide you with an overview that will spark reader's interests in learning more about Oracle9i Release 2 new features. Readers interested in the Oracle9i New Features Class can go to http://education.oracle.com to find information on class dates and locations. I'll see you in class!

--

Christopher Foot has been involved in database management for over 18 years, serving as a database administrator, database architect, trainer, speaker, and writer. Currently, Chris is employed as a Senior Database Architect at RemoteDBA Experts, a remote database services provider. Chris is the author of over forty articles for a variety of magazines and is a frequent lecturer on the database circuit having given over a dozen speeches to local, national and international Oracle User Groups. His book titled OCP Instructors Guide for DBA Certification, can be found at http://www.dba-oracle.com/bp/bp_book14_OCP.htm.