Carl's Oracle

Carl's thougts about the Oracle Database Server

2006/4/29

Now you find my new wordpress Blog at ...

@ 02:37 PM (105 months, 13 days ago)

www.orcasoracle.org

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

2006/3/17

Migrating my Blog to Wordpress ...

@ 06:56 PM (106 months, 25 days ago)

Hi,

Last week i decided to move this blog to a wordpress www.wordpress.com 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

Greetings

Karl

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 (106 months, 26 days ago)

Hi,

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

http://www.thedailywtf.com/forums/64256/ShowPost.aspx

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?

Greetings

Karl

 

 

2006/2/7

Index and NULLs - Much do about nothing ...

@ 04:23 PM (108 months, 4 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)

2006/1/14

CBO - Cardinality oh Cardinaltiy!

@ 04:28 PM (108 months, 28 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)

2006/1/6

PL/SQL - flexible deployment of database objects

@ 10:29 AM (109 months, 6 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)

2005/12/21

Execution plan dependent Query Results - Sorting

@ 05:33 PM (109 months, 21 days ago)

Hi,
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:

SELECT
 Company.Legalname,
 Company.*,
 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 */
         Iq.*,
         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)

2005/12/9

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

@ 03:54 PM (110 months, 4 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)

2005/11/18

OPP2005 - first PL/SQL conference a big success!

@ 01:35 AM (110 months, 25 days ago)
Hi,

PL/SQL becomes mature (specially in Oracle 10.2!)!
Read more about conference papers at this link : http://oracleplsqlprogramming.com/opp2005_agenda.php
Regards
Carl



2005/11/16

Cost based Oracle has reached Germany!

@ 02:02 PM (110 months, 27 days ago)
Hi,
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)

2005/11/9

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

@ 01:57 PM (111 months, 4 days ago)
Hi,
It was time to upgrade my Linux based Oracle database from 9.2.0.4 to 9.2.0.7. 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)

2005/11/1

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

@ 11:12 AM (111 months, 12 days ago)

Hello,

As i heard about the availability of the Patchset 6 (9.2.0.7) of the Oracle 9.2 Database Server i was very surprised. I thought Patchset 5/9.2.0.6 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/9.2.0.4 ;-));

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

SQL> SELECT COUNT(*) FROM Buglist;
COUNT(*)
----------
10256

SQL>

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;

COUNT(*)   FIXEDINRELEASE
---------- ----------------

1347       9.2.0.2
1053       9.2.0.3
1554       9.2.0.4
2547       9.2.0.5
2188       9.2.0.6
1567       9.2.0.7

6 rows selected

SQL>

http://img328.imageshack.us/img328/6498/bugstotalfixedinrelease7ts.jpg


The number of bugs was increasing and had its peak level at Patchset 9.2.0.5/9.2.0.6.

 46% of Bugs were fixed in 9.2.0.5/9.2.0.6 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)

2005/10/29

Oracle 10G : Pioneering innovations with Perfomancemonitoring

@ 05:44 AM (111 months, 15 days ago)

hi,

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 www.oraperf.com

 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 (www.hotsos.com/)
  • 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)

  • V$ACTIVE_SESSION_HISTORY
  • DBA_HIST_ACTIVE_SESS_HISTORY

 

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!

Carl

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

Automatic Performance Diagnosis with Oracle Database 10g Release 2

Carl

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

Regards
Carl

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

@ 05:09 AM (111 months, 15 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 :

SET SERVEROUTPUT ON

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

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

2005/10/28

Welcome to this new blog

@ 11:48 AM (111 months, 16 days ago)
Hi ervery body.
Used to have a blog at http://www.orcasoracle.blogspot.com/
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)