Frequently Used SQLs and Shell Commands (Most SQLs below assume linesize 132 pagesize 100 or more for best display) (Shell commands are in APPENDIX) --Current session waits (9i and older) col event for a30 select sid, seq#, event, p1, p2, p3, wait_time, seconds_in_wait, state from v$session_wait where not (event like '%message%' or event like '%SQL*Net%' or event like '%timer%' or event like 'jobq%'); --10g provides wait_class through which you can exclude idle waits, and you can get other info in one view select sid, seq#, event, p1, p2, p3, wait_time, seconds_in_wait, state, username, program, sql_id, prev_sql_id from v$session where wait_class != 'Idle'; --Currently executing SQLs; may append "and buffer_gets > xxx" select * from v$sql where users_executing > 0 and sql_text not like 'select * from v$sql where users_executing%'; --Actual SQL plan used (10g, especially 10gR2): --There're a lot of useful parameters you can pass to the 3rd arg of display_cursor; see --http://optimizermagic.blogspot.com/2008/02/displaying-and-reading-execution-plans.html select * from table(dbms_xplan.display_cursor('&sql_id', null)); --Actual SQL plan used (9i): --Can add other_tag to select list in case of parallel SQL --Child_number is needed or different lines in diff child cursors would mix --Don't select * from v$sql_plan in 9i. Selecting certain columns may cause ORA-600 [504] [row cache objects]. --Filter_predicates column should be avoided (see Bug:3545517). That column could also cause ORA-7445 (Doc:376923.995) --and ORA-3113 (Bug:4035880). Access_predicates column may also be bad (inferred from Note:340090.1) --Don't forget "order by id". SQL trace of select * from table(dbms_xplan.display) reveals that it's a query with --order siblings by id (siblings because that's a hierarchical query). --Don't forget child_number. Without it, lines of different child cursors would be jumbled together. set linesize 132 col object_name for a30 col other_tag for a30 col operation for a45 col other_tag for a10 var hv number exec :hv := &hv select child_number, lpad(' ',2*depth)||operation||' '||options operation, object_name, cost, bytes, io_cost from v$sql_plan where hash_value = :hv order by child_number, id; --Undocumented parameter col ksppinm for a45 col ksppdesc for a65 col "SessVal" for a20 col "InstVal" for a20 select a.ksppinm, a.ksppdesc, b.ksppstvl "SessVal", c.ksppstvl "InstVal" from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm like '%&nm%' order by 1; --10g. Last 1 day's statistic value change select a.instance_number, begin_interval_time, value from dba_hist_snapshot a, dba_hist_sysstat b where a.snap_id=b.snap_id and a.instance_number=b.instance_number and stat_name='&statname' and begin_interval_time>sysdate-1 order by a.instance_number, a.snap_id; --10g. SQLs run in last 1 minute (exclude SYS and DBSNMP users) select hash_value, username, sql_text from gv$sql a, dba_users b where a.parsing_user_id = b.user_id and inst_id = 1 and last_active_time > sysdate - 1/1440 and parsing_user_id not in (0,24) order by 2; --Statspack (change stats$sysstat, time range and stat name as needed) conn perfstat alter session set nls_date_format = 'yyyymmdd hh24:mi'; select a.snap_id, snap_time, value, value - lag(value) over(order by a.snap_id) diff from stats$sysstat a, stats$snapshot b where snap_time between '20060201 09:00' and '20060201 18:00' and a.snap_id = b.snap_id and name = 'branch node splits' -- and mod(a.snap_id,3) = 0 --every 3 snap_id's: order by 1; --Delta Sesstat: see what statistics have changed for a session, useful in research --(change 1234 to the sid you're monitoring) create table stat (seq int, stat number, val number); define sid=1234 set verify off insert into stat select 1, statistic#, value from v$sesstat where sid = &&sid; --Let session &sid do some work or you do some work in it insert into stat select 2, statistic#, value from v$sesstat where sid = &&sid; select name, oldval, newval, diff --,decode(oldval,0,'***',trunc(diff/oldval*100,2)) pct_chng from ( select stat, lag(val) over (partition by stat order by seq) oldval, val newval, val - lag(val) over (partition by stat order by seq) diff from stat ) t, v$statname n where t.diff > 0 and t.stat = n.statistic# order by name; ------ equivalently below ------ select name, oldval, newval, newval-oldval diff --,decode(oldval,0,'***',trunc((newval-oldval)/oldval*100,2)) pct_chng from ( select stat, lag(val) over (partition by stat order by seq) oldval, val newval from stat ) t, v$statname n where t.newval > t.oldval and t.stat = n.statistic# order by name; --Minimalist graph of stats or wait events (change 100000 and graph column width as needed) col begin_interval_time for a30 col graph for a60 select begin_interval_time, value, rpad(' ',value/100000,'*') graph from dba_hist_sysstat a, dba_hist_snapshot b where a.snap_id = b.snap_id and stat_name = 'CPU used by this session' and begin_interval_time between to_date('20081101 00:00', 'yyyymmdd hh24:mi') and to_date('20081201 00:00', 'yyyymmdd hh24:mi') order by 1; select begin_interval_time, total_waits, rpad(' ',total_waits/100000,'*') graph from dba_hist_system_event a, dba_hist_snapshot b where a.snap_id = b.snap_id and event_name = 'db file scattered read' and begin_interval_time between to_date('20081101 00:00', 'yyyymmdd hh24:mi') and to_date('20081201 00:00', 'yyyymmdd hh24:mi') order by 1; --Find procedure or function name inside a package /* Replace my substitution variables as follows: &1: your package owner &2: package name &3: lineno in the package (or from owa_util.who_called_me) Ref: http://www.freelists.org/post/oracle-l/logging-the-input-values-the-procedurefunction-is-called-with,7 */ with x as (select line, nvl(lead(line)over(order by line),99999) nextline, substr(text, 1, 80) text from dba_source where owner = '&1' and name = '&2' and type = 'PACKAGE BODY' and regexp_like(text,'^ *(PROCEDURE|FUNCTION) ','i') order by 1) select line, text from x where &3 between line and nextline; APPENDIX: Frequently Used Shell Commands For all Perl commands, if run on Windows, change ' to " and add to %path% %oracle_home%\perl\5.8.3\bin\MSWin32-x86-multi-thread (change 5.8.3 as needed) #Show only the SQL statements in SQL trace (10046 trace): grep -A1 ^PARSING tracefile | egrep -v '^PARSING|^--' perl -nle 'print $_=<> if /^PARSING/' tracefile #change -nle to -ne to remove blank lines between SQLs perl -nle 'undef $/; while(/^PARSING[^\n]+\n(.*?)^END/msg) {print $1}' tracefile sed -n -e ' > /^RPC CALL/p > /^PARSING/{ > n > p > }' tracefile #Adding /^RPC CALL/ is needed because some SQLs are below that line, e.g.: select name, dbid from v$database select nvl(max(cpmid),0) from x$kcccp where cpsta = 2 select distinct my.sid, sex.serial from v$mystat my, x$ksusex sex where sex.sid = my.sid alter session set remote_dependencies_mode = signature RPC CALL:FUNCTION SYS.DBMS_BACKUP_RESTORE.DEVICEALLOCATE(TYPE IN VARCHAR2, NAME IN VARCHAR2, IDENT IN VARCHAR2, NOIO IN BOOLEAN, PARAMS IN VARCHAR2, NODE OUT ... #Also see http://oraclue.wordpress.com/2008/10/09/script-to-extract-sql-code-from-trace-file-trimsqlsh/ #Change pagesize and linesize in glogin.sql after default installation (assume PAGESIZE 14 by default) perl -pi -e 's/14/200 linesize 132/' $ORACLE_HOME/sqlplus/admin/glogin.sql #Summarize listener connections by client address (assume lines like '(HOST=appserver)(PORT=...') perl -nle '/HOST=([^)]+)\)\(P/;print $1' listener.log | sort | uniq -c #vi command to join all lines into one with no space between joined lines. #Useful in making a runnable SQL after #select sql_text from v$sqltext_with_newlines where hash_value=&hv order by piece %j! (change % to line range) #Then you can wrap lines at commas with %s/,/,^M/g where ^M is Ctrl-V,Ctrl-M or Ctrl-Q,Ctrl-M #Wrap long lines at 80 char position without cutting words in half fmt -sw80 longlinesql.sql > readablesql.sql fold -s longlinesql.sql > readablesql.sql #Checking hidden chars in text file cat -vet mytext #Windows unix2dos: perl -e "undef $/; while(<>){$d=chr(13); $a=chr(10); s/$d/${d}$a/g; print}" tnsnames.ora > t.txt #Windows dos2unix: perl -e "undef $/; while(<>){$d=chr(13); $a=chr(10); s/${d}$a/$d/g; print}" tnsnames.ora > t.txt #Run simple SQL against many DBs #bash or any shell other than (t)csh: echo "select value from v\$parameter where name='processes';" | sqlplus -s system/xxx@DB1 echo "select value from v\$parameter where name='processes';" | sqlplus -s system/xxx@DB2 #(t)csh: echo select value from v\$parameter where name="'processes';" | sqlplus -s system/xxx@DB1 #Change putty Window title when using bash read a; PROMPT_COMMAND='echo -ne "\033]0;$a\007"' #or put in .bash_profile title(){ read a; PROMPT_COMMAND='echo -ne "\033]0;$a\007"' ;} #with no prompting PROMPT_COMMAND='echo -ne "\033]0;mydesiredtitletext\007"' #Change prompt color to remind of critical (e.g. production) server (drop "\u@" if always login as oracle) PS1='\[\e[7;31m\]\u@\h \W $ \[\e[m\]' #A replacement for RAC `crs_stat -t', showing full CRS resource name with its TARGET and STATE values on one line echo "Columns are: Name Target State Host" crs_stat | perl -ne 'chomp;if(/^NAME=(.*)/){print $1}elsif(/^TARGET=(.*)/){print "\t$1"}elsif(/^STATE=(.*)/){print "\t$1\n"}' #Lightweight instantaneous check of ORA- errors in alert.log (remove "-s 15" if not GNU tail) nohup tail -s 15 -f //bdump/alert_SID.log | perl -nle 'system("echo \"$_\" | mail -s Error_in_alert_SID.log yong321\@yahoo.com") if /^ORA-/' &