Testing the performance effect of a change that changes plans

Download this zip of the example scripts and their logs.

This post is to describe how I go about testing the performance affects of a change that has the potential to change the execution plans of many queries.  For example, adding a new index could change the plan of every query that uses the indexed table.  Similarly, changing the way the optimizer statistics are gathered on a table could modify all of plans that involve that table.

One way you could test the effect of a potential plan changing change would be to exercise the various parts of the application on a test database before making the change, make the change, and then test the application again afterwards.  But, as a database administrator I usually don’t know how to run a comprehensive application test myself.  So, an imperfect but helpful alternative is to grab a substantial number of application SQL queries that were run on production and get their plans on a test database with and without the proposed change.  Those whose plans change should then be run both ways to see if the plan change resulted in a worse or better run time.

Here are the files in the zip and what they represent.  Note that I just ran these on a test database to get a simple example.  It isn’t a full scale real production example so it is overly simple.  Here is what’s in the zip and how they would be used in a real test of a potential production change:

createclobtable.sql – This script should be run on your production database to gather SQL statements that meet whatever criteria you define.  In a real situation I would run this script on production and then export the table sqlclobs from my schema on production and import it into my schema on test.

ORCLcreateclobtable.log – this is the output from running the previous script on my sample database.  It is setup to give me up to 3000 SQL statements that have the string “PLANTEST” in their query.  Imagine that you wanted to add an index on a table called PLANTEST.  Then you would want to get all the production SQLs with this table in their text.  Also, if you had an active production system you might want to limit this SQL text extract to the first 3000 so it isn’t too huge an output to work with.

So, to use the script yourself change

((SQL_TEXT like '%PLANTEST%')) and

to whatever criteria you want to limit the SQL statements to the ones that could be impacted by your change.  Change this to the number of results you want – change from 3000:

        EXIT WHEN sqlnumber > 3000;

makeplans.sql – creates an explain plan for every SQL statement in the table created by the previous script.  In a real scenario I would run this on my test database after importing the table sqlclobs into my schema.  I would run it once before making my proposed change and then again after making the change.  Then I would run a diff (unix text file comparison utility) against the two outputs.  This identifies the changed plans.  Then I would go back to the SQL statements whose plans have changed and run them both with and without the change.  Note that I have a number associated with each SQL statement in the table sqlclobs.  This number relates the plan with the query.  So, the first plan is number 1 and the first SQL text is number 1 and so on sequentially afterward.

ORCLmakeplans.log – this is just the output from my simple test.  I ran the script testquery.sql to create the table PLANTEST and run a query against it.  Then I created an AWR snapshot to record these SQL statements in the AWR.  Interestingly only the create table as select statement that populated PLANTEST with data was caught by the AWR.  The select statement must have run too quickly to be considered a top query.  Here is some sample output, the plan of the create table as select:

1 CREATE TABLE STATEMENT
1 LOAD AS SELECT  PLANTEST
1 HASH JOIN
1 FIXED TABLE FULL X$KSPPCV TABLE (FIXED)
1 MERGE JOIN CARTESIAN
1 HASH JOIN RIGHT OUTER
1 TABLE ACCESS FULL SEG$ CLUSTER
1 HASH JOIN RIGHT OUTER
1 TABLE ACCESS FULL USER$ CLUSTER
1 HASH JOIN RIGHT OUTER
1 TABLE ACCESS FULL DEFERRED_STG$ TABLE
1 HASH JOIN OUTER
1 HASH JOIN OUTER
1 HASH JOIN
1 TABLE ACCESS FULL USER$ CLUSTER
1 HASH JOIN
1 HASH JOIN
1 TABLE ACCESS FULL TS$ CLUSTER
1 TABLE ACCESS FULL TAB$ CLUSTER
1 TABLE ACCESS FULL OBJ$ TABLE
1 INDEX FAST FULL SCAN I_OBJ2 INDEX (UNIQUE)
1 INDEX FAST FULL SCAN I_OBJ1 INDEX (UNIQUE)
1 BUFFER SORT
1 FIXED TABLE FULL X$KSPPI TABLE (FIXED)

I’ve eliminated all of the indention intentionally.  Normally you like to see a plan as a tree with indention to mark the branches.  But, to make it easy to run a diff on the two executions of makeplans.sql I made the plan output something that would only differ if the plan was really different – if that makes sense!  Before I eliminated the indention and other extraneous details I was getting some false differences so I simplified the plan output to this point for ease of comparison.

I know that these scripts could be more user friendly, but these are scripts I really use and they have been a big help to me.  Also the concepts themselves have value and others could no doubt improve on the scripting while applying the same ideas in their own work.

– Bobby