Home » RDBMS Server » Performance Tuning » Performance issue in Plsql (oracle10g)
Performance issue in Plsql [message #417419] Sat, 08 August 2009 02:06 Go to next message
Messages: 58
Registered: March 2009
One of my procedure contains the following sql query which takes along time to fetch the result. Can we tune it anyway

select orv.vendor, orv.loc_code as dc, orv.transport_mode from od_routing_v orv , trans_mode tm where tm.trans_desc = orv.transport_mode and tm.trans_mode_key > 5
select l1.loc_desc as vendor, l2.loc_desc as dc, tm.trans_desc from trans_lead_time tlt, location l1, location l2, trans_mode tm where tlt.source_location_key = l1.location_key and tlt.dest_location_key = l2.location_key and tlt.trans_mode_key = tm.trans_mode_key and tlt.coalition_key = 1 and tm.trans_mode_key > 5

[Updated on: Sat, 08 August 2009 02:08] by Moderator

Report message to a moderator

Re: Performance issue in Plsql [message #417421 is a reply to message #417419] Sat, 08 August 2009 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
- Tuning High-Volume SQL Wiki page

Then provide the required and usual information.

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

Re: Performance issue in Plsql [message #417448 is a reply to message #417419] Sat, 08 August 2009 21:08 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If one or both of those row sources is very large, then you might find restructuring the query as a NOT IN () subquery will give a big improvement. This is because a NOT IN subquery can be resolved (sometimes) as a HASH join, whereas MINUS requires a sort of both row sources.

If you try the NOT IN subquery, you MUST, MUST, MUST ensure that the join columns on both the outer query and the subquery are non-nullable. This can be done with NOT NULL constraints on the database or with AND col IS NOT NULL clauses in the SQL. It is not sufficient for the columns to simply contain non-null values - it must be enforced, otherwise Oracle will not use a HASH join. Run your query through Explain Plan - if you don't see the keyword HASH JOIN ANTI (the ANTI is essential), don't bother running it.

Post your new query here if you need help. This time use CODE tags to format the query, otherwise we cannot read it properly.

Ross Leishman
Previous Topic: Parallelism disabled in data warehouse
Next Topic: ORA-600 and ORA-7445 error in Oracle 10g R2
Goto Forum:

Current Time: Sat Dec 10 03:17:55 CST 2016

Total time taken to generate the page: 0.09779 seconds