Smart Scans efficiency chart for Oracle Engineered Systems
If you manage an Oracle Engineered System you may wonder how well your Smart Scans are performing. Are you taking full advantage of Exadata Optimizations? If so, how do you measure them?
Uwe Hesse explains well some important statistics on Exadata. For some time now, eDB360 includes a report on Smart Scan efficiency, which is nothing but a Google Chart on top of relevant statistics.
Sample chart below is from a data warehouse DW application. It shows in blue that bytes eligible for offloading are around 95%, which denote a large amount of desired full scans. We see in red that between 80 and 95% of the expected I/O did not hit the network, i.e. it was saved (incorrectly referred as IO Saved since what is saved is the network traffic). And in yellow we see between 30 and 45% of the IO was entirely avoided due to Storage Indexes. So, with 80-95% of the expected data not going through the network and 30-45% of IO entirely eliminated, I could conclude that Exadata Optimizations are well applied on this DW.
If you have SQL*Plus access to an Engineered System, and want to produce a chart like this, simply download and execute free tool eDB360. This tool installs nothing on your database!
Carlos Sierra's Tools and Tips 
[…] via Smart Scans efficiency chart for Oracle Engineered Systems — Carlos Sierra’s Tools and Tips […]
Smart Scans efficiency chart for Oracle Engineered Systems — Carlos Sierra’s Tools and Tips | Jitender Kumar
November 16, 2016 at 11:16 am
For some reason, the LUNs of hard drives in all our exadata machines have null value for the CellDisk attribute:
CellCLI> list lun 0_0 detail
name: 0_0
deviceName: /dev/sda
diskType: HardDisk
id: 0_0
isSystemLun: TRUE
lunSize: 7.152265548706055T
lunUID: 0_0
physicalDrives: 8:0
raidLevel: 0
lunWriteCacheMode: “WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU”
status: normal
Therefor the cell disk topology sql scripts (exadisktopo/2.sql) generate a report without values under LUN_DEVICENAME column. I had to change the filter as below to make it work:
…
select DISTINCT
…
–AND cd.name = lun.celldisk (+)
AND cd.lun=lun.ID(+)
According to http://docs.oracle.com/cd/E80920_01/SAGUG/exadata-storage-server-cellcli.htm, “LUNs that are not yet assigned to a cell disk have a NULL value for the cellDisk attribute”. However, that is not the case. Any idea?
Jie Wu
September 19, 2017 at 8:12 am
I have no idea why you would have NULL om such attribute. Thx for the heads-up with regard to eDB360 code. I am making such change for next upload.
Carlos Sierra
September 19, 2017 at 8:17 am
For some reason, the LUNs of hard drives in all our exadata machines have null value for the CellDisk attribute:
CellCLI> list lun 0_0 detail
name: 0_0
deviceName: /dev/sda
diskType: HardDisk
id: 0_0
isSystemLun: TRUE
lunSize: 7.152265548706055T
lunUID: 0_0
physicalDrives: 8:0
raidLevel: 0
lunWriteCacheMode: “WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU”
status: normal
Therefor the cell disk topology sql scripts (exadisktopo/2.sql) generate a report without values under LUN_DEVICENAME column. I had to change the filter as below to make it work:
…
select DISTINCT
…
–AND cd.name = lun.celldisk (+)
AND cd.lun=lun.ID(+)
According to http://docs.oracle.com/cd/E80920_01/SAGUG/exadata-storage-server-cellcli.htm, “LUNs that are not yet assigned to a cell disk have a NULL value for the cellDisk attribute”. However, that is not the case. Any idea?
jiewu3
September 19, 2017 at 8:13 am