Home » SQL & PL/SQL » SQL & PL/SQL » Merge statement (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
Merge statement [message #349707] Mon, 22 September 2008 15:28 Go to next message
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 Go to previous messageGo to next message
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 #349781 is a reply to message #349707] Tue, 23 September 2008 02:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Who owns these other sessions?
What are they waiting on?
Are there any blocking locks?
Re: Merge statement [message #349804 is a reply to message #349781] Tue, 23 September 2008 03:28 Go to previous messageGo to next message
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 #349813 is a reply to message #349804] Tue, 23 September 2008 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
these other sessions are owned by the sub procedures of main procedures.

A procedure does not own a session, a session executes a procedure.
As I said, a procedure does not create new sessions unless you spawn job, check this.

Quote:
How can i check the blocking locks ?

v$lock
dba_lock
v$locked_object
dba_blockers
dba_waiters
...

Regards
Michel
Re: Merge statement [message #349823 is a reply to message #349813] Tue, 23 September 2008 04:02 Go to previous messageGo to next message
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 #349825 is a reply to message #349823] Tue, 23 September 2008 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Use dba_blockers and dba_waiters.
Until you describe in detail (or post code) what you do in your procedure, no diagnosis is possible. I still don't know if you spawn jobs or not (third time I ask).

Regards
Michel
Re: Merge statement [message #349828 is a reply to message #349707] Tue, 23 September 2008 04:18 Go to previous message
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;
Previous Topic: How to pass table/collection/array to function.
Next Topic: Bulk write in text file using utl_file
Goto Forum:
  


Current Time: Thu Nov 14 04:44:40 CST 2024