Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Creating a SQL Plan Baseline from Cursor Cache or AWR

with 18 comments

A DBA deals with performance issues often, and having a SQL suddenly performing poorly is common. What do we do? We proceed to “pin” an execution plan, then investigate root cause (the latter is true if time to next fire permits).

DBMS_SPM provides some APIs to create a SQL Plan Baseline (SPB) from the Cursor Cache, or from a SQL Tuning Set (STS), but not from the Automatic Workload Repository (AWR). For the latter, you need a two-steps approach: create a STS from AWR, then load a SPB from the STS. Fine, except when your next fire is waiting for you, or when deciding which is the “best” plan is not trivial.

Take for example chart below, which depicts multiple execution plans with different performance for one SQL statement. The SQL statement is actually quite simple, and data is not significantly skewed. On this particular application, usually one-size-fits-all (meaning one-and-only-one plan) works well for most values passed on variable place holders. Then, which plan would you choose?

Sample chart created by SQLd360

Looking at summary of known Execution Plans’ performance below (as reported by planx.sql), we can see the same 6 Execution Plans.

1st Plan on list shows an average execution time of 2.897ms according to AWR, and 0.896ms according to Cursor Cache; and number of recorded executions for this Plan are 2,502 and 2,178 respectively. We see this Plan contains one Nested Loop, and if we look at historical performance we notice this Plan takes less than 109ms 95% of the time, less than 115ms 97% of the time, and less then 134ms 99% of the time. We also see that worst recorded AWR period, had this SQL performing in under 150ms (on average for that one period).

We also notice that last plan on list performs one execution in 120.847ms on average (as per AWR) and 181.113ms according to Cursor Cache (on average as well). Then, “pinning” 1st plan on list seems like a good choice, but not too different than all but last plan, specially when we consider both: average performance and historical performance according to percentiles reported.

[code language=”text”]

Plan ET Avg ET Avg CPU Avg CPU Avg BG Avg BG Avg Executions Executions ET 100th ET 99th ET 97th ET 95th CPU 100th CPU 99th CPU 97th CPU 95th
Hash Value AWR (ms) MEM (ms) AWR (ms) MEM (ms) AWR MEM AWR MEM MIN Cost MAX Cost NL HJ MJ Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms)
———– ———– ———– ———– ———– ———— ———— ———— ———— ———- ———- — — — ———– ———– ———– ———– ———– ———– ———– ———–
4113179674 2.897 0.896 2.715 0.714 96 5 2,502 2,178 8 738 1 0 0 149.841 133.135 114.305 108.411 147.809 133.007 113.042 107.390
578709260 29.576 32.704 28.865 31.685 1,583 1,436 6,150 1,843 67 875 1 0 0 154.560 84.264 65.409 57.311 148.648 75.209 62.957 56.305
1990606009 74.399 79.054 73.163 77.186 1,117 1,192 172 214 905 1,108 0 1 0 208.648 208.648 95.877 95.351 205.768 205.768 94.117 93.814
1242077371 77.961 77.182 1,772 8,780 949 1,040 0 1 0 102.966 98.206 91.163 89.272 100.147 97.239 90.165 88.412
2214147219 79.650 82.413 78.242 80.817 1,999 2,143 42,360 24,862 906 1,242 0 1 0 122.535 101.293 98.442 95.737 119.240 99.118 95.266 93.156
1214505235 120.847 181.113 105.485 162.783 506 1,355 48 12 114 718 1 0 0 285.950 285.950 285.950 285.950 193.954 193.954 193.954 193.954

Plans performance summary above is displayed in a matter of seconds by planx.sql, sqlperf.sql and by a new script spb_create.sql. This output helps make a quick decision about which Execution Plan is better for “pinning”, meaning: to create a SPB on it.

Sometimes such decision is not that trivial, as we can see on sample below. Which plan is better? I would go with 2nd on list. Why? performance-wise this plan is more stable. It does a Hash Join, so I am expecting to see a Plan with full scans, but if I can get consistent executions under 0.4s (according to percentiles), I would be tempted to “pin” this 2nd Plan instead of 1st one. And I would stay away from 3rd and 5th. So maybe I would create a SPB with 3 plans instead of just one, and include on this SPB 1st, 2nd and 4th on the list.

[code language=”text”]

Plan ET Avg ET Avg CPU Avg CPU Avg BG Avg BG Avg Executions Executions ET 100th ET 99th ET 97th ET 95th CPU 100th CPU 99th CPU 97th CPU 95th
Hash Value AWR (ms) MEM (ms) AWR (ms) MEM (ms) AWR MEM AWR MEM MIN Cost MAX Cost NL HJ MJ Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms) Pctl (ms)
———– ———– ———– ———– ———– ———— ———— ———— ———— ———- ———- — — — ———– ———– ———– ———– ———– ———– ———– ———–
1917891576 0.467 0.334 0.330 0.172 119 33 554,914,504 57,748,249 6 1,188 2 0 0 6,732.017 10.592 1.628 1.572 1,420.864 1.557 1.482 1.261
99953997 1.162 2.427 0.655 0.492 83 55 58,890,160 2,225,247 12 2,311 0 1 0 395.819 235.474 108.142 34.909 56.008 22.329 12.926 3.069
3559532534 1.175 1,741.041 0.858 91.486 359 46 21,739,877 392 4 20 1 0 0 89,523.768 4,014.301 554.740 298.545 21,635.611 216.456 54.050 30.130
3650324870 2.028 20.788 1.409 2.257 251 199 24,038,404 143,819 11 5,417 0 1 0 726.964 254.245 75.322 20.817 113.259 21.211 13.591 8.486
3019880278 43.465 43.029 20,217 13,349 5,693 5,693 0 1 0 43.465 43.465 43.465 43.465 43.029 43.029 43.029 43.029

About new script spb_create.sql

This new script is a life-saver for us, since our response time for an alert is usually measured in minutes, with a resolution (and sometimes a root cause analysis) expected in less than one hour from the time the incident is raised.

This script is quite simple:

  • it provides a list of known Execution Plans including current (Cursor Cache) and historical (AWR) performance as displayed in two samples above, then
  • asks on which Plan Hash Values (PHVs) you want to create a SPB on. It allows you to enter up to 3 PHVs; last
  • asks if you want these plans to be set as FIXED

After you respond to ACCEPT parameters, then a SPB for your SQL is created and displayed. It does not matter if the Plan exists on Cursor Cache and/or on AWR, it finds the Plan and creates the SPB for you. Then: finding known Execution Plans, deciding which one is a better choice (or maybe more than one), and creating a SPB, all can be done very rapidly.

If you still prefer to use SQL Profiles and not SPBs for whatever reason, script coe_xfr_sql_profile.sql is still around and updated. On these 12c days, and soon 18c and beyond, I’d much rather use SQL Plan Management and create SPBs although!

Anyways, enjoy these free scripts and become a faster hero “pinning” good plans. Then don’t forget to do diligent root cause analysis afterwards. I use SQLd360 by Mauro Pagano for deep understanding of what is going on with my SQL statements.

Soon, I will post about a cool free tool that automates the implementation of SQL Plan Management on a high-rate OLTP where stability is more important than flexibility (frequently changing Execution Plans). Stay tuned!


Written by Carlos Sierra

December 1, 2017 at 6:32 am

18 Responses

Subscribe to comments with RSS.

  1. Thanks for sharing such a useful information does that mean in future sql profile option will be taken off . suppose if I choose between sqlprofile or sqlblaseline to pin the plan which one should I use. Does Pinning the plan go wrong if the table data grow to much in the coming future. Thanks in advance.


    December 1, 2017 at 7:17 am

    • SQL profiles as well as almost all functionality of an Oracle databases cannot simply be taken out. SQL Profiles are here to stay, but SQL Plan Management is a superset. If you are on 11g or higher, you want to use SQL Plan Management since it provides comprehensive functionality when it comes to plan stability. Pinning a plan does not go wrong if the table grows. The plan that you pin is because that is the one you decided to use regardless of data grow, that is the whole purpose. If you want your plan to change even when you created a SQL Plan Baseline for it, then look for “Adaptive SQL Plan Management on 12c”.

      Carlos Sierra

      December 1, 2017 at 10:08 am

      • Thanks carlos for explaining me and keeping us updated.


        December 1, 2017 at 1:46 pm

  2. Hello Carlos,

    Thanks for this new script. I am wondering whether the Estimated Average Elapsed time is always correct since, in this script, you don’t consider the execution in memory with regards to their end_of_fetch_count and that from AWR with regards to their end_of_fetch_count_delta. This is particularly true, when the execution spans several snapshots without finishing. The coe_xfr_sql_profile.sql has proposed me, in a real life case, the bad plan hash_value to fix as a SQL profile. When I checked this I found that the end_of_fetch_count_delta of this plan_hash_value was equal to zero for several lines. Unfortunately I am still unable to come with a reproducible case. I have sent an e-mail to Abel Macias about this issue a couple of months ago but didn’t receive any answer.
    Best Regards
    Mohamed Houri


    December 1, 2017 at 7:29 am

    • In our case executions take milliseconds and they complete, so we do not need to look at end_of_fetch counts, but I do see your point. Estimated average elapsed time are always just that: an estimate. Of course these scripts would not cover corner cases, so please always feel free to customize them to specific cases. Thanks for your insight!

      Carlos Sierra

      December 1, 2017 at 10:02 am

  3. Thanks for sharing Carlos. We’ve been dealing with plan stability problems ever since we upgraded to 12c. We previously only used SQL Profiles but are now actively looking close at SPM so this article was very much on the spot!

    Curious, is the next version of Oracle going to be 13c or 18c as you stated in your article.

    Thanks again!

    Darren Browers

    December 1, 2017 at 11:16 am

  4. thank you very much Carlos for sharing the information

    Shivanand Hindwan

    December 1, 2017 at 8:08 pm

  5. Thanks for the post Carlos. Can’t wait to hear about the new FREE tool to automatically implement SBPs!

    Joe C

    December 1, 2017 at 10:10 pm

    • Joe: Take a look at the presentations link (right side of my blog). Please read the one about Adapting and Adopting SQL Plan Management. Then find the tool under my cscripts. Look for directory iod_spm_fpz. Be sure you understand what it does before customizing it.

      Carlos Sierra

      December 1, 2017 at 10:15 pm

  6. Hi Carlos. previously when we used coe_xfr_sql_profile.sql there were force_match => TRUE but in spm is there any possibilites to use force_match=TRUE for spm also? i searched internet but did not find anything.



    December 19, 2017 at 10:36 am

  7. Hi Carlos, previously when we used coe_xfr_sql_profile.sql there were force_match=TRUE option but in spm there is no possibilites for that. if we are using literals instead of bind variables on the queries what option we need to use in sql plan management script?



    December 19, 2017 at 10:39 am

    • There is no equivalent on SPM. You may want to consider using binds in place of literals

      Carlos Sierra

      December 20, 2017 at 11:17 am

  8. Hi, I can`t download the scripts from git. Can you please re-upload them? Thanks


    January 11, 2018 at 8:17 pm

  9. I can’t download create_sql_plan_baseline.sql script from github.
    Can you fix it?
    Thanks !


    June 10, 2018 at 7:27 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: