| Oracle Performance Issue linked to Concurrency [message #638057] |
Mon, 01 June 2015 21:51  |
 |
sachinj
Messages: 13
Registered: February 2012
Location: India
|
Junior Member |
|
|
I am having performance issue when executing a oracle stored procedure concurrently.
When the procedure is run independently this takes 4-5 minutes, and when 4-5 instances are executed in parallel each execution takes 35-40 mins.
The procedure logic involves selects from multiple tables, and inserts the data in multiple Global Temparary tables. As far I understand the parallel select operations should not block each other, and since the insert is on temporary table this should also not be a performance bottleneck.
I did not observe any blocking sessions while parallel execution, however when I ran the below query to identify the blocked objects, this gave me the list of temporary tables used in the procedure.
SELECT object_name FROM dba_objects WHERE object_id in (SELECT id1 FROM v$lock WHERE TYPE='TM');
Can someone please suggest how should I troubleshoot the issue.? I have attached the AWR logs(converted in .txt extension) for parallel execution.
Thanks for the help.
|
|
|
|
| Re: Oracle Performance Issue linked to Concurrency [message #638058 is a reply to message #638057] |
Mon, 01 June 2015 22:23  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
>inserts the data in multiple Global Temparary tables.
More often than not, Oracle does not require any temporary table.
The fact that you purposefully are (ab)using multiple GTT, I suspect a design flaw.
Does SQL below return any rows while multiple procedures are running concurrently?
SELECT Decode(request, 0, 'Holder: ',
'Waiter: ')
||vl.sid sess,
status,
id1,
id2,
lmode,
request,
vl.TYPE
FROM v$lock vl,
v$session vs
WHERE ( id1, id2, vl.TYPE ) IN (SELECT id1,
id2,
TYPE
FROM v$lock
WHERE request > 0)
AND vl.sid = vs.sid
ORDER BY id1,
request;
|
|
|
|
| Re: Oracle Performance Issue linked to Concurrency [message #638060 is a reply to message #638057] |
Tue, 02 June 2015 01:18 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your report is useless for addressing the problem you describe, because it covers a whole day when (on average) nothing much was happening. You need to generate a report over the period when you run the procedure serially, and another when you are running it in parallel.
However, one can make a few general comments:
I think your online redo log file groups are 50m. Replace them with ones that are 500MB.
Increase your memory_target by 1GB.
Upgrade the daabase from 11.2.0.1 to 11.2.0.4.
Set optimizer_dynamic_sampling=4 (or 11 after you upgrade)
Raise compatible to 11.2.0.1 (or 11.2.0.4 after upgrade)
Ask your sys admin why the I/O rates on your disc are so slow
Fix these points, and you may find that your problem goes away.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
