OraFAQ Forum: Performance Tuning » unable to reduce the cost of the query

Home » RDBMS Server » Performance Tuning » unable to reduce the cost of the query (Oracle 10G /Linux)

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

unable to reduce the cost of the query [message #388536]

Wed, 25 February 2009 04:54

dhanamukesh
Messages: 51
Registered: January 2009

Member

SELECT a.name,
c.encryp_item_val, pkg_pci_cmmn_decrypt.fcn_get_decrypt_val_aes ('BAT', e.reference_nbr, e.order_date, 'RA') as card_nbr,e.*
FROM express_pay_order e, transaction_encryp_item c, agent a
WHERE e.reference_nbr = c.tran_ref_id
and e.rcv_agent_id = a.agent_id

The cost of the above said query would be 261753.I have tried to rearranged the where clause and then tried with index creation also.

But still, the cost is same.Please help me out to tune this query.

Here i have attached DDL of each of the tables.

Regards,
Dhanalakshmi

Attachment: DDL.txt
(Size: 2.17KB, Downloaded 1337 times)

Report message to a moderator

Re: unable to reduce the cost of the query [message #388539 is a reply to message #388536]

Wed, 25 February 2009 05:08

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Post Explan plan stat. report.

Report message to a moderator

Re: unable to reduce the cost of the query [message #388724 is a reply to message #388539]

Thu, 26 February 2009 00:34

dhanamukesh
Messages: 51
Registered: January 2009

Member

Please find the attached Explain Plan.

Attachment: dx8x2rqc5xnud.jpg
(Size: 157.19KB, Downloaded 936 times)

Report message to a moderator

Re: unable to reduce the cost of the query [message #388789 is a reply to message #388724]

Thu, 26 February 2009 04:12

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You're looking at all the rows of some quite large tables, and then performing a function on the results.
It's never going to be quick.

Have you got up to date stats on the tables?

Report message to a moderator

Re: unable to reduce the cost of the query [message #388861 is a reply to message #388789]

Thu, 26 February 2009 07:26

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Please post your explain plan stat. report.

SQL> set autotrace on explain statistics
SQL>

Execute your sql query.

[Updated on: Thu, 26 February 2009 07:26]

Report message to a moderator

Previous Topic:	Subpartitions number
Next Topic:	better way to write or tune a query with joins

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 18:25:15 CST 2024