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…