what query is doing ... or stuck

Pretty common in our scenario that application called and asked to check for some query, which was running perfect earlier and now it's taking 2 or even more hours, where as it usually ran in 2/3 mins.

What should be the correct line of action to find out the culprit query and reason behind of this slowness, usually we don't do any changes in the db, means nothing changed, there must be either some other jobs or something.

Looking for some steps; first to find the candidate query/sql_id, session, find the problem; why it's hanging or taking that long and then try to fix too.

Thanks in advance.
Mushfique KhanDirector OperationsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
As steps, I would say:
1) Identify if there is a period of time during the day where this happens or if it is totally random;
2) Enable the AWR report for your database during that period of time or leave activated for a couple of days;
3) Use the AWR report to identify the slowest queries on that period of time;
4) Use the explain plan to analyze the slowest queries and make changes if needed;
5) Apply the changes and do it all over again until you are satisfied with the result.

In parallel to that, I would also investigate if there is any issues with filesystem that could be causing high concurrency on I/O operations, check the volume of Insert/Update operations on the database and locks, see if the admin jobs to refresh stats on tables are running, etc...
Mushfique KhanDirector OperationsAuthor Commented:
thanks Ritzel, but I need to verify, I can see the sid, serial# of that query, but now what, how to figure it out, what's going on, just now too, facing the same, one query is taking long,

Please assist, what should I do to see, what that query is doing?
Mushfique KhanDirector OperationsAuthor Commented:
how to find the sql_id?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Walter RitzelSenior Software EngineerCommented:
So, just to understand: have you used the AWR report? Are you able to determine what is going on in general on your database? Have you identified the slow queries and worked on them to improve?

The SID, Serial# will be different each time you execute it, so they are not so useful for a long term research around the database. I have some scripts that I use in order to find locks and such, but I dont know if they will help you at this point. I'm attaching them here.
session-001.zip
Mushfique KhanDirector OperationsAuthor Commented:
was able to find the sql_id too ... now what how to check what it's doing, I've 12 oem too
Mushfique KhanDirector OperationsAuthor Commented:
can you assist in figuring out using oem 12?
Mushfique KhanDirector OperationsAuthor Commented:
was able to find the session/sql_id, oem 12c is saying it is done with error, but @toad, it's still active and wait event its showing is: PX Deq Credit: send blkd

This is an Exadata machine, yesterday the same query ran within 1 min
Hamed NasrRetired IT ProfessionalCommented:
"yesterday the same query ran within 1 min"

Could be a machine specific issue.

Try to run the job on another machine.
DavidSenior Oracle Database AdministratorCommented:
The PX Deq Credit: send blkd is an idle event in a parallel execution wait event for RAC.  This isn't anything related to the actual statement or its tuning.  Something in the environment, external to the statement, changed.

If you were to suspect the statement, I'd suggest you forgo the add-on products and work a bit with the built-in dbms features, such as setting autotrace on and running your explain plan thru tkprof.

Being a RAC environment, I presume you've already thought to review your sys and alert logs, etc.

See http://www.dba-oracle.com/oracle_tips_parallel_query_execution_tuning.htm , https://books.google.com/books?id=DwoZBQAAQBAJ&pg=PA272&lpg=PA272&dq=The+PX+Deq+Credit:+send+blkd+is+an+idle+event+in+a+parallel+execution+wait+event+for+RAC.&source=bl&ots=aSK0HA9FOf&sig=dPxz14gBhErobENvIYISilsrB90&hl=en&sa=X&ei=Ou1pVaa-G7KHsQTF3oKwDg&ved=0CEgQ6AEwBQ#v=onepage&q=The%20PX%20Deq%20Credit%3A%20send%20blkd%20is%20an%20idle%20event%20in%20a%20parallel%20execution%20wait%20event%20for%20RAC.&f=false , etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.