Search

Top 60 Oracle Blogs

Recent comments

March 2009

I’m an Oracle ACE Director now :)

Many people have asked me that how come I’m not an Oracle ACE yet. From this week I am an Oracle ACE Director.
Here’s a link to my profile.
Its pretty big honor to be recognized by Oracle Corp at such high level.
I started working with Oracle software in 1997 and got really interested in that stuff after realizing how powerful the database was and how complex some internal details could be.

I’m an Oracle ACE Director now :)

Many people have asked me that how come I’m not an Oracle ACE yet. From this week I am an Oracle ACE Director.
Here’s a link to my profile.
Its pretty big honor to be recognized by Oracle Corp at such high level.
I started working with Oracle software in 1997 and got really interested in that stuff after realizing how powerful the database was and how complex some internal details could be.

JEE and traditional MVC (Part 2)

In the previous post I gave a high level introduction into the MVC design pattern. This pattern classifies all code that you write to implement a database web application, into three classes:Model codeView codeControl codeI also showed that within the JEE architecture code can be deployed to many tiers. In this post I will talk about alternative MVC approaches by looking at the amount of

Optimizer partition oddities, part 2: List partitioning

Back to part 1

Some time ago on the OTN forum the following table layout was part of a discussion regarding performance issues and it revealed an interesting anomaly regarding list partition pruning:

If you're using list partitioning with partitions that use multiple values that map to a single list partition then the optimizer obviously uses a questionable approach when you're using multiple values on the partition key to prune to a single partition.

Consider the following table layout:

CREATE TABLE XYZ
(
TICKER VARCHAR2(22 BYTE) NOT NULL,
EXCH_CODE VARCHAR2(25 BYTE) NOT NULL,
ID_ISIN VARCHAR2(12 BYTE),
HIGH_52WEEK NUMBER(28,10),
LOW_52WEEK NUMBER(28,10),
PX_OPEN NUMBER(28,10),
PX_HIGH NUMBER(28,10),
BLOOMBERG_FILE_SOURCE VARCHAR2(100 BYTE),
LATEST_VERSION_FLAG CHAR(1 BYTE)
)
PARTITION BY LIST (EXCH_CODE)
(
PARTITION BBO_ASIA VALUES ('SL','IS','SP','JF','JN','PK','KP','VM','JS','IN','TB','KQ','JP','NV','JJ','MK','HK','IJ','JT','TT','PA','CS','JX',
'IB','AU','FS','VN','NZ','KS','PM','CH','BD','JQ','VH','CG','JO')
,
PARTITION BBO_NAMR VALUES ('UO','US','UN','PQ','TR','UD','UP','TX','UL','UB','UU','UX','UT','TN','UQ','UR','UW','UV','TA','CT','CV','UC','CJ','UA','UM','CN','UF','CF')
,
PARTITION BBO_LAMR VALUES ('AR','BS','AC','CR','EG','EK','VB','BN','EQ','PE','AF','CX','KY','CC','MM','BM','TP','BV','BH','UY','BZ','ED','VC','VS','BO','CI','CB','PP','BA','JA','CE')
,
PARTITION BBO_EURO VALUES (DEFAULT)
);

I'm now going to populate that table using this sample data:

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: