Oracle provides a plethora of tools for monitoring user activity, including tracing, monitoring wait events, as well as ASH. However, sometimes I want a quick (literally) overview of a session. To do this, I query the gv$sesstat view. However, the data in this view is cumulative, so you don’t what happened during a single time frame. To print the differences over time, I wrote what is below to be run as a SQL*Plus script.<\/p>\n
\r\nset serveroutput on\r\nset feedback off verify off\r\n\r\nvariable v1 number\r\nexec :v1 := &sid\r\n\r\nvariable instance_id number\r\nexec :instance_id := &instance_id\r\n\r\nvariable sleep_time number\r\nexec :sleep_time := &sleep_time\r\n\r\ndeclare\r\n type t_vals1 is table of varchar2(100);\r\n type t_vals2 is table of number;\r\n type t_vals3 is table of varchar2(100);\r\n type t_vals4 is table of number;\r\n vals1 t_vals1 := t_vals1();\r\n vals2 t_vals2 := t_vals2();\r\n vals3 t_vals3 := t_vals3();\r\n vals4 t_vals4 := t_vals4();\r\n i number := 0;\r\n j number := 0;\r\n k number := 0;\r\n l number := 0;\r\nbegin\r\n for cur in (select name,value\r\n from gv$sesstat s, v$statname n\r\n where sid = :v1\r\n and s.statistic# = n.statistic#\r\n and inst_id = :instance_id\r\n order by name) loop\r\n vals1.extend(1);\r\n vals2.extend(1);\r\n i := i + 1;\r\n vals1(i) := cur.name;\r\n vals2(i) := cur.value;\r\n end loop;\r\n dbms_lock.sleep(:sleep_time);\r\n for cur in (select name,value\r\n from gv$sesstat s, v$statname n\r\n where sid = :v1\r\n and s.statistic# = n.statistic#\r\n and inst_id = :instance_id\r\n order by name) loop\r\n vals3.extend(1);\r\n vals4.extend(1);\r\n j := j + 1;\r\n vals3(j) := cur.name;\r\n vals4(j) := cur.value;\r\n end loop;\r\n k := i;\r\n l := 0;\r\n dbms_output.put_line('========================================================');\r\n while l < k loop\r\n l := l + 1;\r\n if vals1(l) = vals3(l) then\r\n if vals4(l) > vals2(l) then\r\n dbms_output.put_line(vals1(l) || ' ' || (vals4(l) - vals2(l)));\r\n end if;\r\n end if;\r\n end loop;\r\n dbms_output.put_line('========================================================');\r\nend;\r\n\/\r\nexit\r\n<\/pre>\nAll this script does is take three arguments:<\/p>\n
The SID in which you are interested
\nThe instance on which the session is running
\nThe number of seconds you want to sleep between checks.<\/p>\n
It queries gv$sesstat for the values for this SID, and then stores them in a dynamically defined type. After sleeping for the number fo seconds specified, it runs the same query again and stores the values in a second collection.<\/p>\n
Lastly, it compares them and prints only those that have changed in the monitoring window.<\/p>\n
I frequently run this with a time gap of as little as ten seconds. This prints output such as below:<\/p>\n
\r\n14:28:55 oracle@oh1xpwcdb01 ~ >sp @diff_sid_stat\r\n\r\nSQL*Plus: Release 10.2.0.4.0 - Production on Sat Sep 10 14:29:04 2011\r\n\r\nCopyright (c) 1982, 2007, Oracle. All Rights Reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, OLAP, Data Mining\r\nand Real Application Testing options\r\n\r\nEnter value for sid: 2836\r\nEnter value for instance_id: 1\r\nEnter value for sleep_time: 10\r\n========================================================\r\nCPU used by this session 53\r\nCPU used when call started 53\r\nDB time 154\r\nRowCR - row contention 5\r\nRowCR attempts 85\r\nRowCR hits 80\r\nSQL*Net roundtrips to\/from client 533\r\nactive txn count during cleanout 1\r\nbuffer is not pinned count 2414\r\nbuffer is pinned count 1264\r\nbytes received via SQL*Net from client 207827\r\nbytes sent via SQL*Net to client 180437\r\ncalls to get snapshot scn: kcmgss 5775\r\ncalls to kcmgas 83\r\ncalls to kcmgcs 1\r\ncleanout - number of ktugct calls 7\r\ncleanouts only - consistent read gets 6\r\ncluster wait time 10\r\ncommit cleanout failures: block lost 2\r\ncommit cleanout failures: callback failure 33\r\ncommit cleanouts 342\r\ncommit cleanouts successfully completed 307\r\ncommit txn count during cleanout 7\r\nconcurrency wait time 6\r\nconsistent changes 2914\r\nconsistent gets 7214\r\nconsistent gets - examination 2748\r\nconsistent gets direct 31\r\nconsistent gets from cache 7183\r\ndb block changes 3934\r\ndb block gets 9774\r\ndb block gets direct 62\r\ndb block gets from cache 9712\r\ndeferred (CURRENT) block cleanout applications 102\r\ndirty buffers inspected 75\r\nenqueue releases 158\r\nenqueue requests 158\r\nexecute count 541\r\nfree buffer inspected 671\r\nfree buffer requested 685\r\ngc CPU used by this session 1\r\ngc cr block receive time 1\r\ngc cr blocks received 5\r\ngc current block receive time 7\r\ngc current blocks received 42\r\ngc local grants 34\r\ngc remote grants 72\r\ngcs messages sent 151\r\nges messages sent 37\r\nglobal enqueue get time 5\r\nglobal enqueue gets sync 187\r\nglobal enqueue releases 187\r\nheap block compress 9\r\nhot buffers moved to head of LRU 280\r\nimmediate (CR) block cleanout applications 6\r\nimmediate (CURRENT) block cleanout applications 1\r\nindex fetch by key 306\r\nindex scans kdiixs1 758\r\nlob reads 496\r\nlob writes 1271\r\nlob writes unaligned 1271\r\nmessages sent 64\r\nno work - consistent read gets 2799\r\nopened cursors cumulative 225\r\nparse count (total) 225\r\nparse time cpu 1\r\nphysical read IO requests 122\r\nphysical read bytes 1318912\r\nphysical read total IO requests 122\r\nphysical read total bytes 1318912\r\nphysical reads 122\r\nphysical reads cache 91\r\nphysical reads direct 31\r\nphysical reads direct (lob) 31\r\nphysical reads direct temporary tablespace 31\r\nphysical write IO requests 62\r\nphysical write bytes 507904\r\nphysical write total IO requests 62\r\nphysical write total bytes 507904\r\nphysical writes 62\r\nphysical writes direct 62\r\nphysical writes direct temporary tablespace 62\r\nphysical writes non checkpoint 62\r\nprefetched blocks aged out before use 5\r\nrecursive calls 971\r\nrecursive cpu usage 36\r\nredo entries 599\r\nredo ordering marks 20\r\nredo size 357572\r\nredo synch time 12\r\nredo synch writes 62\r\nrows fetched via callback 212\r\nsession cursor cache count 1\r\nsession cursor cache hits 131\r\nsession logical reads 16988\r\nsession pga memory 65536\r\nsession uga memory 65408\r\nsession uga memory max 65408\r\nshared hash latch upgrades - no wait 104\r\nsorts (memory) 31\r\nsorts (rows) 567\r\ntable fetch by rowid 1571\r\nundo change vector size 148428\r\nuser I\/O wait time 73\r\nuser calls 663\r\nuser commits 62\r\nworkarea executions - optimal 124\r\n========================================================\r\nDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, OLAP, Data Mining\r\nand Real Application Testing options\r\n14:29:21 oracle@oh1xpwcdb01 ~ >\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Oracle provides a plethora of tools for monitoring user activity, including tracing, monitoring wait events, as well as ASH. However, sometimes I want a quick (literally) overview of a session. To do this, I query the gv$sesstat view. However, the…<\/p>\n