Summary Of Changes
1. It runs a reasonable pre-check and informs if any of the objects potentially required by the script aren't accessible. You'll see an output similar to the following in such cases:---------------------------------------------------------------------------------------------- !!Access Check failed!! ---------------------------------------------------------------------------------------------- Error ORA-00942 when accessing GV$SESSION Error ORA-00942 when accessing V$DATABASE Error ORA-00942 when accessing GV$ACTIVE_SESSION_HISTORY Error ORA-00942 when accessing GV$SQL_PLAN_STATISTICS_ALL Error ORA-00942 when accessing GV$SQL_MONITOR Error ORA-00942 when accessing DBA_HIST_ACTIVE_SESS_HISTORY Error ORA-00942 when accessing DBA_HIST_SQL_PLAN2. Furthermore the script no longer appears to "hang" if privileges are missing or anything else unexpected happens. Actually the script wasn't hanging but usually waited for input with termout set to OFF, since some of the defines populated under regular conditions weren't properly populated when things went wrong. This has been corrected and you should now see the above privilege check output along with regular follow-up error messages, but no hanging script any longer. 3. The output has been re-aligned and further sections have been added. In particular the various "Activity" / "Concurrent Activity" summaries should be helpful to get a better understanding of the overall activity profile of the query and concurrently ongoing database activity. See below for more details about these new sections. Note that in case of a cross-instance Parallel Execution the summaries will also be provided on instance level in addition.
10.2 Support
Except for a few sections that simply aren't available when running on pre-11g the script shows quite similar information when used with 10.2 The biggest difference when using the script with 10.2 is caused by the fact that 10.2 ASH neither has a clear indicator of a SQL execution instance (the SQL_EXEC_START / SQL_EXEC_ID columns added in 11g) nor is there a relation to the execution plan lines (SQL_PLAN_LINE_ID etc.) Therefore the SQL_EXEC_START parameter with 10.2 is mandatory, and simply specifies the lower sample time of ASH data to query. Instead of the non-existing SQL_EXEC_ID the script expects a SQL_EXEC_END date input similar to SQL_EXEC_START that obviously determines the upper sample time for ASH data and is also mandatory. Likewise in 10.2 the script doesn't support searching for the most recent execution like it does on 11g, so - as already outlined - it is up to you to determine what ASH data should be searched for a particular SQL_ID by specifying both SQL_EXEC_START and SQL_EXEC_END. Note that if the ASH samples at SQL_EXEC_START don't include samples for the SQL_ID in question, then the script uses the "closest" samples to SQL_EXEC_START found within the range SQL_EXEC_START / SQL_EXEC_END to determine the session information that will be used to limit the ASH data.Short Introduction
Since the initial post what a bit lengthy, allow me to use this opportunity to summarize what the main purpose of the script is: 1. It provides an output that to a significant degree is similar to information provided by the Real-Time SQL Monitoring even in cases where the Real-Time SQL Monitoring report is no longer available. In particular it allows pulling this information even from the DBA_HIST* views, something that is not possible with Real-Time SQL Monitoring, and therefore allows pulling detailed reports about SQL executions as long as ago as the configured AWR retention. Note that this is applicable to both serial and Parallel Execution, and the tool can also provide information about currently executing SQL statements. 2. For Parallel Execution, it offers some important clues. Some of them aren't provided elsewhere, even not by Real-Time SQL Monitoring (At least not up to version 11.2): - It shows information about the Average Active Sessions (AVERAGE_AS column below) with different granularity. This information is crucial in understanding how well the work was distributed among the Parallel Slaves. When looking at the sample report below it becomes immediately obvious that the AVERAGE_AS figures are way below the Parallel degree used.
Global ASH Summary
-----------------------------------------------
INSTANCE_COUNT|FIRST_SAMPLE |LAST_SAMPLE |DURATION_SECS|STATUS |SAMPLE_COUNT|CPU_SAMPLE_COUNT|PERCENTAGE_CPU|SLAVE_COUNT|AVERAGE_AS|MODULE |ACTION
--------------|-------------------|-------------------|-------------|--------|------------|----------------|--------------|-----------|----------|----------------------------------------------------------------|----------------------------------------------------------------
2|2012-10-16 10:52:00|2012-10-16 10:52:36| 38|INACTIVE| 190| 182| 96| 59| 5|SQL*Plus |
Global ASH Summary per Instance
-----------------------------------------------
INSTANCE_ID|FIRST_SAMPLE |LAST_SAMPLE |START_ACTIVE|DURATION_SECS|SAMPLE_COUNT|PROCESS_COUNT|AVERAGE_AS|TIME_ACTIVE_GRAPH
-----------|-------------------|-------------------|------------|-------------|------------|-------------|----------|--------------------
5|2012-10-16 10:52:01|2012-10-16 10:52:36| 2| 36| 111| 30| 3,08| @@@@@@@@@@@@@@@@@@@
6|2012-10-16 10:52:00|2012-10-16 10:52:31| 1| 32| 79| 30| 2,47| @@@@@@@@@@@@@@@@@
Information on Parallel Degree based on ASH
-----------------------------------------------
INSTANCE_ID|DFO |START_ACTIVE|DURATION_SECS|SAMPLE_COUNT|PROCESS_COUNT| SET_COUNT|ASSUMED_DEGREE|ACTUAL_DEGREE|AVERAGE_AS|TIME_ACTIVE_GRAPH
-----------|------|------------|-------------|------------|-------------|----------|--------------|-------------|----------|--------------------
5|Q1 | 2| 36| 111| 30| 2| 15| 16| 3,08| @@@@@@@@@@@@@@@@@@@
6|Q1 | 2| 31| 78| 29| 2| 15| 16| 2,52| @@@@@@@@@@@@@@@@
In particular the "Parallel Slave activity", which corresponds to the "Parallel" tab of the Real-Time SQL Monitoring and the "Activity Timeline", which corresponds to a mixture of the "Activity" and "Metrics" tab of the Real-Time SQL Monitoring, allows spotting any anomalies regarding skew (both temporal and data distribution skew).
Below example shows that one Parallel Slave is sampled much more than the others. The Activity Timeline (scroll to the right to see the relevant columns) shows that only for a short period of time the expected number of slaves where active at the same time, and a long period of only one average active session.
Parallel Slave activity overview based on ASH
-----------------------------------------------
| | | | SAMPLE| SAMPLE| | | |TOP |
| | SAMPLE| | COUNT| COUNT| PERCENT|MAX |MAX |ACTIVE |TOP
INSTANCE_ID|PROCESS | COUNT|ACTIVITY_GRAPH | CPU| OTHER| CPU|PGA |TEMP |PLAN LINES |ACTIVITIES
-----------|----------------------------------------------------------------|----------|--------------------|----------|----------|----------|------|------|----------------------------------------|----------------------------------------------------------------------
5|5-P000 | 3| | 3| 0| 100| 2026K| |20(3) |ON CPU(3)
|5-P001 | 4| | 4| 0| 100| 2289K| |20(4) |ON CPU(4)
|5-P002 | 4| | 4| 0| 100| 2140K| |20(4) |ON CPU(4)
|5-P003 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|5-P004 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|5-P005 | 4| | 4| 0| 100| 2026K| |20(3),22(1) |ON CPU(4)
|5-P006 | 4| | 4| 0| 100| 2009K| |20(4) |ON CPU(4)
|5-P007 | 36|@@@@ | 36| 0| 100| 4451K| |5(28),20(4),4(2),... |ON CPU(36)
|5-P008 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|5-P009 | 4| | 4| 0| 100| 2075K| |20(4) |ON CPU(4)
|5-P010 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
.
.
.
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | AVERAGE|TOP | | |AVERAGE
| | | | | | ACTIVE|ACTIVE |TOP |TOP |ACTIVE SESSIONS
INSTANCE_ID|DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|PLAN LINES |ACTIVITIES |PROCESSES |GRAPH
-----------|-------------|------|------|----------|----------|----------|----------------------------------------|----------------------------------------------------------------------|------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5| 1| | | 0| 0| 0| | | |
6 | | | 0| 0| 0| | | |
5| 2| | | 0| 0| 0| | | |
6 | 1699K| | 0| 1| 1|2(1) |PX Nsq: PQ load info query(1) |6-sqlplus.exe(1) |*
5| 3| 122M| 15M| 28| 2| 30|20(16),14(14) |ON CPU(28),enq: XL - fault extent map(1),os thread startup(1) |5-P000(1),5-P001(1),5-P002(1),5-P003(1),5-P004(1),... |@@@@@@@@@@@@@@@@@@@@@@@@@@@@**
6 | 99M| 14M| 24| 5| 29|20(16),14(13) |ON CPU(24),enq: XL - fault extent map(4),os thread startup(1) |6-P000(1),6-P001(1),6-P002(1),6-P003(1),6-P004(1),... |@@@@@@@@@@@@@@@@@@@@@@@@*****
5| 4| 50M| 6291K| 17| 0| 17|20(15),14(2) |ON CPU(17) |5-P001(1),5-P002(1),5-P003(1),5-P004(1),5-P005(1),... |@@@@@@@@@@@@@@@@@
6 | 32M| | 16| 0| 16|20(16) |ON CPU(16) |6-P000(1),6-P001(1),6-P002(1),6-P003(1),6-P004(1),... |@@@@@@@@@@@@@@@@
5| 5| 33M| | 16| 0| 16|20(15),22(1) |ON CPU(16) |5-P000(1),5-P001(1),5-P002(1),5-P003(1),5-P004(1),... |@@@@@@@@@@@@@@@@
6 | 32M| | 16| 0| 16|20(16) |ON CPU(16) |6-P000(1),6-P001(1),6-P002(1),6-P003(1),6-P004(1),... |@@@@@@@@@@@@@@@@
5| 6| 33M| | 16| 0| 16|20(16) |ON CPU(16) |5-P000(1),5-P001(1),5-P002(1),5-P003(1),5-P004(1),... |@@@@@@@@@@@@@@@@
6 | 38M| 7340K| 16| 0| 16|20(15),14(1) |ON CPU(16) |6-P000(1),6-P001(1),6-P002(1),6-P004(1),6-P005(1),... |@@@@@@@@@@@@@@@@
5| 7| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 8| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 9| 4451K| | 1| 0| 1|6(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 10| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 11| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 12| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 13| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 14| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 15| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 16| 4451K| | 1| 0| 1|11(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 17| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 18| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 19| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
.
.
.
Note that it is important to understand that this information doesn't tell you anything about the efficiency of the execution plan performed, it just tells you how many Parallel Slaves were active concurrently on average. Whether the operation performed is efficient or not is a different question, however even an otherwise efficient execution plan might run much longer than necessary if the work isn't distributed well among the Parallel Slaves.
- Furthermore it allows systematic troubleshooting of data distribution skew by showing the number of samples per process on exection plan line level. This makes it easy to spot those execution plan operations that were affected by data distribution skew. Armed with this knowledge it can be systematically evaluated if changing the data distribution method of the corresponding join operations allows influencing this reasonably.
The "Parallel Distribution ASH" and "Parallel Distribution Graph ASH" columns added to the DBMS_XPLAN.DISPLAY* output provide that information.
The "Parallel Distribution ASH" shows the process names along with the number of samples found (in descending order). For Parallel Slaves, this is Pxxx, for all other process names it is the actual name found in the PROCESS column. If the execution is cross-instance, the name is prefixed with the instance number:
-------------------------------------------------------------------------------------------- | Parallel Distribution ASH | Parallel Distribution Graph ASH | -------------------------------------------------------------------------------------------- | | | | | | | 6-sqlplus.exe(1) | 0 | | | | | 5-P007(2) | 0 | | 5-P007(28) | 0000000 | | 5-P007(1) | 0 | | | | | | | | | | | | | | 5-P007(1) | 0 | | | | | | | | 5-P021(2),5-P031(2),6-P016(2),6-P031(2),5-P016(1),... | 0123456789ABCDEF0123456789A | | | | | | | | | | | | | | | | | 5-P001(4),5-P002(4),5-P003(4),5-P004(4),5-P006(4),... | 0123456789ABCDEF0123456789ABCDEF | | | | | 5-P005(1) | 0 | --------------------------------------------------------------------------------------------Ideally the number of samples per process should be quite similar, if there is a significant difference, then very likely the underlying cause is an uneven data distribution among the Parallel Slaves. This is how it should look like ideally:
-------------------------------------------------------------------------------------------- | Parallel Distribution ASH | Parallel Distribution Graph ASH | -------------------------------------------------------------------------------------------- | 5-P001(4),5-P002(4),5-P003(4),5-P004(4),5-P006(4),... | 0123456789ABCDEF0123456789ABCDEF |This is how it should not look like:
-------------------------------------------------------------------------------------------- | Parallel Distribution ASH | Parallel Distribution Graph ASH | -------------------------------------------------------------------------------------------- | 5-P007(28) | 0000000 |The "Parallel Distribution Graph ASH" attempts a graphical representation of the information that you see in the "Parallel Distribution ASH" column. The idea of the "Graph" is that you don't need to work through all the rows of the execution plan in the "Parallel Distribution ASH" column and look at the actual sample numbers, but simply can sweep through the "Graph" in order to easily identify the potential skew candidates. So, if you have a uniform distribution of work among the Parallel Slaves, you'll see something like this: 0123456789ABCDEF... (it's just 16 repeating hex-characters if you have more than 16 slaves sampled for an execution plan line) But, if you have a non-uniform distribution of work among the Parallel Slaves, you'll see something like this: 000000000001111223 or in extreme cases 0000000000000 which means that only a few slaves (or only one in the last example) had to do the all of work. If the graph doesn't correspond to what you think to see in the "Parallel Distribution ASH" column - the distribution of work looks skewed, but the graph doesn't correspond to what I've just explained, then try the "ASH,DISTRIB,TIMELINE" option instead. In this case even a single sample of a single Parallel Slave will show up as a very wide (so this option might produce false positives): 0000000000000000000000000 which you then need to cross-check with the "Parallel Distribution ASH" column for relevance: If you have only have a few samples per line, then it is probably not relevant since you simply haven't spend significant time there. But the advantage of this representation is that skew becomes more visible that might not be obvious when using the default DISTRIB_REL option. The underlying reason is that the default option shows the distribution graph relative to the operation with the greatest number of samples assigned to. So if there are execution plan lines with a much higher sample number than the remaining lines (but these samples are not skewed), the graph might be so compressed that the skew doesn't become obvious.
Section Overview For Version 2.0
General Information
General information ----------------------------------------------- SQL_ID |CHILD_NUMBER|SQL_EXEC_START |SQL_EXEC_ID|FORMAT_OPTION |LAST_EXEC_SOURCE|ASH_OPTIONS |ASH_SOURCE -------------|------------|-------------------|-----------|-------------------------|----------------|------------------------|---------- 3pfa4kgc92z10| 1|2012-10-16 10:51:59| 100663297|ADVANCED |SID=446@6 |ASH,DISTRIB_REL,TIMELINE|CURRThis section as in the previous version shows some general info and the options used so that it can be verified that the report is about the intended SQL execution.
Global ASH Summary
Global ASH Summary
-----------------------------------------------
INSTANCE_COUNT|FIRST_SAMPLE |LAST_SAMPLE |DURATION_SECS|STATUS |SAMPLE_COUNT|CPU_SAMPLE_COUNT|PERCENTAGE_CPU|SLAVE_COUNT|AVERAGE_AS|MODULE |ACTION
--------------|-------------------|-------------------|-------------|--------|------------|----------------|--------------|-----------|----------|----------------------------------------------------------------|----------------------------------------------------------------
2|2012-10-16 10:52:00|2012-10-16 10:52:36| 38|INACTIVE| 190| 182| 96| 59| 5|SQL*Plus |
Global ASH Summary per Instance
-----------------------------------------------
INSTANCE_ID|FIRST_SAMPLE |LAST_SAMPLE |START_ACTIVE|DURATION_SECS|SAMPLE_COUNT|PROCESS_COUNT|AVERAGE_AS|TIME_ACTIVE_GRAPH
-----------|-------------------|-------------------|------------|-------------|------------|-------------|----------|--------------------
5|2012-10-16 10:52:01|2012-10-16 10:52:36| 2| 36| 111| 30| 3,08| @@@@@@@@@@@@@@@@@@@
6|2012-10-16 10:52:00|2012-10-16 10:52:31| 1| 32| 79| 30| 2,47| @@@@@@@@@@@@@@@@@
Similar to the previous version this section shows a general summary of the ASH samples found for this SQL execution. If a cross-instance execution is detected, the summary will also be shown on instance level in addition which allows understanding to what extend the instances where involved in the execution.
Global ASH Summary for concurrent activity
Global ASH Summary for concurrent activity
-----------------------------------------------
INSTANCE_ID|SAMPLE_COUNT|CPU_SAMPLE_COUNT|PERCENTAGE_CPU|AVERAGE_AS|FOREGROUND_COUNT|BACKGROUND_COUNT|SLAVE_COUNT|CLIENT_COUNT|PROCESS_COUNT|TRANSACT_COUNT|SQL_ID_COUNT|SQL_EXECUTION_COUNT|MODULE_COUNT|ACTION_COUNT
-----------|------------|----------------|--------------|----------|----------------|----------------|-----------|------------|-------------|--------------|------------|-------------------|------------|------------
5| 6| 2| 33| ,16| 1| 5| 0| 1| 4| 0| 0| 1| 1| 0
6| 4| 1| 25| ,11| 0| 4| 0| 1| 4| 0| 0| 1| 0| 0
This is a new section that takes advantage of the fact that ASH data covers all activity and therefore shows a summary of the ASH samples found during the execution that do not belong to this SQL execution. This can provide important clues why you might see variations in runtime although execution plans and data are similar. Note that for cross-instance executions the concurrent activity covered is limited to the instances participating in the execution. Although in principle the activity on instances not participating can also influence this SQL execution for various reasons I have decided to exclude that information - which can of course easily be changed in the corresponding queries.
Concurrent Activity Summary (not this execution)
Concurrent Activity Summary (not this execution)
-----------------------------------------------
INSTANCE_ID|ACTIVITY |ACTIVITY_CLASS |SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
-----------|--------------------------------------------------|--------------------|------------|----------|--------------------
5|reliable message |Other | 3| 30|@@@@@@
|ON CPU |ON CPU | 2| 20|@@@@
|SQL*Net break/reset to client |Application | 1| 10|@@
6|ASM file metadata operation |Other | 1| 10|@@
|enq: PV - syncstart |Other | 1| 10|@@
|control file parallel write |System I/O | 1| 10|@@
|ON CPU |ON CPU | 1| 10|@@
This is again a new section with a more detailed breakdown of the concurrent activity. For cross-instance executions this will be on instance level.
Information on Parallel Degree based on ASH
Information on Parallel Degree based on ASH
-----------------------------------------------
INSTANCE_ID|DFO |START_ACTIVE|DURATION_SECS|SAMPLE_COUNT|PROCESS_COUNT| SET_COUNT|ASSUMED_DEGREE|ACTUAL_DEGREE|AVERAGE_AS|TIME_ACTIVE_GRAPH
-----------|------|------------|-------------|------------|-------------|----------|--------------|-------------|----------|--------------------
5|Q1 | 2| 36| 111| 30| 2| 15| 16| 3,08| @@@@@@@@@@@@@@@@@@@
6|Q1 | 2| 31| 78| 29| 2| 15| 16| 2,52| @@@@@@@@@@@@@@@@
This section is more or less unchanged to the previous version and only shows up if Parallel Execution gets used, you're running 11g+ and an execution plan could be found for the SQL statement.
It will show details about the different Data Flow Operations (DFOs) a Parallel Execution could consist of, in particular the activity related to each DFO, the Parallel Degree per DFO (could be different per DFO) and if the DFOs were active at the same time or not. In case of a cross-instance Parallel Execution this information will be broken down per participating instance.
Note that from 11.2.0.2 on the actual Parallel Degree is stored in the newly added PX_FLAGS column of the ASH data, so you get an additional column ACTUAL_DEGREE, whereas the ASSUMED_DEGREE uses the number of sampled slaves and the execution plan operations from the corresponding execution plan section for an educated guess about the degree.
Parallel Slave activity overview based on ASH
Parallel Slave activity overview based on ASH
-----------------------------------------------
| | | | SAMPLE| SAMPLE| | | |TOP |
| | SAMPLE| | COUNT| COUNT| PERCENT|MAX |MAX |ACTIVE |TOP
INSTANCE_ID|PROCESS | COUNT|ACTIVITY_GRAPH | CPU| OTHER| CPU|PGA |TEMP |PLAN LINES |ACTIVITIES
-----------|----------------------------------------------------------------|----------|--------------------|----------|----------|----------|------|------|----------------------------------------|----------------------------------------------------------------------
5|5-P000 | 3| | 3| 0| 100| 2026K| |20(3) |ON CPU(3)
|5-P001 | 4| | 4| 0| 100| 2289K| |20(4) |ON CPU(4)
|5-P002 | 4| | 4| 0| 100| 2140K| |20(4) |ON CPU(4)
|5-P003 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|5-P004 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|5-P005 | 4| | 4| 0| 100| 2026K| |20(3),22(1) |ON CPU(4)
|5-P006 | 4| | 4| 0| 100| 2009K| |20(4) |ON CPU(4)
|5-P007 | 36|@@@@ | 36| 0| 100| 4451K| |5(28),20(4),4(2),... |ON CPU(36)
|5-P008 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|5-P009 | 4| | 4| 0| 100| 2075K| |20(4) |ON CPU(4)
|5-P010 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|5-P011 | 4| | 4| 0| 100| 2009K| |20(4) |ON CPU(4)
|5-P012 | 4| | 4| 0| 100| 2009K| |20(4) |ON CPU(4)
|5-P013 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|5-P014 | 4| | 4| 0| 100| 2009K| |20(4) |ON CPU(4)
|5-P015 | 4| | 4| 0| 100| 2009K| |20(4) |ON CPU(4)
|5-P016 | 1| | 1| 0| 100| 6286K| 1049K|14(1) |ON CPU(1)
|5-P017 | 1| | 1| 0| 100| 6286K| 1049K|14(1) |ON CPU(1)
|5-P018 | 1| | 1| 0| 100| 6286K| 1049K|14(1) |ON CPU(1)
|5-P019 | 1| | 1| 0| 100| 6221K| 1049K|14(1) |ON CPU(1)
|5-P020 | 1| | 0| 1| 0| 7007K| 1049K|14(1) |enq: XL - fault extent map(1)
|5-P021 | 2| | 2| 0| 100| 9170K| 3146K|14(2) |ON CPU(2)
|5-P024 | 1| | 1| 0| 100| 6221K| 1049K|14(1) |ON CPU(1)
|5-P025 | 1| | 1| 0| 100| 6680K| 1049K|14(1) |ON CPU(1)
|5-P026 | 1| | 0| 1| 0| 6352K| 1049K|14(1) |os thread startup(1)
|5-P027 | 1| | 1| 0| 100| 6221K| 1049K|14(1) |ON CPU(1)
|5-P028 | 1| | 1| 0| 100| 6221K| 1049K|14(1) |ON CPU(1)
|5-P029 | 1| | 1| 0| 100| 6286K| 1049K|14(1) |ON CPU(1)
|5-P030 | 1| | 1| 0| 100| 6221K| 1049K|14(1) |ON CPU(1)
|5-P031 | 2| | 2| 0| 100| 9956K| 3146K|14(2) |ON CPU(2)
6|6-P000 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|6-P001 | 4| | 4| 0| 100| 1961K| |20(4) |ON CPU(4)
|6-P002 | 4| | 4| 0| 100| 1895K| |20(4) |ON CPU(4)
|6-P003 | 3| | 3| 0| 100| 1961K| |20(3) |ON CPU(3)
|6-P004 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|6-P005 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|6-P006 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|6-P007 | 4| | 4| 0| 100| 1961K| |20(4) |ON CPU(4)
|6-P008 | 4| | 4| 0| 100| 1961K| |20(4) |ON CPU(4)
|6-P009 | 4| | 4| 0| 100| 1961K| |20(4) |ON CPU(4)
|6-P010 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|6-P011 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|6-P012 | 4| | 4| 0| 100| 1895K| |20(4) |ON CPU(4)
|6-P013 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|6-P014 | 4| | 4| 0| 100| 2026K| |20(4) |ON CPU(4)
|6-P015 | 4| | 4| 0| 100| 1961K| |20(4) |ON CPU(4)
|6-P016 | 2| | 1| 1| 50| 8056K| 7340K|14(2) |ON CPU(1),os thread startup(1)
|6-P017 | 1| | 0| 1| 0| 5107K| 1049K|14(1) |enq: XL - fault extent map(1)
|6-P019 | 1| | 1| 0| 100| 5107K| 1049K|14(1) |ON CPU(1)
|6-P020 | 1| | 1| 0| 100| 5107K| 1049K|14(1) |ON CPU(1)
|6-P021 | 1| | 1| 0| 100| 5172K| 1049K|14(1) |ON CPU(1)
|6-P022 | 1| | 0| 1| 0| 5107K| 1049K|14(1) |enq: XL - fault extent map(1)
|6-P025 | 1| | 1| 0| 100| 5107K| 1049K|14(1) |ON CPU(1)
|6-P026 | 1| | 1| 0| 100| 5172K| 1049K|14(1) |ON CPU(1)
|6-P027 | 1| | 1| 0| 100| 5107K| 1049K|14(1) |ON CPU(1)
|6-P028 | 1| | 0| 1| 0| 5172K| 1049K|14(1) |enq: XL - fault extent map(1)
|6-P029 | 1| | 0| 1| 0| 5172K| 1049K|14(1) |enq: XL - fault extent map(1)
|6-P030 | 1| | 1| 0| 100| 5172K| 1049K|14(1) |ON CPU(1)
|6-P031 | 2| | 2| 0| 100| 7007K| 8389K|14(2) |ON CPU(2)
|6-sqlplus.exe | 1| | 0| 1| 0| 1699K| |2(1) |PX Nsq: PQ load info query(1)
This new section corresponds to the "Parallel" tab of the Real-Time SQL Monitoring and only shows up if Parallel Execution was detected. In particular for 10g it is very helpful for Parallel Execution analysis since the even more helpful analysis on Execution Plan line level is not available.
It will show the activities found in the ASH data per every process sampled as part of the Parallel Execution.
The most useful information in this section is very likely the "Activity Graph" column as it allows easily spotting distribution problems, although it doesn't allow the systematic troubleshooting of such issues that the execution plan line level information does, as explained above.
Depending on the version also the "Top Activities" and "Top Active Plan Lines" will also be shown per process. As usual the N of the Top N can be configured in the configuration section of the script.
Activity Class Summary
Activity Class Summary
-----------------------------------------------
ACTIVITY_CLASS |SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
--------------------|------------|----------|--------------------
ON CPU | 182| 96|@@@@@@@@@@@@@@@@@@@
Other | 6| 3|@
Concurrency | 2| 1|
Activity Class Summary per Instance
-----------------------------------------------
INSTANCE_ID|ACTIVITY_CLASS |SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
-----------|--------------------|------------|----------|--------------------
5|ON CPU | 109| 57|@@@@@@@@@@@
|Other | 1| 1|
|Concurrency | 1| 1|
6|ON CPU | 73| 38|@@@@@@@@
|Other | 5| 3|@
|Concurrency | 1| 1|
This is again a new section that summarizes the activity found for this SQL execution and in principle corresponds to some part of the header information shown in Real-Time SQL Monitoring.
The activity is broken down between CPU and non-CPU, and for non-CPU the different Activity Classes will be shown. In case of a cross-instance Parallel Execution this section will be repeated, this time broken down per instance.
Activity Summary
Activity Summary
-----------------------------------------------
ACTIVITY |ACTIVITY_CLASS |SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
--------------------------------------------------|--------------------|------------|----------|--------------------
ON CPU |ON CPU | 182| 96|@@@@@@@@@@@@@@@@@@@
enq: XL - fault extent map |Other | 5| 3|@
os thread startup |Concurrency | 2| 1|
PX Nsq: PQ load info query |Other | 1| 1|
Activity Summary per Instance
-----------------------------------------------
INSTANCE_ID|ACTIVITY |ACTIVITY_CLASS |SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
-----------|--------------------------------------------------|--------------------|------------|----------|--------------------
5|ON CPU |ON CPU | 109| 57|@@@@@@@@@@@
|os thread startup |Concurrency | 1| 1|
|enq: XL - fault extent map |Other | 1| 1|
6|ON CPU |ON CPU | 73| 38|@@@@@@@@
|enq: XL - fault extent map |Other | 4| 2|
|os thread startup |Concurrency | 1| 1|
|PX Nsq: PQ load info query |Other | 1| 1|
This new section corresponds to the previous one but shows a more granular breakdown of the activity on wait event level rather than wait class. Apart from that the same applies as above, also it will be repeated in case of a cross-instance Parallel Execution on instance level.
Activity Timeline based on ASH
Activity Timeline based on ASH
-----------------------------------------------
| | | | | | AVERAGE|TOP | | |AVERAGE
| | | | | | ACTIVE|ACTIVE |TOP |TOP |ACTIVE SESSIONS
INSTANCE_ID|DURATION_SECS|PGA |TEMP | CPU| OTHER| SESSIONS|PLAN LINES |ACTIVITIES |PROCESSES |GRAPH
-----------|-------------|------|------|----------|----------|----------|----------------------------------------|----------------------------------------------------------------------|------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5| 1| | | 0| 0| 0| | | |
6 | | | 0| 0| 0| | | |
5| 2| | | 0| 0| 0| | | |
6 | 1699K| | 0| 1| 1|2(1) |PX Nsq: PQ load info query(1) |6-sqlplus.exe(1) |*
5| 3| 122M| 15M| 28| 2| 30|20(16),14(14) |ON CPU(28),enq: XL - fault extent map(1),os thread startup(1) |5-P000(1),5-P001(1),5-P002(1),5-P003(1),5-P004(1),... |@@@@@@@@@@@@@@@@@@@@@@@@@@@@**
6 | 99M| 14M| 24| 5| 29|20(16),14(13) |ON CPU(24),enq: XL - fault extent map(4),os thread startup(1) |6-P000(1),6-P001(1),6-P002(1),6-P003(1),6-P004(1),... |@@@@@@@@@@@@@@@@@@@@@@@@*****
5| 4| 50M| 6291K| 17| 0| 17|20(15),14(2) |ON CPU(17) |5-P001(1),5-P002(1),5-P003(1),5-P004(1),5-P005(1),... |@@@@@@@@@@@@@@@@@
6 | 32M| | 16| 0| 16|20(16) |ON CPU(16) |6-P000(1),6-P001(1),6-P002(1),6-P003(1),6-P004(1),... |@@@@@@@@@@@@@@@@
5| 5| 33M| | 16| 0| 16|20(15),22(1) |ON CPU(16) |5-P000(1),5-P001(1),5-P002(1),5-P003(1),5-P004(1),... |@@@@@@@@@@@@@@@@
6 | 32M| | 16| 0| 16|20(16) |ON CPU(16) |6-P000(1),6-P001(1),6-P002(1),6-P003(1),6-P004(1),... |@@@@@@@@@@@@@@@@
5| 6| 33M| | 16| 0| 16|20(16) |ON CPU(16) |5-P000(1),5-P001(1),5-P002(1),5-P003(1),5-P004(1),... |@@@@@@@@@@@@@@@@
6 | 38M| 7340K| 16| 0| 16|20(15),14(1) |ON CPU(16) |6-P000(1),6-P001(1),6-P002(1),6-P004(1),6-P005(1),... |@@@@@@@@@@@@@@@@
5| 7| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 8| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 9| 4451K| | 1| 0| 1|6(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 10| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 11| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 12| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 13| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 14| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 15| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 16| 4451K| | 1| 0| 1|11(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 17| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 18| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 19| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 20| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 21| 4451K| | 1| 0| 1|4(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 22| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 23| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 24| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 25| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 26| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 27| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 28| 4451K| | 1| 0| 1|4(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 29| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 30| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 31| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 32| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 33| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | 7007K| 8389K| 1| 0| 1|14(1) |ON CPU(1) |6-P031(1) |@
5| 34| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 35| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 36| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 37| 4451K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
5| 38| 4386K| | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | 0| 0| 0| | | |
This section was previously called "Average Active Session Graph", but since a lot of information has been added it is now called "Activity Timeline" and will also be shown for serial execution if the "TIMELINE" option was specified.
In addition to the already previously available information about the average active sessions and their activity (CPU or non-CPU) plus PGA/TEMP now the top active plan lines (11g+), activities and processes per bucket will be shown. Note that the N of the "Top N sections" can be configured in the "configuration" section of the script.
Activity on execution plan line level
Activity on execution plan line level
-----------------------------------------------
Wrote file .xplan_ash_temp
SQL_ID 3pfa4kgc92z10, child number 1
-------------------------------------
select max(t1_id), max(t1_filler), max(t2_id), max(t2_filler),
max(t3_id), max(t3_filler) from ( select /*+ monitor no_merge
parallel(t1 16 2) PQ_DISTRIBUTE(T1 HASH HASH) */ t1.id as
t1_id , regexp_replace(t3_filler, '^\s+([[:alnum:]]+)\s+$',
lpad('\1', 10), 1, 1, 'c') as t1_filler , v1.* from (
select /*+ no_merge parallel(t2 16 2) parallel(t3 16 2)
PQ_DISTRIBUTE(T2 HASH HASH) */ t2.id as t2_id
, t2.filler as t2_filler , t2.id2 as t2_id2
, t3.id as t3_id , t3.filler as t3_filler
from t1 t2 , t2 t3 where
t3.id = t2.id (+) and regexp_replace(t3.filler,
'^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') =
regexp_replace(t2.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1,
1, 'c') ) v1 , t1 where v1.t2_id2 = t1.id2 (+) +
2001 ) --where rownum > 1
Plan hash value: 1110592967
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | Start | Dur | Time Active Graph | Parallel Distribution ASH | Parallel Distribution Graph ASH | Activity Graph ASH | Top 5 Activity ASH |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | | 23 | SELECT STATEMENT | | | | 32 (100)| | | | | | | | | | | |
| 1 | 0 | 22 | SORT AGGREGATE | | 1 | 2145 | | | | | | | | | | | | |
| 2 | 1 | 21 | PX COORDINATOR | | | | | | | | | +1 | 1 | @ | 6-sqlplus.exe(1) | 0 | | PX Nsq: PQ load info query(1) |
| 3 | 2 | 20 | PX SEND QC (RANDOM) | :TQ10004 | 1 | 2145 | | | Q1,04 | P->S | QC (RAND) | | | | | | | |
| 4 | 3 | 19 | SORT AGGREGATE | | 1 | 2145 | | | Q1,04 | PCWP | | +20 | 8 | @@@@ | 5-P007(2) | 0 | | ON CPU(2) |
| 5 | 4 | 18 | VIEW | | 2000K| 4091M| 32 (7)| 00:00:01 | Q1,04 | PCWP | | +6 | 32 | @@@@@@@@@@@@@@@@@ | 5-P007(28) | 0000000 | @@@ | ON CPU(28) |
|* 6 | 5 | 17 | HASH JOIN RIGHT OUTER | | 2000K| 288M| 32 (7)| 00:00:01 | Q1,04 | PCWP | | +8 | 1 | @ | 5-P007(1) | 0 | | ON CPU(1) |
| 7 | 6 | 4 | PX RECEIVE | | 2000 | 16000 | 2 (0)| 00:00:01 | Q1,04 | PCWP | | | | | | | | |
| 8 | 7 | 3 | PX SEND HASH | :TQ10002 | 2000 | 16000 | 2 (0)| 00:00:01 | Q1,02 | P->P | HASH | | | | | | | |
| 9 | 8 | 2 | PX BLOCK ITERATOR | | 2000 | 16000 | 2 (0)| 00:00:01 | Q1,02 | PCWC | | | | | | | | |
|* 10 | 9 | 1 | TABLE ACCESS STORAGE FULL | T1 | 2000 | 16000 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | | | | | | |
| 11 | 6 | 16 | PX RECEIVE | | 2000K| 272M| 29 (4)| 00:00:01 | Q1,04 | PCWP | | +15 | 1 | @ | 5-P007(1) | 0 | | ON CPU(1) |
| 12 | 11 | 15 | PX SEND HASH | :TQ10003 | 2000K| 272M| 29 (4)| 00:00:01 | Q1,03 | P->P | HASH | | | | | | | |
| 13 | 12 | 14 | VIEW | | 2000K| 272M| 29 (4)| 00:00:01 | Q1,03 | PCWP | | | | | | | | |
|* 14 | 13 | 13 | HASH JOIN RIGHT OUTER BUFFERED| | 2000K| 411M| 29 (4)| 00:00:01 | Q1,03 | PCWP | | +2 | 31 | @@@@@@@@@@@@@@@@ | 5-P021(2),5-P031(2),6-P016(2),6-P031(2),5-P016(1),... | 0123456789ABCDEF0123456789A | @@@ | ON CPU(24),enq: XL - fault extent map(5),os thread startup(2) |
| 15 | 14 | 8 | PX RECEIVE | | 2000 | 212K| 2 (0)| 00:00:01 | Q1,03 | PCWP | | | | | | | | |
| 16 | 15 | 7 | PX SEND HASH | :TQ10000 | 2000 | 212K| 2 (0)| 00:00:01 | Q1,00 | P->P | HASH | | | | | | | |
| 17 | 16 | 6 | PX BLOCK ITERATOR | | 2000 | 212K| 2 (0)| 00:00:01 | Q1,00 | PCWC | | | | | | | | |
|* 18 | 17 | 5 | TABLE ACCESS STORAGE FULL | T1 | 2000 | 212K| 2 (0)| 00:00:01 | Q1,00 | PCWP | | | | | | | | |
| 19 | 14 | 12 | PX RECEIVE | | 2000K| 204M| 27 (4)| 00:00:01 | Q1,03 | PCWP | | | | | | | | |
| 20 | 19 | 11 | PX SEND HASH | :TQ10001 | 2000K| 204M| 27 (4)| 00:00:01 | Q1,01 | P->P | HASH | +2 | 4 | @@ | 5-P001(4),5-P002(4),5-P003(4),5-P004(4),5-P006(4),... | 0123456789ABCDEF0123456789ABCDEF | @@@@@@@@@@@@@ | ON CPU(125) |
| 21 | 20 | 10 | PX BLOCK ITERATOR | | 2000K| 204M| 27 (4)| 00:00:01 | Q1,01 | PCWC | | | | | | | | |
|* 22 | 21 | 9 | TABLE ACCESS STORAGE FULL | T2 | 2000K| 204M| 27 (4)| 00:00:01 | Q1,01 | PCWP | | +4 | 1 | @ | 5-P005(1) | 0 | | ON CPU(1) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$2 / from$_subquery$_001@SEL$1
6 - SEL$2
10 - SEL$2 / T1@SEL$2
13 - SEL$3 / V1@SEL$2
14 - SEL$3
18 - SEL$3 / T2@SEL$3
22 - SEL$3 / T3@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
NO_ACCESS(@"SEL$2" "V1"@"SEL$2")
FULL(@"SEL$2" "T1"@"SEL$2")
LEADING(@"SEL$2" "V1"@"SEL$2" "T1"@"SEL$2")
USE_HASH(@"SEL$2" "T1"@"SEL$2")
PQ_DISTRIBUTE(@"SEL$2" "T1"@"SEL$2" HASH HASH)
SWAP_JOIN_INPUTS(@"SEL$2" "T1"@"SEL$2")
FULL(@"SEL$3" "T3"@"SEL$3")
FULL(@"SEL$3" "T2"@"SEL$3")
LEADING(@"SEL$3" "T3"@"SEL$3" "T2"@"SEL$3")
USE_HASH(@"SEL$3" "T2"@"SEL$3")
PQ_DISTRIBUTE(@"SEL$3" "T2"@"SEL$3" HASH HASH)
SWAP_JOIN_INPUTS(@"SEL$3" "T2"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("V1"."T2_ID2"="T1"."ID2"+2001)
10 - storage(:Z>=:Z AND :Z<=:Z)
14 - access("T3"."ID"="T2"."ID" AND REGEXP_REPLACE ("T3"."FILLER",'^([[:alnum:]]+)\s+$',' \1',1,1,'c',)
18 - storage(:Z>=:Z AND :Z<=:Z)
22 - storage(:Z>=:Z AND :Z<=:Z)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) MAX()[22], MAX()[22], MAX()[22], MAX()[100], MAX()[100], MAX()[4000]
2 - SYS_OP_MSR()[4280]
3 - (#keys=0) SYS_OP_MSR()[4280]
4 - (#keys=0) SYS_OP_MSR()[4280]
5 - "T1_ID"[NUMBER,22], "T1_FILLER"[VARCHAR2,4000], "T2_ID"[NUMBER,22], "T2_FILLER"[VARCHAR2,100],
"T3_ID"[NUMBER,22], "T3_FILLER"[VARCHAR2,100]
6 - (#keys=1) "T1"."ID"[NUMBER,22], "V1"."T2_ID"[NUMBER,22], "V1"."T2_FILLER"[VARCHAR2,100],
"T3_FILLER"[VARCHAR2,100], "V1"."T3_ID"[NUMBER,22]
7 - "T1"."ID2"+2001[22], "T1"."ID"[NUMBER,22], "T1"."ID2"[NUMBER,22]
8 - (#keys=1) "T1"."ID2"+2001[22], "T1"."ID"[NUMBER,22], "T1"."ID2"[NUMBER,22]
9 - "T1"."ID"[NUMBER,22], "T1"."ID2"[NUMBER,22]
10 - "T1"."ID"[NUMBER,22], "T1"."ID2"[NUMBER,22]
11 - "V1"."T2_ID2"[NUMBER,22], "V1"."T2_ID"[NUMBER,22], "V1"."T2_FILLER"[VARCHAR2,100], "T3_FILLER"[VARCHAR2,100],
"V1"."T3_ID"[NUMBER,22]
12 - (#keys=1) "V1"."T2_ID2"[NUMBER,22], "V1"."T2_ID"[NUMBER,22], "V1"."T2_FILLER"[VARCHAR2,100],
"T3_FILLER"[VARCHAR2,100], "V1"."T3_ID"[NUMBER,22]
13 - "V1"."T2_ID"[NUMBER,22], "V1"."T2_FILLER"[VARCHAR2,100], "V1"."T2_ID2"[NUMBER,22], "V1"."T3_ID"[NUMBER,22],
"T3_FILLER"[VARCHAR2,100]
14 - (#keys=2) "T2"."ID"[NUMBER,22], "T3"."ID"[NUMBER,22], "T2"."FILLER"[VARCHAR2,100], "T2"."ID2"[NUMBER,22],
"T3"."FILLER"[VARCHAR2,100]
15 - "T2"."ID"[NUMBER,22], REGEXP_REPLACE ("T2"."FILLER",'^([[:alnum:]]+)\s+$',' \1',1,1,'c',[4000], "T2"."FILLER"[VARCHAR2,100], "T2"."ID2"[NUMBER,22]
16 - (#keys=2) "T2"."ID"[NUMBER,22], REGEXP_REPLACE ("T2"."FILLER",'^([[:alnum:]]+)\s+$',' \1',1,1,'c',[4000], "T2"."FILLER"[VARCHAR2,100], "T2"."ID2"[NUMBER,22]
17 - "T2"."ID"[NUMBER,22], "T2"."ID2"[NUMBER,22], "T2"."FILLER"[VARCHAR2,100]
18 - "T2"."ID"[NUMBER,22], "T2"."ID2"[NUMBER,22], "T2"."FILLER"[VARCHAR2,100]
19 - "T3"."ID"[NUMBER,22], REGEXP_REPLACE ("T3"."FILLER",'^([[:alnum:]]+)\s+$',' \1',1,1,'c',[4000], "T3"."FILLER"[VARCHAR2,100]
20 - (#keys=2) "T3"."ID"[NUMBER,22], REGEXP_REPLACE ("T3"."FILLER",'^([[:alnum:]]+)\s+$',' \1',1,1,'c',[4000], "T3"."FILLER"[VARCHAR2,100]
21 - "T3"."ID"[NUMBER,22], "T3"."FILLER"[VARCHAR2,100]
22 - "T3"."ID"[NUMBER,22], "T3"."FILLER"[VARCHAR2,100]
This section is pretty much unchanged to the previous version and shows the execution plan (if available) along with the activity per execution plan line, as explained in the original post for version 1.0 of the script. Note that the script detects whether an execution plan is available or not and shows the activity per execution plan line in a different format if no execution plan could be found.
A note to RAC users: In versions prior to 11.2.0.2 the execution plan can only be pulled from the local instance - in worst case this execution plan is an incorrect one, since for the same SQL_ID and CHIlD_CURSOR different plans could be in the different Library Caches of the RAC instances.
From 11.2.0.2 on the correct execution plan will be pulled from the corresponding RAC instance by using an (undocumented) function that allows a "remote" instance execution of a query.
Experimental Stuff
There is a global switch _EXPERIMENTAL at the beginning of the configuration section. By default this is disabled because the stuff shown could be called "unreliable" and potentially "misleading" information. If you enable it by setting the configuration switch to an empty string, the I/O figures from the ASH data (only from 11.2+ on) will be shown at various places of the report. Note that this data is unreliable and usually falls short of the actual activity (I've never seen it reporting more than the actual activities). Since sometimes unreliable figures can be much better than nothing at all you can enable it in such cases where you want for example get an idea if the I/O was in the range of MBs or GBs - this is something you should be able to tell from the ASH data. Likewise the average and median wait times from ASH will be shown at different places of the report if experimental is turned on. It is important to understand what these wait times are: These are waits that were "in-flight", not completed when the sampling took place. Doing statistical analysis based on such sampled, in-flight wait times is sometimes called "Bad ASH math", but again, if you know what you are doing and keep telling yourself what you're looking at, there might be cases where this information could be useful, for example, if you see that hundreds or thousands of those "in-flight" waits were sampled with a typical wait time of 0.5 secs (for example a multiblock read) where you expect a typical wait time of 0.005 secs. This might be an indication that something was broken or went wrong and could be worth further investigation. This is what the affected sections look like with experimental turned on:Concurrent Activity Summary (not this execution)
Concurrent Activity Summary (not this execution)
-----------------------------------------------
INSTANCE_ID|ACTIVITY |ACTIVITY_CLASS |AVG_TIM_WAIT_MS|MED_TIM_WAIT_MS|SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
-----------|--------------------------------------------------|--------------------|---------------|---------------|------------|----------|--------------------
5|reliable message |Other | 978,8| 1001| 3| 30|@@@@@@
|ON CPU |ON CPU | | | 2| 20|@@@@
|SQL*Net break/reset to client |Application | 102| 102| 1| 10|@@
6|ASM file metadata operation |Other | 350,6| 350,6| 1| 10|@@
|enq: PV - syncstart |Other | 259,2| 259,2| 1| 10|@@
|control file parallel write |System I/O | 92,1| 92,1| 1| 10|@@
|ON CPU |ON CPU | | | 1| 10|@@
Note again that the wait times shown (average and median) are not true wait times since the wait was ongoing when the sample of the session was taken, which means that it wasn't completed yet, so the actual wait time for that wait will have been longer, and most of the waits won't be covered by the sampling anyway, so as already explained, you need to be very catious with that information.
Global I/O Summary based on ASH
Global I/O Summary based on ASH
-----------------------------------------------
|TOTAL |TOTAL |READ |WRITE |TOTAL |TOTAL |TOTAL |READ |WRITE |IO |AVG |MEDIAN|AVG |MEDIAN|
|READ |WRITE |IO |IO |READ |WRITE |IO |IO |IO |LAYER |READ |READ |WRITE |WRITE |CELL
DURATION |IO |IO |REQS |REQS |IO |IO |LAYER |BYTES |BYTES |BYTES |REQ |REQ |REQ |REQ |OFFL
SECS |REQS |REQS |PERSEC|PERSEC|BYTES |BYTES |BYTES |PERSEC|PERSEC|PERSEC|SIZE |SIZE |SIZE |SIZE |EFF
----------|------|------|------|------|------|------|------|------|------|------|------|------|------|------|-----
38| 27 | 49 | 1 | 1 | 6857K| 12M| 32M| 180K| 327K| 835K| 254K| 254K| 254K| 254K| -64%
Global I/O Summary per Instance based on ASH
-----------------------------------------------
| |TOTAL |TOTAL |READ |WRITE |TOTAL |TOTAL |TOTAL |READ |WRITE |IO |AVG |MEDIAN|AVG |MEDIAN|
| |READ |WRITE |IO |IO |READ |WRITE |IO |IO |IO |LAYER |READ |READ |WRITE |WRITE |CELL
|DURATION |IO |IO |REQS |REQS |IO |IO |LAYER |BYTES |BYTES |BYTES |REQ |REQ |REQ |REQ |OFFL
INSTANCE_ID|SECS |REQS |REQS |PERSEC|PERSEC|BYTES |BYTES |BYTES |PERSEC|PERSEC|PERSEC|SIZE |SIZE |SIZE |SIZE |EFF
-----------|----------|------|------|------|------|------|------|------|------|------|------|------|------|------|------|-----
5| 38| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 0%
6| 33| 27 | 49 | 1 | 1 | 6857K| 12M| 32M| 208K| 377K| 962K| 254K| 254K| 254K| 254K| -64%
This section, only available from 11.2 on, and only shown when the so called "experimental" mode is enabled (disabled by default, see the configuration section of the script).
From 11.2 on Oracle enriches the ASH data with accumulated statistics related to DB Time, CPU Time and I/O activity. This is a new approach since the data is not truly sampled, but accumulated from sample to sample, so in theory represents exact delta figures.
Note that this also means that the historic ASH data written to AWR (all samples every tenth second) requires these values to be aggregated for all samples in between that are not written to AWR in order to have meaningful information in these columns of the historic ASH data.
However, as it turns out, at least in 11.2, obviously not all I/O is covered by these statistics, for example I/O to the TEMP tablespace is not represented correctly and usually falls short of the actual I/O performed.
Furthermore there seem to be inconsistencies in the data that could indicate that it is not necessarily a reliable data source (the script to some extent attempts to detect such outliers and filter them out). Hence that information can be quite incorrect and therefore it is marked as "experimental". Nevertheless it can be much better than no I/O related information at all, therefore I decided to add it as a separate section.
Note that the I/O layer figures can be more than simply read plus write I/O, because these values include any software mirroring performed by Oracle via the ASM layer. So for example any write to ASM with ASM normal redundancy will be doubled in the I/O layer figures.
Please note furthermore that the Cell Offload Efficiency figure is only relevant to Exadata environments where the smart scan capabilities can reduce the actual amount of I/O. Of course you can easily end up with negative efficiency percentages if a lot of mirroring during writing takes place, or HCC compressed data gets uncompressed in the cells and sent to the compute nodes.
Parallel Slave activity overview based on ASH
Parallel Slave activity overview based on ASH
-----------------------------------------------
| | | | SAMPLE| SAMPLE| | | | | |READ |WRITE | | |IO |READ |WRITE |IO_LAY|AVG |MEDIAN|AVG |MEDIAN|TOP |
| | SAMPLE| | COUNT| COUNT| PERCENT|MAX |MAX |READ |WRITE |REQ |REQ |READ |WRITE |LAYER |BYTES |BYTES |BYTES |RE_REQ|RE_REQ|WR_REQ|WR_REQ|ACTIVE |TOP
INSTANCE_ID|PROCESS | COUNT|ACTIVITY_GRAPH | CPU| OTHER| CPU|PGA |TEMP |REQS |REQS |PERSEC|PERSEC|BYTES |BYTES |BYTES |PERSEC|PERSEC|PERSEC|SIZE |SIZE |SIZE |SIZE |PLAN LINES |ACTIVITIES
-----------|----------------------------------------------------------------|----------|--------------------|----------|----------|----------|------|------|------|------|------|------|------|------|------|------|------|------|------|------|------|------|----------------------------------------|----------------------------------------------------------------------
5|5-P000 | 3| | 3| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(3) |ON CPU(3)
|5-P001 | 4| | 4| 0| 100| 2289K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P002 | 4| | 4| 0| 100| 2140K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P003 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P004 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P005 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(3),22(1) |ON CPU(4)
|5-P006 | 4| | 4| 0| 100| 2009K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P007 | 36|@@@@ | 36| 0| 100| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |5(28),20(4),4(2),... |ON CPU(36)
|5-P008 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P009 | 4| | 4| 0| 100| 2075K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P010 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P011 | 4| | 4| 0| 100| 2009K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P012 | 4| | 4| 0| 100| 2009K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P013 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P014 | 4| | 4| 0| 100| 2009K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P015 | 4| | 4| 0| 100| 2009K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|5-P016 | 1| | 1| 0| 100| 6286K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P017 | 1| | 1| 0| 100| 6286K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P018 | 1| | 1| 0| 100| 6286K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P019 | 1| | 1| 0| 100| 6221K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P020 | 1| | 0| 1| 0| 7007K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |enq: XL - fault extent map(1)
|5-P021 | 2| | 2| 0| 100| 9170K| 3146K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(2) |ON CPU(2)
|5-P024 | 1| | 1| 0| 100| 6221K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P025 | 1| | 1| 0| 100| 6680K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P026 | 1| | 0| 1| 0| 6352K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |os thread startup(1)
|5-P027 | 1| | 1| 0| 100| 6221K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P028 | 1| | 1| 0| 100| 6221K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P029 | 1| | 1| 0| 100| 6286K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P030 | 1| | 1| 0| 100| 6221K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|5-P031 | 2| | 2| 0| 100| 9956K| 3146K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(2) |ON CPU(2)
6|6-P000 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P001 | 4| | 4| 0| 100| 1961K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P002 | 4| | 4| 0| 100| 1895K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P003 | 3| | 3| 0| 100| 1961K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(3) |ON CPU(3)
|6-P004 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P005 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P006 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P007 | 4| | 4| 0| 100| 1961K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P008 | 4| | 4| 0| 100| 1961K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P009 | 4| | 4| 0| 100| 1961K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P010 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P011 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P012 | 4| | 4| 0| 100| 1895K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P013 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P014 | 4| | 4| 0| 100| 2026K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P015 | 4| | 4| 0| 100| 1961K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |20(4) |ON CPU(4)
|6-P016 | 2| | 1| 1| 50| 8056K| 7340K| 0 | 18 | 0 | 9 | 0 | 4571K| 9142K| 0 | 2286K| 4571K| | | 254K| 254K|14(2) |ON CPU(1),os thread startup(1)
|6-P017 | 1| | 0| 1| 0| 5107K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |enq: XL - fault extent map(1)
|6-P019 | 1| | 1| 0| 100| 5107K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|6-P020 | 1| | 1| 0| 100| 5107K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|6-P021 | 1| | 1| 0| 100| 5172K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|6-P022 | 1| | 0| 1| 0| 5107K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |enq: XL - fault extent map(1)
|6-P025 | 1| | 1| 0| 100| 5107K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|6-P026 | 1| | 1| 0| 100| 5172K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|6-P027 | 1| | 1| 0| 100| 5107K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|6-P028 | 1| | 0| 1| 0| 5172K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |enq: XL - fault extent map(1)
|6-P029 | 1| | 0| 1| 0| 5172K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |enq: XL - fault extent map(1)
|6-P030 | 1| | 1| 0| 100| 5172K| 1049K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |14(1) |ON CPU(1)
|6-P031 | 2| | 2| 0| 100| 7007K| 8389K| 27 | 31 | 14 | 16 | 6857K| 7873K| 23M| 3428K| 3936K| 11M| 254K| 254K| 254K| 254K|14(2) |ON CPU(2)
|6-sqlplus.exe | 1| | 0| 1| 0| 1699K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | |2(1) |PX Nsq: PQ load info query(1)
If on 11.2 and the already explained "experimental" mode is enabled this will also include the (possibly unreliable) I/O figures per process.
Activity Class Summary
Activity Class Summary
-----------------------------------------------
ACTIVITY_CLASS |AVG_TIM_WAIT_MS|MED_TIM_WAIT_MS|SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
--------------------|---------------|---------------|------------|----------|--------------------
ON CPU | | | 182| 96|@@@@@@@@@@@@@@@@@@@
Other | 373,6| 392,7| 6| 3|@
Concurrency | 358,9| 358,9| 2| 1|
Activity Class Summary per Instance
-----------------------------------------------
INSTANCE_ID|ACTIVITY_CLASS |AVG_TIM_WAIT_MS|MED_TIM_WAIT_MS|SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
-----------|--------------------|---------------|---------------|------------|----------|--------------------
5|ON CPU | | | 109| 57|@@@@@@@@@@@
|Other | 461,7| 461,7| 1| 1|
|Concurrency | 382| 382| 1| 1|
6|ON CPU | | | 73| 38|@@@@@@@@
|Other | 355,9| 347,7| 5| 3|@
|Concurrency | 335,8| 335,8| 1| 1|
Note that the same applies as above - the average and median wait times shown are not true wait times and therefore shouldn't be taken as such - these are sampled, "in-flight" waits not completed yet.
Activity Summary
Activity Summary
-----------------------------------------------
ACTIVITY |ACTIVITY_CLASS |AVG_TIM_WAIT_MS|MED_TIM_WAIT_MS|SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
--------------------------------------------------|--------------------|---------------|---------------|------------|----------|--------------------
ON CPU |ON CPU | | | 182| 96|@@@@@@@@@@@@@@@@@@@
enq: XL - fault extent map |Other | 408| 437,7| 5| 3|@
os thread startup |Concurrency | 358,9| 358,9| 2| 1|
PX Nsq: PQ load info query |Other | 201,3| 201,3| 1| 1|
Activity Summary per Instance
-----------------------------------------------
INSTANCE_ID|ACTIVITY |ACTIVITY_CLASS |AVG_TIM_WAIT_MS|MED_TIM_WAIT_MS|SAMPLE_COUNT|PERCENTAGE|ACTIVITY_GRAPH
-----------|--------------------------------------------------|--------------------|---------------|---------------|------------|----------|--------------------
5|ON CPU |ON CPU | | | 109| 57|@@@@@@@@@@@
|os thread startup |Concurrency | 382| 382| 1| 1|
|enq: XL - fault extent map |Other | 461,7| 461,7| 1| 1|
6|ON CPU |ON CPU | | | 73| 38|@@@@@@@@
|enq: XL - fault extent map |Other | 394,6| 392,7| 4| 2|
|os thread startup |Concurrency | 335,8| 335,8| 1| 1|
|PX Nsq: PQ load info query |Other | 201,3| 201,3| 1| 1|
Same as above, at activity granularity.
Activity Timeline based on ASH
Activity Timeline based on ASH
-----------------------------------------------
| | | | | |READ |WRITE | | |IO |READ |WRITE |IO_LAY|AVG |MEDIAN|AVG |MEDIAN| | | AVERAGE|TOP | | |AVERAGE
| | | |READ |WRITE |REQ |REQ |READ |WRITE |LAYER |BYTES |BYTES |BYTES |RE_REQ|RE_REQ|WR_REQ|WR_REQ| | | ACTIVE|ACTIVE |TOP |TOP |ACTIVE SESSIONS
INSTANCE_ID|DURATION_SECS|PGA |TEMP |REQS |REQS |PERSEC|PERSEC|BYTES |BYTES |BYTES |PERSEC|PERSEC|PERSEC|SIZE |SIZE |SIZE |SIZE | CPU| OTHER| SESSIONS|PLAN LINES |ACTIVITIES |PROCESSES |GRAPH
-----------|-------------|------|------|------|------|------|------|------|------|------|------|------|------|------|------|------|------|----------|----------|----------|----------------------------------------|----------------------------------------------------------------------|------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5| 1| | | | | | | | | | | | | | | | | 0| 0| 0| | | |
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 2| | | | | | | | | | | | | | | | | 0| 0| 0| | | |
6 | 1699K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 0| 1| 1|2(1) |PX Nsq: PQ load info query(1) |6-sqlplus.exe(1) |*
5| 3| 122M| 15M| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 28| 2| 30|20(16),14(14) |ON CPU(28),enq: XL - fault extent map(1),os thread startup(1) |5-P000(1),5-P001(1),5-P002(1),5-P003(1),5-P004(1),... |@@@@@@@@@@@@@@@@@@@@@@@@@@@@**
6 | 99M| 14M| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 24| 5| 29|20(16),14(13) |ON CPU(24),enq: XL - fault extent map(4),os thread startup(1) |6-P000(1),6-P001(1),6-P002(1),6-P003(1),6-P004(1),... |@@@@@@@@@@@@@@@@@@@@@@@@*****
5| 4| 50M| 6291K| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 17| 0| 17|20(15),14(2) |ON CPU(17) |5-P001(1),5-P002(1),5-P003(1),5-P004(1),5-P005(1),... |@@@@@@@@@@@@@@@@@
6 | 32M| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 16| 0| 16|20(16) |ON CPU(16) |6-P000(1),6-P001(1),6-P002(1),6-P003(1),6-P004(1),... |@@@@@@@@@@@@@@@@
5| 5| 33M| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 16| 0| 16|20(15),22(1) |ON CPU(16) |5-P000(1),5-P001(1),5-P002(1),5-P003(1),5-P004(1),... |@@@@@@@@@@@@@@@@
6 | 32M| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 16| 0| 16|20(16) |ON CPU(16) |6-P000(1),6-P001(1),6-P002(1),6-P003(1),6-P004(1),... |@@@@@@@@@@@@@@@@
5| 6| 33M| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 16| 0| 16|20(16) |ON CPU(16) |5-P000(1),5-P001(1),5-P002(1),5-P003(1),5-P004(1),... |@@@@@@@@@@@@@@@@
6 | 38M| 7340K| 0 | 18 | 0 | 18 | 0 | 4571K| 9142K| 0 | 4571K| 9142K| | | 254K| 254K| 16| 0| 16|20(15),14(1) |ON CPU(16) |6-P000(1),6-P001(1),6-P002(1),6-P004(1),6-P005(1),... |@@@@@@@@@@@@@@@@
5| 7| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 8| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 9| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|6(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 10| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 11| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 12| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 13| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 14| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 15| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 16| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|11(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 17| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 18| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 19| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 20| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 21| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|4(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 22| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 23| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 24| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 25| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 26| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 27| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 28| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|4(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 29| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 30| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 31| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 32| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 33| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | 7007K| 8389K| 27 | 31 | 27 | 31 | 6857K| 7873K| 23M| 6857K| 7873K| 23M| 254K| 254K| 254K| 254K| 1| 0| 1|14(1) |ON CPU(1) |6-P031(1) |@
5| 34| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 35| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 36| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 37| 4451K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
5| 38| 4386K| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 1| 0| 1|5(1) |ON CPU(1) |5-P007(1) |@
6 | | | | | | | | | | | | | | | | | 0| 0| 0| | | |
From 11.2 on, and if the "experimental" mode is enabled (as explained above already), the I/O figures per bucket will also be shown.
Although not part of the experimental stuff, please note that in particular the TEMP figures have turned out not to be very reliable, furthermore short peaks in PGA / TEMP usage are not well represented in the ASH data.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.