High Cell Single Block Physical Read Causes Query Performance Downgrade on XT

郑全

High Cell Single Block Physical Read Causes Query Performance Downgrade on XT (Doc ID 2753761.1)

In this Document
Symptoms
Changes
Cause
Solution
References

APPLIES TO:
Oracle Exadata Storage Server Software - Version 19.3.9.0.0 to 20.1.7.0.0 [Release 12.2 to 20.0]
Information in this document applies to any platform.
SYMPTOMS
The select query has performance issue, the main wait is due to 'cell single block physical read'fromthe awr reports. Seems like the smart scan does not work properly.


CHANGES
The query is compared between XT and HC. The two dbs have every parameter, work load the same. I see the same plan with different amount of rows on both dbs. Flash is not included on XT. The same query on XT takes 47 sec to finish and it takes 9 sec on HC.



CAUSE
Each Exadata XT Storage Server includes twelve 14 TB SAS disk drives with 168 TB total raw disk capacity. To achieve a lower cost, Flash is not included and storage software is optional.

An XT storage server will just work out of the box. enableSmartStorage by default will be set to FALSE (user/operator needs to do nothing).  Even if you create the diskgroup with smart_scan_capable=TRUE, the XT server will just send the blocks back instead of performing a smart scan. So even though the database thinks that it is performing a smart scan, the storage server will automatically not do it.

SOLUTION
At some point in the future, when you buy the licenses, just enableSmartStorage to TRUE on the storage server and magically all the queries will start using smart scan with absolutely no change required to the grid/database.



Please donot compare performance between XT and HC.

REFERENCES
NOTE:2608210.1 - Tuning SQLs to Optimize Smart Scan Performance


NOTE:748642.1 - How to Generate an AWR Report and Create Baselines
NOTE:2310422.1 - SRDC - Exadata: Smart Scan Not Working Issues