DBA

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

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

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

For historical reason, when one of the DSS system was upgrade from oracle 9i to 11g, the charactersets are kept as US7ASCII, which does not support french accent properly, this need to be corrected by converting the charactersets from US7ASCII to AL32UTF8. I use metalink document  Doc ID 260192.1  as a guide. Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1) The document has a lot of details, I only document the one that related to my environment. Note: I use csscan to find the lossy data, use DMU to convert the charactersets. step 1：run csscan to generate the report. csscan \"sys/syspassword as sysdba\" full=yes fromchar=us7ascii tochar=us7ascii log=dbcheck capture=y array=1000000 process=2 it took about 2 hours to run against 500GB database. This step generate the dbcheck.txt, dbcheck.out, and dbcheck.err report. step 2:  generate the lossy data query select 'select '||column_name||' from ...