12.5 Reporting on SQL Execution Plans

If you've been a DBA for more than 15 nanoseconds, you've no doubt received an urgent phone call that goes something like this:

"I have a critical SQL statement that's running very slowly! You need to fix the database!"

The next sentence is almost always: "It worked fine, last week!"

As you try desperately to determine why this critical piece of SQL is suddenly running slower than a three-toed sloth taking a nap, you may think to yourself:

"It would be nice if I could see the execution plan for this SQL from when it was working properly."

We've had that exact same thought any number of times ourselves. And so the SXP portion of the PDBA repository was born.

12.5.1 SXP (SQL EXecution Plan) Scripts and Tables

The scripts and tables that make up SXP come in threes — we've designed a triumvirate of tables described earlier in this chapter in Table 12-2 (and shown graphically in Figure 12-1) used to store SQL and its corresponding execution plans, and we've designed a triumvirate of Perl scripts that populate these tables and report on the results. The scripts are:

sxp.pl

Collects SQL statements from the V_$SQLTEXT data dictionary view and stores them unformatted within the PDBA_SXP_SQL table. The script logs in as the user who originally parsed the SQL, and generates an execution plan for the statement with the EXPLAIN PLAN SQL statement. The resulting execution plan is then stored in the PDBA_SXP_EXP repository table.

sxprpt.pl

Generates reports on the stored SQL and execution plans.

sxpcmp.pl

Examines the current SQL statements, as contained in V_$SQLTEXT, generating execution plans for each statement. When a matching SQL statement is found in the repository, the execution plans are compared. If the plans differ, the SQL statement and its varying execution plans are included in the report.

Figure 12-1. The SPX table system

12.5.2 SXP Limitations

Helpful as the SXP scripts are, they do have a few limitations:

Type of SQL

Only SELECT, INSERT, DELETE, and UPDATE statements are retrieved from the SQL cache. PL/SQL anonymous blocks, packages, procedures, and functions are ignored.

Formatting of SQL

The only interface to the cached SQL statements is through either the V_$SQLTEXT or V_$SQLTEXT_WITH_NEWLINES system views. The SQL in these Oracle views is broken into 64-character chunks, often with breaks appearing right in the middle of words. We've chosen to store the SQL as a single line of text, as that's the way it appears after joining the various 64-character chunks together.

As it appears in these views, the SQL is often non-executable, because of comments included in them. Including an embedded comment in a single line of SQL often renders the rest of the statement as a comment too.

To generate an execution plan for the SQL, it must first be preformatted. We do this via the PDBA->formatSql method. The goal of this method is to format the SQL and get it into an executable form that's suitable for use with SQL's EXPLAIN PLAN statement. Most of the time it succeeds, but sometimes it fails, for reasons we'll explain shortly. When that happens, the error is reported and skipped over by the SXP scripts.

Limit on users

As with many EXPLAIN PLAN tools, the SQL generated by the SYS user is ignored.

Passwords

To generate an execution plan for a SQL statement, it's necessary to log in as the same user who parsed the SQL. In addition, the password for that user must be set up in the password server. If the password is unavailable, the SXP scripts report an error and continue on to the next user. If the wrong password is supplied, the scripts terminate.

An alternative approach you might already be familiar with is to store the encrypted form of the user's password, as found in DBA_USERS, then temporarily change the user's password and log in to the account to run EXPLAIN PLAN. The stored and encrypted form of the original password would then be used to restore it back to its previous value.

Even though this method will work,^[4] using it is probably a security violation in many organizations. It's also inconvenient for the actual user: if the user tries to log on to his own account, he may find himself locked out because of a temporarily changed password. If at all possible, we'd rather avoid the extra work involved in dealing with the complaints that we're likely to hear if this kind of thing occurs!

^[4] We sometimes use this method to log in to a user's account for administrative reasons. This is usually in an emergency, however, and is therefore outside the scope of our automated tool.

12.5.3 Collecting SQL with sxp.pl

With those caveats out of the way, let's go about the business of actually using this utility. You'll use the sxp.pl script to collect SQL from the database and store it in the repository.

We suggest that you restrict your initial excursions into the Oracle SQL cache, using only test or development databases until you're familiar with the process. Querying the V_$SYSTEM view can be a resource-intensive task, and doing so multiple times, while you learn to use these tools on a production database, may lead to unfriendly relations with regular users.

On our test database, this script runs in less than a minute. We also employ this tool on several production databases where it can take several minutes^[5] to complete.

^[5] One of these databases had a very large SQL cache and resulted in 122,829 new entries in the PDBA_SXP_SQL table, requiring 98 MB of storage and taking more than 20 minutes to complete. We've hit the old quantum mechanics limit again. It's impossible to measure an event without causing an effect within the area under test. Who shall guard the guards? (We avoid running sxp.pl on that database too often!)

You can enter the following command to collect SQL from database ts01 on server sherlock. (Coincidentally, this is the same database on which the repository resides.) Table 12-7 lists the command-line options for this script.

$ sxp.pl -machine sherlock -database ts01 -username system \
     -rep_machine sherlock -rep_database ts01 \
     -rep_username pdbarep

Table 12-7. Command-line options — sxp.pl

Option	Description
-machine	Server where the target database resides
-database	Target database
-username	DBA account
-password	Password for the DBA account (optional )
-rep_machine	Server where the repository database resides
-rep_database	Database the PDBA repository is in
-rep_username	Repository schema owner
-rep_password	Repository owner password (optional)

12.5.3.1 Unique constraint error

Example 12-13 contains the actual output from sxp.pl, as it was run on one of our test databases. You'll notice that there is an Oracle error:

ORA-00001: unique constraint (PDBAREP.PDBA_SXP_UK_IDX) violated

This error occurred about halfway through processing. This happens occasionally when there are syntactically identical SQL statements in the database cache that have been formatted somewhat differently. When sxp.pl encounters these paired statements, they're reformatted identically for our EXPLAIN PLAN statement. That results in sxp.pl trying to save the same SQL statement twice during the same session. This was a design decision on our part. Rather than search the PDBA_SXP_SQL table looking for duplicates each time we save SQL to the repository, we simply let the database catch it with a unique constraint. The sxp.pl script traps this error, reports it, and continues onto the next SQL statement.

Example 12-13. Output from sxp.pl

%oramon> sxp.pl -machine sherlock -database ts01 -username system \
-rep_machine sherlock -rep_database ts01 -rep_username pdbarep
.......DBD::Oracle::st execute failed: ORA-00001: unique constraint
 (PDBAREP.PDBA_SXP_SQL_UK_IDX) violated (DBD ERROR: OCIStmtExecute) at
 /usr/local/bin/sxp.pl line 283
...no password available from PWD for ORADES
....%oramon>

12.5.3.2 Password and privilege messages

You'll note another message in the sxp.pl output, but this one is simply informational:

no password available from PWD for ORADES

This means that there are SQL statements within the cache for the user ORADES, but that their password was not available from the password server, and thus processing moved onto the next user.

Another error that may appear is:

ORA-01039: insufficient privileges on underlying objects of the view

We encounter this error when attempts are made to generate execution plans for a SELECT statement on a view. Although the user may have SELECT privileges on the view, Oracle requires that we also have SELECT privileges on the view's underlying tables in order to generate an execution plan. There is nothing we can do about that, so we report the error and move onto the next SQL statement.

12.5.4 Reporting Execution Plans

The sxprpt.pl script reports on the SQL and execution plans now stored within the PDBA repository. Table 12-8 lists the command-line options for this script.

Table 12-8. Command-line options — sxprpt.pl

Option	Description
-machine	Server where the target database resides
-database	Target database
-username	DBA account
-password	Password for the DBA account (optional )
-verbose	Prints parameters and the SQL used to query the repository
-rpt_machine	Server where the repository database resides
-rpt_start_date	Optional date on which to begin reporting
-rpt_end_date	Optional date on which to end reporting

Now that we have some data loaded into the repository, let's get a report of what's in there. Because our test database already contains a number of data collections, we'll limit the report by specifying a date range constraint in the following command:

$ sxprpt.pl -machine sherlock -database ts01 \
    -username pdbarep -rpt_database 'ts01%' \
    -rpt_start_date '11/25/2001' -rpt_end_date '11/27/2001'

The resulting report will contain the following:

• The username of the account that parsed the SQL originally

• A checksum for the SQL text

• A reformatted version of the SQL

• The execution plan for the SQL

If all goes well, there will be:

• A copy of the execution plan

• The checksum for the execution plan

In the event that an error occurs while generating the execution plan, the error will be displayed instead of the execution plan.

12.5.5 Checksums

This might be a good time to explain the way checksums work within our scripts. Whenever a SQL statement or execution plan is stored in the repository, the Perl security module Digest::MD5 is drafted into action to generate a unique 32-character "message digest" of the data. Because this digest will be unique for each SQL statement and execution plan, it serves as a unique key that we can use to search for identical SQL statements and compare execution plans. Using a checksum results in better performance — much smaller indexes and faster search times.

12.5.6 Example SPX Report

For your edification and delight, we've reproduced a portion of an SPX report from one of our test databases. In Example 12-14 you'll note that the first SQL statement failed to parse because of Oracle error ORA-00936. This error was the result of the SQL formatting problem mentioned earlier. (This is not a very common error, but worth being aware of.)

The second SQL statement in Example 12-14 also failed during the generation of the execution plan. This was because the user had insufficient privileges on a view's underlying objects. In this case, the PDBAREP user has the SELECT privilege granted on the system view ALL_TABLES, but lacks privileges on the data dictionary tables used in that particular view.

The third and fourth SQL statements shown in Example 12-14 were both successfully submitted to the Oracle parser for the generation of execution plans via the EXPLAIN PLAN statement.

Example 12-14. Example report — sxprpt.pl

Instance: TS01.JKS.COM
sqlUsername: PDBAREP
  
SQL Check Sum: 3413C8988F25F181D463272348F404D4
SnapShot Date: 11/27/2001 12:57:01
SQL Text: 
SELECT TO_CHAR(SYSDATE
   ,'MM/DD/YY') TODAY
   ,        TO_CHAR(SYSDATE
   ,'HH:MI AM') TIME
   ,        
--DATABASE||' Database' DATABASE
   ,        
--rtrim(database) passout        name||' Database' DATABASE
   ,        lower(rtrim(name)) passout 
FROM   v$database
  
Explain Check Sum:
Explain Plan:
  
Explain Error: ORA-00936: missing expression (DBD ERROR: OCIStmtExecute) 
   at ./sxp.pl line 345. eval {...} called at
==========================================================================
sqlUsername: PDBAREP
  
SQL Check Sum: 3A0D45C0E2E730555B413F17A7E41E95
SnapShot Date: 11/25/2001 12:56:24
SQL Text: 
SELECT Table_Name 
FROM ALL_TABLES 
WHERE OWNER = :f1            
ORDER BY Table_Name
  
Explain Check Sum:
Explain Plan:

Explain Error: ORA-01039: insufficient privileges on underlying objects 
   of the view (DBD ERROR: OCIStmtExecute) at
==========================================================================
Instance: TS01.JKS.COM
sqlUsername: PDBAREP
  
SQL Check Sum: 07BF585D872E136C7341FF573CAD8FCD
SnapShot Date: 11/27/2001 12:57:01
SQL Text:          
select     s.global_name cinstance     
   , t.owner     
   , t.table_name     
   , t.column_name     
   , t.column_id     
   , t.data_type     
   , t.data_length     
   , t.data_precision     
   , t.data_scale     
   , t.nullable          
from pdba_snap_dates s
   , pdba_tab_columns t    
where s.global_name like '%'    and t.owner like '%'    
and t.table_name like '%'    
-- here is how to get a range of dates    
and s.pk = 1009436          
and s.pk = t.snap_date_pk    
minus          
select     s.global_name cinstance     
   , t.owner     
   , t.table_name     
   , t.column_name     
   , t.column_id     
   , t.data_type     
   , t.data_length     
   , t.data_precision     
   , t.data_scale     
   , t.nullable          
from pdba_snap_dates s
   , pdba_tab_columns t    
where s.global_name like '%'    and t.owner like '%'    
and t.table_name like '%'    
-- here is how to get a range of dates    
and s.pk = 1000000          
and s.pk = t.snap_date_pk          
order by 1,2,3,4
  
Explain Check Sum: 3D9734F45B31736AB7DF5B69FB8DA713
Explain Plan:                                    TOTAL
POS OPERATION              OBJECT_NAME     COST   ROWS   BYTES  OPTIMIZER
--  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --
 29 SELECT STATEMENT                         29    5K   294203     CHOOSE
  1  MERGE JOIN                              29    5K   294203           
  1   TABLE ACCESS FULL   PDBA_SNAP_DATES    1     7        84   ANALYZED
  2   SORT JOIN                              28  793     32513           
  1    TABLE ACCESS FULL  PDBA_TAB_COLUMNS   4   793     32513   ANALYZED
Explain Error:
==========================================================================
Instance: TS01.JKS.COM
sqlUsername: PDBAREP
  
SQL Check Sum: 3BE4FE5486D11246DA2A358A27A0CE92
SnapShot Date: 11/27/2001 12:57:01
SQL Text: 
select *  
from PDBA_SNAP_DATES 
where  snap_date < trunc(to_date('01/01/1700'
   ,'mm/dd/yyyy')+1)  
order by snap_date
  
Explain Check Sum: 2C7545806582F6D3EC95AA2F48212C6D
Explain Plan:                                     TOTAL
POS OPERATION            OBJECT_NAME         COST  ROWS  BYTES OPTIMIZER
--  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  -- 
  2 SELECT STATEMENT                             2     1    24    CHOOSE
  1  TABLE ACCESS ROWID  PDBA_SNAP_DATES         2     1    24  ANALYZED
  1   INDEX RANGE SCAN   PDBA_SNAP_DATES_UK_IDX  1     1     0  ANALYZED
Explain Error:

12.5.6.1 Comparing execution plans

Now let's take a look at the sxpcmp.pl script. This script scans the SQL buffer via the V_$SQLTEXT view and prepares execution plans and SQL checksums in the buffer. Next, it searches the PDBA repository for a SQL statement with a matching checksum. If more than one match is found in the repository, the most recent one is used. This behavior may be modified with the -rep_report_date option. (See Table 12-9 for all of the command-line options.) If a matching SQL statement is found, the script compares the checksums for the current execution plan and the stored execution plan. If these match, nothing is reported and the next SQL statement is checked. If the checksums for the execution plans don't match, this indicates that some database change has taken place, thus altering the way the SQL executes. The SQL and both execution plans are reported.

Table 12-9. Command-line options — sxpcmp.pl

Option	Description
-machine	Server where the target database resides
-database	Target database
-username	DBA account
-password	Password for the DBA account (optional)
-rep_machine	Server where the target repository resides
-rep_database	Repository database
-rep_username	DBA account password
-rep_password	Password for the DBA account (optional)
-rep_report_date	Date of SQL data to which to compare current SQL. Defaults to the most recent copy of an identical SQL statement.

Example 12-15 contains a sample report generated from sxpcmp.pl. The command used to generate this report looks like this:

$ sxpcmp.pl  -machine sherlock -database ts01 \
    -username system -rep_machine sherlock \
    -rep_database ts01 -rep_username pdbarep \
    -rep_report_date '12/15/2001'  > sxpcmp.txt

12.5.6.2 Looking at the output

It's a good idea to redirect the output of sxpcmp.pl to a file, as we've done via this command, because a fair number of pop-up warnings may end up cluttering the screen on a run through a complex database. Typically, all of the warnings are sent to STDERR, so redirecting STDOUT to a file will often provide a cleaner report.

Example 12-15. Output from sxpcmp.pl

Active SQL From Data Dictionary Matching SQL In Repository    Page:     1
But With Different Execution Paths
  
Database: TS01.JKS.COM                               Date: 12/16/2001 
==========================================================================
SQL Username: SCOTT
SQL Check Sum: 75125F4AD88511A11D3C12AF83BE8F4C
SnapShot Date: 12/15/2001 14:27:57
SQL Text:
  
select /*+ index(e emp_deptno) */  * 
from dept d
, emp e 
where d.deptno = e.deptno 
Current Explain Plan:
                                             TOTAL
POS OPERATION                OBJECT_NAME COST ROWS  BYTES  OPTIMIZER
--------------------------------------------------------------------
  4 SELECT STATEMENT                        4   14    700     CHOOSE
  1   HASH JOIN                             4   14    700           
  1     TABLE ACCESS BY INDEX        EMP    2   14    448   ANALYZED
  1       INDEX FULL SCAN     EMP_DEPTNO    1   14      0   ANALYZED
  2     TABLE ACCESS FULL           DEPT    1    4     72   ANALYZED
  
Stored Explain Plan:
                                              TOTAL
POS OPERATION                OBJECT_NAME COST ROWS   BYTES OPTIMIZER
--------------------------------------------------------------------
  3 SELECT STATEMENT                        3   14     700    CHOOSE
  1   HASH JOIN                             3   14     700           
  1     TABLE ACCESS FULL           DEPT    1    4      72  ANALYZED
  2     TABLE ACCESS FULL            EMP    1   14     448  ANALYZED

Note the following about this output: