With 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 ‘Exadata-ize’?
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.
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 “cell” (a cell refers to the Exadata Storage server); the most common waits with descriptive names include “cell smart table scan,” “cell multiblock physical read,” and “cell single block physical read.”
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:
Event
Waits
Total Wait Time (sec)
Avg Wait
% DB time
Wait Class
DB CPU
115.2K
70.4
SQL*Net more data from dblink
670,196
5471.5
8.16ms
3.3
Network
cell single block physical read
5,661,452
3827.6
676.07us
2.3
User I/O
Sync ASM rebalance
4,350,012
3481.3
800.30us
2.1
Other
cell multiblock physical read
759,885
2252
2.96ms
1.4
User I/O
direct path read
374,368
1811.3
4.84ms
1.1
User I/O
SQL*Net message from dblink
7,983
1725
216.08ms
1.1
Network
cell smart table scan
1,007,520
1260.7
1.25ms
0.8
User I/O
direct path read temp
520,211
808.4
1.55ms
0.5
User I/O
enq: TM — contention
652
795.8
1220.55ms
0.5
Application
From such AWR statistics, the following conclusions are often drawn:
1. The contribution of Exadata magic to database performance is low — it does not exceed 5%, and the database ‘Exadata-izes’ poorly.
2. If such a database is moved from Exadata to a classic architecture of ‘server + array,’ performance will not change significantly. Because even if this array turns out to be three times slower than the Exadata storage system (which is hardly possible for modern All Flash arrays), multiplying 5% by three will give us an increase in I/O wait share to 15% — such a database will surely survive!
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 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.
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.
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.
Author: Alexey Struchenko, head of the DB department at 'Infosis Jet'
Source: habr.com
