Tracking Data Access Patterns in Oracle

Performance in Oracle is directly related to the SQL workload
that is submitted. Often it is not the change in the SQL that degrades
performance but instead is the access path to that data. This article will help
you understand when new access paths have been introduced in your environment.

There are only two types of SQL statements. Those that are
planed for or are canned within an application and those SQL statements that
are ad hoc in nature. Whatever the case, you as a database professional should
always be on the lookout for SQL that has been submitted to the Oracle engine
and that you have never seen before or that is taking a different access path
to the data than what is normal or have been experienced in the past. It is
this deviation from the normal access path that can cause havoc and degrade
performance. This article will show you how you can detect when SQL statements
are taking different access paths then previously experienced.

The major piece of information in determining when data
access paths have changed is the PLAN_HASH_VALUE value from the V$SQL view.
Looking at the V$SQL view is very simple. Listing A
gives a simple SQL statement that interrogates this view for SQL statements
that have been issued against the EMP_TABLE. The result set of the SQL shows that
there are two statements that have been issued against the employee table.
Notice that while the ADDRESS and HASH_VALUE numbers are different, the
PLAN_HASH_VALUE is the same. This means that for the two different SQL
statements, the access path to the data is the same.

LISTING A:

Two SQL statements with the same PLAN_HASH_VALUE value


SQL> select address,hash_value,child_number,plan_hash_value,sql_text
2 from v$sql where sql_text like ‘%emp_table%’

ADDRESS HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
——– ———- ———— ————— ———————————————-
6CB6CF6C 2624221581 0 825022252 select emp_id,employee_name from emp_table where
where emp_id = 3904302231
6CBCF928 1545959777 0 825022252 select emp_id,employee_name from emp_table

We can then query the V$SQL_PLAN view and can see the access
path that the SQL statements will take to get to the data. Listing B clearly shows that both SQL statements from Listing A will take a full table scan of the employee
table. When querying the V$SQL_PLAN table you must supply the address and hash_value
from the V$SQL view.

LISTING B:

Same explain plan for two different SQL statements


SQL> SELECT operation, options, object_name, cost
2 FROM v$sql_plan WHERE address = ‘6CB6CF6C’ AND hash_value = 2624221581;
OPERATION OPTIONS OBJECT_NAME COST
———————— ———————— —————– ———-
SELECT STATEMENT 26
TABLE ACCESS FULL EMP_TABLE 26

SQL> SELECT operation, options, object_name, cost
2 FROM v$sql_plan WHERE address = ‘6CBCF928’ AND hash_value = 1545959777;
OPERATION OPTIONS OBJECT_NAME COST
———————— ———————— —————— ——
SELECT STATEMENT 26
TABLE ACCESS FULL EMP_TABLE 26


To show how the PLAN_HASH_VALUE changes for different access
paths, we can add an index to the employee table and re-issue the same two SQL
statements. Listing C shows the same two SQL
statements queried from the V$SQL view. Notice that the PLAN_HASH_VALUE values
are now different. This indicates that a different access path has been chosen
for the two SQL statements. Listing D shows the
explain plan for the two SQL statements and that the two different SQL
statements have truly taken two different access paths to the data.

LISTING C:

Two SQL statements with different PLAN_HASH_VALUE values


SQL> select address,hash_value,child_number,plan_hash_value,sql_text
2 from v$sql
3 where sql_text like ‘%emp_table%’

ADDRESS HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
——– ———- ———— ————— ———————————————–
6C9CFA7C 1308229677 0 2041761175 select emp_id,employee_name from emp_table
where emp_id = 3904302231
6CBCF928 1545959777 0 825022252 select emp_id,employee_name from emp_table

LISTING D:

Two different explain plans for the two SQL statements


SQL> SELECT operation, options, object_name, cost
2 FROM v$sql_plan WHERE address = ‘6C9CFA7C’ AND hash_value = 1308229677;

OPERATION OPTIONS OBJECT_NAME COST
———————— ———————— —————— ——
SELECT STATEMENT 2
INDEX RANGE SCAN EMP_TABLE_IX01 2

SQL> SELECT operation, options, object_name, cost
2 FROM v$sql_plan WHERE address = ‘6CBCF928’ AND hash_value = 1545959777;

OPERATION OPTIONS OBJECT_NAME COST
———————— ———————— —————— ——
SELECT STATEMENT 26
TABLE ACCESS FULL EMP_TABLE 26

By using the PLAN_HASH_VALUE in the V$SQL view you can track
when new access paths to data have been used. This is very useful since typically,
applications are tuned for specific access paths and any deviation from these
access paths will degrade performance and should be remedied. The best way to
use this information is to determine when applications are running, take a
snapshot of the V$SQL and V$SQL_PLAN views over a specific time slice for the
duration that the applications are running and use that data as a baseline of
access paths. Then any subsequent runs of the same applications could also take
snapshots of the V$SQL and V$SQL_PLAN view. The subsequent information should
then be compared to the baseline information and we can determine if we have
had a change in access path patterns. This is a great time saver because before
Oracle9i we would have had to compare line by line of the explain plan output
to determine when access path patterns had changed.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles