March 2009

If you have the need for plan stability - that is telling the database to use a particular execution plan no matter what the optimizer thinks otherwise - then you might be in the situation that the "good" execution plan is already available in the shared pool or in the AWR, so it would be handy if you could simply tell Oracle to use that particular execution plan to create a Stored Outline.

Note that in 11g this is all possible using the new SQL Plan Management framework (SPM), but that is not available in 10g, so we need to think differently.

In 10g the DBMS_OUTLN package has been enhanced with the CREATE_OUTLINE procedure to create an outline from an existing child cursor in the shared pool.

Please note that in releases prior to 10.2.0.4 there was a severe bug that caused your session to crash when using DBMS_OUTLN.CREATE_OUTLINE (Bug 5454975 which has been fixed in 10.2.0.4). The workaround is to enable the creation of stored outlines by issuing "alter session set create_stored_outlines = true;" before using DBMS_OUTLN.CREATE_OUTLINE. For more information see the Metalink Notes 463288.1 and 445126.1.

Note that from 10g on the hints required to create an outline are stored as part of the plan table in the OTHER_XML column as part of the XML detail information.

You can use the ADVANCED or OUTLINE option of the DBMS_XPLAN.DISPLAY* functions to display that OUTLINE information. For more information see e.g. here.

So let's try DBMS_OUTLN.CREATE_OUTLINE in 10.2.0.4:

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

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

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:

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;