Home » RDBMS Server » Performance Tuning » high cost (oracle 10g,TOAD)
high cost [message #334699] Thu, 17 July 2008 13:25 Go to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member


hi oracle experts,

i have found this query having high cost.can anybody let me khow how to tune this query?

what are the steps required to reduce the cost of a query?




SELECT POLH_SYS_ID , POLH_END_NO_IDX , POLH_END_SR_NO FROM testlive WHERE NVL(POLH_NO , :"SYS_B_0" ) = NVL(:1 , :"SYS_B_1" ) AND NVL(POLH_END_NO , :"SYS_B_2" ) = NVL(:1 , :"SYS_B_3" )


Operation Object Name Rows Bytes Cost

SELECT STATEMENT Hint=ALL_ROWS 1 170164
PARTITION LIST ALL 1 ; 39 170164
TABLE ACCESS FULL PGITH_POLICY 1 39 170164



Regards,
Balaji



Re: high cost [message #334700 is a reply to message #334699] Thu, 17 July 2008 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions in URL above have you tried & what were the results?
Re: high cost [message #334716 is a reply to message #334699] Thu, 17 July 2008 14:18 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
are your table stats upto date
when was the last time you analyzed index/tables

regards

Alan
Re: high cost [message #334758 is a reply to message #334716] Thu, 17 July 2008 17:22 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

last analysed for table /index -- 12/07/2008 @11:25pm

how to check my table stats are up to date?

[Updated on: Thu, 17 July 2008 17:25]

Report message to a moderator

Re: high cost [message #334800 is a reply to message #334758] Fri, 18 July 2008 01:12 Go to previous messageGo to next message
rsoma
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member
If you are using TOAD, then you can see in the table properties under stat tab.
Re: high cost [message #334949 is a reply to message #334800] Fri, 18 July 2008 17:17 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

yes my table stats are up to date.

Do i need to create index ?
Re: high cost [message #334950 is a reply to message #334699] Fri, 18 July 2008 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
The (ab)use of functions, like NVL, preclude the use of the index.
You have to live with the Full Table Scan.
Re: high cost [message #334961 is a reply to message #334950] Fri, 18 July 2008 19:10 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

The (ab)use of functions, like NVL, preclude the use of the index.

can you tell me why to preclude the use of index when NVL function is used in a query?


[Updated on: Fri, 18 July 2008 19:12]

Report message to a moderator

Re: high cost [message #334962 is a reply to message #334699] Fri, 18 July 2008 19:29 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
The short answer is FUNCTION(column) is NOT indexed.
Re: high cost [message #335022 is a reply to message #334962] Sat, 19 July 2008 09:58 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

i know, the function of the column is not indexed.thats y im asking u, should i have to create index on that ?if no why?
Re: high cost [message #335023 is a reply to message #335022] Sat, 19 July 2008 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about following the guidelines?

Regards
Michel
Re: high cost [message #335184 is a reply to message #334699] Mon, 21 July 2008 06:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't create a function based index on the functions that you are using.

If your query had a clause in it like
AND NVL(POLH_NO,'XYZ')=  NVL(:1 , :"SYS_B_1" )
then you could create a function based index on NVL(POLH_NO,'XYZ').

As it is, it looks like you're passing in a bind variable the NVL statement, so there is no way of creating in index on it as you do not know in advance what the value for all null columns should be.
Re: high cost [message #335754 is a reply to message #335184] Wed, 23 July 2008 10:28 Go to previous message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

anyway thanks for your information
Previous Topic: Avoiding : ORA-00470: LGWR process terminated with error
Next Topic: Is possible to turn the following into a view
Goto Forum:
  


Current Time: Mon Dec 05 21:35:02 CST 2016

Total time taken to generate the page: 0.21218 seconds