Learn how to a build a cloud-first strategyRegister Now


Query taking very long time to run

Posted on 2004-11-13
Medium Priority
Last Modified: 2008-01-09
My following query is taking very long time to run.Please suggest the ways to tune this query.
Key1 and key2 are indexed columns.tab2 is a partitioned table.tab1 is in other schema.





from tab1@mdm.com  a,

     tab2 b

where a.key1=b.key2

and program_id=1;

It takes hours to run.
Thanks in advance.


Question by:pooja74
  • 2

Accepted Solution

morphman earned 172 total points
ID: 12576190
Ok, you are using a database link to retreive your data. First you must check if the query still takes ages to run on the source system.

Log into mdm.com database, and execute the query withotu the @mdm.com part. If its fast, you are out of luck, as it means yoru database link is slow normally because of slow network connections.

If it still runs slow, there can eb many things that make it run slow.

1. How many rows are in tab1@mdm.com
2. How many rows are in tab2
3. Is program_id indexed
4. What fields are tab2 partitioned on.

Run an explain plan, if there are any types of nested loops here, that is very bad over a network link, as every row will be read on 1 database, and then the other database will be checked over the network link. You need to USE_HASH the remote table if possible. This will only incur 1 network round trip (or just a few) compared to one per row for nested loop operations.


select /*+ USE_HASH(a) */



from tab1@mdm.com  a,

     tab2 b

where a.key1=b.key2

and program_id=1;

Expert Comment

ID: 12576200
Another alternative would be to create a local copy of tab1@mdm.com before running this query (via materialized views or any other replication technique), and then you can query 2 tables on the same database which will obviosly be much quicker.

Assisted Solution

tomcat75 earned 164 total points
ID: 12579166
For optimizing this query you can also try using the driving_site hint :

"SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, dept@rsite
 WHERE emp.deptno = dept.deptno;"

for oracle to execute the query on the site where you have the largest table.


LVL 13

Assisted Solution

by:Murali Murugesan
Murali Murugesan earned 164 total points
ID: 12592133
Create a Synonym for the table in other schema.
Y cant u use a materialized view for this purpose  i hope that would help u.
Normally for referring another table in a different schema it may take some time.
Just execute the explain plan for ur query and get to know where it takes more time.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question