Home » RDBMS Server » Performance Tuning » unable to reduce the cost of the query (Oracle 10G /Linux)
unable to reduce the cost of the query [message #388536] Wed, 25 February 2009 04:54 Go to next message
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 131 times)
Re: unable to reduce the cost of the query [message #388539 is a reply to message #388536] Wed, 25 February 2009 05:08 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Post Explan plan stat. report.
Re: unable to reduce the cost of the query [message #388724 is a reply to message #388539] Thu, 26 February 2009 00:34 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Please find the attached Explain Plan.
Re: unable to reduce the cost of the query [message #388789 is a reply to message #388724] Thu, 26 February 2009 04:12 Go to previous messageGo to next message
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?
Re: unable to reduce the cost of the query [message #388861 is a reply to message #388789] Thu, 26 February 2009 07:26 Go to previous message
babuknb
Messages: 1734
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:
  


Current Time: Sun Dec 11 04:00:18 CST 2016

Total time taken to generate the page: 0.16727 seconds