Re: SOS Alert

 Dear

Going by UR report U have definetely having problem with enques

Search for foreign keys and see that they R indexed

Query V$lock and v$sesstat

Rao

--

On Mon, 27 Nov 2000 00:30:21  
 Rod Corderey wrote:


>Hi 

>

>. What about the application itself?

>

>. You mention that accesses are via indexes but are they the most optimal for the 

>  retrieval direction?

>

>. Is there any purely navigational sql traffic that is using table accesses, if so 

>  why?

> 

>. If you take a single iteration of the calculation process and analyse each of the 

>  steps, is the process taking 1/500 minute or is the total process slowing due

>  to contention eg intermittant lock waits etc?

>

>. Are there any database configuration parameters out of step with what the

>  process is requesting. eg small sga, substantial use of hash joins with small

>  hash_area_size etc

>

>. At what point in the total run process are commits being executed? 

>

>  If the largest granularity of commit is not at account level, are there any

>  issues with redos affecting the performance?

>

>. I'm not sure whether you are inferring that dynamic sql is used or that you mean

>  hard values were embedded in sql statements which have now been replaced with

>  variables and assignments.

>

>  If you are using dynamic sql then  as you are on 7.3 NDS is not available, so 

>  are you using DBMS_SQL or a C process for executing the dynamic sql.

>  

>. Is the dynamic sql really necessary to the process, without NDS - and sometimes 

>  with it - it will always slow things down a bit?

>

>Other issues might involve the mix of physical distribution of the data set 

>involved in a calculation batch, I am assuming that as it is a bank the data 

>set is fairly large.

>

>sorry this a bit sketchy and questions rather than answers but to offer much more 

>I would need to see the calculation process itself and data model against which it 

>is being executed. Or at least the answers to the questions :-)

>

>I might be being a bit blinkered but I would exhaust the design characteristics 

>before addressing hardware any further.

>

>

>good luck

>

>Rod

>

>-- 

>Rod Corderey

>

>Lane Associates

>RodCorderey_at_Lane-Associates.com

>http://www.Lane-Associates.com

>

>VIVEK_SHARMA wrote:

>> 

>> 

>> 

>> 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 ?

>> 

>> --

>> 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).

>

>-- 

>Rod Corderey

>

>Lane Associates

>RodCorderey_at_Lane-Associates.com

>http://www.Lane-Associates.com

>-- 

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>-- 

>Author: Rod Corderey

>  INET: RodCorderey_at_Lane-Associates.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