Smenu Wiki
Brought to you by:
bpolarsk,
ptimmermans
Menu
▾
▴
Load smenu shortcuts
view all Shortcuts
view all shortcuts starting by 's'
Edit source of shortcut 'frg'
Show me discussion about ...
View the help of a shortcut
RMAN: list backup jobs
RMAN: list content of a backup piece (datafile)
RMAN: list content of a backup piece (archive)
RMAN: List backups
List dependent objects
Statistics: Create a frequency histogram for the 254 more skewed values
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
SQL: 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
SQL: 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
SQL: List Top sql sampled 3 seconds, sort by elapsed
SQL: 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
AWR: List events profile over a period of time
AWR: List Session activity, locking and events for a past period
AWR: Past session events using AWR data imported from another DB
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
Smenu commands
Load smenu shortcuts
/home/oracle/smenu]> ls -l ad lrwxr-xr-x 1 oracle dba 19 Dec 23 16:07 ad -> ./scripts/addpar.sh [vblinux001:DEV:/home/oracle/smenu]> . ./ad
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 | #!/usr/bin/ksh # HOST=`hostname` HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1) }'` SBIN2=${SBIN}/module2 WK_SBIN=${SBIN}/module2/s5 TMP=$SBIN/tmp FOUT=$TMP/free_space_summary_$ORACLE_SID.txt function help { cat <tablespace name> ] frg -os -t <tablespace name> -rn <nn> -u <OWNER> frg -i frg -dus <user> # If <user> is not given then all schema are processed frg -cr [<tablespace name>] [-q] # Generate statement to create tablespace, if no tbs name, # then create statement for all. -b : figures in bytes -dus : Disk usage per user per tablespace -g : figures in Giga bytes -i : List metadata info on tablespaces -rn : Limite selection to <nn> rows -q : add user quota on tbs -os : List Object size -t : limit to this tablespace, it accpet partial name -h : this help . . . |
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
Try any word or part of word as argument.
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.
View the help of a shortcut
All shorctus have a -h options, but most will display the help if the shorctut is called without argument:
/home/oracle/smenu]> tbl
tbl -u <owner> [-ord <n>]
tbl -u <owner> -t <table_name> tbl -ext
tbl -u <owner> -t <table_name> -ldl tbl -u <owner> -ext <table_name> -drop
tbl -u <owner> -t <table_name> -p -d tbl -u <owner> -t <table_name> -ddl # get the ddl
tbl -u <owner> -t <table_name> -c -d tbl -u <owner> -t <table_name> -pred # list table predicate usage
tbl -u <owner> -t <table_name> -a -d -i tbl -u <owner> -cd <CONSTRAINT TYPE : ie 'P'> -x
tbl -u <owner> -t <table_name> -s tbl -u <owner> -log|-logc | -logu
tbl -u <owner> -t <table_name> -l tbl -t <table_name> -lbs <lob column_name>
tbl -u <owner> -cl <CONSTRAINT TYPE : ie 'P'> tbl -u <owner> -t <table_name> -noidxfk
tbl -u <owner> -ce <CONSTRAINT TYPE : ie 'P'> -x tbl -n <table_name> (Add a % before, after or at both end) to the part name
tbl -txn -t <table_name> -u <nn> ] tbl -t <table_name> -spc|-uspc -part <PARTITION_NAME> [-u <OWNER>]
tbl -g -u <OWNER> -t <table_name> tbl [-u <owner>] -t <table_name> -col <COLUMN_NAME>
tbl -dep -t <table_name> -u <tbs> -t
-u <owner> [-nitbs <tbs>]
add to -t :
==============
-u limit to owner -a Chained rows
-c Constraints -d Table description
-i additional info on table -lob show lobs info
-p (sub)partitions -s List stats info gathered on columns
-ldl display last ddl applied on the table -ddl extract ddl of table
-drop drop external table -lbs List lob size distrubtion
-spc List space map using dbms_space.space_usage -uspc List unused space using dbms_space.unused_space
-dep List all dependent segments -txn List transaction available in flash.
-pred show predicate usage; -ord <n> # values are 1 to 7 and represent columns
-g List table columns with histogram on them -col <COLUMN_NAME> List histograms on COLUMN_NAME
-cl List schema Constraints. Constraint type is the letter representing the type of the constraint. type 'ALL' to see all constraintstype
-cd Generate script to Disable schema|table Constraints. Constraint type is the letter representing the type of the constraint.
type 'ALL' affect all constraintstype. If you omit table and leave only schema, then all constraints are taken in account
-ce Generate script to Enable schema|table Constraints. Constraint type is the letter representing
the type of the constraint. type 'ALL' affect all constraintstype. If you omit table and leave
only schema, then all constraint are taken in account
-truncate Generate script to truncate all tables in a given schema. '-x' option will not execute this.
-ext List all externamal tables; drop it if -t
-drop is added
-noidxfk List Foreign key without index and the Parent table name and columns
-ntbs Move table to new tbs <tablespace_name>. with -mi move also the indexes
-nitbs In conjunction with '-ntbs', move also the indexes to new tablespace
-n List all tables whose name is like %xxx%. You need to provide the % yourself : ie: tlb -u soe -n MV_%
-log Supplemental log groups -logc supplemental column -logu supplemental column in table that without FK, PK or BITMAP
Also you can use : 'idx -t <table_name>' to list associated indexes. It is intentional that -drop only access external table.
If you want to drop a table, connect into db. The time it takes gives you a chance to realize what you do.
Example:
tbl -t EMP -u SCOTT -s # List all colulns statistics
tbl -t EMP -u SCOTT -p # List all partitions
tbl -t EMP -u SCOTT -g # display all colums of table EMP with histogram on them
tbl -t EMP -u SCOTT -col DEPNO # display the histogram on column DEPNO
tbl -t EMP -u SCOTT -ce ALL # generate script to enable all constraints
tbl -t EMP -u SCOTT -pred # list column when they used as predicated and the type of predicate
tbl -t EMP -u SCOTT -col EMPNO -pred # list queries that use the column as predicate (from v$sql_plan)
RMAN: list backup jobs
/home/oracle/smenu> lsbk -jb
[loadtest08:IBP:/home/oracle]> lsbk -jb Sess Sess Key Recid INPUT_TYPE IN_SIZE OUT_SIZE START_AT END_AT STATUS * * * ------- ------------- ---------- ---------- ------------------------ ------------------------ ----------------------- 56454 56454 DB FULL 284980 79951 19-JAN-11 18:30 19-JAN-11 22:06 FAILED 56451 56451 ARCHIVELOG 149 83 19-JAN-11 17:50 19-JAN-11 17:50 COMPLETED 56448 56448 ARCHIVELOG 117 71 19-JAN-11 17:40 19-JAN-11 17:40 COMPLETED 56445 56445 ARCHIVELOG 314 140 19-JAN-11 17:30 19-JAN-11 17:30 COMPLETED 56442 56442 ARCHIVELOG 156 88 19-JAN-11 17:20 19-JAN-11 17:20 COMPLETED 56437 56437 ARCHIVELOG 122 69 19-JAN-11 17:10 19-JAN-11 17:10 COMPLETED 56434 56434 ARCHIVELOG 174 95 19-JAN-11 17:00 19-JAN-11 17:00 COMPLETED 56431 56431 ARCHIVELOG 238 116 19-JAN-11 16:50 19-JAN-11 16:50 COMPLETED 56428 56428 ARCHIVELOG 143 84 19-JAN-11 16:40 19-JAN-11 16:40 COMPLETED 56425 56425 ARCHIVELOG 237 118 19-JAN-11 16:30 19-JAN-11 16:30 COMPLETED 56422 56422 ARCHIVELOG 185 99 19-JAN-11 16:20 19-JAN-11 16:20 COMPLETED 56419 56419 ARCHIVELOG 161 91 19-JAN-11 16:10 19-JAN-11 16:10 COMPLETED 56416 56416 ARCHIVELOG 256 126 19-JAN-11 16:00 19-JAN-11 16:00 COMPLETED 56413 56413 ARCHIVELOG 640 242 19-JAN-11 15:50 19-JAN-11 15:50 COMPLETED 56410 56410 ARCHIVELOG 135 84 19-JAN-11 15:40 19-JAN-11 15:40 COMPLETED 56407 56407 ARCHIVELOG 212 112 19-JAN-11 15:30 19-JAN-11 15:30 COMPLETED 56404 56404 ARCHIVELOG 186 99 19-JAN-11 15:20 19-JAN-11 15:20 COMPLETED
RMAN: list content of a backup piece (datafile)
/home/oracle/smenu> lsbk -f 53166
Backuped File
KEY SEQ# size(m) size(m) STATUS NAME
* * *
---------- ---------- ---------- ----------- ------------------------------------------------------------------------------------------
53166 8 215.6 300 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_200802.382.742214885
53166 11 261.9 300 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_200805.384.742215097
53166 13 471 500 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_200807.381.742214815
53166 15 731.7 800 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_200809.378.742214147
53166 17 999.4 1100 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_200811.377.742213819
53166 22 2539.8 2600 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_200904.367.742211337
53166 27 4180.6 4200 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_200909.363.742211335
53166 29 5132.3 5200 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_200911.359.742211329
53166 35 8397.6 8500 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_201005.353.742211315
53166 36 11355.8 11500 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_201006.349.742211307
53166 47 2192.2 2300 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_eb_admin_data_1m.371.742211347
53166 52 32750.1 32768 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_eb_bf_data_32m.344.742211287
53166 53 6300.6 6500 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_eb_bf_index_32m.357.742211321
53166 54 16.7 100 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_eb_meta_data_64k.391.742215283
53166 55 13 100 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_eb_meta_index_64k.392.742215307
53166 63 .2 2048 Available +ASMLTDISKGROUP1/ibp/datafile/ibs6_accinfo_data_201102.376.742213713
RMAN: list content of a backup piece (archive)
/home/oracle/smenu> lsbk -f 53171
Backuped File KEY SEQ# size(m) size(m) STATUS NAME * * * ---------- ---------- ---------- ----------- ------------------------------------------------------------------------------------------ 53171 21964 17.1 43.1 Deleted Archive: Start 2011-01-19 20:08:10 SCN 3391149698 --> 3391179820 53171 21965 17.1 43.1 Deleted Archive: Start 2011-01-19 20:10:15 SCN 3391179820 --> 3391236692 53171 21966 17.1 43.1 Deleted Archive: Start 2011-01-19 20:15:08 SCN 3391236692 --> 3391279165 53171 21967 17.1 43.1 Deleted Archive: Start 2011-01-19 20:19:54 SCN 3391279165 --> 3391325877 53171 21968 17.3 43.6 Deleted Archive: Start 2011-01-19 20:24:05 SCN 3391325877 --> 3391403534 53171 21969 17.1 43.1 Deleted Archive: Start 2011-01-19 20:30:42 SCN 3391403534 --> 3391480890 53171 21970 17.1 43.1 Deleted Archive: Start 2011-01-19 20:38:04 SCN 3391480890 --> 3391558724 53171 21971 17.1 43.1 Deleted Archive: Start 2011-01-19 20:46:39 SCN 3391558724 --> 3391657577 53171 21972 17.1 43.1 Deleted Archive: Start 2011-01-19 20:58:02 SCN 3391657577 --> 3391737399 53171 21973 17.1 43.1 Deleted Archive: Start 2011-01-19 21:06:37 SCN 3391737399 --> 3391827261 53171 21974 17.1 43.1 Deleted Archive: Start 2011-01-19 21:19:08 SCN 3391827261 --> 3391877170 53171 21975 17.1 43.1 Deleted Archive: Start 2011-01-19 21:34:43 SCN 3391877170 --> 3391916124 53171 21976 10 25.4 Deleted Archive: Start 2011-01-19 21:48:49 SCN 3391916124 --> 3392104324
RMAN: List backups
/home/oracle/smenu> lsbk -lp
[loadtest08:IBP:/home/oracle]> lsbk -lp MACHINE loadtest08 - ORACLE_SID: IBP Date - Friday 04th February 2011 17:33:05 Username - SYS (help: lsbk -h) Available backup pieces contained in the control file. Use lsbk -f <BP_PIECE> to view content of the piece BS Pce Cpy BP Com Start End Elapsed Key # # Key Status Handle Time Time Seconds size(m) * * * ---- ---- ------ --------- --- ----------------------------------------------------------------- -------------- -------------- -------- -------- 53171 1 1 53171 Available YES /PDM/IBP/database/backup_IBP_3um2hl0d_1_1 01-19 22:07:10 01-19 22:08:14 64 215.0 53170 1 1 53170 Available YES /PDM/IBP/database/backup_IBP_3tm2hl0d_1_1 01-19 22:07:10 01-19 22:08:04 54 225.6 53169 1 1 53169 Available YES /PDM/IBP/database/backup_IBP_40m2hl0d_1_1 01-19 22:07:11 01-19 22:08:00 49 195.5 53168 1 1 53168 Available YES /PDM/IBP/database/backup_IBP_3vm2hl0d_1_1 01-19 22:07:11 01-19 22:08:00 49 195.9 53167 1 1 53167 Available NO /PDM/IBP/database/cf_auto_IBP_c-810902110-20110119-6c 01-19 22:06:44 01-19 22:06:44 0 40.0 53166 1 1 53166 Available YES /PDM/IBP/database/backup_IBP_3om2h89g_1_1 01-19 18:30:08 01-19 22:06:41 12,993 30033.5 53165 1 1 53165 Available YES /PDM/IBP/database/backup_IBP_3rm2h89g_1_1 01-19 18:30:09 01-19 21:45:07 11,698 28013.7 53164 1 1 53164 Available YES /PDM/IBP/database/backup_IBP_3qm2h89g_1_1 01-19 18:30:08 01-19 19:26:00 3,352 12007.0 53163 1 1 53163 Available YES /PDM/IBP/database/backup_IBP_3pm2h89g_1_1 01-19 18:30:08 01-19 19:13:14 2,586 9857.7 53162 1 1 53162 Expired NO /PDM/IBP/database/cf_auto_IBP_c-810902110-20110119-6b 01-19 17:50:23 01-19 17:50:23 0 40.0 53161 1 1 53161 Expired YES /PDM/IBP/database/backup_IBP_3lm2h5um_1_1 01-19 17:50:15 01-19 17:50:19 4 15.3 53160 1 1 53160 Expired YES /PDM/IBP/database/backup_IBP_3km2h5um_1_1 01-19 17:50:15 01-19 17:50:19 4 16.4 53159 1 1 53159 Expired YES /PDM/IBP/database/backup_IBP_3mm2h5um_1_1 01-19 17:50:15 01-19 17:50:18 3 11.2 53158 1 1 53158 Expired NO /PDM/IBP/database/cf_auto_IBP_c-810902110-20110119-6a 01-19 17:40:23 01-19 17:40:23 0 40.0 53157 1 1 53157 Expired YES /PDM/IBP/database/backup_IBP_3hm2h5bu_1_1 01-19 17:40:15 01-19 17:40:19 4 17.1 53156 1 1 53156 Expired YES /PDM/IBP/database/backup_IBP_3im2h5bu_1_1 01-19 17:40:15 01-19 17:40:19 4 14.2 53155 1 1 53155 Expired NO /PDM/IBP/database/cf_auto_IBP_c-810902110-20110119-69 01-19 17:30:19 01-19 17:30:20 1 40.0 53154 1 1 53154 Expired YES /PDM/IBP/database/backup_IBP_3cm2h4ov_1_1 01-19 17:30:08 01-19 17:30:18 10 34.3 53153 1 1 53153 Expired YES /PDM/IBP/database/backup_IBP_3em2h4ov_1_1 01-19 17:30:08 01-19 17:30:15 7 30.8 53152 1 1 53152 Expired YES /PDM/IBP/database/backup_IBP_3dm2h4ov_1_1 01-19 17:30:08 01-19 17:30:15 7 31.2 53151 1 1 53151 Expired YES /PDM/IBP/database/backup_IBP_3fm2h4ov_1_1 01-19 17:30:11 01-19 17:30:12 1 3.7 53150 1 1 53150 Expired NO /PDM/IBP/database/cf_auto_IBP_c-810902110-20110119-68 01-19 17:20:21 01-19 17:20:22 1 40.0 53149 1 1 53149 Expired YES /PDM/IBP/database/backup_IBP_37m2h46e_1_1 01-19 17:20:15 01-19 17:20:20 5 17.8 53148 1 1 53148 Expired YES /PDM/IBP/database/backup_IBP_38m2h46e_1_1 01-19 17:20:15 01-19 17:20:19 4 16.7 53147 1 1 53147 Expired YES /PDM/IBP/database/backup_IBP_39m2h46e_1_1 01-19 17:20:15 01-19 17:20:18 3 11.1 53146 1 1 53146 Expired YES /PDM/IBP/database/backup_IBP_3am2h46e_1_1 01-19 17:20:15 01-19 17:20:16 1 3.1 53145 1 1 53145 Expired NO /PDM/IBP/database/cf_auto_IBP_c-810902110-20110119-67 01-19 17:10:23 01-19 17:10:23 0 40.0 53144 1 1 53144 Expired YES /PDM/IBP/database/backup_IBP_34m2h3jm_1_1 01-19 17:10:15 01-19 17:10:20 5 16.3 53143 1 1 53143 Expired YES /PDM/IBP/database/backup_IBP_35m2h3jm_1_1 01-19 17:10:15 01-19 17:10:19 4 13.1 53142 1 1 53142 Expired NO /PDM/IBP/database/cf_auto_IBP_c-810902110-20110119-66 01-19 17:00:24 01-19 17:00:24 0 40.0
List dependent objects
/home/oracle/smenu> dep ZU -u APPLICATION_M
MACHINE bpadev - ORACLE_SID : DEV102 Page: 1
Date - Monday 24th January 2011 13:04:17
Username - SYS Objects tree that depends on ZU
Name TYPE OWNER constraint LAST_MODIFIED STATUS
* * *
------------------- -------------------- ------------------------------ ------------------- -------
ZU2VALIDATE TABLE APPLICATION_M ZU2VALIDATE_ZU_FK 2010-04-19 11:11:57 VALID
ZUDDHASH TABLE APPLICATION_M ZUDDHASH_ZU_FK1 2010-04-19 11:11:56 VALID
ZUACL_BITCODE TABLE APPLICATION_M ZUACL_ZU_FR 2010-04-19 11:11:50 VALID
ZUPROCESSOR TABLE APPLICATION_M ZUPROCESSOR2ZU_FK 2011-01-20 06:40:25 VALID
ZU_INVALIDATECACHE TRIGGER APPLICATION_M 2011-01-20 06:40:26 VALID
INT_UPLOAD_MONITOR TRIGGER APPLICATION_M 2011-01-05 14:01:17 INVALID
UPD_ZU_BU TRIGGER APPLICATION_M 2011-01-20 06:40:25 VALID
RELEASED PACKAGE BODY APPLICATION_M 2011-01-05 14:01:15 VALID
ZU_STATE_PKG PACKAGE APPLICATION_M 2011-01-05 14:01:12 VALID
CUSTACCTLVL_BEFORE TRIGGER APPLICATION_M 2011-01-20 06:40:25 VALID
CUSTACCTLVL_BEFORE_UPDATE TRIGGER APPLICATION_M 2011-01-20 06:40:25 VALID
CLONEPKG PACKAGE APPLICATION_M 2011-01-05 14:01:12 VALID
CLONEPKG SYNONYM APPLICATION 2010-09-03 16:12:08 INVALID
CLONEPKG PACKAGE BODY APPLICATION_M 2011-01-05 14:01:15 VALID
HOOKSELECTION PACKAGE APPLICATION_M 2011-01-05 14:01:13 VALID
HOOKSELECTION PACKAGE BODY APPLICATION_M 2011-01-05 14:01:15 VALID
CUSTCURRENCYLVL TABLE APPLICATION_M CUSTCURRENCYLVL_ZU 2011-01-20 06:40:23 VALID
CUSTARCHIACTIONLVLPARAMS TABLE APPLICATION_M CUSTARCHIACTIPARAMS__FK1 2010-04-19 11:11:56 VALID
CUSTARCHIACTIONLVL TABLE APPLICATION_M SMMTRNSCTNLVL_SMMCCTLVL 2011-01-20 06:40:23 VALID
Statistics: Create a frequency histogram for the 254 more skewed values
this routine automate the creation of frequency histogram limited to the 254 more skewed columns. Within the boundaries of the histogram, all value were the one given either by the histogram or by the density. Outside, will be 1
/home/oracle/smenu> tbl -t USERMESSAGE -g -col USERIDENTITY_ID -v
declare
srec dbms_stats.statrec;
a_bucket dbms_stats.numarray;
v_tot_rows_not_in_freq number;
v_tot number;
v_density number;
v_distinct_key_not_in_freq number;
v_cutoff number :=150 -- set here your variable
begin
-- create the histogram, maximum 254 buckets but may be lower following v_cutoff_value
select cpt, USERIDENTITY_ID bulk collect into srec.bkvals, a_bucket from (
select
cpt, USERIDENTITY_ID
from ( select count(*) cpt, USERIDENTITY_ID
from IBS6_EB_OWNER.USERMESSAGE
group by USERIDENTITY_ID order by count(*) desc
)
where rownum
Produces this histogram:
TABLE_NAME COLUMN_NAME Bck nbr ENDPOINT_VALUE Frequency
* * *
--------------- ---------- -------------- ----------
USERMESSAGE USERIDENTITY_ID 303 11 303
1990 12 1687
2443 19 453
5165 21 2722
5417 24 252
5945 27 528
6105 33 160
6381 85 276
23950 148 17569
Expected cardinality is very close. I wonder why it modified the 17569 to 17792
COUNT(*)
* * *
33
SQL_ID 81dtb814kvvy9, child number 0
* * *
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM usermessage um, useridentity ui ,
usermessagetype umt WHERE um.isconsumed = 0 AND ui.useridentity_id = um.useridentity_id AND
umt.usermessagetype_id = um.usermessagetype_id AND ui.useridentity_id = 149
Plan hash value: 3616009165
* * *
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
* * *
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 231 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 231 |
| 2 | NESTED LOOPS | | 1 | 180 | 33 |00:00:00.01 | 231 |
|* 3 | INDEX UNIQUE SCAN | USERIDENTITY_PK | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS BY INDEX ROWID| USERMESSAGE | 1 | 180 | 33 |00:00:00.01 | 229 |
|* 5 | INDEX RANGE SCAN | USERMESSAGE_IDX | 1 | 426 | 421 |00:00:00.01 | 5 |
* * *
Predicate Information (identified by operation id):
* * *
3 - access("UI"."USERIDENTITY_ID"=149)
4 - filter("UM"."ISCONSUMED"=0)
5 - access("UM"."USERIDENTITY_ID"=149)
Not bad
COUNT(*)
* * *
9
SQL_ID 0z8t787a3t8an, child number 0
* * *
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM usermessage um, useridentity ui ,
usermessagetype umt WHERE um.isconsumed = 0 AND ui.useridentity_id = um.useridentity_id AND
umt.usermessagetype_id = um.usermessagetype_id AND ui.useridentity_id = 169
Plan hash value: 3616009165
* * *
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
* * *
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 11 |
| 2 | NESTED LOOPS | | 1 | 10 | 9 |00:00:00.01 | 11 |
|* 3 | INDEX UNIQUE SCAN | USERIDENTITY_PK | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS BY INDEX ROWID| USERMESSAGE | 1 | 10 | 9 |00:00:00.01 | 9 |
|* 5 | INDEX RANGE SCAN | USERMESSAGE_IDX | 1 | 23 | 9 |00:00:00.01 | 3 |
* * *
23 rows this is the default density : 5673/250 = 22.692 round up to 23.
SQL: Show me sql actives the last 'n' seconds
/home/scripts/smenu> sq -m 4
MACHINE vblinux001 - ORACLE_SID : DEV
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
/home/scripts/smenu> st -f 2329266020
Basic logic but works for 99% of SELECT. I recommend to pay a look to the source 'vsh st'
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
SQL: 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 CUSTOMER -pred
non
Equality equijoin equijoin Range Like Null
Table name Column Preds Preds Preds Pred Preds Preds Date
* * *
-------------------------- -------- -------- -------- ------- ------- ------- -----------------
REPL_CA_OWNER.CUSTOMER VIEWERPROFILE_ID 144 457 0 0 0 0 17-12-10 10:47:32
REPL_CA_OWNER.CUSTOMER ARCHACCOUNT_ID 123 75 0 0 0 120 17-12-10 07:39:30
REPL_CA_OWNER.CUSTOMER CUSTOMER_ID 30 67 0 0 0 0 17-12-10 07:39:30
REPL_CA_OWNER.CUSTOMER ARCHCUSTOMER_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 CUSTOMER -pred -col CUSTOMER_ID
non
Equality equijoin equijoin Range Like Null
Table name Column Preds Preds Preds Pred Preds Preds Date
* * *
-------------------------- -------- -------- -------- ------- ------- ------- -----------------
REPL_CA_OWNER.CUSTOMER CUSTOMER_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
SQL: Show plan present in v$sql_plan sorted by cost
/opt/scripts/smenu> sx -l -cost
MACHINE loadtest05 - ORACLE_SID : DEV 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
* * *
----------- ----------- ------------- --- ------ -------- ---------- ----- ------------------------------------------------------------
REPL_CA_PERF 1186469467 12-06/16:14 6t6zxa9xnchk2 1 37414 1 164240 1 SELECT ba.TB_id, b.systemfilename, bc.contractid FROM b
REPL_CA_USER 2167909657 12-03/11:38 b6zmf2cx39vw3 1 33607 73 11022136 4 select TB.TB_id, TB.sender, TB.ARCHcustomer_id, TB.upda
12-04/02:22 b6zmf2cx39vw3 0 33605 352 53147798 4 select TB.TB_id, TB.sender, TB.ARCHcustomer_id, TB.upda
12-07/02:26 b6zmf2cx39vw3 2 33605 40 6039532 4 select TB.TB_id, TB.sender, TB.ARCHcustomer_id, TB.upda
REPL_CA_PERF 2167909657 12-08/00:18 b6zmf2cx39vw3 3 33605 17 2566808 4 select TB.TB_id, TB.sender, TB.ARCHcustomer_id, TB.upda
WMSYS 370289257 11-29/23:15 fnya6frbtntjm 0 8274 7 255809 1 update /* WM$SQL(1194)(REPL_CA_OWNER.TBACL_BITCODE) */
1822140895 11-29/23:15 8y07kfdwu3a6q 0 8260 7 257813 1 update /* WM$SQL(1206)(REPL_CA_OWNER.TBACL_BITCODE) */
1623947852 11-29/23:15 70m6fdav2dtmp 0 4596 7 116071 1 update /* WM$SQL(1194)(REPL_CA_OWNER.SUMMCURRENCYLVL)
635047828 11-29/23:15 cppawjmf5mv8m 0 4591 7 115604 1 update /* WM$SQL(1206)(REPL_CA_OWNER.SUMMCURRENCYLVL)
1392049166 12-03/13:35 6svawdhxztd6y 0 4510 2 181 1 update /* WM$SQL(1206)(REPL_CA_OWNER.TB) */
3698432344 12-03/13:35 1nrpw4v6a7xzb 0 4509 2 177 1 update /* WM$SQL(1194)(REPL_CA_OWNER.TB) */
REPL_CA_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)(REPL_CA_OWNER.INITIATOREBUSERID
26703 11-29/23:15 71jhy6cb98f8u 0 3488 7 110173 1 update /* WM$SQL(1206)(REPL_CA_OWNER.INITIATOREBUSERID
4089586455 11-29/23:15 famrx5a74h2hq 0 2989 6 78230 1 update /* WM$SQL(1194)(REPL_CA_OWNER.TB2ARCHIVE) */
3475308645 11-29/23:15 7x18z3tm1xr7p 0 2983 6 78216 1 update /* WM$SQL(1206)(REPL_CA_OWNER.TB2ARCHIVE) */
REPL_CA_PERF 2502654689 12-07/14:25 d9r3jfj5n0nbm 0 2885 3 20454 1 select * from accountsgbyebuser where ARCHaccount_id in
12-07/14:30 fzg7uf7sqwaaw 0 2885 8 54514 1 select * from accountsgbyebuser where ARCHaccount_id in
662475514 11-30/23:19 fcq1v0s7uaj5c 0 2523 8 9088 1 SELECT bbb.beneficiaryARCHbranch_id, bbb.location, bbb.
2318155007 12-08/10:04 8b514yxf19h0c 1 2412 3 886 1 SELECT TB.TB_id, TB.status, bs.shortname, TB.userfilen
12-08/10:04 ctpktd10b14wj 1 2412 3 886 1 SELECT TB.TB_id, TB.status, bs.shortname, TB.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
SQL: List Top sql sampled 3 seconds, sort by elapsed
/opt/scripts/smenu> sq -top 3 -e
MACHINE loadtest05 - ORACLE_SID : DEV 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 * * * ----------- --- ------ -------- -------- -------- ------ ------ ------- ------ -------------------------------------------------- REPL_CA_PERF 4223820267 2 10 4921 0 0 57 0 0 0 select b.TB_id,b.status,b.releasestatus, b.sender, REPL_CA_USER 284895985 3 70 126 0 0 12 0 0 0 SELECT TBQ.TB_ID,TBQ.LASTUPDATE,TB.INITIATOR,TB.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
SQL: 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 REPL_CA_PERF
363898 12 12 363898 2091 53 0 1071 1047 29/22:49:35 3365955911 0 REPL_CA_PERF
289749 5 3 289749 604 53 0 880 866 30/22:16:18 3365955911 2 REPL_CA_PERF
602 1 0 37 78 106671 23 546 563 01/10:00:32 1039090739 1 REPL_CA_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 REPL_CA_PERF
115 5 4 57 17045651 151013 0 325 345 30/00:37:50 976547715 1 REPL_CA_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 REPL_CA_USER
87356 11 7 87356 1592 53 0 264 258 29/17:16:35 3365955911 1 REPL_CA_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 : DEV
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
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
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 TB(TB_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.ARCHaccount_id, ba.update
1584 20330 48 186pn4b8x25r7 -wait- enq: TM - contention 1 559 72929 INSERT INTO TB(TB_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.ARCHaccount_id, ba.update
AWR: 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 AWR 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 REPL_CA_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
.
.
AWR: List plan from AWR for a given period
/opt/scripts/smenu> aw -pl 2681598973 -b 29946
MACHINE loadtest05 - ORACLE_SID : DEV 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
* * *
----------- ------------ -------- ----------- --- ------------
DEV 383613666 DEV 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
* * *
----------- ------------ -------- ----------- --- ------------
DEV 383613666 DEV 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
* * *
----------- ------------ -------- ----------- --- ------------
DEV 383613666 DEV 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 DEV 1 10.2.0.5.0 NO loadtest05 Second (2nd) 383613666 DEV 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 : DEV 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 TB SET STATUS=:1 , CONTROLSTATUS=:2 , RELEASESTA b6zmf2cx39vw3 29.26 10 1478278 1509880 16.220974 select TB.TB_id, TB.sender, TB.ARCHcustomer_id, TB.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 TB2ARCHIVE (TB_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 REPL_CA_OWNER.TB_LT SET ISURGENT = :B73,UPLOADTO 2vgd86jzmcak3 12.52 29682 2 237916 .000073 DELETE FROM TB2SEND WHERE TB_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 TBQ.TB_ID,TBQ.LASTUPDATE,TB.INITIATOR,TB.STATUS, fnmrqzvxw4sgb 8.97 1910 3 940155 .282189 select b.TB_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 TB SET STATUS=:1 , CONTROLSTATUS=:2 , RELEASESTA b6zmf2cx39vw3 22.77 8 1181860 1207904 12.420478 select TB.TB_id, TB.sender, TB.ARCHcustomer_id, TB.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 TB2ARCHIVE (TB_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 REPL_CA_OWNER.TB_LT SET ISURGENT = :B73,UPLOADTO 2vgd86jzmcak3 9.22 22114 0 177275 0 DELETE FROM TB2SEND WHERE TB_ID = :B1 4g50ymc8gqarj 8.08 50576 0 50579 .029586 SELECT TBQ.TB_ID,TBQ.LASTUPDATE,TB.INITIATOR,TB.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 TB.TB_id, TB.sender, TB.ARCHcustomer_id, TB.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 ARCHaccount set ARCHservice_id = :1 , countryiso 48a932sgdj441 0.32 0.27 0.27 0.00 0.00 0.00 SELECT anr.ARCHaccount_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.TB_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 TB 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 TB2ARCHIVE (TB_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 REPL_CA_OWNER.TB_LT SET ISURGENT = :B73,UPLOADTO 2vgd86jzmcak3 0.69 1562 0 12528 0 DELETE FROM TB2SEND WHERE TB_ID = :B1