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
- view all Shortcuts
- view all shortcuts starting by 's'
- Edit source of shortcut 'frg'
- Show me discussion about ...
- SQL: Show me sql actives the last 'n' seconds
- SQL: Show me the text of the sql whose hash_value|sql_id ...
- SQL:Format the sql text whose hash_Value|sql_id is
- Generate a usable script for this sql with binds
- SQL: Show sql plan execution steps
- List tables columns used as predicates
- View query that uses a given table column as predicate
- 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 events profile over a period of time
- 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
view all Shortcuts
/opt/scripts/smenu> **sp**
|-------------------------------------------------------- |
| SMENU SHORTUCTS SUMMARY (vsl <shct> for more info) |
|-------------------------------------------------------- |
Administrative and miscellanous : wpe sp wp vsh aud lsbk dbrep
Database, jobs : up rac sts vsp dblk jb shed
SGA : soc buf lc par pard sga lom
Stats, Logminer, statspack, trace : sstv sx aw xpl lgm tkp
Tablespaces, datafiles, transport. : frg lstd asm ttbl
Tables, index and objects sources : dsk src dep cpl idx tbl obj seg mod sta
Sessions : cpt mts ks sa sl lsqr dpf
Users and grants : drm rol prf
SQL and Undo : sq sqn slo st spx tx rlbs
Latch and enqueue : lck lat
Redo, Dataguard, Streams, Mview : rdl apl dg app cap rsy rul aq prop mw rep
Waits, events and stats : sys ses sls sle wss srv
view all shortcuts starting by 's'
/opt/scrpits/smenu> vsl s
List of Shortcuts starting with 's' :
========================================
ss "sqlplus / as sysdba"
sel $SBINS/SELECT #0# Select prg using awk
smd cd $SBIN # go to SBIN
spm more $SBINS/addpar.sh # More of addpar.sh
sp $SBINS/smenu_list_shortct_cat.ksh #0# Show Smenu Shortcuts
sm cd $SBIN;$SBIN/smenu.sh #0# Call smenu
src $SBIN/module2/s1/smenu_src.ksh #4# Source view/funct/pkg/proc
sts $SBIN/module2/s1/smenu_view_archive_mode.sh #1# show database status
sq $SBIN/module2/s2/smenu_get_sql_figures.sh #6# Figures for SQL
sqn $SBIN/module2/s2/smenu_get_sqltext_60char.ksh #6# Show first 60 char
slo $SBIN/module2/s2/smenu_long_ops.ksh #6# Show first 60 char
soc $SBIN/module2/s2/smenu_handle.ksh #a# view cursors and handles
sa $SBIN/module2/s2/smenu_session_activity.sh #5# check user activity
st $SBIN/module2/s2/smenu_get_sql_text.ksh #6# Get the sql text for an address
sl $SBIN/module2/s2/smenu_sessions_overview.sh #5# Show open sessions info
seg $SBIN/module2/s4/smenu_seg.ksh #4# all about segment statistics
sys $SBIN/module2/s6/smenu_sys_stats.ksh #9# Show system statistics
ses $SBIN/module2/s6/smenu_session_stats.ksh #9# Show system statistics
sls $SBIN/module2/s6/smenu_system_event.sh #9# Show system events
sle $SBIN/module2/s6/smenu_session_event.sh #9# Show sessions events
srv $SBIN/module2/s6/smenu_service.ksh #9# Show all about services
spx $SBIN/module2/s7/smenu_show_pq_slave.ksh #6# Show parallel query slave
sga $SBIN/module2/s8/smenu_share_mem_usage.sh #a# Shared mem usage
shed $SBIN/module3/s3/smenu_scheduler.ksh #1# List scheduler jobs
sstv $SBIN/module3/s4/smenu_choose_session_to_set_event.ksh #2# Set trace in session
sx $SBIN/module3/s4/smenu_dyn_explain_plan.ksh #2# Display dynamic explain plan
sta $SBIN/module3/s6/smenu_gather_stat_tbl.ksh #4# gather stats on table
Edit source of shortcut 'frg'
/home/scripts/smenu> vsh frg
This works for any shortcuts!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | |
Show me discussion about ...
'wp' will do a grep on its base to see if there is a discussion about the word:
/home/scripts/smenu> wp hint
wp hint
1) Hint ALL_ROWS 42) Hint NO_INDEX
2) Hint AND_EQUAL 43) Hint NO_INDEX_FFS
3) Hint APPEND 44) Hint NO_MERGE
4) Hint CACHE 45) Hint NO_MONITOR
5) Hint CHOOSE 46) Hint NO_NATIVE_FULL_OUTER_JOIN
6) Hint CLUSTER 47) Hint NO_PARALLEL
7) Hint CURSOR_SHARING_EXACT 48) Hint NO_PARALLEL_INDEX
8) Hint DRIVING_SITE 49) Hint NO_PUSH_PRED
9) Hint DYNAMIC_SAMPLING 50) Hint NO_PUSH_SUBQ
10) Hint EXPAND_GSET_TO_UNION 51) Hint NO_PX_JOIN_FILTER
11) Hint FACT 52) Hint NO_QUERY_TRANSFORMATION
12) Hint FIRST_ROWS 53) Hint NO_RESULT_CACHE
13) Hint FULL 54) Hint NO_REWRITE
14) Hint HASH 55) Hint NO_STAR_TRANSFORMATION
15) Hint HASH_AJ 56) Hint NO_SWAP_JOIN_INPUTS
16) Hint HASH_SJ 57) Hint NO_UNNEST
17) Hint INDEX 58) Hint NO_USE_HASH
18) Hint INDEX_ASC 59) Hint NO_USE_MERGE
19) Hint INDEX_COMBINE 60) Hint NO_USE_NL
20) Hint INDEX_DESC 61) Hint OPT_PARAM
21) Hint INDEX_FFS 62) Hint ORDERED
22) Hint INDEX_JOIN 63) Hint ORDERED_PREDICATES
23) Hint INDEX_RS_ASC 64) Hint PARALLEL
24) Hint INDEX_RS_DESC 65) Hint PARALLEL_INDEX
25) Hint INDEX_SS 66) Hint PQ_DISTRIBUTE
26) Hint INDEX_SS_ASC 67) Hint PUSH_PRED
27) Hint INDEX_SS_DESC 68) Hint PUSH_SUBQ
28) Hint INLINE 69) Hint QB_NAME
29) Hint LEADING 70) Hint RESULT_CACHE
30) Hint MATERIALIZE 71) Hint REWRITE
31) Hint MERGE 72) Hint ROWID
32) Hint MERGE_AJ 73) Hint STAR
33) Hint MERGE_SJ 74) Hint STAR_TRANSFORMATION
34) Hint MONITOR 75) Hint SWAP_JOIN_INPUTS
35) Hint NATIVE_FULL_OUTER_JOIN 76) Hint UNNEST
36) Hint NL_AJ 77) Hint USE_CONCAT
37) Hint NL_SJ 78) Hint USE_HASH
38) Hint NOAPPEND 79) Hint USE_MERGE
39) Hint NOCACHE 80) Hint USE_NL
40) Hint NO_EXPAND 81) Hint USE_NL_WITH_INDEX
41) Hint NO_FACT 82) Hint
Select a field to explain, e to leave ==> 18
-------------------------------------------------
Hint INDEX_ASC
-------------------------------------------------
The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan,
then Oracle scans the index entries in ascending order of their indexed values.
/*+ INDEX_ASC ( table [index [index]...] ) */
Because Oracle's default behavior for a range scan is to scan index entries in ascending order of their indexed values,
this hint does not specify anything more than the INDEX hint. However, you might want to use the INDEX_ASC hint
to specify ascending range scans explicitly should the default behavior change.
SQL: Show me sql actives the last 'n' seconds
/home/scripts/smenu> sq -m 4
MACHINE loadtest08 - ORACLE_SID : IBS
Date - Wednesday 29th December 2010 15:13:47
Username - SYS List SQL run during the last 4 seconds
Last active
time HASH_VALUE TO_EXECS AVG_GETS Owner SQL_TEXT
* * *
---------- ---------- ------------ ---------------- ---------------------------------------------------------------------------
15:13:45 1070336601 1 2.0 SYS select to_char(last_active_time,'HH24:MI:SS')last_active_time, hash_value
4281219134 20453 28.4 SYSMAN BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
2194907850 339 0.0 SYS select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UN
1398610540 588 2.0 SYS select text from view$ where rowid=:1
1950821498 3555 4.1 SYS select value$ from props$ where name = 'GLOBAL_DB_NAME'
3737418196 1 0.0 SYS select 'Date - '||to_char(sysdate,'Day Ddth Month YYYY HH24:
SQL: Show me the text of the sql whose hash_value|sql_id ...
/home/scripts/smenu> st 2329266020
SELECT PING.TARGET_GUID, TGT.TIMEZONE_REGION, TGT.TIMEZONE_DELTA, PING.STATUS FROM MGMT_TARGETS TGT, MGMT_EMD_PING PING WHERE TGT.TA
RGET_GUID = PING.TARGET_GUID AND TGT.TARGET_TYPE = :B2 AND TGT.EMD_URL = :B1 FOR UPDATE OF PING.TARGET_GUID
SQL:Format the sql text whose hash_Value|sql_id is
This is basic but work for 99% of SELECT. I recommend to pay a look to the source 'vsh st' /home/scripts/smenu> st -f 2329266020
SELECT
PING.TARGET_GUID, TGT.TIMEZONE_REGION, TGT.TIMEZONE_DELTA, PING.STATUS
FROM
MGMT_TARGETS TGT,
MGMT_EMD_PING PING
WHERE
TGT.TARGET_GUID = PING.TARGET_GUID
AND TGT.TARGET_TYPE = :B2
AND TGT.EMD_URL = :B1 FOR UPDATE OF PING.TARGET_GUID
Generate a usable script for this sql with binds
/home/scripts/smenu> st -sgen 1476264175 This is very useful to re-test existing SQL. When you have several ten's of binds, it becomes a real pain. This script, fetch the binds values and type from v$sql_bind_capture. Works also from the AWR using 'aw -sgen -b <n>' where -b will let you pick the period.
* * *
Date :2010-12-29 14:59:15
* * *
variable :B21 Varchar2(4000)
exec :B21:='96a524c8aa55d25eca99eb65c9fb86b8';
variable :B12 varchar2(30)
exec :B12:=_;_
SELECT BLACKOUT_GUID, START_TIME, END_TIME, STATUS FROM MGMT_BLACKOUT_WINDOWS WHERE TARGET_GUID=:B2 AND START_TIME
SQL: 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
List tables columns used as predicates
opt/scripts/smenu> ** tbl -t viewrule -pred**
non
Equality equijoin equijoin Range Like Null
Table name Column Preds Preds Preds Pred Preds Preds Date
* * *
-------------------------- -------- -------- -------- ------- ------- ------- -----------------
IBS6_EB_OWNER.VIEWRULE VIEWERPROFILE_ID 144 457 0 0 0 0 17-12-10 10:47:32
IBS6_EB_OWNER.VIEWRULE BANKACCOUNT_ID 123 75 0 0 0 120 17-12-10 07:39:30
IBS6_EB_OWNER.VIEWRULE VIEWRULE_ID 30 67 0 0 0 0 17-12-10 07:39:30
IBS6_EB_OWNER.VIEWRULE BANKCUSTOMER_ID 35 3 0 0 0 0 16-12-10 13:47:55
View query that uses a given table column as predicate
opt/scripts/smenu> tbl -t viewrule -pred -col VIEWRULE_ID
non
Equality equijoin equijoin Range Like Null
Table name Column Preds Preds Preds Pred Preds Preds Date
* * *
-------------------------- -------- -------- -------- ------- ------- ------- -----------------
IBS6_EB_OWNER.VIEWRULE VIEWRULE_ID 30 67 0 0 0 0 17-12-10 07:39:30
Plan hash Ch Oper Plan Estimed Avg seen Acc Fil Oth
value Id SQL_ID ld Cost cost card card Execution ess ter er Operation
* * *
---- ------------- --- ------- ------- ---------- ---------- --------- --- --- --- ------------------------------
2566212103 13 4nrwy1mvbu0pf 0 14 48 9832 3 N Y Y TABLE ACCESS FULL
3808009148 1 4vwujzjmw5npy 0 14 14 9888 0 N N Y TABLE ACCESS FULL
2566212103 13 9nypz73870xcr 0 14 48 9832 11 N Y Y TABLE ACCESS FULL
13 1 14 48 9832 5 N Y Y TABLE ACCESS FULL
450446611 1 b5ymc765nc7rw 0 2 2 2 1 N N Y TABLE ACCESS BY INDEX ROWID
299958797 2 f1ss0xgynu4up 0 2 2 1 3 N N Y TABLE ACCESS BY INDEX ROWID
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 events profile over a period of time
/opt/scripts/smenu/tmp> aw -ev -b 5082 -e 5200 -name "log file switch (checkpoint incomplete)"
Snap In
id st Wait class EVENT_NAME Tot waits TOTAL_TIMEOUTS Time Wait(s)
* * *
--- -------------- -------------------------------------------------------- ---------- -------------- --------------
5137 1 Configuration log file switch (checkpoint incomplete) 24 7 13.78
2 log file switch (checkpoint incomplete) 0 0 0.00
5136 1 Configuration log file switch (checkpoint incomplete) 0 0 0.00
2 log file switch (checkpoint incomplete) 0 0 0.00
5135 1 Configuration log file switch (checkpoint incomplete) 9 2 2.94
2 log file switch (checkpoint incomplete) 0 0 0.00
5134 1 Configuration log file switch (checkpoint incomplete) 25 7 10.78
2 log file switch (checkpoint incomplete) 0 0 0.00
5133 1 Configuration log file switch (checkpoint incomplete) 47 12 16.86
2 log file switch (checkpoint incomplete) 0 0 0.00
5132 1 Configuration log file switch (checkpoint incomplete) 53 17 24.24
2 log file switch (checkpoint incomplete) 4 0 0.12
5131 1 Configuration log file switch (checkpoint incomplete) 32 3 10.66
2 log file switch (checkpoint incomplete) 112 18 35.99
5130 1 Configuration log file switch (checkpoint incomplete) 1 0 0.06
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
