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