Important Script Method for tuning
wait.sql enqueue_type.sql lck.sql
lck.sql
select c.sid, c.serial#, c.username, a.object_id, b.object_name, c.program, c.status, d.name, c.osuser, e.sql_text, DBMS_ROWID.ROWID_CREATE(1, c.ROW_WAIT_OBJ#, c.ROW_WAIT_FILE#, c.ROW_WAIT_BLOCK#, c.ROW_WAIT_ROW# ) locked_rowid from v$Locked_object a, All_objects b, v$session c, audit_actions d, v$sqlarea e where a.object_id=b.object_id and a.session_id =c.sid(+) and c.command=d.action and c.row_wait_obj# != -1 and c.sql_id=e.sql_id;
The most important Tuning Notes
Note:61552.1 Database Is Slow. (*) Any recent upgrade/degrade activities??? (2)Check Alert.log and trace files for any cause. (2) Check # of sessions, # of processes. (3) Check event init.ora parameter to see whether any event is set or not. (4) check # of log switching. (5) check archive log destinations space problem or ARCH may…
Oracle 10g Wait Model
*** This article is being delivered in Draft form and may contain errors. Please use the MetaLink “Feedback” button to advise Oracle of any issues related to this article. *** PURPOSE ——- This note includes new columns of V$SESSION and how to effectively use V$SESSION during contention in Oracle 10G. SCOPE & APPLICATION —————————– 1….
Debugging Shell FIles
sh -x ./myshellfile.sh
Rename Tablespace
Alter tablespace OLD_ts_name rename to NEW_TS_NAME;
sql_doing_fts.sql
select sql_text from V$sql a, v$sql_plan b where a.address = b.address and a.hash_value = b.hash_value and b.operation = ‘TABLE ACCESS’ and b.options = ‘FULL’ and b.OBJECT_OWNER ‘SYS’ /
crontab syntax
***** Both hourly jobs ***** 01 * * * * /export/home/oracle/scripts/move_arch_files_uoc.ksh > /export/home/oracle/scripts/log/move_arch_files_uoc.log 2> /export/home/oracle/scripts/log/move_arch_files_uoc.err 01 * * * * /export/home/oracle/scripts/move_arch_files_cif.ksh > /export/home/oracle/scripts/log/move_arch_files_cif.log 2> /export/home/oracle/scripts/log/move_arch_files_cif.err ***** for 15 mins interval ***** 01,16,31,46 * * * * /export/home/oracle/scripts/move_arch_files_uoc.ksh > /export/home/oracle/scripts/log/move_arch_files_uoc.log 2> /export/home/oracle/scripts/log/move_arch_files_uoc.err
move_arch_files.ksh /* Good One */
#!/bin/ksh a=`ps -ef|grep “move_arch_files.ksh”|grep -v grep|wc -l` if [ $a -gt 2 ] then echo “Exiting because of more than 1 Moving archive log script is running ” exit fi # ## Setting Environment information and variables # NODENAME=`hostname` ## change start ORACLE_SID=UAS LOG_FILE=/oracle/scripts/log/MOVE_UAS01.log ARCHMOUNTPOINT=/UASARCH DESTFOLDER=/uocdb7/archlog/UAS ### change end COMFORT_SPACE=75 NO_FILES_MOVED=0 NOTIFY_LIST=’dbaoracle@1800flowers.com’ cp /dev/null $LOG_FILE…
move_arch_files.ksh
echo “The script execution started at `date`” >> $LOG_FILE while [ ${ARCH_SPACE} -ge ${COMFORT_SPACE} ] do FIRST_FILE=`ls -latr $ARCHMOUNTPOINT/*.arc|head -1|awk ‘{print $9}’` # cp -p $FIRST_FILE /blprddb14/archlog/BLINK cp -p $FIRST_FILE $DESTFOLDER CURR_FILE_SZ=`ls -latr $FIRST_FILE|awk ‘{print $5}’` CURR_FL_NAME=`echo $FIRST_FILE |cut -f3 -d’/’` # COPIED_FILE_SZ=`ls -latr /blprddb14/archlog/BLINK/$CURR_FL_NAME |awk ‘{print $5}’` COPIED_FILE_SZ=`ls -latr $DESTFOLDER/$CURR_FL_NAME |awk ‘{print $5}’` if…