Oracle 10G R2 New Features
Rest assured, we will continue our discussion on the10G EM advisors once we have completed our review of 10G R2.
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.
Long time no see
Replies to this comment
access advisor: how to tune a single stmt
* 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
Long time
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.