Exploring 18c exadata functions
Recently I was granted access to the finest and latest exadata. Unfortunately it’s not mine, but I was asked if I managed to log in remotely as a test. Of course I wanted to test that. As it turned out, it was the very latest build of 18c which is available for exadata. You can find the release schedules for Oracle versions here: the single-source-of-truth MOS Note: 742060.1 – Release Schedule of Current Database Releases. I realised this was a cool opportunity and asked the person if I could play with it a bit. During my presentations I often say “expect the unexpected” and I got even a personal sandbox database on the system. Yay!
Recently some colleagues in the Oracle Community pointed me to a very useful view to start exploring databases: v$sqlfn_metadata. This one contains metadata about operators and built-in functions. Note that this view does not contain information about arguments because the number of arguments will be different for various functions. Information about arguments is contained in V$SQLFN_ARG_METADATA. So let’s see what are the top-ten newest build-in functions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> select * from V$SQLFN_METADATA order by 1 desc fetch first 10 rows only; FUNC_ID NAME MINARGS MAXARGS DATATYPE VERSION ANA AGG OFF DISP_TYPE USAGE DESCR CON_ID ---------- -------------------- ---------- ---------- -------- ------------ --- --- --- ------------- -------- ------------------------------------------------------------ ---------- 1148 TO_DOG_YEAR 1 4 NUMERIC V13 Oracle NO NO YES NORMAL TO_DOG_YEAR 0 1147 JSON_MERGEPATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_MERGEPATCH 0 1146 JSON_PATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_PATCH 0 1145 ROUND_TIES_TO_EVEN 1 2 NUMERIC INVALID NO NO YES NORMAL ROUND_TIES_TO_EVEN 0 1144 CON_ID_TO_CON_NAME 1 0 UNKNOWN INVALID NO NO NO NORMAL CON_ID_TO_CON_NAME 0 1143 TIMESTAMP_TO_NUMBER 1 1 UNKNOWN INVALID NO NO YES NORMAL TIMESTAMP_TO_NUMBER 0 1142 TO_UTC_TIMESTAMP_TZ 1 0 UNKNOWN INVALID NO NO YES NORMAL TO_UTC_TIMESTAMP_TZ 0 1141 OPTSYSAPPROXRANK 1 0 UNKNOWN INVALID NO NO NO NORMAL Internal evaluation function for multiple approx_rank's 0 1140 APPROX_RANK 1 1 NUMERIC INVALID NO YES NO NORMAL APPROX_RANK 0 1139 APPROX_SUM 1 2 NUMERIC INVALID NO YES NO NORMAL APPROX_SUM 0 10 rows selected. SQL> |
Mmz, really? Are we serious? This can’t be true, so I did what I always do when I don’t believe something. Look it up. In this case, as I did not find things in the documentation, I used my friend google. 
Apparantly I’m not the only one who is looking for this and it IS a thing it seems. I can imagine that it might come in handy for statistical things or so. As this is very remarkable, I want to see a little more from this.
First easy test is “does it work”? I pick just a random number.
|
1 2 3 4 5 6 7 |
SQL> select to_dog_year(to_date('28-03-2013','DD-MM-YYYY')) from dual; TO_DOG_YEAR(to_date('28-03-2013','DD-MM-YYYY')) ------------------------------------------------ 36.21 SQL> |
Apparently it does 😀 and there is apparently some intelligence build in. I was expecting 35.
Any other parameters documented?
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select * from V$SQLFN_ARG_METADATA where FUNC_ID=1148; FUNC_ID ARGNUM DATATYPE DESCR CON_ID ---------- ---------- -------- ---------------------------------------- ---------- 1148 1 DATETYPE DOB 0 1148 2 BOOLEAN FEMALE 0 1148 3 STRING NLS_BREED 0 1148 4 BOOLEAN OWN_SMOKE 0 SQL> |
A boolean datatype??? Anyhow, it seems lots of new stuff is coming up. Exciting times ahead!
Ok and what about offloading? Let’s find out.
|
1 2 3 4 5 6 7 |
SQL> select /*+ gather_plan_statistics vanpupi */ to_dog_year(to_date('28-03-2013','DD-MM-YYYY')) from dual; TO_DOG_YEAR(5) -------------- 36.21 SQL> |
Then check the plan:
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select sql_id,sql_text,PLAN_HASH_VALUE,CHILD_NUMBER from v$sql where sql_text like '%statistics%vanpupi%'; SQL_ID SQL_TEXT PLAN_HASH_VALUE CHILD_NUMBER ------------- --------------------------------------------------------------------------- --------------- ------------ dwx96cxbqshad select /*+ gather_plan_statistics vanpupi */ to_dog_year(to_date('28-03-2013','DD-MM-YYYY')) from dual 1388734953 0 9873s5xfzx8d2 select sql_id,sql_text,PLAN_HASH_VALUE,CHILD_NUMBER from v$sql where sql_te 903671040 0 xt like '%statistics%vanpupi%' SQL> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> @xplain_short Enter value for sql_id: dwx96cxbqshad Enter value for child_no: 0 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID dwx96cxbqshad, child number 0 ------------------------------------- select /*+ gather_plan_statistics vanpupi */ to_dog_year(to_date('28-03-2013','DD-MM-YYYY')) from dual Plan hash value: 1388734953 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | TABLE ACCESS STORAGE FULL| ORDERS_TAB | 1 | 13 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- |
Lol, it is really using exadata storage.
Anyway, I like that they are implementing the BOOLEAN datatype and this is also a positive shoutout to #WIT. They chose female as argument, which is in my opinion a nice thing.
Who will use this? I don’t know. Possible people interested in statistics, but it’s cool to see new features developed. I’m curious if it’s there in the cloud too.
As always, questions, remarks? find me on twitter @vanpupi
Update 2-APR-2018
For those who didn’t realize this was posted on 1st of April. This idea came from some Oracle Community buddies on the post-UKOUG_TECH17 trip. And what remains true all the year is how this community is full of awesome people. And special thanks to Connor who added great ideas as well on asktom 🙂