Here’s a zip of a script I modified today: zip
Here’s an example output:
QUERY_NUM SQL_ID PLAN_HASH_VALUE EXECUTIONS AVG_ELAPSED OPTIMIZER_COST AVG_FETCHES AVG_SORTS AVG_DISK_READS AVG_BUFFER_GETS AVG_ROWS AVG_CPU AVG_IOWAIT AVG_DIRECT_WRITES AVG_PHYS_READS AVG_PHYS_WRITES ---------- ------------- --------------- ---------- ----------- -------------- ----------- ---------- -------------- --------------- ---------- ---------- ---------- ----------------- -------------- --------------- 1 gxk0cj3qxug85 2051250004 39504258 31630.2852 15 4.71993394 .444248288 4.07294381 440.124393 41.3960784 3447.83137 28056.5262 0 .006406626 0 1 gxk0cj3qxug85 548353012 24360619 31854.5456 15 4.73696596 .574941507 4.16225047 443.290799 41.5668639 3501.62695 28205.9349 0 .009019804 0 2 53b6j9nvd2vwa 376144290 1069593 438746.758 19.6425025 33.7741683 0 58.9193684 3864.5447 332.18592 32952.0388 406548.271 0 19.2981312 0 2 53b6j9nvd2vwa 655563694 1008553 414586.506 15.0111675 33.7122908 0 58.6486828 3851.28671 331.575216 32283.0233 382834.453 0 12.4507269 0 2 53b6j9nvd2vwa 2504177057 274652 418157.478 19.5541562 32.8918486 0 61.1868838 3726.22908 323.358235 31005.0901 388545.269 0 23.2050923 0 3 4usguw7d6kxy4 2543632952 1070303 151648.49 12911.7175 2.30832577 .999997197 5.49557555 6221.80141 16.7674061 130072.596 10153.4778 0 .388805787 0 3 4usguw7d6kxy4 3221641997 996033 151860.479 11987.696 2.22684489 .999998996 7.10842914 6073.16306 15.902446 127194.45 13655.9405 0 .316254582 0 3 4usguw7d6kxy4 1764817920 2 277287.5 12860 1 1 1 6956 5 260000 10575.5 0 1 0 4 buhvbrfw1uyrd 2225737849 2871021 37985.4363 7.37064414 32.5216263 0 5.0695129 439.697767 319.641241 3463.90361 34645.8396 0 2.01971389 0 5 bvttgft3kj2hg 3569562598 293543 252814.965 20.6213018 95.7064212 .999996593 12.3908524 11035.1541 951.571562 137023.945 95634.8559 0 1.94147365 0 6 084v1t4whvzwd 883418687 70258 940211.781 4875.03099 1.00001423 0 30.6947394 22558.3683 .954980216 880713.798 16997.2457 0 2.93185118 0 7 972sx2uhfswn5 632842214 229406586 279.732773 14 1.00002261 .092121309 .003579313 25.6327361 .941600155 229.417607 23.334532 0 .000786085 0
I’m using this script to help figure out which plans to lock in with SQL profiles. For example, sql_id gxk0cj3qxug85 executes plan 2051250004 more often than plan 548353012 and they seem similar in performance so maybe it would be safe to just lock in the first plan.
I’ve also been reviewing the plans manually to see if there were major differences or if the plans were essentially the same.
I had a few minutes so I thought I would pass this along.
– Bobby
