четверг, 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



суббота, 3 марта 2012 г.

Struggling with ORA-00980


If you are struggling with ORA-00980, and all things seem to be ok. Even you can use synonym with dblink in ordinary sql. But in pl/sql it raises ORA-00980.
As last resort you can try to check global name for databases from both side of dblink. They both must not match each other.
Here is query to check global name: select * from global_name;


вторник, 23 августа 2011 г.

TM enqueue with mode=3

There is yet another case when INSERT statement can be blocked.
According to this Oracle doesn`t acquire table locks on the child table during insert into parent table. But I`ve discovered something else.

Suppose you have a MASTER table and a DETAIL table with UNINDEXED foreign key constraint which references the MASTER table. Foreign key has been created with ON DELETE CASCADE option. And there are some rows in both tables.

Session 1 does the following:
delete from master where id=some_value;

There is no commit here.

Session 2:
delete from master where id=other_value;

Now Session 2 is stuck with TM enqueue request in mode=5 for DETAIL table. This is well known behavior.

But when Session 3 tries to insert a row into the MASTER table, it is stuck too with TM enqueue request in mode=3 for DETAIL table. By the way, mode=3 is used on Oracle version 11.2.0.2, and 10.2.0.4 shows mode=2 in this case.

Here is useful SQL to reproduce the case.

--Setup

drop table detail;
drop table master;
create table master as select * from all_objects where 1=2;
alter table master add constraint master_pk primary key (object_id);
create table detail as select * from all_objects where 1=2;
alter table detail add constraint detail_fk foreign key (object_id) references master on delete cascade;
insert into master select * from all_objects;
insert into detail select * from master;
commit;

--For Session 1
delete from master where object_id=100;

--For Session 2
delete from master where object_id=101;

--For Session 3

insert into master
  (owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary/*for 11g, namespace, edition_name*/)
values
  ('a', 'x', null, 1000000, null, null, sysdate, sysdate, null, null, null, null, null/*, 1, null*/);



--For monitoring

select l.sid,
       l.type,
       l.lmode,
       l.request,
       l.block,
       o.owner object_owner,
       o.object_Name,
       q.sql_text
  from sys.all_objects o, v$lock l, v$session s, v$sql q
 where l.sid = s.sid
   and o.object_id(+) = l.id1
   and s.sql_id = q.sql_id(+)
 order by l.sid, o.object_Name;