I finished testing 1000 select statements on our new Exadata X5 to see if they would run faster or slower than on our older Exadata V2. Our current production V2 has 12 nodes and the new X5 has only 2. The memory and parallel server parameters on the X5 are 6 times are large as on the old one, since we have one sixth as many hosts and more than 6 times the memory and CPU per host. I think that memory parameters can sometimes change execution plans, and of course with the newer Exadata software who knows what other differences we might see. I wanted to see if any plan changes or other issues caused some queries to run much slower on our newer Exadata system than the old one. I picked 1000 select statements at random from our current production and tested them comparing plans and execution time. In the end I did not find any bad plan changes and on average the tested select statements ran about 4 times faster on the X5 than on the older V2.
I used my testselect package that I have mentioned in several other posts. Here are some other examples of using this package for performance tuning:
https://www.bobbydurrettdba.com/2015/03/02/different-plan_hash_value-same-plan/
In the other posts I was using the package to test the effect of some change on query plans and performance. So, I was comparing two different situations on the same host. But, in this case I was comparing two different hosts with essentially the same data and settings. But they had different versions of Exadata hardware and larger parameters and fewer nodes on the newer host. Here are the results of my first run with all 1000 statements. I got the execution plan for all 1000 select statements but only executed the ones with different plans. Here were the results:
>execute TEST_SELECT.display_results('X5','V2'); Select statements that ran 3 times faster with X5 than with V2. T1=X5 T2=V2 SQLNUMBER T1_EXECUTE_PLAN_HASH T2_EXECUTE_PLAN_HASH T1_ELAPSED_IN_SECONDS T2_ELAPSED_IN_SECONDS --------- -------------------- -------------------- --------------------- --------------------- 3 287237826 287237826 3 34 4 1245040971 1245040971 1 11 9 36705296 2770058206 4 22 ... edited out most of the lines for brevity ... 997 2423577330 2423577330 0 9 998 2217180459 3921538090 1 13 1000 3842377551 1690646521 2 12 Number of selects=329 Select statements that ran 3 times faster with V2 than with X5. T1=V2 T2=X5 SQLNUMBER T1_EXECUTE_PLAN_HASH T2_EXECUTE_PLAN_HASH T1_ELAPSED_IN_SECONDS T2_ELAPSED_IN_SECONDS --------- -------------------- -------------------- --------------------- --------------------- 95 3919277442 3919277442 0 2 210 3508255766 3508255766 0 2 282 3946849555 3085057493 0 6 347 3278587008 789099618 19 170 375 581067860 460184496 0 3 429 534521834 534521834 1 6 569 3953904703 3484839332 0 2 681 946688683 3451337204 1 6 697 908111030 2971368043 0 1 699 3756954097 1915145267 0 1 706 1121196591 1121196591 0 2 708 581067860 460184496 0 4 797 908111030 2841065272 0 5 950 786005624 2571241212 45 460 966 3151548044 3151548044 1 5 Number of selects=15 Summary of test results TEST_NAME TOTAL_ELAPSED_IN_SECONDS SELECTS_EXECUTED AVERAGE_ELAPSED_IN_SECONDS -------------------- ------------------------ ---------------- -------------------------- X5 5545.9999999999999999999 486 11 V2 21138 486 43
Of the tested statements 329 ran 3 or more times faster on the X5. But 15 selects ran 3 or more times faster on the old V2. So, I needed to test the 15 selects again on both servers.
I’m not sure if it was smart or not, but I decided to run all the selects 5 times in a row to maximize caching. The X5 is new and not in use so there wouldn’t be any activity to stimulate caching. My test script for the X5 looked like this:
truncate table test_results; execute TEST_SELECT.execute_all('X5'); execute TEST_SELECT.execute_all('X5'); execute TEST_SELECT.execute_all('X5'); execute TEST_SELECT.execute_all('X5'); execute TEST_SELECT.execute_all('X5'); execute TEST_SELECT.reexecute_errored('X5'); execute TEST_SELECT.reexecute_errored('X5'); execute TEST_SELECT.reexecute_errored('X5'); execute TEST_SELECT.reexecute_errored('X5'); execute TEST_SELECT.reexecute_errored('X5');
After we made sure that the system had cached everything, all 15 selects ran, on average, 4 times faster on the X5 than the V2:
TE SQLNUMBER SQL_ID EXPLAIN_PLAN_HASH EXECUTE_PLAN_HASH ROWS_FETCHED ELAPSED_IN_SECONDS CPU_USED_BY_THIS_SESSION CONSISTENT_GETS DB_BLOCK_GETS PARSE_TIME_ELAPSED PHYSICAL_READS ERROR_MESSAGE -- ---------- ------------- ----------------- ----------------- ------------ ------------------ ------------------------ --------------- ------------- ------------------ -------------- ---------------------------------------------------------------- X5 95 54a8k0yhbgyfq 3919277442 1 0 12 2583 14 0 1 V2 95 54a8k0yhbgyfq 3919277442 1 1 15 2583 14 1 1 V2 210 b132ygmp743h4 3508255766 0 2 19 1592 14 0 1 X5 210 b132ygmp743h4 3508255766 0 2 8 1430 14 0 1 V2 282 aw5f12xsa8c2h 3946849555 0 0 14 3468 14 0 2 X5 282 aw5f12xsa8c2h 3946849555 0 0 8 3322 14 2 2 V2 347 8ncbyjttnq0sk 3278587008 1 3 462 1203794 14 0 61838 X5 347 8ncbyjttnq0sk 3278587008 1 2 206 1126539 14 4 51849 X5 375 4yq5jkmz2khv5 581067860 0 0 9 14530 14 0 2 V2 375 4yq5jkmz2khv5 581067860 0 0 19 14686 14 1 2 V2 429 49pyzgr4swm4p 534521834 0 2 11 1814 14 0 0 X5 429 49pyzgr4swm4p 534521834 0 0 5 1638 14 1 0 X5 569 3afmdkmzx6fw8 630418386 694 0 74 70173 14 3 0 V2 569 3afmdkmzx6fw8 3527323087 694 1 73 68349 14 0 3588 V2 681 dyufm9tukaqbz 668513927 0 0 10 6298 14 0 2 X5 681 dyufm9tukaqbz 3317934314 0 0 8 6096 14 0 2 V2 697 1fqc3xkzw8bhk 908111030 0 0 3 1406 14 0 1 X5 697 1fqc3xkzw8bhk 908111030 0 0 2 1406 14 0 1 V2 699 03qk2cjgr4q2k 1915145267 31 0 476 95922 14 1 0 X5 699 03qk2cjgr4q2k 1915145267 31 0 272 96299 14 0 0 V2 706 28fnjtdhjqwrg 1121196591 0 0 21 1355 14 0 4 X5 706 28fnjtdhjqwrg 1121196591 0 0 13 1355 14 0 4 V2 708 2yrkwqs46nju0 581067860 0 0 14 14684 14 0 0 X5 708 2yrkwqs46nju0 581067860 0 0 9 14528 14 0 0 V2 797 dc5481yn8pm85 908111030 0 0 3 1407 14 0 2 X5 797 dc5481yn8pm85 908111030 0 0 2 1407 14 0 2 V2 950 by6n1m74j82rt 786005624 6 7 2087 249736 14 1 245443 X5 950 by6n1m74j82rt 2571241212 6 0 186 90897 14 0 3 X5 966 5c2n74gfrxwxx 3151548044 12 0 24 116360 14 9 84949 V2 966 5c2n74gfrxwxx 3151548044 12 0 52 119701 14 1 88002
The summary of the results:
Select statements that ran 3 times faster with X5 than with V2. T1=X5 T2=V2 SQLNUMBER T1_EXECUTE_PLAN_HASH T2_EXECUTE_PLAN_HASH T1_ELAPSED_IN_SECONDS T2_ELAPSED_IN_SECONDS --------- -------------------- -------------------- --------------------- --------------------- 95 3919277442 3919277442 0 1 429 534521834 534521834 0 2 569 630418386 3527323087 0 1 950 2571241212 786005624 0 7 Number of selects=4 Select statements that ran 3 times faster with V2 than with X5. T1=V2 T2=X5 SQLNUMBER T1_EXECUTE_PLAN_HASH T2_EXECUTE_PLAN_HASH T1_ELAPSED_IN_SECONDS T2_ELAPSED_IN_SECONDS --------- -------------------- -------------------- --------------------- --------------------- Number of selects=0 Summary of test results TEST_NAME TOTAL_ELAPSED_IN_SECONDS SELECTS_EXECUTED AVERAGE_ELAPSED_IN_SECONDS -------------------- ------------------------ ---------------- -------------------------- X5 4 15 0 V2 16 15 1
I guess it is no surprise that the X5 is faster than the five-year older V2. But, I thought it was a good example of how to use my testselect package to do see how a set of queries will run in two different situations.
Bobby