пятница, 11 июля 2014 г.

Materialize subquery on Active DataGuard

Due to some bug Oracle can fail to materialize subquery from with ... as clause on Active DataGuard with error
ORA-00604: error occurred at recursive SQL level 2
ORA-16000: database open for read-only access

If subquery does not have materialize hint, the situation can be fixed by setting "_with_subquery"=INLINE on session or system level.

Please consult Oracle Support before using this on production system.

вторник, 8 июля 2014 г.

Oracle PL/SQL programming 6th edition

There are several statement from the book "Oracle PL/SQL programming 6th edition" which I disagree with.

1) page 475 (paper)
SAVEPOINT gives a name to, and marks a point in, the processing of your transaction.
This marker allows you to ROLLBACK TO that point, undoing any changes and releasing
any locks issued after that savepoint, but preserving any changes and locks that
occurred before you marked the savepoint.

I think that it is worth to be mentioned that sessions which was waiting on locks released after rollback to savepoint remain waiting, although new sessions can lock that resources.
By the way, implicit savepoint before anonymous block works in the same manner.

2) page 476 (paper)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE | READ COMMITTED
This version defines how transactions that modify the database should be handled.
You can specify a serializable or read-committed isolation level. When you specify
SERIALIZABLE, a DML statement that attempts to modify a table that has already
been modified in an uncommitted transaction will fail.

Serialized transaction will fail with "ORA-08177: can't serialize access for this transaction" only after the uncommitted transaction eventually becomes committed, if it rolls back then serialized is successful.

to be continued...



четверг, 12 декабря 2013 г.

FOR UPDATE SKIP LOCKED

One new thing I`ve learned from recent PL/SQL Challenge quiz is about how SELECT FOR UPDATE SKIP LOCKED works.

It does not really lock rows on EXEC phase of query execution (open cursor), when ordinary FOR UPDATE does.

Here are two excerpts from SQL Trace:
PARSING IN CURSOR #47354360485408 len=72 dep=1 uid=63 oct=3 lid=63 tim=1386839485248389 hv=4069083409 ad='539eab670' sqlid='csw3rbvt8kk8j'
SELECT * FROM PLCH_EMPLOYEES ORDER BY EMPLOYEE_ID FOR UPDATE SKIP LOCKED
END OF STMT
EXEC #47354360485408:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1212233107,tim=1386839485248389

PARSING IN CURSOR #47085989084664 len=60 dep=1 uid=63 oct=3 lid=63 tim=1386839807239479 hv=3207034820 ad='539de1f98' sqlid='aqqrp16zkfxy4'
SELECT * FROM PLCH_EMPLOYEES ORDER BY EMPLOYEE_ID FOR UPDATE
END OF STMT
PARSE #47085989084664:c=3000,e=2655,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,plh=1212233107,tim=1386839807239478
EXEC #47085989084664:c=0,e=199,p=0,cr=7,cu=4,mis=0,r=0,dep=1,og=1,plh=1212233107,tim=1386839807239715 

вторник, 25 июня 2013 г.

SQLPLUS ARRAYSIZE and LOB

SQLPLUS ARRAYSIZE drops to 1 if your query select list contains LOB column(s).

Extended SQL Trace file contains following rows for such queries (on my 11.2.0.3 Linux box)



FETCH #4013088:c=24997,e=24286,p=0,cr=384,cu=394,mis=0,r=1,dep=0,og=1,plh=1447629766,tim=1372161822772660
WAIT #4013088: nam='SQL*Net message from client' ela= 315 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=1372161822773140
WAIT #0: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=1372161822773212
LOBREAD: c=0,e=57,p=0,cr=2,cu=0,tim=1372161822773231
WAIT #0: nam='SQL*Net message from client' ela= 385 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=1372161822773655
WAIT #0: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=1372161822773720
LOBREAD: c=0,e=54,p=0,cr=2,cu=0,tim=1372161822773739
WAIT #0: nam='SQL*Net message from client' ela= 1432 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=1372161822775210
LOBTMPFRE: c=0,e=21,p=0,cr=0,cu=0,tim=1372161822775271
WAIT #0: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=1372161822775301
WAIT #0: nam='SQL*Net message from client' ela= 359 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=1372161822775677
WAIT #4013088: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=1372161822795869
FETCH #4013088:c=19997,e=20226,p=0,cr=389,cu=394,mis=0,r=1,dep=0,og=1,plh=1447629766,tim=1372161822795935

вторник, 11 июня 2013 г.

"PL/SQL lock timer" wait event is not written into ASH (v$active_session_history/dba_hist_active_sess_history)

Recently I investigated some database lock issue by digging into ASH data. I accidentally found that while session is in 'ACTIVE' state and 'WAITING' on "PL/SQL lock timer", no rows is added into ASH.
So if you trying to figure out what was happened in the past, you can find that some active session disappeared from ASH for period of time. And only manually made snapshots of v$session can give some real info.

вторник, 19 февраля 2013 г.

ASH buffer size

An undocumented feature is shown in modern Oracle version alert log
For example:
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 33554432 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
 select total_size,awr_flush_emergency_count from v$ash_info;

среда, 4 июля 2012 г.

Before/After Trigger efficiency

Something changes continuously... and trigger efficiency too :).
Please compare Oracle statement about triggers (see "Note" about trigger efficiency)
Oracle 9iOracle 10g and Oracle 11g.

I`ve tested this on 11.1.0.7 (the only database version in my hands now).
Its behavior matches 11g and 9i documents.


I used triggers with empty body like this:

create or replace trigger t1_tr_bu
before update on t1
for each row
begin
null;
end;
/

create or replace trigger t1_tr_au
after update on t1
for each row
begin
null;
end;
/


and receive these results:
without triggers



1 row updated.




Statistics
----------------------------------------------------------
 0  recursive calls
 1  db block gets
 7  consistent gets
 0  physical reads
352  redo size
674  bytes sent via SQL*Net to client
591  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed


with before trigger




1 row updated.




Statistics
----------------------------------------------------------
 0  recursive calls
 2  db block gets
 7  consistent gets
 0  physical reads
296  redo size
674  bytes sent via SQL*Net to client
591  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed



with after trigger

1 row updated.


Statistics
----------------------------------------------------------
 0  recursive calls
 1  db block gets
 7  consistent gets
 0  physical reads
 0  redo size
674  bytes sent via SQL*Net to client
591  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed