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.