Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Anubha.

Asked: January 29, 2018 - 2:03 pm UTC

Last updated: February 03, 2018 - 4:29 am UTC

Version: Oracle10g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Few days back I attended an interview, and one question which I failed to answer properly,was How to use ASH report. I know few things about ASH report like Top user event,Top CPU time and which SQL has spent how much % on which event. But interviewer asked me to give step by step details to identify which SQL is troubling.
Can you please help me to understand How to identify troubling SQL stepwise, by looking into ASH report.
Thanks,
-Anu

and Connor said...

I've created a very simple ASH report here

SAMPLE_ASH

So you can follow along.

I'd start with "Top User Events", so I'm seeing overall, what is possibly of concern. In this case, its "CPU" (which makes sense because I set a runaway SQL going in another session).

So now I'm looking for candidates that fall into that category. That takes me to "Top SQL Command Types" which tells me it is SELECT's I'm looking for.

Which takes me to "Top SQL with Top Events" and I find the SQL in question, and 86% of the activity.

From there, I would leave the ASH report and hence into the database:

v$sql
v$sqlstats
v$sql_plan

and perhaps some SQL monitoring to see whats happening with that SQL

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Elaborate

A reader, February 02, 2018 - 8:50 am UTC

which makes sense because I set a runaway SQL going in another session.

What do you mean by that and how explain it from report cause when reading report all come from whole sessions personally didn't know.
Connor McDonald
February 03, 2018 - 4:29 am UTC

which makes sense because I set a runaway SQL going in another session.

In order to capture some ASH activity, I set a long running SQL going in another session

ASH report

A reader, February 14, 2018 - 6:44 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.