Leaving Blogger.com

Hi,
I am very thankfull to have the oportunity to use this free blog;
Now i move(d) to http://orcasoracle.bloghi.com/

The reason for is the much better RTF-mode Editor. I spent a lot of time to format my messages. I want spent more time to increase the quality of my postings ;-)

Will delete the blog at December 15th

With kind regards
Carl

A deeper look to constraints ...

Hi,
did it not happen to you too? You work with something a long time and because it's complex you make some suppositions which bases on experiences. But one fine day you touch really ground.
So today;-)

I am a developer DBA (what's this? A DBA which is not on production site - but on the site where the software is developed to support/convince the developers and to do the database side stuff).

Even though the Software works with an object model we let the database be the master of data. We have an Activity table which holds the activities of a company. The activities are linked together in a Double Linked List (previous/next Activity) implemented with foreign key
relationships.

I made following notice :

When I deleted all Activities of a company I got no
FOREIGN KEY Violation?

SQL>
delete from activity where company_id
='5F765BAACB194E56A43EBF35D5A6694A';
11
rows
deleted

Let us look closer :

When Oracle starts to delete one of these four activities a FOREING KEY is always violated.

Activity (1) Activity (2) Activity (3) Activity (4)
ID <- PREV_ID NEXT_ID -> ID (NULL)
NEXT_ID -> ID <- PREV_ID <- PREV_ID NULL <- PREV_ID NEXT_ID -> ID NEXT_ID -> NULL

REMARK from author : did not succeed inlcuding a HTML-Table - sorry :-(

As following SQL shows:

SQL>
delete from activity where company_id
='5F765BAACB194E56A43EBF35D5A6694A'
and rownum < 3;
delete
from activity where company_id ='5F765BAACB194E56A43EBF35D5A6694A'
and rownum < 3
ORA-02292:
Integritäts-Constraint (CRE.ACTIVITY_NEXT_FK) verletzt -
untergeordneter Datensatz gefunden

I could not bring these experiences together :

1. Delete of one linked Activity lead to FOREIGN KEY VIOLATION
2. Delete of the complete List -> NO FOREIGN KEY VIOLATION

Contacted Oracle support. - 30 minutes later

the oracle support analyst gave me the hint from Oracle Concepts
Guide:

This case also shows that constraint checking is deferred until the
complete execution of the statement. All rows are inserted first, then all rows are
checked for constraint violations. You can also defer the checking of constraints until the
end of the transaction.

Now I understood:

• First Oracle deletes the complete rowset and then it checks the Referential integrity.

• This means even in a DML operation a Constraint can be violated and no error is raised at the end of DML when the constraint then is not violated any more.

• Deleting the complete linked List ends normally – All activities have been deleted so no one exists to point to a deleted one.

So I learned this today and I am happy to be able to learn again interesting aspects or oracle and come into the deep! On development side we only implement a FOREIGN KEY ON DELETTE CASCADE Relationship referencing the Company table.

Carl

You can test this – here the scripts and a data generator.

The Table DDL :

-- Create table
create table
ACTIVITY
(
ID RAW(16) not null,

COMPANY_ID RAW(16) not null,
NEXT_ID RAW(16),

PREV_ID RAW(16)
)
;

-- Create/Recreate primary, unique and foreign key constraints
alter table ACTIVITY
add constraint ACTIVITY_PK
primary key (ID)
;
alter table ACTIVITY
add constraint
ACTIVITY_NEXT_FK foreign key (NEXT_ID)
references ACTIVITY
(ID);
alter table ACTIVITY
add constraint ACTIVITY_PREV_FK
foreign key (PREV_ID)
references ACTIVITY (ID);
--
Create/Recreate indexes
create index ACTIVITY_NEXT_FK on ACTIVITY
(NEXT_ID)
;
create index ACTIVITY_PREV_FK on ACTIVITY
(PREV_ID)
;


The generator Test-Script :
DECLARE

l_activity PLS_INTEGER :=0;
l_Company_id
RAW(16);
l_prev_act_id RAW(16);
l_Curr_act_id RAW(16);
BEGIN

FOR Company IN 1 .. 500
LOOP

l_Company_id := SYS_GUID();
l_prev_act_id :=
SYS_GUID();

INSERT INTO
Activity
(Id,
Company_Id,
Next_Id,
Prev_Id)
VALUES
(l_prev_act_id,
l_Company_Id,
NULL,
NULL);


FOR
ii IN 1 .. 10 LOOP

l_Curr_act_id := SYS_GUID();

INSERT
INTO
Activity
(Id,
Company_Id,
Next_Id,
Prev_Id)
VALUES
(l_Curr_act_id,
l_Company_Id,
NULL,
l_prev_act_id);

UPDATE
Activity Act
SET Act.Next_Id = l_Curr_act_id
WHERE Id =
l_prev_act_id;

l_prev_act_id := l_Curr_act_id;

END
LOOP;
END LOOP;
END;
/

Oracle 10G : Pioneering innovations with Perfomancemonitoring

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 Meetering model in the performance.
To Oracle 9 there were decisively two methods to get onto performance bottlenecks.

• statspack - goodies
  - sampling of global database statistics
  - Top 5 Waitevents
  - Intance statistics
  - Top SQL's with buffer gets
  - Top SQL's with disk reads
  - Top SQL's with executions
  - Reporting Script
  - HTML-Reporing with www.oraperf.com

• statspack - not so good
  - missing connection of the waitevents to the executed SQL and to the session
  - agregated values mislead in interpretation of report (If for example 1000 users each is waiting 1 second on db file scattered read then this event woukd have 1000 seconds!)
  - hard to decide to an unkown System what a normal load is and waht not
  - No time lines minimum interval 10 - 15 mins
  
  

• extended traceevent 10046 - goodies
  - excellent wait time diagnostics on SQL/Session level
  - generate text report with tkprof
  - Easy to find the root cause of responsetime problem
  
  

• extended traceevent 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 datbase session.
  

• both aproaches 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 logswitches.
  

Oracle 10G the new approach

It seems the fact that Oracle the gap between the global approach and
the session-oriented approach is closed. The thousands of Html Pages of database control makes blind to see which really for the Future of the Oracle performance engineering innovative 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 waitevents (look at V$ACTIVE_SESSION_HISTORY) with it's time line and with it's effective execution plans. 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 identifed by a unique SQL_ID and completly 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 perfomance tools providers!

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

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

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

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

Some points will be worked out with Oracle 10.2 - but why Oracle let it's 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 i printed the code templates to output, strored as CLOB.
After 255 digits execeded i made this experience :

SET SERVEROUTPUT ON
DECLARE
l_str VARCHAR2(1024) := '01234567890123456789012345678901234567890123456789' ;
l_xl_str VARCHAR2(1024);
BEGIN
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);

l_xl_str := l_xl_str || chr(10) || l_str;
  dbms_output.put_line('LENGTH : ' || length(l_xl_str));
  dbms_output.put_line(l_xl_str); 
END;
/

SQL>

LENGTH : 255
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789

LENGTH : 306

DECLARE
l_str VARCHAR2(1024) := '01234567890123456789012345678901234567890123456789' ;
l_xl_str VARCHAR2(1024);
BEGIN
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);

l_xl_str := l_xl_str || chr(10) || l_str;
dbms_output.put_line('LENGTH : ' || length(l_xl_str));
dbms_output.put_line(l_xl_str);
END;

ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 11

Immediatly i searched for a workaround on the web - even on Tom Kytes AskTom site i did not found something which really helped. So i had to find my own workaround. The Procedure can print maximal 32K sized string due to the PL/SQL limitation. But it could be overloaded with CLOB datatype parameter and the use of DBMS_LOB package inside of the procedure.

DECLARE
l_Str    VARCHAR2(1024) := '01234567890123456789012345678901234567890123456789';
l_Xl_Str VARCHAR2(1024);

PROCEDURE Put_Xl_Line(p_Str IN VARCHAR2) IS
 l_Length PLS_INTEGER := 0;
 l_Offset PLS_INTEGER := 0;
 l_Cr_Pos PLS_INTEGER := 0;
 l_Line   VARCHAR2(256);
BEGIN

 l_Length := Length(p_Str);
 l_Offset := 1;

 WHILE l_Offset <= l_Length LOOP
   l_Cr_Pos := Instr(Substr(p_Str, l_Offset, 255),  Chr(10));
   -- if last line without CR 
   IF (l_Cr_Pos = 0) THEN
     l_Cr_Pos := l_Length - l_Offset + 2;
   END IF;
   l_Line := Substr(p_Str,  l_Offset,  l_Cr_Pos - 1);
   Dbms_Output.Put_Line(l_Line);
   l_Offset := l_Offset + l_Cr_Pos;
 END LOOP;
END Put_Xl_Line;

BEGIN
 l_Xl_Str := l_Str || Chr(10) || l_Str || Chr(10) || l_Str || Chr(10) ||
             l_Str || Chr(10) || l_Str;
 Dbms_Output.Put_Line('LENGTH : ' || Length(l_Xl_Str));
  Put_Xl_Line(l_Xl_Str);

 l_Xl_Str := l_Xl_Str || Chr(10) || l_Str;
 Dbms_Output.Put_Line('LENGTH : ' || Length(l_Xl_Str));
  Put_Xl_Line(l_Xl_Str);
END;
/ 

SQL>

LENGTH : 254
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
LENGTH : 305
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789

PL/SQL procedure successfully completed

Wow it really worked!!
Carl ;-)

9207 Install does not patch PRO*Fortran nor PRO*Cobol

Oracle alterted Users which downloaded the PatchtSet 6 ( discussed that days before ;-))
to download and apply the PatchSet again!
The reason was that two components have not been updated with the PatchSet 6 when patching 9.2.0.7.0 on top of patch set 9.2.0.6:

• Pro*COBOL 1.8.77.6.0


• Pro*FORTRAN 1.8.77.6.0

Please search in metalink for further information

Carl    

Oracle releases Oracle Database 10g Developer's Release 2

Hi,

Oracle 10G R2 (10.2) was released for the Linux platform first. Now after a while Oracle released the second edition of it's 10G database as developer edition (some beta state) server on the technet for the windows platform.

What's new in Oracle 10G R2?

Personally i like the feature to develop .NET Stored Procedures in the Windows Edition.

Carl

Oracles Patchset 6 (9.2.0.7) for the 9.2 Database Server and some stats

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 would be the last Patchset and ... that all errors are 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 (Patchlevel 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 Pachset
  
  
• 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 Patchses about 1706/PatchSet.

How many Bugs have been fixed with each Patchset?

i would expects that the number of bugs decreases 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>

The number of bugs was increasing and had its peak level at Patchset 9.2.0.5/9.2.0.6. 46% of Bugs wer 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 companies 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 10.2 but not at main release 10.1.

Patchset 6 is the one with 3rd place ranking in number of bug fixes. This is really disappointing. I will compare the 9.2 stats with the 10.2 stats and we will see if oracle is producing more quality then in past.

Top 20 Oracle Server 9.2 Bug Components?

In which areas does 9.2 have highest concentration on fixed bugs? Oracle sepereates it's errors in Components. There are 139 Components listed.

From the top 20 is see following Components with heavy load.

• 2.1.29 Error May Occur (961)
  This sound a little bit strange. A Compenent which fixed bugs which were difficult to find because they may occure.
  

2.1.29 Error May Occur

The following table lists the Error May Occur bugs addressed in this patch set:
Fixed in Release Bug Number Description
9.2.0.7 2375764 Export fails with ORA-1455 if leaf_blocks or distinct_keys >= 2^31
9.2.0.7 2380561 "DRG-10817" from CATSEARCH with stemmed stopwords
9.2.0.7 2391697 ORA-235 doing backup with RMAN in nocatalog mode
9.2.0.7 2412991 ORA-12533 while resolving (duplicate) net services from names server
9.2.0.7 2672986 SET CONSTRAINT DEFERRED sometimes errors out with ORA-1031
9.2.0.7 2857330 ORA-1008 from Binds in CURSOR subqueries using WITH
9.2.0.7 2858082 False ORA-1652 in alert log when there is free space in RAC environment
9.2.0.7 2902077 Adding TEMP files with no size specified gives an error with ODM
9.2.0.7 2966778 Intermittent ORA-904 errors during heavy load
9.2.0.7 3026474 ORA-20000 from DBMS_STATS importing table stats with missing column

• 2.1.30 Internal Error May Occur (ORA-600) (791)
  This is the most famous section with ORA-00600 Errors. An ORA-00600 is an error in the oracle serve code which was not catched by any exception handler.
  

2.1.30 Internal Error May Occur (ORA-600)

The following table lists the Internal Error May Occur (ORA-600) bugs addressed in this patch set:
Fixed in Release Bug Number Description
9.2.0.7 2307997 OERI [kolaHashFind:hash table] from SELECT xmltypeview over a database link
9.2.0.7 2489130 OERI:1112 can occur while dumping PROCESSSTATE informatio
9.2.0.7 2707302 OERI[KSSRMP1] in PMON during shutdown
9.2.0.7 2736591 OCI-21500 [KGHALO2] with large value_sz values for OCIDefineByPos
9.2.0.7 2783333 OERI[voprsla1] can occur when running a query
9.2.0.7 2818800 OERI[kjzcwaitX] may occur in RAC
9.2.0.7 2847303 OERI:kkofkrMarkK with FIRST_ROWS_N from GROUP BY
9.2.0.7 2881246 Incorrect OERI[kcblibr_1] / OERI[kxttdropobj-1] can occur
9.2.0.7 2887209 UGA memory leak / OERI:729 with failed shared database link connection
9.2.0.7 2924135 False OERI:6074 can occur
9.2.0.7 2972961 OERI[ktcdso-1] during refresh on commit of materialized view
9.2.0.7 3032886 OERI[kjatioc:!ver] possible in RAC
9.2.0.7 3048658 OERI [ksires_1] [BADOPTIONS] in RAC
9.2.0.7 3116865 Various dumps can occur using XML clonenode

• 2.1.31 Process May Dump (ORA-7445) / Abend / Abort (604)
  An Abortion of a server process user process terminates the oracle session, an abortion of a backround process like DBWriter terminates the complete instance.
  

2.1.31 Process May Dump (ORA-7445) / Abend / Abort

The following table lists the Process May Dump (ORA-7445) / Abend / Abort bugs addressed in this patch set:
Fixed in Release Bug Number Description
9.2.0.7 4059639 * ORA-7445 SKGXPDMPCTX Instance Crash with 9.2.0.5 or 9.2.0.6 Patchset . This bug is alerted in Note:297306.1
9.2.0.7 2000938 A process may dump in kjrgpar in OPS/RAC if an ORA-4031 occurs
9.2.0.7 2314193 Dump from SELECT with query rewrite which uses MVIEW with functional index
9.2.0.7 2363758 Returning NULL from an EXTPROC callout may dump
9.2.0.7 2484985 Dump from anti or semi joins involving collections
9.2.0.7 2487943 Bulk insert in PLSQL may dump (kprcdt / memcpy)
9.2.0.7 2630783 Dump occurs inserting a VARRAY with 65535 elements
9.2.0.7 2718235 Dump (evacnt) when query uses WINDOW NOSORT over an aggregation
9.2.0.7 2763625 Some function pointers are stored in the SGA
9.2.0.7 2768251 Query against composite partitioned table fails with ORA-900 or dumps
9.2.0.7 2892189 Dump updating columns in a nested table with a trigger on the table
9.2.0.7 2953897 Dump (kkqucpon) can occur during semantic analysis

• 2.1.28 Wrong Results (501)
  This error hurts a lot. Data could get wrong values due to wrong results of a Query.
  

2.1.28 Wrong Results

The following table lists the Wrong Results bugs addressed in this patch set:
Fixed in Release Bug Number Description
9.2.0.7 4080972 + Wrong results from RBO range predicate on concatenated index with NULLs
9.2.0.7 2055862 OracleCachedRowSet.getRow() returns wrong value after last()
9.2.0.7 2339943 Query using OR between two subtypes returns a wrong number of rows
9.2.0.7 2562253 Wrong results due to lost join predicate with predicate move around
9.2.0.7 2628526 Wrong results from outer join to inline view in SELECT list
9.2.0.7 2859498 Incorrect sort order when ORDER BY DESC against a view with PQ
9.2.0.7 2925619 JDBC Thin always default timezone to +00:00
9.2.0.7 2960012 Incorrect (large) 'logons current' in GV$SYSSTAT in RAC
9.2.0.7 2974848 GetElementsByTagName in DBMS_XMLDOM returns nodes in wrong order
9.2.0.7 3017095 ALTER MVIEW COMPILE does not update LAST_DDL_TIME in DBA/ALL/USER_OBJECTS

• Conclusion
  

From the point of technology the Oracle Database Server has a leading role. This leading role must be confirmed by higher quality of the Oracle code.
The Code is written in C/C++ and thus a great challenge to create solid code with it - even with a excellent staff of programmers. Microsoft has moved it's programmig model from low level C++ and an very low level component model (COM/DCOM) to .NET. This assures better software with more solid features in future.
The Code has grown a lot so in future we will have to fight even with more bugs and Patchsets - if nothing changes.

This article is not to blame oracle but to improve its quality!
I will publish an update when the first Patchset of the Oracle 10G R2 was released.
Carl

Paging a Query Resultset

hi,

in a .NET driven project we got the demand to sample some ideas for paging of resultsets. With a database driven Client fetching of a complete resultset could last a very long time. First all the rows have to be fetched accross the network to the client. Then the client has to store the complete resultset in memory and probably has to convert the 'raw' rows into another format of data for example objects.

Resources are not unlimited - as we know ;-) - so we should find an approach of - i like to call it - an 'endless machine';

With google and searching for 'range paging' i found an interesteting article how to page resultsets in an very effektive way. The core functionality is very simple:

SELECT * FROM (
SELECT /*+ FIRST_ROWS */ IQ.*, ROWNUM AS Z_R_N FROM (
SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC
) IQ WHERE ROWNUM < :P0)
WHERE Z_R_N >= :P1

The blue business query is ceated by the client and wrapped by two nested range queries. This construct is he way to do this with an Oracle database. Setting the positional parameters :P0/:P1 declares the window in the result set. When :P0 is 40 and :P1 is 20 then all rows in the window from r0w 21 till row 40 are ready to be fetched (the resultset should be larger the 40 ;-)). The FIRST_ROWS hint is a tip for the Oracle optimizer to use the most effective path to retrieve the data thru the range query. The design of the query is from Tom Kyte.

But the core construct does not handle the logic around paging. There must be some control around the construct - this resulted in the utl_page PL/SQL package.


The Package design is simple.

A method (Create_Area) creates and tests the parameters for the query window

• the business query like : SELECT * FROM MY_OBJECTS ORDER BY OBJECT_NAME
• the pages size for example 20
  

A method (Get_Nextpage)

• which returns a REF Cursor (this is a variable which contains all information about an opened resultset originated by a given query) - Or simple returns the next page of a result set.
  

A method (Get_Prevpage)

• which returns a REF Curser - The previous page of the resultset.
  

Ok let's begin. I used the Visuals Studio .NET 2003 with the Oracle plugin Oracle Developer Tools for Visual Studio .NET. First let us create the Paging Area :







Now fetch the next page :
The query is ordered by object_name which you can see. An additional Colum Z_R_N tells you the position of the row int the complete rowset. Position 1 to 20 is fetched.






Fetch the next Page - row 21 to 40 :




Last but not least fetch of the previus page - rows 1 to 20







That's it. Sure - this is not the only one possible way to solve the demand. You could store the unique id's of a resultset in a temporary table to use this as baseline for paging. But if i can achieve it i do not write to the database for some paging.

If you want to get the PL/SQL Source Code then write to my employee-email kreitsch@csc.com and i will send it to you.



Another step could be to impelement the logic with a C# Class. I know that a lot of OO-oriented developers do not like to code with the PL/SQL :-(.
In a future article i like to write about the future of .NET and Oracle - a great future!
Bye
Carl