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