Tracking Data Access Patterns in Oracle

August 8, 2003





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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers