Smenu Wiki
Brought to you by:
bpolarsk,
ptimmermans
Menu
▾
▴
Smenu_commands
There is a newer version of this page. You can find it here.
- Smenu commands
- Show sql plan execution steps
- Show plan present in v$sql_plan sorted by cost
- view all open cursor for a session
- Display objects count and type per schema
- List DB tablespaces
- List content of a tablespace
- Show Hot segments in DB
- List Top sql sampled 3 seconds, sort by elapsed
- List Sql sorted by gets
- List Session statistics overview sorted by Disk reads
- List SQL with variant execution time
- List overview of Plan hash values stats performances for a given SQL_ID
- Mine AWR: List Session activity, locking and events for a past period
- MINE WAR: Past session events using AWR data imported from another DB
- MINE AWR: List plan from AWR for a given period
- create the AWR (text) report of the last period
- create the AWR (text) report on a previous period
- create the AWR (text) report on a previous range of period
- produce differential report between 2 (range) snap_id
- List buffer busy waits for last period
- Show sql performances through all snap_id
- Show AWR retentions parameters
- Set Awr retention period
- List bind captured into AWR for a given SQL_ID
- Show stats for a range of snap_id
- Show stats for an old snap_id
- Overview of each snapid statistics
- List DBID present into the repository
- List snap_id in AWR
- Show waits for last snap_id
- Show stats for an old snap_id
Smenu commands
Show sql plan execution steps
Given the following plan: /opt/scripts/smenu> sx -s 032c25z00scp3
* * *
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
* * *
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT UNIQUE | | 1 | 211 | 1 (100)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 211 | 0 (0)| |
| 3 | NESTED LOOPS | | 1 | 150 | 0 (0)| |
|* 4 | FIXED TABLE FULL | X$KGLLK | 1 | 60 | 0 (0)| |
|* 5 | FIXED TABLE FIXED INDEX| X$KGLNA (ind:1) | 1 | 90 | 0 (0)| |
|* 6 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD_SQLID (ind:1 | 1 | 61 | 0 (0)| |
* * *
We can see the orders of the executions steps: /opt/scripts/smenu> sx -stp 032c25z00scp3
Id in
Step plan LINE
* * *
----- --------------------------------------------------------------------------------------------------------------------------------------------
1 (4) FIXED TABLE FULL X$KGLLK Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
2 (3) NESTED LOOPS Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
3 (2) NESTED LOOPS OUTER Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
4 (1) SORT UNIQUE Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
6 (5) FIXED TABLE FIXED INDEX X$KGLNA (ind:1) Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
7 (3) NESTED LOOPS Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
8 (2) NESTED LOOPS OUTER Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
9 (1) SORT UNIQUE Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
11 (6) FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD_SQLID (ind:1 Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
12 (2) NESTED LOOPS OUTER Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
13 (1) SORT UNIQUE Ela= 0 (0) C.Ela=0 (0) Gets=0 (0) C.Gets=0 (0) Dsk Reads=0 (0) C.Dsk Reads=0 (0)E Rows=1 A Rows=0
Show plan present in v$sql_plan sorted by cost
/opt/scripts/smenu> sx -l -cost
MACHINE loadtest05 - ORACLE_SID : IBS Page: 1
Date - Wednesday 08th December 2010 16:53:51
Username - BPA List plan by Cost
Plan last active Nbr
Username hash value time SQL_ID Ch Cost Execs gets plan TXT
* * *
----------- ----------- ------------- --- ------ -------- ---------- ----- ------------------------------------------------------------
IBS6_EB_PERF 1186469467 12-06/16:14 6t6zxa9xnchk2 1 37414 1 164240 1 SELECT ba.bf_id, b.systemfilename, bc.contractid FROM b
IBS6_EB_USER 2167909657 12-03/11:38 b6zmf2cx39vw3 1 33607 73 11022136 4 select bf.bf_id, bf.sender, bf.bankcustomer_id, bf.upda
12-04/02:22 b6zmf2cx39vw3 0 33605 352 53147798 4 select bf.bf_id, bf.sender, bf.bankcustomer_id, bf.upda
12-07/02:26 b6zmf2cx39vw3 2 33605 40 6039532 4 select bf.bf_id, bf.sender, bf.bankcustomer_id, bf.upda
IBS6_EB_PERF 2167909657 12-08/00:18 b6zmf2cx39vw3 3 33605 17 2566808 4 select bf.bf_id, bf.sender, bf.bankcustomer_id, bf.upda
WMSYS 370289257 11-29/23:15 fnya6frbtntjm 0 8274 7 255809 1 update /* WM$SQL(1194)(IBS6_EB_OWNER.BFACL_BITCODE) */
1822140895 11-29/23:15 8y07kfdwu3a6q 0 8260 7 257813 1 update /* WM$SQL(1206)(IBS6_EB_OWNER.BFACL_BITCODE) */
1623947852 11-29/23:15 70m6fdav2dtmp 0 4596 7 116071 1 update /* WM$SQL(1194)(IBS6_EB_OWNER.SUMMCURRENCYLVL)
635047828 11-29/23:15 cppawjmf5mv8m 0 4591 7 115604 1 update /* WM$SQL(1206)(IBS6_EB_OWNER.SUMMCURRENCYLVL)
1392049166 12-03/13:35 6svawdhxztd6y 0 4510 2 181 1 update /* WM$SQL(1206)(IBS6_EB_OWNER.BF) */
3698432344 12-03/13:35 1nrpw4v6a7xzb 0 4509 2 177 1 update /* WM$SQL(1194)(IBS6_EB_OWNER.BF) */
IBS6_EB_PERF 1972646353 12-03/15:19 cps43g0yyyj1m 0 4230 14 1492746 4 SELECT cp.counterparty_id, cp.updatecount, cp.alias, cp
12-06/16:40 cps43g0yyyj1m 1 4230 218 23242218 4 SELECT cp.counterparty_id, cp.updatecount, cp.alias, cp
12-07/13:25 cps43g0yyyj1m 2 4230 897 95664557 4 SELECT cp.counterparty_id, cp.updatecount, cp.alias, cp
12-08/10:04 cps43g0yyyj1m 3 4230 3 319884 4 SELECT cp.counterparty_id, cp.updatecount, cp.alias, cp
BPA 1419144079 12-08/12:27 95zy2kcmfrrfh 0 4177 1 1942 1 select instance_number, to_char(sample_time,'HH24:MI:SS
4138118298 12-08/12:21 9m2qnbbppv9zm 0 4177 2 3915 1 select instance_number, to_char(sample_time,'HH24:MI:SS
WMSYS 2453292706 11-29/23:15 004yhvzd1htk8 0 3494 7 110187 1 update /* WM$SQL(1194)(IBS6_EB_OWNER.INITIATOREBUSERID
26703 11-29/23:15 71jhy6cb98f8u 0 3488 7 110173 1 update /* WM$SQL(1206)(IBS6_EB_OWNER.INITIATOREBUSERID
4089586455 11-29/23:15 famrx5a74h2hq 0 2989 6 78230 1 update /* WM$SQL(1194)(IBS6_EB_OWNER.BF2ARCHIVE) */
3475308645 11-29/23:15 7x18z3tm1xr7p 0 2983 6 78216 1 update /* WM$SQL(1206)(IBS6_EB_OWNER.BF2ARCHIVE) */
IBS6_EB_PERF 2502654689 12-07/14:25 d9r3jfj5n0nbm 0 2885 3 20454 1 select * from accountsgbyebuser where bankaccount_id in
12-07/14:30 fzg7uf7sqwaaw 0 2885 8 54514 1 select * from accountsgbyebuser where bankaccount_id in
662475514 11-30/23:19 fcq1v0s7uaj5c 0 2523 8 9088 1 SELECT bbb.beneficiarybankbranch_id, bbb.location, bbb.
2318155007 12-08/10:04 8b514yxf19h0c 1 2412 3 886 1 SELECT bf.bf_id, bf.status, bs.shortname, bf.userfilen
12-08/10:04 ctpktd10b14wj 1 2412 3 886 1 SELECT bf.bf_id, bf.status, bs.shortname, bf.userfilen
SYS 375163 11-29/17:32 7cq8d0jqxzum1 0 2284 47 488021 3 delete from smon_scn_time where thread=0 and scn = (se
12-01/02:14 7cq8d0jqxzum1 2 2284 147 1526337 3 delete from smon_scn_time where thread=0 and scn = (se
12-07/22:05 7cq8d0jqxzum1 4 2284 245 2543956 3 delete from smon_scn_time where thread=0 and scn = (se
view all open cursor for a session
/opt/scripts/smenu> soc -o 1322
HASH_VALUE COMMAND Cpu time (ms) LAST_ACTIVE_TIME Sql Text
* * *
-------------- ------------- ------------------------ ----------------------------------------------------------------
1950821498 SELECT 2.5 DEC-06 15:11:05 select value$ from props$ where name = 'GLOBAL_DB_NAME'
2017311249 SELECT 0.6 DEC-06 15:11:05 select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT
', 2 , 'PREPARSE', 4 , 0), decode(failover_type, NULL, 1 , 'NONE
', 1 , 'SESSION', 2, 'SELECT', 4, 1), failover_retries, failove
r_delay, flags from service$ where name = :1
2194907850 SELECT 2.4 DEC-06 15:11:05 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERE
NV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'),
SYS_CONTEXT('USERENV', 'SERVICE_NAME'), INSTANCE_NUMBER, STARTU
P_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance wher
e INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')
3873422482 SELECT 2.3 DEC-06 15:11:05 select privilege# from sysauth$ where (grantee#=:1 or grantee#=1
) and privilege#>0
Display objects count and type per schema
/opt/scripts/smenu> cpt
SCHEMA CLSTR TABLE INDEX SEQNC TRIGR FUNCT PROCD PACKG VIEWS SYNYM OTHER
* * *
------ ------ ------ ----- ----- ----- ------ ----- ------ ------ ------
SYS 10 709 837 83 10 77 58 506 2935 9 17889
SYSTEM 141 205 20 2 1 1 12 8 97
PUBLIC 20084 50
BPA 101 2 246
CTXSYS 37 47 3 2 2 67 55 136
DBSNMP 22 12 2 1 3 1 26
DMSYS 2 2 1 12 27 1 175
EXFSYS 44 39 1 5 23 8 17 56 136
MDSYS 55 86 7 38 107 3 49 59 598
OLAPSYS 126 137 5 48 1 45 308 177
ORACLE_OCM 3 18
ORDPLUGINS 5 11
ORDSYS 4 4 34 8 17 5 1663
OUTLN 3 5 1 1
SCOTT 4 2 0
SI_INFORMTN_SCHE 8 0
SYSMAN 342 426 5 48 8 2 73 136 375
TSMSYS 1 2 1
WMSYS 44 67 9 2 4 4 22 110 149
XDB 36 385 2 11 5 5 20 2 577
List DB tablespaces
/opt/scripts/smenu> frg
Free Largest Total Available % % used
Tablespace Frags Frag (Megs) (Megs) (Megs) Used Auto ext
* * *
-------- ------------ ------------ ------------ ---- ---------
SYSAUX 1 5.2 240.0 5.2 98 1
SYSTEM 2 8.9 450.0 9.0 98 1
TEMP 0 0.0 20.0 15.0 25 0
UNDOTBS1 19 1.1 25.0 3.5 86 0
USERS 2 0.8 1,288.8 1.5 100 4
-------- ------------ ------------
sum 24 2,023.8 34.2
List content of a tablespace
/opt/scripts/smenu> frg -t users -os
MACHINE loadtest05 - ORACLE_SID : DEV102A Page: 1
OWNER SEGMENT_NAME SEGMENT_TYPE Size(mb)
* * *
------------------------------------------------------- ------------------ ---------
BPA SYS_LOB0000052203C00038$$ LOBSEGMENT 573.0
WRH$_SQL_BIND_METADATA TABLE 496.0
SYS_LOB0000052200C00004$$ LOBSEGMENT 72.0
WRH$_SYSMETRIC_SUMMARY TABLE 23.0
WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_810902110_5020 TABLE PARTITION 12.0
WRH$_SQL_PLAN TABLE 11.0
WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_810902110_4709 TABLE PARTITION 11.0
WRH$_ENQUEUE_STAT TABLE 10.0
WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_810902110_4924 TABLE PARTITION 10.0
WRH$_SQLSTAT.WRH$_SQLSTA_810902110_4709 TABLE PARTITION 9.0
WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_810902110_4421 TABLE PARTITION 9.0
WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_810902110_4229 TABLE PARTITION 8.0
WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_810902110_4517 TABLE PARTITION 8.0
WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_810902110_4325 TABLE PARTITION 7.0
WRH$_SQLTEXT TABLE 4.0
WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_810902110_4613 TABLE PARTITION 4.0
SYS_IL0000052203C00038$$ LOBINDEX 0.4
SCOTT DEPT TABLE 0.1
EMP TABLE 0.1
BONUS TABLE 0.1
SALGRADE TABLE 0.1
BPA WRH$_FILESTATXS_BL TABLE 0.1
WRH$_TEMPSTATXS TABLE 0.1
WRH$_SYSTEM_EVENT_BL TABLE 0.1
WRH$_LATCH_NAME TABLE 0.1
WRH$_BUFFER_POOL_STATISTICS TABLE 0.1
Show Hot segments in DB
/opt/scripts/smenu> seg -hot
OWNER OBJECT_NAME SUB OBJECT_TYPE Statistics name Hits
* * *
---------------------------- ---------------------------- ------------------ ------------------------------ -------------------
BPA SYS_LOB0000052203C00038$$ - LOB physical writes direct 72,095
BPA SYS_LOB0000052203C00038$$ - LOB physical writes 72,496
BPA WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_810902110_4709 TABLE PARTITION logical reads 338,735,216
SYS SYS_LOB0000051836C00039$$ - LOB physical reads direct 378
SYS TYPE$ - TABLE physical reads 1,949
List Top sql sampled 3 seconds, sort by elapsed
/opt/scripts/smenu> sq -top 3 -e
MACHINE loadtest05 - ORACLE_SID : IBS
Date - Wednesday 01st December 2010 17:02:05
Username - BPA Top 24 sql sampled 3 seconds, sort by elapsed
Total number of SQL considered : 2533 --> actives : 4
. Elapse App/w IO/w Conc/w
Owner Hash_value C# execs Gets reads d.Writes (ms) (ms) (ms) (ms) SQL text
* * *
----------- --- ------ -------- -------- -------- ------ ------ ------- ------ --------------------------------------------------
IBS6_EB_PERF 4223820267 2 10 4921 0 0 57 0 0 0 select b.bf_id,b.status,b.releasestatus, b.sender,
IBS6_EB_USER 284895985 3 70 126 0 0 12 0 0 0 SELECT BFQ.BF_ID,BFQ.LASTUPDATE,BF.INITIATOR,BF.ST
SYS 2830877044 1 1 3 0 0 0 0 0 0 select job, nvl2(last_date, 1, 0) from sys.job$ wh
SYS 2848646510 1 1 3 0 0 0 0 0 0 select count(*) from sys.job$ where (next_date > s
List Sql sorted by gets
/opt/scripts/smenu> sq -e
invali- parse Disk Buff Gets Rows per cpu elapse Last time Parsing
Execs loads dations calls reads Per Exec Exec Time(s) Time(s) Loaded HASH_VALUE c# Schema name
* * *
------ --------- --------- --------- --------- --------- ------- ------- ----------- ---------- --- ----------------------
1568 1 0 599 1222 106644 23 1649 2969 29/23:22:20 1039090739 0 IBS6_EB_PERF
363898 12 12 363898 2091 53 0 1071 1047 29/22:49:35 3365955911 0 IBS6_EB_PERF
289749 5 3 289749 604 53 0 880 866 30/22:16:18 3365955911 2 IBS6_EB_PERF
602 1 0 37 78 106671 23 546 563 01/10:00:32 1039090739 1 IBS6_EB_PERF
2 1 0 2 3619678 2422304 0 286 557 29/22:00:03 84828483 0 SYS
2164 1 0 591 9915 2463 15 275 353 29/23:21:59 702466998 0 IBS6_EB_PERF
115 5 4 57 17045651 151013 0 325 345 30/00:37:50 976547715 1 IBS6_EB_USER
2 1 0 2 224429 214147 0 16 335 29/22:00:02 2932656157 0 SYS
58 1 0 58 187057 3957 27 11 323 29/22:00:07 3471794499 0 SYS
86 6 2 49 12730639 150995 0 247 293 30/23:48:52 976547715 2 IBS6_EB_USER
87356 11 7 87356 1592 53 0 264 258 29/17:16:35 3365955911 1 IBS6_EB_PERF
List Session statistics overview sorted by Disk reads
/opt/scripts/smenu>sl -l r
(Other possible sort are p : hard parse c : sort by CPU r : physical read b : block change g : gets cc : consistent change)
User Hard Blocks Consistent Consistent Physical
Sid username ospid Com HASH_VALUE Type Cpu Parse Changes Changes Gets Reads %Hit waits(s)
* * *
-------------------- ---------- --- ---------- ---- -------- ------- ---------- ---------- ----------- ---------- ---- ---------
141 SYS 16582 0 0 USER 0.00 649 355234 91832 1017743865 8219 99.9 125.8
164 -(SMON) 15938 0 0 BACK 0.00 48 10599 4 18771 3736 86.3 0.4
158 SYS 16361 47 3732391352 USER 2.00 357 15640 41 97129 560 99.4 2.0
161 -(MMON) 15944 0 3393152264 BACK 0.00 95 81 0 5529 365 93.7 0.9
144 SYS 16580 0 0 USER 0.00 103 6195 10 26135 178 99.4 0.2
162 -(CJQ0) 15942 0 0 BACK 0.00 28 0 0 9052 28 99.6 0.2
165 -(CKPT) 15936 0 0 BACK 0.00 7 0 0 151 6 96.0 0.2
163 -(RECO) 15940 0 0 BACK 0.00 3 0 0 149 2 98.6 0.0
150 -(q000) 15967 0 3393152264 BACK 0.00 4 0 0 158 2 98.7 0.0
156 -(ARC0) 15956 0 0 BACK 0.00 0 0 0 0 0 None 0.1
155 -(ARC1) 15958 0 0 BACK 0.00 0 0 0 0 0 None 0.1
154 -(QMNC) 15960 0 0 BACK 0.00 0 0 0 6 0 100 9.9
149 -(q001) 15972 0 3393152264 BACK 0.00 0 0 0 8 0 100
List SQL with variant execution time
/opt/scripts/smenu> sq -pl
MACHINE loadtest05 - ORACLE_SID : IBS
Date - Wednesday 01st December 2010 16:50:10
Username - BPA Sql stats per execution
Use 'sx' and 'aw' to further research
Min execution Max execution
SQL_ID EXECS Time Time NORM_STDDEV
* * *
------------ ------------- ------------- -------------
abrgm89497xy2 29 .03 .16 2.6453
5rbaurby4gn7h 1,894 .06 .35 3.4219
505cbu3296guw 7,546 .04 .28 4.1210
b4rcpvh8fxrgr 3 .20 1.68 5.1215
gd9z2yucjwtut 7,463 .01 .12 5.1600
3ubc6y3m6gdbn 4 .22 3.09 9.0420
c4p3dr8gv2n1q 537 .01 .19 13.9747
bq8n3wu5n435c 4 .02 .38 15.7384
0dwr2vd6vbqzs 42 .06 1.78 19.5416
56k61wftczbbk 7 .01 .30 37.3061
ddvwyh82v6jj9 152 .00 .12 38.7720
gtcqm547tz937 3 .08 4.56 40.6791
a2thpq2wh41nw 175 .00 .14 48.1570
a4apv2vk5vxmp 780 .00 339.05 90,720.6052
b60kdnvjvfp2f 815 .00 345.61 93,674.2458
List overview of Plan hash values stats performances for a given SQL_ID
/opt/scripts/smenu/tmp> sq -ph 5rbaurby4gn7h
Begin interval Plan hash Total exec Avg Disk Wait
Snap Inst time SQL_ID Value Cost Execs Time(s) Time(ms) Avg Gets Reads time(ms)
* * *
----- ---------------------- ------------- ---------- ------ -------- ------------ ----------- ---------- --------- --------
30157 1 2010-12-01 10:27:19 5rbaurby4gn7h 2681598973 32 51 .4 8.2 1355 0 164
30132 1 2010-11-30 23:19:49 5rbaurby4gn7h 32 252 11.7 46.6 1599 3 9901
30125 1 2010-11-30 18:16:20 5rbaurby4gn7h 32 48 .3 5.2 1357 0 0
30117 1 2010-11-30 15:57:20 5rbaurby4gn7h 32 58 .3 5.3 1405 0 0
30111 1 2010-11-30 14:12:01 5rbaurby4gn7h 32 23 .1 4.9 1393 0 0
30107 1 2010-11-30 12:09:14 5rbaurby4gn7h 32 48 .4 7.9 1357 0 139
30092 1 2010-11-29 23:23:01 5rbaurby4gn7h 32 252 16.0 63.6 1599 5 14057
30082 1 2010-11-29 14:00:10 5rbaurby4gn7h 32 51 11.5 225.6 1366 17 11014
30066 1 2010-11-28 23:14:09 5rbaurby4gn7h 32 252 12.9 51.3 1599 4 11073
30040 1 2010-11-27 23:14:08 5rbaurby4gn7h 32 252 13.9 55.3 1599 4 11983
29992 1 2010-11-26 23:26:26 5rbaurby4gn7h 32 252 14.1 56.1 1599 6 12253
29977 1 2010-11-26 09:00:00 5rbaurby4gn7h 32 252 20.1 79.9 1599 7 18108
29955 1 2010-11-25 15:00:15 5rbaurby4gn7h 32 51 9.3 183.3 1356 3 482
29945 1 2010-11-25 05:02:20 5rbaurby4gn7h 2389699352 64 51 18.1 354.7 738 9 4623
29929 1 2010-11-24 14:00:15 5rbaurby4gn7h 64 1 .2 246.9 745 34 121
Mine AWR: List Session activity, locking and events for a past period
/opt/scripts/smenu> aw -slk -b 5084
MACHINE loadtest05 - ORACLE_SID : DEV102A Page: 1
Date - Thursday 09th December 2010 12:27:59
Username - SYS Processing only first 20000 rows of snap range, use -rn <nn> to increase input sample
Prev wait : any value > 0 means SQL currently running on CPU, numeric refer to wait(secs) before this run
Prev
In Usr wait
st Time Sid Serl# id SQL_ID time(s) Event Fl# obj# block# Session sql text
--- -------- -------------- ------ ---- ------------- --------- -------------------------- ---- ------- -------- -----------------------------------
1 14:00:25 771 61945 48 -wait- log file sync 51 78011 31405
818 1 0 -wait- log file parallel write 0 -1 0
14:00:55 1577 63589 48 3tuvfaqr1raza -wait- gc current block busy 53 78192 16457
760 63079 48 3tuvfaqr1raza -wait- gc buffer busy 53 78192 16457
746 5627 48 0g7y44z6btjh0 -wait- gc current block busy 5 78021 28832 update usermessagepkiuser set useri
1589 1308 48 0g7y44z6btjh0 -wait- gc buffer busy 5 78021 28832 update usermessagepkiuser set useri
1577 63589 48 5b1tx3jug7ksc -wait- enq: TM - contention 47 77945 68267 INSERT into BF(BF_ID,INITIATOR,STAT
1621 34237 48 97tg3rp58f0xm -wait- enq: TM - contention 47 77936 650 Delete from DOWNLOADERPROFILE where
778 6583 48 c29pbjn2wgrj8 0.01 49 78252 214506 select ba.bankaccount_id, ba.update
1584 20330 48 186pn4b8x25r7 -wait- enq: TM - contention 1 559 72929 INSERT INTO BF(BF_ID,STATUS,USERFIL
1621 34237 48 97tg3rp58f0xm -wait- enq: TM - contention 47 77936 650 Delete from DOWNLOADERPROFILE where
778 6583 48 c29pbjn2wgrj8 0.01 49 78252 214506 select ba.bankaccount_id, ba.update
MINE WAR: Past session events using AWR data imported from another DB
/opt/scripts/smenu/scripts]> aw -dbid 810902110 -b 5084 -sa 778
We imported into this our TEST DB, the WAR of production DB, whose DBID is 81092100.
We can mine the AWR by adding '-dbid 810902110'. Since on this TEST db we mine all the time production DATA
we even put this "alias aw1='aw -dbid 810902110 '" so that to avoid type all the type the -dbid nn
Let's see session 778 activity, that was causing an lock (see "List Session activity, locking" just above )
Prev Block
Sample Usr Inst wait Block sess
time Username id Num Serl# time(s) SQL_ID Wait class Event XID sess serl#
* * *
------------------ ---- ---- ------ -------- ------------- ------------ ---------------------------- ---------------- ----- ------
15:09:26 IBS6_EB_USER 48 1 6583 0.01 a2nq0h7t8y295 0006002400009C27
15:09:37 1 0.01 bcvswhfj5mt3x 0006002400009C27
15:09:47 1 0.01 b95mvskrb84kt 0006002400009C27
15:10:07 1 0.01 bcvswhfj5mt3x 0006002400009C27
15:10:37 1 0.01 c29pbjn2wgrj8 0006002400009C27
15:10:48 1 0.01 b95mvskrb84kt 0006002400009C27
15:10:58 1 0.01 c29pbjn2wgrj8 0006002400009C27
15:11:08 1 0.02 b95mvskrb84kt 0006002400009C27
15:11:16 2 10964 0.02 86gzgqd2w3sds
15:11:18 1 6583 11.92 a2nq0h7t8y295 0006002400009C27
15:11:28 1 0.01 bcvswhfj5mt3x 0006002400009C27
15:11:48 1 0.01 b95mvskrb84kt 0006002400009C27
15:11:58 1 0.01 0006002400009C27
15:12:08 1 11.67 a2nq0h7t8y295 0006002400009C27
15:12:18 1 0.01 0006002400009C27
15:12:39 1 0.01 0006002400009C27
15:12:49 1 11.16 0006002400009C27
15:12:59 1 1.26 ad6h0aausfn97 0006002400009C27
15:13:09 1 6.98 a1873zsqzgn99 0006002400009C27
15:13:19 1 24.48 0006002400009C27
15:13:29 1 0.01 c29pbjn2wgrj8 0006002400009C27
15:13:39 1 6.81 a2nq0h7t8y295 0006002400009C27
15:13:47 2 10964 0.00 186pn4b8x25r7 Application enq: TM - contention
15:13:57 2 0.00 Application enq: TM - contention
15:13:59 1 6583 0.01 bcvswhfj5mt3x 0006002400009C27
.
.
MINE AWR: List plan from AWR for a given period
/opt/scripts/smenu> aw -pl 2681598973 -b 29946
MACHINE loadtest05 - ORACLE_SID : IBS Page: 1
Date - Wednesday 01st December 2010 16:55:42
Username - BPA Show plan for 2681598973
Type of
SNAP_ID ID PARENT Operations COST CARDINALITY SEARCH_COLUMNS OBJECT_NODE OBJECT_NAME
* * *
---- ------ --------------------------------------------- ---------- ----------- -------------- ---------------- ----------------------
30132 0 SELECT STATEMENT 32 0
1 0 HASH UNIQUE 32 1 0
2 1 NESTED LOOPS 31 1 0
3 2 NESTED LOOPS 29 1 0
4 3 NESTED LOOPS 28 1 0
5 4 NESTED LOOPS 27 1 0
6 5 NESTED LOOPS 25 1 0
7 6 NESTED LOOPS 24 1 0
8 7 NESTED LOOPS 22 1 0
9 8 NESTED LOOPS 19 1 0
10 9 NESTED LOOPS 16 1 0
11 10 NESTED LOOPS 13 1 0
create the AWR (text) report of the last period
/opt/scripts/smenu>aw -r
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
* * *
----------- ------------ -------- ----------- --- ------------
IBS 383613666 IBS 1 10.2.0.5.0 NO loadtest05
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 30165 01-Dec-10 15:00:29 81 40.7
End Snap: 30166 01-Dec-10 16:00:30 85 39.4
Elapsed: 60.03 (mins)
DB Time: 3.63 (mins)
.
.
.
create the AWR (text) report on a previous period
/opt/scripts/smenu> aw -r -b 30150
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
* * *
----------- ------------ -------- ----------- --- ------------
IBS 383613666 IBS 1 10.2.0.5.0 NO loadtest05
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 30150 01-Dec-10 09:54:51 83 41.9
End Snap: 30151 01-Dec-10 10:00:24 83 41.9
Elapsed: 5.54 (mins)
DB Time: 0.39 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 640M 640M Std Block Size: 8K
Shared Pool Size: 464M 464M Log Buffer: 15,136K
.
.
.
create the AWR (text) report on a previous range of period
/opt/scripts/smenu> aw -r -b 30150 -e 30155
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
* * *
----------- ------------ -------- ----------- --- ------------
IBS 383613666 IBS 1 10.2.0.5.0 NO loadtest05
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 30150 01-Dec-10 09:54:51 83 41.9
End Snap: 30155 01-Dec-10 10:26:05 82 41.9
Elapsed: 31.23 (mins)
DB Time: 2.45 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 640M 640M Std Block Size: 8K
Shared Pool Size: 464M 464M Log Buffer: 15,136K
produce differential report between 2 (range) snap_id
/opt/scripts/smenu> aw -dif -b 30150 -e 30150 -b 30160 -e 30161
WORKLOAD REPOSITORY COMPARE PERIOD REPORT
Snapshot Set DB Id Instance Inst Num Release Cluster Host
* * *
----------- ------------ -------- ----------- ------- ------------
First (1st) 383613666 IBS 1 10.2.0.5.0 NO loadtest05
Second (2nd) 383613666 IBS 1 10.2.0.5.0 NO loadtest05
Snapshot Set Begin Snap Id Begin Snap Time End Snap Id End Snap Time Elapsed Time (min) DB Time (min) Avg Active Users
* * *
-------------- ------------------ ------------ ------------------ -------------------------- -------------------------- --------------------------
1st 30160 01-Dec-10 11:21:21 30161 01-Dec-10 11:21:36 0.26 0.10 0.38
2nd 30161 01-Dec-10 11:21:36 30162 01-Dec-10 11:36:27 14.84 14.93 58.02
List buffer busy waits for last period
/opt/scripts/smenu> aw -bbw
SORT0 SORT1 DAY OBJECT_TYPE OWNER OBJECT_NAME BUFFER_BUSY_WAITS
* * *
---------- ------ ------------------ ------------------ ------------------------------ -----------------
20101201 1 01-DEC TABLE SYS OPQTYPE$ 0
20101201 1 INDEX SYS I_COBJ# 0
20101201 1 TABLE SYS PROPS$ 0
20101201 1 INDEX SYS I_SETTINGS1 0
20101201 1 SYS I_LOB2 0
20101201 1 SYS I_COLTYPE1 0
20101201 1 SYS I_SUBCOLTYPE1 0
20101201 1 SYS SYS_IOT_TOP_7492 0
Show sql performances through all snap_id
/opt/scripts/smenu> aw -pf b6zmf2cx39vw3
direct
Plan Optim Row Fetches Elapsed cpu Disk Cluster App Concurr write
Snap Date hash value cost Exec Gets processed delta time(ms) Time(ms) reads iowait iowait iowait wait
* * *
------------ ------- ------- ---------- ---------- --------- ----------- ----------- ---------- ---------- ------- ------- ----------
2010-12-01 15:00 2167909657 33607 5 755047 0 5 18623.8 15940.6 742343 11487656 0 0 0
2010-12-01 14:00 2167909657 33607 5 755067 0 5 33639.9 17040.4 742703 26653925 0 0 0
2010-12-01 13:00 2167909657 33607 5 755063 0 5 30416.0 15021.7 742903 23408730 0 0 0
2010-12-01 11:36 2167909657 33607 7 1056964 0 7 19644.6 19288.1 1039196 10639334 0 0 0
2010-12-01 11:21 2167909657 33607 2 302010 0 2 5925.2 5558.2 296527 3394756 0 0 0
2010-12-01 11:00 2167909657 33607 1 150988 0 1 3541.0 2754.6 148326 2242929 0 0 0
2010-12-01 10:29 2167909657 33607 2 301976 0 2 6292.1 5663.1 296608 3665457 0 0 0
2010-12-01 10:00 2167909657 33607 3 452978 0 3 9242.0 8379.7 444527 5331891 0 0 0
2010-12-01 09:46 2167909657 33607 1 150988 0 1 3278.5 2846.6 148256 1933929 0 0 0
Show AWR retentions parameters
/opt/scripts/smenu]> aw -lret
MACHINE loadtest05 - ORACLE_SID : IBS Page: 1
Date - Wednesday 01st December 2010 16:20:58
Username - BPA Retention Period for AWR repository
Snapshot Interval Retention Retention
(minutes) (in minutes) (in days)
* * *
------------ ----------
60 10080 7
Set Awr retention period
/opt/scripts/smenu> aw -set 20160 30 -x
--> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(20160,30) ;
Add -x to execute. This command wil then be executed:
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(20160,30) ;
List bind captured into AWR for a given SQL_ID
/opt/scripts/smenu]> aw -sb 8ak51tv4a0ua7
Data
snap_id Date capture pos Name Value type
* * *
---------------- ---- ---------------- ------------------------------ ---------------
30166 2010-12-01 15:49 6 :6 90000004543809 NUMBER
5 :5 NUMBER
4 :4 TIMESTAMP
3 :3 NUMBER
2 :2 NUMBER
1 :1 NUMBER
30165 2010-12-01 14:49 6 :6 90000004543809 NUMBER
5 :5 NUMBER
4 :4 TIMESTAMP
3 :3 NUMBER
2 :2 NUMBER
1 :1 NUMBER
30164 2010-12-01 13:49 6 :6 90000004543809 NUMBER
5 :5 NUMBER
4 :4 TIMESTAMP
3 :3 NUMBER
2 :2 NUMBER
1 :1 NUMBER
Show stats for a range of snap_id
/opt/scripts/smenu]> aw -sl -b 30142 -e 30150
sql Total execution Total Total Total
SQL_ID Profile Time (sec) Executions Disk reads Buffer gets IO Wait SQL_TEXT_FRAGMENT
* * *
------- --------------- ---------- ---------- ----------- ---------- -------------------------------------------------------
8ak51tv4a0ua7 85.67 29673 29 1580679 .001224 UPDATE BF SET STATUS=:1 , CONTROLSTATUS=:2 , RELEASESTA
b6zmf2cx39vw3 29.26 10 1478278 1509880 16.220974 select bf.bf_id, bf.sender, bf.bankcustomer_id, bf.upda
98pbuc8pydm23 19.87 29708 1 273699 .02343 begin if(true or :1 is null or :2 is null or :3 is nul
8a59xhf4wrpyn 19.74 29674 27 683413 .0009 INSERT INTO BF2ARCHIVE (BF_ID, LASTUPDATE, CLASS_ID) VA
fcc148zc418z8 17.56 7 459 35895 8.152136 begin DBMS_WORKLOAD_REPOSITORY.create_snapshot(); end;
b27aa442at1xz 13.62 29682 3 1569977 .036708 SELECT sd.domainname,st.token,st.useridentity_id,sd.epa
ad2ksh7u0btxw 13.52 29706 1 273681 .02343 UPDATE IBS6_EB_OWNER.BF_LT SET ISURGENT = :B73,UPLOADTO
2vgd86jzmcak3 12.52 29682 2 237916 .000073 DELETE FROM BF2SEND WHERE BF_ID = :B1
350myuyx0t1d6 11.32 9 0 3558 8.543694 insert into wrh$_tablespace_stat (snap_id, dbid, ins
4g50ymc8gqarj 10.95 67783 0 75984 .048236 SELECT BFQ.BF_ID,BFQ.LASTUPDATE,BF.INITIATOR,BF.STATUS,
fnmrqzvxw4sgb 8.97 1910 3 940155 .282189 select b.bf_id,b.status,b.releasestatus, b.sender, b.ba
59ntbm3nbzc6v 8.21 29673 27 653318 .0009 SELECT NEXTVER, LTLOCK, DELSTATUS, VERSION, ROWID FROM
Show stats for an old snap_id
/opt/scripts/smenu]> aw -sl -b 30142
sql Total execution Total Total Total
SQL_ID Profile Time (sec) Executions Disk reads Buffer gets IO Wait SQL_TEXT_FRAGMENT
* * *
------- --------------- ---------- ---------- ----------- ---------- -------------------------------------------------------
8ak51tv4a0ua7 63.32 22114 0 1177989 .000192 UPDATE BF SET STATUS=:1 , CONTROLSTATUS=:2 , RELEASESTA
b6zmf2cx39vw3 22.77 8 1181860 1207904 12.420478 select bf.bf_id, bf.sender, bf.bankcustomer_id, bf.upda
98pbuc8pydm23 14.70 22115 0 203803 0 begin if(true or :1 is null or :2 is null or :3 is nul
8a59xhf4wrpyn 14.62 22115 0 509328 0 INSERT INTO BF2ARCHIVE (BF_ID, LASTUPDATE, CLASS_ID) VA
b27aa442at1xz 10.08 22115 0 1169661 0 SELECT sd.domainname,st.token,st.useridentity_id,sd.epa
ad2ksh7u0btxw 9.98 22114 0 203794 0 UPDATE IBS6_EB_OWNER.BF_LT SET ISURGENT = :B73,UPLOADTO
2vgd86jzmcak3 9.22 22114 0 177275 0 DELETE FROM BF2SEND WHERE BF_ID = :B1
4g50ymc8gqarj 8.08 50576 0 50579 .029586 SELECT BFQ.BF_ID,BFQ.LASTUPDATE,BF.INITIATOR,BF.STATUS,
Overview of each snapid statistics
/opt/scripts/smenu>aw -lst
Hard
Snap DB SQL PL/SQL Parse Parse Background
SNAP_ID BEGIN_SNAP len(s) DB Time On cpu exec time exec time Time Time Time
* * *
---------------------- ------ -------- -------- --------- --------- ------ ------ ----------
2 01 Dec 2010 14:00:23 3510 58.8 58.8 57.4 0 0 0 0
1 01 Dec 2010 13:26:18 2045
List DBID present into the repository
/opt/scripts/smenu/tmp]> aw -ldb
MACHINE loadtest05 - ORACLE_SID : DEV102A Page: 1
Date - Wednesday 01st December 2010 16:05:17
Username - SYS List dbid present in the repository
DBID COUNT
* * *
----------
2981407198 3
List snap_id in AWR
/opt/scripts/smenu]> aw -l
Snapshots for DEV102A instance : 1
###### ==============================
DBID SNAP_ID SNAP_LEVEL SNAP_BEGIN SNAP_END
* * *
---------- ---------- --------------------- ---------------------
2981407198 3 1 01 Dec 2010 14:58:52 01 Dec 2010 16:00:26
2981407198 2 1 01 Dec 2010 14:00:23 01 Dec 2010 14:58:52
2981407198 1 1 01 Dec 2010 13:26:18 01 Dec 2010 14:00:23
Show waits for last snap_id
/opt/scripts/smenu]> aw -sll -wait
Total execution total IO cluster Application Concurrency
SQL_ID Time (sec) Wait Wait Wait Wait Wait SQL_TEXT_FRAGMENT
* * *
--------------- ------------ ------------ ------------ ------------ ------------ -------------------------------------------------------
b6zmf2cx39vw3 18.62 11.49 11.49 0.00 0.00 0.00 select bf.bf_id, bf.sender, bf.bankcustomer_id, bf.upda
350myuyx0t1d6 2.34 2.03 2.03 0.00 0.00 0.00 insert into wrh$_tablespace_stat (snap_id, dbid, ins
7mtcjxvu79pz6 1.12 0.40 0.40 0.00 0.00 0.00 SELECT ebu.ebuser_id ebuserId FROM ebuser ebu WHERE ebu
azqfknz85afth 0.64 0.31 0.31 0.00 0.00 0.00 UPDATE bankaccount set bankservice_id = :1 , countryiso
48a932sgdj441 0.32 0.27 0.27 0.00 0.00 0.00 SELECT anr.bankaccount_id,anr.formattedaccountnumber FR
fktgk087pf692 1.26 0.25 0.25 0.00 0.00 0.00 SELECT 'SCHEMA' type, username owner, username object_n
fnmrqzvxw4sgb 4.68 0.14 0.00 0.00 0.00 0.14 select b.bf_id,b.status,b.releasestatus, b.sender, b.ba
451q2032pshqm 0.15 0.14 0.14 0.00 0.00 0.00 select owner#,name,namespace,remoteowner,linkname,p_tim
8swypbbr0m372 0.12 0.12 0.12 0.00 0.00 0.00 select order#,columns,types from access$ where d_obj#=:
Show stats for an old snap_id
opt/scripts/smenu> aw -sl -b 30151
sql Total execution Total Total Total
SQL_ID Profile Time (sec) Executions Disk reads Buffer gets IO Wait SQL_TEXT_FRAGMENT
* * *
------- --------------- ---------- ---------- ----------- ---------- -------------------------------------------------------
8ak51tv4a0ua7 4.66 1562 0 83219 0 UPDATE BF SET STATUS=:1 , CONTROLSTATUS=:2 , RELEASESTA
12v4bu5t9rcdv 3.91 1 9 366990 .000073 select output from table(dbms_workload_repository.awr_r
fcc148zc418z8 3.74 2 97 9133 1.1902 begin DBMS_WORKLOAD_REPOSITORY.create_snapshot(); end;
bunssq950snhf 1.23 2 0 20 0 insert into wrh$_sga_target_advice (snap_id, dbid, in
8a59xhf4wrpyn 1.07 1562 0 35994 0 INSERT INTO BF2ARCHIVE (BF_ID, LASTUPDATE, CLASS_ID) VA
98pbuc8pydm23 1.06 1562 0 14374 0 begin if(true or :1 is null or :2 is null or :3 is nul
cps43g0yyyj1m 0.89 2 27 108602 .023507 SELECT cp.counterparty_id, cp.updatecount, cp.alias, cp
9xn1c3wcm1rwr 0.88 1 35 22833 .148314 INSERT INTO sharedcounterparty ( sharedcounterparty_id,
b27aa442at1xz 0.73 1562 0 82622 0 SELECT sd.domainname,st.token,st.useridentity_id,sd.epa
ad2ksh7u0btxw 0.72 1562 0 14374 0 UPDATE IBS6_EB_OWNER.BF_LT SET ISURGENT = :B73,UPLOADTO
2vgd86jzmcak3 0.69 1562 0 12528 0 DELETE FROM BF2SEND WHERE BF_ID = :B1
