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?
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
- 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.
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) */
---PR.PTY_STA_TYP---------=---LE.PTY_STA_TYP---AND ----- Added
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
---- 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
---- 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?
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.