Try querying V$SESSION for blocking_session, BLOCKING_SESSION_STATUS, etc...
A reader, May 02, 2011 - 9:06 pm UTC
A reader, May 03, 2011 - 8:14 am UTC
Hi Tom,
I have a similar requirement, my developers have coded in such a way that thire sql code delete's and updates some of the primary keys in the tables and at the same time inserts into those tables, we had a situation where the DB completely hangs and upon looking there were no foreign keys index on the child table, upon creating those indexes prevented such hangs on the DB and now my application team wants me to give them some code that they will schedule so that it will execute the code for every few minutes and alert them is they see any such locking issue.
May 04, 2011 - 12:35 pm UTC
do you have access to AWR/ASH information?
Grid
Stuart, May 04, 2011 - 5:27 am UTC
This is definitely a strange requirement.
You can set up alerting for blocking sessions in Grid Control, we implement that although I'm not sure it will poll enough to satisfy your requirements.
It'll email out something along the lines of:
EM Alert: Critical:[instance_name] - Session 155 is blocking 19 other sessions
A reader, May 05, 2011 - 5:00 pm UTC
Yes Sir, below is some part of the ASH Report.
DB Name DB Id Instance Inst num Release RAC Host
XXXXXX 1604357259 xxxxxx 11.2.0.1.0 NO sun
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
16 3,059M (100%) 704M (23.0%) 1,051M (34.4%) 29.0M (0.9%)
Sample Time Data Source
Analysis Begin Time: 29-Nov-10 12:30:19 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 6355
Analysis End Time: 29-Nov-10 13:00:19 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 6357
Elapsed Time: 30.0 (mins)
Sample Count: 23,703
Average Active Sessions: 131.68
Avg. Active Session per CPU: 8.23
Report Target: None specified
ASH Report
Top Events
Load Profile
Top SQL
Top PL/SQL
Top Java
Top Call Types
Top Sessions
Top Objects/Files/Latches
Activity Over Time
Back to Top
--------------------------------------------------------------------------------
Top Events
Top User Events
Top Background Events
Top Event P1/P2/P3 Values
Back to Top
Top User Events
Event Event Class % Event Avg Active Sessions
enq: TM - contention Application 56.89 74.92
latch free Other 13.66 17.99
CPU + Wait for CPU CPU 8.87 11.68
cursor: pin S Concurrency 5.46 7.19
library cache: mutex X Concurrency 5.11 6.73
Back to Top Events
Back to Top
Top Background Events
No data exists for this section of the report.
Back to Top Events
Back to Top
Top Event P1/P2/P3 Values
Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3
enq: TM - contention 56.89 "1414332419","78578","0" 25.67 name|mode object # table/partition
"1414332419","78557","0" 21.00
"1414332419","78558","0" 3.86
latch free 13.66 "15032439656","27","0" 12.46 address number tries
"15032522256","236","0" 1.17
cursor: pin S 5.46 "675908569","4629974745092","17179869184" 0.64 idn value where
library cache: mutex X 5.11 "4291192444","1202590842880","106" 0.05 idn value where
enq: TX - row lock contention 4.86 "1415053316","1966080","20108" 0.42 name|mode usn<<16 | slot sequence
May 06, 2011 - 10:18 am UTC
it is very pretty. Thank you.
????
A reader, May 09, 2011 - 8:59 am UTC
Sorry about that sir it was a mistake. I am including below some part of the AWR for your comments.
WORKLOAD REPOSITORY report for
******************************
+-------+----------+--------+--------+---------------+----------+---+
|DB Name|DB Id |Instance|Inst num|Startup Time |Release |RAC|
+-------+----------+--------+--------+---------------+----------+---+
|db101 |1604357259 |db101 | 1|22-Oct-10 16:10|11.2.0.1.0|NO |
+-------+----------+--------+--------+---------------+----------+---+
+-----------+-----------------------+----+-----+-------+-----------+
|Host Name |Platform |CPUs|Cores|Sockets|Memory (GB)|
+-----------+-----------------------+----+-----+-------+-----------+
|sunoradb |Solaris[tm] OE (64-bit)| 16| 8| 4| 32.00|
+-----------+-----------------------+----+-----+-------+-----------+
+-----------+-------+------------------+--------+---------------+
| |Snap Id|Snap Time |Sessions|Cursors/Session|
+-----------+-------+------------------+--------+---------------+
|Begin Snap:| 6363|29-Nov-10 14:30:12| 1033| 1.1|
+-----------+-------+------------------+--------+---------------+
|End Snap: | 6367|29-Nov-10 15:30:16| 893| 1.3|
+-----------+-------+------------------+--------+---------------+
|Elapsed: | | 60.05 (mins) | | |
+-----------+-------+------------------+--------+---------------+
|DB Time: | | 6,876.57 (mins) | | |
+-----------+-------+------------------+--------+---------------+
Report Summary
--------------
Cache Sizes
+-----------------+------+------+---------------+--------+
| |Begin |End | | |
+-----------------+------+------+---------------+--------+
|Buffer Cache: | 704M| 688M|Std Block Size:| 8K|
+-----------------+------+------+---------------+--------+
|Shared Pool Size:|1,184M|1,200M|Log Buffer: | 17,544K|
+-----------------+------+------+---------------+--------+
Load Profile
+-----------------+----------+---------------+--------+--------+
| |Per Second|Per Transaction|Per Exec|Per Call|
+-----------------+----------+---------------+--------+--------+
|DB Time(s): | 114.5| 3.8| 0.04| 0.01|
+-----------------+----------+---------------+--------+--------+
|DB CPU(s): | 4.1| 0.1| 0.00| 0.00|
+-----------------+----------+---------------+--------+--------+
|Redo size: | 171,569.5| 5,676.1| | |
+-----------------+----------+---------------+--------+--------+
|Logical reads: | 59,967.4| 1,983.9| | |
+-----------------+----------+---------------+--------+--------+
|Block changes: | 1,218.0| 40.3| | |
+-----------------+----------+---------------+--------+--------+
|Physical reads: | 26,599.9| 880.0| | |
+-----------------+----------+---------------+--------+--------+
|Physical writes: | 79.4| 2.6| | |
+-----------------+----------+---------------+--------+--------+
|User calls: | 9,104.3| 301.2| | |
+-----------------+----------+---------------+--------+--------+
|Parses: | 2,721.1| 90.0| | |
+-----------------+----------+---------------+--------+--------+
|Hard parses: | 13.1| 0.4| | |
+-----------------+----------+---------------+--------+--------+
|W/A MB processed:| 17.0| 0.6| | |
+-----------------+----------+---------------+--------+--------+
|Logons: | 2.3| 0.1| | |
+-----------------+----------+---------------+--------+--------+
|Executes: | 2,747.9| 90.9| | |
+-----------------+----------+---------------+--------+--------+
|Rollbacks: | 0.2| 0.0| | |
+-----------------+----------+---------------+--------+--------+
|Transactions: | 30.2| | | |
+-----------------+----------+---------------+--------+--------+
Instance Efficiency Percentages (Target 100%)
+----------------------------+------+-----------------+-------+
|Buffer Nowait %: |100.00|Redo NoWait %: | 100.00|
+----------------------------+------+-----------------+-------+
|Buffer Hit %: | 98.79|In-memory Sort %:| 100.00|
+----------------------------+------+-----------------+-------+
|Library Hit %: | 98.68|Soft Parse %: | 99.52|
+----------------------------+------+-----------------+-------+
|Execute to Parse %: | 0.98|Latch Hit %: | 99.16|
+----------------------------+------+-----------------+-------+
|Parse CPU to Parse Elapsd %:| 91.25|% Non-Parse CPU: | 96.10|
+----------------------------+------+-----------------+-------+
Shared Pool Statistics
+------------------------------------+-----+-----+
| |Begin|End |
+------------------------------------+-----+-----+
|Memory Usage %: |69.70|70.42|
+------------------------------------+-----+-----+
|% SQL with executions>1: |74.01|73.26|
+------------------------------------+-----+-----+
|% Memory for SQL w/exec>1: |70.54|72.80|
+------------------------------------+-----+-----+
Top 5 Timed Foreground Events
+--------------------+---------+-------+-------------+---------+-----------+
|Event |Waits |Time(s)|Avg wait (ms)|% DB time|Wait Class |
+--------------------+---------+-------+-------------+---------+-----------+
|enq: TM - contention| 8,642|376,245| 43537| 91.19|Application|
+--------------------+---------+-------+-------------+---------+-----------+
|enq: TX - row lock | 664| 16,027| 24137| 3.88|Application|
|contention | | | | | |
+--------------------+---------+-------+-------------+---------+-----------+
|DB CPU | | 14,647| | 3.55| |
+--------------------+---------+-------+-------------+---------+-----------+
|db file sequential |1,137,409| 3,128| 3| 0.76|User I/O |
|read | | | | | |
+--------------------+---------+-------+-------------+---------+-----------+
|SQL*Net break/reset | 9,378| 2,101| 224| 0.51|Application|
|to client | | | | | |
+--------------------+---------+-------+-------------+---------+-----------+
Host CPU (CPUs: 16 Cores: 8 Sockets: 4)
+------------------+----------------+-----+-------+----+------+
|Load Average Begin|Load Average End|%User|%System|%WIO|%Idle |
+------------------+----------------+-----+-------+----+------+
| 10.39| 6.14| 26.7| 13.7| 0.0| 59.6|
+------------------+----------------+-----+-------+----+------+
Instance CPU
+----------+---------+-------------------------------------------+
|%Total CPU|%Busy CPU|%DB time waiting for CPU (Resource Manager)|
+----------+---------+-------------------------------------------+
| 26.0| 64.3| 0.0|
+----------+---------+-------------------------------------------+
Memory Statistics
+------------------------------+--------+--------+
| |Begin |End |
+------------------------------+--------+--------+
|Host Mem (MB): |32,768.0|32,768.0|
+------------------------------+--------+--------+
|SGA use (MB): | 2,048.0| 2,048.0|
+------------------------------+--------+--------+
|PGA use (MB): | 1,383.6| 1,265.4|
+------------------------------+--------+--------+
|% Host Mem used for SGA+PGA: | 10.47| 10.11|
+------------------------------+--------+--------+
Main Report
===========
· Report Summary
· Wait Events Statistics
· SQL Statistics
· Instance Activity Statistics
· IO Stats
· Buffer Pool Statistics
· Advisory Statistics
· Wait Statistics
· Undo Statistics
· Latch Statistics
· Segment Statistics
· Dictionary Cache Statistics
· Library Cache Statistics
· Memory Statistics
· Streams Statistics
· Resource Limit Statistics
· Shared Server Statistics
· init.ora Parameters
Back to Top
Wait Events Statistics
======================
· Time Model Statistics
· Operating System Statistics
· Operating System Statistics - Detail
· Foreground Wait Class
· Foreground Wait Events
· Background Wait Events
· Wait Event Histogram
· Wait Event Histogram Detail (64 msec to 2 sec)
· Wait Event Histogram Detail (4 sec to 2 min)
· Wait Event Histogram Detail (4 min to 1 hr)
· Service Statistics
· Service Wait Class Stats
Back to Top
Time Model Statistics
---------------------
· Total time in database user-calls (DB Time): 412593.9s
· Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
· Ordered by % or DB time desc, Statistic name
+------------------------------------------+----------+------------+
|Statistic Name |Time (s) |% of DB Time|
+------------------------------------------+----------+------------+
|sql execute elapsed time |402,304.03| 97.51|
+------------------------------------------+----------+------------+
|DB CPU | 14,647.38| 3.55|
+------------------------------------------+----------+------------+
|parse time elapsed | 765.63| 0.19|
+------------------------------------------+----------+------------+
|hard parse elapsed time | 211.35| 0.05|
+------------------------------------------+----------+------------+
|connection management call elapsed time | 45.32| 0.01|
+------------------------------------------+----------+------------+
|hard parse (sharing criteria) elapsed time| 40.00| 0.01|
+------------------------------------------+----------+------------+
|hard parse (bind mismatch) elapsed time | 34.00| 0.01|
+------------------------------------------+----------+------------+
|PL/SQL execution elapsed time | 25.01| 0.01|
+------------------------------------------+----------+------------+
|RMAN cpu time (backup/restore) | 6.93| 0.00|
+------------------------------------------+----------+------------+
|PL/SQL compilation elapsed time | 3.56| 0.00|
+------------------------------------------+----------+------------+
|inbound PL/SQL rpc elapsed time | 0.89| 0.00|
+------------------------------------------+----------+------------+
|failed parse elapsed time | 0.41| 0.00|
+------------------------------------------+----------+------------+
|repeated bind elapsed time | 0.31| 0.00|
+------------------------------------------+----------+------------+
|sequence load elapsed time | 0.04| 0.00|
+------------------------------------------+----------+------------+
|DB time |412,593.91| |
+------------------------------------------+----------+------------+
|background elapsed time | 489.98| |
+------------------------------------------+----------+------------+
|background cpu time | 322.32| |
+------------------------------------------+----------+------------+
Back to Wait Events Statistics
Back to Top
Operating System Statistics
---------------------------
· *TIME statistic values are diffed. All others display actual values. End
Value is displayed if different
· ordered by statistic type (CPU Use, Virtual Memory, Hardware Config),
Name
+------------------------+--------------+---------+
|Statistic |Value |End Value|
+------------------------+--------------+---------+
|AVG_BUSY_TIME | 145,471| |
+------------------------+--------------+---------+
|AVG_IDLE_TIME | 214,633| |
+------------------------+--------------+---------+
|AVG_SYS_TIME | 49,175| |
+------------------------+--------------+---------+
|AVG_USER_TIME | 96,185| |
+------------------------+--------------+---------+
|BUSY_TIME | 2,329,433| |
+------------------------+--------------+---------+
|IDLE_TIME | 3,436,117| |
+------------------------+--------------+---------+
|SYS_TIME | 788,618| |
+------------------------+--------------+---------+
|USER_TIME | 1,540,815| |
+------------------------+--------------+---------+
|LOAD | 10| 6|
+------------------------+--------------+---------+
|OS_CPU_WAIT_TIME | 328,200| |
+------------------------+--------------+---------+
|RSRC_MGR_CPU_WAIT_TIME | 0| |
+------------------------+--------------+---------+
|PHYSICAL_MEMORY_BYTES |34,359,738,368| |
+------------------------+--------------+---------+
|NUM_CPUS | 16| |
+------------------------+--------------+---------+
|NUM_CPU_CORES | 8| |
+------------------------+--------------+---------+
|NUM_CPU_SOCKETS | 4| |
+------------------------+--------------+---------+
|TCP_RECEIVE_SIZE_DEFAULT| 49,152| |
+------------------------+--------------+---------+
|TCP_RECEIVE_SIZE_MAX | 1,048,576| |
+------------------------+--------------+---------+
|TCP_SEND_SIZE_DEFAULT | 49,152| |
+------------------------+--------------+---------+
|TCP_SEND_SIZE_MAX | 1,048,576| |
+------------------------+--------------+---------+
Back to Wait Events Statistics
Back to Top
Operating System Statistics - Detail
------------------------------------
+---------------+-----+-----+-----+-----+-----+-------+
|Snap Time |Load |%busy|%user|%sys |%idle|%iowait|
+---------------+-----+-----+-----+-----+-----+-------+
|29-Nov 14:30:12|10.39| | | | | |
+---------------+-----+-----+-----+-----+-----+-------+
|29-Nov 14:45:13| 7.01|39.89|25.91|13.98|60.11| 0.00|
+---------------+-----+-----+-----+-----+-----+-------+
|29-Nov 15:00:14| 6.04|40.33|26.90|13.43|59.67| 0.00|
+---------------+-----+-----+-----+-----+-----+-------+
|29-Nov 15:15:15| 8.81|43.23|28.55|14.68|56.77| 0.00|
+---------------+-----+-----+-----+-----+-----+-------+
|29-Nov 15:30:16| 6.14|38.15|25.54|12.62|61.85| 0.00|
+---------------+-----+-----+-----+-----+-----+-------+
Back to Wait Events Statistics
Back to Top
Foreground Wait Class
---------------------
· s - second, ms - millisecond - 1000th of a second
· ordered by wait time desc, waits desc
· %Timeouts: value of 0 indicates value was < .5%. Value of null is truly
0
· Captured Time accounts for 100.3% of Total DB time 412,593.91 (s)
· Total FG Wait Time: 399,328.20 (s) DB CPU time: 14,647.38 (s)
+--------------+----------+-----------+--------------+------------+--------+
|Wait Class |Waits |%Time -outs|Total Wait |Avg wait |%DB time|
| | | |Time (s) |(ms) | |
+--------------+----------+-----------+--------------+------------+--------+
|Application | 19,402| 21| 394,381| 20327| 95.59|
+--------------+----------+-----------+--------------+------------+--------+
|DB CPU | | | 14,647| | 3.55|
+--------------+----------+-----------+--------------+------------+--------+
|User I/O | 2,025,062| 0| 3,616| 2| 0.88|
+--------------+----------+-----------+--------------+------------+--------+
|Commit | 111,832| 0| 595| 5| 0.14|
+--------------+----------+-----------+--------------+------------+--------+
|Concurrency | 22,449| 1| 552| 25| 0.13|
+--------------+----------+-----------+--------------+------------+--------+
|Other | 208,845| 19| 141| 1| 0.03|
+--------------+----------+-----------+--------------+------------+--------+
|Network |23,389,536| 0| 41| 0| 0.01|
+--------------+----------+-----------+--------------+------------+--------+
|Configuration | 26| 8| 2| 81| 0.00|
+--------------+----------+-----------+--------------+------------+--------+
|System I/O | 4,580| 0| 2| 0| 0.00|
+--------------+----------+-----------+--------------+------------+--------+
|Administrative| 1| 0| 0| 129| 0.00|
May 09, 2011 - 11:22 am UTC
Ok, what would you like me to say about this?
I mean, I could tell you
a) great, everything looks perfect
b) wow, that's really bad
c) meh, that is acceptable
I could come up with a story that would support any of the three above conclusions - which one are you looking for?
These are just numbers - I suggest if you want automated "this is what to look at" you run your ADDM report.
Otherwise, you need to have a specific problem you are looking to try to solve.
A reader, May 09, 2011 - 9:13 am UTC
And Below is the ash report during the DB hang.
SH Report For db101/db101
****************************
+-------+----------+--------+--------+----------+---+-----------+
|DB Name|DB Id |Instance|Inst num|Release |RAC|Host |
+-------+----------+--------+--------+----------+---+-----------+
|db101 |1604357259 |db101 | 1|11.2.0.1.0 |NO |sunoradb|
+-------+----------+--------+--------+----------+---+-----------+
+----+-------------+------------+--------------+---------------+
|CPUs|SGA Size |Buffer Cache|Shared Pool |ASH Buffer Size|
+----+-------------+------------+--------------+---------------+
| 16|3,059M (100%)|704M (23.0%)|1,051M (34.4%)| 29.0M (0.9%)|
+----+-------------+------------+--------------+---------------+
+--------------------------+------------------+----------------------------+
| |Sample Time |Data Source |
+--------------------------+------------------+----------------------------+
|Analysis Begin Time: |29-Nov-10 12:30:19|DBA_HIST_ACTIVE_SESS_HISTORY|
| | | in AWR snapshot 6355 |
+--------------------------+------------------+----------------------------+
|Analysis End Time: |29-Nov-10 13:00:19|DBA_HIST_ACTIVE_SESS_HISTORY|
| | | in AWR snapshot 6357 |
+--------------------------+------------------+----------------------------+
|Elapsed Time: | 30.0 (mins)| |
+--------------------------+------------------+----------------------------+
|Sample Count: | 23,703| |
+--------------------------+------------------+----------------------------+
|Average Active Sessions: | 131.68| |
+--------------------------+------------------+----------------------------+
|Avg. Active Session per | 8.23| |
|CPU: | | |
+--------------------------+------------------+----------------------------+
|Report Target: | None specified| |
+--------------------------+------------------+----------------------------+
ASH Report
==========
- Top Events
- Load Profile
- Top SQL
- Top PL/SQL
- Top Java
- Top Call Types
- Top Sessions
- Top Objects/Files/Latches
- Activity Over Time
Back to Top
----------------------------------------------------------------------------
Top Events
==========
- Top User Events
- Top Background Events
- Top Event P1/P2/P3 Values
Back to Top
Top User Events
---------------
+----------------------+-----------+-------+-------------------+
|Event |Event Class|% Event|Avg Active Sessions|
+----------------------+-----------+-------+-------------------+
|enq: TM - contention |Application| 56.89| 74.92|
+----------------------+-----------+-------+-------------------+
|latch free |Other | 13.66| 17.99|
+----------------------+-----------+-------+-------------------+
|CPU + Wait for CPU |CPU | 8.87| 11.68|
+----------------------+-----------+-------+-------------------+
|cursor: pin S |Concurrency| 5.46| 7.19|
+----------------------+-----------+-------+-------------------+
|library cache: mutex X|Concurrency| 5.11| 6.73|
+----------------------+-----------+-------+-------------------+
Back to Top Events
Back to Top
Top Background Events
---------------------
No data exists for this section of the report.
Back to Top Events
Back to Top
Top Event P1/P2/P3 Values
-------------------------
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
|Event |% |P1 Value, P2 Value, P3 Value |% |Parameter|Parameter|Parameter 3 |
| |Event| |Activity|1 |2 | |
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
|enq: TM - |56.89| "1414332419","78578","0"| 25.67|name|mode|object # |table/partition|
|contention| | | | | | |
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
| | | "1414332419","78557","0"| 21.00| | | |
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
| | | "1414332419","78558","0"| 3.86| | | |
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
|latch free|13.66| "15032439656","27","0"| 12.46|address |number |tries |
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
| | | "15032522256","236","0"| 1.17| | | |
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
|cursor: | 5.46|"675908569","4629974745092","17179869184"| 0.64|idn |value |where |
|pin S | | | | | | |
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
|library | 5.11| "4291192444","1202590842880","106"| 0.05|idn |value |where |
|cache: | | | | | | |
|mutex X | | | | | | |
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
|enq: TX - | 4.86| "1415053316","1966080","20108"| 0.42|name|mode|usn<<16 ||sequence |
|row lock | | | | |slot | |
|contention| | | | | | |
+----------+-----+-----------------------------------------+--------+---------+---------+---------------+
Back to Top Events
Back to Top
Load Profile
============
- Top Service/Module
- Top Client IDs
- Top SQL Command Types
- Top Phases of Execution
Back to Top
Top Service/Module
------------------
+---------+----------------+----------+-------+--------+
|Service |Module |% Activity|Action |% Action|
+---------+----------------+----------+-------+--------+
|SYS$USERS|JDBC Thin Client| 99.22|UNNAMED| 99.22|
+---------+----------------+----------+-------+--------+
Back to Load Profile
Back to Top
Top Client IDs
--------------
No data exists for this section of the report.
Back to Load Profile
Back to Top
Top SQL Command Types
---------------------
- 'Distinct SQLIDs' is the count of the distinct number of SQLIDs with the given SQL Command Type found over all the ASH samples in the analysis period
+----------------+---------------+----------+-------------------+
|SQL Command Type|Distinct SQLIDs|% Activity|Avg Active Sessions|
+----------------+---------------+----------+-------------------+
|INSERT | 52| 50.46| 66.45|
+----------------+---------------+----------+-------------------+
|SELECT | 201| 13.31| 17.52|
+----------------+---------------+----------+-------------------+
|UPDATE | 37| 7.74| 10.19|
+----------------+---------------+----------+-------------------+
|DELETE | 18| 4.99| 6.57|
+----------------+---------------+----------+-------------------+
Back to Load Profile
Back to Top
Top Phases of Execution
-----------------------
+------------------+----------+-------------------+
|Phase of Execution|% Activity|Avg Active Sessions|
+------------------+----------+-------------------+
|SQL Execution | 70.63| 93.01|
+------------------+----------+-------------------+
|Parse | 7.32| 9.64|
+------------------+----------+-------------------+
|Cursor Close | 2.32| 3.06|
+------------------+----------+-------------------+
Back to Load Profile
Back to Top
May 09, 2011 - 11:23 am UTC
what hang and who are you and what are you talking about?
A reader, May 09, 2011 - 12:19 pm UTC
Hi Tom,
Sorry for any confusion. This is in regards to your request to post the AWR/ASH reports for my request, that is the second post in this thread.
Thanks
May 09, 2011 - 2:19 pm UTC
I didn't ask you to post anything. I asked if you had access to it.
If you have access to ASH and AWR, you have a history of what was going on in your database. You can then query that history anytime you want to see if people were waiting on locks.
All I wanted to know was "do you have access to AWR/ASH information?" I never asked for a report- the report won't do me any good, you are looking for a specific thing - waits on locks. Now, knowing that you have licensed ASH/AWR, I can say "just query it up, look for waits on locks, it would answer your question "now my
application team wants me to give them some code that they will schedule so that it will execute
the code for every few minutes and alert them is they see any such locking issue", you can also set up events in enterprise manager - for example see the post following immediately after your initial one.
Misleading data in DBA_HIST_ACTIVE_SESS_HISTORY
Tony, July 08, 2011 - 6:41 am UTC
Tom,
v$active_session_history has snapshot of every second whereas DBA_HIST_ACTIVE_SESS_HISTORY has one in 10 of v$active_session_history. If the history doesn't have all the rows, what is the use of having history then? The data in the history table is misleading as its not contineous.
Kindly clarify
Thanks
Tony
July 08, 2011 - 3:14 pm UTC
nothing is continuous. NOTHING, not even sql trace. It is a snapshot, it shows you were you spent MOST of your time. It shows you what you did MOST OFTEN.
If you spend most of your time executing some query, it will show up in the history. If you spend most of your time waiting for IO, it will show up in history. If you spend most of your time waiting for a lock, it will show up in history. If you spend most of your time doing totally different things - that fact too will show up in history (there will be no trend to see)
It is not misleading, it is data you never had before - it is more information. It has less fidelity than a sql trace - but it always on, it is always there. It can answer the question "what was different last night from the night before".
Blocking Sessions
Narendra, July 13, 2011 - 4:25 am UTC
11g Has introduced a new v$ view to identify blocking session details. It is called v$session_blockers, although one could argue that v$session already has enough details.