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:
BEGIN -- Disable the web interface DBMS_XDB.SETHTTPPORT(0); -- Disable FTP just in case DBMS_XDB.SETFTPPORT(0); END;
There are similarly named functions for getting the current ports as well.
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.
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;
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!
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!