Re: Data Guard and network untilization

From: Karl Arao <karlarao_at_gmail.com>
Date: Fri, 8 Apr 2011 01:59:53 +0800
Message-ID: <BANLkTikMfksEEXekNHzVwEB1GxOjKcBUNg_at_mail.gmail.com>



I will have to correlate that with the following:
  • sysmetric view (Network Traffic Volume Per Sec) - see the SQL below
  • also have a look at the Redo MB/s
  • SAR data about the network transfer (txbyt/s) - I'll use kSar on this

I had a client before that keeps on pointing on the Data Guard being the bottleneck because we are configured with Maximum Availability. When we started gathering performance data (systematically) on their problematic runs and proved them that it's not the network but an SQL problem.... everybody believed on the hard data and the network, storage, development, dba teams began to be cooperative on solving/tuning the issue.

(BTW, the network transfer rate is the same on their bad and good runs.. we were able to prove it from the gathered data..)

In the end, tuning the network (OS & DB) + adding an index on the SQL solved the problem. Even made it faster than their prior runs.

set echo off verify off

ttitle center 'AWR Network Workload Report' skip 2 set pagesize 50000
set linesize 250

col minval         format 999990.00        heading "Network|Minvalue|(mb)/s"
col maxval         format 999990.00        heading "Network|Maxvalue|(mb)/s"
col average        format 999990.00        heading
"Network|Average|value|(mb)/s"
col std_dev        format 999990.00        heading
"Network|Std_dev|value|(mb)/s"

select snap_id id, TO_CHAR(end_time,'YY/MM/DD HH24:MI') tm, instance_number inst, metric_name, minval/1024/1024 minval, maxval/1024/1024 maxval, average/1024/1024 average, standard_deviation/1024/1024 std_dev from dba_hist_sysmetric_summary
where metric_name = 'Network Traffic Volume Per Sec' -- and snap_id in (338,339)
/

-- 
Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 07 2011 - 12:59:53 CDT

Original text of this message