FAQ
ACTION Identified the 2 indexes having the waits of "db file sequential
read"
Created 2 NEW Tablespaces Each containing 1 Index
Also Specified parameter log_small_entry_max_size = 0
NOTE - ORA 7.3.4.5

RESULT Performance thruput increased to 1500 A/cs per minute from 1000 A/cs
per minute
Allow me to begin :-
There were 2 routes in between the APP & DB Servers .
One had a Bandwidth of 10 MBPS while Another had a Bandwidth of 100 MBPS
The 10 MBPS route was being used all this while . On Enabling the 100 MBPS
route
(by Specifying the I.P. Address Instead of Hostname in the tnsnames.ora &
listener.ora)
the performance has jumped to 1100 records per minute (from 500 records
per minute)
i.e. Almost Doubled .
100 MBPS is the MAX Possible Bandwidth Possible as per the SA

Qs. Your Views on this please

Additional responses Follow your Views BELOW :-

-----Original Message-----
From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
Sent: Tuesday, November 28, 2000 6:30 PM
To: 'VIVEK_SHARMA'
Cc: Arun Chakrapani
Subject: RE: SOS Alert

Vivek:

Sorry for not replying to you yesterday itself. A Busy day. Took a look
at
you report.txt. They were having 2 different profiles. Since You had
mentioned that it also stalled in Test DB I concentrated more on the test
report.txt. One thing which stood out on the other one (live) on opening
you
report.txt is the SQL _AREA GET_HIT_RATIO is very poor. I don't know
whether
you have already pinned your SYS objects and other objects that are
executed
often in shared pool. Please use DBMS_SHARED_POOL.keep to pin
package,procedure,function,sequence,cursors, triggers that are used often.

Ans OK will do so

So now I will concentrate on the Test DB.

1. Even though the SQL_AREA GET_HIT_RATIO is higer(82%) when compared to
live this is not good enough. It should be close to 100%(1 some where like
.98 alteast). Please pin the SYS objects to start with.

OK
Allow me to say that the shared_pool_size has by experience been kept at
bare minimum required
for we did experience performance poblems with a shred_pool_size of 300 MB

( Though on Another machine on SUN OS )
It is recommended to keep shared_pool_size at rock bottom just above the
Limit
where ORA-4031 out of shared pool segments occurs


2. On seeing you system wide wait events the following stands out.

1.log file sync 2863 83922 29.31
2.write complete waits 905 71401 78.9
3.free buffer waits 60 4718 78.63

The first event log file sync event mostly comes when there is a disk
i/o bottleneck for the online redo logs.This happens when the processes
have
to wait for the LGWR to sync a particular REDO log. The p1 parameter in a
Log sync in the corresponding LOg Buffer.


The Write complete waits comes
when the user process is waiting for the LGWR to complete the writing of
the
redo information. So Please take a look at the placing of the online redo
log. In which disk have you placed this. Also if it has been place in its
own file system it doesn't mean that it is not going to a busy disk. Since
you have 0+1, The slices may belong to a HOT disk. What you need to do is
ask your SA to monitor the disk I/O usage and see whats happening to the
disk containing the redo log file. Also you can use iostat -xPnce 5 5 (I
think its available in AIX). the output will look something like

us sy wt id
23 7 49 21
extended device statistics ---- errors ---
r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b s/w h/w trn tot
device
0.0 0.0 0.0 0.0 0.0 0.0 0.0 13.4 0 0 0 0 0 0
c1t10d0s0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.4 0 0 0 0 0 0
c1t10d0s1
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0 0 0
c1t10d0s2
0.0 1.3 0.3 10.1 0.0 0.1 4.6 105.8 0 1 0 0 0 0
c1t10d0s3
Watch out for %b column. If its somewhere around 60 then you may be
experiencing contention on those slices. Ask you SA by giving him/her the
device name and they will be able to give you what the FS is.

NOTE - 4 Datafiles Containing a Particular Table & it's Corresponding
indexes (to Which INSERTS
happen ) were Moved out of the RAID onto a Local Disk whereafter
performance Jumped from
850 per minute to 1100 per minute

Ans - OK . Will Issue the Command

Also I see
Free Buffer waits. This is caused because there are no free buffers when
they are requested - pointing directly to the inefficiency of the DBWR.
Again the DBWR may be slow because you have a I/O bottleneck for the DB
files and indirectly because of the Write complete waits.

Qs. Would you recommend Setting Multiple db_writer processes
even though async_io is set to true ?


" Thus a Volume Group Contains 8 Disks with 4 Disks
Stripe Size = 4K "

[Vivek] Please confirm me that the Stripe size is 4K. Usually you will
have
a stripe size of 32K,64K,128K,256K .... If you are having a stripe size of
4K and your db_block_size as 8K, It is going to take 2 reads to satisfy
one
request for a db_file_sequential_read (Usually requests 1 Oracle block)
[Vivek] . This doesn't sound good if thats true.

NOTE - The "test" Database is a 4K DB_BLOCK_SIZE while the Live Database
is
an 8K DB_BLOCK_SIZE, But the SAME performance Limits were Observed on
Both

So if possible could you please do the following.

1. Please shutdown the Test db and start fresh.
2. Run the same process again and also run the iostat -xPnce 60 10000 for
the duration of the run (output this to a file)
3. Run vmstat and see whats the CPU usage for USER and SYSTEM
4. When the 500 A/c per sec reaches - Please run

set lines 132
select sid, substr(event,1,40),p1,p2,p3 from v$session_wait
where event in ('db file scattered read','db file sequential read','latch
free','buffer busy waits','log file sync','db file scattered
read','enqueue');

and give me the output.

OK - Will Action & revert to you

5. IF may waits are showing as "latch free" in the event column of
v$session_wait, Please query v$latch for
latch#,name,level,GETS,MISSES,SLEEPS IMMEDIATE_GETS
,WAITS_HOLDING_LATCH, SPIN_GETS, SLEEP1.
Ans OK

6. Preserve the output for v$latch_children.

Ans OK

Qs. Are their any IBM OS Specific Parameters which are considered
benefitial for performance ?


I will take a look at both the report.txt in more detail today and get
back
to you.

Thanks Again so much
Where are you Located / Phone No ? if you please
We are Currently Located in Bangalore & Addressing The Problem at Mumbai
over a WAN
Phone 91 80 6588668

HTH
Vivek
-----Original Message-----
From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
Sent: Monday, November 27, 2000 11:16 AM
To: 'Vivek'
Cc: Arun Chakrapani
Subject: RE: SOS Alert

THANKS so much for the association in this issue

Attached 2 report.txt each taken for 1 Hour approx . Is it Sufficient ?

Attached report.txt of Problem on the "Live" Database

<>
NOTE - A "test" Database Created on the DB Server Itself in the Same
Partitions as the "Live" Database
Runs Done on this "test" Database Also failing to go beyond 500 A/cs per
minute


<>

NOTE - Interest Runs were Done on the "Test" Database During Down-Time
Periods of the "Live" Database

You Wrote :-
Since there is some write complete waits Please take a look at the
place the on line redo logs are and what is the read, write on these
disks.

Ans Online Redo logs ALSO present on the RAID 0+1 Disks & are
Multiplexed
Size of Online Redo logfile on the "Live" Database = 30 MB

NOTE - On the test Database we increased the Size of the Online Redo
logfile
to 100MB but with NO benefit

You wrote :-
Please give me the DB Version as well

Ans Database Oracle version = 7.3.4.5.0

You wrote :-
I see some waits on More data from client.
Qs. What is to be done about the Same ?

NOTE - Will Run the 2 SQL Scripts Suggested by you & revert back

Thanks Again

-----Original Message-----
From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
Sent: Monday, November 27, 2000 7:44 PM
To: 'VIVEK_SHARMA_at_infy.com'

Cc: Arun Chakrapani
Subject: SOS Alert

Hi Vivek:

Will it be possible to send the whole report.txt to me. Since you have not
given for how long this was taken (time from utlbstat and utlestat) it is
more difficult to say anything. Based on the report.txt snippet you have
given there seems to me there are some issues with the enqueues and latch
free waits. Please run the following SQL as SYS so bet' 2 different times
(
say 1/2 apart) and send me both the values(It will be great if you
could
run
this when the batch processing is running).

select KSQSTTYP,KSQSTGET,KSQSTWAT from x$ksqst where KSQSTGET>0 and
KSQSTWAT
0;
The delta bet' the 2 results will give you information on what is the
enqueue that we see more waits on. Also Please run this sql given
below
in
a
cron every minute or so and output this to a file

SELECT p1 "File", p2 "Block", p3 "Reason" FROM v$session_wait WHERE event
like 'buffer busy waits';

This will give you and idea about the buffer busy waits. Since there
is
some
write complete waits Please take a look at the place the on line redo logs
are and what is the read, write on these disks. Also Please give me
infomation on the relog file size. Please give me the DB Version as well.
I
see some waits on More data from client. Again since I don't know for how
long the stats were run can't say whether they are significant or not.

HTH
Vivek



From: VIVEK_SHARMA
Date: Mon, 27 Nov 2000 12:11:59 +0530
Subject: SOS Alert

CASE In a Bank, Interest Calculation Batch Processing Unable to go beyond
500 A/cs per minute

CAUSE of the Problem is UNKNOWN

ORACLE 7.3.4.5 on AIX 4.3.3
DB Server - IBM S80 Model - 12 CPUs, 3 GB RAM
APP Server 1 - IBM S80 Model - 6 CPUs, 2 GB RAM
APP Servers 2 & 3 - IBM Thin Nodes model - 4 CPUs, 1 GB RAM

Storage Box :-
===========
SS Class Storage
RAID 0+1 - (First Striped & then Mirrored)
NOTE - 2 Storage Boxes Exist, one being the Mirror of the Other
Striping exists across a set 4 Disks (in one Box) with another 4 being
it's
mirror
(in another Box).
Thus a Volume Group Contains 8 Disks with 4 Disks
Stripe Size = 4K

NOTE - Runs Tried from BOTH the APP Servers 1 OR 2,But with the SAME MAX
of
500 A/cs
processed per minute

CPU Utilizations on BOTH APP & DB Server = 40 %
wio% on BOTH APP & DB Servers = 35 %
No paging happening on Both APP & DB Servers

- Oracle Contention Values Seem Small to us as shown Below or so they seem
to us :-

SVRMGR> Rem System wide wait events for non-background processes
(PMON,
SVRMGR> Rem SMON, etc). Times are in hundreths of seconds. Each one
of
SVRMGR> Rem these is a context switch which costs CPU time. By
looking
at
SVRMGR> Rem the Total Time you can often determine what is the
bottleneck
SVRMGR> Rem that processes are waiting for. This shows the total time
spent
SVRMGR> Rem waiting for a specific event and the average time per wait on
SVRMGR> Rem that event.
SVRMGR> select n1.event "Event Name",
2> n1.event_count "Count",
3> n1.time_waited "Total Time",
4> round(n1.time_waited/n1.event_count, 2) "Avg Time"
5> from stats$event n1
6> where n1.event_count > 0
7> order by n1.time_waited desc;
Event Name Count Total Time Avg Time
-------------------------------- ------------- -------------
-------------
SQL*Net message from client 10856276 31977110 2.95
enqueue 1295 374980 289.56
db file sequential read 3614044 303848 .08
write complete waits 5812 295937 50.92
latch free 5045060 242170 .05
SQL*Net more data from client 13939 165275 11.86
log file sync 12794 146409 11.44
buffer busy waits 100443 92477 .92


- ALL Literal SQLs were Converted to using Bind variables
- ALL Tables Running on Indexes Without Any FULL Scans happening .

- All the Literal SQLs (Dynamic) Converted to using Bind variables (Static
Queries)

- event="10181 trace name context forever, level 1000"
NOTE - Set nevertheless, None of the SQLs taking Excessive time to parse
though

- NO statistics are Analyzed
- 7 tables involved in the interest Calc.
inserts to 2 Tables

Search Discussions

  • VIVEK_SHARMA at Dec 20, 2000 at 7:24 am
    CASE In a Bank, Interest Calculation Batch Processing Unable to go beyond
    500 A/cs per minute

    ACTION Moved the Database to Another IBM Machine ( 4 CPU, 4 GB RAM ) with
    HARDWARE Mirroring & 64 K Stripe Size

    NOTE - On the Under-performing DB Server S80 Model of IBM) there exists
    Software Mirroring (By the AIX O.S.) & a Stripe Size of 4K exist

    Firing Off the Interest Calculation Runs from 2 APP Servers Giving an
    Overall Total Processing of Number of 3000 A/cs per minute ( 1500 from each
    )

    Qs. Which of the 2 i.e. Hardware Mirroring OR 64 K Striping, do you think
    is the Cause of the Increase in performance as we put BOTH features Together
    at the SAME Time OR do Both Contribute Equally ?

    Thanks to Vivek for everything

    -----Original Message-----

    From: VIVEK_SHARMA
    Sent: Thursday, December 07, 2000 6:35 PM
    Subject: RE: SOS Alert

    ACTION Identified the 2 indexes having the waits of "db file
    sequential read"
    Created 2 NEW Tablespaces Each containing 1 Index
    Also Specified parameter log_small_entry_max_size = 0
    NOTE - ORA 7.3.4.5

    RESULT Performance thruput increased to 1500 A/cs per minute from

    1000 A/cs per minute

    Allow me to begin :-
    There were 2 routes in between the APP & DB Servers .
    One had a Bandwidth of 10 MBPS while Another had a Bandwidth of 100
    MBPS
    The 10 MBPS route was being used all this while . On Enabling the
    100 MBPS route
    (by Specifying the I.P. Address Instead of Hostname in the
    tnsnames.ora & listener.ora)
    the performance has jumped to 1100 records per minute (from 500
    records per minute)
    i.e. Almost Doubled .
    100 MBPS is the MAX Possible Bandwidth Possible as per the SA

    Qs. Your Views on this please

    Additional responses Follow your Views BELOW :-

    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Tuesday, November 28, 2000 6:30 PM
    To: 'VIVEK_SHARMA'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert

    Vivek:

    Sorry for not replying to you yesterday itself. A Busy day. Took a
    look at
    you report.txt. They were having 2 different profiles. Since You had
    mentioned that it also stalled in Test DB I concentrated more on
    the test
    report.txt. One thing which stood out on the other one (live) on
    opening you
    report.txt is the SQL _AREA GET_HIT_RATIO is very poor. I don't know
    whether
    you have already pinned your SYS objects and other objects that are
    executed
    often in shared pool. Please use DBMS_SHARED_POOL.keep to pin
    package,procedure,function,sequence,cursors, triggers that are used

    often.

    Ans OK will do so

    So now I will concentrate on the Test DB.

    Even though the SQL_AREA GET_HIT_RATIO is higer(82%) when
    compared to
    live this is not good enough. It should be close to 100%(1 some
    where like
    .98 alteast). Please pin the SYS objects to start with.

    OK
    Allow me to say that the shared_pool_size has by experience been
    kept at bare minimum required
    for we did experience performance poblems with a shred_pool_size of
    300 MB
    ( Though on Another machine on SUN OS )
    It is recommended to keep shared_pool_size at rock bottom just above
    the Limit
    where ORA-4031 out of shared pool segments occurs

    2. On seeing you system wide wait events the following stands out.

    1.log file sync 2863 83922 29.31
    2.write complete waits 905 71401 78.9
    3.free buffer waits 60 4718 78.63

    The first event log file sync event mostly comes when there is a
    disk
    i/o bottleneck for the online redo logs.This happens when the
    processes have
    to wait for the LGWR to sync a particular REDO log. The p1 parameter
    in a
    Log sync in the corresponding LOg Buffer.

    The Write complete waits comes
    when the user process is waiting for the LGWR to complete the
    writing of the
    redo information. So Please take a look at the placing of the
    online redo
    log. In which disk have you placed this. Also if it has been place
    in its
    own file system it doesn't mean that it is not going to a busy disk.
    Since
    you have 0+1, The slices may belong to a HOT disk. What you need to
    do is
    ask your SA to monitor the disk I/O usage and see whats happening to
    the
    disk containing the redo log file. Also you can use iostat -xPnce 5
    5 (I
    think its available in AIX). the output will look something like

    us sy wt id
    23 7 49 21
    extended device statistics ---- errors
    ---
    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b s/w h/w trn
    tot
    device
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 13.4 0 0 0 0 0

    c1t10d0s0
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.4 0 0 0 0 0

    c1t10d0s1
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0 0

    c1t10d0s2
    0.0 1.3 0.3 10.1 0.0 0.1 4.6 105.8 0 1 0 0 0

    c1t10d0s3
    Watch out for %b column. If its somewhere around 60 then you may be
    experiencing contention on those slices. Ask you SA by giving
    him/her the
    device name and they will be able to give you what the FS is.

    NOTE - 4 Datafiles Containing a Particular Table & it's
    Corresponding indexes (to Which INSERTS
    happen ) were Moved out of the RAID onto a Local Disk whereafter
    performance Jumped from
    850 per minute to 1100 per minute

    Ans - OK . Will Issue the Command

    Also I see
    Free Buffer waits. This is caused because there are no free buffers
    when
    they are requested - pointing directly to the inefficiency of the
    DBWR.
    Again the DBWR may be slow because you have a I/O bottleneck for the
    DB
    files and indirectly because of the Write complete waits.

    Qs. Would you recommend Setting Multiple db_writer processes
    even though async_io is set to true ?

    " Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K "
    [Vivek] Please confirm me that the Stripe size is 4K. Usually you
    will have
    a stripe size of 32K,64K,128K,256K .... If you are having a stripe
    size of
    4K and your db_block_size as 8K, It is going to take 2 reads to
    satisfy one
    request for a db_file_sequential_read (Usually requests 1 Oracle
    block)
    [Vivek] . This doesn't sound good if thats true.

    NOTE - The "test" Database is a 4K DB_BLOCK_SIZE while the Live
    Database is
    an 8K DB_BLOCK_SIZE, But the SAME performance Limits were Observed

    on Both

    So if possible could you please do the following.

    Please shutdown the Test db and start fresh.
    Run the same process again and also run the iostat -xPnce 60
    10000 for
    the duration of the run (output this to a file)
    Run vmstat and see whats the CPU usage for USER and SYSTEM
    When the 500 A/c per sec reaches - Please run

    set lines 132
    select sid, substr(event,1,40),p1,p2,p3 from v$session_wait
    where event in ('db file scattered read','db file sequential
    read','latch
    free','buffer busy waits','log file sync','db file scattered
    read','enqueue');

    and give me the output.

    OK - Will Action & revert to you

    5. IF may waits are showing as "latch free" in the event column of
    v$session_wait, Please query v$latch for
    latch#,name,level,GETS,MISSES,SLEEPS IMMEDIATE_GETS,WAITS_HOLDING_LATCH, SPIN_GETS, SLEEP1.

    Ans OK

    6. Preserve the output for v$latch_children.

    Ans OK

    Qs. Are their any IBM OS Specific Parameters which are considered

    benefitial for performance ?

    I will take a look at both the report.txt in more detail today and
    get back

    to you.

    Thanks Again so much
    Where are you Located / Phone No ? if you please
    We are Currently Located in Bangalore & Addressing The Problem at
    Mumbai over a WAN
    Phone 91 80 6588668

    HTH

    Vivek
    -----Original Message-----
    From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
    Sent: Monday, November 27, 2000 11:16 AM
    To: 'Vivek'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert >
    THANKS so much for the association in this issue >
    Attached 2 report.txt each taken for 1 Hour approx . Is it
    Sufficient ?
    >
    Attached report.txt of Problem on the "Live" Database >
    <>
    NOTE - A "test" Database Created on the DB Server Itself in the Same
    Partitions as the "Live" Database
    Runs Done on this "test" Database Also failing to go beyond 500 A/cs per
    minute
    >
    >
    <> >
    NOTE - Interest Runs were Done on the "Test" Database During Down-Time
    Periods of the "Live" Database >
    You Wrote :-
    Since there is some write complete waits Please take a look at the
    place the on line redo logs are and what is the read, write on these
    disks. >
    Ans Online Redo logs ALSO present on the RAID 0+1 Disks & are
    Multiplexed
    Size of Online Redo logfile on the "Live" Database = 30 MB >
    NOTE - On the test Database we increased the Size of the Online Redo
    logfile
    to 100MB but with NO benefit >
    You wrote :-
    Please give me the DB Version as well >
    Ans Database Oracle version = 7.3.4.5.0 >
    You wrote :-
    I see some waits on More data from client.
    Qs. What is to be done about the Same ? >
    NOTE - Will Run the 2 SQL Scripts Suggested by you & revert back >
    Thanks Again
    >
    >
    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Monday, November 27, 2000 7:44 PM
    To: 'VIVEK_SHARMA_at_infy.com'
    > >
    Cc: Arun Chakrapani
    Subject: SOS Alert
    > >
    Hi Vivek:
    > >
    Will it be possible to send the whole report.txt to me. Since
    you have
    not
    given for how long this was taken (time from utlbstat and
    utlestat) it
    is
    more difficult to say anything. Based on the report.txt snippet
    you
    have
    given there seems to me there are some issues with the enqueues
    and
    latch
    free waits. Please run the following SQL as SYS so bet' 2
    different
    times
    (
    say 1/2 apart) and send me both the values(It will be great if
    you could
    run
    this when the batch processing is running).
    > >
    select KSQSTTYP,KSQSTGET,KSQSTWAT from x$ksqst where KSQSTGET>0
    and
    KSQSTWAT
    0;
    > >
    The delta bet' the 2 results will give you information on what
    is the
    enqueue that we see more waits on. Also Please run this sql
    given below
    in
    a
    cron every minute or so and output this to a file
    > >
    SELECT p1 "File", p2 "Block", p3 "Reason" FROM v$session_wait
    WHERE
    event
    like 'buffer busy waits';
    > >
    This will give you and idea about the buffer busy waits. Since
    there is
    some
    write complete waits Please take a look at the place the on line
    redo
    logs
    are and what is the read, write on these disks. Also Please give
    me
    infomation on the relog file size. Please give me the DB Version
    as
    well.
    I
    see some waits on More data from client. Again since I don't
    know for
    how
    long the stats were run can't say whether they are significant
    or not.
    > >
    HTH
    > >
    Vivek
    > >
    > >
    > >
    From: VIVEK_SHARMA
    Date: Mon, 27 Nov 2000 12:11:59 +0530
    Subject: SOS Alert
    > >
    CASE In a Bank, Interest Calculation Batch Processing Unable to
    go
    beyond
    500 A/cs per minute
    > >
    CAUSE of the Problem is UNKNOWN
    > >
    ORACLE 7.3.4.5 on AIX 4.3.3
    DB Server - IBM S80 Model - 12 CPUs, 3 GB RAM
    APP Server 1 - IBM S80 Model - 6 CPUs, 2 GB RAM
    APP Servers 2 & 3 - IBM Thin Nodes model - 4 CPUs, 1 GB RAM
    > >
    Storage Box :-
    ===========
    SS Class Storage
    RAID 0+1 - (First Striped & then Mirrored)
    NOTE - 2 Storage Boxes Exist, one being the Mirror of the Other
    Striping exists across a set 4 Disks (in one Box) with another 4
    being
    it's
    mirror
    (in another Box).
    Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K
    > >
    NOTE - Runs Tried from BOTH the APP Servers 1 OR 2,But with the
    SAME
    MAX
    of
    500 A/cs
    processed per minute
    > >
    CPU Utilizations on BOTH APP & DB Server = 40 %
    wio% on BOTH APP & DB Servers = 35 %
    No paging happening on Both APP & DB Servers
    > >
    - Oracle Contention Values Seem Small to us as shown Below or so
    they
    seem
    to us :-
    > >
    SVRMGR> Rem System wide wait events for non-background processes
    (PMON,
    SVRMGR> Rem SMON, etc). Times are in hundreths of seconds.
    Each one of
    >
    SVRMGR> Rem these is a context switch which costs CPU time. By
    looking
    at
    SVRMGR> Rem the Total Time you can often determine what is the
    bottleneck
    SVRMGR> Rem that processes are waiting for. This shows the
    total time
    spent
    SVRMGR> Rem waiting for a specific event and the average time
    per wait
    on
    SVRMGR> Rem that event.
    SVRMGR> select n1.event "Event Name",
    2> n1.event_count "Count",
    3> n1.time_waited "Total Time",
    4> round(n1.time_waited/n1.event_count, 2) "Avg Time"
    5> from stats$event n1
    6> where n1.event_count > 0
    7> order by n1.time_waited desc;
    Event Name Count Total Time Avg
    Time
    >
    -------------------------------- ------------- -------------
    -------------
    SQL*Net message from client 10856276 31977110 2.95
    enqueue 1295 374980 289.56
    db file sequential read 3614044 303848 .08
    write complete waits 5812 295937 50.92
    latch free 5045060 242170 .05
    SQL*Net more data from client 13939 165275 11.86
    log file sync 12794 146409 11.44
    buffer busy waits 100443 92477
    .92
    > >
    > >
    > >
    - ALL Literal SQLs were Converted to using Bind variables
    - ALL Tables Running on Indexes Without Any FULL Scans happening
    .
    > >
    - All the Literal SQLs (Dynamic) Converted to using Bind
    variables
    (Static
    Queries)
    > >
    - event="10181 trace name context forever, level 1000"
    NOTE - Set nevertheless, None of the SQLs taking Excessive time
    to
    parse
    though
    > >
    - NO statistics are Analyzed
  • Mohan, Ross at Dec 20, 2000 at 8:20 pm
    This message is in MIME format. Since your mail reader does not understand
    this format, some or all of this message may not be legible.

    ------_=_NextPart_001_01C06AC2.46995EF0
    Content-Type: text/plain;

    charset="iso-8859-1"

    4K striping is a major Major MAJOR mistake.

    Doing it in software makes it at least 3x as
    bad.

    Either fix alone would have provided at least a
    2-4x improvement.

    Given a choice, I'd fix the stripe size first, then
    convert the mirroring to hardware.

    hth

    imho

    ymmv

    afaik,

    etc.

    Ross Mohan

    -----Original Message-----
    From: VIVEK_SHARMA
    Sent: Wednesday, December 20, 2000 11:13 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert

    CASE In a Bank, Interest Calculation Batch Processing Unable to go beyond
    500 A/cs per minute

    ACTION Moved the Database to Another IBM Machine ( 4 CPU, 4 GB RAM ) with
    HARDWARE Mirroring & 64 K Stripe Size

    NOTE - On the Under-performing DB Server S80 Model of IBM) there exists
    Software Mirroring (By the AIX O.S.) & a Stripe Size of 4K exist

    Firing Off the Interest Calculation Runs from 2 APP Servers Giving an
    Overall Total Processing of Number of 3000 A/cs per minute ( 1500 from each
    )

    Qs. Which of the 2 i.e. Hardware Mirroring OR 64 K Striping, do you think
    is the Cause of the Increase in performance as we put BOTH features Together
    at the SAME Time OR do Both Contribute Equally ?

    Thanks to Vivek for everything

    -----Original Message-----

    From: VIVEK_SHARMA
    Sent: Thursday, December 07, 2000 6:35 PM
    Subject: RE: SOS Alert

    ACTION Identified the 2 indexes having the waits of "db file
    sequential read"
    Created 2 NEW Tablespaces Each containing 1 Index
    Also Specified parameter log_small_entry_max_size = 0
    NOTE - ORA 7.3.4.5

    RESULT Performance thruput increased to 1500 A/cs per minute from

    1000 A/cs per minute

    Allow me to begin :-
    There were 2 routes in between the APP & DB Servers .
    One had a Bandwidth of 10 MBPS while Another had a Bandwidth of 100
    MBPS
    The 10 MBPS route was being used all this while . On Enabling the
    100 MBPS route
    (by Specifying the I.P. Address Instead of Hostname in the
    tnsnames.ora & listener.ora)
    the performance has jumped to 1100 records per minute (from 500
    records per minute)
    i.e. Almost Doubled .
    100 MBPS is the MAX Possible Bandwidth Possible as per the SA

    Qs. Your Views on this please

    Additional responses Follow your Views BELOW :-

    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Tuesday, November 28, 2000 6:30 PM
    To: 'VIVEK_SHARMA'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert

    Vivek:

    Sorry for not replying to you yesterday itself. A Busy day. Took a
    look at
    you report.txt. They were having 2 different profiles. Since You had
    mentioned that it also stalled in Test DB I concentrated more on
    the test
    report.txt. One thing which stood out on the other one (live) on
    opening you
    report.txt is the SQL _AREA GET_HIT_RATIO is very poor. I don't know
    whether
    you have already pinned your SYS objects and other objects that are
    executed
    often in shared pool. Please use DBMS_SHARED_POOL.keep to pin
    package,procedure,function,sequence,cursors, triggers that are used

    often.

    Ans OK will do so

    So now I will concentrate on the Test DB.

    Even though the SQL_AREA GET_HIT_RATIO is higer(82%) when
    compared to
    live this is not good enough. It should be close to 100%(1 some
    where like
    .98 alteast). Please pin the SYS objects to start with.

    OK
    Allow me to say that the shared_pool_size has by experience been
    kept at bare minimum required
    for we did experience performance poblems with a shred_pool_size of
    300 MB
    ( Though on Another machine on SUN OS )
    It is recommended to keep shared_pool_size at rock bottom just above
    the Limit
    where ORA-4031 out of shared pool segments occurs

    2. On seeing you system wide wait events the following stands out.

    1.log file sync 2863 83922 29.31
    2.write complete waits 905 71401 78.9
    3.free buffer waits 60 4718 78.63

    The first event log file sync event mostly comes when there is a
    disk
    i/o bottleneck for the online redo logs.This happens when the
    processes have
    to wait for the LGWR to sync a particular REDO log. The p1 parameter
    in a
    Log sync in the corresponding LOg Buffer.

    The Write complete waits comes
    when the user process is waiting for the LGWR to complete the
    writing of the
    redo information. So Please take a look at the placing of the
    online redo
    log. In which disk have you placed this. Also if it has been place
    in its
    own file system it doesn't mean that it is not going to a busy disk.
    Since
    you have 0+1, The slices may belong to a HOT disk. What you need to
    do is
    ask your SA to monitor the disk I/O usage and see whats happening to
    the
    disk containing the redo log file. Also you can use iostat -xPnce 5
    5 (I
    think its available in AIX). the output will look something like

    us sy wt id
    23 7 49 21
    extended device statistics ---- errors
    ---
    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b s/w h/w trn
    tot
    device
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 13.4 0 0 0 0 0

    c1t10d0s0
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.4 0 0 0 0 0

    c1t10d0s1
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0 0

    c1t10d0s2
    0.0 1.3 0.3 10.1 0.0 0.1 4.6 105.8 0 1 0 0 0

    c1t10d0s3
    Watch out for %b column. If its somewhere around 60 then you may be
    experiencing contention on those slices. Ask you SA by giving
    him/her the
    device name and they will be able to give you what the FS is.

    NOTE - 4 Datafiles Containing a Particular Table & it's
    Corresponding indexes (to Which INSERTS
    happen ) were Moved out of the RAID onto a Local Disk whereafter
    performance Jumped from
    850 per minute to 1100 per minute

    Ans - OK . Will Issue the Command

    Also I see
    Free Buffer waits. This is caused because there are no free buffers
    when
    they are requested - pointing directly to the inefficiency of the
    DBWR.
    Again the DBWR may be slow because you have a I/O bottleneck for the
    DB
    files and indirectly because of the Write complete waits.

    Qs. Would you recommend Setting Multiple db_writer processes
    even though async_io is set to true ?

    " Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K "
    [Vivek] Please confirm me that the Stripe size is 4K. Usually you
    will have
    a stripe size of 32K,64K,128K,256K .... If you are having a stripe
    size of
    4K and your db_block_size as 8K, It is going to take 2 reads to
    satisfy one
    request for a db_file_sequential_read (Usually requests 1 Oracle
    block)
    [Vivek] . This doesn't sound good if thats true.

    NOTE - The "test" Database is a 4K DB_BLOCK_SIZE while the Live
    Database is
    an 8K DB_BLOCK_SIZE, But the SAME performance Limits were Observed

    on Both

    So if possible could you please do the following.

    Please shutdown the Test db and start fresh.
    Run the same process again and also run the iostat -xPnce 60
    10000 for
    the duration of the run (output this to a file)
    Run vmstat and see whats the CPU usage for USER and SYSTEM
    When the 500 A/c per sec reaches - Please run

    set lines 132
    select sid, substr(event,1,40),p1,p2,p3 from v$session_wait
    where event in ('db file scattered read','db file sequential
    read','latch
    free','buffer busy waits','log file sync','db file scattered
    read','enqueue');

    and give me the output.

    OK - Will Action & revert to you

    5. IF may waits are showing as "latch free" in the event column of
    v$session_wait, Please query v$latch for
    latch#,name,level,GETS,MISSES,SLEEPS IMMEDIATE_GETS,WAITS_HOLDING_LATCH, SPIN_GETS, SLEEP1.

    Ans OK

    6. Preserve the output for v$latch_children.

    Ans OK

    Qs. Are their any IBM OS Specific Parameters which are considered

    benefitial for performance ?

    I will take a look at both the report.txt in more detail today and
    get back

    to you.

    Thanks Again so much
    Where are you Located / Phone No ? if you please
    We are Currently Located in Bangalore & Addressing The Problem at
    Mumbai over a WAN
    Phone 91 80 6588668

    HTH

    Vivek
    -----Original Message-----
    From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
    Sent: Monday, November 27, 2000 11:16 AM
    To: 'Vivek'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert >
    THANKS so much for the association in this issue >
    Attached 2 report.txt each taken for 1 Hour approx . Is it
    Sufficient ?
    >
    Attached report.txt of Problem on the "Live" Database >
    <>
    NOTE - A "test" Database Created on the DB Server Itself in the Same
    Partitions as the "Live" Database
    Runs Done on this "test" Database Also failing to go beyond 500 A/cs per
    minute
    >
    >
    <> >
    NOTE - Interest Runs were Done on the "Test" Database During Down-Time
    Periods of the "Live" Database >
    You Wrote :-
    Since there is some write complete waits Please take a look at the
    place the on line redo logs are and what is the read, write on these
    disks. >
    Ans Online Redo logs ALSO present on the RAID 0+1 Disks & are
    Multiplexed
    Size of Online Redo logfile on the "Live" Database = 30 MB >
    NOTE - On the test Database we increased the Size of the Online Redo
    logfile
    to 100MB but with NO benefit >
    You wrote :-
    Please give me the DB Version as well >
    Ans Database Oracle version = 7.3.4.5.0 >
    You wrote :-
    I see some waits on More data from client.
    Qs. What is to be done about the Same ? >
    NOTE - Will Run the 2 SQL Scripts Suggested by you & revert back >
    Thanks Again
    >
    >
    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Monday, November 27, 2000 7:44 PM
    To: 'VIVEK_SHARMA_at_infy.com'
    > >
    Cc: Arun Chakrapani
    Subject: SOS Alert
    > >
    Hi Vivek:
    > >
    Will it be possible to send the whole report.txt to me. Since
    you have
    not
    given for how long this was taken (time from utlbstat and
    utlestat) it
    is
    more difficult to say anything. Based on the report.txt snippet
    you
    have
    given there seems to me there are some issues with the enqueues
    and
    latch
    free waits. Please run the following SQL as SYS so bet' 2
    different
    times
    (
    say 1/2 apart) and send me both the values(It will be great if
    you could
    run
    this when the batch processing is running).
    > >
    select KSQSTTYP,KSQSTGET,KSQSTWAT from x$ksqst where KSQSTGET>0
    and
    KSQSTWAT
    0;
    > >
    The delta bet' the 2 results will give you information on what
    is the
    enqueue that we see more waits on. Also Please run this sql
    given below
    in
    a
    cron every minute or so and output this to a file
    > >
    SELECT p1 "File", p2 "Block", p3 "Reason" FROM v$session_wait
    WHERE
    event
    like 'buffer busy waits';
    > >
    This will give you and idea about the buffer busy waits. Since
    there is
    some
    write complete waits Please take a look at the place the on line
    redo
    logs
    are and what is the read, write on these disks. Also Please give
    me
    infomation on the relog file size. Please give me the DB Version
    as
    well.
    I
    see some waits on More data from client. Again since I don't
    know for
    how
    long the stats were run can't say whether they are significant
    or not.
    > >
    HTH
    > >
    Vivek
    > >
    > >
    > >
    From: VIVEK_SHARMA
    Date: Mon, 27 Nov 2000 12:11:59 +0530
    Subject: SOS Alert
    > >
    CASE In a Bank, Interest Calculation Batch Processing Unable to
    go
    beyond
    500 A/cs per minute
    > >
    CAUSE of the Problem is UNKNOWN
    > >
    ORACLE 7.3.4.5 on AIX 4.3.3
    DB Server - IBM S80 Model - 12 CPUs, 3 GB RAM
    APP Server 1 - IBM S80 Model - 6 CPUs, 2 GB RAM
    APP Servers 2 & 3 - IBM Thin Nodes model - 4 CPUs, 1 GB RAM
    > >
    Storage Box :-
    ===========
    SS Class Storage
    RAID 0+1 - (First Striped & then Mirrored)
    NOTE - 2 Storage Boxes Exist, one being the Mirror of the Other
    Striping exists across a set 4 Disks (in one Box) with another 4
    being
    it's
    mirror
    (in another Box).
    Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K
    > >
    NOTE - Runs Tried from BOTH the APP Servers 1 OR 2,But with the
    SAME
    MAX
    of
    500 A/cs
    processed per minute
    > >
    CPU Utilizations on BOTH APP & DB Server = 40 %
    wio% on BOTH APP & DB Servers = 35 %
    No paging happening on Both APP & DB Servers
    > >
    - Oracle Contention Values Seem Small to us as shown Below or so
    they
    seem
    to us :-
    > >
    SVRMGR> Rem System wide wait events for non-background processes
    (PMON,
    SVRMGR> Rem SMON, etc). Times are in hundreths of seconds.
    Each one of
    >
    SVRMGR> Rem these is a context switch which costs CPU time. By
    looking
    at
    SVRMGR> Rem the Total Time you can often determine what is the
    bottleneck
    SVRMGR> Rem that processes are waiting for. This shows the
    total time
    spent
    SVRMGR> Rem waiting for a specific event and the average time
    per wait
    on
    SVRMGR> Rem that event.
    SVRMGR> select n1.event "Event Name",
    2> n1.event_count "Count",
    3> n1.time_waited "Total Time",
    4> round(n1.time_waited/n1.event_count, 2) "Avg Time"
    5> from stats$event n1
    6> where n1.event_count > 0
    7> order by n1.time_waited desc;
    Event Name Count Total Time Avg
    Time
    >
    -------------------------------- ------------- -------------
    -------------
    SQL*Net message from client 10856276 31977110 2.95
    enqueue 1295 374980 289.56
    db file sequential read 3614044 303848 .08
    write complete waits 5812 295937 50.92
    latch free 5045060 242170 .05
    SQL*Net more data from client 13939 165275 11.86
    log file sync 12794 146409 11.44
    buffer busy waits 100443 92477
    .92
    > >
    > >
    > >
    - ALL Literal SQLs were Converted to using Bind variables
    - ALL Tables Running on Indexes Without Any FULL Scans happening
    .
    > >
    - All the Literal SQLs (Dynamic) Converted to using Bind
    variables
    (Static
    Queries)
    > >
    - event="10181 trace name context forever, level 1000"
    NOTE - Set nevertheless, None of the SQLs taking Excessive time
    to
    parse
    though
    > >
    - NO statistics are Analyzed
    - 7 tables involved in the interest Calc.
    inserts to 2 Tables
    selects, updates in 5 tables
    > >
    Qs. Are there Any BASIC O.S./Hardware Features to Enhance
    Performance on
    IBM
    AIX Systems ?
    > >
    Qs. Is Any Other portion of the report.txt required for perusal
    ?
    > >
    Please Revert to me for any further info / Clarifications ?
    > >
    > >
    > >
    << File: report.txt_Live >> << File: report.txt_TEST >>
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: VIVEK_SHARMA
    INET: VIVEK_SHARMA_at_infy.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    ------_=_NextPart_001_01C06AC2.46995EF0
    Content-Type: text/html;
    charset="iso-8859-1"

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

    RE: SOS Alert

    4K striping is a major Major MAJOR mistake.

    Doing it in software makes it at least 3x as
    bad.

    Either fix alone would have provided at least a
    2-4x improvement.

    Given a choice, I'd fix the stripe size first, then
    convert the mirroring to hardware.

    hth

    imho

    ymmv

    afaik,

    etc.

    - Ross Mohan

    -----Original Message-----
    From: VIVEK_SHARMA [mailto:VIVEK_SHARMA_at_infy.com]
    Sent: Wednesday, December 20, 2000 11:13 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert

    CASE In a Bank, Interest Calculation Batch Processing Unable to go beyond
    500 A/cs per minute

    ACTION Moved the Database to Another IBM Machine ( 4 CPU, 4 GB RAM ) with
    HARDWARE Mirroring &amp; 64 K Stripe Size

    NOTE - On the Under-performing DB Server S80 Model of IBM) there exists
    Software Mirroring (By the AIX O.S.) &amp; a Stripe Size of 4K exist

    Firing Off the Interest Calculation Runs from 2 APP Servers Giving an
    Overall Total&nbsp; Processing of Number of 3000 A/cs per minute ( 1500 from each
    )

    Qs. Which of the 2 i.e. Hardware Mirroring OR 64 K Striping, do you think
    is the Cause of the Increase in performance as we put BOTH features Together
    at the SAME Time OR do Both Contribute Equally ?

    Thanks to Vivek for everything

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -----Original Message-----
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; From:&nbsp;&nbsp; VIVEK_SHARMA
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sent:&nbsp;&nbsp; Thursday, December 07, 2000 6:35 PM
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Subject:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RE: SOS Alert

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ACTION Identified the 2 indexes having the waits of &quot;db file
    sequential read&quot;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Created 2 NEW Tablespaces Each containing 1 Index
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Also Specified parameter log_small_entry_max_size = 0
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOTE - ORA 7.3.4.5

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RESULT Performance thruput increased to 1500 A/cs per minute from
    1000 A/cs per minute

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ==============================================
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Allow me to begin :-
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; There were 2 routes in between the APP &amp; DB Servers .
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; One had a Bandwidth of 10 MBPS while Another had a Bandwidth of 100
    MBPS
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The 10 MBPS route was being used all this while . On Enabling the
    100 MBPS route
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (by Specifying the I.P. Address Instead of Hostname in the
    tnsnames.ora &amp; listener.ora)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; the performance has jumped to 1100 records per minute (from 500
    records per minute)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.e. Almost Doubled .
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100 MBPS is the MAX Possible Bandwidth Possible as per the SA

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Qs. Your Views on this please

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Additional responses Follow your Views BELOW :-

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -----Original Message-----
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; From:&nbsp;&nbsp; Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sent:&nbsp;&nbsp; Tuesday, November 28, 2000 6:30 PM
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; To:&nbsp;&nbsp;&nbsp;&nbsp; 'VIVEK_SHARMA'
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cc:&nbsp;&nbsp;&nbsp;&nbsp; Arun Chakrapani
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Subject:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RE: SOS Alert

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Vivek:

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; Sorry for not replying to you yesterday itself. A Busy day. Took a
    look at
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; you report.txt. They were having 2 different profiles. Since You had
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mentioned that it also stalled in&nbsp; Test DB I concentrated more on
    the test
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; report.txt. One thing which stood out on the other one (live) on
    opening you
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; report.txt is the SQL _AREA GET_HIT_RATIO is very poor. I don't know
    whether
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; you have already pinned your SYS objects and other objects that are
    executed
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; often in shared pool. Please use DBMS_SHARED_POOL.keep to pin
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; package,procedure,function,sequence,cursors, triggers that are used
    often.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ans OK will do so

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; So now I will concentrate on the Test DB.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1. Even though the SQL_AREA GET_HIT_RATIO is higer(82%) when
    compared to
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; live this is not good enough. It should be close to 100%(1 some
    where like
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .98 alteast). Please pin the SYS objects to start with.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OK
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Allow me to say that the shared_pool_size has by experience been
    kept at bare minimum required
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for we did experience performance poblems with a shred_pool_size of
    300 MB
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( Though on Another machine on SUN OS )
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; It is recommended to keep shared_pool_size at rock bottom just above
    the Limit
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where ORA-4031 out of shared pool segments occurs

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2. On seeing you system wide wait events the following stands out.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1.log file sync&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2863&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 83922&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29.31
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 2.write complete waits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 905&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 71401&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 78.9
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 3.free buffer waits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4718&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 78.63

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; The first event log file sync event mostly comes when there is a
    disk
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i/o bottleneck for the online redo logs.This happens when the
    processes have
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to wait for the LGWR to sync a particular REDO log. The p1 parameter
    in a
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Log sync in the corresponding LOg Buffer.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The Write complete waits comes
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when the user process is waiting for the LGWR to complete the
    writing of the
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; redo information.&nbsp; So Please take a look at the placing of the
    online redo
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; log. In which disk have you placed this. Also if it has been place
    in its
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; own file system it doesn't mean that it is not going to a busy disk.
    Since
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; you have 0+1, The slices may belong to a HOT disk. What you need to
    do is
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ask your SA to monitor the disk I/O usage and see whats happening to
    the
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; disk containing the redo log file. Also you can use iostat -xPnce 5
    5 (I
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; think its available in AIX). the output will look something like

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; us sy wt id
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23&nbsp; 7 49 21
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; extended device statistics ---- errors
    ---
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; r/s&nbsp; w/s&nbsp;&nbsp; kr/s&nbsp;&nbsp; kw/s wait actv wsvc_t asvc_t&nbsp; %w&nbsp; %b s/w h/w trn
    tot
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; device
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp; 13.4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1t10d0s0
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 2.4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1t10d0s1
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1t10d0s2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0.0&nbsp; 1.3&nbsp;&nbsp;&nbsp; 0.3&nbsp;&nbsp; 10.1&nbsp; 0.0&nbsp; 0.1&nbsp;&nbsp;&nbsp; 4.6&nbsp; 105.8&nbsp;&nbsp; 0&nbsp;&nbsp; 1&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1t10d0s3
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Watch out for %b column. If its somewhere around 60 then you may be
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; experiencing contention on those slices. Ask you SA by giving
    him/her the
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; device name and they will be able to give you what the FS is.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOTE - 4 Datafiles Containing a Particular Table &amp; it's
    Corresponding indexes (to Which INSERTS
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; happen ) were Moved out of the RAID onto a Local Disk whereafter
    performance Jumped from
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 850 per minute to 1100&nbsp; per minute

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ans - OK . Will Issue the Command

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Also I see
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Free Buffer waits. This is caused because there are no free buffers
    when
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; they are requested - pointing directly to the inefficiency of the
    DBWR.
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Again the DBWR may be slow because you have a I/O bottleneck for the
    DB
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; files and indirectly because of the Write complete waits.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Qs. Would you recommend Setting Multiple db_writer processes
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; even though async_io is set to true ?

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &quot; Thus a Volume Group Contains 8 Disks with 4 Disks
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Stripe Size = 4K &quot;

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Vivek]&nbsp; Please confirm me that the Stripe size is 4K. Usually you
    will have
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a stripe size of 32K,64K,128K,256K .... If you are having a stripe
    size of
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4K and your db_block_size as 8K, It is going to take 2 reads to
    satisfy one
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; request for a db_file_sequential_read (Usually requests 1 Oracle
    block)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Vivek]&nbsp; . This doesn't sound good if thats true.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOTE - The &quot;test&quot; Database is a 4K DB_BLOCK_SIZE while the Live
    Database is
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; an 8K DB_BLOCK_SIZE, But the SAME performance Limits were Observed
    on Both

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; So if possible could you please do the following.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1. Please shutdown the Test db and start fresh.
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2. Run the same process again and also run the iostat -xPnce 60
    10000 for
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; the duration of the run (output this to a file)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3. Run vmstat and see whats the CPU usage for USER and SYSTEM
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4. When the 500 A/c per sec reaches - Please run

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set lines 132
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select sid, substr(event,1,40),p1,p2,p3 from v$session_wait
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where event in ('db file scattered read','db file sequential
    read','latch
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; free','buffer busy waits','log file sync','db file scattered
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; read','enqueue');

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and give me the output.&nbsp;

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OK - Will Action &amp; revert to you

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5. IF may waits are showing as &quot;latch free&quot; in the event column of
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; v$session_wait, Please query v$latch for
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; latch#,name,level,GETS,MISSES,SLEEPS IMMEDIATE_GETS
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,WAITS_HOLDING_LATCH, SPIN_GETS, SLEEP1.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ans OK

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6. Preserve the output for v$latch_children.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ans OK

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Qs. Are their any IBM OS Specific Parameters which are considered
    benefitial for performance ?

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I will take a look at both the report.txt in more detail today and
    get back
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to you.

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thanks Again so much
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Where are you Located&nbsp; / Phone No ? if you please
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; We are Currently Located in Bangalore &amp; Addressing The Problem at
    Mumbai over a WAN
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Phone 91 80 6588668

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HTH

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Vivek

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; -----Original Message-----
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Sent: Monday, November 27, 2000 11:16 AM
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; To:&nbsp;&nbsp; 'Vivek'
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Cc:&nbsp;&nbsp; Arun Chakrapani
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Subject:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RE: SOS Alert
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; THANKS so much for the association in this issue
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Attached 2 report.txt each taken for 1 Hour approx . Is it
    Sufficient ?
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Attached&nbsp; report.txt of&nbsp; Problem on the &quot;Live&quot; Database
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;&nbsp; &lt;&lt;report.txt_Live&gt;&gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; NOTE - A &quot;test&quot; Database&nbsp; Created on the DB Server Itself&nbsp; in the
    Same
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Partitions as the &quot;Live&quot; Database
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Runs Done on this &quot;test&quot; Database Also failing to go beyond 500
    A/cs per
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; minute
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;&nbsp; &lt;&lt;report.txt_TEST&gt;&gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; NOTE - Interest Runs were Done on the &quot;Test&quot; Database During
    Down-Time
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Periods of the &quot;Live&quot; Database
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; You Wrote :-
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Since there is some write complete waits Please take a look
    at the
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; place the on line redo logs are and what is the read, write on
    these
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; disks.
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Ans Online Redo logs ALSO present on the RAID 0+1 Disks &amp; are
    Multiplexed
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Size of Online Redo logfile on the &quot;Live&quot; Database = 30 MB
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; NOTE - On the test Database we increased the Size of the Online
    Redo
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; logfile
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; to 100MB but with NO benefit
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; You wrote :-
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Please give me the DB Version as well
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Ans Database Oracle&nbsp; version = 7.3.4.5.0
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; You wrote :-
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I see some waits on More data from client.
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Qs. What is to be done about the Same ?
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; NOTE - Will Run the 2 SQL Scripts Suggested by you &amp; revert back
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Thanks Again
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; -----Original Message-----
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; From:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Sent:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Monday, November 27, 2000 7:44 PM
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; To: 'VIVEK_SHARMA_at_infy.com'
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Cc: Arun Chakrapani
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Subject:&nbsp;&nbsp;&nbsp; SOS Alert
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Hi Vivek:
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Will it be possible to send the whole report.txt to me. Since
    you have
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; not
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; given for how long this was taken (time from utlbstat and
    utlestat) it
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; is
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; more difficult to say anything.&nbsp; Based on the report.txt snippet
    you
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; have
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; given there seems to me there are some issues with the enqueues
    and
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; latch
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; free waits. Please run the following SQL as SYS so bet' 2
    different
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; times
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; (
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; say 1/2 apart) and send me both the values(It will be great if
    you could
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; run
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; this when the batch processing is running).
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; select KSQSTTYP,KSQSTGET,KSQSTWAT from x$ksqst where KSQSTGET&gt;0
    and
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; KSQSTWAT
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; &gt;0;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; The delta bet' the 2 results will give you information on what
    is the
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; enqueue that we see more waits on. Also Please run this sql
    given below
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; in
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; a
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; cron every minute or so and output this to a file
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SELECT p1 &quot;File&quot;, p2 &quot;Block&quot;, p3 &quot;Reason&quot;&nbsp; FROM v$session_wait
    WHERE
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; event
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; like&nbsp; 'buffer busy waits'&nbsp;;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; This will give you and idea about the buffer busy waits. Since
    there is
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; some
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; write complete waits Please take a look at the place the on line
    redo
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; logs
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; are and what is the read, write on these disks. Also Please give
    me
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; infomation on the relog file size. Please give me the DB Version
    as
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; well.
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; I
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; see some waits on More data from client. Again since I don't
    know for
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; how
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; long the stats were run can't say whether they are significant
    or not.
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; HTH
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Vivek
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; From: VIVEK_SHARMA &lt;VIVEK_SHARMA_at_infy.com&gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp; Date: Mon, 27 Nov 2000 12:11:59 +0530
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp; Subject: SOS Alert
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; CASE In a Bank, Interest Calculation Batch Processing Unable to
    go
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; beyond
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; 500 A/cs per minute
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; CAUSE of the Problem is UNKNOWN
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; ORACLE 7.3.4.5 on AIX 4.3.3
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; DB Server - IBM S80 Model - 12 CPUs, 3 GB RAM
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; APP Server 1 - IBM S80 Model - 6 CPUs, 2 GB RAM
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; APP Servers 2 &amp; 3 - IBM Thin Nodes model - 4 CPUs, 1 GB RAM
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Storage Box :-
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; ===========
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SS Class Storage
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; RAID 0+1 - (First Striped &amp; then Mirrored)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; NOTE - 2 Storage Boxes Exist, one being the Mirror of the Other
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Striping exists across a set 4 Disks (in one Box) with another 4
    being
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; it's
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; mirror
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; (in another Box).
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Thus a Volume Group Contains 8 Disks with 4 Disks
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Stripe Size = 4K
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; NOTE - Runs Tried from BOTH the APP Servers 1 OR 2,But with the
    SAME
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; MAX
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; of
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; 500 A/cs
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; processed per minute
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; CPU Utilizations on BOTH APP &amp; DB Server = 40 %
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; wio% on BOTH APP &amp; DB Servers = 35 %
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; No paging happening on Both APP &amp; DB Servers
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - Oracle Contention Values Seem Small to us as shown Below or so
    they
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; seem
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; to us :-
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem System wide wait events for non-background processes
    (PMON,
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem SMON, etc).&nbsp; Times are in hundreths of seconds.
    Each one of
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem these is a context switch which costs CPU time.&nbsp; By
    looking
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; at
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem the Total Time you can often determine what is the
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; bottleneck
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem that processes are waiting for.&nbsp; This shows the
    total time
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; spent
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem waiting for a specific event and the average time
    per wait
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; on
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem that event.
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; select &nbsp;&nbsp;&nbsp;&nbsp; n1.event &quot;Event Name&quot;,
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; n1.event_count &quot;Count&quot;,
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&gt; &nbsp;&nbsp;&nbsp; n1.time_waited &quot;Total Time&quot;,
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&gt; &nbsp;&nbsp;&nbsp; round(n1.time_waited/n1.event_count, 2) &quot;Avg Time&quot;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&gt;&nbsp;&nbsp;&nbsp; from stats$event n1
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&gt;&nbsp;&nbsp;&nbsp; where n1.event_count &gt; 0
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7&gt;&nbsp;&nbsp;&nbsp; order by n1.time_waited desc;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Event Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Total Time&nbsp;&nbsp;&nbsp; Avg
    Time
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; -------------------------------- ------------- -------------
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; -------------
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SQL*Net message from client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10856276&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31977110
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 2.95
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; enqueue&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1295&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 374980
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 289.56
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; db file sequential read&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3614044&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 303848
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; .08
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; write complete waits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5812&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 295937
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 50.92
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; latch free&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5045060&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 242170
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; .05
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SQL*Net more data from client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13939&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 165275
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 11.86
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; log file sync&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12794&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 146409
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 11.44
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; buffer busy waits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100443&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 92477
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; .92
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - ALL Literal SQLs were Converted to using Bind variables
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - ALL Tables Running on Indexes Without Any FULL Scans happening
    .
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - All the Literal SQLs (Dynamic) Converted to using Bind
    variables
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; (Static
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Queries)
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - event=&quot;10181 trace name context forever, level 1000&quot;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; NOTE - Set nevertheless, None of the SQLs taking Excessive time
    to
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; parse
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; though
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - NO statistics are Analyzed
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - 7 tables involved in the interest Calc.
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; inserts to 2 Tables
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; selects, updates in 5 tables
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Qs. Are there Any BASIC O.S./Hardware Features to Enhance
    Performance on
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; IBM
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; AIX Systems ?
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Qs. Is Any Other portion of the report.txt required for perusal
    ?
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Please Revert to me for any further info / Clarifications ?
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp; &lt;&lt; File: report.txt_Live &gt;&gt;&nbsp; &lt;&lt; File: report.txt_TEST &gt;&gt;
  • VIVEK_SHARMA at Dec 21, 2000 at 9:42 pm
    BIG THANKS TO VIVEK, MOHAN ROSS, DAVID & ALL
    -----Original Message-----
    From: Mohan, Ross [SMTP:MohanR_at_STARS-SMI.com]
    Sent: Thursday, December 21, 2000 1:51 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert

    4K striping is a major Major MAJOR mistake.

    Doing it in software makes it at least 3x as
    bad.

    Either fix alone would have provided at least a
    2-4x improvement.

    Given a choice, I'd fix the stripe size first, then
    convert the mirroring to hardware.

    hth

    imho

    ymmv

    afaik,

    etc.

    - Ross Mohan

    -----Original Message-----
    From: VIVEK_SHARMA [ ]
    Sent: Wednesday, December 20, 2000 11:13 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert


    CASE In a Bank, Interest Calculation Batch Processing Unable to go beyond

    500 A/cs per minute

    ACTION Moved the Database to Another IBM Machine ( 4 CPU, 4 GB RAM ) with

    HARDWARE Mirroring & 64 K Stripe Size

    NOTE - On the Under-performing DB Server S80 Model of IBM) there exists
    Software Mirroring (By the AIX O.S.) & a Stripe Size of 4K exist

    Firing Off the Interest Calculation Runs from 2 APP Servers Giving an
    Overall Total Processing of Number of 3000 A/cs per minute ( 1500 from
    each
    )

    Qs. Which of the 2 i.e. Hardware Mirroring OR 64 K Striping, do you think

    is the Cause of the Increase in performance as we put BOTH features
    Together
    at the SAME Time OR do Both Contribute Equally ?

    Thanks to Vivek for everything


    -----Original Message-----
    From: VIVEK_SHARMA
    Sent: Thursday, December 07, 2000 6:35 PM
    Subject: RE: SOS Alert



    ACTION Identified the 2 indexes having the waits of "db file
    sequential read"
    Created 2 NEW Tablespaces Each containing 1 Index
    Also Specified parameter log_small_entry_max_size = 0
    NOTE - ORA 7.3.4.5
    RESULT Performance thruput increased to 1500 A/cs per minute from
    1000 A/cs per minute


    ==============================================
    Allow me to begin :-
    There were 2 routes in between the APP & DB Servers .
    One had a Bandwidth of 10 MBPS while Another had a Bandwidth of
    100
    MBPS
    The 10 MBPS route was being used all this while . On Enabling the
    100 MBPS route
    (by Specifying the I.P. Address Instead of Hostname in the
    tnsnames.ora & listener.ora)
    the performance has jumped to 1100 records per minute (from 500
    records per minute)
    i.e. Almost Doubled .
    100 MBPS is the MAX Possible Bandwidth Possible as per the SA

    Qs. Your Views on this please

    Additional responses Follow your Views BELOW :-

    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Tuesday, November 28, 2000 6:30 PM
    To: 'VIVEK_SHARMA'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert

    Vivek:

    Sorry for not replying to you yesterday itself. A Busy day. Took
    a
    look at
    you report.txt. They were having 2 different profiles. Since You
    had
    mentioned that it also stalled in Test DB I concentrated more on
    the test
    report.txt. One thing which stood out on the other one (live) on
    opening you
    report.txt is the SQL _AREA GET_HIT_RATIO is very poor. I don't
    know
    whether
    you have already pinned your SYS objects and other objects that
    are
    executed
    often in shared pool. Please use DBMS_SHARED_POOL.keep to pin
    package,procedure,function,sequence,cursors, triggers that are
    used
    often.

    Ans OK will do so

    So now I will concentrate on the Test DB.

    1. Even though the SQL_AREA GET_HIT_RATIO is higer(82%) when
    compared to
    live this is not good enough. It should be close to 100%(1 some
    where like
    .98 alteast). Please pin the SYS objects to start with.

    OK
    Allow me to say that the shared_pool_size has by experience been
    kept at bare minimum required
    for we did experience performance poblems with a shred_pool_size
    of
    300 MB
    ( Though on Another machine on SUN OS )
    It is recommended to keep shared_pool_size at rock bottom just
    above
    the Limit
    where ORA-4031 out of shared pool segments occurs


    2. On seeing you system wide wait events the following stands out.


    1.log file sync 2863 83922
    29.31
    2.write complete waits 905 71401 78.9

    3.free buffer waits 60 4718
    78.63

    The first event log file sync event mostly comes when there is
    a
    disk
    i/o bottleneck for the online redo logs.This happens when the
    processes have
    to wait for the LGWR to sync a particular REDO log. The p1
    parameter
    in a
    Log sync in the corresponding LOg Buffer.


    The Write complete waits comes
    when the user process is waiting for the LGWR to complete the
    writing of the
    redo information. So Please take a look at the placing of the
    online redo
    log. In which disk have you placed this. Also if it has been place

    in its
    own file system it doesn't mean that it is not going to a busy
    disk.
    Since
    you have 0+1, The slices may belong to a HOT disk. What you need
    to
    do is
    ask your SA to monitor the disk I/O usage and see whats happening
    to
    the
    disk containing the redo log file. Also you can use iostat -xPnce
    5
    5 (I
    think its available in AIX). the output will look something like

    us sy wt id
    23 7 49 21
    extended device statistics ----
    errors
    ---
    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b s/w h/w
    trn
    tot
    device
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 13.4 0 0 0 0


    c1t10d0s0
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.4 0 0 0 0


    c1t10d0s1
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0


    c1t10d0s2
    0.0 1.3 0.3 10.1 0.0 0.1 4.6 105.8 0 1 0 0


    c1t10d0s3
    Watch out for %b column. If its somewhere around 60 then you may
    be
    experiencing contention on those slices. Ask you SA by giving
    him/her the
    device name and they will be able to give you what the FS is.

    NOTE - 4 Datafiles Containing a Particular Table & it's
    Corresponding indexes (to Which INSERTS
    happen ) were Moved out of the RAID onto a Local Disk whereafter
    performance Jumped from
    850 per minute to 1100 per minute

    Ans - OK . Will Issue the Command

    Also I see
    Free Buffer waits. This is caused because there are no free
    buffers
    when
    they are requested - pointing directly to the inefficiency of the
    DBWR.
    Again the DBWR may be slow because you have a I/O bottleneck for
    the
    DB
    files and indirectly because of the Write complete waits.

    Qs. Would you recommend Setting Multiple db_writer processes
    even though async_io is set to true ?


    " Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K "

    [Vivek] Please confirm me that the Stripe size is 4K. Usually you

    will have
    a stripe size of 32K,64K,128K,256K .... If you are having a stripe

    size of
    4K and your db_block_size as 8K, It is going to take 2 reads to
    satisfy one
    request for a db_file_sequential_read (Usually requests 1 Oracle
    block)
    [Vivek] . This doesn't sound good if thats true.

    NOTE - The "test" Database is a 4K DB_BLOCK_SIZE while the Live
    Database is
    an 8K DB_BLOCK_SIZE, But the SAME performance Limits were
    Observed
    on Both

    So if possible could you please do the following.

    1. Please shutdown the Test db and start fresh.
    2. Run the same process again and also run the iostat -xPnce 60
    10000 for
    the duration of the run (output this to a file)
    3. Run vmstat and see whats the CPU usage for USER and SYSTEM
    4. When the 500 A/c per sec reaches - Please run

    set lines 132
    select sid, substr(event,1,40),p1,p2,p3 from v$session_wait
    where event in ('db file scattered read','db file sequential
    read','latch
    free','buffer busy waits','log file sync','db file scattered
    read','enqueue');

    and give me the output.

    OK - Will Action & revert to you

    5. IF may waits are showing as "latch free" in the event column of

    v$session_wait, Please query v$latch for
    latch#,name,level,GETS,MISSES,SLEEPS IMMEDIATE_GETS
    ,WAITS_HOLDING_LATCH, SPIN_GETS, SLEEP1.
    Ans OK

    6. Preserve the output for v$latch_children.

    Ans OK

    Qs. Are their any IBM OS Specific Parameters which are considered
    benefitial for performance ?


    I will take a look at both the report.txt in more detail today and

    get back
    to you.

    Thanks Again so much
    Where are you Located / Phone No ? if you please
    We are Currently Located in Bangalore & Addressing The Problem at
    Mumbai over a WAN
    Phone 91 80 6588668

    HTH
    Vivek
    -----Original Message-----
    From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
    Sent: Monday, November 27, 2000 11:16 AM
    To: 'Vivek'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert

    THANKS so much for the association in this issue

    Attached 2 report.txt each taken for 1 Hour approx . Is it
    Sufficient ?
    Attached report.txt of Problem on the "Live" Database

    <>
    NOTE - A "test" Database Created on the DB Server Itself in
    the
    Same
    Partitions as the "Live" Database
    Runs Done on this "test" Database Also failing to go beyond 500 A/cs per
    minute


    <>

    NOTE - Interest Runs were Done on the "Test" Database During Down-Time
    Periods of the "Live" Database

    You Wrote :-
    Since there is some write complete waits Please take a
    look
    at the
    place the on line redo logs are and what is the read, write on these
    disks.

    Ans Online Redo logs ALSO present on the RAID 0+1 Disks & are
    Multiplexed
    Size of Online Redo logfile on the "Live" Database = 30 MB

    NOTE - On the test Database we increased the Size of the Online Redo
    logfile
    to 100MB but with NO benefit

    You wrote :-
    Please give me the DB Version as well

    Ans Database Oracle version = 7.3.4.5.0

    You wrote :-
    I see some waits on More data from client.
    Qs. What is to be done about the Same ?

    NOTE - Will Run the 2 SQL Scripts Suggested by you & revert back
    Thanks Again

    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Monday, November 27, 2000 7:44 PM
    To: 'VIVEK_SHARMA_at_infy.com'

    Cc: Arun Chakrapani
    Subject: SOS Alert

    Hi Vivek:

    Will it be possible to send the whole report.txt to me. Since
    you have
    not
    given for how long this was taken (time from utlbstat and
    utlestat) it
    is
    more difficult to say anything. Based on the report.txt
    snippet
    you
    have
    given there seems to me there are some issues with the
    enqueues
    and
    latch
    free waits. Please run the following SQL as SYS so bet' 2
    different
    times
    (
    say 1/2 apart) and send me both the values(It will be great if
    you could
    run
    this when the batch processing is running).

    select KSQSTTYP,KSQSTGET,KSQSTWAT from x$ksqst where
    KSQSTGET>0
    and
    KSQSTWAT
    0;
    The delta bet' the 2 results will give you information on what
    is the
    enqueue that we see more waits on. Also Please run this sql
    given below
    in
    a
    cron every minute or so and output this to a file

    SELECT p1 "File", p2 "Block", p3 "Reason" FROM v$session_wait
    WHERE
    event
    like 'buffer busy waits';

    This will give you and idea about the buffer busy waits. Since
    there is
    some
    write complete waits Please take a look at the place the on
    line
    redo
    logs
    are and what is the read, write on these disks. Also Please
    give
    me
    infomation on the relog file size. Please give me the DB
    Version
    as
    well.
    I
    see some waits on More data from client. Again since I don't
    know for
    how
    long the stats were run can't say whether they are significant
    or not.
    HTH
    Vivek



    From: VIVEK_SHARMA
    Date: Mon, 27 Nov 2000 12:11:59 +0530
    Subject: SOS Alert

    CASE In a Bank, Interest Calculation Batch Processing Unable
    to
    go
    beyond
    500 A/cs per minute

    CAUSE of the Problem is UNKNOWN

    ORACLE 7.3.4.5 on AIX 4.3.3
    DB Server - IBM S80 Model - 12 CPUs, 3 GB RAM
    APP Server 1 - IBM S80 Model - 6 CPUs, 2 GB RAM
    APP Servers 2 & 3 - IBM Thin Nodes model - 4 CPUs, 1 GB RAM

    Storage Box :-
    ===========
    SS Class Storage
    RAID 0+1 - (First Striped & then Mirrored)
    NOTE - 2 Storage Boxes Exist, one being the Mirror of the
    Other
    Striping exists across a set 4 Disks (in one Box) with another
    4
    being
    it's
    mirror
    (in another Box).
    Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K

    NOTE - Runs Tried from BOTH the APP Servers 1 OR 2,But with
    the
    SAME
    MAX
    of
    500 A/cs
    processed per minute

    CPU Utilizations on BOTH APP & DB Server = 40 %
    wio% on BOTH APP & DB Servers = 35 %
    No paging happening on Both APP & DB Servers

    - Oracle Contention Values Seem Small to us as shown Below or
    so
    they
    seem
    to us :-

    SVRMGR> Rem System wide wait events for non-background
    processes
    (PMON,
    SVRMGR> Rem SMON, etc). Times are in hundreths of seconds.
    Each one of
    SVRMGR> Rem these is a context switch which costs CPU time.
    By
    looking
    at
    SVRMGR> Rem the Total Time you can often determine what is the
    bottleneck
    SVRMGR> Rem that processes are waiting for. This shows the
    total time
    spent
    SVRMGR> Rem waiting for a specific event and the average time
    per wait
    on
    SVRMGR> Rem that event.
    SVRMGR> select n1.event "Event Name",
    2> n1.event_count "Count",
    3> n1.time_waited "Total Time",
    4> round(n1.time_waited/n1.event_count, 2) "Avg Time"
    5> from stats$event n1
    6> where n1.event_count > 0
    7> order by n1.time_waited desc;
    Event Name Count Total Time
    Avg
    Time
    -------------------------------- ------------- -------------
    -------------
    SQL*Net message from client 10856276 31977110 2.95
    enqueue 1295 374980 289.56
    db file sequential read 3614044 303848 .08
    write complete waits 5812 295937 50.92
    latch free 5045060 242170 .05
    SQL*Net more data from client 13939 165275 11.86
    log file sync 12794 146409 11.44
    buffer busy waits 100443 92477 .92


    - ALL Literal SQLs were Converted to using Bind variables
    - ALL Tables Running on Indexes Without Any FULL Scans
    happening
    .
    - All the Literal SQLs (Dynamic) Converted to using Bind
    variables
    (Static
    Queries)

    - event="10181 trace name context forever, level 1000"
    NOTE - Set nevertheless, None of the SQLs taking Excessive
    time
    to
    parse
    though

    - NO statistics are Analyzed
    - 7 tables involved in the interest Calc.
    inserts to 2 Tables
    selects, updates in 5 tables

    Qs. Are there Any BASIC O.S./Hardware Features to Enhance
    Performance on
    IBM
    AIX Systems ?

    Qs. Is Any Other portion of the report.txt required for
    perusal
    ?
    Please Revert to me for any further info / Clarifications ?



    << File: report.txt_Live >> << File: report.txt_TEST >>
    --
    Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
    --
    Author: VIVEK_SHARMA
  • VIVEK_SHARMA at Dec 22, 2000 at 6:26 am
    A related Basic Qs.

    Can the Application Software ( Banking Application of pro*c Executables in
    this Case) be put on Striped Disks belonging to the APP Server ?

    Does it Have any Certain Negative performance impacts ?
    OR is Performance the Same whether the Application Lies on Striped Disks or
    NON-Striped Disks ?
    -----Original Message-----
    From: Mohan, Ross [SMTP:MohanR_at_STARS-SMI.com]
    Sent: Thursday, December 21, 2000 1:51 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert

    4K striping is a major Major MAJOR mistake.

    Doing it in software makes it at least 3x as
    bad.

    Either fix alone would have provided at least a
    2-4x improvement.

    Given a choice, I'd fix the stripe size first, then
    convert the mirroring to hardware.

    hth

    imho

    ymmv

    afaik,

    etc.

    - Ross Mohan

    -----Original Message-----
    From: VIVEK_SHARMA [ ]
    Sent: Wednesday, December 20, 2000 11:13 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert


    CASE In a Bank, Interest Calculation Batch Processing Unable to go beyond

    500 A/cs per minute

    ACTION Moved the Database to Another IBM Machine ( 4 CPU, 4 GB RAM ) with

    HARDWARE Mirroring & 64 K Stripe Size

    NOTE - On the Under-performing DB Server S80 Model of IBM) there exists
    Software Mirroring (By the AIX O.S.) & a Stripe Size of 4K exist

    Firing Off the Interest Calculation Runs from 2 APP Servers Giving an
    Overall Total Processing of Number of 3000 A/cs per minute ( 1500 from
    each
    )

    Qs. Which of the 2 i.e. Hardware Mirroring OR 64 K Striping, do you think

    is the Cause of the Increase in performance as we put BOTH features
    Together
    at the SAME Time OR do Both Contribute Equally ?

    Thanks to Vivek for everything


    -----Original Message-----
    From: VIVEK_SHARMA
    Sent: Thursday, December 07, 2000 6:35 PM
    Subject: RE: SOS Alert



    ACTION Identified the 2 indexes having the waits of "db file
    sequential read"
    Created 2 NEW Tablespaces Each containing 1 Index
    Also Specified parameter log_small_entry_max_size = 0
    NOTE - ORA 7.3.4.5
    RESULT Performance thruput increased to 1500 A/cs per minute from
    1000 A/cs per minute


    ==============================================
    Allow me to begin :-
    There were 2 routes in between the APP & DB Servers .
    One had a Bandwidth of 10 MBPS while Another had a Bandwidth of
    100
    MBPS
    The 10 MBPS route was being used all this while . On Enabling the
    100 MBPS route
    (by Specifying the I.P. Address Instead of Hostname in the
    tnsnames.ora & listener.ora)
    the performance has jumped to 1100 records per minute (from 500
    records per minute)
    i.e. Almost Doubled .
    100 MBPS is the MAX Possible Bandwidth Possible as per the SA

    Qs. Your Views on this please

    Additional responses Follow your Views BELOW :-

    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Tuesday, November 28, 2000 6:30 PM
    To: 'VIVEK_SHARMA'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert

    Vivek:

    Sorry for not replying to you yesterday itself. A Busy day. Took
    a
    look at
    you report.txt. They were having 2 different profiles. Since You
    had
    mentioned that it also stalled in Test DB I concentrated more on
    the test
    report.txt. One thing which stood out on the other one (live) on
    opening you
    report.txt is the SQL _AREA GET_HIT_RATIO is very poor. I don't
    know
    whether
    you have already pinned your SYS objects and other objects that
    are
    executed
    often in shared pool. Please use DBMS_SHARED_POOL.keep to pin
    package,procedure,function,sequence,cursors, triggers that are
    used
    often.

    Ans OK will do so

    So now I will concentrate on the Test DB.

    1. Even though the SQL_AREA GET_HIT_RATIO is higer(82%) when
    compared to
    live this is not good enough. It should be close to 100%(1 some
    where like
    .98 alteast). Please pin the SYS objects to start with.

    OK
    Allow me to say that the shared_pool_size has by experience been
    kept at bare minimum required
    for we did experience performance poblems with a shred_pool_size
    of
    300 MB
    ( Though on Another machine on SUN OS )
    It is recommended to keep shared_pool_size at rock bottom just
    above
    the Limit
    where ORA-4031 out of shared pool segments occurs


    2. On seeing you system wide wait events the following stands out.


    1.log file sync 2863 83922
    29.31
    2.write complete waits 905 71401 78.9

    3.free buffer waits 60 4718
    78.63

    The first event log file sync event mostly comes when there is
    a
    disk
    i/o bottleneck for the online redo logs.This happens when the
    processes have
    to wait for the LGWR to sync a particular REDO log. The p1
    parameter
    in a
    Log sync in the corresponding LOg Buffer.


    The Write complete waits comes
    when the user process is waiting for the LGWR to complete the
    writing of the
    redo information. So Please take a look at the placing of the
    online redo
    log. In which disk have you placed this. Also if it has been place

    in its
    own file system it doesn't mean that it is not going to a busy
    disk.
    Since
    you have 0+1, The slices may belong to a HOT disk. What you need
    to
    do is
    ask your SA to monitor the disk I/O usage and see whats happening
    to
    the
    disk containing the redo log file. Also you can use iostat -xPnce
    5
    5 (I
    think its available in AIX). the output will look something like

    us sy wt id
    23 7 49 21
    extended device statistics ----
    errors
    ---
    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b s/w h/w
    trn
    tot
    device
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 13.4 0 0 0 0


    c1t10d0s0
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.4 0 0 0 0


    c1t10d0s1
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0


    c1t10d0s2
    0.0 1.3 0.3 10.1 0.0 0.1 4.6 105.8 0 1 0 0


    c1t10d0s3
    Watch out for %b column. If its somewhere around 60 then you may
    be
    experiencing contention on those slices. Ask you SA by giving
    him/her the
    device name and they will be able to give you what the FS is.

    NOTE - 4 Datafiles Containing a Particular Table & it's
    Corresponding indexes (to Which INSERTS
    happen ) were Moved out of the RAID onto a Local Disk whereafter
    performance Jumped from
    850 per minute to 1100 per minute

    Ans - OK . Will Issue the Command

    Also I see
    Free Buffer waits. This is caused because there are no free
    buffers
    when
    they are requested - pointing directly to the inefficiency of the
    DBWR.
    Again the DBWR may be slow because you have a I/O bottleneck for
    the
    DB
    files and indirectly because of the Write complete waits.

    Qs. Would you recommend Setting Multiple db_writer processes
    even though async_io is set to true ?


    " Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K "

    [Vivek] Please confirm me that the Stripe size is 4K. Usually you

    will have
    a stripe size of 32K,64K,128K,256K .... If you are having a stripe

    size of
    4K and your db_block_size as 8K, It is going to take 2 reads to
    satisfy one
    request for a db_file_sequential_read (Usually requests 1 Oracle
    block)
    [Vivek] . This doesn't sound good if thats true.

    NOTE - The "test" Database is a 4K DB_BLOCK_SIZE while the Live
    Database is
    an 8K DB_BLOCK_SIZE, But the SAME performance Limits were
    Observed
    on Both

    So if possible could you please do the following.

    1. Please shutdown the Test db and start fresh.
    2. Run the same process again and also run the iostat -xPnce 60
    10000 for
    the duration of the run (output this to a file)
    3. Run vmstat and see whats the CPU usage for USER and SYSTEM
    4. When the 500 A/c per sec reaches - Please run

    set lines 132
    select sid, substr(event,1,40),p1,p2,p3 from v$session_wait
    where event in ('db file scattered read','db file sequential
    read','latch
    free','buffer busy waits','log file sync','db file scattered
    read','enqueue');

    and give me the output.

    OK - Will Action & revert to you

    5. IF may waits are showing as "latch free" in the event column of

    v$session_wait, Please query v$latch for
    latch#,name,level,GETS,MISSES,SLEEPS IMMEDIATE_GETS
    ,WAITS_HOLDING_LATCH, SPIN_GETS, SLEEP1.
    Ans OK

    6. Preserve the output for v$latch_children.

    Ans OK

    Qs. Are their any IBM OS Specific Parameters which are considered
    benefitial for performance ?


    I will take a look at both the report.txt in more detail today and

    get back
    to you.

    Thanks Again so much
    Where are you Located / Phone No ? if you please
    We are Currently Located in Bangalore & Addressing The Problem at
    Mumbai over a WAN
    Phone 91 80 6588668

    HTH
    Vivek
    -----Original Message-----
    From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
    Sent: Monday, November 27, 2000 11:16 AM
    To: 'Vivek'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert

    THANKS so much for the association in this issue

    Attached 2 report.txt each taken for 1 Hour approx . Is it
    Sufficient ?
    Attached report.txt of Problem on the "Live" Database

    <>
    NOTE - A "test" Database Created on the DB Server Itself in
    the
    Same
    Partitions as the "Live" Database
    Runs Done on this "test" Database Also failing to go beyond 500 A/cs per
    minute


    <>

    NOTE - Interest Runs were Done on the "Test" Database During Down-Time
    Periods of the "Live" Database

    You Wrote :-
    Since there is some write complete waits Please take a
    look
    at the
    place the on line redo logs are and what is the read, write on these
    disks.

    Ans Online Redo logs ALSO present on the RAID 0+1 Disks & are
    Multiplexed
    Size of Online Redo logfile on the "Live" Database = 30 MB

    NOTE - On the test Database we increased the Size of the Online Redo
    logfile
    to 100MB but with NO benefit

    You wrote :-
    Please give me the DB Version as well

    Ans Database Oracle version = 7.3.4.5.0

    You wrote :-
    I see some waits on More data from client.
    Qs. What is to be done about the Same ?

    NOTE - Will Run the 2 SQL Scripts Suggested by you & revert back
    Thanks Again

    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Monday, November 27, 2000 7:44 PM
    To: 'VIVEK_SHARMA_at_infy.com'

    Cc: Arun Chakrapani
    Subject: SOS Alert

    Hi Vivek:

    Will it be possible to send the whole report.txt to me. Since
    you have
    not
    given for how long this was taken (time from utlbstat and
    utlestat) it
    is
    more difficult to say anything. Based on the report.txt
    snippet
    you
    have
    given there seems to me there are some issues with the
    enqueues
    and
    latch
    free waits. Please run the following SQL as SYS so bet' 2
    different
    times
    (
    say 1/2 apart) and send me both the values(It will be great if
    you could
    run
    this when the batch processing is running).

    select KSQSTTYP,KSQSTGET,KSQSTWAT from x$ksqst where
    KSQSTGET>0
    and
    KSQSTWAT
    0;
    The delta bet' the 2 results will give you information on what
    is the
    enqueue that we see more waits on. Also Please run this sql
    given below
    in
    a
    cron every minute or so and output this to a file

    SELECT p1 "File", p2 "Block", p3 "Reason" FROM v$session_wait
    WHERE
    event
    like 'buffer busy waits';

    This will give you and idea about the buffer busy waits. Since
    there is
    some
    write complete waits Please take a look at the place the on
    line
    redo
    logs
    are and what is the read, write on these disks. Also Please
    give
    me
    infomation on the relog file size. Please give me the DB
    Version
    as
    well.
    I
    see some waits on More data from client. Again since I don't
    know for
    how
    long the stats were run can't say whether they are significant
    or not.
    HTH
    Vivek



    From: VIVEK_SHARMA
    Date: Mon, 27 Nov 2000 12:11:59 +0530
    Subject: SOS Alert

    CASE In a Bank, Interest Calculation Batch Processing Unable
    to
    go
    beyond
    500 A/cs per minute

    CAUSE of the Problem is UNKNOWN

    ORACLE 7.3.4.5 on AIX 4.3.3
    DB Server - IBM S80 Model - 12 CPUs, 3 GB RAM
    APP Server 1 - IBM S80 Model - 6 CPUs, 2 GB RAM
    APP Servers 2 & 3 - IBM Thin Nodes model - 4 CPUs, 1 GB RAM

    Storage Box :-
    ===========
    SS Class Storage
    RAID 0+1 - (First Striped & then Mirrored)
    NOTE - 2 Storage Boxes Exist, one being the Mirror of the
    Other
    Striping exists across a set 4 Disks (in one Box) with another
    4
    being
    it's
    mirror
    (in another Box).
    Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K

    NOTE - Runs Tried from BOTH the APP Servers 1 OR 2,But with
    the
    SAME
    MAX
    of
    500 A/cs
    processed per minute

    CPU Utilizations on BOTH APP & DB Server = 40 %
    wio% on BOTH APP & DB Servers = 35 %
    No paging happening on Both APP & DB Servers

    - Oracle Contention Values Seem Small to us as shown Below or
    so
    they
    seem
    to us :-

    SVRMGR> Rem System wide wait events for non-background
    processes
    (PMON,
    SVRMGR> Rem SMON, etc). Times are in hundreths of seconds.
    Each one of
    SVRMGR> Rem these is a context switch which costs CPU time.
    By
    looking
    at
    SVRMGR> Rem the Total Time you can often determine what is the
    bottleneck
    SVRMGR> Rem that processes are waiting for. This shows the
    total time
    spent
    SVRMGR> Rem waiting for a specific event and the average time
    per wait
    on
    SVRMGR> Rem that event.
    SVRMGR> select n1.event "Event Name",
    2> n1.event_count "Count",
    3> n1.time_waited "Total Time",
    4> round(n1.time_waited/n1.event_count, 2) "Avg Time"
    5> from stats$event n1
    6> where n1.event_count > 0
    7> order by n1.time_waited desc;
    Event Name Count Total Time
    Avg
    Time
    -------------------------------- ------------- -------------
    -------------
    SQL*Net message from client 10856276 31977110 2.95
    enqueue 1295 374980 289.56
    db file sequential read 3614044 303848 .08
    write complete waits 5812 295937 50.92
    latch free 5045060 242170 .05
    SQL*Net more data from client 13939 165275 11.86
    log file sync 12794 146409 11.44
    buffer busy waits 100443 92477 .92


    - ALL Literal SQLs were Converted to using Bind variables
    - ALL Tables Running on Indexes Without Any FULL Scans
    happening
    .
    - All the Literal SQLs (Dynamic) Converted to using Bind
    variables
    (Static
    Queries)

    - event="10181 trace name context forever, level 1000"
    NOTE - Set nevertheless, None of the SQLs taking Excessive
    time
    to
    parse
    though

    - NO statistics are Analyzed
    - 7 tables involved in the interest Calc.
    inserts to 2 Tables
    selects, updates in 5 tables

    Qs. Are there Any BASIC O.S./Hardware Features to Enhance
    Performance on
    IBM
    AIX Systems ?

    Qs. Is Any Other portion of the report.txt required for
    perusal
    ?
    Please Revert to me for any further info / Clarifications ?



    << File: report.txt_Live >> << File: report.txt_TEST >>
    --
    Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
    --
    Author: VIVEK_SHARMA
  • Mohan, Ross at Dec 22, 2000 at 5:32 pm
    This message is in MIME format. Since your mail reader does not understand
    this format, some or all of this message may not be legible.

    ------_=_NextPart_001_01C06C3D.239F2110
    Content-Type: text/plain;

    charset="iso-8859-1"

    Interesting question! My stab in the dark, shot from the hip, wild-ass guess
    is as follows:

    Since modern OSes can cache code segments via shared
    libraries, there is a *very high* read to write ratio (i.e.
    it is read once from disk into OS cache, and re-used from
    there by many folks ) and so the "read from disk" is not a
    significant performance issue.

    Where disk config *would* become significant would be in case
    of failure: What is faster? Recover by backup? Software
    re-install? And, what is more likely to fail? Software
    striped across ten disks _without_ a mirror is more likely to
    become unavailable than software located on one disk, just
    because of the inverse additive effect of individual disk
    MTBFs in a stripe set. So, if you must stripe app binaries,
    mirror them.

    There are other comments about code re-use in the OS, but
    if i start on those, this won't be a "shot from the hip"
    anymore.

    Maybe Eric Pierce can Google us up some pithy and provocative
    URLs to keep us thinking on this topic!

    hth

    Ross

    -----Original Message-----
    From: VIVEK_SHARMA
    Sent: Friday, December 22, 2000 1:26 AM
    To: 'ORACLE-L_at_fatcity.com'; 'MohanR_at_STARS-SMI.com'; 'turner_at_tellme.com';
    'oracledba_at_lazydba.com'
    Subject: RE: SOS Alert

    A related Basic Qs.

    Can the Application Software ( Banking Application of pro*c Executables in
    this Case) be put on Striped Disks belonging to the APP Server ?

    Does it Have any Certain Negative performance impacts ?
    OR is Performance the Same whether the Application Lies on Striped Disks or
    NON-Striped Disks ?
    -----Original Message-----
    From: Mohan, Ross [SMTP:MohanR_at_STARS-SMI.com]
    Sent: Thursday, December 21, 2000 1:51 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert

    4K striping is a major Major MAJOR mistake.

    Doing it in software makes it at least 3x as
    bad.

    Either fix alone would have provided at least a
    2-4x improvement.

    Given a choice, I'd fix the stripe size first, then
    convert the mirroring to hardware.

    hth

    imho

    ymmv

    afaik,

    etc.

    - Ross Mohan

    -----Original Message-----
    From: VIVEK_SHARMA [ ]
    Sent: Wednesday, December 20, 2000 11:13 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert


    CASE In a Bank, Interest Calculation Batch Processing Unable to go beyond

    500 A/cs per minute

    ACTION Moved the Database to Another IBM Machine ( 4 CPU, 4 GB RAM ) with

    HARDWARE Mirroring & 64 K Stripe Size

    NOTE - On the Under-performing DB Server S80 Model of IBM) there exists
    Software Mirroring (By the AIX O.S.) & a Stripe Size of 4K exist

    Firing Off the Interest Calculation Runs from 2 APP Servers Giving an
    Overall Total Processing of Number of 3000 A/cs per minute ( 1500 from
    each
    )

    Qs. Which of the 2 i.e. Hardware Mirroring OR 64 K Striping, do you think

    is the Cause of the Increase in performance as we put BOTH features
    Together
    at the SAME Time OR do Both Contribute Equally ?

    Thanks to Vivek for everything


    -----Original Message-----
    From: VIVEK_SHARMA
    Sent: Thursday, December 07, 2000 6:35 PM
    Subject: RE: SOS Alert



    ACTION Identified the 2 indexes having the waits of "db file
    sequential read"
    Created 2 NEW Tablespaces Each containing 1 Index
    Also Specified parameter log_small_entry_max_size = 0
    NOTE - ORA 7.3.4.5
    RESULT Performance thruput increased to 1500 A/cs per minute from
    1000 A/cs per minute


    ==============================================
    Allow me to begin :-
    There were 2 routes in between the APP & DB Servers .
    One had a Bandwidth of 10 MBPS while Another had a Bandwidth of
    100
    MBPS
    The 10 MBPS route was being used all this while . On Enabling the
    100 MBPS route
    (by Specifying the I.P. Address Instead of Hostname in the
    tnsnames.ora & listener.ora)
    the performance has jumped to 1100 records per minute (from 500
    records per minute)
    i.e. Almost Doubled .
    100 MBPS is the MAX Possible Bandwidth Possible as per the SA

    Qs. Your Views on this please

    Additional responses Follow your Views BELOW :-

    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Tuesday, November 28, 2000 6:30 PM
    To: 'VIVEK_SHARMA'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert

    Vivek:

    Sorry for not replying to you yesterday itself. A Busy day. Took
    a
    look at
    you report.txt. They were having 2 different profiles. Since You
    had
    mentioned that it also stalled in Test DB I concentrated more on
    the test
    report.txt. One thing which stood out on the other one (live) on
    opening you
    report.txt is the SQL _AREA GET_HIT_RATIO is very poor. I don't
    know
    whether
    you have already pinned your SYS objects and other objects that
    are
    executed
    often in shared pool. Please use DBMS_SHARED_POOL.keep to pin
    package,procedure,function,sequence,cursors, triggers that are
    used
    often.

    Ans OK will do so

    So now I will concentrate on the Test DB.

    1. Even though the SQL_AREA GET_HIT_RATIO is higer(82%) when
    compared to
    live this is not good enough. It should be close to 100%(1 some
    where like
    .98 alteast). Please pin the SYS objects to start with.

    OK
    Allow me to say that the shared_pool_size has by experience been
    kept at bare minimum required
    for we did experience performance poblems with a shred_pool_size
    of
    300 MB
    ( Though on Another machine on SUN OS )
    It is recommended to keep shared_pool_size at rock bottom just
    above
    the Limit
    where ORA-4031 out of shared pool segments occurs


    2. On seeing you system wide wait events the following stands out.


    1.log file sync 2863 83922
    29.31
    2.write complete waits 905 71401 78.9

    3.free buffer waits 60 4718
    78.63

    The first event log file sync event mostly comes when there is
    a
    disk
    i/o bottleneck for the online redo logs.This happens when the
    processes have
    to wait for the LGWR to sync a particular REDO log. The p1
    parameter
    in a
    Log sync in the corresponding LOg Buffer.


    The Write complete waits comes
    when the user process is waiting for the LGWR to complete the
    writing of the
    redo information. So Please take a look at the placing of the
    online redo
    log. In which disk have you placed this. Also if it has been place

    in its
    own file system it doesn't mean that it is not going to a busy
    disk.
    Since
    you have 0+1, The slices may belong to a HOT disk. What you need
    to
    do is
    ask your SA to monitor the disk I/O usage and see whats happening
    to
    the
    disk containing the redo log file. Also you can use iostat -xPnce
    5
    5 (I
    think its available in AIX). the output will look something like

    us sy wt id
    23 7 49 21
    extended device statistics ----
    errors
    ---
    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b s/w h/w
    trn
    tot
    device
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 13.4 0 0 0 0


    c1t10d0s0
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.4 0 0 0 0


    c1t10d0s1
    0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0 0


    c1t10d0s2
    0.0 1.3 0.3 10.1 0.0 0.1 4.6 105.8 0 1 0 0


    c1t10d0s3
    Watch out for %b column. If its somewhere around 60 then you may
    be
    experiencing contention on those slices. Ask you SA by giving
    him/her the
    device name and they will be able to give you what the FS is.

    NOTE - 4 Datafiles Containing a Particular Table & it's
    Corresponding indexes (to Which INSERTS
    happen ) were Moved out of the RAID onto a Local Disk whereafter
    performance Jumped from
    850 per minute to 1100 per minute

    Ans - OK . Will Issue the Command

    Also I see
    Free Buffer waits. This is caused because there are no free
    buffers
    when
    they are requested - pointing directly to the inefficiency of the
    DBWR.
    Again the DBWR may be slow because you have a I/O bottleneck for
    the
    DB
    files and indirectly because of the Write complete waits.

    Qs. Would you recommend Setting Multiple db_writer processes
    even though async_io is set to true ?


    " Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K "

    [Vivek] Please confirm me that the Stripe size is 4K. Usually you

    will have
    a stripe size of 32K,64K,128K,256K .... If you are having a stripe

    size of
    4K and your db_block_size as 8K, It is going to take 2 reads to
    satisfy one
    request for a db_file_sequential_read (Usually requests 1 Oracle
    block)
    [Vivek] . This doesn't sound good if thats true.

    NOTE - The "test" Database is a 4K DB_BLOCK_SIZE while the Live
    Database is
    an 8K DB_BLOCK_SIZE, But the SAME performance Limits were
    Observed
    on Both

    So if possible could you please do the following.

    1. Please shutdown the Test db and start fresh.
    2. Run the same process again and also run the iostat -xPnce 60
    10000 for
    the duration of the run (output this to a file)
    3. Run vmstat and see whats the CPU usage for USER and SYSTEM
    4. When the 500 A/c per sec reaches - Please run

    set lines 132
    select sid, substr(event,1,40),p1,p2,p3 from v$session_wait
    where event in ('db file scattered read','db file sequential
    read','latch
    free','buffer busy waits','log file sync','db file scattered
    read','enqueue');

    and give me the output.

    OK - Will Action & revert to you

    5. IF may waits are showing as "latch free" in the event column of

    v$session_wait, Please query v$latch for
    latch#,name,level,GETS,MISSES,SLEEPS IMMEDIATE_GETS
    ,WAITS_HOLDING_LATCH, SPIN_GETS, SLEEP1.
    Ans OK

    6. Preserve the output for v$latch_children.

    Ans OK

    Qs. Are their any IBM OS Specific Parameters which are considered
    benefitial for performance ?


    I will take a look at both the report.txt in more detail today and

    get back
    to you.

    Thanks Again so much
    Where are you Located / Phone No ? if you please
    We are Currently Located in Bangalore & Addressing The Problem at
    Mumbai over a WAN
    Phone 91 80 6588668

    HTH
    Vivek
    -----Original Message-----
    From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
    Sent: Monday, November 27, 2000 11:16 AM
    To: 'Vivek'
    Cc: Arun Chakrapani
    Subject: RE: SOS Alert

    THANKS so much for the association in this issue

    Attached 2 report.txt each taken for 1 Hour approx . Is it
    Sufficient ?
    Attached report.txt of Problem on the "Live" Database

    <>
    NOTE - A "test" Database Created on the DB Server Itself in
    the
    Same
    Partitions as the "Live" Database
    Runs Done on this "test" Database Also failing to go beyond 500 A/cs per
    minute


    <>

    NOTE - Interest Runs were Done on the "Test" Database During Down-Time
    Periods of the "Live" Database

    You Wrote :-
    Since there is some write complete waits Please take a
    look
    at the
    place the on line redo logs are and what is the read, write on these
    disks.

    Ans Online Redo logs ALSO present on the RAID 0+1 Disks & are
    Multiplexed
    Size of Online Redo logfile on the "Live" Database = 30 MB

    NOTE - On the test Database we increased the Size of the Online Redo
    logfile
    to 100MB but with NO benefit

    You wrote :-
    Please give me the DB Version as well

    Ans Database Oracle version = 7.3.4.5.0

    You wrote :-
    I see some waits on More data from client.
    Qs. What is to be done about the Same ?

    NOTE - Will Run the 2 SQL Scripts Suggested by you & revert back
    Thanks Again

    -----Original Message-----
    From: Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    Sent: Monday, November 27, 2000 7:44 PM
    To: 'VIVEK_SHARMA_at_infy.com'

    Cc: Arun Chakrapani
    Subject: SOS Alert

    Hi Vivek:

    Will it be possible to send the whole report.txt to me. Since
    you have
    not
    given for how long this was taken (time from utlbstat and
    utlestat) it
    is
    more difficult to say anything. Based on the report.txt
    snippet
    you
    have
    given there seems to me there are some issues with the
    enqueues
    and
    latch
    free waits. Please run the following SQL as SYS so bet' 2
    different
    times
    (
    say 1/2 apart) and send me both the values(It will be great if
    you could
    run
    this when the batch processing is running).

    select KSQSTTYP,KSQSTGET,KSQSTWAT from x$ksqst where
    KSQSTGET>0
    and
    KSQSTWAT
    0;
    The delta bet' the 2 results will give you information on what
    is the
    enqueue that we see more waits on. Also Please run this sql
    given below
    in
    a
    cron every minute or so and output this to a file

    SELECT p1 "File", p2 "Block", p3 "Reason" FROM v$session_wait
    WHERE
    event
    like 'buffer busy waits';

    This will give you and idea about the buffer busy waits. Since
    there is
    some
    write complete waits Please take a look at the place the on
    line
    redo
    logs
    are and what is the read, write on these disks. Also Please
    give
    me
    infomation on the relog file size. Please give me the DB
    Version
    as
    well.
    I
    see some waits on More data from client. Again since I don't
    know for
    how
    long the stats were run can't say whether they are significant
    or not.
    HTH
    Vivek



    From: VIVEK_SHARMA
    Date: Mon, 27 Nov 2000 12:11:59 +0530
    Subject: SOS Alert

    CASE In a Bank, Interest Calculation Batch Processing Unable
    to
    go
    beyond
    500 A/cs per minute

    CAUSE of the Problem is UNKNOWN

    ORACLE 7.3.4.5 on AIX 4.3.3
    DB Server - IBM S80 Model - 12 CPUs, 3 GB RAM
    APP Server 1 - IBM S80 Model - 6 CPUs, 2 GB RAM
    APP Servers 2 & 3 - IBM Thin Nodes model - 4 CPUs, 1 GB RAM

    Storage Box :-
    ===========
    SS Class Storage
    RAID 0+1 - (First Striped & then Mirrored)
    NOTE - 2 Storage Boxes Exist, one being the Mirror of the
    Other
    Striping exists across a set 4 Disks (in one Box) with another
    4
    being
    it's
    mirror
    (in another Box).
    Thus a Volume Group Contains 8 Disks with 4 Disks
    Stripe Size = 4K

    NOTE - Runs Tried from BOTH the APP Servers 1 OR 2,But with
    the
    SAME
    MAX
    of
    500 A/cs
    processed per minute

    CPU Utilizations on BOTH APP & DB Server = 40 %
    wio% on BOTH APP & DB Servers = 35 %
    No paging happening on Both APP & DB Servers

    - Oracle Contention Values Seem Small to us as shown Below or
    so
    they
    seem
    to us :-

    SVRMGR> Rem System wide wait events for non-background
    processes
    (PMON,
    SVRMGR> Rem SMON, etc). Times are in hundreths of seconds.
    Each one of
    SVRMGR> Rem these is a context switch which costs CPU time.
    By
    looking
    at
    SVRMGR> Rem the Total Time you can often determine what is the
    bottleneck
    SVRMGR> Rem that processes are waiting for. This shows the
    total time
    spent
    SVRMGR> Rem waiting for a specific event and the average time
    per wait
    on
    SVRMGR> Rem that event.
    SVRMGR> select n1.event "Event Name",
    2> n1.event_count "Count",
    3> n1.time_waited "Total Time",
    4> round(n1.time_waited/n1.event_count, 2) "Avg Time"
    5> from stats$event n1
    6> where n1.event_count > 0
    7> order by n1.time_waited desc;
    Event Name Count Total Time
    Avg
    Time
    -------------------------------- ------------- -------------
    -------------
    SQL*Net message from client 10856276 31977110 2.95
    enqueue 1295 374980 289.56
    db file sequential read 3614044 303848 .08
    write complete waits 5812 295937 50.92
    latch free 5045060 242170 .05
    SQL*Net more data from client 13939 165275 11.86
    log file sync 12794 146409 11.44
    buffer busy waits 100443 92477 .92


    - ALL Literal SQLs were Converted to using Bind variables
    - ALL Tables Running on Indexes Without Any FULL Scans
    happening
    .
    - All the Literal SQLs (Dynamic) Converted to using Bind
    variables
    (Static
    Queries)

    - event="10181 trace name context forever, level 1000"
    NOTE - Set nevertheless, None of the SQLs taking Excessive
    time
    to
    parse
    though

    - NO statistics are Analyzed
    - 7 tables involved in the interest Calc.
    inserts to 2 Tables
    selects, updates in 5 tables

    Qs. Are there Any BASIC O.S./Hardware Features to Enhance
    Performance on
    IBM
    AIX Systems ?

    Qs. Is Any Other portion of the report.txt required for
    perusal
    ?
    Please Revert to me for any further info / Clarifications ?



    << File: report.txt_Live >> << File: report.txt_TEST >>
    --
    Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
    --
    Author: VIVEK_SHARMA
    INET: VIVEK_SHARMA_at_infy.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    ------_=_NextPart_001_01C06C3D.239F2110
    Content-Type: text/html;

    charset="iso-8859-1"

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

    RE: SOS Alert

    Interesting question! My stab in the dark, shot from the hip, wild-ass guess is as follows:

    Since modern OSes can cache code segments via shared
    libraries, there is a *very high* read to write ratio (i.e.
    it is read once from disk into OS cache, and re-used from
    there by many folks ) and so the &quot;read from disk&quot; is not a
    significant performance issue.

    Where disk config *would* become significant would be in case
    of failure: What is faster? Recover by backup? Software
    re-install? And, what is more likely to fail?&nbsp; Software
    striped across ten disks _without_ a mirror is more likely to
    become unavailable than software located on one disk, just
    because of the inverse additive effect of individual disk
    MTBFs in a stripe set.&nbsp; So, if you must stripe app binaries,

    mirror them.

    There are other comments about code re-use in the OS, but
    if i start on those, this won't be a &quot;shot from the hip&quot;
    anymore.

    Maybe Eric Pierce can Google us up some pithy and provocative

    URLs to keep us thinking on this topic!

    hth

    Ross

    -----Original Message-----
    From: VIVEK_SHARMA [mailto:VIVEK_SHARMA_at_infy.com]
    Sent: Friday, December 22, 2000 1:26 AM
    To: 'ORACLE-L_at_fatcity.com'; 'MohanR_at_STARS-SMI.com'; 'turner_at_tellme.com';
    'oracledba_at_lazydba.com'
    Subject: RE: SOS Alert

    A related Basic Qs.

    Can the Application Software (&nbsp; Banking Application of pro*c Executables&nbsp; in
    this Case) be put on Striped Disks belonging to the APP Server ?

    Does it Have any Certain Negative performance impacts ?
    OR is Performance the Same whether the&nbsp; Application Lies on Striped Disks or
    NON-Striped Disks ?

    &gt; -----Original Message-----
    &gt; From: Mohan, Ross [SMTP:MohanR_at_STARS-SMI.com]
    &gt; Sent: Thursday, December 21, 2000 1:51 AM
    &gt; To:&nbsp;&nbsp; Multiple recipients of list ORACLE-L
    &gt; Subject:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RE: SOS Alert
    &gt;
    &gt; 4K striping is a major Major MAJOR mistake.
    &gt;
    &gt; Doing it in software makes it at least 3x as
    &gt; bad.
    &gt;
    &gt; Either fix alone would have provided at least a
    &gt; 2-4x improvement.
    &gt;
    &gt; Given a choice, I'd fix the stripe size first, then
    &gt; convert the mirroring to hardware.
    &gt;
    &gt; hth
    &gt;
    &gt; imho
    &gt;
    &gt; ymmv
    &gt;
    &gt; afaik,
    &gt;
    &gt; etc.
    &gt;
    &gt; - Ross Mohan
    &gt;
    &gt; -----Original Message-----
    &gt; From: VIVEK_SHARMA [ &lt;mailto:VIVEK_SHARMA_at_infy.com&gt;]
    &gt; Sent: Wednesday, December 20, 2000 11:13 AM
    &gt; To: Multiple recipients of list ORACLE-L
    &gt; Subject: RE: SOS Alert
    &gt;
    &gt;
    &gt; CASE In a Bank, Interest Calculation Batch Processing Unable to go beyond
    &gt;
    &gt; 500 A/cs per minute
    &gt;
    &gt; ACTION Moved the Database to Another IBM Machine ( 4 CPU, 4 GB RAM ) with
    &gt;
    &gt; HARDWARE Mirroring &amp; 64 K Stripe Size
    &gt;
    &gt; NOTE - On the Under-performing DB Server S80 Model of IBM) there exists
    &gt; Software Mirroring (By the AIX O.S.) &amp; a Stripe Size of 4K exist
    &gt;
    &gt; Firing Off the Interest Calculation Runs from 2 APP Servers Giving an
    &gt; Overall Total&nbsp; Processing of Number of 3000 A/cs per minute ( 1500 from
    &gt; each
    &gt; )
    &gt;
    &gt; Qs. Which of the 2 i.e. Hardware Mirroring OR 64 K Striping, do you think
    &gt;
    &gt; is the Cause of the Increase in performance as we put BOTH features
    &gt; Together
    &gt; at the SAME Time OR do Both Contribute Equally ?
    &gt;
    &gt; Thanks to Vivek for everything
    &gt;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -----Original Message-----
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; From:&nbsp;&nbsp; VIVEK_SHARMA
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sent:&nbsp;&nbsp; Thursday, December 07, 2000 6:35 PM
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Subject:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RE: SOS Alert
    &gt;
    &gt;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ACTION Identified the 2 indexes having the waits of &quot;db file
    &gt; sequential read&quot;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Created 2 NEW Tablespaces Each containing 1 Index
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Also Specified parameter log_small_entry_max_size = 0
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOTE - ORA 7.3.4.5
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RESULT Performance thruput increased to 1500 A/cs per minute from
    &gt; 1000 A/cs per minute
    &gt;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ==============================================
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Allow me to begin :-
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; There were 2 routes in between the APP &amp; DB Servers .
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; One had a Bandwidth of 10 MBPS while Another had a Bandwidth of
    &gt; 100
    &gt; MBPS
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The 10 MBPS route was being used all this while . On Enabling the
    &gt; 100 MBPS route
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (by Specifying the I.P. Address Instead of Hostname in the
    &gt; tnsnames.ora &amp; listener.ora)
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; the performance has jumped to 1100 records per minute (from 500
    &gt; records per minute)
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.e. Almost Doubled .
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100 MBPS is the MAX Possible Bandwidth Possible as per the SA
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Qs. Your Views on this please
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Additional responses Follow your Views BELOW :-
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -----Original Message-----
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; From:&nbsp;&nbsp; Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sent:&nbsp;&nbsp; Tuesday, November 28, 2000 6:30 PM
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; To:&nbsp;&nbsp;&nbsp;&nbsp; 'VIVEK_SHARMA'
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cc:&nbsp;&nbsp;&nbsp;&nbsp; Arun Chakrapani
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Subject:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RE: SOS Alert
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Vivek:
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sorry for not replying to you yesterday itself. A Busy day. Took
    &gt; a
    &gt; look at
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; you report.txt. They were having 2 different profiles. Since You
    &gt; had
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mentioned that it also stalled in&nbsp; Test DB I concentrated more on
    &gt; the test
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; report.txt. One thing which stood out on the other one (live) on
    &gt; opening you
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; report.txt is the SQL _AREA GET_HIT_RATIO is very poor. I don't
    &gt; know
    &gt; whether
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; you have already pinned your SYS objects and other objects that
    &gt; are
    &gt; executed
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; often in shared pool. Please use DBMS_SHARED_POOL.keep to pin
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; package,procedure,function,sequence,cursors, triggers that are
    &gt; used
    &gt; often.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ans OK will do so
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; So now I will concentrate on the Test DB.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1. Even though the SQL_AREA GET_HIT_RATIO is higer(82%) when
    &gt; compared to
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; live this is not good enough. It should be close to 100%(1 some
    &gt; where like
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .98 alteast). Please pin the SYS objects to start with.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OK
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Allow me to say that the shared_pool_size has by experience been
    &gt; kept at bare minimum required
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for we did experience performance poblems with a shred_pool_size
    &gt; of
    &gt; 300 MB
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( Though on Another machine on SUN OS )
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; It is recommended to keep shared_pool_size at rock bottom just
    &gt; above
    &gt; the Limit
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where ORA-4031 out of shared pool segments occurs
    &gt;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2. On seeing you system wide wait events the following stands out.
    &gt;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.log file sync&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2863&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 83922
    &gt; 29.31
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.write complete waits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 905&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 71401&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 78.9
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.free buffer waits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4718
    &gt; 78.63
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The first event log file sync event mostly comes when there is
    &gt; a
    &gt; disk
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i/o bottleneck for the online redo logs.This happens when the
    &gt; processes have
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to wait for the LGWR to sync a particular REDO log. The p1
    &gt; parameter
    &gt; in a
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Log sync in the corresponding LOg Buffer.
    &gt;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The Write complete waits comes
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when the user process is waiting for the LGWR to complete the
    &gt; writing of the
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; redo information.&nbsp; So Please take a look at the placing of the
    &gt; online redo
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; log. In which disk have you placed this. Also if it has been place
    &gt;
    &gt; in its
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; own file system it doesn't mean that it is not going to a busy
    &gt; disk.
    &gt; Since
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; you have 0+1, The slices may belong to a HOT disk. What you need
    &gt; to
    &gt; do is
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ask your SA to monitor the disk I/O usage and see whats happening
    &gt; to
    &gt; the
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; disk containing the redo log file. Also you can use iostat -xPnce
    &gt; 5
    &gt; 5 (I
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; think its available in AIX). the output will look something like
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; us sy wt id
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23&nbsp; 7 49 21
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; extended device statistics ----
    &gt; errors
    &gt; ---
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r/s&nbsp; w/s&nbsp;&nbsp; kr/s&nbsp;&nbsp; kw/s wait actv wsvc_t asvc_t&nbsp; %w&nbsp; %b s/w h/w
    &gt; trn
    &gt; tot
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; device
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp; 13.4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0
    &gt; 0
    &gt; 0
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1t10d0s0
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 2.4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0
    &gt; 0
    &gt; 0
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1t10d0s1
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp; 0.0&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp;&nbsp; 0.0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0
    &gt; 0
    &gt; 0
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1t10d0s2
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.0&nbsp; 1.3&nbsp;&nbsp;&nbsp; 0.3&nbsp;&nbsp; 10.1&nbsp; 0.0&nbsp; 0.1&nbsp;&nbsp;&nbsp; 4.6&nbsp; 105.8&nbsp;&nbsp; 0&nbsp;&nbsp; 1&nbsp;&nbsp; 0&nbsp;&nbsp; 0
    &gt; 0
    &gt; 0
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1t10d0s3
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Watch out for %b column. If its somewhere around 60 then you may
    &gt; be
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; experiencing contention on those slices. Ask you SA by giving
    &gt; him/her the
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; device name and they will be able to give you what the FS is.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOTE - 4 Datafiles Containing a Particular Table &amp; it's
    &gt; Corresponding indexes (to Which INSERTS
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; happen ) were Moved out of the RAID onto a Local Disk whereafter
    &gt; performance Jumped from
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 850 per minute to 1100&nbsp; per minute
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ans - OK . Will Issue the Command
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Also I see
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Free Buffer waits. This is caused because there are no free
    &gt; buffers
    &gt; when
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; they are requested - pointing directly to the inefficiency of the
    &gt; DBWR.
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Again the DBWR may be slow because you have a I/O bottleneck for
    &gt; the
    &gt; DB
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; files and indirectly because of the Write complete waits.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Qs. Would you recommend Setting Multiple db_writer processes
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; even though async_io is set to true ?
    &gt;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &quot; Thus a Volume Group Contains 8 Disks with 4 Disks
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Stripe Size = 4K &quot;
    &gt;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Vivek]&nbsp; Please confirm me that the Stripe size is 4K. Usually you
    &gt;
    &gt; will have
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a stripe size of 32K,64K,128K,256K .... If you are having a stripe
    &gt;
    &gt; size of
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4K and your db_block_size as 8K, It is going to take 2 reads to
    &gt; satisfy one
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; request for a db_file_sequential_read (Usually requests 1 Oracle
    &gt; block)
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Vivek]&nbsp; . This doesn't sound good if thats true.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOTE - The &quot;test&quot; Database is a 4K DB_BLOCK_SIZE while the Live
    &gt; Database is
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; an 8K DB_BLOCK_SIZE, But the SAME performance Limits were
    &gt; Observed
    &gt; on Both
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; So if possible could you please do the following.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1. Please shutdown the Test db and start fresh.
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2. Run the same process again and also run the iostat -xPnce 60
    &gt; 10000 for
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; the duration of the run (output this to a file)
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3. Run vmstat and see whats the CPU usage for USER and SYSTEM
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4. When the 500 A/c per sec reaches - Please run
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set lines 132
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select sid, substr(event,1,40),p1,p2,p3 from v$session_wait
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where event in ('db file scattered read','db file sequential
    &gt; read','latch
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; free','buffer busy waits','log file sync','db file scattered
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; read','enqueue');
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and give me the output.&nbsp;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OK - Will Action &amp; revert to you
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5. IF may waits are showing as &quot;latch free&quot; in the event column of
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; v$session_wait, Please query v$latch for
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; latch#,name,level,GETS,MISSES,SLEEPS IMMEDIATE_GETS
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,WAITS_HOLDING_LATCH, SPIN_GETS, SLEEP1.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ans OK
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6. Preserve the output for v$latch_children.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ans OK
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Qs. Are their any IBM OS Specific Parameters which are considered
    &gt; benefitial for performance ?
    &gt;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I will take a look at both the report.txt in more detail today and
    &gt;
    &gt; get back
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to you.
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thanks Again so much
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Where are you Located&nbsp; / Phone No ? if you please
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; We are Currently Located in Bangalore &amp; Addressing The Problem at
    &gt; Mumbai over a WAN
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Phone 91 80 6588668
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HTH
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Vivek
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; -----Original Message-----
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Sent: Monday, November 27, 2000 11:16 AM
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; To:&nbsp;&nbsp; 'Vivek'
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Cc:&nbsp;&nbsp; Arun Chakrapani
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Subject:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RE: SOS Alert
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; THANKS so much for the association in this issue
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Attached 2 report.txt each taken for 1 Hour approx . Is it
    &gt; Sufficient ?
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Attached&nbsp; report.txt of&nbsp; Problem on the &quot;Live&quot; Database
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;&nbsp; &lt;&lt;report.txt_Live&gt;&gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; NOTE - A &quot;test&quot; Database&nbsp; Created on the DB Server Itself&nbsp; in
    &gt; the
    &gt; Same
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Partitions as the &quot;Live&quot; Database
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Runs Done on this &quot;test&quot; Database Also failing to go beyond 500
    &gt; A/cs per
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; minute
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;&nbsp; &lt;&lt;report.txt_TEST&gt;&gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; NOTE - Interest Runs were Done on the &quot;Test&quot; Database During
    &gt; Down-Time
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Periods of the &quot;Live&quot; Database
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; You Wrote :-
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Since there is some write complete waits Please take a
    &gt; look
    &gt; at the
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; place the on line redo logs are and what is the read, write on
    &gt; these
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; disks.
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Ans Online Redo logs ALSO present on the RAID 0+1 Disks &amp; are
    &gt; Multiplexed
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Size of Online Redo logfile on the &quot;Live&quot; Database = 30 MB
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; NOTE - On the test Database we increased the Size of the Online
    &gt; Redo
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; logfile
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; to 100MB but with NO benefit
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; You wrote :-
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Please give me the DB Version as well
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Ans Database Oracle&nbsp; version = 7.3.4.5.0
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; You wrote :-
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I see some waits on More data from client.
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Qs. What is to be done about the Same ?
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; NOTE - Will Run the 2 SQL Scripts Suggested by you &amp; revert back
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; Thanks Again
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; -----Original Message-----
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; From:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Vivek [SMTP:Vivek_at_1800FLOWERS.com]
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Sent:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Monday, November 27, 2000 7:44 PM
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; To: 'VIVEK_SHARMA_at_infy.com'
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Cc: Arun Chakrapani
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Subject:&nbsp;&nbsp;&nbsp; SOS Alert
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Hi Vivek:
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Will it be possible to send the whole report.txt to me. Since
    &gt; you have
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; not
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; given for how long this was taken (time from utlbstat and
    &gt; utlestat) it
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; is
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; more difficult to say anything.&nbsp; Based on the report.txt
    &gt; snippet
    &gt; you
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; have
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; given there seems to me there are some issues with the
    &gt; enqueues
    &gt; and
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; latch
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; free waits. Please run the following SQL as SYS so bet' 2
    &gt; different
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; times
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; (
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; say 1/2 apart) and send me both the values(It will be great if
    &gt;
    &gt; you could
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; run
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; this when the batch processing is running).
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; select KSQSTTYP,KSQSTGET,KSQSTWAT from x$ksqst where
    &gt; KSQSTGET&gt;0
    &gt; and
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; KSQSTWAT
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; &gt;0;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; The delta bet' the 2 results will give you information on what
    &gt;
    &gt; is the
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; enqueue that we see more waits on. Also Please run this sql
    &gt; given below
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; in
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; a
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; cron every minute or so and output this to a file
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SELECT p1 &quot;File&quot;, p2 &quot;Block&quot;, p3 &quot;Reason&quot;&nbsp; FROM v$session_wait
    &gt;
    &gt; WHERE
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; event
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; like&nbsp; 'buffer busy waits'&nbsp;;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; This will give you and idea about the buffer busy waits. Since
    &gt;
    &gt; there is
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; some
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; write complete waits Please take a look at the place the on
    &gt; line
    &gt; redo
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; logs
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; are and what is the read, write on these disks. Also Please
    &gt; give
    &gt; me
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; infomation on the relog file size. Please give me the DB
    &gt; Version
    &gt; as
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; well.
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; I
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; see some waits on More data from client. Again since I don't
    &gt; know for
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; how
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; long the stats were run can't say whether they are significant
    &gt;
    &gt; or not.
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; HTH
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Vivek
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; From: VIVEK_SHARMA &lt;VIVEK_SHARMA_at_infy.com&gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp; Date: Mon, 27 Nov 2000 12:11:59 +0530
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp; Subject: SOS Alert
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; CASE In a Bank, Interest Calculation Batch Processing Unable
    &gt; to
    &gt; go
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; beyond
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; 500 A/cs per minute
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; CAUSE of the Problem is UNKNOWN
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; ORACLE 7.3.4.5 on AIX 4.3.3
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; DB Server - IBM S80 Model - 12 CPUs, 3 GB RAM
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; APP Server 1 - IBM S80 Model - 6 CPUs, 2 GB RAM
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; APP Servers 2 &amp; 3 - IBM Thin Nodes model - 4 CPUs, 1 GB RAM
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Storage Box :-
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; ===========
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SS Class Storage
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; RAID 0+1 - (First Striped &amp; then Mirrored)
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; NOTE - 2 Storage Boxes Exist, one being the Mirror of the
    &gt; Other
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Striping exists across a set 4 Disks (in one Box) with another
    &gt; 4
    &gt; being
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; it's
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; mirror
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; (in another Box).
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Thus a Volume Group Contains 8 Disks with 4 Disks
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Stripe Size = 4K
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; NOTE - Runs Tried from BOTH the APP Servers 1 OR 2,But with
    &gt; the
    &gt; SAME
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; MAX
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; of
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; 500 A/cs
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; processed per minute
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; CPU Utilizations on BOTH APP &amp; DB Server = 40 %
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; wio% on BOTH APP &amp; DB Servers = 35 %
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; No paging happening on Both APP &amp; DB Servers
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - Oracle Contention Values Seem Small to us as shown Below or
    &gt; so
    &gt; they
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; seem
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; to us :-
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem System wide wait events for non-background
    &gt; processes
    &gt; (PMON,
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem SMON, etc).&nbsp; Times are in hundreths of seconds.
    &gt; Each one of
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem these is a context switch which costs CPU time.
    &gt; By
    &gt; looking
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; at
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem the Total Time you can often determine what is the
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; bottleneck
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem that processes are waiting for.&nbsp; This shows the
    &gt; total time
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; spent
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem waiting for a specific event and the average time
    &gt; per wait
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; on
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; Rem that event.
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SVRMGR&gt; select&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; n1.event &quot;Event Name&quot;,
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; n1.event_count &quot;Count&quot;,
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&gt;&nbsp;&nbsp;&nbsp;&nbsp; n1.time_waited &quot;Total Time&quot;,
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&gt;&nbsp;&nbsp;&nbsp;&nbsp; round(n1.time_waited/n1.event_count, 2) &quot;Avg Time&quot;
    &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&gt;&nbsp;&nbsp;&nbsp; from stats$event n1
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&gt;&nbsp;&nbsp;&nbsp; where n1.event_count &gt; 0
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7&gt;&nbsp;&nbsp;&nbsp; order by n1.time_waited desc;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Event Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Total Time
    &gt; Avg
    &gt; Time
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; -------------------------------- ------------- -------------
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; -------------
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SQL*Net message from client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10856276&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31977110
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 2.95
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; enqueue&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1295&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 374980
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 289.56
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; db file sequential read&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3614044&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 303848
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; .08
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; write complete waits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5812&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 295937
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 50.92
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; latch free&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5045060&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 242170
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; .05
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; SQL*Net more data from client&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13939&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 165275
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 11.86
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; log file sync&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12794&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 146409
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; 11.44
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; buffer busy waits&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100443&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 92477
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; .92
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - ALL Literal SQLs were Converted to using Bind variables
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - ALL Tables Running on Indexes Without Any FULL Scans
    &gt; happening
    &gt; .
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - All the Literal SQLs (Dynamic) Converted to using Bind
    &gt; variables
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; (Static
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Queries)
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - event=&quot;10181 trace name context forever, level 1000&quot;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; NOTE - Set nevertheless, None of the SQLs taking Excessive
    &gt; time
    &gt; to
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; parse
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; though
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - NO statistics are Analyzed
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; - 7 tables involved in the interest Calc.
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; inserts to 2 Tables
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; selects, updates in 5 tables
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Qs. Are there Any BASIC O.S./Hardware Features to Enhance
    &gt; Performance on
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; IBM
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; AIX Systems ?
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Qs. Is Any Other portion of the report.txt required for
    &gt; perusal
    &gt; ?
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt; Please Revert to me for any further info / Clarifications ?
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;
    &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &gt; &gt;&nbsp; &lt;&lt; File: report.txt_Live &gt;&gt;&nbsp; &lt;&lt; File: report.txt_TEST &gt;&gt;
  • Mohan, Ross at Dec 22, 2000 at 6:37 pm
    This message is in MIME format. Since your mail reader does not understand
    this format, some or all of this message may not be legible.

    ------_=_NextPart_001_01C06C46.4AC37D50
    Content-Type: text/plain;

    charset="iso-8859-1"

    Interesting question! My stab in the dark, shot from the hip, wild-ass guess
    is as follows:

    Since modern OSes can cache code segments via shared
    libraries, there is a *very high* read to write ratio (i.e.
    it is read once from disk into OS cache, and re-used from
    there by many folks ) and so the "read from disk" is not a
    significant performance issue.

    Where disk config *would* become significant would be in case
    of failure: What is faster? Recover by backup? Software
    re-install? And, what is more likely to fail? Software
    striped across ten disks _without_ a mirror is more likely to
    become unavailable than software located on one disk, just
    because of the inverse additive effect of individual disk
    MTBFs in a stripe set. So, if you must stripe app binaries,
    mirror them.

    There are other comments about code re-use in the OS, but
    if i start on those, this won't be a "shot from the hip"
    anymore.

    Maybe Eric Pierce can Google us up some pithy and provocative
    URLs to keep us thinking on this topic!

    hth

    Ross

    -----Original Message-----
    From: VIVEK_SHARMA
    Sent: Friday, December 22, 2000 12:21 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert

    A related Basic Qs.

    Can the Application Software ( Banking Application of pro*c Executables in
    this Case) be put on Striped Disks belonging to the APP Server ?

    Does it Have any Certain Negative performance impacts ?
    OR is Performance the Same whether the Application Lies on Striped Disks or
    NON-Striped Disks ?

    ------_=_NextPart_001_01C06C46.4AC37D50
    Content-Type: text/html;

    charset="iso-8859-1"

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

    RE: SOS Alert

    Interesting question! My stab in the dark, shot from the hip, wild-ass guess is as follows:

    Since modern OSes can cache code segments via shared
    libraries, there is a *very high* read to write ratio (i.e.
    it is read once from disk into OS cache, and re-used from
    there by many folks ) and so the &quot;read from disk&quot; is not a
    significant performance issue.

    Where disk config *would* become significant would be in case
    of failure: What is faster? Recover by backup? Software
    re-install? And, what is more likely to fail?&nbsp; Software
    striped across ten disks _without_ a mirror is more likely to
    become unavailable than software located on one disk, just
    because of the inverse additive effect of individual disk
    MTBFs in a stripe set.&nbsp; So, if you must stripe app binaries,
    mirror them.

    There are other comments about code re-use in the OS, but
    if i start on those, this won't be a &quot;shot from the hip&quot;
    anymore.

    Maybe Eric Pierce can Google us up some pithy and provocative
    URLs to keep us thinking on this topic!

    hth

    Ross

    -----Original Message-----
    From: VIVEK_SHARMA [mailto:VIVEK_SHARMA_at_infy.com]
    Sent: Friday, December 22, 2000 12:21 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: SOS Alert

    A related Basic Qs.

    Can the Application Software (&nbsp; Banking Application of pro*c Executables&nbsp; in
    this Case) be put on Striped Disks belonging to the APP Server ?

    Does it Have any Certain Negative performance impacts ?
    OR is Performance the Same whether the&nbsp; Application Lies on Striped Disks or

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 7, '00 at 1:05p
activeDec 22, '00 at 6:37p
posts7
users2
websiteoracle.com

2 users in discussion

VIVEK_SHARMA: 4 posts Mohan, Ross: 3 posts

People

Translate

site design / logo © 2022 Grokbase