Query taking very long time to run

Posted on 2004-11-13
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  a,

     tab2 b

where a.key1=b.key2

and program_id=1;

It takes hours to run.
Thanks in advance.


Question by:pooja74
    LVL 6

    Accepted Solution

    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 database, and execute the query withotu the 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
    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  a,

         tab2 b

    where a.key1=b.key2

    and program_id=1;
    LVL 6

    Expert Comment

    Another alternative would be to create a local copy of 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.
    LVL 1

    Assisted Solution

    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
    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now