Montag, 30. April 2018

Tracing the Database Configuration Assistant

I am struggling with DBCA right now so a little tracing is advised.
Note Tracing the Database Configuration Assistant (DBCA) (Doc ID 188134.1) gives a nice suggestion to add these parameters:
-DTRACING.ENABLED=true -DTRACING.LEVEL=2
but it's no elegant solution to edit the shell script in $ORACLE_HOME/bin/dbca

I prefer my new swiss knife for java parameters. A simple environment variable:
_JAVA_OPTIONS= -DTRACING.ENABLED=true -DTRACING.LEVEL=3
This is very useful for any java program. As Oracle has many such programs, there is often use for _JAVA_OPTIONS

Sonntag, 1. April 2018

more fun with ages


On-call duties sometimes leads to interesting results.
I have to observe a restore running (or to be more precise wait for a tape library to find the right tapes and put them into drives.
This gives me time to catch up my news aggregator. And some time to play with interesting news.
One interesting post I stumbled across is

Franck Pachos After IoT, IoP makes its way to the database
There he mentions a new, yet undocumented function to_dog_year().
As I had some fun with ages in general, this promises to be even more entertainment!

If I had a dog with equivalent age then I'm old, what's it's birthday?
It's simple now:
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

select BX_YEAR_CALC.ADDITION(sysdate,   
         -1 * to_dog_year(BX_YEAR_CALC.DIFFERENCE(:MY_BIRTHDAY))) 
                                                as dogs_birthdate 
from dual;

DOGS_BIRTHDATE     
-------------------
2012-09-26 03:27:18

(I don't care about breed or this other parameter as it's only a hypothetical dog in my case).


As the library is still shuffling tapes I was guessing about the origin of this function.
Francks idea of Internet of Pets makes some sense to me, but such a very specific function?
I know stories about Oracle providing special versions of it's database to important customers. But that was back in something like Version 5 or 6 (long before my time) and more dedicated to performance issues of any kind. But maybe this is the case here also.
IF this important customer is doing any business with a huge amount of dogs and needs to calculate their "Age" all over the application, it might make sense to ask Oracle to code it in the core of the database. To have custom functions is very uncommon for me, but maybe we will see the next big thing soon, and Oracle tries to gain it's market share there?



Restore is done, but recovery takes it's time! I'm sure archivelogs are spread cross many tapes. This gave me time to do some (very simple!) performance tests with to_dog_year. It seems to be very easy to calculate  the "age" of a puppy, but this gets more expensive rapidly. At the input of 2 (or somewhere around - I increased input by 0.5) the complexity seems to drop and the effort increases only slightly.
It would be interesting to see the real formula, if it will ever get published, but at the input of 2 it seems to flip from a "calculation for young dogs" to "calculation for adult dogs".
If it's really for something like tracking dogs, I hope they (whoever they are) have a good capacity planning: I assume customers with young dogs will be more interested to join the service (whatever it will be) which makes the computation requirements slightly higher than average.

Anyhow, future will tell.
As the restore finished, I'll continue the night with better things to do...


Update:
If you are interested in more details about to_dog_year(), before you invest any effort there, please check date and time when this post was published.
I hope you liked it as much as we did.