Merge statement [message #349707] |
Mon, 22 September 2008 15:28 |
vesile_taskiran
Messages: 66 Registered: August 2008 Location: Turkey
|
Member |
|
|
Hi all
I have a procedure , that calls sub procedures. and these sub procedures include only merge statements. When I run the main procedure, after executing a few merge procedures i check sessions from v$sessions i see about 150 active sessions and my db slows down. why this happens, is it normal?
|
|
|
Re: Merge statement [message #349752 is a reply to message #349707] |
Tue, 23 September 2008 00:42 |
|
Michel Cadot
Messages: 68712 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Procedures? which kind of procedures? PL/SQL, Java, C, VB, Ada, PL/1, Lisp, Prolog, APL?
PL/SQL procedure don't and can't open session unless you spawn jobs.
Regards
Michel
|
|
|
|
Re: Merge statement [message #349804 is a reply to message #349781] |
Tue, 23 September 2008 03:28 |
vesile_taskiran
Messages: 66 Registered: August 2008 Location: Turkey
|
Member |
|
|
Its PL SQL procedure these other sessions are owned by the sub procedures of main procedures.
when i check
select * from v$session where username='NORTHI_LOADER_MTRL_BSS_GSR81' and status='ACTIVE'
i get between 60 and 160 active sessions. How can i check the blocking locks ?
Thanks in advance.
|
|
|
|
Re: Merge statement [message #349823 is a reply to message #349813] |
Tue, 23 September 2008 04:02 |
vesile_taskiran
Messages: 66 Registered: August 2008 Location: Turkey
|
Member |
|
|
when i run following procedure
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id
OS_USER_NAME LOCKED_MODE OBJECT_NAME OBJECT_TYPE
NORTHI_PARSER 6 CARRIER_STATISTIC4 TABLE
NORTHI_PARSER 6 CARRIER_STATISTIC3 TABLE
NORTHI_PARSER 6 CARRIER_STATISTICS TABLE
NORTHI_PARSER 6 BSS_STATISTICS TABLE
NORTHI_PARSER 6 CBL_STATISTICS TABLE
NORTHI_PARSER 6 CELL_STATISTICS_2 TABLE
NORTHI_PARSER 6 ENTITY TABLE
NORTHI_PARSER 6 CARRIER_STATISTIC2 TABLE
NORTHI_PARSER 6 NBR_STATISTICS TABLE
is this a problem?
regards.
|
|
|
|
Re: Merge statement [message #349828 is a reply to message #349707] |
Tue, 23 September 2008 04:18 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Quote: | How can i check the blocking locks ?
|
SELECT l1.SID, ' IS BLOCKING ', l2.SID
FROM v$lock l1, v$lock l2
WHERE l1.BLOCK =1 AND l2.request > 0
AND l1.id1=l2.id1
AND l1.id2=l2.id2;
|
|
|