Home » RDBMS Server » Performance Tuning » optimisation (oracle 10g)
optimisation [message #427169] Wed, 21 October 2009 07:07 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Below is the buffer_get and execution details for oracle database, which is time
consuming during
load.

BUFFER_GETS      139392

EXECUTIONS         1584

BUFFER_GETS/EXECUTIONS  
88 


Below is the query

SELECT DISTINCT
ECD.CONSULTANT_ID, ECD.CONSULTANT_NAME  
FROM EMRConsultantDetails ECD  
INNER JOIN Users USR ON
USR.USER_LOGIN = ECD.CONSULTANT_ID  
INNER JOIN EMRUserRoleLocation EURL ON EURL.USER_LOGIN =
ECD.CONSULTANT_ID  
WHERE  USR.USER_STATUS = 'active' AND EURL.LOCATION_ID = 1501 
ORDER BY
ECD.CONSULTANT_NAME



any other possibility of optimisation
Re: optimisation [message #427171 is a reply to message #427169] Wed, 21 October 2009 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rewrite it as
select dummy from dual;

Regards
Michel
Re: optimisation [message #427172 is a reply to message #427169] Wed, 21 October 2009 07:10 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
rajasekhar857 wrote on Wed, 21 October 2009 07:07

any other possibility of optimisation



Read the Performance Tuning sticky in the performance tuning section.


1. Make sure that statistics are upto date
2. Please provide the show plan for the sql
3. Also what are the indexes for the tables used in the sql
4. How many rows are there in each table


Thanks
Re: optimisation [message #427184 is a reply to message #427172] Wed, 21 October 2009 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With 400+ posts I don't think it is very useful to post once more the guide.

Regards
Michel
Re: optimisation [message #427188 is a reply to message #427184] Wed, 21 October 2009 08:21 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sir,
Yes it is.
I put the points just to tell that some informations still he needs to provide.

Re: optimisation [message #427191 is a reply to message #427188] Wed, 21 October 2009 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I meant is that he already knows the information that are required, needed and requested for such a question...

Regards
Michel
Re: optimisation [message #427194 is a reply to message #427169] Wed, 21 October 2009 08:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's 88 buffer gets per execution.
For a 3 table query, presumably using indexed lookups, and returning multiple duplicate rows (otherwise why would you need a distinct) that's probably not far off as good as you'll get.


You get more help when you've confirmed that the stats are up to date, posted and explain plan and detaqils of the indexes on the tables.
Re: optimisation [message #427783 is a reply to message #427169] Sun, 25 October 2009 09:18 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why do you think that that particular query is the cause of bad performance?
Re: optimisation [message #429800 is a reply to message #427169] Thu, 05 November 2009 17:53 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
try reformulating as two correlated subqueries. This will get rid of the distinct and remove the need to visit all rows in all three tables. You should see dramatic increase in performance as long as the correlated columns are indexed appropriately.

Kevin
Previous Topic: Optimizing the procedure
Next Topic: SQL Tune
Goto Forum:
  


Current Time: Sun Sep 25 23:14:08 CDT 2016

Total time taken to generate the page: 0.14080 seconds