November 2006

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

November 2006
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Viewing Execution Plans with Statspack

You’re invited to submit your Oracle-related questions to us at askdbspecialists11@dbspecialists.com. Please include your name and telephone number in your email.

This month’s question comes to us from John in Redwood City, CA: I believe that a query on my system has changed execution plans. I have Statspack collecting snapshots every hour. Is there a way to find out from Statspack if my query has changed execution plans?

Terry Sutton of the Database Specialists team responds: If you’re gathering Statspack snapshots at a level of 6 or higher, you can probably see if the execution plan for your query has changed by using a simple Statspack script provided by Oracle. A level 6 snapshot includes optimizer execution plan and plan usage data for each of the SQL statements captured. In order for Statspack to capture the plan for a SQL statement, the statement must be in the shared pool at the time the snapshot is taken, and the statement must also exceed one of the following thresholds:

Threshold Default Value
Number of executions of the SQL statement 100
Number of disk reads performed by the SQL statement 1,000
Number of parse calls performed by the SQL statement 1,000
Number of buffer gets performed by the SQL statement 10,000
Size of sharable memory used by the SQL statement 1 Mb
Version count for the SQL statement 20

You may need to change thresholds from their default settings in order for the SQL statement you desire to get captured, and you will need to raise the snapshot level if it is below 6 as well. These changes are made using the statspack.modify_statspack_parameter procedure call. For example, to change the snapshot level to 6, minimum buffer gets to 1,000 and minimum disk reads to 100, you could use the following procedure call:

SQL> BEGIN
  2    statspack.modify_statspack_parameter
  3    (
  4    i_snap_level=>6, i_buffer_gets_th=>1000, i_disk_reads_th=>100
  5    );
  6  END;
  7  /

If the query has been captured by the Statspack snapshots, you can get information on the execution plans it has used by running the sprepsql.sql script. This is one of the standard Statspack scripts, and is generally located in $ORACLE_HOME/rdbms/admin. The script will prompt you to determine which Statspack snapshots you want to consider, and it will prompt you for the hash value of your query. If you don’t know the hash value, you can get it from the database. For a demonstration we’ll use the following query:

select count(*) from testtab where charcol = '643';

For our example, the TESTTAB table is about 5 Mb in size. Initially it has no indexes. We will run the query a few times and then take a Statspack snapshot. Then we will create an index on the CHARCOL column of the TESTTAB table, run the query a few more times, and take another Statspack snapshot. Let’s see what the sprepsql.sql script tells us.

First we find the hash value for our query:

SQL> SELECT hash_value, sql_text
  2  FROM   v$sql
  3  WHERE  UPPER (sql_text) LIKE 'SELECT COUNT(*) FROM TESTTAB WHERE CHARCOL =%';

HASH_VALUE
----------
SQL_TEXT
------------------------------------------------------------
2931314013
select count(*) from testtab where charcol = '643'

Then we run sprepsql.sql, and it tells us the following:

 

SQL Text
~~~~~~~~
select count(*) from testtab where charcol = '643'

All Optimizer Plan(s) for this Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this Hash value, and the Snap Id's they
were first found in the shared pool
-> ordered by Snap Id

        Plan
  Hash Value  Snap Id       Cost Optimizer
------------ -------- ---------- --------------------
  2656308840        1        67  CHOOSE
  1159282624        4         3  CHOOSE

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified.  The values for Rows, Bytes and Cost shown below are
those which existed at the time the first-ever snapshot captured this plan
– these values often change over time, and so may not be indicative of
current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 1159282624 ----|       |      |      3 |
|SORT AGGREGATE                  |                     |     1 |   24 |        |
| INDEX RANGE SCAN               |TEST_IDX             |     4 |   96 |      3 |
|SELECT STATEMENT                |----- 2656308840 ----|       |      |     67 |
|SORT AGGREGATE                  |                     |     1 |   25 |        |
| TABLE ACCESS FULL              |TESTTAB              |     4 |  100 |     67 |

We see that there are two execution plans and thus two plan hash values (PHVs) associated with our query. One plan, PHV 2656308840, was first seen in Statspack snapshot 1 (before we created the index), and the other plan, PHV 1159282624, was first seen in snapshot 4 (after we created the index). The plan with PHV 2656308840 shows the query performing a full table scan of TESTTAB, with a cost of 67. The other plan shows the query using the new index, TEST_IDX, with a cost of 3. This is exactly what we would expect.

As you can see, the sprepsql.sql script can be very useful for seeing if a query has changed execution plans, and for determining the execution plans that were being used at a specific point in time. This script is part of the standard Statspack facility and is present on nearly every Oracle system.

Oracle on Linux: Full Steam Ahead

By now, you may have heard about Oracle’s announcement of “Unbreakable on Linux” at Oracle OpenWorld. If you missed it, here’s the latest news from Oracle Corp: http://www.oracle.com/technologies/linux/index.html, which explains that Oracle Corp. will now offer Linux operating system support.

Oracle on Linux continues to grow in popularity, and we continue to see many of our customers adopting the Linux platform. To stay on top of general happenings in the Linux world, check out the Linux Journal, a monthly magazine for the Linux community. Magazine archives going back to 1994 can be found at http://www.linuxjournal.com/xstatic/magazine/archives.

If you’re just getting started with Oracle, you can take advantage of our detailed guide: Installing Oracle on Linux. It’s full of detailed, step-by-step instructions for installing Oracle on Linux. After you’re up and running, take a look at some of our other resources on backup scripts, performance issues, and much more at http://dbspecialists.wpengine.com/presentations.html.

Check Out the List

We’re talking about the Oracle-L list, moderated by Steve Adams. This has been a long-time favorite resource for research and information exchange among Oracle users. You can sign up to participate, receive daily digests, or use the site to search archives on an issue you are currently investigating.

Introduction to MySQL

It’s becoming more common for companies to have a combination of database software within their IT department to handle various tasks. The open source database, MySQL, has been growing in popularity, and we’re seeing it alongside Oracle databases in IT departments. To get a quick overview of MySQL (and its history, background, and uses) check out the following listing at Wikipedia: http://en.wikipedia.org/wiki/MySQL

If you’ve already gotten started and want to learn more about MySQL, check out the many resources available at http://dev.mysql.com/doc/. This web page includes items such as:

  • MySQL Reference Manuals
  • MySQL GUI Tools
  • Expert Guides
  • MySQL Help Tables

In addition, there is a large listing of articles at http://dev.mysql.com/tech-resources/articles/index.html. The list includes topics such as:

  • MySQL 5.1 New Features: MySQL Partitions
  • Enforcing Foreign Keys Programmatically in MySQL
  • MySQL Cluster 5.0 ALTER TABLE In-Depth

 

 

Check out October’s archives to get a flavor of the variety of topics raised: http://www.freelists.org/archives/oracle-l/10-2006/. Some of the topics that month include:

  • Oracle vs. SQL Server
  • Gathering Statistics for a Specific Schema
  • Linux Memory Accounting

Leave a Reply

Your email address will not be published. Required fields are marked *