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 26SQL> 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 2SQL> 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.
