Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Long Running Query in Oracle

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
Rhonda Carroll
Asked:
Rhonda Carroll
  • 8
  • 5
  • 4
  • +1
1 Solution
 
anumosesCommented:
You as the user, do you have previleges to run this query?
0
 
Rhonda CarrollAuthor Commented:
I have read access to all the tables that are in the query.
0
 
slightwv (䄆 Netminder) Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Rhonda CarrollAuthor Commented:
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
 
Rhonda CarrollAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
If you cannot generate a plan, I suggest you work directly with your local DBAs to help troubleshoot this porblem.
0
 
Rhonda CarrollAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
explain plan runs in seconds.  It never actually executes the select.
0
 
Rhonda CarrollAuthor Commented:
ok ... I am not familiar with any oracle commands
0
 
Rhonda CarrollAuthor Commented:
Plan is attached
-Plan.xls
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Rhonda CarrollAuthor Commented:
Thanks I will contact our local DBA's to see if there is something we can find there.

Thanks again for your help.
0
 
sdstuberCommented:
isn't a split in order?
0
 
Rhonda CarrollAuthor Commented:
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
 
sdstuberCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now