Search

Top 60 Oracle Blogs

Recent comments

March 2009

New presentation: Simulating failures for testing and diagnostic practice

In this entertaining presentation, Jeremiah Wilton demonstrates creative ways to induce Oracle failures with the objective of learning how to detect, assess and diagnose problems.  Some of the self-induced failures are quite amusing, and will allow the reader to have some fun with their DBA friends.

Check out the presentation on our whitepaper page.

Hotsos 2009

This was my sixth Hotsos Symposium as an attendee, my second as a speaker, and this gathering has become something of a spring time ritual for me. Others may look at the temperature, the dogwoods or the daffodils, but Hotsos is how I know spring is here, even if it did snow in Texas last year.I can divide my life as a DBA into three distinct phases:Phase one: I was given a server (DEC Alpha 2100

J2EE and traditional MVC (Part 1)

A short note to new visitors: this blog documents my vision on how to build database web applications. Normally I do this by presenting a two hour presentation know as "A Database Centric Approach to J2EE Application Development". First given at Oracle Openworld 2002. You can find the original paper here (it's the one titled "A First Jdeveloper Project"). Since the Mayday Miracle gathering in

Generating Histograms

Today I have been trying to generate a histogram for a table column as part of a larger statistics project. In fact it is quite a straightforward process although some of the syntax is a bit tricky.

The table I am working with is called GP.CAR and contains a list of all cars that have raced in Formula 1 since 1961. I am trying to build a frequency histogram based on the DRIVER_KEY column which is a CHAR(4). The histogram will contain the following data:

MSCH 91
APRO 51
ASEN 41
NMAN 31
JSTE 27
NLAU 25
JCLA 25
NPIQ 23
FALO 22
DHIL 22
MHAK 20

The histogram can be built using the following code:

DECLARE
l_statrec dbms_stats.statrec;
l_charvals dbms_stats.chararray;
l_bkvals dbms_stats.numarray;
BEGIN
NULL;
l_charvals := dbms_stats.chararray ();
l_charvals.extend (11);

l_bkvals := dbms_stats.numarray ();
l_bkvals.extend (11);

l_charvals(1) := 'MSCH'; l_bkvals(1) := 91;
l_charvals(2) := 'APRO'; l_bkvals(2) := 51;
l_charvals(3) := 'ASEN'; l_bkvals(3) := 41;
l_charvals(4) := 'NMAN'; l_bkvals(4) := 31;
l_charvals(5) := 'JSTE'; l_bkvals(5) := 27;
l_charvals(6) := 'NLAU'; l_bkvals(6) := 25;
l_charvals(7) := 'JCLA'; l_bkvals(7) := 25;
l_charvals(8) := 'NPIQ'; l_bkvals(8) := 23;
l_charvals(9) := 'FALO'; l_bkvals(9) := 22;
l_charvals(10) := 'DHIL'; l_bkvals(10) := 22;
l_charvals(11) := 'MHAK'; l_bkvals(11) := 20;

DBMS_OUTPUT.NEW_LINE

This week I have been writing a lengthy SQL*Plus script to extract details about object statistics from the data dictionary. Some of the join statements have not been performing well, but I did not have time to investigate them so I rewrote them using PL/SQL cursors and outputting the results using DBMS_OUTPUT.

The problem I have always had with DBMS_OUTPUT is outputting blank lines. For example:

SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line ('Line One');
dbms_output.new_line;
dbms_output.put_line ('Line Two');
END;
/

returns the following output

Line One
Line Two

I have also tried using PUT_LINE with a space character. For example:

SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line ('Line One');
dbms_output.put_line (' ');
dbms_output.put_line ('Line Two');
END;
/

which returns the same output:

Line One
Line Two

In other words I cannot output a blank line to improve readability

For a long time (since Oracle 6.0) I have been aware of the CHR() built-in function and this offers one solution:

SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line ('Line One');
dbms_output.put_line (CHR(10));
dbms_output.put_line ('Line Two');
END;
/

which returns the required output:

Line One
 
Line Two

However, a much more elegant solution is available, at least in Oracle 10.2, possibly in earlier versions which modifies the SET SERVEROUTPUT ON statement.

SET SERVEROUTPUT ON FORMAT WRAPPED
BEGIN
dbms_output.put_line ('Line One');
dbms_output.new_line;

Oracle 11g: Reading alert log via SQL

Oracle has done some major improvements in the diagnosability infrastructure in version 11g. Here’s one little detail.

Before Oracle 11g it is possible to access the alert log via SQL using an external table or a pipelined function which in turn uses utl_file.

After reading the text you need to parse it to extract the information you need from there.

Starting from 11g Oracle does all this work for you. There is a fixed table X$DBGALERTEXT, when you query it, Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows:

Oracle 11g: Reading alert log via SQL

Oracle has done some major improvements in the diagnosability infrastructure in version 11g. Here’s one little detail.

Before Oracle 11g it is possible to access the alert log via SQL using an external table or a pipelined function which in turn uses utl_file.

After reading the text you need to parse it to extract the information you need from there.

Starting from 11g Oracle does all this work for you. There is a fixed table X$DBGALERTEXT, when you query it, Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows:

Configuring VMWare on a Laptop

I have been using VMWare Server Linux VMs on my Windows XP laptop for over two years; with both single instance and RAC configurations across numerous VMs. Normally my laptop is connected to my home network (192.168.1.x) so I have been assigning VMs with IP addresses in the same subnet and configuring bridged networks. This worked OK while I was at home but I always had a problem when I was on the road and I could not find a suitable network connection. If all my adapters were reporting "media disconnected" I could not get a network connection between my host and the VMs.

I finally solved this problem (with help from Simon Haslam of Verition) last week. I have installed a Microsoft Loopback Adapter and configured a network address on this. The network address works even if the laptop is not connected to any networks.

The Microsoft Loopback Adapter can be installed using Control Panel -> Add Hardware which launches the Add Hardware Wizard

On the first page answer select "Yes, I have already connected the hardware"

On the next page select "Add a new hardware device"

On the next page select "Install the hardware that I manually select from a list (Advanced)"

On the next page select "Network adapters"

On the next page in the Manufacturer drop down select "Microsoft" and then in the Network Adapter drop down select "Microsoft Loopback Adapter"

The Loopback Adapter will be installed.

You can then specify an IP address for the loopback adapter using Settings -> Network Connections. I used 192.168.5.100 for my host; 192.168.5.0 is a new subnet in my network.

Another LatchProfX use case

Riyaj Shamsudeen wrote an excellent article about systematic latch contention troubleshooting.
Especially if the latch contention problem is ongoing, looking into system wide stats (like v$latch.sleep columns) is not the best idea in busy systems. This may sometimes lead you to fixing the wrong problem.
This is because sometimes the latch contention is not caused by some system wide inefficiency but rather by one or few sessions.
The right approach would be to measure the following things:

Another LatchProfX use case

Riyaj Shamsudeen wrote an excellent article about systematic latch contention troubleshooting.
Especially if the latch contention problem is ongoing, looking into system wide stats (like v$latch.sleep columns) is not the best idea in busy systems. This may sometimes lead you to fixing the wrong problem.
This is because sometimes the latch contention is not caused by some system wide inefficiency but rather by one or few sessions.
The right approach would be to measure the following things: