Oracle Study Notes by Denis
What have I learned about Oracle today?
T h u r s d a y, S e p t e m b e r 0 3 , 2 0 0 9
My Collection of DBA Scripts
In this post, I intend to list some scripts that I use often for daily DBA
work and for trouble-shooting. I will update the list as necessary when
new scripts come into play. I guess every DBA has his or her own handy
scripts for work.
My List of DBA script:
event.sql
Show the overall picture of system activities by summarizing the session
wait event count from v$session_wait. Used when doing health check or
trouble-shooting. Check Tanel Poder's opinion about sampling
v$session_wait.
get_ddl.sql
Generate DDL SQL statement for creating various objects such as tables,
indexes, view etc. When want to know the constraints of a table or find
the storage properties, I often find it pretty easy by obtaining the DDL of
the table through this script. Also, when I find an index name looks like
system-generated, I use this script to get the index DDL, which can tell
me if the index is funtion-based and what kind of function it is.
login.sql
Used to give username@SID > as SQL* Plus prompte. Placed at the
SQLPATH or working directory. Also give better AUTOTRACE explain plan
output.
longops.sql
Display the progress of some long operations, such as table scan if it is
long enough (> 6s). Some time it is useful to estimate how soon your
SQL will finish.
perf_log_switch_history_daily.sql
Display number of log switch in every hour in a tabular format. Very
useful to understand the workload distribution. From Jeff Hunter.
pxses.sql
I support a reporting database, on which parallel queries are frequently
executed. This script displays the parallel execution server sessions,
which is useful to verify if parallel execution happens and to check the
degree of parallelism.
qlocks.sql
Display blocking and blocked session. An example of using this script
can be found here.
sesevt.sql
Display a session information given wait event as input.
sessid.sql
Display a session information given SID as input.
sesusr.sql
Display a session information given USERNAME as input.
sqlsid.sql
Display the current running SQL of the session. Input session id (sid).
Output the sql text, address, hash value (in 10g, can add sql_id)
sw.sql
Display wait event of a give session by SID. From Tanel Poder.
tabcols.sql
Display table column CBO statistics. Very useful when doing SQL
tunning.
tabix.sql
List of the indexes of a table and show on which columns and in which
order the indexes are.Very usefull when tune a SQL.
tbs.sql
Show a list of data files of a tablespace.
tf.sql
Show tablespace space usage. Used with tbs.sql when adding space or
resize a data file of a tablespace.
xadr.sql
Obtain execution plan from the library cache. Good for 9i (The DB in my
current working environment are mostly 9i). Need to input the address of
the SQL (obtainable from v$sql or v$sqlarea) and child cursor number. I
usually use sesusr.sql to find the SID of a user session given the user
name, then use sqlsid.sql to find out the current running SQL, next using
this script to find the execution plan.
yds at 2:40 PM
Share
No comments:
Post a Comment
‹ Home ›
View web version
About Me
View my complete profile
Powered by Blogger.
