![\"AWR:](\"\/wp-content\/uploads\/2020\/09\/b0ca997a4bd415723ae56c183e2dd42f.jpeg\")
<\/p>\nWith this short post, I would like to dispel a misunderstanding regarding the analysis of AWR for databases running on Oracle Exadata. For nearly 10 years, I have been continually faced with the question: what is the contribution of Exadata Software to performance? Or, using newly coined terms: how much does a database \u2018Exadata-ize\u2019?<\/p>\n
<\/p>\n
Often, this correct question is answered incorrectly, referencing AWR statistics. It presents a system wait method, interpreting response time as the sum of CPU time (DB CPU) and waiting times of various classes.<\/p>\n
With the emergence of Exadata, specific system waits related to Exadata Software appeared in AWR statistics. Typically, the names of these waits begin with the word \u201ccell\u201d (a cell refers to the Exadata Storage server); the most common waits with descriptive names include \u201ccell smart table scan,\u201d \u201ccell multiblock physical read,\u201d and \u201ccell single block physical read.\u201d<\/p>\n
In most cases, the share of such Exadata waits in the overall response time is small, and therefore they do not even make it into the Top10 Foreground Events by Total Wait Time section (in this case, they should be searched for in the Foreground Wait Events section). We have struggled to find an example of a daily AWR at our clients where Exadata waits made it into the Top10 section and accounted for about 5% in total:<\/p>\n
Event<\/strong><\/p>\n Waits<\/strong><\/p>\n Total Wait Time (sec)<\/strong><\/p>\n Avg Wait<\/strong><\/p>\n % DB time<\/strong><\/p>\n Wait Class<\/strong><\/p>\n DB CPU<\/p>\n <\/p>\n 115.2K<\/p>\n <\/p>\n 70.4<\/p>\n <\/p>\n SQL*Net more data from dblink<\/p>\n 670,196<\/p>\n 5471.5<\/p>\n 8.16ms<\/p>\n 3.3<\/p>\n Network<\/p>\n cell single block physical read<\/p>\n 5,661,452<\/p>\n 3827.6<\/p>\n 676.07us<\/p>\n 2.3<\/p>\n User I\/O<\/p>\n Sync ASM rebalance<\/p>\n 4,350,012<\/p>\n 3481.3<\/p>\n 800.30us<\/p>\n 2.1<\/p>\n Other<\/p>\n cell multiblock physical read<\/p>\n 759,885<\/p>\n 2252<\/p>\n 2.96ms<\/p>\n 1.4<\/p>\n User I\/O<\/p>\n direct path read<\/p>\n 374,368<\/p>\n 1811.3<\/p>\n 4.84ms<\/p>\n 1.1<\/p>\n User I\/O<\/p>\n SQL*Net message from dblink<\/p>\n 7,983<\/p>\n 1725<\/p>\n 216.08ms<\/p>\n 1.1<\/p>\n Network<\/p>\n cell smart table scan<\/p>\n 1,007,520<\/p>\n 1260.7<\/p>\n 1.25ms<\/p>\n 0.8<\/p>\n User I\/O<\/p>\n direct path read temp<\/p>\n 520,211<\/p>\n 808.4<\/p>\n 1.55ms<\/p>\n 0.5<\/p>\n User I\/O<\/p>\n enq: TM \u2014 contention<\/p>\n 652<\/p>\n 795.8<\/p>\n 1220.55ms<\/p>\n 0.5<\/p>\n Application<\/p>\n From such AWR statistics, the following conclusions are often drawn:<\/p>\n 1. The contribution of Exadata magic to database performance is low \u2014 it does not exceed 5%, and the database 'exadata-fies' poorly.<\/p>\n 2. If such a database is moved from Exadata to a classic 'server + array' architecture, the performance will not change significantly. Because even if this array turns out to be three times slower than the storage system of Exadata (which is unlikely for modern All Flash arrays), multiplying 5% by three gives us an increase in input-output wait time to 15% \u2014 a database is sure to withstand that!<\/p>\n Both of these outputs are inaccurate; moreover, they distort the understanding of the idea embedded in Exadata Software. Exadata does not merely provide fast input-output; it operates fundamentally differently compared to the traditional architecture of 'server + array.' If the database operation is truly 'Exadata-like,' then SQL logic is transferred to the storage system. servers<\/a> Thanks to a number of special mechanisms (primarily Exadata Storage Indexes, but not only), they find the necessary data themselves and send it to the DB servers. They do this quite efficiently, which is why the share of characteristic Exadata waits in the overall response time is small. <\/p>\n How will this share change outside of Exadata? How will it affect the overall performance of the database? The best way to answer these questions is through testing. For example, the 'cell smart table scan' wait outside of Exadata can turn into such a heavy Table Full Scan that the input-output can consume all response time, dramatically worsening performance. This is why it is incorrect to consider the total percentage of Exadata waits as a contribution of its magic to performance when analyzing AWR, and even more so to use this percentage for forecasting performance outside of Exadata. To understand how 'Exadata-like' the database operation is, it is necessary to study the AWR statistics in the 'Instance Activity Stats' section (there are many statistics with descriptive names) and compare them with each other.<\/p>\n To understand how the database will behave outside of Exadata, it is best to create a clone of the database from a backup on the target architecture and analyze the performance of this clone under load. Such an option is typically available to Exadata owners.<\/p>\n