Home » RDBMS Server » Performance Tuning » Performance question (Oracle 9.2.0.3)
Performance question [message #411701] Mon, 06 July 2009 06:39 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
A query in PRODUCTION giving performance issue but the same sql is running quite well in test environment.
The databse is same in both the environment. But in test environment there are less data.Also, there is difference in Production vs Test environment in terms of memory, data etc..

So how to ensure that the sql I write will have no performance issues in Production env.

Many thanks...
Re: Performance question [message #411704 is a reply to message #411701] Mon, 06 July 2009 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So how to ensure that the sql I write will have no performance issues in Production env.

If you know/get a recipe for this then you will be billionaire before long.

Regards
Michel
Re: Performance question [message #411709 is a reply to message #411704] Mon, 06 July 2009 06:57 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Quote:
If you know/get a recipe for this then you will be billionaire before long.


Excellent !!! Smile

[Updated on: Mon, 06 July 2009 06:59]

Report message to a moderator

Re: Performance question [message #411745 is a reply to message #411709] Mon, 06 July 2009 09:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There's two things in play here:

1. Your query may not scale. It may seem to work OK on small volumes but just doesn't cope with large volumes.

2. Oracle can CHANGE the way it executes a query depending on many system and data factors. This is usually for the better, but sometimes for the worse.

You need to find out whether you have crap SQL (1 above) or good SQL that Oracle is performing badly (2 above).

Run an explain plan on both environments - or better yet - a SQL Trace and TK*Prof - and compare them. Post them here if you are unsure.

Ross Leishman
Re: Performance question [message #411818 is a reply to message #411745] Tue, 07 July 2009 00:43 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I heard someone telling that we can bring production statistics on test environment...is it? dont know how! if its true is there any doc/link for that ?
Re: Performance question [message #411823 is a reply to message #411818] Tue, 07 July 2009 01:07 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then? What do you expect exporting test statistics into production database but fooling the optimizer and getting the worst possible execution plan?

Regards
Michel
Previous Topic: query_rewrite_enabled
Next Topic: 9i -> 10g causes JAVA performance problems
Goto Forum:
  


Current Time: Thu Dec 08 08:10:03 CST 2016

Total time taken to generate the page: 0.09507 seconds