Dani Rey's Oracle Stuff

In my last post I was writing about predicate offloading. I showed that it is possible, to offload a filter such as WHERE columnX equals Y. But how useful is this in real live? Most queries are more complex than this. Many use functions, such as upper, length, substr, mod, ceil, abs, ….

So what happens, if a function is part of a predicate? Does the Exadata Cell know about all of them?

No. But it knows about a lot of them. You can use every Oracle 11g database, to find out which one can be handled by the Exadata Cells and therefore don’t prevent offload processing. This information is part of the data dictionary.

SELECT * FROM v$sqlfn_metadata WHERE offloadable = 'YES';

In addition all those functions can be combined with the following operators.

!=, , =, IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS, IS OF type, NOT, AND, OR

To get an idea of how well it works, I made a simple test using the MOD function. The MOD function calculates [first parameter] modulo [second parameter]. As the ID column is unique, the following query will return every 117th row.

SELECT * FROM opitz.huge_customers WHERE mod(id,117) = 0;

What did the execution plan look like?

--------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |   639K|    46M|   196K  (1)| 00:39:17 |
|*  1 |  TABLE ACCESS STORAGE FULL| HUGE_CUSTOMERS |   639K|    46M|   196K  (1)| 00:39:17 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage(MOD("ID",117)=0)
       filter(MOD("ID",117)=0)

The line
storage(MOD(“ID”,117)=0)
in the Predicate Information, indicates that offload processing could be used for this query. As we already know, we have to have a look at the events, to know whether offload processing was used or not. As you can see, from the first part of the tracefile, offload processing was used.

PARSING IN CURSOR #2 len=86 dep=0 uid=0 oct=3 lid=0 tim=1289240657920538 hv=3576726616 ad='22674a1e0' sqlid='f4d2btvam112s'
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM opitz.huge_customers WHERE mod(id,117) = 0
END OF STMT
PARSE #2:c=2000,e=2852,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=4233741383,tim=1289240657920537
EXEC #2:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4233741383,tim=1289240657920643
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1289240657920667
WAIT #2: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1289240657921078
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=-1 tim=1289240657921136
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 179 name|mode=1263468550 2=131153 0=1 obj#=-1 tim=1289240657921343
WAIT #2: nam='reliable message' ela= 951 channel context=10055297152 channel handle=10033718256 broadcast message=10096394424 obj#=-1 tim=1289240657922493
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=-1 tim=1289240657922535
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 88 name|mode=1263468550 2=131153 0=1 obj#=-1 tim=1289240657922650
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=-1 tim=1289240657922694
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 114 name|mode=1263468545 2=131153 0=2 obj#=-1 tim=1289240657922835
WAIT #2: nam='cell smart table scan' ela= 136 cellhash#=2520626383 p2=0 p3=0 obj#=15043 tim=1289240657931912
WAIT #2: nam='cell smart table scan' ela= 120 cellhash#=88802347 p2=0 p3=0 obj#=15043 tim=1289240657934294
WAIT #2: nam='cell smart table scan' ela= 246 cellhash#=398250101 p2=0 p3=0 obj#=15043 tim=1289240657937276
WAIT #2: nam='cell smart table scan' ela= 1126 cellhash#=2520626383 p2=0 p3=0 obj#=15043 tim=1289240657955228
WAIT #2: nam='cell smart table scan' ela= 1086 cellhash#=88802347 p2=0 p3=0 obj#=15043 tim=1289240657956385
WAIT #2: nam='cell smart table scan' ela= 1085 cellhash#=398250101 p2=0 p3=0 obj#=15043 tim=1289240657957539
WAIT #2: nam='cell smart table scan' ela= 654 cellhash#=2520626383 p2=0 p3=0 obj#=15043 tim=1289240657958306
WAIT #2: nam='cell smart table scan' ela= 205 cellhash#=88802347 p2=0 p3=0 obj#=15043 tim=1289240657958540
WAIT #2: nam='cell smart table scan' ela= 16 cellhash#=398250101 p2=0 p3=0 obj#=15043 tim=1289240657958584
WAIT #2: nam='cell smart table scan' ela= 14378 cellhash#=398250101 p2=0 p3=0 obj#=15043 tim=1289240657974236
WAIT #2: nam='cell smart table scan' ela= 2920 cellhash#=2520626383 p2=0 p3=0 obj#=15043 tim=1289240657977313
WAIT #2: nam='cell smart table scan' ela= 1 cellhash#=88802347 p2=0 p3=0 obj#=15043 tim=1289240657977362
WAIT #2: nam='cell smart table scan' ela= 1 cellhash#=398250101 p2=0 p3=0 obj#=15043 tim=1289240657977379
FETCH #2:c=23996,e=56800,p=94,cr=103,cu=0,mis=0,r=1,dep=0,og=1,plh=4233741383,tim=1289240657977490
...

But is there also a way to see, how much it helped. We already know that the query returns only every 117th row, therefore only a 117th of the table needs to be returned by the Exadata Cells. Can we verify and confirm this?

Yes, there is such information in the v$sesstat View. Remember that those numbers are cumulative and you have to subtract the values of those stats before running the query, from the values after running the query. The following where my results.

physical read bytes = 5891588096 = 5600 MB
cell physical IO interconnect bytes = 47898864 = 45MB

And 5600MB / 117 > 45MB

So reality is even better than the theory. Therefore the overhead of the data blocks divided by 117 was bigger then the overhead of the result set

In my next post, I will write about column projection.