SQL monitor and timestamp bind data

Recently I was investigating on performance issues with a java application. Due to object relational mapping this application generated a lot of varying sql statements which often had plan issues.
I wanted to replay some of these statements with variatons to find a generic solution to this.
The SQL monitor reports however had a lot of bind variables of the timestamp type. In the reports these were represented in a value like this: 7874051A0B1F01
At first I just guessed the values but at some time I really needed to know if they were querying a week or a year of data.

I decided to reverse engineer the format with a testscript which generated SQL monitor reports using a range of timestamps. From the reports I digested the format and made a SQL query to translate them:

select to_timestamp
 (  to_char (to_number (substr (:input, 1, 2), 'xx') - 100, 'fm00')
 || to_char (to_number (substr (:input, 3, 2), 'xx') - 100, 'fm00')
 || to_char (to_number (substr (:input, 5, 2), 'xx'), 'fm00')
 || to_char (to_number (substr (:input, 7, 2), 'xx'), 'fm00')
 || to_char (to_number (substr (:input, 9, 2), 'xx') - 1, 'fm00')
 || to_char (to_number (substr (:input, 11, 2), 'xx') - 1, 'fm00')
 || to_char (to_number (substr (:input, 13, 2), 'xx') - 1, 'fm00')
 || to_char (nvl (to_number (substr (:input, 15, 8), 'xxxxxxxx'), 0), 'fm000000000')
 , 'yyyymmddhh24missff'
 )
 from   dual

The above timestamp representation translates to 26-5-2016 10:30:00,000000000.
For timestamps with timezones or ones with a different precision the formula will probably be similar.

Verder lezen…SQL monitor and timestamp bind data

Buffer sort madness

Last week I was called in on a performance issue regarding a query on a datawarehouse that took about 4 hours. When looking at the execution plan in the excellent sql monitor I noticed a big full table scan yielding a whopping 125 million rows. Before I could turn around to ask the application team … Verder lezen…

EDI – de ultieme integratie

Ik ben inmiddels al ruim 13 jaar bezig met EDI. Dat is best lang. Zo lang dat ik bij veel collega’s zelfs bekend sta als “Mister EDI”. En het is ook alweer zo’n 9 jaar geleden dat ik voor het eerst te maken kreeg met het vakgebied van Integration en Middleware. In mijn geval betreft … Verder lezen…