Oracle Data Warehouse Performance

[Pedro Cirne]

Hi,

I've a DataWarehouse running on rp4440 with 2 CPUs and 2 Gb RAM, connected to a Symmetrix DMX-800 with 2 HBA with PowerPath load balancing.

I would like to improve performance, from SAR statistics I can see a high %WIO, any ideia on how to improve this?:

root@porsx005:/> sar -u

HP-UX porsx005 B.11.11 U 9000/800 01/26/06

00:00:00 %usr %sys %wio %idle
00:20:00 13 3 47 38
00:40:01 12 3 63 22
01:00:00 32 3 47 18
01:20:00 32 5 56 7
01:40:01 22 6 69 4
02:00:00 22 3 53 22
02:20:00 32 3 46 19
02:40:01 19 4 66 11
03:00:00 37 3 40 20
03:20:00 29 3 62 7
03:40:00 20 5 70 5
04:00:00 43 4 49 4
04:20:00 29 5 56 10
04:40:00 23 4 72 1
05:00:02 13 3 73 11
05:20:02 26 4 58 12
05:40:00 12 3 61 24
06:00:00 20 3 54 23
06:20:03 27 3 44 25
06:40:00 29 5 48 18
07:00:00 19 3 56 22
07:20:09 34 5 55 5
07:40:00 42 4 39 15
08:00:00 43 4 34 19
08:20:01 35 3 30 31
08:40:00 48 4 42 6
09:00:00 63 5 29 3
09:20:00 69 4 26 1
09:40:00 60 4 33 2
10:00:00 48 4 42 6
10:20:00 63 4 23 10
10:40:00 44 3 47 5
11:00:00 46 4 46 3
11:20:00 25 3 51 20
11:40:00 21 5 53 21
12:00:00 26 4 53 17
12:20:00 29 3 59 8
12:40:00 15 3 62 19
13:00:00 23 3 51 23
13:20:00 45 3 25 27
13:40:00 16 3 50 32
14:00:00 26 3 61 10
14:20:00 50 4 35 11
14:40:00 22 4 56 18
15:00:00 44 5 38 13
15:20:00 68 8 24 0
15:40:00 35 7 54 4
16:00:00 29 6 53 12
16:20:00 26 2 33 38
16:40:00 22 3 51 24
17:00:00 28 3 43 26
17:20:00 28 3 28 42
17:40:00 15 4 54 27
18:00:00 36 5 53 6
18:20:01 33 3 41 24
18:40:00 8 3 47 42

root@porsx005:/> sar -w

HP-UX porsx005 B.11.11 U 9000/800 01/26/06

00:00:00 swpin/s bswin/s swpot/s bswot/s pswch/s
00:20:00 0.34 0.4 0.34 7.6 2018
00:40:01 0.67 0.0 0.75 0.1 2420
01:00:00 0.82 0.3 0.82 0.5 2550
01:20:00 0.88 0.3 0.83 0.1 4398
01:40:01 0.78 0.0 0.84 0.0 4227
02:00:00 0.96 0.3 0.97 9.1 1945
02:20:00 0.92 0.4 0.83 0.9 2638
02:40:01 0.22 0.5 0.22 0.0 2973
03:00:00 0.90 0.3 0.95 0.3 1880
03:20:00 0.68 0.2 0.72 0.4 2189
03:40:00 0.86 0.2 0.85 10.7 2945
04:00:00 0.83 0.6 0.84 1.1 2969
04:20:00 0.33 0.2 0.24 0.0 2868
04:40:00 0.59 0.1 0.70 0.2 3097
05:00:02 0.72 0.3 0.71 0.7 2204
05:20:02 0.89 0.7 0.89 12.6 2948
05:40:00 0.96 0.2 0.95 0.0 2331
06:00:00 0.39 0.0 0.30 0.0 2358
06:20:03 0.20 0.1 0.24 4.8 2242
06:40:00 0.96 0.3 1.00 0.4 4048
07:00:00 0.86 0.3 0.85 0.0 2247
07:20:09 0.90 0.3 0.90 1.0 4837
07:40:00 0.78 0.7 0.71 13.6 3390
08:00:00 0.02 0.0 0.02 0.0 3553
08:20:01 0.35 0.0 0.44 0.0 2442
08:40:00 0.64 0.8 0.64 8.6 3870
09:00:00 0.71 0.6 0.72 0.2 4949
09:20:00 0.95 0.6 0.95 0.0 5187
09:40:00 1.00 0.6 1.00 0.1 4002
10:00:00 0.81 0.1 0.80 0.0 3612
10:20:00 0.73 0.4 0.63 0.1 4293
10:40:00 0.00 0.0 0.00 0.0 3175
11:00:00 0.31 0.2 0.39 0.1 3686
11:20:00 0.49 0.1 0.43 0.0 2779
11:40:00 0.89 0.5 0.97 0.8 3036
12:00:00 0.80 0.2 0.79 0.0 2773
12:20:00 0.94 0.5 0.95 0.5 2996
12:40:00 0.94 0.3 0.92 0.0 2549
13:00:00 0.85 0.2 0.77 0.2 2778
13:20:00 0.68 0.3 0.68 0.0 3574
13:40:00 0.70 0.2 0.73 0.0 2078
14:00:00 0.74 0.1 0.80 0.0 2806
14:20:00 0.60 0.1 0.59 0.0 3176
14:40:00 0.75 0.3 0.67 0.2 3288
15:00:00 0.57 0.1 0.66 0.7 4809
15:20:00 0.98 0.6 0.99 3.3 8774
15:40:00 0.90 0.8 0.92 4.4 5933
16:00:00 0.40 0.4 0.28 0.0 5318
16:20:00 0.07 0.0 0.12 0.0 2169
16:40:00 0.71 0.2 0.78 0.8 2692
17:00:00 0.87 1.0 0.88 0.1 2706
17:20:00 0.98 0.0 0.98 0.0 2521
17:40:00 0.75 0.2 0.75 0.0 2847
18:00:00 0.96 0.3 0.92 0.4 3885
18:20:01 0.82 0.3 0.84 0.0 2513
18:40:00 0.62 0.2 0.52 0.0 1984

Average 0.70 0.3 0.70 1.5 3259


mou

Thks

Pedro

[Jakes Louw]

I sure hope you have Glance....
if not, you will have to use SAR to analyse the disk performance, per disk.
My guess is that PowerPath isn't doing its job, and all the I/O is going down the one path.

With PVLINKS, to fix this is easy:

vgreduce the primary disk from the VG, then vgextend the VG again with the same disk. This fools the IO daemon and LVM into using the old secondary path disk as the new primary, and helps with load balancing over the fiber channels.

[Darrel Louis]

Hi,

Use sar -d to check which disk has a I/O problem.

If you've Glance, you can see more info regarding your performance problem.
How is your Memory:
- swapinfo -tam
- iostat
- vmstat.

GoodLuck

Darrel

[Geoff Wild]

Your %wio isn't really that high...

That just means "idle with some process waiting for I/O"

Check

sar -d 5 5

Look at avwait - should be less then 10...

Rgds...Geoff

[Pedro Cirne]

Hi All,

Geof, sar -d 5 5:

Average c21t2d7 14.56 0.50 170 4112 0.00 0.89
Average c21t3d0 11.00 0.50 188 3813 0.00 0.60
Average c21t3d1 13.52 0.50 200 4729 0.00 0.72
Average c21t3d2 14.40 0.50 213 4598 0.00 0.70
Average c21t3d3 19.08 0.50 225 5341 0.00 0.91
Average c21t3d4 16.00 0.50 221 5018 0.00 0.75
Average c21t3d5 15.40 0.50 251 5852 0.00 0.65
Average c21t3d6 19.00 0.50 267 6367 0.00 0.77
Average c21t3d7 20.40 0.50 246 5094 0.00 0.89
Average c21t4d0 14.16 0.50 260 5324 0.00 0.58
Average c21t4d1 14.48 0.50 242 4820 0.00 0.61
Average c23t2d7 13.36 0.50 163 3987 0.00 0.84
Average c23t3d0 10.36 0.50 184 3761 0.00 0.59
Average c23t3d1 13.40 0.50 201 4833 0.00 0.71
Average c23t3d2 13.52 0.50 214 4445 0.00 0.66
Average c23t3d3 18.28 0.50 223 5548 0.00 0.88
Average c23t3d4 16.36 0.50 226 5026 0.00 0.76
Average c23t3d5 16.00 0.50 268 6250 0.00 0.62
Average c23t3d6 19.36 0.50 272 6406 0.00 0.77
Average c23t3d7 20.28 0.50 249 4971 0.00 0.86
Average c23t4d0 14.80 0.50 253 5275 0.00 0.62
Average c23t4d1 13.28 0.50 240 4662 0.00 0.58


So avwait is...... ZERO!

The "%idle" metric in SAR is not the same we can see at top, am I right? The "idle" metric in top considers CPU idle when waiting for I/O and in SAR this is not true?

Thks!

Pedro

[Geoff Wild]

More or less that sounds right.

With 0 await - looks like your server isn't really doing anything :)

Good job.

man sar gives some good info on what is displayed.

Rgds...Geoff

[Yogeeraj_1]

hi pedro,

First of all, You should take snap at different time intervals so as to be able to compare the values you are getting for the SAR command.

There is also the STATSPACK report which you can run which will clearly indicate where your application is suffering any performance problems. Again, snaps should be taken at different periods.

hope this helps too!
kind regards
yogeeraj

[Darrel Louis]

Hi,

Try the performance script written by one of the forum members:
http://www.hpux.ws/system.perf.sh

Use this to see if and where you've a bottleneck.

Darrel

[Eric Antunes]

Hi Pedro :)

Here are some basic Oracle performamce scripts about buffer cache:

select (bbc.total_waits*100/(cg.value+dbg.value)) "Buff busy ratio ind. <= 0,007"
from v$system_event bbc,
v$sysstat cg, v$sysstat dbg
where bbc.event='buffer busy waits'
and cg.name ='consistent gets'
and dbg.name='db block gets';

select round(100 * sum(getmisses) / sum(gets), 2) "Misses Pct < 5%"
from v$rowcache
having sum(gets) > 1000;


O "hit ratio" a seguir deve ser entre 90% e 95%. Mais pode ser custoso. Pode ser menos em grandes e bem desenhadas BDs (clusters, etc...)

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
from v$rowcache;

Best Regards,

Eric Antunes

[Steve Lewis]

If you get those stats whilst loading data, then its a storage speed issue.

If you get those stats whilst running queries, then I suggest you look at the indexing strategy in the DW itself, because it means that someone is doing lots of full table scans. You can have many more indexes in a DW than OLTP system.

You may also look at table partitioning and parallel queries as a means to reduce scans.

[Steve Lewis]

If you get those stats whilst loading data, then its a storage speed issue.

If you get those stats whilst running queries, then I suggest you look at the indexing strategy in the DW itself, because it means that someone is doing lots of full table scans. You can have many more indexes in a DW than OLTP system.

You may also look at table partitioning, memory sort space and parallel queries as a means to reduce scans.

[Eric Antunes]

Pedro,

Monitor also the checkpoint frequency:

select value
from v$sysstat
where name like '%DBWR checkpoints%'

select / (sysdate - startup_time) "Checkopints/Hour" from v$instance;

You should have from at least 1 to a maximum of 10 checkpoints per hour. Less or more than that is bad...

Best Regards,

Eric Antunes