Carl's Oracle

Carl's thougts about the Oracle Database Server


Now you find my new wordpress Blog at ...

@ 02:37 PM (108 months, 14 days ago)

Read the rest of this entry ... (1 words left)


Migrating my Blog to Wordpress ...

@ 06:56 PM (109 months, 27 days ago)


Last week i decided to move this blog to a wordpress based blog.

Wordpress supports static pages, categories and has a lot of wunderfull themes and functionality.

I reorganize the page. My target is to concentrate the content on Basics of Tuning, CBO, ...;

The new site is under construction and will be available as soon i have the right structure

and when i am finished with manually migration of content



PS.:Bloghi was a good platform -  a lot of reliable functionality and a very good editor.


We Don't Need No Stinkin' Database ...

@ 06:58 AM (109 months, 28 days ago)


this is not the first story about avoiding the use of RDBMS with all it's consequences :

My question really is why Databases have a real bad reputation in the development community?

  • is it the old technology?
  • is it the experience meeting some strange database guys?
  • is it the database less education in school/university?






Index and NULLs - Much do about nothing ...

@ 04:23 PM (111 months, 5 days ago)

I was inspired by Tom Kytes Blog Entries about NULLs in indexes. Only if all Columns of an index are set to NULL they are not part of the index;

Read the rest of this entry ... (546 words left)


CBO - Cardinality oh Cardinaltiy!

@ 04:28 PM (111 months, 29 days ago)

Hi, Applications do not live as data islands. The more an application integrates data of other applications of a company the better is. Even we live in the Year 2006 the most common way to import data from other systems is the flat file. Via SQL*LOADER the data is loaded into an Import table which is a table presentation of the flat file. Then the Processing of data starts to apply the imported data to the applications tables.

Read the rest of this entry ... (875 words left)


PL/SQL - flexible deployment of database objects

@ 10:29 AM (112 months, 8 days ago)

Hi, unfortunately in the current project the customer has on development site databases on Windows Server but on production site databases on AIX based UNIX servers. Usually this is no problem when the Oracle versions are the same. Some Oracle Database objects are still OS dependent like the directory objects. Oracle Directory objects are an encapsulation of the real directories in the file system of the OS. Grants on Oracle directory objects controls the user access.

Read the rest of this entry ... (459 words left)


Execution plan dependent Query Results - Sorting

@ 05:33 PM (112 months, 23 days ago)

do you think execution plans are only the compiled access plan on which data will retrieved hopefully with low costs and good response time? Execution plans can also have influence on the result set of your SQL! Happended day ago in my current project. A milestone was put on a separate database. After first tests we detected some lists were not sorted. We support paged lists. On database side a window of rows sorted by a column will be retrieved - enriched with country coded texts.
We were surprised because we did not expect from the sorted window to get unsorted output. Here the SQL:

 V1.VALUE AS Industry,
 V2.VALUE AS Industrysubtype,
 V3.VALUE AS Companystatetext,
 V4.VALUE AS Bgsdescription,
 V5.VALUE AS Legalform,
 V6.VALUE AS Region,
 V7.VALUE AS Countrytext
  FROM Isis.Industrytext V1,
       Isis.Industrysubtypetext V2,
       Isis.Companystatetext V3,
       Isis.Bgscodetext V4,
       Isis.Legalformtext V5,
       Isis.Regiontext V6,
       Isis.Countrytext V7,
       (SELECT /*+ FIRST_ROWS */
         Rownum AS z_r_n
          FROM (SELECT Company.*
                  FROM (SELECT *
                          FROM Isis.Company
                         WHERE Company.Isis_Prefered = 1) Company
                 WHERE Company.Companystate <> 5
                   AND Upper(TRIM(Company.City)) LIKE Upper(TRIM('BERLIN'))
ORDER BY Company.Legalname ASC) Iq
         WHERE Rownum <= 15) Company
 WHERE z_r_n >= 1
   AND (V1.Key(+) = Company.Industrykey AND V1.Locale(+) = 'en-GB' AND
       V2.Key(+) = Company.Industrysubtypekey AND V2.Locale(+) = 'en-GB' AND
       V3.Key(+) = Company.Companystate AND V3.Locale(+) = 'en-GB' AND
       V4.Key(+) = Company.Bgscode AND V4.Locale(+) = 'en-GB' AND
       V5.Key(+) = Company.Legalformkey AND V5.Locale(+) = 'en-GB' AND
       V6.Key(+) = Company.Regionkey AND V6.Locale(+) = 'en-GB' AND
       V7.Key(+) = Company.Countryisocode AND V7.Locale(+) = 'en-GB')

•    And its resulting Execution plan :

Read the rest of this entry ... (970 words left)


GUIDs or - Query Rewrite - Why my Materialized View is not used

@ 03:54 PM (113 months, 5 days ago)
At the current project developers use to identify the C# objects with the almost unique GUID. No sequences are used at all. As we know objects cannot live a longer period without persistence - the objects are not only referenced by GUID in memory but also in the database. With GUID an object can be referenced unique even between different databases. What's  exact is a GUID?

Read the rest of this entry ... (1843 words left)


OPP2005 - first PL/SQL conference a big success!

@ 01:35 AM (113 months, 27 days ago)

PL/SQL becomes mature (specially in Oracle 10.2!)!
Read more about conference papers at this link :


Cost based Oracle has reached Germany!

@ 02:02 PM (113 months, 28 days ago)
today when I came home there was a package from amazon – it contained the book Cost Based Oracle Fundamentals which I ordered in advance. I just could not wait to open the book. In 15 Minutes I would have to leave with my wife to go shopping at IKEA. So I had only a few minutes for the first review.

Read the rest of this entry ... (187 words left)


Patching Oracle to on SuSE 9.2 Professional raises - undefined reference to `dl_iterate_phdr@GLIBC_2.2.4'

@ 01:57 PM (114 months, 5 days ago)
It was time to upgrade my Linux based Oracle database from to A thing which is easy going – I thought! Linux was SusE 9.2 Professional with 2.6.x kernel and gcc 3.3.4. After the oracle installer finished copying the files to Oracle Home the linking of the executable started. Soon an error raised which I could not solve:

Read the rest of this entry ... (661 words left)


Oracles Patchset 6 ( Database Server arrived : time now for a Summary of all 9.2 Patchsets

@ 11:12 AM (114 months, 14 days ago)


As i heard about the availability of the Patchset 6 ( of the Oracle 9.2 Database Server i was very surprised. I thought Patchset 5/ would be the last Patchset and ... that all errors would have been finally patched ;-).

For Analysis i scanned the bugs HTML document, pasted and prepared the bugs info with an editor and transformed it with awk. Now it had the right format for excel and then what else did you expect - I stored all the data in an 9.2 database (Patchset 3/ ;-));

With the data in the database you could answer a lot of questions. I reduced that to two questions:

  • How many errors have been patched with each Patchset?
  • Which are the top 20 Components (of 139) have the largest amount of errors

The Total Number of fixed Bugs for the Oracle 9.2 Database Server



10256 Bugs have been fixed with 6 Patchsets - about 1706 Bugs/PatchSet.

How many Bugs have been fixed with each Patchset?

I would expects that the number of bugs would decrease with every new Patchset.


SQL> SELECT COUNT(*), bl.FixedInRelease FROM Buglist bl group by bl.FixedInRelease;

---------- ----------------


6 rows selected


The number of bugs was increasing and had its peak level at Patchset

 46% of Bugs were fixed in Patchsets!

An explanation could be: when a new version of Oracle is released a lot of customers do not switch to the new version very quickly. Some database releases are even not used at all. For example on the current customers site Oracle 10g will be allowed to be used at Main release 10.2 but not at Main release 10.1.

Read the rest of this entry ... (1257 words left)


Oracle 10G : Pioneering innovations with Perfomancemonitoring

@ 05:44 AM (114 months, 17 days ago)


I heard an amount about the new Features of the Oracle 10G server. A little bit set in my opinion is the Revolution of it's new Metering model concerning the performance monitoring.

Up to Oracle 9 there were decisively two (most famous ;-)) methods to get on to performance bottlenecks.

Statspack - goodies

  • Sampling of global database statistics
  • Top 5 Wait events
  • Instance statistics
  • Top SQL's with buffer gets
  • Top SQL's with disk reads
  • Top SQL's with executions
  • Reporting Script
  • HTML-Reporting with

 Statspack - not so good

  • missing connection of the wait events to the executed SQL and to the session
  • aggregated values mislead in interpretation of report (If for example 1000 users each is waiting 1 second on db file scattered read then this event would have 1000 seconds!)
  • Hard to decide to an unknown System what a normal load is and what not
  • No time lines - minimum interval 10 - 15 minutes

Extended trace event 10046 - goodies

  • Excellent wait time diagnostics on SQL/Session level
  • generate text report with tkprof
  • Easy to find the root cause of response time problem
  • Excellent timing tree of recursive SQL with hotsos viewer

Extended trace event 10046 - not so good

  • could be get difficult to find the right session to monitor
  • Not able to monitor reconnecting sessions
  • Overhead
  • Sophisticated report tools are very expensive (
  • Focus on only one session - difficult to trace programs which allocate more then one database session.

Both approaches do not support time lines to monitor - all metering data is aggregated. So you see not the timing of the real peaks in statement execution or some waiting gaps due to for example slow log switches.

Oracle 10G - the new approach


It seems the fact that Oracle closed the gap between the global approach and the session-oriented approach. The thousands of Html Pages of the new database control makes blind to see which really essential step of Oracle performance engineering innovative this is. Here the a subset of the description of the new features (copied from metalink note Note:276103.1).


New Time Model: In Oracle10g the database introduces a different way of store statistics and metrics regarding the time consumed by the different sessions connected. Without it, it would not be possible for the database to clearly identify the root cause of the problem in an acceptable timeframe. The following describes in detail the characteristics. Active Session History (ASH): One of the components of the AWR repository is the Active Session History (ASH) which collects every second samples of all the sessions that are in status "waiting" from v$sessions (inactive sessions are not captured).


This features allows to report a resource costing SQL query with it's wait events (look at V$ACTIVE_SESSION_HISTORY) and with it's time line and with it's effective execution plans and with it’s session context. The data is kept in the Oracle repository (DBA_HIST-Views)



All data is kept in tables and so easy to report. SQL's are identified by a unique SQL_ID and completely stored (CLOB) in the repository - to aggregate - to format - to export.

The new Database Control allows a graphical view on this data. And what looks so colorful and easy to maintain is the new oracle approach - it makes you even more independent from expensive performance tools of other non oracle providers!

It is clear that the new performance monitoring features are more then just an internalized statspack.
It's a revolution!
You cannot compare/combine these methods (do not mix the old with the new wine;-)).

Good job Oracle - Please continue!


UPDATE : found a document which goes into deep of Oracle 10G new tuning model.

Automatic Performance Diagnosis with Oracle Database 10g Release 2


UPDATE : Found an interesting link about The AWR :The Automatic Workload Repository


ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line

@ 05:09 AM (114 months, 17 days ago)

Do you like the full featured supplied PL/SQL Package DBMS_OUTPUT? It seems to be the package which ranks in top position with it's limitations ;-)

  • limited maximal output buffer
  • limited length of line (255)
  • limited support of output of data types like CLOB, XML, ...

Some points will be worked out with Oracle 10.2 - but why Oracle let its users wait so long?

At the current project I developed a source code generator generating a table api with special demand for an OR-Mapper. For some easy usage the code templates are printed to output, stored as CLOB. After 255 digits line buffer exceeds i made this experience :


VARCHAR2(1024) := '01234567890123456789012345678901234567890123456789';
-- constructing STRING WITH 255 chars
  l_Xl_Str :
= l_Str || Chr(10) || l_Str || Chr(10) || l_Str || Chr(10) ||
|| Chr(10) || l_Str || Chr(10);
('LENGTH : ' || Length(l_Xl_Str));

Read the rest of this entry ... (549 words left)


Welcome to this new blog

@ 11:48 AM (114 months, 18 days ago)
Hi ervery body.
Used to have a blog at
but the RTF-Editor i missed is implemented at bloghi in a better way;
and last but not least support for tables :

Read the rest of this entry ... (79 words left)