Oracle 10G Hidden Features Part II

The Hidden Secrets of Oracle10G Blog Part II

Consider this to be the exciting sequel to The Hidden Secrets of Oracle10G Blog Part I.  This series of blogs are not intended to be an inclusive list of Oracle10G hidden features.   I'm hoping to provide readers with information on some of the 10G benefits not covered by other authors.   If you have a hidden feature you would like to highlight, please feel free to respond to this blog.  That's what blogging is all about!

Shrinking Tables
Getting rid of unused space in a table improves the performance of full table scans.  Removing unused space in index structures provides quicker index access due to a more compact tree.  The additional benefit is a more efficient utilization of space because the space freed is available for other objects. In releases prior to Oracle10G, getting rid of free space above and below a table's high-water mark was usually done by the tried-and-true, Export, Drop Table, Import commands.  Administrators could also use the ALTER INDEX REBUILD statement to reorganize index segments to create a more compact index tree.

Oracle10G objects that reside in Automatic Segment Space Managed (ASSM) tablespaces can be shrunk using the "ALTER………SHRINK" statement.    The shrink statement is not limited to just heap tables and indexes, Oracle also allows index-organized tables, partitions, subpartitions, materialized views and materialized view logs to be shrunk using ALTER commands.  In addition, the CASCADE option can be used to propagate the shrink operation to all dependent objects except materialized views, LOB indexes, IOT mapping tables and overflow segments.

It is interesting to note that because the shrink operation may change the ROWIDS in heap-organized tables, row movement must first be enabled by executing the "ALTER TABLE tablename ENABLE ROW MOVEMENT" statement. The shrink operation can be performed while the table is on-line and active and does not require any extra database data file space.  The shrink operation itself is performed by the database internally executing INSERT and DELETE statements.  Since the data itself is not changed (just rearranged), DML triggers are not fired during shrink operations.

Oracle uses the high water mark to identify the highest amount of space used by a particular segment.  It acts as the boundary between used and unused space.  As the amount of data grows, the segment's high water mark grows accordingly.  But as the amount of data shrinks (i.e. row deletes), the high water mark is not altered to reflect the segment's new characteristics.  The high water mark not being adjusted as the data shrinks has the tendency to create a somewhat confusing performance problem.

During a full table scan, Oracle scans all blocks up to the table's high water mark.  This happens regardless of whether those blocks contain data or not.   Business needs often require that all rows of a table be scanned. DBAs often attempt to increase the performance of the aforementioned table scans by trying to store as little data as possible in the scanned table.  If the table is purged by SQL DELETE statements, the high water mark will not be moved and the table scans will continue to perform poorly.  Please note that future INSERT statements can reuse the space.  Until that happens, you may be scanning a lot of blocks and not retrieving any data.  10G's new shrink command will definitely help in this situation.

If all of the rows are being deleted, the administrator should use the TRUNCATE statement to remove the unwanted rows.  TRUNCATE adjusts the high water mark to the first block in the segment.  If a partial purge is being performed, the administrator should reorganize the table with EXPORT/IMPORT or the ALTER TABLE MOVE command in Oracle9i or the new segment shrink command in Oracle10G.

Segment Advisor
Administrators are able to use Oracle10G's Segment Advisor to identify candidates for shrink operations.  The advisor estimates the amount of unused space that will be released when the shrink operation is run on the particular object.  A wizard is available that allows users to evaluate all objects in the database, all objects in a specific tablespace or all objects owned by a particular schema. 

Although we have tested this in our labs, we have yet to test the segment advisor in an active 10G environment.  The DBA running the 10G project here likes to "aggressively test and implement" new releases here.   Rest assured that in future blogs, I'll be providing you with a wealth of information on the segment shrink statement and the segment advisor. 

Tablespace Rename
Tablespaces can be renamed in Oracle10G by executing the "ALTER TABLESPACE old_name TO new_ name" SQL statement.   Oracle10G allows permanent, temporary and undo tablespaces to be renamed.  Oracle will automatically update all tablespace name references in the data dictionary, control files and on-line data file headers.  If the tablespace being renamed is an undo tablespace, Oracle will perform the additional step of updating the UNDO_TABLESPACE parameter in the SPFILE.  If an SPFILE is not used, a message is written to the database's alert log notifying administrators that the parameter file must be manually changed to reflect the undo tablespace's new name.  

The way my DBAs type, I'm happy to see ANY new rename statement become available in Oracle.   I'll put it to you this way, if Mavis Beacon ever stopped by and saw our DBAs in action; she'd probably have to use the old paper bag trick to stop from hyperventilating.  I won't be cringing as much now when I hear "what do you mean I spelled the name wrong?" coming from my team. 

Oracle File Copies
Oracle10G's DBMS_FILE_TRANSFER PL/SQL package provides administrators with a mechanism to copy binary files between Oracle databases without using OS commands or FTP.   The transfer package can be executed locally to transfer files to another database server or can be executed remotely to transfer files between two remote databases. 

Currently, the only files that can be copied using this mechanism are Data Pump dump sets and tablespace data files.  In addition, the file size must be a multiple of 512 bytes and less than 2 terabytes.  Using the file transfer package in conjunction with Oracle's transportable tablespace feature allows administrators to totally automate tablespace data transfers from one database to another.   The process to unplug tablespace data files from the source database, copy the files to the destination server and plug the tablespace data files into the target database can now be executed on a recurring basis by batch jobs initiated by DBMS_JOBS, OEM, KRON, AT and third-party schedulers. The transferred files created on the target platforms are owned by the Oracle account and can be accessed by all database processes. For long copy operations, progress is displayed in the V$SESSION_LONGOPS view.

Redo Logfile Size Advisor
Describing the process of determining the size of a database's redo logfile as "somewhat error-prone" is like stating that the Titanic sprung a small leak.  Administrators must balance the performance implications of redo logfiles that are too small with the recovery implications of having redo logfiles that are too large.   Oracle10G comes to the rescue with another new advisor, the Redo Logfile Size Advisor.  The advisor suggests the smallest on-line redo logfile based on the current FAST_START_MTTR_TARGET parameter and workload statistics.  Like database managed undo segments, Oracle must have thought we were doing such a "bang up" job sizing redo logfiles that they felt we needed help.  For many of us, they were right!

I can't under estimate the importance that redo logfile sizing has on database performance.  When I was a high-priced Oracle consultant on a tuning gig, one of the first things I looked at when I performed my database environment performance review was to look at log switch frequency.  If I saw "checkpoint not complete" messages in the alert log, I'd start to salivate knowing that fixing this problem was going to be an easy way to tune the client's environment.

A common rule of thumb is to adjust the redo log's size so that Oracle performs a log switch every 15 to 30 minutes. Log switches that occur more frequently may have a negative impact on performance. Log switches that occur several times a minute have a definite impact on database performance. Checking messages in the alert log is one way to determine how fast Oracle is filling and switching logs. If the following messages are found, you can be sure that performance is being affected:

Thread 1 advanced to log sequence 248
Current log# 2 seq# 248 mem# 0: /orant/oradata/logs/redolog2a.log
Thread 1 cannot allocate new log, sequence 249
Checkpoint not complete

The "checkpoint not complete" messages are generated because the logs are switching so fast that the checkpoint associated with the log switch isn't complete.  During that time, Oracle's LGWR process has filled up the other redo log groups and is now waiting for the first checkpoint to successfully execute.  Oracle will stop processing until the checkpoint completes successfully. 

One easy way to prevent improper redo logfile sizing from affecting database performance in Oracle10G is to use the handy new redo logfile size advisor.

Flushing the Buffer Cache
The buffer cache can magically increase the performance of SQL benchmarking tests.   The first run will incur the I/O to load the data buffers and subsequent runs may be faster because the data is now cached.  The "ALTER SYSTEM FLUSH SHARED POOL" empties the database buffer cache and allows users to establish and maintain a consistent SQL benchmark environment.   Administrators are able to flush the buffer cache between statement executions to determine if their tuning changes have made a positive impact on the execution times of the SQL statement being tuned.

Even though I am an ex-Oracle instructor, I still have a strong desire to train others.   If it wasn't for my wife forgetting who I was, I would still be teaching.  One of my favorite teases for really new DBAs was to run a query and time it, tap the top of the monitor and run it again.   Becuase the first query "primed" or pre-loaded the buffers, the run-time would magically be reduced!   It was an excellent way to start our discussion on the benefits of properly sizing the database buffer cache.

Active Session History
Oracle10G contains a new internal utility, called Active Session History, to provide administrators with access to current performance information.

Active Session History samples data from the V$SESSION dynamic performance table every second and stores the information in V$ACTIVE_SESSON_HISTORY.  The information contains the events that current sessions are waiting for.  The information pertains to active sessions only; information from inactive sessions is not recorded.  The view contains one row per active session for each one-second sample.  Administrators are able to access V$ACTIVE_SESSON_HISTORY as they would any other V$ dynamic performance table.

It is important to note that like V$SQLAREA, V$ACTIVE_SESSION_HISTORY is a rolling buffer in memory.  Oracle's internal documentation states that current workload analysis most often requires detailed performance statistics on activity within the last five to ten minute time-period.  As new information is added, earlier information contained in the view will be removed.  Active Session History consumes 2 megabytes of memory per CPU and is fixed for the lifetime of the instance. 

I've been tuning for a long time, having started with Oracle Version 6.  I've also been teaching Oracle for a long time.   Here is some helpful advice, learn as much as you can about Active Session History.   This new feature WILL REDUCE the amount of time you spend tuning applications that use Oracle. 

Conclusion
As stated previously, the intent of these blogs were to highlight a few of the interesting, but often overlooked, new features in Oracle10G. Thanks for reading and good luck with Oracle10G.  Please feel free to respond back with any comments, corrections or questions!