/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
/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
/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
/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 . . . |
'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.
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)
/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
/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
/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
/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
/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
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.
/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:
/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
/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
/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
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
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
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
/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
/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
/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
/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
/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
/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
/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
/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
/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
/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
/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
/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
/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
/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 . .
/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
/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) . . .
/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 . . .
/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
/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
/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
/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
/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
/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) ;
/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
/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
/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,
/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
/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
/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
/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#=:
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