DBA

1. the exclude script, it return 1 if the database is excluded, otherwise return 0. more ./exclude_date.ksh TODAY=`date +%Y%m%d` EXCLUDEFILE=/home/oracle/dba/exclude.date if [ -f  $EXCLUDEFILE ] ; then echo "exclude date file exists" echo $TODAY EXCLUDE=`grep $TODAY $EXCLUDEFILE|grep -c $1`         if [ $EXCLUDE -ne 0 ]; then         echo "today is excluded"         exit 1         else         echo "today is not excluded"         exit 0         fi else echo "exclude file does not exists for $1" fi echo "reach end of the script" exit 0 2. the backup script calls the exclude script. ... /home/oracle/dba/exclude_date.ksh $DB if [ $? -eq 1 ] ; then exit fi ...

oracle guru  Burleson's note   helped me to improve performance on a query cross database link http://www.dba-oracle.com/t_with_clause.htm we have a database link from oracle to MS SQL server using oracle DRDA, the following query take long to complete, I cannot wait for it to complete and have to kill the query. SELECT count(*) FROM "ContractItem"@QSENCT002 A, "contractheader_cr"@QSENCT002 B WHERE A. "ContractHeaderID" = B. "contractheaderid" AND "ContractItemDayID" BETWEEN 37345 AND 37346 and not exists (SELECT REVENUESTREAMEXPORTID FROM radiostage.REVENUESTREAMEXPORT_XREF WHERE DIVESTED = 'Y' and A."RevenueStreamExportID" =REVENUESTREAMEXPORTID ) / After re-write the query using with clause and materialize hint, the query complete in 1 second. However, if I give bigger range of date in the query, the performance is not improved much, so it's depends on case by case. with ctview as (select /

Oracle has historical table that you can query to find out active session history etc, sqlserver does not have that, the script below build the history table in sqlserver and create procedure that can be scheduled to run by sqlserver agent to collect the snapshot information. The historical tables are focusing on wait events and cpu scheduler, you can use the following query to check the blocking situation which last more than 10 seconds. select timeofsnapshot,wait_duration_ms,wait_type,session_id,blocking_session_id,login_name,wait_resource,command,nt_user_name,blocking_nt_user_name,text from dm_os_waiting_tasks where wait_duration_ms>10000 and wait_type like 'LCK%' order by wait_duration_ms --order by snapshot_id desc the result is something like the screenshot below you can schedule sqlserver agent to run every 10 seconds to get the snapshot, to collect the snapshot, run procedure “exec collect_dmv_all” Below are the DDLs to create the database objects U