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  |
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 #195638 is a reply to message #195635] |
Fri, 29 September 2006 18:44  |
rleishman
Messages: 3728
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 |
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 |
 |
Mahesh Rajendran
Messages: 10708
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 |
|
|
|
|
|
Goto Forum:
Current Time: Wed Nov 27 05:43:59 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
