::::::::::::::
bind.sql
::::::::::::::
--
-- Author: Andrew Fraser
-- Date: 13 June 2008
-- Name: bind.sql
-- Purpose: displays code that apparently doesn't use bind variables
-- Notes:
--
select substr(sql_text,1,30), sum(executions), count(*)
from v$sql
group by substr(sql_text,1,30)
having count(*) > 50
order by 3
/
::::::::::::::
c8.sql
::::::::::::::
--
-- Author: Andrew Fraser
-- Date: 13 June 2008
-- Name: c8.sql
-- Purpose: displays the high read code in the SQL cache
-- Notes: Vary the threshold to suit
-- Statspack has this also (if it is set up to run). In 10g AWR has this and runs by default.
-- 9i and above can use elapsed_time
-- But 8i and below has to use either buffer_gets or disk_reads instead
-- With 10g, ?/rdbms/admin/sqltrpt should be used in preference to this script
--
set pages 9999
spool c.lst
select disk_reads, executions, disk_reads/greatest(executions,1) reads_per_exec, sql_text
from v$sql
where disk_reads > 100000
order by 3 desc
/
spool off
ed c.lst
::::::::::::::
c9.sql
::::::::::::::
--
-- Author: Andrew Fraser
-- Date: 13 June 2008
-- Name: c9.sql
-- Purpose: displays the high read code in the SQL cache
-- Notes: Statspack has this also (if it is set up to run). In 10g AWR has this and runs by default.
-- 9i and above can use elapsed_time
-- But 8i and below has to use either buffer_gets or disk_reads instead
-- With 10g, ?/rdbms/admin/sqltrpt should be used in preference to this script
--
set pages 9999
spool c.lst
select secs, executions, secs_per_exec, sql_text
from (
select elapsed_time/1000000 secs, executions, elapsed_time/1000000/greatest(executions,1) secs_per_exec, sql_text,
rank() over ( order by elapsed_time/1000000/greatest(executions,1) desc ) spe_rank
from v$sql )
where spe_rank <= 10
/
spool off
ed c.lst
::::::::::::::
demo1.sql
::::::::::::::
--
-- Author: Andrew Fraser
-- Date: 13 June 2008
-- Name: demo1.sql
-- Purpose: For training purposes, demonstrates that NOT IN doesn't cope with NULLs
-- Notes:
--
set echo on
create table t1 (c1 number) ;
create table t2 (c2 number) ;
insert into t1 values ( 1 ) ;
insert into t1 values ( 2 ) ;
insert into t2 values ( 1 ) ;
select * from t1 where c1 not in ( select c2 from t2 ) ;
select * from t1 where not exists ( select null from t2 where c1 = c2 ) ;
insert into t2 values ( null ) ;
select * from t1 where c1 not in ( select c2 from t2 ) ;
select * from t1 where not exists ( select null from t2 where c1 = c2 ) ;
select * from t1 where c1 not in ( select c2 from t2 where c2 is not null ) ;
drop table t1 ;
drop table t2 ;
set echo off
::::::::::::::
ind.sql
::::::::::::::
--
-- Author: Andrew Fraser
-- Date: 20 June 2008
-- Name: ind.sql
-- Purpose: Shows what columns are indexed
--
set pages 9999 lines 132
col column_position form 9 head Pos
col column_name form a30
col column_expression form a30
break on table_name on index_name
select c.table_name, c.index_name, c.column_position, c.column_name, e.column_expression
from dba_ind_columns c, dba_ind_expressions e
where c.index_name = e.index_name(+)
and c.index_owner = e.index_owner(+)
and c.table_name = e.table_name(+)
and c.column_position = e.column_position(+)
and c.table_name = 'MYTABLE'
and c.table_owner = 'MYOWNER'
order by 1,2,3
/
::::::::::::::
spid.sql
::::::::::::::
--
-- Author: Andrew Fraser
-- Date: 20 June 2008
-- Name: spid.sql
-- Purpose: Shows what sid is related to a unix shadow process id
--
set pages 9999 verify off
accept spid prompt "Unix Shadow Process ID: "
select s.sid
from v$session s, v$process p
where s.paddr=p.addr
and p.spid = &spid
/
::::::::::::::
sql.sql
::::::::::::::
--
-- Author: Andrew Fraser
-- Date: 13 June 2008
-- Name: sql.sql
-- Purpose: displays SQL and other info on sessions currently active or active within the last minute
-- Notes: "username is not null" excludes the background sessions
--
set pages 9999 lines 132
col username form a20
col osuser form a20
col program form a50
col minutes form 999
ALTER SESSION SET nls_date_format = 'Dy DD-Mon-YYYY HH24:MI:SS' ;
SELECT DISTINCT s.inst_id , s.sid , s.serial# , s.username , s.osuser , s.program || ' ' || s.module AS program
, s.status , ROUND ( s.last_call_et / 60 ) AS minutes , s.logon_time
, s.state , s.event , s.seconds_in_wait , s.sql_id , q.sql_text
FROM gv$session s
JOIN gv$sql q
ON q.inst_id = s.inst_id
AND q.sql_id = s.sql_id
WHERE ( s.status = 'ACTIVE' OR last_call_et < 60 ) -- active in last minute
AND s.username IS NOT NULL -- exclude background sessions
ORDER BY s.inst_id , s.sid
;
/* old versions before 10g need to include v$session_wait view:
select distinct
s.sid, s.username, s.osuser, s.program||' '||s.module program,
s.status, s.last_call_Et/60 minutes, s.logon_time, w.state, w.event, w.seconds_in_wait ,
q.sql_text
from v$session s, v$sql q, v$session_wait w
where ( s.status = 'ACTIVE' or last_call_et < 60 )
and s.sql_hash_value = q.hash_value(+)
and s.sql_address = q.address(+)
and s.sid = w.sid
and s.username is not null
order by s.sid ; */
::::::::::::::
sw.sql
::::::::::::::
--
-- Author: Andrew Fraser
-- Date: 13 June 2008
-- Name: sw.sql
-- Purpose: Shows what sessions are waiting on
-- Notes: In 10g, there is also a v$session_wait_history view
--
set pages 9999 lines 112
col event form a60 head "Event - All Sessions"
select event, count(*), sum(seconds_in_wait)
from v$session_wait
group by event
order by 3 desc
/
col event form a60 head "Event - Excluding background sessions"
select w.event, count(*), sum(w.seconds_in_wait)
from v$session_wait w, v$session s
where w.sid = s.sid
and s.username is not null
group by w.event
order by 3 desc
/
::::::::::::::
t.sga
::::::::::::::
#
# Author: Andrew Fraser
# Date: 13 June 2008
# Name: t.sga
# Purpose: displays the sga and pga values for all databases on a server
# Notes: Database names with "_"'s in them will throw this script out. So those have to be queried manually.
#
export ORAENV_ASK=NO
for ORACLE_SID in `ps -ef|grep pmon|grep -v grep|awk -F_ '{print $3}'`
do
. oraenv
#sqlplus -s "/ as sysdba" <<e1
sqlplus -s / <<e1
set pages 0 head off feed off
select '$ORACLE_SID sga', sum(value)/1024/1024 mb from v\$sga
/
select '$ORACLE_SID pga', sum(value)/1024/1024 mb from v\$sesstat s, v\$statname n
where s.statistic# = n.statistic#
and n.name = 'session pga memory'
group by '$ORACLE_SID pga'
/
-- select '$ORACLE_SID sga_max_size', value/1024/1024 mb from v\$parameter where name = 'sga_max_size';
-- select '$ORACLE_SID pga_target', value/1024/1024 mb from v\$parameter where name = 'pga_aggregate_target';
e1
done
::::::::::::::
trace.sql
::::::::::::::
--
-- Author: Andrew Fraser
-- Date: 13 June 2008
-- Name: trace.sql
-- Purpose: Not a script as such, rather a list of useful commands and scripts to use for tracing etc.
--
set pages 9999 doc on
doc
Tracing your own session:alter session set timed_statistics=true ; alter session set max_dump_file_size=unlimited ; alter session set tracefile_identifier='MY_OPTIONAL_TEXT' ; alter session set events '10046 trace name context forever, level 8' ; /* code to be traced goes here */ alter session set events '10046 trace name context off' ;Tracing someone else's session:
exec dbms_monitor.session_trace_enable ( session_id => 123 , serial# = 123 ) /* and to switch it off: */ exec dbms_monitor.session_trace_disable ( session_id => 123 , serial# = 123 )In a RAC environment you must be connected to the same instance for those commands to take effect.
exec sys.dbms_system.set_bool_param_in_session(sid,serial#,'timed_statistics',true) exec sys.dbms_system.set_int_param_in_session(sid,serial#,'max_dump_file_size', 2147483647) exec sys.dbms_system.set_ev(sid,serial#,10046,8,'') /* and to switch it off: */ exec sys.dbms_system.set_ev(sid,serial#,10046,0,'')Select session's to trace with SQL something like:
SELECT inst_id , sid , serial# , username FROM gv$session WHERE ( status = 'ACTIVE' OR last_call_et < 60 ) ;Output goes into a trace file in user_dump_dest
cat 1.trc 2.trc 3.trc 4.trc > big.trc tkprof big big
Tkprof produces a .prf output from that. Sort on either fchela or exeela, depending on which is overall largest:
First run:
tkprof [trace file] [output file]Go to end of file, and see whether fetch or execute has largest elapsed time.
Then run either:
tkprof [trace file] [output file] sort=fchela tkprof [trace file] [output file] sort=exeela(If at all possible, use the latest version of tkprof, even with traces produced from an older version database, and even if that means copying trace files to different servers)
For more 10046 tracing options, see the More 10046 Tracing page
-------------------------------------------Statspack for 8i and 9i:
@?/rdbms/admin/spreport - main report @?/rdbms/admin/sprepsql - individual sql report, input the hash_value for the sql you want @?/rdbms/admin/spcreate - installs statspack exec perfstat.statspack.snap - manually take a new snapshotAWR for 10g and above:
@?/rdbms/admin/awrrpt - main report @?/rdbms/admin/awrsqrpt - report one one particular SQL statement, input the sql_id for the sql you want @?/rdbms/admin/sqltrpt - lists top 15 statements, then tunes any sql statement, input the sql_id for the sql you wantOracle Enterprise Manager Database Control default URL is http://server:1158/em
Explain plan:
set pages 9999 lines 192 col plan_plus_exp form a132 @?/rdbms/admin/utlxpls -- outputs explain plan set lines 132 @?/rdbms/admin/utlxplan - (9i and below only) installs explain_plan table. Best to do this as sys only and create public synonymTo set values for bind variables in sql*plus:
variable b1 varchar2(6) ; exec :b1 := '2009'
-------------------------------------------If you have accepted a sql profile from a tuning advisor recommandation like:
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_239607', replace => TRUE) ;Then the regression commands to back it out are like:
select name from dba_sql_profiles ;
exec dbms_sqltune.drop_sql_profile('MYNAME')
-------------------------------------------Get password values for connecting in as a user:
select password from dba_users where username = 'MYUSER' ; -- old versions up to 10g
set long 9999
select dbms_metadata.get_ddl ('USER', 'MYUSER') from dual ; -- 11g and above
alter user myuser identified by temp_pass ;
conn myuser/temp_pass ;
alter user myuser identified by values 'STRING OF CHARACTERS FROM ABOVE' ;
-------------------------------------------Index monitoring usage for all tables:
set pages 9999 lines 112 spool go.tmp SELECT 'alter index '||owner||'.'||index_name||' monitoring usage ;' from dba_indexes where table_owner = 'MYSCHEMA' order by 1 / spool offDocumentation on index monitoring says:
The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active.Each time that you specify MONITORING USAGE, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX...MONITORING USAGE statement is issued, the view information is left unchanged.