Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, September 1, 2012

EXADATA smart scan is used by my database/ application

Is my application scalable on my EXADATA, is EXADATA for me? Will my queries benefit from EXADATA smart scan?

Exadata has the ability to off load the load onto the storage cells and do smart scan by doing it saves tons of IO round trips which yields faster response times...

So what is :
Cell Offloading:-
The storage cells are intelligent enough to process some workload inside them, saving the database nodes from that work. This process is referred to as cell offloading.

&

Smart Scan:- ( Mostly applicable to Direct path accesses like Full Table scans and Index fast full scans )
In a traditional Oracle database, when a user selects a row or even a single column in a row, the entire block containing that row is fetched from the disk to the buffer cache, and the selected row (or column, as the case may be) is then extracted from the block and presented to the user’s session. In the Exadata Database Machine, this process holds true for most types of access, except a very important few. Direct path accesses – for instance, full table scans and full index scans – are done differently. The Exadata Database Machine can pull the specific rows (or columns) from the disks directly and send them to the database nodes. This functionality is known as Smart Scan. It results in huge savings in I/O.

Storage Indexes
How does Smart Scan achieve sending only those relevant rows and columns instead of blocks? A special data structure built on the pattern of the data within the storage cells enables this. For a specific segment, it stores the minimum, maximum, and whether nulls are present for all the columns of that segment in a specified region of the disk, usually 1MB in size. This data structure is called a storage index. When a cell gets a Smart Scan-enabled query from the database node via iDB, it checks which regions of the storage will not contain the data. For instance if the query predicate states where rating = 3, a region on the disk where the minimum and maximum values of the column RATING are 4 and 10 respectively will definitely not have any row that will match the predicate. Therefore the cell skips reading that portion of the disk. Checking the storage index, the cell excludes a lot of regions that will not contain that value and therefore saves a lot of I/O.


Now how to verify if my database queries are getting benefited from these new features.

One way to verify this is generate the Instance specific AWR report, in that look under
"Instance Activity Statistics"  you will the below:

If the cell index scans counter is "0" that means optimizer didn't  filter the predicate using the storage index (cell memory index) . Note:- Memory for this storage index is automatically managed and user has no control over this.  For good this number should be always high which indicates smart scan is being used productively.

Cell physical IO bytes eligible for predicate offload --- This number should be high
The higher the number more  MB/GB is filtered out at the cell level itself rather sending it to the buffer cache to filter the rows.

Cell physical IO bytes saved by storage index--- This number should be high
This number will give how much MB/GB we saved by doing a smart scan

cell physical IO interconnect bytes:
  Bytes transferred  by the interconnect here in example is 8gb.

cell physical IO interconnect bytes returned by smart scan: -- This number should be high or equal to "cell physical IO interconnect bytes: " the bigger the number the more number of bytes has been sent by smart scan from cell storage.  It will be a optimal system if the number of "bytes returned by smart scan" equals to "cell physical IO interconnect bytes" that means optimizer is using the smart scan to fullest.


   In my example the AWR reports shows the counters as zero i.e i did have database load (8g interconnect traffic )but the optimizer didn't use the smart scan feature for this load.

Another place to look for smart scan is under I/O Stat functions:









If you see Smart scan here and it matches with Buffer Cache Reads that means it is using smart scan to the fullest.




EXADATA wait events:
Try to minimize the single block reads in EXADATA.




Buffered waits:
cell single block physical read
cell list of blocks physical read
cell multiblock physical read
In EXADATA db file sequential read and "db file scattered read" has been replaced  by the below buffered wait events if you these wait events that means the optimizer is not using the smart scan and loading the blocks into the buffer cache, filtering the rows then passing them back to the user.  This is the traditional approach of doing things i.e processing(using the CPU) at the compute nodes instead of paralleling the load at the storage cell level.

Direct waits:
cell smart table scan ( New direct read wait event )

 The results are fetched for a particular query and will not used by another query since this is a direct read.

Next place to look at is the Unoptimized SQLs.



Here look at the queries where %Optimal  is lower and try to tune the queries so that they start using the EXADATA smart scan so on..


4 comments:

Vinay said...

Hi
So what do you do to convert cell single block physical reads to smart scan?.

e.g creat bitmap index on my system is showing cell single block physical reads and taking a very long time. My app is Siebel Analytics. What can I do to speed up?

Thanks

sameer said...

Vinay,

EXADATA is all about doing direct reads i.e Full table scans.
EXADATA Perf team recommends not to use indexes except when needed like primary key, domain, unique indexes,function indexes ..

First see if you really need an index.. just make the index invisible and see if it is doing Full storage cell call to the table and if it is faster than the index, you can also cache the whole table into the smart cache.

Anonymous said...

Hi Sameer
I would like to connect with you on linked in. what is your userid?

thanks

sameer said...

Sorry I am not on Linked in yet.