I agree to Idea Show currently executing step/line in Session Monitor | Plan
Voting Disabled

27 votes

I disagree to Idea Show currently executing step/line in Session Monitor | Plan

Rank30

Idea#262

This idea is active.
Monitor »

Show currently executing step/line in Session Monitor | Plan

In Session Monitor | Current Statement | Explain Plan, highlight the currently executing step in the plan. This information I believe can be had via V$SESSION_LONGOPS.

Right now there is a plan "cursor" that is yellow by default but it is just for working with plan rows (like hitting F4 to describe the object in the step).

If we could do some other highlight like marching ants (Photoshop style) or something else that would give us some clue as to WHERE in the plan Oracle is churning on.

Right now it's not easy to figure that out via the Long Ops tab because, yes, you know the object but if the object is used more than once in a query you don't know which one.

In the attachment I have mocked up a green rectangle around the "currently executing step". You could do better than my mockup but it's a start.

I realize this would be for long-running statements only and that's okay. Who cares what step we are in if the query takes 150ms? But if it is taking 150 seconds, now we start to care.

Submitted by Steve C 3 years ago

Comments (8)

  1. Moderator

    It would be cool to know what step the query is on for slow execution plans, but most of the time there isn't any way to know for sure what step Oracle is on. Like you said - you can figure it out when some part of the query happens to make it to v$longops and there is only one reference to that table in the query - but more often, this will not be the case.

    It just doesn't seem like there's enough to go on for this one.

    3 years ago
    0 Agreed
    0 Disagreed
  2. Steve C Idea Submitter

    I know this isn't perfect but what about something like this:

    select distinct

    sl.sid, spm.plan_cardinality, plan_line_id, plan_operation, plan_object_owner, plan_object_name

    from

    v$session_longops sl

    inner join v$sql_plan_monitor spm on (sql_plan_line_id = plan_line_id and sl.sid= spm.sid and sl.sql_id = spm.sql_id)

    3 years ago
    0 Agreed
    0 Disagreed
  3. Moderator

    Interesting - I haven't noticed v$sql_plan_monitor before...it looks promising. We might be able to do something with it. For now, I'd like to leave this idea up for voting.

    3 years ago
    0 Agreed
    0 Disagreed
  4. 'set autotrace on' could be your friend meanwhile. It outputs an execution plan with timing information so one can see where the database has spend an unusual amount of time...

    bye

    T

    3 years ago
    0 Agreed
    0 Disagreed
  5. Steve C Idea Submitter

    Hi Thomas, With monitoring turned on you can use DBMS_XPLAN to display actual versus estimated plan statistics. Meaning if Oracle estimated 10 rows but really 10,000 rows were used in the step, you will know that. Why is that important? My best performance gains have been in trying to get Oracle to get the right cardinality for each plan step. If you do, Oracle is far better at getting the right plan than I ever could tune. It will choose the right join type, access path and join order...

    3 years ago
    0 Agreed
    0 Disagreed
  6. Here's a blog on the new 11g real time sql monitoring that goes into a bit of detail. I think this is a great idea!

    3 years ago
    0 Agreed
    0 Disagreed
  7. For some reason my blog link didn't go through, here's the blog i was referencing

    http://blog.aristadba.com/?p=111

    3 years ago
    0 Agreed
    0 Disagreed
  8. see also v$open_cursor.SQL_EXEC_ID

    2 years ago
    0 Agreed
    0 Disagreed

Attachments Show