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 /<path>/bdump/alert_SID.log | perl -nle 'system("echo \"$_\" | mail -s Error_in_alert_SID.log yong321\@yahoo.com") if /^ORA-/' &