Tested 1000 Select Statements on New Exadata X5

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/

https://www.bobbydurrettdba.com/2013/12/09/testing-removing-subpartitions-from-a-table-with-query-testing-package/

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.