Real Time SQL Monitoring

I’m a sucker for convenience.

I’ve always liked the /*+ GATHER_PLAN_STATISTICS */ hint when followed by

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

Yes, I know you can get the same information via other means but I don't care, I love it (having regressed to a 9i site for nearly a year and only back to a civilised Oracle version for a couple of weeks, I'm probably guilty of a little overexuberance - it's not often that can be said about me).

There's always been a slight conundrum though in that you need to wait for your SQL to complete before you get your execution plan and runtime statistics back.

But, in 11g, wait no more.

You've got REAL TIME SQL MONITORING.

It's cool, it's convenient. What's not to like?

I know I've been back off 11g for about a year now, but how did I miss this previously?

This is old news - but how come more people don't use it? (Other than they're not on 11, of course).

Obviously, there are GUIs and buttons and HTML versions, etc.

Me - I like my command line and text reports :)

Anyway, just to demo an example.

I've pasted the text output of the report into Excel because

  1. WordPress only allows me upload a small set of filetypes and
  2. Excel was the only one that could cope nicely with the width ("never mind the quality, feel the width" as they say).

This demo was done at the end of the SQL - i.e. it wasn't real-time monitoring but if you do it whilst the SQL is running, there's extra progress information which comes from V$SESSION_LONGOPS - i.e. roughly how far through that HASH JOIN are we?

Also, this statement was run in parallel and it produced a really nice summary of parallel execution details.

SELECT dbms_sqltune.report_sql_monitor('8dwq85mf22bs2') 
FROM   dual;

can produce a report a bit like this
forecast_sql_monitoring

Other links:
Performance Tuning Guide
Kerry Osborne - Real time SQL Monitoring
Greg Rahn - Real-time SQl Monitoring Using...

About these ads

12 Responses to Real Time SQL Monitoring

  1. Connor says:

    SQL Monitoring is very cool ………….and separately licensed :-(

  2. Dom Brooks says:

    By the way, the reason why the index names in the execution plan are so dire is that I was just testing out some Advisor recommendations.

  3. Doug Burns says:

    Use it *all the time*, as do my colleagues. I’ve done a few excited presentations on this feature and I’ll be doing an updated presentation on it in a few weeks in Dublin and then on a boat off Norway.

    Another thing worth noting, courtesy of Maria Colgan at Oracle, is that this is a better way of looking at Parallel Queries because the A-ROWS values are unreliable using ALLSTATS LAST because they come from the QC

    • Doug,
      Do you have a Bug# or Article/Note# ? (re. “the A-ROWS values are unreliable using ALLSTATS LAST because they come from the QC” — only for PQ ?)

      Hemant

      • Doug Burns says:

        Hi Hemant,

        I’m not sure there would be a bug number around this because it’s more expected behaviour as I understand it. When you say ALLSTATS LAST the stats come from the last execution of the cursor and the last execution is really the QCs work because it finishes last. That’s how I picked it up, but Maria might be able to comment more accurately.

        Cheers,

        Doug

      • Maria Colgan says:

        Hi Hemant,

        What Doug was referring to is not actually a bug its expected behavior. It’s just a side effect of Parallel Query. When you use ALLSTATS LAST you are asking Oracle to show the last executed of the cursor. In Parallel Query the last process to execute the cursor is the Query Coordinator. Typically the Query Coordinator doesn’t do the majority of the work in a query so there won’t be much information the A-Rows column. The majority of the work is done by the parallel server processes (parallel slaves). If you want to see a complete set of values in the A-Rows for the steps done by the Query Coordinator and the parallel server processes you can use ALLSTATS ALL, which shows you information about ALL of the processes that executed the cursor. However, you should be careful with this if the statement is executed more than once. When you ask for ALLSTATS ALL it will aggregate the information from multiple executions. That’s why SQL Monitor is the perferred way to view A-Rows information for Parallel Query.

      • Doug, Maria,

        Thank you for the explanation. With PQ, I have queried V$PQ_TQSTAT and not GATHER_PLAN_STATISTICS so I haven’t noticed this earlier.

        Hemant K Chitale

    • Dom Brooks says:

      Thanks for the comments Doug.
      The “Parallel Execution Details” section is very effective.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 68 other followers

%d bloggers like this: