Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Blogs » Chris Foot Blog » Chris Foot's Oracle10g Blog » System Triage V - Access Path Identification Part II
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3620
 

System Triage V - Access Path Identification Part II System Triage V - Access Path Identification Part II

We continue our series on system triage by learning how to find the access paths our poorly performing queries are taking during execution. In this blog, we’ll review the two data objects that contain the access path raw data - plan_table and v$sql_plan. In addition will review a few of the V$ dynamic performance views that provide information pertaining to SQL statements executing in our Oracle database environment.
Explain Plan
The explain plan clause is used to ask Oracle to insert data describing a SQL statement's predicted access path into a database table. When the statement containing the explain plan clause is executed, Oracle will not run the statement; it sends the statement to the optimizer to generate the access path and then updates the plan table accordingly. The user is then able to execute standard SQL statements to retrieve the data from the table and review the access paths that the optimizer is predicting the SQL statement will take.

Oracle's Plan Table
Oracle provides a table to contain the output from the explain plan execution. The script utlxplan.sql that contains the DDL to create the plan table can be found in the $ORACLE_HOME/rdbms/admin directory in most installations. Utlxplan.sql creates a table called plan_table, which is owned by the account that executed utlxplan.sql.

The EXPLAIN PLAN statement will inserts rows into the plan_table that describes a given statement's execution plans. Oracle's standard recommendation is to drop and recreate all plan tables after an Oracle upgrade to ensure that you use the latest and greatest version of the object. It is a good practice to follow Oracle's advice just in case the definition of the plan_table changes from one release to another.

Manually Running the Explain
Although the intent of this series of blogs is to review the tools that Oracle provides to automatically display access path output, it is important to begin with the basics. The only tool you need to manually run an explain plan is SQL*PLUS.

Take a look at the examples below. I'm adding the explain plan clause to the beginning of the statement to notify Oracle to update the plan_table with rows describing the statement's access path:

  • EXPLAIN PLAN FOR select * from scott.emp;

    This statement will explain the statement "select * from scott.emp" and place the results in the plan_table owned by the account executing the statement.

  • EXPLAIN PLAN INTO schema.plan_table FOR select * from scott.emp;

    This statement will explain the statement "select * from scott.emp" and place the results in the plan_table owned by account referenced in schema.plan_table. The account executing the statement must have the proper privileges on the plan_table to execute this statement successfully.

  • EXPLAIN PLAN INTO schema.plan_table SET STATEMENT_ID = 'test1' FOR select * from scott.emp;

    This statement will explain the statement "select * from scott.emp" and place the results in the plan_table owned by account referenced in schema.plan_table. The statement ID allows users to retrieve the statement's access path information from a plan_table that contains rows from many different explains.

It's important to note that the contents of the plan_table are a prediction of the access path that the statment will take. V$SQL_PLAN, discussed below, contains information on the access paths the statements used during execution. For more information, please refer to my previous blog titled "System Triage IV - Access Path Identification Part I"

When you explain in statements in SQL*PLUS, you also need to be aware of how the tool interprets the bind variables. Tom Kyte has a discussion on bind variable interpretation on his Ask Tom website.

You need to be careful when you are copying and pasting SQL statements that you are attempting to explain. I have seen experienced DBAs forget to copy the keywords "explain plan for" when they copy the statement, dump the statement into SQL*PLUS and run the statement instead of explaining it. It's an easy mistake to make when you are rushing to solve a performance problem. If it's an update, use the ROLLBACK statement to remove the unwanted data.

V$SQL, V$SQLAREA, V$SQLTEXT and V$SQLTEXT_WITH_NEWLINES
Before we begin our discussion on v$sql_plan, let's take a high level look at some of the views we will use to gather information on SQL statements contained in the library cache. We'll need this information before we access v$sql_plan to determine what access path our queries are taking.

  • V$SQL - contains information on statements in the library cache. It will contain a row per SQL statement, which allows you to review information for an individual query. This becomes beneficial when you have tables that have the same names but have different owners. If two users are both executing "SELECT last_name, first_name, emp_id FROM employees" and both users own a table called employees, you'll find both statements in v$sql.

    V$SQL's sql_text column provides the first 1,000 characters of the text of the SQL statement and an address column that we can use to as an identifier when we query the other V$ views. V$SQL's sql_fulltext is a CLOB column that contains the entire SQL statement.

    V$SQL also provides an abundant amount of information on resource utilization including sorts, memory utilization, disk reads, buffer gets, rows processed.

  • V$SQLAREA - Is an aggregate of the aforementioned v$sql view and contains much of the same information as in v$sql. Because it is an aggregate you'll have to remember that if two users are both executing "SELECT last_name, first_name, emp_id FROM employees" and both users own a table called employees, you'll find only one statement in v$sqlarea.

  • V$SQLTEXT - Although both v$sql and v$sqlarea do provide CLOB columns that provide this information, this view provides an easy way to retrieve the entire text of the SQL statement. If the text of the SQL statement looks like it has been truncated in v$sql/v$sqlarea's sql_text columns, use the value in the address column from v$sql/v$sqlarea to query v$sqltext to get the entire statement. If the text of the SQL statement contains any newlines or control characters, they will be replaced with whitespace.

  • V$SQLTEXT_WITH_NEWLINES - Contains the text of the SQL statement without the newlines or control characters being replaced by whitespace.

It is important to note that information for specific SQL statements contained in the V$ performance views provided above, as well as v$sql_plan, could be flushed from the system based on workload and memory allocations. Although you will always find data for currently executing queries, you may, or may not, find information pertaining to statements that were executed some time in the past. If a SQL statement has been aged out of the library cache, you won't be able to find information on it.

Going in-depth into each of these dynamic performance views is far beyond the scope of this particular blog. Consult the Oracle Reference Guide for your specific release for more information on this VERY helpful set of views.

V$SQL_PLAN
Oracle provides the v$sql_plan view to hold information on the access paths SQL statements used during execution. If you want to quickly find out exactly what access path was taken during runtime, this is the view to go to for that information.

If you do a describe on a plan_table and compare it to v$sql_plan, you'll notice that although most of the column definitions are the same, v$sql_plan, has additional columns to help you match the access path information to the SQL statement that generated it.

Let's take a look at the statements below:

The first step is to execute the statement we'll be using in the example. That way we'll be able to query a few of the dynamic performance views to retrieve some identification and performance information.

SQL>
SQL> r
1 select a.employee_id, a.last_name, b.department_id, b.department_name,c.street_address, c.postal_code, c.city, c.state_province
2 from hr.employees a,
3 hr.departments b,
4 hr.locations c
5 where a.department_id=b.department_id
6 and b.location_id=c.location_id
7 and a.employee_id = 174
8* order by a.last_name

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME STREET_ADDRESS POSTAL_CODE CITY
----------- ------------------------- ------------- ------------------------------ ---------------------------------------- ------------ ------------------------------
STATE_PROVINCE
-------------------------
174 Abel 80 Sales Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford
Oxford

Let's see if we can find the statement in the library cache by accessing the v$sqlarea. Since I know there is just one query in the library cache, I'm not interested in finding each specific version of it.

In this case, I'm interested in the address value to identify the statement and the number of buffer gets to get a feel for the statement's workload. I'll query the v$sqlarea view to retrieve the address for the statement, the number of executions and the buffer gets it consumed during those executions.

SQL>
SQL> select address, executions, buffer_gets, sql_text from v$sqlarea
2 where sql_text like '%select a.employee_id, a.last_name%' order by buffer_gets desc;

ADDRESS EXECUTIONS BUFFER_GETS
-------- ---------- ---------SQL_TEXT --------------------------------------------------------------------------------------------------------------------------------------------------------------------
697F521C 1 349
select a.employee_id, a.last_name, b.department_id, b.department_name,c.street_address, c.postal_code, c.city, c.state_province from hr.employees a, hr.departments b, hr.locations c where a.department_id=b.department_id and b.location_id=c.location_id and a.employee_id = 174 order by a.last_name

A couple of quick points to remember. Here is the WHERE clause for the above statement:

sql_text like '%select a.employee_id, a.last_name%'

We know that v$sql and v$sqlarea only contain the first 1,000 characters of the text of the SQL statement. If your search value falls after those 1,000 characters, you'll have to find the address from v$sqltext and use that value to search v$sql, v$sqlarea and v$sql_plan. In addition, the way the statements are stored can also have an affect on whether you will be able to successfully find the statement you are looking for.

If there is a break between "employee_id" and "a.last_name", you may not be able to find it using this search. That's why I attempt to search for the first few characters for a given SQL statement. The important point is to find the smallest chunk of the statement that allows you to differentiate it from other statements and use that value in your searches. There is a whole host of different options when searching the SQL_TEXT column. Tom Kyte's blog contains a lot of good suggestions.

Although the text of this SQL statement can be found entirely in the v$sqlarea performance view, as I stated, there are times when the statement will be too large to be entirely contained in the sql_text column. If the text of the SQL statement looks like it has been truncated, use the statement's address to query v$sqltext to retrieve the entire text of the statement.

SQL> r
1* select sql_text from v$sqltext where address = '697F521C' order by piece

SQL>
-------------------------------------------------------------
select a.employee_id, a.last_name, b.department_id, b.department_name
,c.street_address, c.postal_code, c.city, c.state_province from hr.employees a, hr.departments b, hr.locations c where.department_id=b.department_id
and b.location_id=c.location_id and a.employee_id = 174 order by a.last_name

OK, let's use the same value for the address column to query the v$sql_plan view to take a look at the access path it used during execution:

SQL> r
1 select operation, options, object_name, id, parent_id, position
2* from v$sql_plan where address = '697F521C'

Here's the output for the statement above.

You'll notice that the SQL statement I personally use to retrieve the statement's access path looks different to most others you will find. This is how it was done in "the old days" and I still feel comfortable using it from time-to-time. If I'm working on simple queries (no partitioned tables accessed, etc.), I will use this one because it allows me to quickly review the access path for the query.

It is important to note that this statement does not provide cardinality information. Cardinality is the number of rows that each, individual operation will access. Like the access paths themselves, there is a predicted cardinality and the actual cardinality. Bad things can happen if the predicted and actual differ. We'll discuss the importance of cardinality in a later blog. Right now, we are just concerned about displaying basic access path information. We'll take a look at some of the queries that Oracle provides to format access path output in my next blog.

The output of the query tells me the operation being performed, option used, object being accessed, ID and PARENT_ID. I'm a big fan of "bubbling out" queries. I'll use this query, grab a piece of scratch paper and draw a graphical representation of the query's access path. I'll discuss this in-depth in my next blog. The intent of this blog is to just provide you with a high level understanding of the two main objects we use to retrieve access path information.

Let's take a look at the next statement below:

SQL> r
1 select A.employee_id, a.last_name, b.department_id, b.department_name,c.street_address, c.posta
2 from hr.employees a,
3 hr.departments b,
4 hr.locations c
5 where a.department_id=b.department_id
6 and b.location_id=c.location_id
7 and a.employee_id = 174
8* order by a.last_name

I have changed the lower case character "a" to an upper case "A" in "A.employee_id". That allows me to make the statement look different to Oracle. It makes it easier for me to find that exact statement in v$sqlarea. I'll do this if I decide to make some kind of structural change (building an index on a table) or am testing changes to session parameters (optimizer_index_caching, optimizer_index_cost_adj) that influence the optimizer.

That allows me to do before and after comparisons. I'll run the statement document the buffer gets, disk reads, access path and timing, perform the tuning alterations and then execute the statement making a slight change to the text of the SQL. I will then find the new address for the statement and use it to access v$sql, v$sqlarea and v$sql_plan to retrieve the performance and access path information.

If I see a decrease in buffer gets, I'll know that I'm probably headed in the right direction. It is important to not forget about preloading the buffer cache. When a query executes, it will populate the buffers with data. As a result, subsequent executions will be faster. I'll often execute both the before and after versions of the query multiple times and use the aggregate performance data to perform my analysis.

You will also have to replace any bind variables with hard coded values to be able to successfully execute the statement. If you use the same values for the before and after, it will allow you to accurately compare the performance of the statement before and after your changes.

I'll use the same query I used previously to identify the ADDRESS for this statement. Please note that I have changed the lower case character "a" to an upper case "A" in "A.employee_id". This will allow me to retrieve information on the statement executed after I made my tuning changes.

SQL> r
1 select address, executions, buffer_gets, sql_text from v$sqlarea
2* where sql_text like '%select A.employee_id, a.last_name%' order by buffer_gets desc

ADDRESS EXECUTIONS BUFFER_GETS
-------- --------- -----------
SQL_TEXT
----------------------------------------------------------------------------------------------------
697AD768 1 8
select A.employee_id, a.last_name, b.department_id, b.department_name,c.street_address, c.postal_cod
c where a.department_id=b.department_id and b.location_id=c.location_id and a.employee_id = 174 order by a.last_name

I can then use the new address value for this statement to access the v$sql_plan view to determine if the access path has changed. You can add spaces between words, change case, place a newline to make the statements look different to Oracle.

Running Statements Using Bind Variables
If you prefer to try and mimic the SQL that is being executed as closely as possible, here's an example (courtesy of ace DBA Ron Berner) of how to use inline input to SQL bind variables that will be executed in SQL*PLUS. Please note that executing a SQL statement in SQL*PLUS requires that you define both input and output variables.

Finding the Top Resource Consuming Queries
If I want to perform a traditional "top down" tuning approach and tune the highest resource consuming SQL, I'll use the statements below to identify the top resource consuming queries.

The following query identifies the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, address, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads;

The following query identifies the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, address, sql_text FROM v$sqlarea WHERE buffer_gets > 10000 ORDER BY buffer_gets;

You can create a more readable report in SQLPLUS by inserting report breaks between the output lines. To generate the report breaks in SQLPLUS, issue the following statement before running the query:

BREAK ON disk_reads SKIP 2 --- for the disk read report and
BREAK ON buffer_gets SKIP 2 --- for the buffer get report

It's common knowledge that poorly performing SQL is responsible for the majority of database performance problems. The first query returns SQL statements responsible for generating disk reads greater than 5,000 while the second query returns SQL statements responsible for generating buffer reads greater than 10,000. I used these numbers just as an example but I do personally use them as a starting point from time-to-time. Based on their output, I'll then adjust the numbers up or down accordingly. The numbers also depend on the system I'm reviewing. I'll use different numbers for OLTP environments than I would for data warehouses.

You'll notice that I divide the number of disk and buffer reads by the number of statement executions. If a statement is generating 1,000,000 disk reads but is executed 500,000 times, it probably doesn't need tuning. But the more it runs, the more emphasis I will place on tuning it. If you can reduce the buffer gets generated by a query that runs thousands of times of day by even small amount, you are still reducing the total workload the system has to process.

Heavy disk reads per statement execution usually means a lack of proper indexing, poor selection criteria, etc.. Heavy buffer reads sometimes means the exact opposite - indexes are being used when they shouldn't be. But I'm personally most interested in workload, that's why I most often use the buffer cache hits in my initial queries.

I don't (can't) use the above queries to tune individual statement executions for a given user. In that case, I would be using a SQL trace and TKPROF to review individual statement execution. I'll use the queries above to get a general understanding of what statements are the top resource consumers.

I hope you enjoyed this blog. In my next blog, we'll take a look at the various queries we can use to format the raw access path data contained in the plan_table and v$sql_plan.


Thanks for Reading,

Chris Foot
Oracle Ace


Monday, December 04, 2006  |  Permalink |  Comments (2)
trackback URL:   http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2006-12-01.4894880310/sbtrackback

Hi

Posted by samx18 at 2006-12-09 09:30 AM
Hi ,

I have been reading your blog for sometime now, i like it much particularly the way you have orgarnized it and the wealth of information it provides.Thanks and Keep it up.

Sam
http://www.appsdbablog.com

sys.plan_table$ in 10g

Posted by WilliamRobertson at 2006-12-15 10:32 AM
In 10g there is a public synonym PLAN_TABLE pointing to SYS.PLAN_TABLE$, which is a global temporary table. Now I look, I see Jonathan lewis has a note about it here: http://www.jlcomp.demon.co.uk/plan_table_hack.html.
 

Powered by Plone