Home » RDBMS Server » Performance Tuning » Oracle Performance Issue linked to Concurrency (11G)
Oracle Performance Issue linked to Concurrency [message #638057] Mon, 01 June 2015 21:51 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25856
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 Go to previous messageGo to next message
John Watson
Messages: 7262
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.
Re: Oracle Performance Issue linked to Concurrency [message #638125 is a reply to message #638060] Wed, 03 June 2015 02:04 Go to previous messageGo to next message
sachinj
Messages: 13
Registered: February 2012
Location: India
Junior Member
John,

Thanks for the inputs, and I am trying out the same.

I am attaching the AWR logs for the parallel and non-parallel run. The logs have been merged into a single txt file as the post only allows 1 attachment. The first html document tag is for parallel run, and the other is for non-parallel run.

Can you please suggest if you see any issue in the same?

The procedure 'crt_get_account_tree' is taking ~7 mins for non-parallel run, and for parallel run this is taking ~40 mins.
Also, I did not observer any blocking session during the concurrent execution.

Thanks
Sachin
  • Attachment: AWRLogs.txt
    (Size: 896.48KB, Downloaded 176 times)
Re: Oracle Performance Issue linked to Concurrency [message #638126 is a reply to message #638125] Wed, 03 June 2015 02:09 Go to previous message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
I can't open that file. And in any case, I see no point until you have implemented my suggestions, which should take you no more than an hour.
Previous Topic: Please help for improving execution plan
Next Topic: ANALYZE and GATHER STATS
Goto Forum:
  


Current Time: Wed Feb 21 06:11:11 CST 2018

Total time taken to generate the page: 0.14269 seconds