Archive for March, 2014

Convert from milliseconds since epoch to Oracle timestamp

Today I needed to convert from numbers in a log file originally generated using System.currentTimeMillis() in Java to Oracle timestamps and I wanted to do it with PL/SQL, without involving Java in the database. I love Java, but inside Oracle? No thanks.

Surely this is simple and surely the Internet abounds with examples? No and no. It is easy to create a date, but date columns loose precision. I needed to preserve the milliseconds. There are many examples for getting the number of milliseconds since the epoch from an Oracle timestamp, but not for creating a timestamp from a millisecond value.

After some thinking I ended up with the following code:

create or replace function millis_to_timestamp(p_millis in number)
  return timestamp is
  v_datePart date;
  v_millisPart number;
  v_datePart := to_date('19700101', 'YYYYMMDD') + (p_millis / 86400000);
  v_millisPart := mod(p_millis, 1000);
  return to_timestamp(to_char(v_datePart, 'YYYYMMDDHH24MISS') ||
         substr(to_char(v_millisPart + 1000), 2, 3), 'YYYYMMDDHH24MISSFF');

Not that pretty, but it works! Share and enjoy…

Categories: Database, Oracle

Stop Oracle XE from listening on port 8080

A default installation of Oracle Express Edition 11g has a web interface on port 8080 and listens for database connections on port 1521. When the database is used in a development environment this is often less than ideal, as port 8080 tends to clash with other web servers. To disable APEX and get rid of the web interface, set the port number to 0. Connect as a DBA user and run:

  -- Disable the web interface
  -- Disable FTP just in case

There are similarly named functions for getting the current ports as well.

Categories: Database, Oracle

Oracle performance tuning scripts

As a developer I rarely get access to the graphical consoles for Oracle. The DBA:s tend to keep them locked down. It is usually easier to get access to the built-in performance views. Over the years I have gathered a few scripts that help me diagnose performance issues. They are nothing fancy, I’m posting them partly for my own benefit as this makes them easy to find when I need them.

I have used the scripts for a long time, so I don’t remember their origin. A likely source is Tom Kyte’s excellent Effective Oracle by Design, a must read for anyone into Oracle performance tuning.

High-level view

select  wait_class, total_waits,
  round(100 * (total_waits / sum_waits),2) pct_waits,
  time_waited, round(100 * (time_waited / sum_time),2) pct_time
from (select wait_class, total_waits, time_waited
        from v$system_wait_class where wait_class != 'idle'),
     (select sum(total_waits) sum_waits, sum(time_waited) sum_time
        from v$system_wait_class where wait_class != 'idle')
  order by 5 desc;

Top wait events

select h.event "wait event",
       sum(h.wait_time + h.time_waited) "total wait time"
  from v$active_session_history h, v$event_name e
  where h.sample_time between sysdate - 1/24 and sysdate
  and h.event_id = e.event_id
  and e.wait_class  'idle'
  group by h.event
  order by 2 desc;

Table scans

select to_char(sn.begin_interval_time,'dd mon hh24:mi:ss') start_date,
       to_char(sn.end_interval_time,'dd mon hh24:mi:ss') end_date,
       newmem.value-oldmem.value fts
  from dba_hist_sysstat oldmem,
       dba_hist_sysstat newmem,
       dba_hist_snapshot sn
  where sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
    and newmem.snap_id = sn.snap_id
    and oldmem.snap_id = sn.snap_id-1
    and oldmem.stat_name = 'table scans (long tables)'
    and newmem.stat_name = 'table scans (long tables)';


select h.user_id, u.username, sql.sql_text,
       sum(h.wait_time + h.time_waited) "total wait time"
  from v$active_session_history h,
       v$sqlarea sql,
       dba_users u,
       v$event_name e
  where h.sample_time between sysdate - 1/24 and sysdate
    and h.sql_id = sql.sql_id
    and h.user_id = u.user_id
    and h.sql_id is not null
    and e.event_id = h.event_id
    and e.wait_class  'idle'
   group by h.user_id,sql.sql_text, u.username order by 4 desc;

This is usually enough to point me in the right direction. It is even easier with the graphical user interface, but SQL always works!

Categories: Database, Oracle

Java 8 is here – learn how to use the new stuff!

Java 8 has finally been released! Time to try out all the new things. Most of it has been covered before (the previews have been out for some time and there have been talks at JavaOne, JFokus and many other conferences), but personally I found some gold nuggets in the newly released book Java 8 Lambdas: Pragmatic Functional Programming. It covers the basics, but it also includes valuable advice on debugging and unit testing lambda functions and how to use them effectively. Recommended!

Categories: Java