Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Blogs » Chris Foot Blog » Chris Foot's Oracle10g Blog » Oracle 10G R2 New Features
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 4702
 

Oracle 10G R2 New Features Oracle 10G R2 New Features

OK, here comes another "Stop the Presses" blog. I intended to discuss the SQL Tuning Advisor, but Oracle 10G R2 intervened. Since Oracle is currently publicizing all of the features contained in the latest and greatest release of its flagship product, I thought it would be appropriate to spend a few blogs discussing them.

Rest assured, we will continue our discussion on the10G EM advisors once we have completed our review of 10G R2.
These next few blogs will contain just a subset of Oracle10G R2's new features. I thought I would focus on the enhancements that provide the greatest benefit to the general DBA public. Because of space considerations, and to not turn our new feature discussion into the world's largest blogs, the topics will be initially covered at a high-level. That's the cool thing about blogs; I'll have lots of time in the future to discuss each feature in depth. If you have a feature you would like to highlight, please feel free to respond to this blog. That's what blogging is all about!

SQL Access Advisor
Since our last discussion was on the SQL Access Advisor, I thought that this would be an appropriate feature to start with. You have to admit, the SQL Access Advisor is a pretty helpful tool. If you read my last blog, you know that the SQL Access Advisor used to recommend two types of indexes, B-Tree and bitmap. You can now add function-based indexes to that list. The utility is now able to recommend the creation of all of the major index structures available in Oracle. Once we get 10G R2 installed, I'll provide you with our SQL Access Advisor test results.

Additional enhancements to the SQL Access Advisor include:

  • The ability to quick tune a single statement
  • Users are able to create templates to simplify the data capture and evaluation process
  • The SQL Access Advisor panels in 10G Enterprise Manager R2 now display recommendation and action implementation statuses

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.

The 10G R2 Segment Advisor has been enhanced to identify tables that suffer from excessive row chaining and row migrations. Does this mean we can finally put the SQL ANALYZE statement to rest?

Why should we care about row chaining and row migrations? When a row is updated and becomes too large to fit into its original block (due to insufficient free space), the row is moved to a new block and a pointer is placed in the original block that identifies the row's new home. This is called a row migration. So when you access the row through an index, Oracle navigates first to the row's original block and then follows the pointer to the block where the row is actually stored. This means you are generating unnecessary I/O to access a migrated row. You correct this by identifying the tables affected and reorganizing them. A row chain occurs when a row is simply too long to fit into a single block. Oracle will chain the row together on multiple blocks using pointers to connect the chain's pieces. You solve this problem by increasing the block size or decreasing the row's length. Most often you just have to live with row chaining.

In the past, we identified row chaining and row migrations by reviewing the "table fetch by continued row" output line in our STATSPACK reports and ran SQL ANALYZE statements on the data objects on a regular basis. Remember DBMS_STATS does not populate the CHAIN_CNT column in DBA_TABLES. If you want to populate that column, you'll need to run the ANALYZE statement. But the CHAIN_CNT column is incremented for both row chains and row migrations. How do you know which rows are chained and which ones are migrated? Tom Kyte's website has an excellent discussion on this topic. Do a search on "row chaining" to find it.

In 10G R2, the Segment Advisor is automatically scheduled by Enterprise Manager to run during a predefined maintenance window. The maintenance window is initially defined as follows:

  • Monday through Friday - 10PM to 6AM
  • Saturday 12:00 a.m. to Monday morning at 12:00 a.m

The maintenance window's default times can be changed to tailor it to an individual application's availability requirements. The Automatic Segment Advisor doesn't analyze all of the data objects in the database. It intelligently selects them by identifying segments that are the most active, have the highest growth rate or exceed a critical or warning space threshold.

Database Managed Undo Retention Time Period
In Oracle9i, administrators had their choice of continuing to manage rollback segments on their own (manual undo management) or configuring the database to manage its own before image data (automatic undo management). Oracle refers to system managed before image segments as undo segments.

You didn't have to be an Oracle expert to know that manual rollback segments were "somewhat troublesome." Out of space conditions, contention, poor performance and the perennial favorite "snap shot too old" errors had been plaguing Oracle database administrators for over a decade. Oracle finally decided that the database could probably do a better job of managing before images of data than we could.

But implementing automatic undo retention didn't necessarily guarantee users a trouble free undo environment. There really is only one parameter that administrators can tweak in a system that uses automatic undo. The UNDO_RETENTION parameter specifies the amount of time in seconds that Oracle attempts to keep undo data available. Setting this parameter to the appropriate value could be described as more of an art than a science.

Set it too low and you are wasting disk space. In addition, you aren't taking advantage of being able to flashback your data to as far back as the disk space allocated to the undo tablespace allows. Set it too high and you are in danger of running out of freespace in the undo tablespace.

10G R2 comes to the rescue! The database now collects undo usage statistics, identifies the amount of disk space allocated to the undo tablespace and uses that information to tune the undo retention time period to provide maximum undo data retention. Administrators can determine the current retention time period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view.

Database Managed Multiblock Read Count
The DB_FILE_MULTIBLOCK_READ_COUNT parameter determines the number of blocks that will be read in a group (called a multiblock prefetch). Multiblock prefetching is usually invoked when a sequential scan of blocks is needed, such as a full table scan or index fast full scan. Prefetching groups of blocks reduces the I/O overhead associated with scans because multiple blocks become available in a single read request. Setting this value correctly can significantly improve database performance. 10G R2 collects information on the operating system optimal I/O size and the size of the data buffer cache to automatically tune the DB_FILE_MULTIBLOCK_READ_COUNT parameter for optimal multiblock prefetch performance.

Size-Based Tablespace Freespace Alerts
If you have read some of my past blogs, you know that we have several large data warehouses here at Giant Eagle. Tables having 3, 4, 5 billion rows and dozens and dozens (and dozens) of partitions are common. Some of our tablespaces have hundreds of GIGs of disk space allocated to them. Setting the free space alerts to 85% for warning and 90% for critical on these tablespaces led to us getting paged on out-of-space conditions when a substantial amount of free space was still available. 10G R2 remedies this problem by allowing administrators to also set warning and critical thresholds in absolute values (megabytes) of disk free space available. This is one feature that we will be implementing ASAP.

Enterprise Manager Direct SGA Attach
Have you ever had a database workload get out of control on you? You know, the kind of workload that consumes so many resources that you can't even log in to see what programs are running amok? The kind of workload that makes your UNIX admin's alarm page light up like a Christmas Tree? Enterprise Manager now provides screens that attach to the SGA and access statistics directly from the shared memory areas. No SQL statements are issued or required. This feature facilitates the problem determination process for slow and hung systems by allowing DBAs to access diagnostic information that would be otherwise unavailable.

Dropping Empty Datafiles
If you have ever added a datafile to the wrong tablespace or created it in the wrong directory structure, you know what a pain it was to correct the problem. The data usually had to be taken offline to correct the error. In addition, any time you move data around you potentially expose it to additional risks (personally, the less I have to juggle data around the safer I feel).

10G R2 provides an easier way to fix this mistake by allowing users to drop empty tablespace datafiles. The command below drops a datafile from tablespace TESTTBS1:

ALTER TABLESPACE testtbs1 DROP DATAFILE 'c:\oraclass\oradata\u06\testtbs1_empty_file_01.dbf'

If you attempt to drop a datafile that contains data, Oracle will return an error message notifying you of your mistake. In addition, you can't drop the last datafile in a tablespace (drop the tablespace instead) and the tablespace is required to be online and have a status of read/write.

V$SQLSTATS Performance View
Before we discuss the new V$SQLSTATS view, let me deviate for a minute to provide you with some quick SQL tuning advice. V$SQLAREA is one of my favorite SQL tuning views. I use the two queries below to identify poorly performing SQL. I take the traditional "top down" tuning approach and start tuning the highest resource consuming SQL idenfified by the scripts below.

The following query dentifies the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, hash_value, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads;

The following query dentifies the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, hash_value, sql_text FROM v$sqlarea WHERE buffer_gets > 100000 ORDER BY buffer_gets;

You can create a more readable report in SQLPLUS by inserting report breaks between the output lines. To generate the report breaks in SQLPLUS, issue the following statement before running the query:

BREAK ON disk_reads SKIP 2 --- for the disk read report and
BREAK ON buffer_gets SKIP 2 --- for the buffer get report

It's common knowledge that poorly performing SQL is responsible for the majority of database performance problems. The first query returns SQL statements responsible for generating disk reads greater than 5,000 while the second query returns SQL statements responsible for generating buffer reads greater than 100,000. These are good numbers to start with and you can adjust them according to the size of the system you are tuning. You'll notice that I divide the number of disk and buffer reads by the number of statement executions. If a statement is generating 1,000,000 disk reads but is executed 500,000 times, it probably doesn't need tuning. Heavy disk reads per statement execution usually means a lack of proper indexing. Heavy buffer reads usually means the exact opposite - indexes are being used when they shouldn't be.

But the SQLTEXT column in V$SQLAREA does not provide the entire text of the SQL statement. That's why I include the HASH_VALUE column in the report. I can use that value to dump the entire SQL statement from V$SQLTEXT using the statement below (where xxxxxxxx is the value in the HASH_VALUE column from the V$SQLAREA reports above):

SELECT sql_text FROM v$sqltext WHERE hash_value = 'xxxxxxxxx' ORDER BY piece;

Oracle 10G R2 provides a new view called V$SQLSTATS that contains a combination of columns that appear in V$SQL and V$SQLAREA. The benefits that V$SQLSTATS provides are as follows:

  • Since V$SQLSTATS contains the entire text of the SQL statement AND its associated performance statistics, we are no longer required to access both the V$SQLTEXT and V$SQLAREA to obtain the information we need.
  • Oracle states that V$SQLSTATS is faster and more scalable.
  • The data in V$SQLAREA has a tendency to get its contents flushed out just when you need to get additional information from it. The V$SQLSTATS view provides users with a longer access window. That I'm sure I'll like.

Thanks for reading my first blog on 10G R2! Look for Parts 2 and 3 to come soon.


Saturday, July 16, 2005  |  Permalink |  Comments (5)
trackback URL:   http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-07-16.1165649869/sbtrackback

Long time

Posted by rcrotti at 2005-07-20 08:21 AM
Chris,
Great blog. Look fwd to next part. How's it going since Be Free? Heard you're at Giant Eagle. Took a class on RAC with a former instructor friend of yours last week.

Long time no see

Posted by rcrotti at 2005-07-21 12:19 PM
Great blog. How's it going since the Be Free days? Attended a class taught by one of your old Cont. Tech guys.

access advisor: how to tune a single stmt

Posted by mstrobl20 at 2005-11-22 06:31 AM
One feature of sql access advistor ist:
* The ability to quick tune a single statement
I've read this in the docs as well in your blog, but never found the right entry point in em 10gr2. Did you ever try this?
BR,
Martin
Chris Foot
Senior Database Architect
Oracle Ace
Bio & Writings
Subscribe to my blog Subscribe to my blog
 
 

Powered by Plone