Setting up PL/SQL Developer, part 2

This article was written for PL/SQL Developer 8.0.4 using Oracle 11.2 and Windows XP, in a Parallels virtual machine on my Mac, which is why the screenshots show a mixture of XP Silver and Aqua windows.

PL/SQL Developer is one of several integrated development environments (IDEs) that are available for Oracle. One of the things I like about it is how configurable it is - you can change almost anything, and with downloadable plug-ins such as Browser Extender you can add your own functionality.

After moving PCs several times and having to reinstall PL/SQL Developer afresh each time, I have found that there are some customisations I couldn't live without, and I thought I'd document them. Part One covered preferences such as fonts and screen layout, and Part 2 covers the Session browser.

I was going to include the custom right-mouseclick actions that I've added using Browser Extender, but there is so much you can do with the Session browser that I'm going to have to leave that for Part 3.

Extending the Session Browser

1. Make the Session Browser easier to find

In the default layout, it's buried way down the list under the Tools menu, but as it's something you'll use all the time it's much better to have a button for it. In case you missed it in part 1, you can customise the toolbar by adding an icon for the Session Browser. Here's the kind of thing you can do:

Default toolbar

New (window or database object) Open file Save file Print portrait Print landscape Undo Redo Cut Copy Paste Search/Replace Repeat last find Navigate back Navigate forward PL/SQL Beautifier Selection indent Selection unindent Selection comment Selection uncomment Macro record Macro playback Macro library Cascade windows Tile windows Log on Execute (F8) Break Commit Roll back Explain Plan Query Builder Find database objects Recompile invalid objects Oracle documentation Configuring the toolbar

Customised toolbar

Log on Sessions Save file Execute (F8) Break Commit Roll back Find database objects PL/SQL Beautifier Uppercase selection Lowercase selection Apply syntax case Selection indent Selection indent Selection comment Selection uncomment Navigation back Navigation forward Code contents Code folding Show special characters Color marker SQL*Plus Configuring the toolbar

Notice the "cross keys" icon second from the left in the customised toolbar.

2. A look at the default settings

Now open the Session Browser and look at the default set-up. (Actually not quite default - I've changed the font to Corbel 8pt, which fits more information on the screen, as well as being more attractive than the default in my opinion. Tahoma also works well. You will be looking at this screen a lot, after all.)

Session Browser - default

The screen is a master-detail report, with both "master" and "detail" queries configurable using the wrench icon. The "master" query in the top part of the window is defined under "Filters", and some variations on "select * from v$session" are provided. Under "Details", there are four very basic queries for open cursors, current SQL statement, session statistics and locks:

Session filters - default Session details - default

Notice the bind variable :sid in the "Cursors" query. The cool thing about session browser detail queries is you can refer to the current value of any column from the top section as a bind variable in detail queries. So, as long as the main query contains a column named "sid", we can use expressions like "where session_id = :sid" in any detail query. (This does mean, however, that you may need to include a few columns in the master query purely to use as keys in detail queries.)

One more point to note about the detail query box is that adding the text /* concatenate */ after the query makes PL/SQL Developer join all the lines of output into one large block. While a neat feature, this also prevents scrolling so I find it a mixed blessing.

3. Write your own V$SESSION queries

Active Sessions

The default queries are all select * from v$session where..., which of course is a sensible default setting that will work across all Oracle versions. New and useful attributes get added to v$session in every release, and of course coding them explicitly in joins and look-ups means the query might not work in an earlier version.1 If you are working with multiple Oracle versions, you may need to save more than one query in the "Filters" section, and select the appropriate one as needed (unfortunately PL/SQL Developer can't check the version and choose it for you). Here's a better "Active sessions" query for Oracle 10.2.0.2 onwards (note the columns plsql_entry_object_id and plsql_entry_subprogram_id, amongs others, were added in this version so it won't work in Oracle 10g XE).

This includes:

GV$ views for RAC

The v$ views (actually synonyms for sys.v_$ views) all have g-prefixed versions - for example, gv$session - that include the instance number, for use in RAC systems. For single-instance systems this will always be 1. The documentation lists only the v$ version, so if you want to know about gv$session, for example, just look up v$session and assume there will be one extra column named inst_id. I have used the regular v$ and RAC-ready gv$ names interchangeably.

Copy the query below into the Query box (after testing it in a SQL window to make sure it works with your Oracle version and permissions - to access the V$ views you need SELECT_CATALOG_ROLE). Note that there is no semicolon at the end. You might also want to review it against v$session in case there are useful columns that are useful to you.

select pxs.qcsid as parent
     , se.sid
     , se.serial#
     , se.blocking_session as blocked_by
     , se.username
     , se.osuser
     , se.module
     , se.action
     , se.client_info
     , ( select aa.name from
                ( select action, name from audit_actions union select 189, 'MERGE' from dual ) aa
         where aa.action = se.command + case when se.command < 0 then 256 else 0 end) as command
     , se.program
     , se.state
     , se.wait_class
     -- From docs for v$session:
     -- If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition.
     -- If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME / 100 is the active seconds since the last wait ended.
     , case when se.wait_time = 0 then se.seconds_in_wait end as current_wait_secs
     , case when se.wait_time > 0 then se.seconds_in_wait - se.wait_time / 100 end as secs_since_last_wait
     , se.event as current_wait
     , ( select rtrim(owner || '.' || object_name || '.' || o.subobject_name,'.') from dba_objects o
         where  o.object_id = row_wait_obj# ) as wait_object
     , se.lockwait
     , ( select rtrim(pro_entry.object_name || '.' || pro_entry.procedure_name,'.')
         from   dba_procedures pro_entry
         where  pro_entry.object_id = se.plsql_entry_object_id
         and    pro_entry.subprogram_id = se.plsql_entry_subprogram_id ) as entry_procedure
     , ( select rtrim(pro_current.object_name || '.' || pro_current.procedure_name,'.')
         from   dba_procedures pro_current
         where  pro_current.object_id = se.plsql_object_id
         and    pro_current.subprogram_id = se.plsql_subprogram_id ) as current_procedure
     --, row_wait_file#, row_wait_block#, row_wait_row#
     , se.row_wait_obj#  -- Needed for 'Current wait' detail query
     , se.logon_time
     --, last_call_et
     , se.pdml_enabled
     , se.pdml_status
     --, pddl_status
     , se.pq_status
     , ltrim(pxs.req_degree || ' (' || pxs.req_degree || ')','() ') as "Parallel (Reqested)"
     -- , resource_consumer_group  -- Uncomment if using Oracle Resource manager
     , se.current_queue_duration
     , se.client_identifier
     , se.service_name
     -- , sql_trace, sql_trace_waits, sql_trace_binds
     , se.saddr
     --, process  -- client process not normally useful
     , se.paddr
     , se.audsid
     , se.taddr
     , se.machine
     , se.terminal
     , se.sql_address
     , se.sql_hash_value
     , se.sql_id
     , se.sql_child_number
     --, se.sql_exec_id  -- 11g
     --, se.sql_exec_start  -- 11g
     --, se.prev_sql_addr
     --, prev_hash_value
     , prev_sql_id
     , prev_child_number
     --, failover_type, failover_method, failed_over
     --, blocking_session_status, blocking_instance
     --, server
     , user#
     , round(sm.cpu,1) as "CPU"
     , sm.physical_reads
     , sm.logical_reads
     , round(sm.pga_memory/1048576,1) as pga_memory_mb
     , sm.hard_parses, sm.soft_parses
     , round(sm.physical_read_pct,1) as physical_read_pct
     , round(sm.logical_read_pct,1)  as logical_read_pct

     , ( select round(sum(tu.blocks) * ts.block_size / 1048576,1)
         from   gv$tempseg_usage tu
                join dba_tablespaces ts
                on   ts.tablespace_name = tu.tablespace
         where  tu.session_addr = se.saddr
         and    tu.inst_id = se.inst_id
         and    tu.session_addr = se.saddr
         and    tu.session_num = se.serial#
         group by ts.block_size ) as temp_mb

     , row_number() over (order by sm.cpu desc nulls last) as rank_by_cpu
     , row_number() over (order by sm.physical_reads desc nulls last) as rank_by_physical_reads
     , row_number() over (order by sm.logical_reads desc nulls last) as rank_by_logical_reads
     , row_number() over (order by sm.pga_memory desc nulls last) as rank_by_pga

     , row_number() over (order by sm.cpu desc nulls last) +
       row_number() over (order by sm.physical_reads desc nulls last) +
       row_number() over (order by sm.logical_reads desc nulls last) +
       row_number() over (order by sm.pga_memory desc nulls last) as ranks_combined

     , se.inst_id as instance
     , se.status

from   gv$session se

       left join gv$px_session pxs
       on   pxs.sid = se.sid
       and  pxs.serial# = se.serial#
       and  pxs.inst_id = se.inst_id

       left join gv$sessmetric sm
       on   sm.session_id = se.sid
       and  sm.serial_num = se.serial#
       and  sm.inst_id = se.inst_id

       left join v$transaction tr
       on   tr.addr = se.taddr

where  ( se.username is not null and 'ACTIVE' in (tr.status, se.status) and se.audsid != sys_context('USERENV','SESSIONID') )
or     se.sid in
       ( select blocking_session from v$session
         union
         select qcsid from gv$px_session )
order by se.inst_id, pxs.qcsid, pxs.qcserial# nulls first  -- PQ controller appears without serial# in v$px_session
      , se.sid, se.sid,se.serial#

My Sessions

On a busy system, you sometimes just want to see your own sessions and exclude everything else. For this I use a "My sessions" query, which is the same as the one above except for the WHERE clause, which is:

where  osuser = sys_context('userenv','os_user')

All Sessions

It's also sometimes handy to have one version that shows all sessions, including the Oracle log writer, process monitor etc. Make another copy of the query above, and just leave out the WHERE clause.

4. Now add your own detail tabs

SQL Stats

This uses v$sqlstats to display detailed execution statistics about the session's current SQL statement (identified by sql_id). Note that it refers to all instances of the cursor, not just this session's current call. (Also, as the v$ views reflect only what's in memory right now, it may be different from what you see in the dba_hist_% views if you have the Diagnostics Pack.) The idea of the percentages is to show how the total elapsed time breaks down into CPU, I/O, concurrency waits etc. It's only approximate and they don't always add up to 100%, because there may be other factors unaccounted for such as network transfer times and application processing, but they give you an idea of how the statement is being processed.

select sql_id
     , plan_hash_value
     , last_active_time
     , executions
     , round(100 * parse_calls/nullif(executions,0),1)  as "Parsed%"
     , parse_calls
     , cast(numtodsinterval(elapsed_time/1e6,'SECOND')  as interval day(0) to second(0)) as elapsed_total
     , cast(numtodsinterval(elapsed_time / nullif(executions,0) / 1e6,'SECOND') as interval day(0) to second(0)) as elapsed_per_exec
     , round(100 * cpu_time / elapsed_time,1) "CPU%"
     , round(100 * user_io_wait_time / elapsed_time,1) "IO%"
     , round(100 * concurrency_wait_time / elapsed_time,1) "CONCURRRENCY%"
     , round(100 * application_wait_time / elapsed_time,1) "APPLICATION%"
     , round(100 * plsql_exec_time / elapsed_time,1) "PL/SQL%"
     , buffer_gets buffer_gets_total
     , round(buffer_gets / nullif(executions,0))        as buffer_gets_per_exec
     , disk_reads
     , round(rows_processed / nullif(fetches,0),1)      as rows_per_fetch
     , round(rows_processed / nullif(executions,0),1)   as rows_per_exec
     , direct_writes
     , rows_processed
     , fetches
     , end_of_fetch_count
     , loads
     , version_count
     , invalidations
     , px_servers_executions
     , round(avg_hard_parse_time / 1e6,2)               as avg_hard_parse_secs
     , cluster_wait_time
     , cast(numtodsinterval(plsql_exec_time/1e6,'SECOND') as interval day(0) to second(0)) as plsql_exec_time
     , cast(numtodsinterval(java_exec_time/1e6,'SECOND') as interval day(0) to second(0)) as java_exec_time
     , sorts
     , sharable_mem
     , total_sharable_mem
     , last_active_child_address
     , serializable_aborts
from   v$sqlstats s
where  s.sql_id = :sql_id

Now you should get a "SQL Stats" tab like the screenshot below for any session that's executing SQL. ("Binds", "Prev SQL" etc are other tabs I'll define in a moment.)

SQL Stats detail tab display

Perf history this cursor

If a SQL statement is taking a long time, you might want to check its performance history (from dba_hist_sqlstats) to see whether this is normal for the cursor or whether something has changed.

The first query below gives the distinct execution plans and their corresponding runtime statistics, aggregated for the whole history of the cursor, so you can see the average execution time and whether multiple plans exist. (Notice the join to gv$sql_plan - the 'g' indicating the RAC-enabled version - which seeems to be the most reliable way to find the execution plan that is currently being used as it includes the child number. As v$sqlstats only reports one row per distinct sql_id, it might not show the plan for the version currently executing.)

select case when s.plan_hash_value =
          ( select plan_hash_value from
                   ( select plan_hash_value, row_number() over (order by timestamp desc) as seq
                     from   gv$sql_plan p
                     where  p.sql_id = :sql_id
                     and    p.child_number = :sql_child_number
                     and    p.inst_id = :instance )
            where seq = 1 )
          then 'Y'
       end as current_plan
     , plan_hash_value
     , sum(executions_delta) executions
     , cast(numtodsinterval(sum(elapsed_time_delta)/1e6,'SECOND') as interval day(1) to second(2)) as total_run_time
     , cast(numtodsinterval(sum(elapsed_time_delta)/ nullif(sum(executions_delta),0)/1e6,'SECOND') as interval day(1) to second(1)) as avg_run_time
     , sum(buffer_gets_delta) buffer_gets
     , round(sum(buffer_gets_delta)/nullif(sum(executions_delta),0)) as buffer_gets_per_exec
     , sum(rows_processed_delta) as rows_processed
     , round(sum(rows_processed_delta) / nullif(sum(executions_delta),0),1) as rows_processed_per_exec
     , cast(numtodsinterval(sum(elapsed_time_delta)/nullif(sum(rows_processed_delta),0)/1e6,'SECOND') as interval day(1) to second(3)) as avg_time_per_row
     , round(sum(buffer_gets_delta)/nullif(sum(rows_processed_delta),0)) as buffer_gets_per_row
from   dba_hist_sqlstat s
where  sql_id = :sql_id
group by plan_hash_value
order by 1,2

The second version - which I label as "Perf history this cursor by date" - splits the same information by day, so you can see whether it ran quickly last Tuesday, or whether the plan changed this morning:

select case when s.plan_hash_value =
          ( select plan_hash_value from
                   ( select plan_hash_value, row_number() over (order by timestamp desc) as seq
                     from   gv$sql_plan p
                     where  p.sql_id = :sql_id
                     and    p.child_number = :sql_child_number
                     and    p.inst_id = :instance )
            where seq = 1 )
          then 'Y'
       end as current_plan
     , trunc(cast (h.begin_interval_time as date)) as exec_date
     , plan_hash_value
     , sum(executions_delta) executions
     , cast(numtodsinterval(sum(elapsed_time_delta)/1e6,'SECOND') as interval day(1) to second(2)) as total_time
     , cast(numtodsinterval(sum(elapsed_time_delta)/ nullif(sum(executions_delta),0)/1e6,'SECOND') as interval day(1) to second(1)) as avg_time
     , sum(buffer_gets_delta) buffer_gets
     , round(sum(buffer_gets_delta)/nullif(sum(executions_delta),0)) as buffer_gets_per_exec
     , sum(rows_processed_delta) as "ROWS"
     , round(sum(rows_processed_delta) / nullif(sum(executions_delta),0),1) as rows_per_exec
     , cast(numtodsinterval(sum(elapsed_time_delta)/nullif(sum(rows_processed_delta),0)/1e6,'SECOND') as interval day(1) to second(3)) as avg_time_per_row
     , round(sum(buffer_gets_delta)/nullif(sum(rows_processed_delta),0)) as buffer_gets_per_row
from   dba_hist_sqlstat s
       join dba_hist_snapshot h on h.snap_id = s.snap_id and h.dbid = s.dbid
where  s.sql_id = :sql_id
group by trunc(cast (h.begin_interval_time as date)), s.plan_hash_value
order by trunc(cast (h.begin_interval_time as date)) desc, 1 nulls last, s.plan_hash_value

Binds

The following query will list any bind variables held in v$sql_bind_capture for the current SQL statement. I've filtered the results to exclude duplicates.

Note that Oracle doesn't capture every single bind value, and just holds the last value captured at interval _cursor_bind_capture_interval and depending on the amount available space up to _cursor_bind_capture_area_size.

select name, datatype_string, last_captured, value_string
from   ( select name
              , datatype_string
              , last_captured
              , value_string
              , row_number() over(partition by position order by last_captured nulls last) as seq
         from   v$sql_bind_capture
         where  sql_id = :sql_id
         and    child_number = :sql_child_number
         and    address = :sql_address
         and    hash_value = :sql_hash_value )
where seq = 1
order by last_captured

Sampled bind variable values

An alternative is to get the bind data used at parse time from v$sql_plan, although this takes some decoding as it's held in RAW format within an XML column - see Jonathan Lewis' blog post Bind Capture, which links to Creating Test Scripts With Bind Variables from Kerry Osborne and Tracking the Bind Value from Dion Cho. This led me to the following query using an idea from Kyle Hailey in the comments on Jonathan Lewis' post:

select t.name
     , t.position
     , t.dup_position
     , t.datatype_string
     , t.precision
     , t.scale
     , t.max_length
     , t.last_captured
     , t.value_string
from   v$sql s, table(dbms_sqltune.extract_binds(s.bind_data)) t
where  s.sql_id = :sql_id
and    s.child_number = :sql_child_number

In my tests using Oracle 11.2.0.2 this omits the bind names. Anyway capturing bind values is a big subject, so I'll leave you with the queries above to experiment with, and move on.

Previous SQL, previous SQL stats

Occasionally it's useful to see what the previous statement was. v$session contains several prev_ columns, so just duplicate the detail tabs for SQL Text and SQL Stats but substitute prev_sql_id and prev_child_number.

Object stats

When investigating a performance issue, you often want to check the current state of the statistics on the tables involved in the query. The query below joins v$sql_plan_statistics_all with dba_tab_statistics to list this information - it's not perfect if partitioned tables are involved, as the problem might lie with stats for an individual partition or subpartition, but it's a start.

select * from (
with plan_objects as
     ( select --+ materialize
              p.object_owner
            , p.object_name
            , p.object_type
            , p.partition_start
            , p.partition_stop
            , p.cardinality
            , p.operation
            , p.options
            , count(*) as occurs_in_plan
       from   v$sql_plan_statistics_all p
       where  p.sql_id = :sql_id
       and    p.child_number = :sql_child_number
       and    p.plan_hash_value =
              ( select plan_hash_value from
                       ( select plan_hash_value, row_number() over (order by timestamp desc) as seq
                         from   gv$sql_plan p
                         where  p.sql_id = :sql_id
                         and    p.child_number = :sql_child_number
                         and    p.inst_id = :instance )
                where seq = 1 )
       and    p.object_type != 'VIEW'
       group by p.object_owner, p.object_name, p.object_type, p.partition_start, p.partition_stop, p.cardinality, p.operation, p.options )
   , object_stats as
     ( select ts.owner as object_owner
            , ts.table_name as object_name
            , ts.table_name as display_name
            , ts.num_rows
            , ts.blocks
            , ts.last_analyzed
            , ts.stale_stats
       from   dba_tab_statistics ts
       where  (ts.owner, ts.table_name)  in
              (select object_owner, object_name from plan_objects where object_type like 'TABLE%')
       and    ts.partition_name is null
       union
       select xs.owner
            , xs.index_name
            , '(' || xs.table_name || ') ' || index_name as display_name
            , xs.num_rows
            , xs.leaf_blocks as blocks
            , xs.last_analyzed
            , xs.stale_stats
       from   dba_ind_statistics xs
       where  (xs.owner, xs.index_name) in
              (select object_owner, object_name from plan_objects where object_type like 'INDEX%')
       and    xs.partition_name is null
     )
select --+ dynamic_sampling(8)
       object_owner
     , o.object_type
     , nvl(s.display_name,object_name) as object_name
     , s.stale_stats as "Stale?"
     , o.operation || ' ' || o.options as operation
     , o.cardinality
     , s.num_rows as "Rows (global)"
     , s.blocks
     , s.last_analyzed
     , o.partition_start
     , o.partition_stop
from   plan_objects o
       left join object_stats s using(object_owner, object_name)
order by
      case object_owner when 'SYS' then 2 else 1 end
    , object_owner
    , ltrim(object_name,'(')
)

Cursors

Replace the default 'Cursors' query ("select * from v$open_cursor where sid = :sid") with the following to add some activity statistics. (Note that the 'executions' statistic refers to all sessions, not just the current session.)

select c.sql_id, c.sql_text, s.executions, s.executions - s.end_of_fetch_count as incomplete
     , ash.last_active
     , case when systimestamp - ash.last_active < interval '1' minute then 'Y' end as active_in_last_min
     , case when systimestamp - ash.last_active < interval '10' minute then 'Y' end as active_in_last_10_min
from   v$open_cursor c
       join v$sqlstats s on s.sql_id = c.sql_id
       left join
            ( select session_id, sql_id, max(sample_time) as last_active
              from   v$active_session_history
              group by session_id, sql_id ) ash
       on   ash.session_id = c.sid
       and  ash.sql_id = c.sql_id
where  sid = :sid
order by ash.last_active desc nulls last, incomplete desc, c.sql_id

Current plan

PL/SQL Developer's built-in Explain Plan tool (F5) is all well and good, but it can only be as good as explain plan. That is, the tool uses explain plan to predict the execution plan, and then displays the results in graphical form. Sometimes this is not the same as the actual runtime plan. When viewing currently executing sessions, I like to use dbms_xplan.display_cursor() to see what the database is actually doing. Define a "Current plan" tab using the following:

select plan_table_output || chr(10) as plan
from   table(dbms_xplan.display_cursor(:sql_id, :sql_child_number, 'ADVANCED +peeked_binds +projection'))
/* concatenate */

The /* concatenate */ comment will make PL/SQL Developer wrap all the lines of output from the query into one large block. This makes it easier to read, although it does also prevent scrolling so I'm not sure it's all that useful here. (Unfortunately you can't specify a monospace font for an individual menu item, so the default display isn't that great.) The best way to read it is to copy and paste into a new SQL window. This is easier if you defined a hotkey such as Alt-S for "File > New > SQL Window" as I suggested in part 1. (I also have a Browser Extender extension to do this in one right-click, which I'll come to later.)

Current plan from dbms_xplan.display_cursor

I also use another variation of this query, which I've labelled "Current plan GPS" ("Gather Plan Statistics" - although perhaps "Extended plan" would be a better name now I think about it). This uses ALLSTATS LAST in the "format" argument to dbms_xplan.display_cursor to get the estimated and actual row counts (cardinality) if the query used the /*+ gather_plan_statistics */ hint, or if the parameter statistics_level was set to 'ALL' for the session.

select plan_table_output || chr(10) as plan
from   table(dbms_xplan.display_cursor(:sql_id, :sql_child_number, 'ALLSTATS LAST +OUTLINE'))
/* concatenate */

The slightly tricky part with this is that you can't use it until the query completes (because the actual row counts aren't yet known), but when it does complete it is no longer the currently executing query and therefore disappears from v$session, and so when you refresh your session browser it's gone. Instead you need to refresh the screen while the query is executing, but wait until it completes before going to the "Current plan GPS" tab.

Current wait

Although the session's current wait state is already shown in the master query, above, I like to have the information in its own tab as well. I've labelled the wait object looked up from row_wait_obj# as "possibly unrelated" as a reminder that although this is the most recent object that the session waited for, processing might now have moved on to something else (sorting output for example, or waiting for an application to process the query output) and the session is not actually accessing this object at the moment.

select wait_time
     , seconds_in_wait, wait_class
     , state
     , event
     , ( select initcap(o.object_type) || ' ' || o.owner || '.' || o.object_name ||
                nvl2(o.subobject_name, ' (' || o.subobject_name || ')', null)
         from   all_objects o
         where  o.object_id = :row_wait_obj#
         and   :status = 'ACTIVE' ) as "Last wait obj (poss unrelated)"
from   v$session_wait
where sid = :sid
order by seq#

Last 10 waits

The following gives a quick glance at the session's recent activity using v$session_wait_history (wait time is in hundredths of a second):

select event
     , p1text, p1
     , case p1text when 'file#' then
          (select file_name from dba_data_files f where f.file_id = wh.p1)
       end as object_desc
     , p2text, p2
     , p3text, p3
     , nullif(wait_time,0) as wait_time
from   v$session_wait_history wh
where  wh.sid = :sid
order by wh.seq#

Long ops

v$session_longops displays the status of various operations that run for longer than 6 seconds. These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

(from the Database Reference manual.)

If a query uses hash or sort operations, table scans, partition operations etc that take more than 6 seconds, these operations will appear in v$session_longops and you can track their progress. (Note that it's only individual operations that are tracked, not entire queries.) Many long-running Oracle processes are instrumented as well, as the manual mentions. Others not listed above include Database Replay and SQL Performance Analyzer runs (11g), and datapump import/export jobs - and of course any of your own processes where you included dbms_application_info.set_session_longops calls to log the total work and amount processsed so far.

select sid
     , case when l.time_remaining > 0 or l.sofar < l.totalwork then 'Yes' end as "Active?"
     , l.opname as operation
     , l.totalwork || ' ' || l.units as totalwork
     , nvl(l.target,l.target_desc) as target
     , l.start_time
     , round(100 * l.sofar/greatest(l.totalwork,1),1) as "Complete %"
     , rtrim(rtrim(ltrim(ltrim(numtodsinterval(l.elapsed_seconds,'SECOND'),'+0'),' '),'0'),'.') as elapsed
     , ltrim(ltrim
       ( cast(numtodsinterval(l.elapsed_seconds * greatest(nullif((l.totalwork - l.sofar)/nullif(l.sofar,0),0),0),'SECOND')
              as interval day(2) to second(0))
       ,'+0'),' ') as remaining
     , case
           when l.sofar >= l.totalwork then l.last_update_time
           else sysdate + numtodsinterval( l.elapsed_seconds * greatest(nullif((l.totalwork - l.sofar)/nullif(l.sofar,0),0),0),'SECOND')
       end as est_completion
     , message
     , l.sql_id
     , l.sql_address
     , l.sql_hash_value
from v$session_longops l
where :sid in (sid,qcsid)
and  l.start_time >= to_date(:logon_time,'DD/MM/YYYY HH24:MI:SS')
order by l.start_time desc

I also define a "Long ops this query" tab, which is a copy of the one above but with an additional filter to limit it to the currently executing operation:

and  l.sql_id = :sql_id

ASH summary - session

v$active_session_history is a snapshot of v$session taken once each second, held for a limited period (normally 30 to 60 minutes) and then stored in dba_hist_active_sess_history. (To use this you need the Diagnostics Pack, so make sure you're licenced even if works - you don't want your boss to get an unexpected bill following an Oracle audit.) There are many creative ways to mine this information, and I use three queries to track what a currently running session is doing.

Since ASH samples every second, it can be useful to summarise it by SQL statement and list the results by time taken. If you are watching a procedure or batch that calls several statements, this gives an overview of where the session is spending its time (a bit like tracing the session). The following query gives one row per sql_id, in descending order of total time, with a total at the bottom.

select case curr.sql_id when ash.sql_id then 'Y' end as "Current"
     , ash.sql_id
     , to_char(cast(min(ash.sample_time) as date),'HH24:MI:SS') as first_active
     , to_char(cast(max(ash.sample_time) as date),'HH24:MI:SS') as last_active
     , cast(numtodsinterval(count(*), 'SECOND') as interval day(1) to second(0)) as elapsed  -- assumes 1 sample per sec
     , cast(numtodsinterval(sum(s.elapsed_time)/nullif(sum(s.executions),0)/1e6,'SECOND') as interval day(1) to second(1)) as avg_for_cursor
     , case
          when count(*) > 1.1 * sum(s.elapsed_time)/nullif(sum(s.executions),0)/1e6
          and  count(*) - sum(s.elapsed_time)/nullif(sum(s.executions),0)/1e6 > 1
          and  ash.sql_id is not null then 'Y'
       end as over_avg
     , round(100 * ratio_to_report(count(*)) over(partition by grouping_id(ash.sql_id, s.sql_text, s.executions, s.rows_processed))) as percent
     , s.executions as "Executions (global)"
     , s.rows_processed as "Rows (global)"
     , round(s.rows_processed / nullif(s.executions,0),1) as "Rows/exec"
     , s.sql_text
from   gv$active_session_history ash
       left join v$sqlstats s on s.sql_id = ash.sql_id
       left join gv$session curr
            on curr.inst_id = ash.inst_id and curr.sid = ash.session_id and curr.serial# = ash.session_serial# and curr.sql_id = ash.sql_id
where  ash.inst_id = :instance
and    session_id = :sid
and    session_serial# = :serial#
and    user_id = :user#
group by grouping sets((ash.sql_id, curr.sql_id, s.sql_text, s.executions, s.rows_processed), ())
order by grouping_id(ash.sql_id, s.sql_text, s.executions, s.rows_processed), max(ash.sample_time) desc, min(ash.sample_time) desc

ASH summary - executions

I also have a more detailed version made possible in 11g by the sql_exec_start column in v$active_session_history, which allows me to see individual executions of a SQL statement rather than a single aggregated row.

select case curr.sql_id when ash.sql_id then 'Y' end as "Current"
     , ash.sql_id
     , to_char(ash.sql_exec_start,'HH24:MI:SS') as started  -- 11g
     , to_char(ash.sql_exec_start + (count(*)/86400),'HH24:MI:SS') as ended  -- start + elapsed more accurate than next start - start
     , cast(numtodsinterval(count(*), 'SECOND') as interval day(1) to second(0)) as elapsed  -- assumes 1 sample per sec
     , cast(numtodsinterval(sum(s.elapsed_time)/nullif(sum(s.executions),0)/1e6,'SECOND') as interval day(1) to second(1)) as avg_for_cursor
     , case
          when count(*) > 1.1 * sum(s.elapsed_time)/nullif(sum(s.executions),0)/1e6
          and  count(*) - sum(s.elapsed_time)/nullif(sum(s.executions),0)/1e6 > 1
          and  ash.sql_id is not null then 'Y'
       end as over_avg
     , round(100 * ratio_to_report(count(*)) over(partition by grouping_id(ash.sql_id, s.sql_text, s.executions, s.rows_processed))) as percent
     , s.executions as "Executions (global)"
     , s.rows_processed as "Rows (global)"
     , round(s.rows_processed / nullif(s.executions,0),1) as "Rows/exec"
     , s.sql_text
from   gv$active_session_history ash
       left join v$sqlstats s on s.sql_id = ash.sql_id
       left join gv$session curr
            on curr.inst_id = ash.inst_id and curr.sid = ash.session_id and curr.serial# = ash.session_serial# and curr.sql_id = ash.sql_id
where  ash.inst_id = :instance
and    session_id = :sid
and    session_serial# = :serial#
and    user_id = :user#
group by grouping sets((ash.sql_id, curr.sql_id, ash.sql_exec_start, s.sql_text, s.executions, s.rows_processed), ())
order by grouping_id(ash.sql_id, ash.sql_exec_start, s.sql_text, s.executions, s.rows_processed), max(ash.sample_time) desc, min(ash.sample_time) desc

ASH summary - time / cursor object

Report showing objects waited for by all SQL statements for the specified session. This is meant as a quick way to see what a session has spent its time on, in terms of objects rather than queries.

select case
          when grouping_id > 0 then '(Total:)'
          else sql_id
       end as sql_id
     , wait_class
     , ( select aa.name from
                ( select action, name from audit_actions union select 189, 'MERGE' from dual ) aa
         where aa.action = sql_opcode + case when sql_opcode < 0 then 256 else 0 end) as command
     , elapsed
     , round(100 * ratio_to_report(samples) over (partition by grouping_id),1) as percent
     , case when current_obj# > 0 then
          ( select distinct o.owner || '.' || o.object_name || rtrim('.' || o.subobject_name,'.')
            from   dba_objects o where o.object_id = current_obj# )
       end as wait_object
     , ( select st.sql_text from v$sqlstats st where st.sql_id = a.sql_id ) as sql_text
     , coalesce
       ( ( select pls.owner || '.' || pls.object_name || '.' || pls.procedure_name
           from   dba_procedures pls
           where  pls.object_id = plsql_entry_object_id and pls.subprogram_id = plsql_entry_subprogram_id )
       , to_char(plsql_entry_subprogram_id) ) as plsql_entry_object
from   ( select ash.sql_id
              , ash.wait_class
              , ash.sql_opcode
              , ash.plsql_entry_object_id
              , ash.plsql_entry_subprogram_id
              , ash.current_obj#
              , count(*) as samples
              , cast(numtodsinterval(count(*), 'SECOND') as interval day(1) to second(0)) as elapsed  -- assumes 1 sample per sec
              , grouping_id
                ( ash.sql_id
                , ash.wait_class
                , ash.plsql_entry_object_id
                , ash.plsql_entry_subprogram_id
                , ash.current_obj# ) as grouping_id
         from   gv$active_session_history ash
 
                left join gv$session ses
                on   ses.inst_id = ash.inst_id
                and  ses.sid = ash.session_id
                and  ses.serial# = ash.session_serial#
                and  ses.sql_id = ash.sql_id
                and  ses.row_wait_obj# = ash.current_obj#
                and  ses.plsql_object_id = ash.plsql_object_id
                and  ses.plsql_subprogram_id = ash.plsql_subprogram_id
 
         where  ash.session_id = :sid
         and    ash.session_serial# = :serial#
         and    ash.sql_id is not null
         and    ash.current_obj# is not null
         group by grouping sets
              ( ( ash.inst_id, ash.sql_id
                , ash.wait_class
                , ash.sql_opcode
                , ash.plsql_entry_object_id
                , ash.plsql_entry_subprogram_id
                , ash.current_obj#, ses.sid )
              , () )
       ) a
order by grouping_id asc, samples desc

ASH summary this query with caller

Next I have a GROUP-BY query for the current sql_id, in descending order of sample count. The idea is to see where the time is being spent in the currently executing statement (rather than which statements have taken time in the current session). Since Active Session History uses a 1-second polling interval, something that occurs in 10 samples has probably taken about 10 seconds. Note that it only filters on sql_id, so multiple executions of the same query by the session will all be aggregated together. (In 11g you could use the new sql_exec_id column to distinguish between executions.) Also bear in mind that ASH may sample activity as 'On CPU' together with a database object - this only means the last object accessed at the time the sample was taken, not that the CPU was necessarily related to that object.

I have two flavours of this one, with and without details of the calling PL/SQL procedure.

select current_wait
     , case
          when grouping_id > 0 then '(Total:)'
          else session_state
       end as session_state
     , wait_class
     , elapsed
     , round(100 * ratio_to_report(samples) over (partition by grouping_id),1) as percent
     , case when current_obj# > 0 then
          ( select distinct o.owner || '.' || o.object_name || rtrim('.' || o.subobject_name,'.')
            from   dba_objects o where o.object_id = current_obj# )
       end as wait_object
     , coalesce
       ( ( select pls.owner || '.' || pls.object_name || '.' || pls.procedure_name
           from   dba_procedures pls
           where  pls.object_id = plsql_entry_object_id and pls.subprogram_id = plsql_entry_subprogram_id )
       , to_char(plsql_entry_subprogram_id) ) as plsql_entry_object
     , ( select pls.owner || '.' || pls.object_name || '.' || pls.procedure_name
         from   dba_procedures pls
         where  pls.object_id = plsql_object_id and pls.subprogram_id = plsql_subprogram_id ) as plsql_object
     , ( select sq.sql_text from v$sqlstats sq where sq.sql_id = top_level_sql_id ) as top_level_sql
from   ( select case when ses.sid is not null then 'Y' end as current_wait
              , ash.session_state
              , ash.wait_class
              , ash.plsql_object_id
              , ash.plsql_subprogram_id
              , ash.plsql_entry_object_id
              , ash.plsql_entry_subprogram_id
              , ash.current_obj#
              , ash.top_level_sql_id
              , count(*) as samples
              , cast(numtodsinterval(count(*), 'SECOND') as interval day(1) to second(0)) as elapsed  -- assumes 1 sample per sec
              , grouping_id
                ( ash.sql_id
                , ash.session_state
                , ash.wait_class
                , ash.plsql_object_id
                , ash.plsql_subprogram_id
                , ash.plsql_entry_object_id
                , ash.plsql_entry_subprogram_id
                , ash.current_obj#
                , ash.top_level_sql_id ) as grouping_id
         from   gv$active_session_history ash
 
                left join gv$session ses
                on   ses.inst_id = ash.inst_id
                and  ses.sid = ash.session_id
                and  ses.serial# = ash.session_serial#
                and  ses.sql_id = ash.sql_id
                and  ses.row_wait_obj# = ash.current_obj#
                and  ses.plsql_object_id = ash.plsql_object_id
                and  ses.plsql_subprogram_id = ash.plsql_subprogram_id
 
         where  ash.session_id = :sid
         and    ash.session_serial# = :serial#
         and    ash.sql_id = :sql_id
         group by grouping sets
              ( ( ash.inst_id, ash.sql_id, ash.session_state
                , ash.wait_class
                , ash.plsql_object_id
                , ash.plsql_subprogram_id
                , ash.plsql_entry_object_id
                , ash.plsql_entry_subprogram_id
                , ash.current_obj#
                , ses.sid
                , ash.top_level_sql_id )
              , () )
       )
order by grouping_id asc, samples desc

ASH summary this query SQL only

This is the same as the previous query, but without the calling PL/SQL details to give a clearer view of the database access.

select a.current_wait
     , case
          when a.grouping_id > 0 then '(Total:)'
          else a.session_state
       end as session_state
     , a.wait_class
     , a.elapsed
     , round(100 * ratio_to_report(a.samples) over (partition by a.grouping_id),1) as percent
     , case when a.current_obj# > 0 then
          ( select distinct o.owner || '.' || o.object_name || rtrim('.' || o.subobject_name,'.')
            from   dba_objects o where o.object_id = current_obj# )
       end as wait_object
from   ( select case ses.row_wait_obj# when ash.current_obj# then 'Y' end as current_wait
              , ash.session_state
              , ash.wait_class
              , ash.current_obj#
              , count(*) as samples
              , cast(numtodsinterval(count(*), 'SECOND') as interval day(1) to second(0)) as elapsed  -- assumes 1 sample per sec
              , ash.inst_id
              , grouping_id
                ( ash.sql_id
                , ash.session_state
                , ash.wait_class
                , ash.current_obj#
                , ses.row_wait_obj# ) as grouping_id
         from   gv$active_session_history ash

                left join gv$session ses
                on   ses.inst_id = ash.inst_id
                and  ses.sid = ash.session_id
                and  ses.serial# = ash.session_serial#
                and  ses.sql_id = ash.sql_id

         where  ash.session_id = :sid
         and    ash.session_serial# = :serial#
         and    ash.sql_id = :sql_id
         group by grouping sets
              ( ( ash.inst_id, ash.sql_id, ash.session_state
                , ash.wait_class
                , ash.current_obj#
                , ses.row_wait_obj# )
              , () )
       ) a
order by grouping_id asc, samples desc

ASH detail this session

Finally, I have a straight listing of v$active_session_history for the current session so you can get an idea of what it is currently doing:

select ash.sample_time
     , ash.sql_id
     , ash.blocking_session, ash.session_state
     , ash.wait_class
     , case when ash.current_obj# > 0 then
          ( select o.owner || '.' || o.object_name || rtrim('.' || o.subobject_name,'.') from dba_objects o where o.object_id = nullif(ash.current_obj#,-1) )
       end as wait_object
     , case when ash.plsql_entry_object_id is not null then
          ( select pls.owner || '.' || pls.object_name || '.' || pls.procedure_name
            from   dba_procedures pls
            where  pls.object_id = ash.plsql_entry_object_id and pls.subprogram_id = ash.plsql_entry_subprogram_id )
       end as plsql_entry_object
     , case when ash.plsql_object_id is not null then
          ( select pls.owner || '.' || pls.object_name || '.' || pls.procedure_name
            from   dba_procedures pls
            where  pls.object_id = ash.plsql_object_id and pls.subprogram_id = ash.plsql_subprogram_id )
       end as plsql_object
from   gv$active_session_history ash
where  ash.inst_id = :instance
and    ash.session_id = :sid
and    ash.session_serial# = :serial#
order by sample_time desc

Locks

The default setup does come with a "Locks" tab. Let's say a session performs the following actions:

update locations set capacity = 3 where room_id = 1;

lock table big_table in exclusive mode nowait;

lock table test_table in share update mode;

The default "Locks" tab displays this:

Output from the default Locks tab

Changing it to the following provides some more details:

select decode(l.xidusn, 0,'Waiting','Held') as lock_status  -- xidusn is the undo segment ID
     , l.locked_mode
     , decode( l.locked_mode
             , 0, case when l.xidusn = 0 then 'Table-Exclusive' else 'None' end  -- from trial and error!
             , 1, 'NULL'
             , 2, 'Row-Shared (select for update) or table in share update mode'
             , 3, 'Row-Exclusive (insert/update/delete) or select for update'
             , 4, 'Table-Share (historically, unindexed FK)'
             , 5, 'Share Row-Exclusive'
             , 6, 'Table-Exclusive'
             , l.locked_mode ) AS locked_mode
     , o.owner as object_owner, o.object_name, o.subobject_name
     , to_date(t.start_time,'MM/DD/RR HH24:MI:SS') AS start_time
     , l.process
from   v$locked_object l
       join dba_objects o on o.object_id = l.object_id
       left join v$transaction t
       on t.xidusn = l.xidusn and t.xidslot = l.xidslot and t.xidsqn = l.xidsqn
where  l.session_id = :sid
and    l.oracle_username = :username
and    l.os_user_name = :osuser
order  by o.object_name

Output from the customised Locks tab

Optimizer nonstandard

I find this useful to check what optimizer settings are in use by a particular session (which may not be the same settings as your session or the instance defaults).

select sql_feature, name, sy.value as instance_setting, se.value as session_setting
from   v$ses_optimizer_env se join v$sys_optimizer_env sy using(name,sql_feature)
where  se.sid = :sid
and se.value <> sy.value
order by sql_feature, name

This joins v$sys_optimizer_env (optimizer-related system parameters) with v$ses_optimizer_env (optimizer-related session parameters, initially inherited from the system-level settings but reflecting any changes made by alter session commands) and reports differences.

Temp space

How much temp space is this session using for hash joins, sorts etc?

select s.tablespace
     , s.contents
     , s.segtype
     , s.sql_id
     , s.segfile#
     , s.extents
     , s.blocks
     , s.blocks * f.blocksize_k as KB
     , s.blocks * f.blocksize_m as MB
     , round(s.blocks * f.blocksize_g,1) as GB
from   v$tempseg_usage s
     , ( select distinct tablespace_name
              , bytes/blocks/1024 as blocksize_k
              , bytes/blocks/1048576 as blocksize_m
              , bytes/blocks/1073741824 as blocksize_g
         from   dba_temp_files ) f
where  s.session_addr = :saddr
and    s.session_num = :serial#
and    f.tablespace_name = s.tablespace