Search

Top 60 Oracle Blogs

Recent comments

April 2010

Something I recently unlearned...

This is how many of the things I learn everyday come into being. They are actually things I have to "unlearn" because what used to be true has changed over time.

Once upon a time ago - I remember the day I learned this, it was during a benchmark in 1993, I learned that UNINDEXED foreign keys had some locking implications. Specifically if you:

  • update the parent primary key (which does happen, some 'frameworks' update every column even if the value did not change)
  • delete from parent

Then you should probably index the foreign key in the child table - else there will be a full table lock placed on the child table - for the duration of the transaction.

Then Oracle 9i was released and I had to relearn the rule. The rule in 9i was as above still - just modified as to the duration of the lock (many people think the restriction actually went away - but it did not, it was changed). In 9i and above, if you update the parent or delete from the parent with an unindexed foreign key - the child table is still locked - just for the duration of the update or delete! The lock is released after the statement processed - not when you commit. This was "better", but the lock still exists.

Sometime during 9i - I learned yet another modification to the rule above. The rule in 9i now has to include:

  • if you merge into the parent table

in addition to update and delete. As I was getting ready to add that to the 2nd Edition of Expert Oracle Database Architecture - I learned something new, the rule has changed again. The MERGE doesn't always lock the table anymore in 11g Release 1 and above - so we are back to just update and delete (sort of!).

Here is the small test case you can use to verify - the set up is:

ops$tkyte%ORA9IR2> create table p ( x int primary key, y int );
Table created.

ops$tkyte%ORA9IR2> insert into p values ( 1, null );
1 row created.

ops$tkyte%ORA9IR2> insert into p values ( 2, null );
1 row created.

ops$tkyte%ORA9IR2> create table c ( x references p );
Table created.

ops$tkyte%ORA9IR2> create or replace
procedure modify_p( p_what in varchar2 )
2 as
3 pragma autonomous_transaction;
4 deadlock exception;
5 pragma exception_init( deadlock, -60 );
6 begin
7 if ( p_what = 'DELETE' ) then delete from p where x = 2;
8 elsif ( p_what = 'UPDATE' ) then update p set x = 2 where x = 2;
9 elsif ( p_what = 'MERGE' ) then
10 merge into p using (select 2 x, 42 y from dual) d
11 on (p.x=d.x)
12 when matched then update set y = d.y
13 when not matched then insert(x,y) values (d.x,d.y);
14 end if;
15 rollback;
16 dbms_output.put_line( p_what || ': successful...' );
17 exception
18 when deadlock then
19 dbms_output.put_line( p_what ||
': we deadlocked, we needed full table lock');
20 rollback;
21 end;
22 /
Procedure created.

So, a parent table with two rows - 1 and 2. An empty child table with an unindexed foreign key. A stored procedure that runs as an autonomous transaction - so it cannot share the locks of the parent transaction, if the parent transaction has anything locked - the autonomous_transaction will NOT be able to also lock it. The autonomous transaction attempts to either

  • delete row X=2 from parent
  • update row X=2 in parent
  • merge into row x=2 in parent using when matched then update, when not matched then insert

and if it deadlocks - prints out a message telling us that and rolls back. If successful, prints out a message telling us that and likewise rolls back.

To test, we just insert into the child table a record that points to row x=1 in parent (we'll never touch that row in the parent table) and then try the three DML opertions:

ops$tkyte%ORA9IR2> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA9IR2> exec modify_p( 'DELETE' );
DELETE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> rollback;
Rollback complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA9IR2> exec modify_p( 'UPDATE' );
UPDATE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> rollback;
Rollback complete.

ops$tkyte%ORA9IR2> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA9IR2> exec modify_p( 'MERGE' );
MERGE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> rollback;
Rollback complete.

There you go, you can see it deadlocked on all three - they all needed to lock the child table before doing their work.

If you run that in 10gr1 and 10gr2 - you'll see the same results - all three lock. However, starting in 11g Release 1 - you'll see this:

ops$tkyte%ORA11GR1> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA11GR1> exec modify_p( 'DELETE' );
DELETE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> rollback;
Rollback complete.

ops$tkyte%ORA11GR1> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA11GR1> exec modify_p( 'UPDATE' );
UPDATE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> rollback;
Rollback complete.

ops$tkyte%ORA11GR1> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA11GR1> exec modify_p( 'MERGE' );
MERGE: successful...
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> rollback;
Rollback complete.

That merge no longer locks the child table. Curious as to this change - I wanted to see if it was official or not and found bug 5970280 - from which I learned that it was officially changed and that some of you running 10.2 might see a different result for this test case (the fix was backported and is available for 10.2).

The fix is more complex than appears (aren't they always?) It is not just "turn off lock for MERGE", it is "analyze the merge and

  • if the merge just inserts - treat as an insert
  • if merge does an update or update and insert (and we are NOT updating the primary key!) treat as an update to non-primary key columns
  • if merge does an update or update and insert (and we are updating the primary key) lock child table
  • if merge includes a delete - treat as a delete and lock child table


So, if your merge was:


10 merge into p using (select 2 x, 42 y from dual) d
11 on (p.y=d.x)
12 when matched then update set x = d.x
13 when not matched then insert(x,y) values (d.x,d.y);

then you would see:


ops$tkyte%ORA11GR1> exec modify_p( 'MERGE' );
MERGE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

or if your merge included a possible delete branch, you would see the same.

So, I guess the rule in 11gR1 and above is, if you

  • update parent primary key
  • delete from parent
  • use a merge that does either of the above

and you have an unindexed foreign key - you should expect a full table lock on the child table. If you index that foreign key - no untoward locking will take place.

Things change over time :)

This Is A VERY Boring Blog!

I’ve been stranded in Europe for 4 days and the situation persists!  Needless to say I haven’t been thinking that much about blogging I do have a post nearly ready to go about booting 4s48c Opteron 6100 systems with _enable_NUMA_support set to TRUE. There are some caveats, and some very significant benefits as well. I’ll [...]

5th Planboard DBA Symposium: Registration now open

On June 8 Planboard will run her 5th Dutch DBA Symposium and the registration is now open. This “for Dutch DBA’s, by Dutch DBA’s” symposium has become the place to be for the serious DBA who wants to share his or her knowledge with other DBA’s in an open environment with plenty of networking time […]

My Sessions at IOUG Collaborate 2010

Thank you for all those attended my sessions during Collaborate 2010. Two of the sessions I presented were not mine; but that of Riyaj Shamsuddin. Riyaj was stuck in Denmark thanks to the ash cloud scenario in Europe and asked if I could present his. I agreed to and, with a lot of trepidation, I did. I hope I did justice to the sessions. For questions on those sessions, please reach out to Riyaj directly.

Going to my own sessions, here is where you can download the presentations. For the sessions I wanted to show live demos; but in a short span of 30 minutes for Quick Tips, it was impossible. You can download the scripts here so that you can check them out yourself. The slides show which scripts to execute.

RAC Performance Tuning, part of RAC Bootcamp (Recorded)
Stats with Intelligence (Recorded)
Publish Stats after Checking, part of Manageability Bootcamp (Recorded and shown via Webcast)

Once again, your patronage by attending is highly appreciated. A speaker is nothing without attendees. I sincerely hope that you got some value from the sessions. As always, I am looking forward to hearing from you – not just that you liked; but things you didn't.

Viewing Figures

Although I wasn’t convinced by the 25,000,000 view figures I reported last week, I’m inclined to trust my page view counter which is currently showing a little of 1,500,000 page views; and, as I have in the past, I thought I would produce a summary of what’s been of most interest to visitors: If we [...]

What will happen if...

So, something else I learned recently...

Say you have a table T:

ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.

and you update a row in that table:

ops$tkyte%ORA10GR2> update t set x = x+1;
1 row updated.

and using an evil autonomous transaction you try to lock that row (in the same session, but a new transaction - one that cannot 'see' the effects of the parent transaction)

ops$tkyte%ORA10GR2> declare
2 pragma autonomous_transaction;
3 l_rec t%rowtype;
4 begin
5 select * into l_rec from t for update wait 5;
6 commit;
7 end;
8 /
declare
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-06512: at line 5

So far, it all seems normal. You asked to wait for 5 seconds, you did - and you time out. But, what happens if you wait longer?

ops$tkyte%ORA10GR2> declare
2 pragma autonomous_transaction;
3 l_rec t%rowtype;
4 begin
5 select * into l_rec from t for update wait 6;
6 commit;
7 end;
8 /
declare
*
ERROR at line 1:
??????????????????????????????????

What error are you expecting that time - hint, it is not ORA-30006 it is

ops$tkyte%ORA10GR2> declare
2 pragma autonomous_transaction;
3 l_rec t%rowtype;
4 begin
5 select * into l_rec from t for update wait 6;
6 commit;
7 end;
8 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 5

apparently the self deadlock code kicks in before the timeout happens and the deadlock detection code doesn't see the "we will eventually time out"

And yes, this applies to multi-session cases as well - run this script to see that:

drop table t;
set echo on
create table t ( x int );
insert into t values ( 1 );
insert into t values ( 2 );
commit;
select * from t where x = 1 for update;
set echo off
prompt in another session issue:
prompt select * from t where x = 2 for update;;
pause
prompt in another session issue:
prompt select * from t where x = 1 for update wait 10;;
set echo on
select * from t where x = 2 for update;

It will deadlock one of the sessions - without waiting for the "wait" timeout.

So, deadlock detection trumps a waiting period. If the waiting period expires before the deadlock routines kick in - you get the ora-30006, if the deadlock routines kick in before the timeout - you get ora-60.

Index Rebuild ?

While searching on Metalink for clues about an oddity relating to an index rebuild, I came across Bug 6767655  – reported in 10.2.0.3 and fixed in 11.2.  The problem is described as follows: When having two sessions running concurrently, one doing inserts to a partitioned table, and the other doing partition maintenance operations, there is [...]

NO_DATA_NEEDED - something I learned recently

I'll be writing about this in Oracle Magazine shortly as part of the asktom column - but thought I'd mention it here too.

Recently on asktom.oracle.com, I was asked a question about the pre-defined exception NO_DATA_NEEDED. At first I thought that is was a typo – they really meant NO_DATA_FOUND – since I hadn’t heard of or read about that exception. But in looking a little deeper, I discovered what it was.

If you ever write a pipelined function - there is a good chance you need to be aware of it. Don't go searching for it in the documentation (it will be there in the next dot release - but it isn't there yet), you won't find it. Don't google for it - you won't find much about it yet. But it is something we've needed, probably knew we needed, just never thought about it.

What if you have a pipelined function that does something like:

a) open file
b) read line - pipe row
c) when no more data, close file and return

It works perfectly - if you read all records from the file. However, if you call it from a query such as:

select * from table(pipelined_function( '/tmp/foo.dat' )) where rownum = 1;

What happens - what happens if there was zero records in /tmp/foo.dat to read? one record? More than one record?

Well, "it depends". In all likelihood - if there was one or more records - you would leave a file handle open - and if you called this function over and over, you would leak an open file each time and eventually run out of file handles. So, you would have an error that would "sometimes happen" and "sometimes not happen". In other words - one of those strange non-reproducible bugs that only happens when it rains on a Tuesday after midnight but before 8am.

Enter NO_DATA_NEEDED - an exception that doesn't behave like any other exception. An exception that is raised - but does not cause failure. An exception that can be caught, but if it isn't - everything is still OK.

Say you have code like this:


create or replace function
foo( inputs ... )
return some_type
PIPELINED
as
/* declaration */
begin
/* initialization */

/* process a loop */
pipe row(i);
end loop;

/* clean up */
return;
end;
/

The clean up code would execute and do the right thing if you exit the loop - but not so if you just stopped calling this function. The NO_DATA_NEEDED exception is there for just such a case. Here is a concrete example:


SQL> create or replace function
2 generate_data( n in number )
3 return sys.odciNumberList
4 PIPELINED
5 as
6 begin
7 dbms_output.put_line
8 ( '===>>> INITIALIZE' );
9 for i in 1..generate_data.n
10 loop
11 dbms_output.put_line
12 ( '===>>> PROCESS' );
13 pipe row(i);
14 end loop;
15 dbms_output.put_line
16 ( '===>>> CLEAN UP' );
17 return;
18 end;
19 /

Function created.

That is a pretty straightforward PL/SQL pipelined function – if we run it to completion – we would see this output:


SQL> select *
2 from table(generate_data(2));

COLUMN_VALUE
------------
1
2

===>>> INITIALIZE
===>>> PROCESS
===>>> PROCESS
===>>> CLEAN UP
SQL>

Which is what we expect – but what if we don’t fetch two rows from that function, what if we only fetch one?


SQL> select *
2 from table(generate_data(2))
3 where rownum = 1;

COLUMN_VALUE
------------
1

===>>> INITIALIZE
===>>> PROCESS
SQL>

As you can see – we did the initialize and one process bit of our code, but the rest – it was just skipped over, because the invoking SQL statement did not need it. We didn’t see any error (we would expect an unhandled exception to raise an error!), it just appears to have worked.

There was however, an exception raised – an exception that does not have to be caught. It will be ignored entirely if it is not caught. It differs from every other exception in that regard – we would expect an unhandled exception to propagate to the client and appear as “an error”. Lets see what happens with out code if we implement this error handler:


SQL> create or replace function
2 generate_data( n in number )
3 return sys.odciNumberList
4 PIPELINED
5 as
6 begin
7 dbms_output.put_line
8 ( '===>>> INITIALIZE' );
9 for i in 1..generate_data.n
10 loop
11 dbms_output.put_line
12 ( '===>>> PROCESS' );
13 pipe row(i);
14 end loop;
15 dbms_output.put_line
16 ( '===>>> CLEAN UP' );
17 return;
18 exception
19 when no_data_needed
20 then
21 dbms_output.put_line
22 ( '***>>> CLEAN UP' );
23 return;
24 end;
25 /

Function created.

On line 19 we catch the predefined exception NO_DATA_NEEDED and on line 21 announce that we are cleaning up (releasing any resources that need be released). Now when we run this pipelined function without exhausting it we see:


SQL> select *
2 from table(generate_data(2))
3 where rownum = 1;

COLUMN_VALUE
------------
1

===>>> INITIALIZE
===>>> PROCESS
***>>> CLEAN UP
SQL>

As you can see – our special cleanup code (we used ***>>> to announce it) was executed and we could clean up any resources we allocated.

The Core Performance Fundamentals Of Oracle Data Warehousing – Parallel Execution

[back to Introduction] Leveraging Oracle’s Parallel Execution (PX) in your Oracle data warehouse is probably the most important feature/technology one can use to speed up operations on large data sets.  PX is not, however, “go fast” magic pixi dust for any old operation (if thats what you think, you probably don’t understand the parallel computing paradigm). With Oracle PX, a large task is broken up into smaller parts, sub-tasks if you will, and each sub-task is then worked on in parallel.  The goal of Oracle PX: divide and conquer.  This allows a significant amount of hardware resources to be engaged in solving a single problem and is what allows the Oracle database to scale up and out when working with large data sets. I though I’d touch on some basics and add my observations but this is by far not an exhaustive write up on Oracle’s Parallel Execution.  There is an entire chapter in the Oracle Database documentation on PX as well as several white papers.  I’ve listed all these in the Resources section at the bottom of this post.  Read them, but as always, feel free to post questions/comments here.  Discussion adds great value. A Basic Example of Parallel Execution [...]

adhd ocd dba

Possibly random thoughts of a oddly organized dba
with a very short attention span