Re: ORACLE ON AIX PERFORMANCE PROBLEMS

From: Ralph (ralph.rye_at_norgetek.com)
Date: 09/15/03 

Date: 15 Sep 2003 13:05:46 -0700

agstamy@yahoo.com (agis) wrote in message news:<d2aa3279.0309150615.4c673a1@posting.google.com>...
> Hi to all,
>
> I have an RS/6000 with2 CPUs and 2048 MB of RAM running tow instances
> of ORACLE 8.1.7 database.One with 440MB SGA and with about 20
> dedicated connections and about 100 in shared servers and one with
> 35MB SGA with almost no activity on JFS file system.
>
> Some times on peak hours we face performance problems in response
> time. Oracle is tuned and with vmstat i realized that we had a lot of
> paging and swapping. I use nmon utility with the folwoing output :
>
> % Used 100.0% 31.1% to Paging Space 12.0 29.0
> numperm 73.1%
> % Free 0.0% 68.9% to File System 457.6 815.3
> minperm 20.0%
> MB Used 2047.5MB 636.2MB Page Scans 407.7
> maxperm 79.8%
> MB Free 0.5MB 1411.8MB Page Cycles 0.0
> minfree 120
> Total(MB) 2048.0MB 2048.0MB Page Reclaim 0.0
> maxfree 128
>
> Also i post 2 vmstat outputs one with normal workload and one with the
> performance problems.
>
> NORMAL
>
>
> kthr memory page faults cpu
> ----- ----------- ------------------------ ------------ -----------
> r b avm fre re pi po fr sr cy in sy cs us sy id wa
> 2 2 178921 121 0 4 3 317 290 0 194 318 222 22 9 60 9
> 2 2 178921 128 0 12 5 113 159 0 819 6716 2565 63 16 13 7
> 2 2 178921 126 0 3 3 89 122 0 797 5971 2414 60 14 19 7
> 1 2 178921 123 0 2 1 50 75 0 477 2566 993 36 4 53 7
> 1 2 178921 125 0 2 3 73 120 0 549 2662 1243 45 5 40 10
>
> PROBLEM
>
> kthr memory page faults cpu
> ----- ----------- ------------------------ ------------ -----------
> r b avm fre re pi po fr sr cy in sy cs us sy id wa
> 2 2 178979 123 0 4 3 317 290 0 194 322 224 22 9 60 9
> 2 2 178979 124 0 12 27 792 1973 0 1071 4877 3069 63 15 4 18
> 4 3 179532 112 0 66 37 889 1561 0 1150 3573 4763 72 17 2 9
> 3 3 179532 119 0 13 13 728 1319 0 1072 3792 4546 73 16 2 9
> 4 3 179532 124 0 9 65 733 1487 0 1135 4299 4041 66 21 3 10
> 5 2 179812 92 0 14 72 891 1666 0 1175 4183 3866 73 15 2 10
> 5 3 179881 123 0 10 156 851 3283 0 1153 2445 2031 36 8 10 45
> 2 4 179896 0 0 15 182 947 2160 0 1250 2719 2428 31 10 7 52
> 1 5 180565 32 0 7 189 596 2143 0 966 1563 1478 14 6 8 72
> 3 4 180655 107 0 53 104 688 1749 0 1148 4207 3253 60 13 2 25
> 3 4 179879 906 0 17 129 625 1167 0 1078 2961 3994 56 13 3 28
> 1 5 180201 0 0 50 107 765 1541 0 1102 2609 2824 29 9 8 54
> 1 4 181176 94 0 19 167 961 2153 0 1106 2439 2070 26 9 5 60
> 6 5 181338 117 0 87 59 633 1408 0 1138 6255 3419 63 14 2 21
> 2 4 181338 0 0 26 129 639 1952 0 1009 2332 1875 38 7 11 44
> 3 2 180957 125 0 50 130 403 940 0 915 2771 1677 33 7 9 51
>
> I have determinate that we use about 1.4 GB for file caching. I want
> to try reduce minperm and maxperm in order to allow at max about 30%
> of RAM for file caching and i think i should gain 700MB free memory
> for oracle.
>
> Is my thought correct ?
>
> Has someone faced the same problem ?
>
> Is anything else i can do ?
>
>
>
> Thanks in abvance and sorry for the extened thread.
>
> Agis
>
> ORACLE DBA

I would start with minperm = 10 & maxperm = 30, that will definitely
help your performance.

What version of AIX?

Do you have Asynch I/O enabled? Are you mounting the filesystem with
the Oracle tablespaces with Direct I/O ?

Ralph

Relevant Pages

  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... Oracle recommends not using bitmap indexes in OLTP ... long due to performance problems) readers will lock and cause deadlocks. ... shouldn't enter a non-default isolation level for long - behave, ... Even in your database code you are extremely cognizant of the ...
    (comp.databases.oracle.server)
  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... use it in SS because of the performance problems it will generate. ... Oracle recommends not using bitmap ... and you can get round deadlocks using it by coding properly AS ...
    (comp.databases.oracle.server)
  • ORACLE ON AIX PERFORMANCE PROBLEMS
    ... of ORACLE 8.1.7 database.One with 440MB SGA and with about 20 ... 35MB SGA with almost no activity on JFS file system. ... Some times on peak hours we face performance problems in response ... I have determinate that we use about 1.4 GB for file caching. ...
    (comp.unix.aix)
  • Re: Using an import as a defrag
    ... What performance problems is the application currently experiencing? ... Is the database growth causing a problem, ... Even though the paper dates back to Oracle 8 (educating people ... those practices, there's one thing less to worry about. ...
    (comp.databases.oracle.server)
  • Re: ORACLE ON AIX PERFORMANCE PROBLEMS
    ... It's a good start to tune your memory usage. ... > Some times on peak hours we face performance problems in response ... > I have determinate that we use about 1.4 GB for file caching. ... > for oracle. ...
    (comp.unix.aix)