Skip to main content

Posts

Showing posts from 2013

redo log generation hourly

http://www.dba-oracle.com/oracle_tips_sizing_advisor.htm set lines 120; set pages 9999; SELECT to_char(first_time,'YYYY-MON-DD') day, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07", to_char(sum

unix shell script to exclude a date from database backup schedule

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 with clause and materialize hint

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 /

SQL DMV history as in Oracle

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

MS SQL Server performance analysis

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, runnable_tasks

oracle dba_hist_sysmetric_summary

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 pages 50 feedback off set markup html

powershell

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

Isolation Levels in SQL Server

http://www.mssqltips.com/sqlservertip/2977/demonstrations-of-transaction-isolation-levels-in-sql-server/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20130826 Demonstrations of Transaction Isolation Levels in SQL Server By:  Derek Colley    |    Read Comments (5)    |   Related Tips:  More  >  Locking and Blocking Free SQL Server Learning: You Inherited a Database, Now What? Problem You are looking at ways of ensuring atomicity and consistency in your database by ensuring transactional integrity. You may want to avoid locks, or ensure problems such as lost updates or dirty reads cannot occur, or otherwise ensure the quality of your data. Transactional isolation is a narrow but somewhat complex subject that might be ideal for your needs. Solution This article will cover the five transaction isolation settings - READ UNCOMMITTED, READ COMMITTED (locking), READ COMMITTED (snapshot), REPEATABLE READ and SERIALIZATION. You

Unix shell script: run task on specific weekday.

I have a request from user that he want to run a report from server automatically on third Monday every month, I use the following Unix shell script to take care of it. #RUNDATE=`cal |awk 'NR == 3 { print $2; }'` #if [ "$RUNDATE" == '' ]; then NUMBERFIELD=`cal |awk 'NR == 3 { print NF; }'` if [ $NUMBERFIELD -lt 6 ]; then         RUNDATE=`cal |awk 'NR == 6 { print $2; }'`         else RUNDATE=`cal |awk 'NR == 5 { print $2; }'` fi echo $RUNDATE TODAY=`date +%e` if [ $TODAY == $RUNDATE ]; then echo "Today `date +%Y%m%d` is run day!" else echo "Today `date +%Y%m%d` is NOT run day!" echo "run date should be at ${RUNDATE}th, which is the thrid Monday for each month." exit fi ... sqlplus -s / as sysdba <<- EOF         SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF         SET ECHO OFF TERM OFF VERIFY OFF FEEDBACK OFF TRIMS OFF PAGESIZE 999         set markup html on      

non-existent process lock port on windows server

I have a database link created between oracle and sqlserver using oracle tg4odbc, the product is installed on windows server and run as service "OracleOraGtw11g_home1TNSListener", but sometime the service cannot started, the root cause of this problem is that the port number 1521 is used by an non-existent process. The first step is to use netstat -bano|find "1521" to get the process id, in my case it's 5844, which shows the connection is from my oracle server 10.8.0.169 H:\>netstat -bano|find "1521"   TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       5844   TCP    10.14.45.33:1521       10.8.0.169:42987       ESTABLISHED     5844 however the process id does not show in either task manager or process explorer. The next step is to run tcpview, which shows non-existent under process column, there are three rows, two show status as "listening", the other one shows status "established", right click and k

find unindexed foreign key columns

I use the following query to find the unindexed columns. column columns format a35 word_wrapped column table_name format a35 word_wrapped col a_column format a35 word_wrapped col b_column format a35 word_wrapped col status format a9 word_wrapped select * from ( select owner,decode( b.table_name, NULL, 'unindexed', 'ok' ) Status,       a.table_name, a.columns a_column, b.columns b_column from ( select a.owner,substr(a.table_name,1,30) table_name,             substr(a.constraint_name,1,30) constraint_name,         max(decode(position, 1,     substr(column_name,1,30),NULL)) ||         max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||         max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||         max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||         max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||         max(decode(position, 6,', '||substr(column_name

cost of turning off rebalance of ASM diskgroup

One time I turned of the rebalance of ASM diskgroup when adding new disk and forgot about it until the developer told me that they run into error ORA-01653,  unable to extend table in tablespace.   Usually this is caused by a few scenarios(no space on disk, data file reach to max, datafile is not auextensible etc), but this time it's caused by asm disk not balanced. the datafile is 16GB, let me try to extend it to 20GB. 14:14:56 RMS8DEV> alter database datafile 26 resize 20000m; alter database datafile 26 resize 20000m * ERROR at line 1: ORA-01237: cannot extend datafile 26 ORA-01110: data file 26: '+DATA2/rms8dev/datafile/radiostage_data.282.819106481' ORA-17505: ksfdrsz:1 Failed to resize file to size 2560000 blocks ORA-15041: diskgroup "DATA2" space exhausted it failed, but the disk /dev/rhdisk18 has 79GB free space! why cannot I use it? ASMCMD [+] > lsdsk -k Total_MB  Free_MB   OS_MB  Name        Failgroup   Failgroup