DBA

1. shell script: #!/bin/ksh export ORACLE_SID= export ORACLE_HOME= export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib export PATH=$ORACLE_HOME/bin:$HOME/dba:$PATH sqlplus  -s " u/p@prod" <<EOF set verify off set feed off set pagesize 1000 set linesize 130 set head on set markup html on entmap ON spool on preformat off spool cap_report.xls @cap_report.sql spool off EOF BDATE=`sqlplus -s  "u/p" <<EOF set pages 0 trimsp on feed off timing off time off select TRUNC(SYSDATE-7) from dual; exit EOF ` EDATE=`sqlplus -s  "u/p" <<EOF set pages 0 trimsp on feed off timing off time off select TRUNC(SYSDATE-1) from dual; exit EOF ` (echo "IBMS PROD load metrics and top SQL for $BDATE to $EDATE";uuencode cap_report.xls cap_report.xls)|mail -s  "IBMS PROD load metrics and top SQL for $BDATE to $EDATE" email@email.com mv cap_report.xls cap_report.xls.`date +%Y%m%d%H%M%S` 2. the sql cap_re

An nightly ETL job run longer and longer recently and the SLA is jeopardized, we analyzed the query performance and reduced the job duration 5 times. three changes are made, the major improvement came from the de-fragmentation of the table subpartition. The first step is to find out which queries are the top consumer of CPU and elapsed time of the nightly job, I use the following query sqljournal to find out what query is responsible to the slowness.  undefine mnits   undefine begin_hh24mi    undefine end_hh24mi  set linesize 300  col sample_time format a25  col username format a12  col hh24miss format a8  col sid format 9999  col blksid format 9999  col event format a30  col object_name format a15  col current_obj format 99999999  col entry_obj format 9999999  col program format a15  col module format a15  col machine format a17  col MIN(SAMPLE_TIME) format a10  col MAX(SAMPLE_TIME) format a10  col program format a30  col inst# format 99  col session_id format