OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » high cost (oracle 10g,TOAD)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

high cost [message #334699]

Thu, 17 July 2008 13:25

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

Report message to a moderator

Re: high cost [message #334700 is a reply to message #334699]

Thu, 17 July 2008 13:29

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: high cost [message #334716 is a reply to message #334699]

Thu, 17 July 2008 14:18

alanm
Messages: 284
Registered: March 2005

Senior Member

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

regards

Alan

Report message to a moderator

Re: high cost [message #334758 is a reply to message #334716]

Thu, 17 July 2008 17:22

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

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.

Report message to a moderator

Re: high cost [message #334949 is a reply to message #334800]

Fri, 18 July 2008 17:17

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

yes my table stats are up to date.

Do i need to create index ?

Report message to a moderator

Re: high cost [message #334950 is a reply to message #334699]

Fri, 18 July 2008 17:22

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: high cost [message #334961 is a reply to message #334950]

Fri, 18 July 2008 19:10

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The short answer is FUNCTION(column) is NOT indexed.

Report message to a moderator

Re: high cost [message #335022 is a reply to message #334962]

Sat, 19 July 2008 09:58

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?

Report message to a moderator

Re: high cost [message #335023 is a reply to message #335022]

Sat, 19 July 2008 10:00

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What about following the guidelines?

Regards
Michel

Report message to a moderator

Re: high cost [message #335184 is a reply to message #334699]

Mon, 21 July 2008 06:25

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.

Report message to a moderator

Re: high cost [message #335754 is a reply to message #335184]

Wed, 23 July 2008 10:28

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

anyway thanks for your information

Report message to a moderator

Previous Topic:	Avoiding : ORA-00470: LGWR process terminated with error
Next Topic:	Is possible to turn the following into a view

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 22:28:05 CST 2024