DBAs script bunches

http://www.shutdownabort.com/

/*+ PRECOMPUTE_SUBQUERY */

Here is some interesting findings:

Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint

Some explanation at OTN: precompute_subquery hint

Here is simple example of common mistake:

SQL> drop table t
2 /

Table dropped
SQL> create table t
2 as
3 select level as num from dual connect by level <= 100
4 /

Table created

SQL> begin
2
3 savepoint a;
4
5 execute immediate 'truncate table t';
6
7 insert into t
8 select level+100 from dual connect by level <= 100;
9
10 raise_application_error( -20000,'Fake exception');
11
12 exception
13 when others then
14 rollback to a;
15 raise;
16 end;
17 /

begin

savepoint a;

execute immediate 'truncate table t';

insert into t
select level+100 from dual connect by level <= 100;

raise_application_error( -20000,'Fake exception');

exception
when others then
rollback to a;
raise;
end;

ORA-01086: savepoint 'A' never established
ORA-06512: at line 15
ORA-20000: Fake exception


The cause of rollback failure is that rollback to savepoint A operation could be completed after commitment by DDL statement.

bind peeking and execution plan...

It's gone long time since i've posted my first blog message but this year i'm going to be more active here :)

The first post of this year intends to share nice articles about bind peeking and execution plans:

1. Karen Morton's blog introduced the article - "Dynamic" WHERE clauses that aren't
2. The Great Jonathan Lewis blog - Conditional SQL (2)
3. The article by Alex Fatkulin - Stabilize Oracle 10G’s Bind Peeking Behaviour by Cutting Histograms