Oracle SPARC M7 – tracing DAX with DTrace and busstat

Thanks to Oracle I had a possibility to test the new Sparc M7 with DAX coprocessors to boost In-Memory performance.
You can read about it here and here

My first thought was – how to check if and when the DAX coprocessors are being used?

When you have a POC for Exadata, you want to check wait events for CELL SMART SCAN, to be sure that query offloading occurred.
Unfortunately there are no DAX-specific wait events, that you can check at the database level – so how will we know that DAX coprocessors are working? Should we just believe that queries are being offloaded? Well… there’s always DTrace 😉

It’s time for testing! I have three tables, loaded into In-Memory buffer:

Let’s start with something simple – what modules/libraries are used, when querying a table stored in memory?
I will use this DTrace script

to trace the server process, which will execute this query:

and the results look like this:

WOW – it’s great! We can see that Oracle is using two DAX-specific libraries – libdax.so.1 and libdax_query.so.1.
Those libraries can be found in this path: /usr/lib/sparcv9/
After closer examination we also find out that libdax_query.so.1 contains functions for offloading OZIP operations – that’s why DAX coprocessors are most effective with MEMCOMPRESS FOR QUERY HIGH algorithm.

Another great finding was that the fbt provider, provides functions for tracing dax at system level.

After some investigation, I’ve noticed that most functions responsible for In-Memory execution are called kdmo*, kdzdcol*, kdml* and *ozip*. So I’ve written a short DTrace script to reveal all the functions names and execution times (nanosec) used in my previous query:

Here are the results:

We can also trace DAX usage at the system level, using just DAX module from FBT provider – user stack examination can give us some information about functions which were offloaded. To do that, we can use this DTrace scirpt:

Sample results will look like this:

In the above example we can see that OZIP algorithm was offloaded by DAX coprocessors.

Another way to trace DAX usage is busstat:

Those are physical CPU counters that can be used to determine the performance of DAX pipelines. There is no documentation (yet?) for those counters, but thanks to Martin Mueller, I know the meaning of two of them:

• DAX_SCH_query_cmd_sched (number of query cmds scheduled)
• DAX_SCH_query_cmd_stall_cyc (# of cycles a query cmd had to stall because of lack resources, a high number would indicate DAX overload)

You can monitor them using this command:

That’s it for now 🙂

P.S.
If you want to learn about dynamic tracing technics for analyzing Oracle behavior – you should read this awesome article by Luca Canali http://db-blog.web.cern.ch/blog/luca-canali/2016-01-linux-perf-probes-oracle-tracing