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

save the following scripts into one and run it during performance issue, you will have an overall view of the server wait events, cpu load and what sql is running. 1. to clear dm_os_wait_stats   DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR) 2. use the following query to get the delta of 2 seconds of os wait statistics. select wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms into #originalwaitstatsnapshot from sys.dm_os_wait_stats  waitfor delay '00:00:02'  select wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms into #latestwaitstatsnapshot from sys.dm_os_wait_stats  select l.wait_type,(l.wait_time_ms-o.wait_time_ms) accum_wait_ms from #originalwaitstatsnapshot o inner join #latestwaitstatsnapshot l on o.wait_type=l.wait_type  where l.wait_time_ms > o.wait_time_ms order by accum_wait_ms desc 3. Query to find out which statement is running (stmt_end = -1), and cpu status (is_idle =0, r...

DECLARE @Handle binary(100) SELECT @Handle =0x020000002AA01D147A952286D9A2BE3ECA9B96FC9D588946 select * from ::fn_get_sql(@handle)

found this blog is helpful to get CPU and IO statistics on oracle database. http://shob-dbadmin.blogspot.ca/2012/12/how-to-find-total-io-of-database.html courtesy to  Shomil Bansal , below are hist writing, not mine. How to find total IO of the database instance Total IO of database instance is sum of the physical reads, physical writes and redo writes. There are several views to find these values. v$sysmetric  - Reports metric values for only the most current time sample 60 secs. v$sysmetric_summary  - Reports metric values for time sample of 1 hour. v$sysmetric_history  - Reports metric values every 60 sec from the time instance is up. Better way to analyse IO using this view to take deltas between two time periods. dba_hist_sysmetric_history  - All the above views are refreshed when the instance is restarted. This view, part of AWR, stores the historical stats. I have used this view for my report. Query: ====== set lines 350...

found this article. http://www.mssqltips.com/sql-server-tip-category/81/PowerShell/ http://www.mssqltips.com/sqlservertip/2911/using-powershell-to-access-event-logs-for-sql-server/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20130917 substract commands: about logs: Get-EventLog -list Get-EventLog -LogName "application" | Where-Object {$_.source -like "*SQL*"} | select-Object Index,entrytype, message Get-EventLog -LogName "application" -Message "*error*" -After "April 01, 2013 4:52:31 PM" | Where-Object {$_.source -like "*SQL*"} | format-list TimeGenerated, Source, Message Get-EventLog -LogName "system" -newest 20 | export-csv c:\tools\top_20_events.csv  Get-EventLog -LogName "system" | Where-Object {$_.source -like "*SQL*"} | select-Object Index,entrytype, message | out-file C:\tools\sql_server_logs.txt $begintime = Get-Date 10/31/2...