Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Smart Scans efficiency chart for Oracle Engineered Systems

with 3 comments

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.

screen-shot-2016-11-08-at-7-06-14-am

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!

Advertisements

Written by Carlos Sierra

November 8, 2016 at 10:26 am

Posted in edb360, Exadata, Tools

3 Responses

Subscribe to comments with RSS.

  1. […] via Smart Scans efficiency chart for Oracle Engineered Systems — Carlos Sierra’s Tools and Tips […]

  2. 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: