Solved

Long Running Query in Oracle

Posted on 2011-03-09
18
423 Views
Last Modified: 2012-06-27
I am trying to run a query in Oracle 10g,  when I use my ID the query takes hours and eventually will error out.  I have had other users try the same query and the query finished in under 1 minute.  I have compared that we are all running the same client, etc ... .  

Any suggestions as to what may be causing the query to run so long for my ID or something else I could check?

Thanks
0
Comment
Question by:Rhonda Carroll
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +1
18 Comments
 
LVL 6

Expert Comment

by:anumoses
ID: 35084171
You as the user, do you have previleges to run this query?
0
 

Author Comment

by:Rhonda Carroll
ID: 35084272
I have read access to all the tables that are in the query.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35084429
Are you sure the different users are selecting from the exact same tables in the exact same databases?

What is the error when it times out?

0
Industry Leaders: 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!

 

Author Comment

by:Rhonda Carroll
ID: 35084483
I have sent the users the query, so they are using the same query.  I am not sure if they have a copy of the tables stored somewhere that they are accessing but we have discussed it and it didn't come up.  I haven't let it run in a while so I don't have the exact error message
0
 

Author Comment

by:Rhonda Carroll
ID: 35084492
the user ID that I am using is used for all reporting and is a generic read only ID ... could that be the problem.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35084688
A readonly id should not causes any problem.

See if you can generate an execution plan for the query.  If so, please post the results.

explain plan for
select ... the rest of your query

If that works, please post the results of:
select * from table(dbms_xplan.display);
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35084697
If you cannot generate a plan, I suggest you work directly with your local DBAs to help troubleshoot this porblem.
0
 

Author Comment

by:Rhonda Carroll
ID: 35084707
I will try that ... and post my findings but it might be a little while before I get back with it depending on how it runs.

Thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35084749
explain plan runs in seconds.  It never actually executes the select.
0
 

Author Comment

by:Rhonda Carroll
ID: 35084790
ok ... I am not familiar with any oracle commands
0
 

Author Comment

by:Rhonda Carroll
ID: 35084874
Plan is attached
-Plan.xls
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35085094
do you have FGAC policies on any of the objects?
or are there other function calls that may behave differently depending on the user calling them?
what about triggers?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35085110
does each user have a private db links? If so, are you all going to the same remote db and running the same query there with the same privileges?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35085726
Nothing in that plan jumps out at me that it should take hours to run.  Granted, it isn't very pretty and I would look to see if I could simplify the select that generated it but it shouldn't take hours.

As sdstubers hinted at:  I don't think you are hitting the same data as the other users.

You really should check with your DBA's and/or developers to make sure you are running against the exact same data as the other users.

You might also have one of the other people that runs this in seconds, generate a plan and upload it to us.  That should help us determine if the data appears to be the same.
0
 

Author Closing Comment

by:Rhonda Carroll
ID: 35086075
Thanks I will contact our local DBA's to see if there is something we can find there.

Thanks again for your help.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35086148
isn't a split in order?
0
 

Author Comment

by:Rhonda Carroll
ID: 35086206
Sorry about that but slightvw did give me the assistance regarding the plan, how to run the plan, and then looked at the plan and provided feedback
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35086293
ok, if that's all you were looking for, then no problem.  

but you didn't select the post where he gave plan advice, you selected the one where he referenced my posts, so it seemed like a split should have been applied
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select and Insert Query running slow 4 58
make null the repeated levels 2 37
Read XML values 8 56
Migration from sql server to oracle 5 38
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

733 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