2012-03-08

Treasure hunting and blocking sessions

Bored with being just a Sherlock Holmes dba? Take a look at treasurehunt.solita.fi some tricky puzzles available for a week. The airprize available only to scan-agile participants but the pages seem to be open for all internet.

Back to Sherlock Holmesing. Blocking sessions are tricky ones. At least when the bug report is talking about yesterday. The application is throwing 5 minute transaction timeout to end user face. Lucky us the Diagnostic pack is available.

The bug information is tracked to a action SOS.createSO. And lucky us we have module, action and client_identifier populated to the session while accessing database.
select session_id,blocking_session,blocking_session_serial#,sql_exec_start,min(sample_time),max(sample_time), count(*), action
  from  DBA_HIST_ACTIVE_SESS_HISTORY s
 where sample_time > sysdate -300 
   and event= 'enq: TX - row lock contention' 
   and action = 'SOS.createSO'
 group by session_id,blocking_session,blocking_session_serial#,sql_exec_start,action
 having count(*) > 29
 order by min(sample_time) desc
;

sessio blocki blocking sql_exec_start         min(sample_time)           max(sample_time)           count action
465    699    17041    07.03.2012 12:32:45    07.03.2012 12:32:49,839    07.03.2012 12:37:40,203    30    SOS.createSO

We see that our reported bug session 465 was blocked by session 699. The five minutes timeout is readable from count(*) being 30. There is no other information about the blocking session than the sid and serial#. Should we be lucky to see some samples around the same time from this blocking session.
select s.*
  from  DBA_HIST_ACTIVE_SESS_HISTORY s 
 where session_id = 699 
   and session_serial# = 17041
;
No rows
Unlucky us. How about any other sessions being influenced by the kept row lock?
select session_id,blocking_session,blocking_session_serial#,sql_exec_start,min(sample_time),max(sample_time), count(*), action
  from  DBA_HIST_ACTIVE_SESS_HISTORY s
 where sample_time > sysdate -300 
   and event= 'enq: TX - row lock contention' 
 group by session_id,blocking_session,blocking_session_serial#,sql_exec_start,action
 having count(*) > 29
 order by min(sample_time) desc
;

sessio blocki blocking sql_exec_start         min(sample_time)           max(sample_time)           count action
465    699    17041    07.03.2012 12:32:45    07.03.2012 12:32:49,839    07.03.2012 12:37:40,203    30    SOS.createSO
30     699    17041    07.03.2012 12:31:11    07.03.2012 12:31:19,712    07.03.2012 12:36:10,080    30    SOS.createSOL
461    699    17041    07.03.2012 12:16:32    07.03.2012 12:16:38,563    07.03.2012 12:21:28,946    30    SOS.createSOL
406    699    17041    07.03.2012 12:14:21    07.03.2012 12:14:28,402    07.03.2012 12:19:18,780    30    SOS.createSOL
657    304    2655    02.03.2012 12:38:37    02.03.2012 12:38:41,735    02.03.2012 12:43:32,113    30    SOS.createSOL


The session 699 blocked also other sessions doing action SOS.createSOL. The time frame 699 was holding the lock got wider is has started already at time 12:14:21.

And yet again lucky us the similar thing happened a couple days ago also. Another blocking session is found.
select sample_time,sql_exec_start,s.module,s.action,s.client_id
  from  DBA_HIST_ACTIVE_SESS_HISTORY s 
 where session_id = 304 
   and session_serial# = 2655
;

sample_tim sql_exec module   action       client_id
02.03.2012 12:25:20 services RIS.updateRI integration
02.03.2012 12:47:47 works    SS.getSOT    obfuscateduser
And yet again lucky us. We get a before and after problem suspect end users and actions. The guilty code lines are possible to find with this information and application logs. Yet again Diagnostic pack license purchase got us to the right direction. The problem would have otherwise been unsolved.

2012-03-07

Time to learn model clause

I received the latest paper printed Oracle magazine today. Reading what the soon to be OUG Harmony 2012  keynote speaker has to say in the magazine askTom column. Dealing with the problem of Grouping Ranges.
So many ways writing code. The article introduces PL/SQL, recursive common table and model clause approaches. I just added a SQL and analytic function approach solution to the problem discussion at http://asktom.oracle.com. Not so pretty, but possible. Maybe the model clause version is still the most efficient one for this problem. Should also I start rehearsing model clause to my coding tool set...

2012-02-29

Speaking at OUG Harmony 2012

I will be speaking about execution plans OUG Harmony 30.-31.5.2012 Hämeenlinna Finland. Ougf.fi draft agenda published. Keynote speakers: Chris Date and Tom Kyte

2012-01-24

XMLType from no rows

I used DBMS_XMLGEN package in my post constraint name generated. There I used GETXML function and got problems when the schema constraints were all named. So the query result was empty - no rows.

SELECT XMLTYPE(
         DBMS_XMLGEN.GETXML('SELECT 1 FROM dual WHERE 1=0')
       ) AS xml
  FROM dual
;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

There is a built in DBMS_XMLGEN.GETXMLTYPE function to get the XMLType. This is how you get rid of the ORA-06502 problem.

SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT 1 FROM dual WHERE 1=0') AS xml
  FROM dual
;

2012-01-20

A single serializable session throwing ORA-08177

We have a newly created table in a 11.2.0.3 Enterprise edition instance. A single session is throwing ORA-08177 can't serialize access for this transaction. The whole service level is marked for trace.

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('servicename')

No one else is updating the tables involved. There comes out only two trace files. One having only the select 1 from dual connection pool check. And in the another trace there may be found.
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE
END OF STMT
Yes we wanted transactions to be serialized. Couple of inserts and internal staements. And an update to seg$ table thowing ORA-08177.
PARSING IN CURSOR #47594977567928 len=314 dep=1 uid=0 oct=6 lid=0 tim=1327068762208173 hv=3096556448 ad='de83deb8' sqlid='0kkhhb2w93cx0'
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL
, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where t
s#=:1 and file#=:2 and block#=:3
END OF STMT
PARSE #47594977567928:c=0,e=673,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1327068762208172
EXEC #47594977567928:c=1999,e=1411,p=0,cr=3,cu=1,mis=1,r=1,dep=1,og=4,plh=2170058777,tim=1327068762209667
STAT #47594977567928 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  SEG$ (cr=3 pr=0 pw=0 time=116 us)'
STAT #47594977567928 id=2 cnt=1 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ (cr=3 pr=0 pw=0 time=16 us cost=2 size=68 card=1)'
STAT #47594977567928 id=3 cnt=1 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)'
CLOSE #47594977567928:c=0,e=2,dep=1,type=3,tim=1327068762209778
EXEC #47594977567928:c=0,e=125,p=0,cr=3,cu=1,mis=0,r=1,dep=1,og=4,plh=2170058777,tim=1327068762209931
CLOSE #47594977567928:c=0,e=1,dep=1,type=3,tim=1327068762209972
EXEC #47594977567928:c=0,e=129,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,plh=2170058777,tim=1327068762210127
CLOSE #47594977567928:c=0,e=1,dep=1,type=3,tim=1327068762210167
EXEC #47594984047704:c=50992,e=50764,p=7,cr=576,cu=241,mis=1,r=0,dep=0,og=1,plh=0,tim=1327068762211409
ERROR #47594984047704:err=8177 tim=1327068762211441
STAT #47594984047704 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=1 us)'

*** 2012-01-20 16:12:42.223
WAIT #47594984047704: nam='log file sync' ela= 11537 buffer#=84 sync scn=604674242 p3=0 obj#=-1 tim=1327068762223055
WAIT #47594984047704: nam='SQL*Net break/reset to client' ela= 21 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=1327068762223170
WAIT #47594984047704: nam='SQL*Net break/reset to client' ela= 3715 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=1327068762226909

After awhile the problem vanished... No solutions in this post. Just wondering why...

2012-01-19

Oracle instance memory usage

The right place to look for memory usage of a process in at operating system level. Interpreting the shared memory usage might be misleading. The top command seems to add the shared part to each process. And thou makes it hard to get right numbers. Here is a blog post trying to do that. Thou you could ask the numbers also from Oracle. Parameters influencing Oracle instance memory usage limits. 11g instance might have memory target set or sga and pga set separately.
select name,display_value 
  from v$parameter 
 where name like 'mem%target' 
    or name like 'pga%' 
    or name like 'sga%'
;

The actual PGA usage might hit and go above those limits. The current allocation can be queried.
select round(sum(bytes)/1024/1024/1024,3) SGA_G 
  from v$sgastat;

select round(value/1024/1024/1024,3) PGA_G
  from v$pgastat 
 where name = 'total PGA allocated';

Most often the database load varies over time. For resource planning it is vital to know how the memory usage is changing during night time. There might be going on some batch jobs whose behavior is not seen during day time. If you have diagnostics pack purchased you can also ask what was the situation earlier. Here is a query getting hourly memory usages of an Oracle instance.
select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,trunc(SN.END_INTERVAL_TIME,'mi') time
  from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo 
   from DBA_HIST_SGASTAT 
  group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo 
    from DBA_HIST_PGASTAT where name = 'total PGA allocated' 
   group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn 
where sn.snap_id=sga.snap_id
  and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
  and sn.snap_id=pga.snap_id
  and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;


About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.