Home » RDBMS Server » Performance Tuning » consecutive executions (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
consecutive executions [message #654142] Wed, 27 July 2016 06:48 Go to next message
bibhuti037
Messages: 2
Registered: December 2014
Location: Chennai
Junior Member
I have changed one trigger in prod. I have added two select statements to select some data from table.
Previously one process was taking 30/35 seconds to process 1000 records, now it is taking 30/40 minutes after my change.
After my change first three thousand records are finishing in 1 minuts, but later it is takikg 40/50 minutes.

trigger t1.
procedure p1.
table tab1.

trigger t1 is on table tab1.
p1 will insert/update/delete records from/into tab1.
I have changed t1.

Previously
rows processed 9000 010052
rows processed 8000 010017
rows processed 7000 010000
rows processed 6000 005945
rows processed 5000 005925
rows processed 4000 005904
rows processed 3000 005841
rows processed 2000 005810
rows processed 1000 005710

Now, after change
rows processed 10000 062336
rows processed 9000 061742
rows processed 8000 052140
rows processed 7000 051114
rows processed 6000 045101
rows processed 5000 041121
rows processed 4000 035825
rows processed 3000 035729
rows processed 2000 035626
rows processed 1000 035427


Please help.

Re: consecutive executions [message #654145 is a reply to message #654142] Wed, 27 July 2016 08:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Looks like,
There is one trigger, one table, two new select statements that selects some data
and some other process that used to run good. Apart from this, there no
useful information for us to start with.
Re: consecutive executions [message #654146 is a reply to message #654145] Wed, 27 July 2016 08:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

we need a few more details.
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof
3) EXPLAIN PLAN
4) complete trigger code
Re: consecutive executions [message #654147 is a reply to message #654146] Wed, 27 July 2016 08:12 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Based on the information given, I would suggest "undo the change you made"
Re: consecutive executions [message #654185 is a reply to message #654147] Thu, 28 July 2016 02:14 Go to previous messageGo to next message
bibhuti037
Messages: 2
Registered: December 2014
Location: Chennai
Junior Member
Sorry .
Actually this is a production code. I cant put the whole code on this forum.

Just I need some information on which direction I should look,that will help me to resolve this performance issue.
When this process completes and start from first it is working fine for some iterations. Then again it is becoming so slow.
Previously it was taking 30 seconds, Now it was taken 53 mins for 1000 records.

I have added just two select queries to take count for one combination. If count there then take min(date) value for that combination.

Just help me on..on which area I should check.

Thanks in advance.
Re: consecutive executions [message #654189 is a reply to message #654185] Thu, 28 July 2016 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In your 2 new selects.

Re: consecutive executions [message #654191 is a reply to message #654185] Thu, 28 July 2016 02:24 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
bibhuti037 wrote on Thu, 28 July 2016 08:14
Sorry .
Actually this is a production code. I cant put the whole code on this forum.
'Anonymise' the code.

Quote:
Just I need some information on which direction I should look,that will help me to resolve this performance issue.
It would appear that the performance issue is due to the change that you made. That is the direction in which you should be looking.

Quote:
When this process
What process? We have no idea what your process does, how do you expect anyone to be able to comment on it from a performance perspective?
Quote:
completes and start from first it is working fine for some iterations. Then again it is becoming so slow.
As above.

Quote:
Previously it was taking 30 seconds, Now it was taken 53 mins for 1000 records.
See above.

Quote:
I have added just two select queries to take count for one combination.
And if you back those changes out, does that resolve the performance issue?
Quote:
If count there then take min(date) value for that combination.
I don't know what you're saying here, is that what the extra code does?

Quote:
Just help me on..on which area I should check.
The bit that you changed that seems to have caused the problem.

Thanks in advance.
[/quote]
Re: consecutive executions [message #654192 is a reply to message #654189] Thu, 28 July 2016 02:24 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Thu, 28 July 2016 08:20

In your 2 new selects.


Somewhat more succinct than my post Smile
Previous Topic: Compare spatial and attributive query
Next Topic: Approach for Oracle Profiles to tune queries
Goto Forum:
  


Current Time: Thu Mar 28 09:30:00 CDT 2024