-
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' )
-
Sorry, but as much as I love tuning SQL, I would need a whole lot more information to be of any use here. It looks like you've done a great job of tuning so far. You have reduced the buffer get count to 1/418th of what it was - nicely done. Why torture yourself over another 1/4? Where did this target number come from anyway?
- Chris
-
Well, is it possible to reduce it more? The target number is set by the Lead DBA.
Please help.
Thanks.
-
My disclaimer from my first reply still holds. 'Gross' tuning of SQL is not so hard. In order to generally tune a statement, one needs to know the
- statement
- plan
- table sizes
- abailable indexes
... and that is just to generally tune the statement, which you have already done.
To *fine* tune the statement, I need the database. There's no other way around it.
So, unfortunately, as I said the first time, there's precious little I can do for you.
Again, however, I re-state that you have done a fine job. Your lead dba needs to have his head checked. :) Setting arbitrary goals is kinda pointless.
However, if he/she is trying to 'teach' you how to optimize, then that is a different story. I do the same thing with the people I teach. I figure out the best path and then give them a goal to shoot for. If this is the case, good luck.
If your lead dba has *not* already determined how to optimize this statement, the he/she may be pulling numbers out of thin air and this may *not* actually be an attainable goal.
In any case, I doubt that I can help you. However, I am willing to give it a shot if you want to take the time to provide me with *every* piece of info you have - indexes, table sizes, distribution, histograms, plans - *anything* that could help. Simply providing the statement is wholly insufficient.
- Chris
-
Okay, this has been tugging at me brain, so here are a few thoughts...
First of all, always join everything that can be joined. Here is your statement with some minor changes:
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.CUST_REF_TYP------=---'LIABNO'---------AND
---PR.PTY_ID------------=---LE.PTY_ID------AND----- Added
---PR.PTY_STA_TYP---------=---LE.PTY_STA_TYP---AND ----- Added
---PR.PTY_ROLE_CATG_TYP---=---'CUST'---------AND
---PR.PTY_ROLE_TYP------=---'LGLBU'---------AND
---P.PTY_ID---------------=---LE.PTY_ID------AND
---P.PTY_STA_TYP---------=---LE.PTY_STA_TYP---AND---
---P.PTY_ID---------------=---PR.PTY_ID------AND
---P.PTY_STA_TYP---------=---PR.PTY_STA_TYP---AND
---P.PTY_TYP------------=---'LGLU'
Okay, your best options are most likely:
1- LE accessed via index on CUST_REF_TYP
2- Nested Loop join to PR with index on PTY_ID, PTY_STA_TYP, PTY_ROLE_CATG_TYP, PTY_ROLE_TYP
3- Nested Loop join to P with index on PTY_ID, PTY_STA_TYP, PTY_TYP
This assumes
---- Step 1 is the most restrictive step and is very restrictive
---- The predicate restrictions on PR do not cut the result set down as much as joing to LE would
------- Same with P
---- All the specified indexes exist
Another option is
1- LE accessed via index on CUST_REF_TYP
2- PR accessed via index on PTY_ROLE_CATG_TYP, PTY_ROLE_TYP
3- These results are hash-joined together via common fields: PTY_ID, PTY_STA_TYP
4- P accessed via index on PTY_TYP
5- These results are has-joined together via common fields: PTY_ID, PTY_STA_TYP
This assumes
---- Step 1 and 2 are simliarly-restrictive but not highly restrictive
---- The indexes mentioned in the first solution do not exist
---- The indexes mentioned in the second solution *do* exist :)
Any combination of NLs and Hash joins are possible. Also, the order can change.
Personally, I would look into the hash join option.
It is worrisome that you had to hint the statement to use the indexes.
Why didn't the optimizer pick the indexes?
Is your data severely skewed?
Do you have histograms?
Should you have histograms?
Are all your stats current?
Can you use better indexes?
Anyway, things to think about.
Hope this helps,
- Chris
-
Hi,
I really appreciate the effort taken by you. Here is the info:
The following are the indexes on table Legal_entity :
1. LEGAL_ENTITY_NDX_1 :
CUST_REF_ID
CUST_REF_TYP
2. LEGAL_ENT_LEGAL_ENT_FK :
INC_PTY_ID
3. PK_LEGAL_ENTITY (unique):
PTY_ID
PTY_STA_TYP
4. SIC_TBL_LEGAL_ENTITY_FK
SIC_COD
The foll. are the indexes on table Party:
1. COUNTRY_PARTY_FK
ISO_CTRY_COD
2. CURRENCY_PARTY_FK
ISO_CCY_COD
3. PARTY_NDX_3
PTY_NAM
PTY_STA_TYP
PTY_TYP
4. PARTY_NDX_4
PTY_ID
PTY_STA_TYP
PTY_TYP
5. PARTY_NDX_5
REF_KEY
6. PARTY_NDX_6
PTY_TYP
7. PK_PARTY(unique)
PTY_ID
PTY_STA_TYP
8. PTY_JUNK
PTY_SHRT_NAM
The foll. are the indexes on Party_role table:
1. FK_PARTY_PARTY_ROLE
PTY_ID
PTY_STA_TYP
2. PARTY_ROLE_NDX_1
WL_CUST_NO
3. PARTY_ROLE_NDX_3
PTY_ROLE_CATG_TYP
4. PK_PARTY_ROLE (unique)
PTY_ID
PTY_ROLE_TYP
PTY_STA_TYP
If you need any more info, please let me know. Also, the sql was given to me wihtout the hint and the ORDERED clause. So, if you want to start from scratch, ie. start tuning the sql from the beginning, it's okay.
If i need to create nay indexes, let me know. The only things is that I will not be able to test it in the same environment if a new index is created as the new index will be created in the DEvelopment environment and this is QC envt.
Thanks a lot.
