2013/12/12

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 

2013/06/25

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

2013/06/11

"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.

2013/02/19

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;