Histogram endpoint date values to ‘human readable’ date format

Let’s get an endpoint_value for a hight-balanced histogram which represents a date:

select table_name, column_name, endpoint_number, endpoint_value, endpoint_actual_value
from dba_tab_histograms where table_name='BSLN_BASELINES' and column_name='LAST_COMPUTE_DATE'
order by endpoint_number;

Endpoint values for dates will be shown as followed: ‘2457594.23701157’.

The number in front of the dot is the day’s since ’01 JAN -4712′, this is retrievable with the Julian format element ‘J’:

select to_char(to_timestamp('2457594', 'J'), 'DD MON YYYY') endpoint_actual_value from dual; -- 24 JUL 2016
select to_char(to_timestamp('1', 'J'), 'DD MON SYYYY') endpoint_actual_value from dual; -- 01 JAN -4712
select to_char(to_timestamp('5373484', 'J'), 'DD MON YYYY') endpoint_actual_value from dual; -- 31 DEC 9999

The number after the dot is the faction of the day: .5 is noon, 0.25 is six ‘o clock in the morning:

select to_char(trunc(sysdate) + 0.25, 'HH24:MI:SS') endpoint_actual_value from dual; -- 06:00:00
select to_char(trunc(sysdate) + 0.23701157, 'HH24:MI:SS') endpoint_actual_value from dual; -- 05:41:18
select to_char(trunc(sysdate) + 0.87305213, 'HH24:MI:SS') endpoint_actual_value from dual; -- 20:57:12

Continue reading

Measuring MREADTIM system statistic, alternative way

[Why this post: GATHER_SYSTEM_STATS does not gather MREADTIM information from Direct Path Reads]

Oracle can be tuned in a lot of parts and places. One of these is when Oracle is going to choose between reading an index or doing a full table scan.

In this blog I’m not going into depth about all this, but one of the ‘parameters’ here is setting the MREADTIM system statistic to a ‘real life’ value. This value will tell Oracle how fast reading multiple blocks from disk is with all the overhead in between. How many multiple blocks is, is defined by the multi block read count (MBRC) setting. Together with SREADTIM, IOSEEKTIM and MBRC this will have influence in the execution path Oracle will choose.

Continue reading