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
Customised 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.)
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:
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:
- All sessions that are currently active (except Oracle background processes such as Log Writer), or that are blocking other sessions, or owned by me.
- Their parent, if part of a parallel query
- The object currently being waited for (usually a table or index) - looked up from dba_objects using row_wait_obj#.
- The PL/SQL entry and current procedures - looked up from dba_procedures using the plsql_* columns added in Oracle 10.2.0.2.
- Some statistics about CPU, reads, memory usage and query parsing, from v$sessmetric. When the results are displayed, you can click on these columns to sort sessions by CPU use
- Any session that is blocking another, regardless of its status, in addition to currently active foreground sessions.
- The RAC instance, for multi-node clusters. If you only have a single instance it will be 1 (you might want to move it to the end of the listing to make room for other columns).
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 for 'Active Sessions' (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 se.status
, pxs.qcsid as parent
, se.sid
, se.serial#
, se.blocking_session as waiting_for
, se.username
, se.osuser
, se.module
, se.action
, se.client_info
, rtrim(o.owner||'.'||o.object_name, '.') as called_from
, ( select regexp_substr(s.text, '[^ ().]+', 1, 2) from dba_source s
where s.owner = o.owner
and s.type = o.object_type
and s.name = o.object_name
and s.line < sq.program_line#
and regexp_like(s.text,'^ *(PROCEDURE|FUNCTION) ', 'i')
order by line desc
fetch first row only ) as subprogram
, nullif(sq.program_line#,0) as called_from_line
, ( select aa.name from
( select action, name from audit_actions where action <> 0 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.sql_id
, sq.plan_hash_value
, se.logon_time
, se.sql_exec_start
, cast(numtodsinterval(sysdate - se.sql_exec_start, 'DAY') as interval day(1) to second(0)) as sql_elapsed
, 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
--, 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_child_number
, se.sql_exec_id
--, 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 / power(1024,3),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_gb
, 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
from gv$session se
left join v$sql sq
on sq.sql_id = se.sql_id
and sq.child_number = se.sql_child_number
left join dba_objects o
on o.object_id = sq.program_id
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 )
)
and se.sid <> sys_context('userenv','sid')
order by se.inst_id, pxs.qcsid, pxs.qcserial# nulls first -- PQ controller appears without serial# in v$px_session
, se.sql_id, 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 total_time
, cast(numtodsinterval(elapsed_time / nullif(executions,0) / 1e6,'SECOND') as interval day(0) to second(0)) as average_time
, 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.)
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
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.)
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
, 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:
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
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