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(...

Use plan baseline to stabilize query plan. I use the following two links as reference. http://rnm1978.wordpress.com/2011/06/28/oracle-11g-how-to-force-a-sql_id-to-use-a-plan_hash_value-using-sql-baselines/ http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/ step 1: find the problem query which use bad query plan rather than the good plan. step 2: find the snapshot id which has has the good plan plan for this sql_id. VARIABLE cnt NUMBER EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -                     sql_id => '0yv4z9c24ywm6'); set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_del...

Dev team complains about performance of a complex query which join a dozen of tables, they insist that the row count of the tables in the join remain same, but the query elapse time various from 1 hour to 5 hours. I use AWR to analyze the overall query performance, but down to the fact tables and lookup tables, I use the following DBA query to prove that the logical_read various, which caused the variation of query elapsed time, and most importantly, Tune the query to reduce the elapsed time from hours to only a few minutes! DBA query 1: On table snapshot level: this query help to compare visiting blocks of involved tables on snapshot id level. we have snapshot every 10 minutes. col BEGIN_INTERVAL_TIME format a25 col object_name format a20 col start_time format a15 select object_name, to_char(BEGIN_INTERVAL_TIME,'hh24:mi-dd-mon') start_time, LOGICAL_READS_TOTAL logical_total, LOGICAL_READS_DELTA logical_delta, SPACE_USED_TOTAL space_total, SPACE_USED_DELTA spac...

1. get the sql_id, elapsed_time etc from this script. with viewd as ( select /*+ materialize */ t.*,round(t.BUFFER_GETS /decode(t.exec,0,1,t.exec)) gets_per_exec,round(t.Ela/decode(t.exec,0,1,t.exec)) Ela_sec_per_exec from ( select sql_id,sum(BUFFER_GETS_DELTA) BUFFER_GETS, sum(st.DISK_READS_DELTA) DISK_READS, sum(st.EXECUTIONS_DELTA) exec, round(sum(st.ELAPSED_TIME_DELTA)/1000000)  Ela from dba_hist_sqlstat st,dba_hist_snapshot sn where st.snap_id=sn.snap_id and st.snap_id=sn.snap_id and  trunc(BEGIN_INTERVAL_TIME)>=trunc(sysdate-7) and  trunc(BEGIN_INTERVAL_TIME)<=trunc(sysdate) and EXTRACT(HOUR FROM Sn.END_INTERVAL_TIME) between 9 and 16 and to_NUMBER(TO_CHAR(END_INTERVAL_TIME,'D')) NOT IN (1,7) group by sql_id order by sum(st.ELAPSED_TIME_DELTA) desc   ) t ) select * from (select viewd.* ,substr(sqlt.sql_text,1,90) sql_text from viewd left join dba_hist_sqltext sqlt on viewd.sql_id=sqlt.sql_id where upper(sqlt.s...

We are comparing the performance on oracle database when the AIX is configured with SMT 4 and NO SMT, I create the following scripts to test the elapsed time when running multiple oracle queries at one time. 1. The PL/SQL code: the procedure smttestproc measures the elapsed time in milliseconds.  create table SMTTEST (roundid int,totalprocess int, processid int,starttime timestamp,endtime timestamp,elapsedmilliseconds number(10,0),tablerows int); create or replace procedure smttestproc(roundid in int,totalprocess in int,processid in int) is begintime timestamp; endtime timestamp; elapsedmilliseconds number(10,0); tablerows int; begin select count(*) into tablerows from table_line; select systimestamp into begintime from dual; select count(*) into tablerows from table_line,table_line; select systimestamp into endtime from dual;  select sum(     (extract(hour from endtime)-extract(hour from endtime))*3600+     (extract(minute from endtime)-...

I use this script to find out how the sql run in the history col buffer_gets_DELTA for 999,999,999 col buffer_gets_total for 999,999,999,999 col stat_time for a15 col end_time for a15 select sql_id,PLAN_HASH_VALUE,to_char(BEGIN_INTERVAL_TIME,'hh24:mi-dd-mon') start_time,to_char(end_intervAL_TIME,'hh24:mi-dd-mon') end_time, BUFFER_GETS_DELTA,buffer_gets_total,round(cpu_time_delta/1000000) "cpu_time_del(s)",round(ELAPSED_TIME_DELTA/1000000) "ele_del(s)" from dba_hist_sqlstat st, DBA_HIST_SNAPSHOT sn where st.snap_id=sn.snap_id and BEGIN_INTERVAL_TIME > sysdate-&days and sql_id='&sql_id' order by BEGIN_INTERVAL_TIME; undefine snap_id undefine sql_id