Home » RDBMS Server » Performance Tuning » doubt in Performace tuning (Oracle 10G /Linux)
doubt in Performace tuning [message #387119] Wed, 18 February 2009 00:12 Go to next message
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 1406 times)

Report message to a moderator

Re: doubt in Performace tuning [message #387137 is a reply to message #387119] Wed, 18 February 2009 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask the one that added this undocumented function.
And what about checking explain plans?

Regards
Michel

[Updated on: Wed, 18 February 2009 00:43]

Report message to a moderator

Re: doubt in Performace tuning [message #387144 is a reply to message #387119] Wed, 18 February 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By the way:
SYS_OP_MAP_NONNULL(CONSUMER_TRAN.LYLTY_PGM_MBSHP_ID) is not null

is silly as SYS_OP_MAP_NONNULL cannot return null.

In addition, the 2 queries are not equivalent.
Don't use something you don't understand.

Regards
Michel

Report message to a moderator

Re: doubt in Performace tuning [message #387148 is a reply to message #387144] Wed, 18 February 2009 01:02 Go to previous messageGo to next message
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

Report message to a moderator

Re: doubt in Performace tuning [message #387161 is a reply to message #387148] Wed, 18 February 2009 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
One of the query i have raised earlier was replied by JRowBottom

This does not change the answer:
Quote:
Don't use something you don't understand.


Quote:
And result sets are also same for both original and tuned query.

You are (un)lucky.

Regards
Michel

Report message to a moderator

Re: doubt in Performace tuning [message #387164 is a reply to message #387161] Wed, 18 February 2009 01:56 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
what you are coming to convey ...

Report message to a moderator

Re: doubt in Performace tuning [message #387170 is a reply to message #387148] Wed, 18 February 2009 02:26 Go to previous messageGo to next message
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
, restricting the rowset to ones with a null value in LYLTY_PRM_MBSHP.

The second query has this line:
 AND  SYS_OP_MAP_NONNULL(CONSUMER_TRAN.LYLTY_PGM_MBSHP_ID) is not null
, which is always true.

Report message to a moderator

Re: doubt in Performace tuning [message #387175 is a reply to message #387170] Wed, 18 February 2009 03:06 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Those two queries are same only ...one is original query and the another one is tuned one...I want to know whether the steps followed by me is correct or not??? thats it...

Report message to a moderator

Re: doubt in Performace tuning [message #387177 is a reply to message #387175] Wed, 18 February 2009 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They are not.

Regards
Michel

Report message to a moderator

Re: doubt in Performace tuning [message #387184 is a reply to message #387177] Wed, 18 February 2009 03:37 Go to previous messageGo to next message
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

Report message to a moderator

Re: doubt in Performace tuning [message #387200 is a reply to message #387184] Wed, 18 February 2009 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel

Report message to a moderator

Re: doubt in Performace tuning [message #387246 is a reply to message #387175] Wed, 18 February 2009 08:03 Go to previous messageGo to next message
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)
but I'd never put something undocumented into production.
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.

Report message to a moderator

Re: doubt in Performace tuning [message #387370 is a reply to message #387246] Thu, 19 February 2009 00:48 Go to previous message
dhanamukesh
Messages: 51
Registered: January 2009
Member
JRowBottom,

Thank you very much..



Regards,
Dhanalakshmi.P

Report message to a moderator

Previous Topic: Hard Code values in sql vs lookup
Next Topic: Execution Plan (merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 01 22:08:14 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.