DBA

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_delta/decode(nvl(executions_delta,0),0,1,exe

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.sql_text) no

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)-extract(minute from begintime

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

We have a database lock situation happens on one production applications, it's caused by the application design, when one user make change and does not save it quickly, the other users will be blocked with no helpful information but hang screen. I build this procedure to run every minute and send email alert to dba team and support staff, so we can talk to the blocker users to save the change or logoff for other users to continue their works. This procedure only list the top 1 waiter and blocker, this is good enough for this application. Here is the script. CREATE  procedure [dbo].[alert_on_databaselock] as declare @blockee_session_id varchar(100) declare @blockee_wait_type varchar(100) declare @wait_duration_ms varchar(100) declare @blocker_session_id varchar(100) declare @start_time varchar(100) declare @status varchar(100) declare @blockee_command varchar(100) declare @database_id varchar(100) declare @blockee_user_id varchar(100) declare @blockee_host_name var

Use the following query to list table size, it's running ok on sqlserver 2010. http://stackoverflow.com/questions/3927231/how-can-you-tell-what-tables-are-taking-up-the-most-space-in-a-sql-server-2005-d SELECT t . NAME AS TableName , i . name AS indexName , SUM ( p . rows ) AS RowCounts , SUM ( a . total_pages ) AS TotalPages , SUM ( a . used_pages ) AS UsedPages , SUM ( a . data_pages ) AS DataPages , ( SUM ( a . total_pages ) * 8 ) / 1024 AS TotalSpaceMB , ( SUM ( a . used_pages ) * 8 ) / 1024 AS UsedSpaceMB , ( SUM ( a . data_pages ) * 8 ) / 1024 AS DataSpaceMB FROM sys . tables t INNER JOIN sys . indexes i ON t . OBJECT_ID = i . object_id INNER JOIN sys . partitions p ON i . object_id = p . OBJECT_ID AND i . index_id = p . index_id INNER JOIN sys . allocation_units a ON p . partition_id = a . container_id WHERE t . NAME NOT LIKE 'dt%' AND i . OBJECT_ID > 255 AND i . index_id &

I use the following query to display the top 20 hot segments which have the biggest logical reads today. query: with hotsegmentvw as ( select * from ( SELECT obj# obj_id ,dataobj# data_obj_id,      sum(logical_reads_delta) AS total_logical_reads FROM dba_hist_seg_stat a WHERE     a.snap_id in (select snap_id from dba_hist_snapshot  where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate)) GROUP BY obj#,dataobj# order by sum(logical_reads_delta) desc ) where rownum <= 20 ) select owner,object_name, total_logical_reads from hotsegmentvw a,dba_objects b where a.obj_id = b.object_id      AND a.data_obj_id = b.data_object_id order by total_logical_reads / sample result: OWNER           OBJECT_NAME                    TOTAL_LOGICAL_READS --------------- ------------------------------ ------------------- ODYSSEY         SPOT_CHANNEL_I4                          334938608 ODYSSEY         SCHEDULE_AVAIL                           369947968 BSS             VERSION_U1        

A daily refresh copy of the production database will be created on the mirror database server, The purpose is to offload the reporting activities from the production database.  This will also intended to alleviate the database locks for users that are performing non-reporting activities and to gain improved performance when generating the reports.  The following steps will outline how to create the automated database refresh process. Create the daily refresh database. 1.        Create the daily refresh database by restoring a backup of the Production database from \\s hared. 2.        Name the database, PROD_Refresh_Reports. Create a Linked Server 1.        Create a Linked Server to the primary database server .  This will be used to retrieve the backup information of the Prod database to verify if a backup file was created. Create daily refresh SQL Agent job. The job is scheduled to run Monday – Friday at 3:00am.  The job will check if the database bac