Home » RDBMS Server » Performance Tuning » Stored Procedure is taking too long time to Execute. (2 Merged) (Oracle 11.2)
Stored Procedure is taking too long time to Execute. (2 Merged) [message #497293] Fri, 04 March 2011 14:17 Go to next message
nanuvamsi
Messages: 3
Registered: March 2011
Location: US
Junior Member
Hi all,

I have a stored procedure which executes in 1 hr in one database, but the same stored procedure is taking more than 6 hour in the other database.

Both the database are in oracle 11.2

Can you please suggest what might be the reasons.

Thanks.
Re: Stored Procedure is taking too long time to Execute. [message #497295 is a reply to message #497293] Fri, 04 March 2011 14:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
do below against both databases

ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the problem procedure
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here
Re: Stored Procedure is taking too long time to Execute. [message #497299 is a reply to message #497293] Fri, 04 March 2011 14:47 Go to previous messageGo to next message
nanuvamsi
Messages: 3
Registered: March 2011
Location: US
Junior Member
ok,you think trace is the only option.

Because i dont have SYS privs to generate a trace.

Thanks.
Re: Stored Procedure is taking too long time to Execute. [message #497300 is a reply to message #497299] Fri, 04 March 2011 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post FORMATTED EXPLAIN PLAN from both DBs
Re: Stored Procedure is taking too long time to Execute. (2 Merged) [message #497304 is a reply to message #497293] Fri, 04 March 2011 15:27 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
nanuvamsi wrote on Fri, 04 March 2011 21:17
Can you please suggest what might be the reasons.


One database having six times the data of the other one.

or

One database server having six time the processing power of the other one.

or

Something that will show up in a trace or perhaps an explain plan.
Re: Stored Procedure is taking too long time to Execute. (2 Merged) [message #498330 is a reply to message #497304] Wed, 09 March 2011 12:06 Go to previous messageGo to next message
nanuvamsi
Messages: 3
Registered: March 2011
Location: US
Junior Member
Hi all,

I am seeing in Toad in Server Statistics tab:

Library cache get hit ratio dynamic or unshareable sql?

Thanks.
Re: Stored Procedure is taking too long time to Execute. (2 Merged) [message #498332 is a reply to message #498330] Wed, 09 March 2011 12:36 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when you ignore our requests, then we ignore yours.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Previous Topic: SQL Tuning
Next Topic: Query tuning
Goto Forum:
  


Current Time: Thu Mar 28 08:02:25 CDT 2024