| optimisation [message #427169] |
Wed, 21 October 2009 07:07  |
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 #427172 is a reply to message #427169] |
Wed, 21 October 2009 07:10  |
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 #427194 is a reply to message #427169] |
Wed, 21 October 2009 08:59 |
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 #429800 is a reply to message #427169] |
Thu, 05 November 2009 17:53 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
