Introduction
I would describe myself as a fairly good tuner. Not because tuning ever came easy to me, I have just spent a lot of time working with the optimizer. I still have troubles rewriting complex SQL statements from time to time but I will admit that 20 years of experience makes it easier on me than it used to be.

One of the areas I am strong at is using the proper indexes to influence SQL statement access paths. We know that an index can influence the optimizer to change an access path from a tablescan to a table lookup using an index ROWID. But indexes can also influence the type of join used as well as the order of the tables being joined.

Let's take a look at the benefits virtual indexes have and we'll finish this blog by reviewing how indexes can affect simple access paths, join methods and join orders.

Virtual Indexes
I use virtual indexes in both 9i and 10G. I like them because I can quickly do "what if" scenarios. I can create the index and present it to the optimizer without waiting for the index to become populated and stored on disk. This feature allows me to reduce the amount of time I spend tuning, which is always a good thing.

I also use virtual indexes when I am training myself and others. Most production environments do have test counterparts, but there are times when I need to teach many different indexing concepts and waiting for indexes to build often becomes a detriment to the learning process.

9I OEM Virtual Index Wizard
The Virtual Index Wizard feature is provided in the Tuning Pack option of 9I OEM. Although there are a couple of ways to activate the wizard, let's take a look at the one that I most often use. Since we are going to be naviating through the SQL Analyze tool, we might as well review some of the other features it provides.

SQL Analyze
9I OEM provides the SQL Analyze tool to view access path information. SQL Analyze is only available if you have purchased the tuning pack option for 9I OEM. This screenshot shows me navigating through 9I OEM's menus to activate SQL Analyze.

When you activate SQL Analyze, the first step the utility performs is to display a listing of all of the top resource consuming SQL statements that are contained in the library cache. If the statement has been flushed from cache, you won't find it in this display.

The tool lists all of the databases on the left side of the panel that you worked with in the past. As you can see, I am a pretty active user of SQL Analyze. I can double-click on any statement in the top SQL listing to view its access path information. I can also use the menu system at the top of the screen to create a blank worksheet for new queries that I can enter manually. I found the statement I wanted from the listing and double-clicked on it to activate the SQL tuning panel.

The panel displays the SQL statement, and like our other utilities, allows us to step through the access path the query is taking. I can click on the SQL drop down menu at the top of the screen to ask Oracle to explain the statement using the various optimizer modes that are available.

SQL Analyze Tuning Tools
SQL Analyze provide us with tools that facilitate the SQL tuning process. The tools menu at the top of the screen allows me to choose from three different wizards. We'll review the Virtual Index Wizard in just a moment.

The Hint Wizard allows us to choose hints from a drop-down menu system, while the SQL Tuning Wizard activates an intelligent advisor that provides us with SQL tuning recommendations. Once you have run the SQL Tuning Wizard a few times, it's pretty easy to see that it was the precursor to some of the more advanced utilities that are now available in Oracle 10G.

Virtual Index Wizard
The Virtual Index Wizard allows me to create virtual indexes on tables that my query is accessing. The key word in that last sentence is "virtual". Oracle does not permanently create the index. The intent of the wizard is to allow users to determine the potential performance impact a new index would have on the statement being analyzed. It is then up to the user to permanently create the index.

If I click on the Virtual Index Wizard menu selection, 9I OEM responds by displaying the Virtual Index Wizard introduction panel. When I click on next, the wizard displays a panel that allows me to select the columns for my virtual index. Clicking next again displays a panel that allows me to attempt to force Oracle to use the new index or allow it to choose the new index on its own. My personal preference is to allow Oracle to choose the index on its own without providing any additional prodding.

Clicking Next again displays the projected cost improvement the new index will have on the statement being analyzed. I used a full table scan on a rather large table as an example. Based on the display, we can be pretty sure that the new index will have a positive affect on our statement's performance. The last panel also displays a button that allows us to view the before and after access paths.

The Virtual Index Wizard does a fairly good job of estimating the impact that new indexes will have on a SQL statement. It is an excellent tool to use when you are starting your tuning education as well as evaluating access paths in Oracle 9I databases.

I was surprised when I found that this feature was not contained in 10G Grid Control. I continue to firmly believe that using a tool to quickly build a virtual index, determine if the optimizer will use it and then display a prediction of the index's performance boost (or degradation) is very beneficial to the tuning process. I used the virtual index wizard dozens of times and will continue to use it when I tune 9I (and earlier) databases.

Virtual Indexes in Oracle10G
Although Grid Control doesn't provide the same graphical interface to create virtual indexes as it did in 9I OEM, all is not lost. We can revert to the tried-and-true SQL*PLUS command line to build virtual indexes using the "NOSEGMENT" option. The only feature it won't provide us is the forecast of the statement's performance.

The format of this blog page doesn't provide me with enough space to show you the output from my SQL*PLUS demo. Not without a whole lot of editing anyway. So, I decided that the best option would be to create a separate HTML page for the 10G virtual index demo output.

The demo will show you:

• How to create virtual indexes in 10G
• How to configure your session to allow the optimizer to choose a virtual index as an access path
• The affect the "_use_nosegment_indexes" parameter has on access path selection
• The error messages returned if you attempt to manipulate virtual indexes
• Information stored in the data dictionary pertaining to virtual indexes

Here's a link to the 10G SQL*PLUS Virtual Index demo!

Now that we understand how to create virtual indexes, lets' take a look at how any index can influence simple access paths, join methods and join orders.

Index Affects on Access Paths
Let's use a nested loop join as a quick and somewhat easy example. In a nested loop join, one of the tables in the join is known as the outer table while the other table (the table being probed) is known as the inner table. Oracle reads a row from the outer table and uses the columns in the join condition to probe the inner table.

Take a look at the SQL statement and graphical access path display below:

select a.employee_id, a.last_name, b.department_id, b.department_name,
c.street_address, c.postal_code, c.city, c.state_province
from hr.employees a, hr.departments b, hr.locations c
where a.department_id=b.department_id
and b.location_id=c.location_id
and a.employee_id = 174
order by a.last_name;

We are joining three tables together HR.EMPLOYEES, HR.DEPARTMENTS and HR.LOCATIONS. Notice that we have two different nested loop join operations. Oracle only joins two tables together at a time. It then creates an intermediate result set and uses that result set to join to the next table. It can use any type of join method available for any of the join operations.

The indexes we have available to us are:

• EMP_EMP_ID_PK - index on the EMPLOYEE_ID column of the HR.EMPLOYEES table
• DEPT_ID_PK - index on the DEPARTMENT_ID column of the HR.DEPARTMENTS table
• LOC_ID_PK - index on the LOCATION_ID of the HR. LOCATIONS table

The optimizer has chosen to join the HR.EMPLOYEES AND HR.DEPARTMENTS tables first using the nested loop join method. It is on this join condition - A.DEPARTMENT_ID = B.DEPARTMENT_ID. HR.EMPLOYEES is known as the outer table and HR.DEPARTMENTS is classified as the inner table of the join.

The optimizer looked at the local predicate "A.EMPLOYEE_ID=174" and checked to see if there was an index that it could use to improve data access performance. It found the EMP_EMP_ID_PK index that is defined on the EMPLOYEE_ID column. It will access the index first and retrieve the ROWID for the row that has an EMPLOYEE_ID of 174. It will use the ROWID to probe the HR.EMPLOYEES table.

As stated previously, the optimizer has chosen the nested loop join operation. It will access the HR.EMPLOYEES table using the EMP_EMPID_PK index, retrieve the value for DEPARTMENT_ID and use it to probe the HR.DEPARTMENTS table looking for a match (A.DEPARTMENT_ID = B.DEPARTMENT_ID). If it finds a DEPARTMENT_ID value in the HR.DEPARTMENTS table that matches, Oracle will use that row in future operations. If it does not match, Oracle will discard it.

Notice that the optimizer has chosen to use the DEPT_ID_PK index that is built on the HR.DEPARTMENTS' DEPARTMENT_ID column. Since we are probing this table using the values we found for DEPARTMENT_ID from the HR.EMPLOYEES table, Oracle will use the index on the HR.DEPARTMENTS' DEPARTMENT_ID column.

The B.LOCATION_ID = C.LOCATION_ID is the join condition that is used in the second join operation. The intermediate result set is used as the outer table and the HR.LOCATIONS table is used as the inner table. The same type of probe occurs. Since we are looking for matches using the LOCATION_ID contained in the intermediate result set, the optimizer will choose the LOC_ID_PK index.

Here's how indexes can influence the type of join used and the order of the tables being joined:

• If we didn't have indexes on either of the first two tables being joined, the optimizer would most likely have used a hash operation instead of a nested loop join to join the tables together. The optimizer knows that the hash join operation is best used for joins that return larger result sets. It could also have influenced the join order of the tables. In the example, the optimizer could have chosen to join the HR.DEPARTMENTS table to the HR.LOCATIONS table first. It would have then used the intermediate result set created to probe the HR.EMPLOYEES table.
• The more indexed columns we have on a table, the greater the chance that the optimizer will use it as the outer table of the nested loop join. It can filter out more rows before it sends it to the join operation. The earlier it can filter rows out, the better.
• If we have an index on both join columns (i.e. indexes on the DEPARTMENT_ID columns for both the HR.DEPARTMENTS and HR.EMPLOYEES tables), Oracle will use the DEPARTMENT_ID index on the inner table but not the outer. Why is that? Oracle doesn't know if it has a DEPARTMENT_ID VALUE in the inner table that matches a DEPARTMENT_ID value in the outer table unless it reads each and every row of the outer table to do the match. Think about it. It knows that since it has to read every row from the outer table to perform the match, the only indexes it can use are the indexes built on local predicates (i.e. "A.EMPLOYEE_ID=174").
• If we have an index on the join column for one table and not the other, the optimizer will often favor the table that has the index on the join column as the inner table of the join. It knows it can't use an index on the join column if it is the outer table but it can use the index if it is the inner.

This is just one quick example to show you the impact that indexes have on access path selection. The key to success is to continuously experiment with the optimizer. One of the features that can speed up your experimentation is to use virtual indexes. Remember, this feature uses an "undocumented" parameter in 10G.

I would like to thank super-DBA Mark Shore for re-igniting my interest in both 9I and 10G virtual indexes. Marks has a natural talent for this profession which I often wish I had.

Thanks for Reading,

Chris Foot
Director of Operations
Remote DBA Experts
Oracle Ace

The key to being a successful Oracle DBA is that you must be willing to accept the fact that with each new database release, you will have to adjust the way you administer your environments. In my profession, I work with many DBAs from companies across the globe. The most successful administrators I have met are the ones that intelligently leverage the new features that are available in the releases of the database they are administering.

Running Commands and Scripts Against Multiple Targets
Being involved in an IT audit, like death and taxes, is inevitable. If you work as a technician for any length of time, you will be working with an auditor and some point. Since we provide remote database services, our organization is audited numerous times per year. Sox audits, security audits, HIPPA audits - you name the audit and we have been involved in it. We have an office that is left open full-time for onsite auditors.

Auditors often ask you to run scripts on the operating systems and databases your unit is responsible for supporting. In the past, you would review the scripts and then distribute them to team members to run on the environments they were responsible for supporting. 10G Grid Control makes this task a whole lot simpler. Grid provides you with the capability of running host commands and scripts against multiple host targets at the same time. Virtually any command or script that can be executed on a single host can also be executed on multiple hosts using 10G Grid Control R2. Here's a blog that shows you how to use 10G Grid Control R2 to run operating system scripts against multiple targets.

10G Grid Control R2 also provides an easy-to-use panel that allows administrators to run SQL scripts against multiple database targets simultaneously. Just like its host command counterpart, all you need to do is code up a SQL statement, choose a set of database targets, run the SQL against the multiple targets and review the output. The benefits that this feature provides is only constrained by the DBAs imagination. Here's a link to a blog that will show you how to run scripts against multiple database targets.

Restore Points
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.

Let's go back to my days as an Oracle instructor…. A common question in the Oracle DBA backup and recovery class was "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."

That was before Flashback Database came along. Oracle's Flashback Database feature provided 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. Administrators often used a timestamp during the execution of flashback operations.

10G R2 enhances Flashback Database by allowing administrators to associate a user-defined name with a point-in-time. The user-defined name, called a restore point, can be used in place of a SCN or timestamp when executing a FLASHBACK TABLE or FLASHBACK DATABASE statement.

A SQL statement is used to create the restore point which means it can be embedded in application programs as we as executed on an as-needed basis in SQL*PLUS. The example below shows the creation of a restore point:

CREATE RESTORE POINT batch_daily_cycle_complete;

Now if I execute the following statement:

UPDATE cti.employee_salary_table SET
salary = 200000 WHERE empl_lname = 'FOOT';

My boss can execute this statement to correct the 'mistake' (although I prefer to call it a fully justifiable pay adjustment):

FLASHBACK TABLE cti.employee_salary_table TO batch_daily_cycle_complete

10GR2 also provides guaranteed restore points which ensure that the database can be flashed back to the point-in-time they were created. Guaranteed restore points save disk space because only the flashback logs required to meet the guaranteed restore point need to be retained by the database. The statement below creates a guaranteed restore point:

CREATE RESTORE POINT batch_daily_cycle_complete GUARANTEE FLASHBACK DATABASE;

Automatic Undo Retention
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 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.

RMAN Automatically Creates Temporary Datafiles During Restore
Ever perform a restore and forget to recreate the temporary tablespace's data files? I have. The database comes on-line but the first time a user's sort overflows from memory to disk, an error code is returned. Recreating a database's temporary files is one of those activities that are often overlooked. 10G R2 RMAN will automatically recreate temporary datafiles belonging to locally managed temporary tablespaces during the recovery operation.

Enterprise Manager Statistics Management Support
A new link titled 'Manage Optimizer' statistics has been added to the database administration home page in Enterprise Manager. Clicking on the link displays the new Manage Optimizer Statistics home page. The new home page provides wizards and templates that facilitate and automate optimizer statistics management. In 10GR2 administrators are able to use Enterprise Manager to gather, restore, delete, lock and unlock statistics

Why would you want to lock and unlock statistics? Let me describe one real-life example from a recent project. We have a warehouse system that contains an extremely volatile table. Describing it as volatile is like stating the Titanic sprung a small leak. Hundreds of thousands of rows are added and removed around the clock.

The level of activity is based on the particular business process taking place. At one particular time, the table can contain hundreds of thousands of rows and at other times it can contain dozens. OK, so when do you generate statistics? If you run DBMS_STATS at the same time each day, in most cases you would think you would get a predictable set of statistics generated. Not so in this table's case, sometimes you get hundreds of rows and some times you get hundreds of thousands.

If you are unlucky and generate statistics on the table when it has hundreds of rows, access paths suffer when the table grows to hundreds of thousands. So, we ran DBMS_STATS when we knew the table contained numerous rows and never generated statistics on it again. Problem is that we can no longer easily use the SCHEMA option for DBMS_STATS. Freezing statistics on this table will allow us to use the SCHEMA option and not generate statistics for selected tables.

DML Error Logging
What I like about SQL*LOADER is its ability to continue processing through load errors. If the record being loaded is rejected because of a unique constraint violation or discarded because it does not meet some user-defined criteria, SQL*LOADER places the record into a discard or reject file and keeps on running until it has reached a user-specified maximum number of rejects or discards.

The loader's log file will show how many records were loaded, rejected or discarded. I can look at the messages and review the discard or reject files, fix the problem and attempt to reload them again.

In 10G R2, this same type of processing has been applied to bulk DML operations. Users are able to specify whether they want to log errors or abort the entire DML statement, set the amount of detail information logged and the maximum error threshold. So instead of the entire DML statement blowing up and rolling back, Oracle will log the errors to the error table and continue processing until it exceeds the maximum number of errors. Just like SQL*Loader!

Oracle 10G R2's DBMS_ERRLOG package is used to create the error log output table and link it to the table being updated. The package's specification is provided below:

DBMS_ERRLOG.CREATE_ERROR_LOG (
dml_table_name IN VARCHAR2,
err_log_table_name IN VARCHAR2 := NULL,
err_log_table_owner IN VARCHAR2 := NULL,
err_log_table_space IN VARCHAR2 := NULL,
skip_unsupported IN BOOLEAN := FALSE);

Most of the columns are pretty self explanatory: table name being updated, error log table name, owner of error log table and the error log table's tablespace. If the SKIP_UNSIPPORTED is set to TRUE, column types that are not supported by error logging will be skipped over and not added to the error logging table. If it is set to FALSE, an unsupported column type will cause the procedure to fail.

Here's a quick example:

Let's create or table that will be updated:
SQL> r
1 CREATE TABLE foot.emp_table
2 (empno number(4), ename varchar2(10), job varchar2(8))
3* TABLESPACE users;

Table created.

Add a primary key:
SQL> ALTER TABLE foot. emp_table ADD PRIMARY KEY(empno)
2 USING INDEX TABLESPACE users;

Table altered.;

Load some rows:

SQL> INSERT INTO foot.emp_table VALUES
2 (7499, 'ALLEN', 'SALESMAN');

1 row created.

SQL> INSERT INTO foot.emp_table VALUES
2 (7521, 'WARD', 'SALESMAN');

1 row created.

SQL> INSERT INTO foot.emp_table VALUES
2 (7566, 'JONES', 'MANAGER');

Let's cause a unique constraint violation:

SQL> insert into foot.emp_table select * from foot.emp_table;
insert into foot.emp_table select * from foot.emp_table
*
ERROR at line 1:
ORA-00001: unique constraint (FOOT.SYS_C009069) violated

Create the 10G R2 error logging table:

exec dbms_errlog.CREATE_ERROR_LOG ('emp_table','dml_errors_emp_table')

Let's create a table that we can update and change one row to see if we can get one row to load and 2 to fail and be placed into the DML_ERRORS_EMP_TABLE:

SQL> create table foot.emp_load_table tablespace users as select * from foot.emp_table
Table created.

Update one row to change the EMPNO column's value to avoid the primary key violation:

SQL> update foot.emp_load_table set empno=123 where empno=7499;
1 row updated.

Rerun the statement specifying the new 10G R2 error logging syntax. Use our new load input table so that our one changed row will be loaded and two will be rejected and placed into the DML_ERRORS_EMP_TABLE:

SQL> insert into foot.emp_table select * from foot.emp_table
LOG ERRORS INTO dml_errors_emp_table('test_load_20050718')
REJECT LIMIT UNLIMITED;

1 row created.

OK, we loaded one. What happened to our other two? Let's see what our DML_ERRORS_EMP_TABLE contains.

First, let's describe the DML_ERRORS_EMP_TABLE:

SQL> DESC foot.dml_errors_emp_table

ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
EMPNO VARCHAR2(4000)
ENAME VARCHAR2(4000)
JOB VARCHAR2(4000)

Our error logging table contains an incrementing error counter, the error message, ROWID, error type, tag (contains our users specified name from above -'test_load_20050718') and the three columns of our table.

Let's select from the table. I have truncated the ORA_ERR_MESG$ table for readability sake:

SQL> SELECT ora_err_number$, ora_error_mesg$, emp_no FROM foot.dml_errors_emp_table;

ORA_ERR_NUMBER$ ORA_ERR_MESG$ EMP_NO
--------------- -------------------------------------------------- ------
1 ORA-00001: unique constraint (FOOT.SYS_C009069) violated…..7521
2 ORA-00001: unique constraint (FOOT.SYS_C009069) violated….7566

We loaded one row and rejected two rows due to primary key constraint violations.

Conclusion
I hope you enjoyed this series on 10G features.

Favorite DBA Conversation #1
Any discussion that ends in "I hope you're not too busy because we need it now." I really dislike the conversation that ends in "we needed it yesterday." If you needed it yesterday you should have stopped in to discuss this request a week before yesterday. We all know these types of requests because we have all gotten them. One of your customers pops in and states:

"We need a complete refresh of XYZ database from production. Oh, and its an ERP application database and we don't have enough disk space in test to hold everything so you'll need to figure out how to reduce its disk size requirements AND make sure the application still works.

I know the box only has 1 CPU and 250 MEG of memory, but can you make sure we still get good performance? We're testing some application coding changes and we need to get performance benchmarks. We'll compare the stats from the test box to the ones we get from our 22 CPU production box. It may not be an "apples to apples" but I think the boxes are close enough to compare the performance stats.

I know its Friday afternoon at 3:00 and even though we knew about this refresh 14 weeks ago, we didn't think we needed to tell you until 1 day before we wanted it. So, we would like to have the system ready by Monday."

Over the last 20 years I have received dozens of these last minute requests. Requests that have made me cancel plans, postpone trips and generate numerous apologies to friends and family. The work never bothered me, but the timing of the requests sometimes did. It also reduced the quality of service I provided to my other customers. When you have DBAs jumping from one non-planned request to another, it affects their ability to work on planned requests as well as perform proactive administrative duties.

Fix #1: You minimize these requests by creating, advertising and enforcing DBA work request SLA agreements. Agreements that clearly state lead times per type of request. The more widespread the problem is at your organization, the more advertising you must do. It really becomes a sales pitch on the importance of a DBA unit being able to forecast workloads. The unit can't provide quality service to their customers if they are being affected by work requests that could have been planned for - but weren't.

The mantra should be "all of our customers suffer when we receive un-planned work requests." I'm not afraid of telling application manager A that she can't get her planned requests done because application manager B just gave us 32 emergency requests. Kind of like putting them both in a jar and shaking it up a bit.

Fix #2: 10G Grid Control allows you to clone both the Oracle Home software installation as well as the Oracle database. Grid will automatically adjust the specific home properties (IP address, host name, listener settings) during the cloning process. 10G DBCA templates allow you to clone a new database from existing databases. In addition, DBCA allows you to clone just the source database file structures or the structures and the data.

You can also use some of the 10G Data Pump features to tailor the new database to your user's specifications. 10G Data Pump provides numerous options that:

• Specifiy what objects you want to transfer from one database to another
• Transfer a subset of data from those objects using various filtering options
• Remap objects during the migration process. You can remap schema objects from one user to another, from one tablespace to another

You already know I'm a huge fan of 10G Data Pump. You need to leverage the features this tool provides to customize schema and data transfers from one database to another. You shouldn't be writing scripts when you don't need to. The only way you will realize the full potential of 10G Data Pump is to READ THE UTILITIES manual and see how powerful 10G Data Pump really is. It will make your life easier.

Favorite DBA Conversation #2
You come in to a meeting and you're confronted by an application manager who tells you that you need to tune your database because his ace developer "Sir Lawrence of LongSQL" can't get his 32 page SQL statement to achieve sub-second response times.

You know the SQL statement that I'm talking about. It's the one that contains 15 inner and outer joins, 12 subselects, and…….(feel free to add any other type of complex SQL Syntax here).

Note from database administrators to application developers:
Just because you can code a 32 page SQL statement, it doesn't mean you should. We all understand how powerful the SQL language is, but do you really need to try and use every feature available in a single statement? There's a difference between leveraging the power of the SQL language and attempting to perform too much work in a single SQL statement. Generating long, complex SQL statements that no one can debug does NOT classify you as a supercoder. It does classify you as something else, but I can't say that publicly.

Favorite DBA Conversation #3
Favorite DBA Conversation #3 combines components of conversations #1 and #2. It occurs when you are approached by a customer who says they have developed this "small" application that goes live in two weeks and it isn't performing "as well as we think it should." This means that you have to review the SQL from 23 new application programs in those two weeks.

Fix #1: Reinforce the need for the DBA team to be involved in all application development projects. This can be done by creating a series of application design review meetings. A dedicated an entire blog on this subject.

Fix#2: You will need to start a top down performance analysis project that focuses on tuning the most poorly performing queries first. If you don't have access to the 10G Grid Control sissy GUI tools, you can use the V$ views to retrieve the top resource consuming statements. You can use the V$SQLAREA view for Oracle9I and below. 10G offers the V$SQLSTATS view that provides some distinct advantages over its previous counterparts.

10G 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. Until V$SQLSTATS came along…

If I want to perform a traditional "top down" tuning approach and tune the highest resource consuming SQL, I'll use the statements below to identify the top resource consuming queries.

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

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

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

SELECT buffer_gets, executions, buffer_gets/executions, address, sql_text FROM v$sqlarea WHERE buffer_gets > 10000 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

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 10,000. I used these numbers just as an example but I sometimes use them as a starting point. I'll then adjust them up or down accordingly, based on their output. The numbers also depend on the system I'm reviewing. I'll use different numbers for OLTP environments than I would for data warehouses.

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, poor selection criteria, etc.. Heavy buffer reads sometimes means the exact opposite - indexes are being used when they shouldn't be.

But I'm personally most interested in workload, that's why I most often use the buffer cache hits in my initial queries.

But the SQLTEXT column in V$SQLAREA does not provide the entire text of the SQL statement. That's why I include the address 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 address column from the V$SQLAREA reports above):

select sql_text from v$sqltext where address = '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's one of the key benefits to this view.

Next Up
The series continues.

Ease of Installation
I remember the good old days. When I began my career, the Oracle installers were just screen painters that forced you to use the TAB key to navigate back and forth between selections and screens.

Installations used to take hours and experiencing problem after problem was commonplace. Successfully installing Oracle became a rite of passage as a DBA. Four hours after you started the installation (and fighting through errors, warnings and placing a half dozen CDs in the reader), you would finish up and think to yourself "I am an installation GENIUS!". You next thought would be "I hope I don't have to go through this again for a while."

With each new release, the installer became easier to use, installation speeds increased and the number of installation problems decreased. In a few years, I'll be telling new DBAs "You don't know how good you have it. I remember when installations used to take three days... We were real DBAs back then. We even had to log in at NIGHT TIME and take the data OFFLINE to do work." Darn kids don't know how good they have it nowadays….

We do DOZENS of Oracle installations a month here at Remote DBA Experts. One of the benefits of working here is the sheer thrill of working on virtually every operating system that Oracle makes a product on. Name the O/S and hardware combination and we can truthfully say "been there and installed an Oracle product on it."

When our first set of 10G CDs arrived a couple of years ago, the DBA opened up the package and said "Hey, where's the rest of them? I think they forgot to send me all of the CDs I need to do the install." We checked MetaLink and found that Oracle10G only needs a couple of CDs. One main CD and another called "The Companion CD" which really should be called "The Companion CD that is required for all installs" since it pretty much has to be installed when you install Oracle.

The team here really likes the fact that the installer now performs pre-installation requirement checks before laying down the software. The installer itself now determines if the O/S environment is configured to correctly run the Oracle software. Having the installer verify that you have performed "your pre-installation" duties is very beneficial.

10G Data Pump
I'm a big fan of 10G Data Pump. Once you become accustomed to the new environment, it's a pretty good utility. There is a learning curve - like the first time you try to restart a Data Pump job by just rerunning the script again. You'll quickly find out that Data Pump has a command for that. At least it provides you with the "Master table exists" error message to point you to your mistake.

Here's a quick list of some of the features I like:

• The utility IS faster than Export and Import. Maybe not as much faster as Oracle says it is - but it is faster. I have personally seen Exports and Imports run for hours - and days.
  
  
• I like being able to use parallelism to improve performance.
  
  
• You can stop and restart jobs. How great is that? The master table knows where you left off so you don't have to spend all of that time trying to code a new Import statement that won't duplicate rows in tables that were successfully loaded. This is really helpful.
  
  
• How many times did you have to "pick a number, any number" when you had to determine how much space you would need for your Export Dump file? Data Pump provides an option that estimates the output dump file size that would be created by a Data Pump Export.
  
  
• One of my favorite features is the network transfer option that allows administrators to attach to any source database that can be accessed from the network, transfer the data and write it to the target database. No dumpfile required! Administrators use the NETWORK_LINK parameter to specify a pre-created database link that points to the source database.
  
  
• You can use the Data Pump Flashback feature to go back in time and export data how it looked in the past. This helps when you get a visit from one of your developers stating "I think I might have updated 47 production tables by mistake, but I won't know until I see the data." Use the flashback feature during the Data Pump Export and use the Data Pump Import utility to pump the data into a new schema for the developer to review.
  
  
• You can use the EXCLUDE parameter to exclude specific objects using custom syntax. For example, EXCLUDE=INDEX:"LIKE 'DEPT%'" would exclude indexes that start with the name "DEPT".

The more I thought about the Data Pump features I liked, the longer I thought this blog would be. So here are links to three blogs I wrote on Data Pump.

• Data Pump Part 1 Features and Benefits - A general overview of the 10G Data Pump Utility.
  
  
• Data Pump Design and Architecture - We dig deeper into inner-workings of the Data Pump utility.
  
  
• Data Pump Export - We continue the Data Pump discussion by learning how to use 10G Data Pump Export to "pump" data out of an Oracle database.
  
  
• Data Pump Import - The series finale on Data Pump Import.

10G Automatic Alerting
Oracle10G provides early warning mechanisms to alert administrators of possible error conditions. The database itself collects numerous metrics that were previously collected by 9i's Oracle Enterprise Manager. Administrators are able to select from dozens of server-generated alerts. Server generated alerts are activated by a new background process called MMON. MMON is able to access the SGA directly and perform the metrics calculations and threshold monitoring.

10G Grid Control enhances these capabilities by providing administrators with a GUI interface to administer alerts and configure notification mechanisms. Administrators are able to use Grid Control or the standalone version, Database Control to set up e-mail and pager notifications. In addition, all server-generated alerts are displayed on the Database Control/Grid Control home pages.

As readers of this blog know, I'm a huge fan of 10G Grid Control. Although the database alerts can be set up in the database using the archaic command line interface, if I can do it using Grid Control with a few clicks of the mouse, that's the tool I will use. And to think I used to call 9i OEM "a Sissy GUI Tool".

Setting up 10G's "early warning system" places you in the proactive DBA category as opposed to reactive. The term we us to describe a pure reactive DBA is "firefighter". That's the DBA or DBA team that runs from one fire to the next. I will acknowledge that we are ALL firefighters from time to time. At one of my previous jobs, I spent six months working with another DBA trying to beat an absolutely atrocious third-party vendor package into submission.

You all know the type of third-party vendor package I'm talking about. Someone from your management food chain shows up in your cube and states "Hey, even though you rated this vendor an absolute 0 in all technical categories, and flatly stated that we should not consider them as a viable alternative, we chose them anyway. The Senior VP from the business unit liked the color of their screens. I'm sure you'll be able to get it working."

You then get on 72 conference calls with the vendor after the package crushes a 6 CPU box with 10 concurrent users. Most of the conversations are with the vendor's lead technical experts who are as arrogant as they are inexperienced. Their continued mantra is "you don't understand how Oracle works, it's your fault, you don't understand how Oracle works, it's your fault….". This mantra never stops even though you show them poor SQL coding, transaction looping problems, etc…

But I digress... During my career as an Oracle instructor, one of the most common recommendations I used to provide to my Oracle students was the benefits of database monitoring. My catch phrase was "We monitor by day so we don't get called at night." By the end of the DBA I class, all I would have to say is "We monitor by day…" and the class would respond accordingly. I would then award them with a set of scripts that allowed them to do database connect checks and track datafile free space.

10G Grid Control does all that (and more) for you automatically. The set of checks range the spectrum, from agent unable to connect to target (that means either the database, server or agent broke), to tablespace freespace and alert log errors. As I stated previously, 10G provides dozens of alert choices and is your one-stop-shop for proactive database monitoring. That's a good thing. I would rather wade through dozens of alerts I don't want to activate than spend ANY time coding an alert that wasn't provided by the toolset.

One of my previous blogs titled "Configuring 10G OEM Grid Control's Automatic Alert Notification System" will help you set up proactive performance monitoring in 10G. The title says it all. It provides everything you need to activate 10G Grid Control's early warning system.

Next Up
The 10 Thinks I like About 10G Continues. Could be more than 10 maybe...

We've spent a lot of time discussing SQL statement tuning. We started off this 15 blog series with a discussion on system triage and finished with Oracle access path identification and tuning. I also dedicated a dozen blogs or so to 10G Grid Control performance monitoring and analysis tools. With the abundance of long-winded information I have made available on this blog, I thought it might be beneficial to condense some of it into a review of some of my favorite tuning toolsets.

My Number One Tuning Tool of All Time - 10G Grid Control
If we are administering a 10G database, our administration tool of choice is either 10G Database Control (non-grid, standalone version), or 10G Grid Control to administer it. The advanced alerting, monitoring and administration features make this tool our preferred method of administration. The advanced performance monitoring and analysis features make it my number one tool for performance problem determination.

The future of Oracle database tuning will be administrators interpreting and implementing the recommendations generated by the intelligent advisors and ADDM. It is a foregone conclusion that Oracle will continue to improve upon its performance monitoring and analysis toolsets. Self tuning features are no longer options that are "nice to have", they are requirements for Oracle's competitive survival. SQL Server is continuing to scale, moving into areas that were once dominated by UNIX big-iron machines running Oracle databases. Oracle must compete with SQL Server's ease of use or it will undoubtedly lose market share.

As the intelligence of the advisors and ADDM increases, the need to possess an in-depth knowledge of Oracle and the usage of detailed diagnostics to improve database performance will decrease. And you heard it here first folks, I also think that reading SQL traces and statistics dumps will be a thing of the past. My crystal ball tells me that its just a matter of time until Oracle's SQL advisors make SQL traces and statistics dumps less and less important until they become totally unnecessary.

The new breed of top tuners will be the administrators who focus on how to use the toolsets and interpret their output. Not the tuners who spend the majority of time digging down into the dark, inner workings of the Oracle software. I'm not saying that knowing how the database works is immaterial. I am stating that this intimate knowledge will become less and less important as the tools mature. It's only a matter of time until tuning is relegated to a minor sub-task that can be scheduled between other activities.

Here's a listing of my favorite 10G Grid Control tools.

Host Performance Home Page
If someone tells me "the whole system is slow", the first thing I'm going to do is review the host platform's key performance indicators. 10G Grid Control's Host Performance panel provides me with exactly the information I'm looking for - current CPU, memory and disk resource utilization.

Each resource's graphical display contains links that allow me to view more detailed statistical information. Each of the drill down panels for CPU, Memory and Disk contains a drop down menu list that I can use to view current as well as historical performance statistics for the last 24 hours, 7 days and 31 days. The main performance panel also displays information on the top resource consuming processes that are currently running on the host. Once again, a drop down menu lets me switch between top CPU and top memory consumption reports.

For a more in-depth discussion on host performance tuning, please refer to my blog titled "Host Performance Monitoring Using 10G Enterprise Manager Grid Control".

10G Grid Control Database Performance Home Page
If I want to review database performance, I will access the 10G Grid Control Database Performance Home Page, which is available in both 10G Database Control and Grid Control.

The database performance home page allows me to review performance historically, identify what sessions are dominating "finite system resources", activate advisors and drill down into the top resource consumers. I can then quickly review the SQL they are executing. Does it get any better than that?

I challenge anyone to tell me that they would be able to monitor database performance more efficiently using archaic command line toolsets. By the time you have identified the problem, I'll have already contacted the user running the SQL and created a profile to improve its performance. If I have Grid Control's alerting system configured (which I always do), Grid Control's database performance threshold alert would have already notified me that a problem was occurring. By the time you got that call from an irate user, I would have had it solved. If you want to learn how to configure 10G Grid Control's performance alerting feature, please turn to my blog titled "Configuring 10G OEM Grid Control's Automatic Alert Notification System".

To learn more about 10G Grid Control's database performance monitoring and analysis features, please turn to my blog titled "Database Performance Monitoring Using 10G OEM Grid Control".

10G Grid Control Advisors
OK, I'll admit it. I'm hooked on the advisors. At first I was very wary of their recommendations, but the longer I worked with them, the better I liked them. I have lots of experience tuning Oracle, but if an advisor is going to make my job easier (and let me spend less time debugging a performance problem), I'm all for it.

For an overview of 10G Grid Control's advisory features, please refer to my blog that is aptly titled "An Overview of 10G Advisors".

Here's a listing of blogs that provide more specific information on the individual advisors. Please note that each title below also links to a blog on that advisor.

• 10G SQL Access Advisor - The SQL Access Advisor recommends a set of materialized views and indexes based on a supplied workload input. The tool can also help administrators optimize materialized views to take advantage of fast refresh and query rewrite capabilities. In addition, the SQL Access Advisor may recommend dropping indexes and materialized views that aren't being used. The SQL Advisor ranks and groups the recommendations based on the positive impact they will have if implemented.
  
  
• SQL Tuning Advisor - The SQL Tuning Advisor, as its name implies, provides SQL tuning recommendations and includes information justifying why it generated those recommendations. The recommendations may include collecting statistics on objects, new index creation, restructuring the SQL statement or the creation of a SQL profile to create a more optimal access path. I have a more detailed blog the SQL Tuning Advisor titled "SQL Access Advisor Revisited".
  
  
• Segment Advisor - Although I don't use this advisor as much as the SQL Tuning and SQL Access advisors, I do use it on a regular basis. The 10G segment advisor identifies segments that have become fragmented as a result of update and delete operations. The 10G R2 Segment Advisor has been enhanced to identify tables that suffer from excessive row chaining and row migrations. Oracle describes these objects as being sparsely populated. Not only do sparsely populated objects waste space but they can also contribute to SQL performance problems.

Automatic Database Diagnostic Monitor (ADDM)
ADDM is a "recommendation engine" that uses performance information stored in the Automatic Workload Repository (AWR) as input. Here's a blog that provides a detailed description of the Automatic Database Diagnostic Monitor. By default, AWR snapshots occur every 60 minutes. For more information on the AWR snapshots, please turn to my blog titled "Working with Automatic Workload Repository Performance Snapshots".

To learn how to tailor the 10G AWR snapshot environment to meet your specific needs, please refer to my blog titled "Configuring and Administering Automatic Workload Repository Performance Snapshots".

After the AWR information snapshot is executed, the Automatic Database Diagnostic Monitor is triggered to analyze the information contained in the AWR for the period pertaining to the last two Snapshots. ADDM's output includes a plethora of reports, charts, graphs, heartbeats and related visual aids. In general, ADDM provides users with a top-down analysis of performance bottlenecks and their associated resolutions. But you can also execute ADDM manually to generate recommendations for current, as well as historical, time periods.

Statistical information, by itself, is often useless. If you have nothing to compare a measurement to, how will you know if its good or not so good? 10G Grid Control allows two sets of snapshots to be compared to one another. The results of the comparison are displayed graphically using horizontal bars to indicate differences between the two measurements. To learn how to perform snapshot comparison, please refer to my blog titled "You Can't Improve What You Can't Compare".

Conclusion
Take some advice from your friendly ex-Oracle instructor. LEARN HOW TO USE THESE TOOLS. I can't emphasis that statement strongly enough. As I stated, ADDM and the intelligent advisors may not currently be a total replacement for DBA experience and expertise, but sooner or later, they most definitely will be. Don't get left behind - because its only a matter of time.

Learning how to tune requires that you spend time with the optimizer experimenting with different environmental settings and database objects that affect access paths. It also requires that you have a general understanding of the basics of optimization.

I dedicated an entire blog on educational resources that will help you build a firm foundation of Oracle optimization knowledge. It's critical that we understand as much as we can about optimization before we begin our experimentation. But the key to success is to spend dedicated time experimenting. I'm not professing that you spend the rest of your career becoming "at one" with the optimizer. But if you don't want to be at a total loss each time something runs long, you need to experiment to learn how to tune.

Environmental Documentation and SQL Statement Selection
Before we begin, we'll need to document some of the key parameters that affect optimization and gather some information on the data objects we will be using in our experimentation. Please refer to my blog titled "Access Path Scientific Analysis Part I" for more information on the importance of documentation as well as the documentation process itself. In a previous blog, I provided a few hints and tips on identifying what types of statements and access paths would be most beneficial to analyze.

Access Path Identification Tools
Here are the tools that I most often use to review Oracle access paths:

• SQL Scratchpad - I like to see graphical representations of access paths. Even though I have lots of experience reading SQL Trace output and the multitude of variations of access path displays, I like seeing a graphical representation. The problem is that Scratchpad must be installed on your client (comes embedded with 9I OEM) and it doesn't like accessing 10G databases. If I am tuning a 9I database and I have access to SQL Scratchpad, that is the tool I am going to use to display access path information. For more information on SQL Scratchpad and other 9I OEM tools, please turn to my blog titled "Access Path Identification Part V".
  
  
• SQL*PLUS Autotrace - If I want to quickly display the statement's access path and associated run-time performance statistics, I will use SQL*PLUS Autotrace. Here's a blog containing a few hints and tips on Autotrace.
  
  
• Explain Plan - If I don't want to run the query, and I am not concerned about the access path changing during SQL statement execution (read next bullet), I will used the tried and true "explain plan into" clause to ask Oracle to dump the access path information into a table. I'll run a statement to retrieve the data from the plan table and format the output. Please read my blogs titled "Access Path Identification Part II" and "Access Path Identification Part III" for more information.
  
  
• SQL Trace - If I want to be absolutely sure that the access path the other tools are predicting the statement will take matches what access path is chosen during runtime, I'll run SQL Trace. It is one of the only tools you can use to determine the path chosen during execution. How can the access path change during execution? Read this blog to find out! The blog contains an example of a production problem that was caused by an access path changing during runtime. For lots of information on activating SQL Trace, please turn to my blog titled "Access Path Identification Part IV". SQL Trace is the tool I will turn to if I want to analyze a set of statements that belong to a specific online transaction or batch job. The output quickly tells me what the top resource consuming statements are.
  
  
• V$SQLPLAN - V$SQLPLAN contains access path information the SQL statement takes during execution. I'll run the statement, review the access path taken during execution, make my tuning change, alter the text of the statement look different to the optimizer, retrieve the access path information again and compare the before and after results. My blog titled "Access Path Identification Part II" provides all of the information you need when you are using the V$SQLPLAN table to retrieve information on access paths taken during execution.
  
  
• Regardless of which tool I select, I will use the output to graphically display the statement's access path. Although it is time consuming, graphically displaying a statement's access path allows me to more clearly understand the access path and simplifies the tuning process. This blog will show you how to graphically display a statement's access path.

Another Graphical Display I find Useful
During my career as an Oracle instructor, I was often asked in the SQL tuning classes how I personally documented a query I was attempting to tune. This is the display that I showed them. Let's take a look at the contents, starting from the top of the page:

• SQL statement text - the full text of the SQL statement.
  
  
• Columns in the SELECT clause - a listing of the columns that are in the SELECT clause are displayed under the table they belong to.
  
  
• Columns in the WHERE clause - a listing of the columns used in the WHERE clause are displayed under the table they belong to. If the columns are tables used in a join operation, an arrow is used to designate which of the other table's columns they are joined to. If I am interested in cardinality and data skew, I would document that information directly below the column name. For more information on determining column cardinality and data skew, please turn to this blog.
  
  
• Indexed columns - The bottom of the page contains all of the indexes that are on columns in the WHERE and SELECT clauses. This allows me to easily determine if I need to create additional indexes are begin analyzing why an index isn't being used.

Performing the Scientific Analysis
Before we begin, we need to understand the effect that preloading the buffers has on SQL statement performance. If you run the query and return the results, you will be loading the buffer cache with data. As a result, subsequent runs of the same statement could be faster. This is because your statement is retrieving data from memory while the initial runs were forced to retrieve the data from disk. You'll need to run the statement twice, make your change and run the statement twice again to ensure that buffers don't impact your timings. You can then compare the output of the second execution of each test. 10G provides a SQL statement to flush the buffer cache.

If you are building indexes during your testing, don't forget to generate statistics on the new objects. You don't want the lack of object information to affect the optimization process.

In the discussion that follows, I'll be providing you with some examples of changes to make during your scientific analysis. You will run the SQL statement, record the execution time and statistics and document the access path. You will then make the suggested alteration, review the access path to see if it has changed and run the statement again to record the new performance statistics and execution time. Please note that in the examples below, I'm not going to remind you each time to run the query to evaluate the performance statistics and execution times.

Index Testing
Let's use the same sample query I used in my graphical display in this discussion:

select a.employee_id, a.last_name, b.department_id, b.department_name,
c.street_address, c.postal_code, c.city, c.state_province
from hr.employees a, hr.departments b, hr.locations c
where a.department_id=b.department_id
and b.location_id=c.location_id
and a.employee_id = 174
order by a.last_name;

There is a myriad of different changes we can make. Here's a couple of examples to get you started:

• Drop all of the indexes on the tables and review the access path. We know it will use table scans, but what join method did it choose, how about the join order?
  
  
• Build an index on one of the join columns (i.e. dept.department_id) and review the access path. Determine if it chose the index, what join method was used and the order in which the tables were joined.
  
  
• Drop that index and build an index on the other column in the join. Identify if it used the index, join method and join order.
  
  
• Build an index on employee_id, department_id and last_name. Identify if it used the index, join method and join order. Did you get an index only access path?
  
  
• Drop the index you just created and change the order putting employee_id between department_id and last_name. What was the access path?
  
  
• Change the WHERE clause to look for rows based on the last_name and first_name columns. Remove the a.employee_id = 174 clause. Build an index on the first_name and last_name columns. What happened?
  
  
• Drop the index built on the first_name and last_name columns and build two indexes, one on first_name and the other on last_name. Did the optimizer use both indexes? Which one did it choose?
  
  
• Add local predicates to the other tables and identify the access path changes.
  
  
• Identify a column in one of the tables that has low cardinality. Change the WHERE clause to access that low cardinality column. If you don't have a low cardinality column available, its pretty simple to add a column and update it with a few repeating values to create a low cardinality column. Build a bitmap index on the low cardinality column. Did the optimizer choose the bitmap index? What happens if you use a hint?
  
  
• Build a bitmap index on a column that has high cardinality (i.e. employee_id). If it doesn't use the bitmap index, use a hint to influence the optimizer to choose it. Drop the bitmap index and build a B-tree index. Does the optimizer use that index without the hint? Compare before and after execution performance statistics and timing.
  
  
• Identify columns that have skewed data. Build an index on the skewed column. Change the query to search for a value that occurs many times. Compare that execution to a search on a value that appears few times. Analyze the table and build a histogram. Re-execute the tests and compare the results.

Parameters that Affect Optimization
We also need to learn how different parameter settings affect optimization. We can start by adjusting a few of the basic parameters that have the best chance of changing the access path for a given statement. Most of the parameters can be changed in your session by using the ALTER SESSION statement. Using the ALTER SESSION statement will allow you to change the parameter for your session only. You won't have to worry about affecting other folks executing statements in the same database that you are using for your testing.

Here's a blog that provides a listing of parameters we can use during our initial testing. Please note that this is not an all inclusive list. It is a listing of parameters that I feel have the greatest chance to influence the optimizer to choose a different access path.

You need to execute the statement, change a parameter and note its affects on the statement's access path, performance statistics and runtimes. If you are going to test changing values for different parameters, don't forget to change the one you just tested back to its original setting. That way you won't have multiple changes impacting your test results. You can also test combinations of changes. Just be aware of the changes you are making.

Hints
Administrators embed hints in a SQL statement to influence the optimizer to choose a particular access path. Using hints will allow us to evaluate the affect that different access paths have on SQL statement performance. We will run the statement without any modification, review the access path and performance statistics, use a hint to (hopefully) change the access path, run the statement again and compare the before and after results. Since hints can be embedded in virtually any SQL statement, they will provide us with an easy mechanism to learn more about access paths. I have devoted a previous blog to hints. The blog also contains a demo document that will show you how to embed hints and the affects the hints have on access paths. One of the key tests is to determine the impact that operation cardinality has on SQL statement performance. You can test this by using the ORDERED hint to ask the optimizer to change the join order for statements that join more than two tables together.

Conclusion
The tests above will hopefully stimulate your creativity and allow you to generate your own test cases. As we become more experienced we'll need to review subselects, views, complex selectivity issues (and the list goes on and on…) Oracle performance tuning is truly a wonderfully complex subject. The important thing is that we start the process. That's the only way we'll become database performance scientists!

Thanks for Reading,

Chris Foot
Oracle Ace

• Let's Get Technical! - Using Deductive Reasoning and Communication Skills to Identify and Solve Performance Problems
  The first blog of this series provides some helpful hints on what questions to ask during the initial stages of application performance problem analysis.
  
  
• System Triage Part II - Host Performance Analysis Using Grid Control and Host Commands
  Using operating system commands and 10G Grid Control to monitor database server performance.
  
  
• System Triage Part III - Finding the Top Resource Consumers
  We learned how to use 10G Grid Standalone to identify the top resource consumers for a given instance.
  
  
• System Triage IV - Access Path Identification Part I
  A high-level overview of Oracle query optimization. Also discussed the difference between estimated and runtime access paths.
  
  
• System Triage V - Access Path Identification Part II
  We reviewed the two data objects that contain access path raw data - plan_table and v$sql_plan. In addition, we discussed a few of the V$ dynamic performance views that provide information pertaining to SQL statements executing in our Oracle database environment.
  
  
• Access Path Identification - Part III
  A discussion on the tools we can use to display graphical and text versions of explain plan data including the DBMS_XPLAN package, UTLXPLS.SQL, UTLXPLP.SQL and Oracle 9I Scratchpad (graphical). We also learned how to create our own graphical displays of Oracle access paths.
  
  
• Access Path Identification - Part IV
  An investigation of everyone's favorite performance analysis tools - SQL*PLUS Autotrace and SQL Trace.
  
  
• Access Path Identification - Part V
  A discussion on a couple of 9I Oracle Enterprise Manager tools that we can use to identify Oracle access paths. We also learned how we can use 9I OEM's SQL Analyze and the Virtual Index Wizard to help us better understand access paths, monitor database performance and tune statements running in an Oracle 9I database environment.
  
  
• Access Paths VI - 10G Grid Control SQL Details Panels
  We turned our attention to the 10G Grid Control's access path display panel - the SQL Details Panel. It seems like no matter where you start your investigation in 10G Grid Control, sooner or later, you'll end up with a panel that contains a link to the SQL Details Panels. We took a look at a few of the more popular ways of finding our way to them.
  
  
• Access Paths VII - Access Path Education
  Focuses on the importance of Oracle access path education. The blog contains links to resources and numerous Metalink Notes that provide access path educational information.
  
  
• Access Path Scientific Analysis Part I
  An introduction to Oracle Access Path Scientific Analysis. We learn why it is important for us to become "database performance scientists" if we want to fully understand Oracle access paths and the affect they have on SQL statement performance. We also learned how to select a test system to use as well as document the parameters and database objects that play key roles in the optimization process.
  
  
• Access Path Scientific Analysis Part II
  We learn how to use hints to influence access paths for testing purposes. Using hints allows us to evaluate the affect that different access paths have on SQL statement performance. When we finally begin our access path scientific analysis, we will run the statement without any modification, review the access path and performance statistics, use a hint to change the access path, run the statement again and compare the before and after results. Contains a document that provides a demo on using hints to influence access paths. The demo document also contains a description of some of the basic access paths that Oracle can choose from.
  
  
• Access Path Scientific Analysis Part III
  An overview of the various types of indexes as well as indexing strategies that affect Oracle access path selection. Also includes a list of parameter that can influence the optimizer to favor index access.

Selecting SQL Statements to Analyze
We need to select a few SQL statements that we can use as input to our scientific analysis. Let's take a look at the various tools we can use to select the SQL statements we can use for testing.

• Using 9I Oracle Enterprise Manager - We can use the Session Details Panel to retrieve the SQL text of statements that are currently executing in the database. The Session Details Panel also provides the access path the statement is taking. The blog on 9I OEM will show you how to drill down to find the text of the SQL statement and the access path it is taking.
  
  
• 10G Grid Control - Grid Control' s Execution Plan Panel allows administrators to display the entire text of a SQL statement and the access path it is taking. This blog on 10G Grid Control will show you how to navigate through the various panels to access the Execution Plan Panel.
  
  
• SQL Trace - If we don't have access to the "SGTs" (Sissy GUI Tools), all is not lost. We can also use the SQL Trace utility to capture the text of the SQL statement as well as its access path. To use this method, we would contact one of our friendly application developers and ask them to run a series of batch jobs or online transactions in the environment we have selected as our test bed.
  
  We can then activate SQL Trace, contact the developer to run the selected workload, deactivate the trace and format the trace's output into an easily readable format. SQL Trace provides the benefit of allowing us to capture multiple SQL statement access paths in a single execution. The blog on Autotrace and SQL Trace provides instructions on how to activate the SQL Trace utility.

What Statements Should I Choose?
We are looking for statements that utilize the different Oracle access paths. Try and find statements that use:

• Table scan and index access paths:
  
  
  • Index only - Oracle is able to read all of the data required to satisfy the query's data needs from the index structure alone.
    
    
  • Index to table - Oracle uses a row identifier to probe the table to satisfy the data request.
    
    
  • Full table scan - Oracle reads all rows from the table. Oracle will scan the table to the last block used (as opposed to the last block that actually contains data).
    
    
• Join access paths:
  Used when the statement retrieves data based on matches between two tables (i.e. retrieve all of the employees that have the department name of "Welding"). The employee information is contained in the employee table and the department information (including the department name) is in the department table.
  
  
  • Nested loop join - Good path when the join is accessing a small subset of rows.
    
    
  • Hash join - Efficient access path for joins that access larger sets of data.
    
    
  • Sort merge join - Sorts rows to allow quicker access during the join.
    
    
  • Outer joins - An outer join returns all of the rows that satisfy the particular join condition and returns additional rows from one table that do not satisfy the join condition.

We don't have to find each and every access path I outlined above. In addition, each statement that does a join will also contain table scan and/or index access paths. Try to find statements that access a single table and statements that join two, three and four tables together. It should be relatively easy to find the nested loop and hash join access paths. Sort merge and outer joins may be a little harder to find. Try and stay away from SQL statements that use parallel processing, are 13 pages long or join 5 or more tables together. We want to start with the basics. We also don't want to use statements that have exorbitantly long execution times. We'll be executing them on a fairly regular basis during testing and waiting hours for the results will lengthen the testing process.

Next Up
We devise our scientific analysis testing process.

Almost Forgot
How are the Daylight Savings Time changes treating you? I may break in one last time to discuss their impact on our organization.

Thanks for Reading,

Chris Foot
Oracle Ace

The Problem
Before we begin, we need to understand exactly what the problem is. If you haven't read my previous blogs on this topic, I highly suggest you do so before continuing.

Metalink DST Information
In my first blog on DST, I provided you with numerous links to Metalink Notes that focused on Daylight Saving Time. In my second blog, I provided a link to Oracle's new Metalink support category on DST.

Updates
We have patched dozens of databases and operating systems since I wrote that first blog on DST. I've compiled a a few notes on what we have found:

Operating System Patches

Windows
We have patched all of our customers database servers that we are responsible for providing OS support for and have experienced no issues. But some of our customers have notified us that they have experienced problems when they patched their mail and application servers.

If one implementation experiences a problem, you have a tendency to chalk that one up to that specific environment. When multiple implementations experience problems, we decided to inform our entire customer base of the potential for issues. Especially when we know that several of the customers that reported problems have top-notch Windows administrators on staff.

All of the environments had test and production operating systems that were configured to mirror each other as closely as possible. The test patches worked as advertised but a few of their production environments failed. Either the patch wasn't applied successfully or the environment refused to work after the patch was applied. In one case, Microsoft support had the administrator apply the patch again using the exact same steps and it worked the second time. Go figure. Several of the other customers were asked by Microsoft support to make various changes to their O/S configuration and attempt the patch a second time.

A few of our customers experienced mail performance problems because the DST patch caused the mail engine to generate dozens (upon dozens) of meeting time updates. The DST patch forces the mail engine to change the meeting times for all meetings that were sheduled between March 11 (new DST date) and April 1 (old DST date).

I do apologize that I don't have specifics on the the other issues. I do know that each fix was specific to that environment. I know that this may seem not very helpful at this time but I think a general warning was warranted.

Recommendations
Just because all of your test patches worked, don't assume that the production environment will also be successful. When you patch the production environment, be prepared for issues that did not occur during the test patches. You can't blame Microsoft either; test and production environments that are configured "as closely to each other as possible" aren't mirror images of each other. Close doesn't count when it comes to operating system/hardware configurations. Visit Microsoft's website regularly to ensure that there is no additional information you need to be aware of.

UNIX, LINUX
Our O/S admins experienced a few minor issues with UNIX and Linux. Please remember - if the server has its own Java engine (and you run Java programs on the O/S), your best bet is to patch both the operating system and the Java engine. We did find that several O/S vendors were updating their DST patching instructions on a regular basis.

Recommendations
Don't forget to patch that Java engine too! Visit your O/S vendor's website regularly to ensure that there is no additional information you need to be aware of.

Oracle Database Patches
The biggest issue we had was with Oracle's updates to the DST patching documentation. Since we were required to do a LOT of patching, it was important for us to get a head start. In the middle of January, we download all of the DST documentation and studied the information. We then created our plan of attack and began implementing it.

We also checked the DST area on Metalink every few days for updates. Here's where the problem arose. Some of the database patching documentation (10G specifically) can be described as "somewhat fluid" in nature. Our DST coordinator, Brian "Captain Patch" Hays found that some of the changes forced him to assemble his DST team to review their impact.

Although the updated documentation did not force us to redo any patches, we did have to revisit several of the environments that we already patched to ensure that we didn't have to reapply them. In one case, the initial documentation did not provide a complete listing of DST datatypes that could be used in table definitions. We reviewed each of the environments looking for tables containing the additional DST datatypes and were pleased to find that none were in use. I can state that when we did apply the database patches (both for the database and the Oracle Java Engine), they worked flawlessly.

Recommendations
We intend to check for updates to all Oracle DST documentation until March 11. I would highly suggest that you also do so.


Oracle Application Server
Oracle's application server was the biggest challenge. Oracle recommended numerous patches for the infrastructure database to ensure DST compliance. Many of the patches required that the infrastructure database be at a specific release. Oracle also recommended that we apply DST patches to all of the target databases that could potentially be accessed by the application server. Once again, all of the patches worked.

Recommendations
If yo