Hi,
I have the following sql to be tuned. The buffer gets should not be greater than 2000. I put 'ORDERED' hint and index hints. The buffer_gets is 7975. It was 3337346 without the hints.

How can I reduce the buffer gets to 2000?

Please help.





SELECT /*+ORDERED index(LE LEGAL_ENTITY_NDX_1) index(PR PK_PARTY_ROLE) */

P.PTY_ID,LE.PTY_Id,PR.PTY_ID,P.PTY_STA_TYP,LE.PTY_STA_TYP,PR.PTY_STA_TYP,

PR.PTY_ROLE_CATG_TYP,PR.PTY_ROLE_TYP,

P.PTY_TYP,P.PTY_NAM,P.PTY_SHRT_NAM,P.PTY_DES,P.ADR_LNE_1,P.ADR_LNE_2,P.ADR_LNE_3,P.ADR_LNE_4

,P.ISO_CTRY_COD,LE.SIC_COD,P.VERSION_NO,LE.CUST_REF_ID,LE.CUST_REF_TYP,LE.LGL_NAM
FROM b19991112qc31.LEGAL_ENTITY LE,b19991112qc31.PARTY_ROLE PR,b19991112qc31.PARTY P
WHERE ( LE.PTY_ID = P.PTY_ID AND
LE.PTY_STA_TYP = P.PTY_STA_TYP
AND PR.PTY_ID = P.PTY_ID
AND PR.PTY_STA_TYP = P.PTY_STA_TYP
AND PR.PTY_ROLE_CATG_TYP = 'CUST'
AND PR.PTY_ROLE_TYP = 'LGLBU'
AND P.PTY_TYP = 'LGLU'
AND LE.CUST_REF_TYP = 'LIABNO' )