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
- 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
- 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
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
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
