| doubt in Performace tuning [message #387119] |
Wed, 18 February 2009 00:12  |
dhanamukesh
Messages: 51
Registered: January 2009
|
Member |
|
|
I have tuned one of the sql query using SYS_OP_MAP_NONNULL (instead of NULL) and first_rows hint.And i have seen the cost
got reduced a lot from the original cost.
i.e Original cost is somewhere around 90005 and after tuning the cost reduced to 30.I want to know whether i have used the correct tuning steps or not..
for your investigation,
I have attached both the original and tuned query.
Please let me know your suggestions.
I want to know exact functionality of the function SYS_OP_MAP_NONNULL
Regards,
Dhanalakshmi.P
-
Attachment: queries.txt
(Size: 1.10KB, Downloaded 1520 times)
|
|
|
|
|
|
|
|
| Re: doubt in Performace tuning [message #387148 is a reply to message #387144] |
Wed, 18 February 2009 01:02  |
dhanamukesh
Messages: 51
Registered: January 2009
|
Member |
|
|
One of the query i have raised earlier was replied by JRowBottom used this function.thats why i tried with this...
And result sets are also same for both original and tuned query.
regards,
Dhanalakshmi.P
|
|
|
|
|
|
|
|
| Re: doubt in Performace tuning [message #387170 is a reply to message #387148] |
Wed, 18 February 2009 02:26 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What am I being blamed for now?
@Michel is completely right - your queries are very different.
The first query has this line:AND CONSUMER_TRAN.LYLTY_PGM_MBSHP_ID is null
The second query has this line: AND SYS_OP_MAP_NONNULL(CONSUMER_TRAN.LYLTY_PGM_MBSHP_ID) is not null
|
|
|
|
|
|
|
|
| Re: doubt in Performace tuning [message #387184 is a reply to message #387177] |
Wed, 18 February 2009 03:37 |
dhanamukesh
Messages: 51
Registered: January 2009
|
Member |
|
|
Leave all the above..I will send you the original query for you..tell me the tuning steps for me .
Query is
SELECT CONSUMER_TRAN.TRAN_REF_ID
, CONSUMER_TRAN.SND_TRAN_DATE
, CONSUMER_MERGE_XREF_S.PRM_CNSMR_ID
FROM
CONSUMER_TRAN
, CONSUMER_MERGE_XREF_S
WHERE
CONSUMER_MERGE_XREF_S.CNSMR_ID = CONSUMER_TRAN.SND_CNSMR_ID
AND CONSUMER_MERGE_XREF_S.PRM_CNSMR_ID <> CONSUMER_MERGE_XREF_S.CNSMR_ID
AND CONSUMER_TRAN.LYLTY_PGM_MBSHP_ID IS NULL
regards
Dhanalakshmi.P
|
|
|
|
|
|
| Re: doubt in Performace tuning [message #387246 is a reply to message #387175] |
Wed, 18 February 2009 08:03 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: | Those two queries are same only ...one is original query and the another one is tuned one
|
They are not the same - they are capable of returning different sets of data.
Without any details of the explain plans for the query, or the indexes available on the tables, this is mostly firing blind.
Make sure you've got an index on CONSUMER_MERGE_XREF_S (CNSMR_ID,PRM_CNSMR_ID)
If you want to mess about with undocumented features, you could create an index on CONSUMER_TRAN (SND_CNSMR_ID,sys_op_man_nonnull(LYLTY_PGM_MBSHP_ID),CNSMR_ID ) and rewrite the query as:...
WHERE
CONSUMER_MERGE_XREF_S.CNSMR_ID = CONSUMER_TRAN.SND_CNSMR_ID
AND CONSUMER_MERGE_XREF_S.PRM_CNSMR_ID <> CONSUMER_MERGE_XREF_S.CNSMR_ID
AND sys_op_map_nonnull(CONSUMER_TRAN.LYLTY_PGM_MBSHP_ID) = sys_op_map_nonnull(null)
I'd create the index on ...,nvl(lylty_pgm_mbshp_id,-9999) where -9999 is a value that the column can never hold, and use the NVL in the query instead.
Then I'd comment the query heavily to make sure that other developers know what's going on.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
