Home » RDBMS Server » Performance Tuning » Pls help in reducing the buffer gets for the query below
Pls help in reducing the buffer gets for the query below [message #191764] Fri, 08 September 2006 01:29 Go to next message
aarti.mishra
Messages: 1
Registered: September 2006
Location: India
Junior Member
Buffer gets with this query is 460K.
We want to reduce it drastically.

SELECT temp21.resource_code
,temp21.employee_type employee_type
,temp21.resource_name resource_name
,temp21.manager_id
,temp21.manager_name
,temp21.period_start
,temp21.period_finish
,temp21.avail_hours
,temp21.act_hours
,temp21.timesheet_status timesheet_status
,temp21.email,temp21.ouc
,temp21.work_country
,f.level1_unit_id level1_id
,f.level2_unit_id level2_id
,f.level3_unit_id level3_id
,f.level4_unit_id level4_id
,f.level5_unit_id level5_id
,f.level6_unit_id level6_id
,f.level7_unit_id level7_id
,f.level8_unit_id level8_id
,f.level9_unit_id level9_id
,f.level10_unit_id level10_id
,f.level1_name
,f.level2_name
,f.level3_name
,f.level4_name
,f.level5_name
,f.level6_name
,f.level7_name
,f.level8_name
,f.level9_name
,f.level10_name
FROM (SELECT avail.res_id
,avail.resource_code
,avail.employee_type
,avail.person_type
,avail.resource_name
,avail.manager_id
,avail.manager_name
,avail.period_start
,avail.period_finish
,avail.avail_hours
,NVL(act.act_hours,0) act_hours
,act.timesheet_status
,avail.prid
,avail.email
,avail.ouc
,avail.work_country
FROM (SELECT /*+ALL_ROWS*/r.id res_id
,lk.name employee_type
,r.unique_name resource_code
,r.person_type
,r.first_name||' '||r.last_name resource_name
,r.manager_id manager_id
,r1.first_name||' '||r1.last_name manager_name
,TRUNC(tp.prstart) period_start
,TRUNC(tp.prfinish - 1) period_finish
,NVL(o.gs_hrs_avail_week,0) avail_hours
,r.email
,o.ouc
,o.work_country
,tp.prid
FROM niku.ODF_CA_RESOURCE o
,niku.SRM_RESOURCES r
,niku.SRM_RESOURCES r1
,niku.PRTIMEPERIOD tp
,niku.CMN_SEC_USER_GROUPS usr_grp
,niku.CMN_SEC_GROUPS grp
,(SELECT
NLS.NAME
,LKP.ID
FROM CMN_CAPTIONS_NLS NLS,CMN_LOOKUPS LKP
WHERE NLS.PK_ID=LKP.ID
AND LKP.LOOKUP_TYPE='SRM_RESOURCE_TYPE'
AND NLS.TABLE_NAME='CMN_LOOKUPS'
AND NLS.LANGUAGE_CODE='en') lk
WHERE r.id=o.id
AND r1.user_id(+)=r.manager_id
AND r.user_id=usr_grp.user_id
AND usr_grp.GROUP_ID=grp.id
AND r.person_type=lk.id
AND r.is_active = 1
AND r1.is_active = 1
AND grp.GROUP_CODE='gs_tb'
AND tp.prisopen = 1
) avail
,(SELECT r.id
,tp.prid
,SUM(NVL(practsum,0)/3600) act_hours
,ts.prstatus timesheet_status
FROM niku.SRM_RESOURCES r
,niku.PRTIMESHEET ts
,(
SELECT /*+ALL_ROWS*/ MAX(prid) prid
,prtimeperiodid
,prresourceid
FROM niku.prtimesheet
GROUP BY prtimeperiodid,prresourceid
) ts_new
,niku.PRTIMEENTRY te
,niku.PRTIMEPERIOD tp
,niku.CMN_SEC_USER_GROUPS usr_grp
,niku.CMN_SEC_GROUPS grp
WHERE ts.prid=ts_new.prid
AND ts.prtimeperiodid=ts_new.prtimeperiodid
AND ts.prresourceid=ts_new.prresourceid
AND r.id=ts.PRRESOURCEID
AND ts.PRID=te.PRTIMESHEETID
AND ts.PRTIMEPERIODID=tp.prid
AND usr_grp.USER_ID=r.USER_ID
AND grp.id=usr_grp.group_id
AND r.is_active=1
AND tp.PRISOPEN=1
AND ts.prstatus not in(0,2,5)
AND grp.group_code='gs_tb'
AND TRUNC(tp.PRSTART) >= TRUNC(TO_DATE('7/24/2006','MM/DD/YYYY HH:MI:SS AM'))
AND TRUNC(tp.PRFINISH-1) <= TRUNC(TO_DATE('9/25/2006','MM/DD/YYYY HH:MI:SS AM'))
GROUP BY r.ID
,tp.PRID
,ts.prstatus) act
WHERE act.id(+) = avail.res_id
AND act.prid(+) = avail.prid
AND (avail.avail_hours - NVL(act.act_hours,0) > 0))temp21
,prj_obs_associations o1
,nbi_dim_obs f
WHERE 1=1
AND temp21.prid in (SELECT prid
FROM PRTIMEPERIOD
WHERE TRUNC(PRSTART) >= TRUNC(TO_DATE('7/24/2006','MM/DD/YYYY HH:MI:SS AM'))
AND TRUNC(PRFINISH-1) <= TRUNC(TO_DATE('9/25/2006','MM/DD/YYYY HH:MI:SS AM'))
)
AND
temp21.res_id = o1.record_id
AND o1.unit_id = f.obs_unit_id
AND o1.table_name = 'SRM_RESOURCES'
AND f.obs_type_id = 5000009
AND f.level5_unit_id = 5013334
ORDER BY temp21.manager_name
,temp21.manager_id
,temp21.resource_name
,temp21.resource_code
,temp21.period_start
,temp21.period_finish
,temp21.timesheet_status
Re: Pls help in reducing the buffer gets for the query below [message #192012 is a reply to message #191764] Sat, 09 September 2006 14:28 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Huge select Wink
Nothing else can be seen from Your post.
We have no idea about what indexes You have, size of the objects, execution plan, Your data distribution etc.

The problem is likely, that it is going through the wrong indexes or going the wrong way through Your tables.

Have You gathered statistics recently?

Version of Oracle?

Br
Kim Anthonisen
Re: Pls help in reducing the buffer gets for the query below [message #195633 is a reply to message #191764] Fri, 29 September 2006 16:05 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Come on Santia.

Try to avoid more joins. check indexes. use only indexed fields.

Really?

So do You index every field You use in the where clause?
- hope not!

Check indexes - for what?

Avoid more joins???
- Too few joins could be the problem too!
- Oracle are built for joins (*R*dbms)!

But yes - execution plan - a good place to start Smile

Kim
Re: Pls help in reducing the buffer gets for the query below [message #195635 is a reply to message #195633] Fri, 29 September 2006 17:44 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Sathia wrote on Fri, 29 September 2006 17:05

Using joins is not a good thing. If its necessary we can use it until we find some other efficient way.

Indexes - same. If possible use more indexed fields. If you check the index part , if you try to give more indexes then its better.

I have to agree with Kim.

Joins are not always avoidable AND Indexes are not always good.
Re: Pls help in reducing the buffer gets for the query below [message #195638 is a reply to message #195635] Fri, 29 September 2006 18:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To these sparkling gems of wisdom ("use fewer joins" and "build more indexes"), I would add one more crucial rule:
Ignore bad advice



I assume that you want to reduce Buffer Gets because your DBA told you to, not because you have identified that the query actually processes far more data that it has to.

So, why do you think the Buffer Gets is too high? Is it because you are providing highly constraining predicates and joining on Unique keys? You have not provided enough information.

You need to do two things:
  • Run the SQL under SQL*Trace and process the trace file through TK*Prof. Post the TK*Prof output for the statement here, including the Explain Plan. Instruction for this are in the Performance Tuning Manual.
  • Edit your original post and enclose the SQL in [code] and [/code] tags so that we can read it. Do the same with the TK*Prof output.

If you do BOTH of these things, then the quality of advice you get might start to improve.

Ross Leishman
Re: Pls help in reducing the buffer gets for the query below [message #195794 is a reply to message #195638] Mon, 02 October 2006 08:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And some 'less wise' people merely regurgitate erroneous opinions that they have heard and never actually tested for themselves.

I'm sure it will be easy for you to provide us with a simple, concise example supporting your allegation that it it quicker to get data back by using less joins, and that more indexes always make things quicker.
Re: Pls help in reducing the buffer gets for the query below [message #195820 is a reply to message #195794] Mon, 02 October 2006 11:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
EBrian/JRowbottom stated,
>>Joins are not always avoidable AND Indexes are not always good.
Period. Enough said.

And the one-who-does-not-like-spoon-feeding says

>> some wise people can tell only after getting all the basic information just like spoon feeding.
>> "give your trace log . I will tell the problems". This is not a good answer from matured wise experianced programmers

What else is the good answer?
I am not a wise/good/experienced programmer. I do not do programming for a living.
Could someone educate me?

The last time i checked (A week before to be precise) Oracle support asked for trace files.
How dare they? (immature/inexpereinced/unwise folks @OSS).
Next time, i will send my astrological/natal chart/horoscope.
logfiles/tracefiles stinks.

[Updated on: Mon, 02 October 2006 13:26]

Report message to a moderator

Re: Pls help in reducing the buffer gets for the query below [message #203574 is a reply to message #191764] Wed, 15 November 2006 10:11 Go to previous message
skn
Messages: 6
Registered: September 2006
Junior Member
Acting on this private message from @Sathia
Quote:
Can you pls. remove my answers for the below mentioned question in performance page?
"Pls help in reducing the buffer gets for the query below


I am deleting all the responses.
Mahesh Rajendran

[Updated on: Wed, 15 November 2006 11:10] by Moderator

Report message to a moderator

Previous Topic: I need a listing of Oracle indexes and how often they are used, how?
Next Topic: Interpret STATSPack Report
Goto Forum:
  


Current Time: Thu Dec 08 03:57:54 CST 2016

Total time taken to generate the page: 0.06682 seconds