Rachel, This has not gotten to the level of getting it in 'black & white' or to the 'beating' level, yet. That's why my boss wanted to just review the database and see if this thing will scale when we add more Applications and of course, hundreds of more users. More digging will be required to find problematic areas, if any, and if some one complains that there is such-and-such problem. Currently, no one is complaining, as the batch processes finish while we are asleep and the on-line load has been pretty light. But there is no guarantee that it will be so in the near future. And looking at this database at this time was a good idea from my boss, and getting someone else, (not the primary/secondary DBA) to do that is even better (for him, that is ;)
Thanks. - Kirti -----Original Message----- Sent: Sunday, August 11, 2002 8:38 PM To: Multiple recipients of list ORACLE-L Kirti, Get it in WRITING that you are not allowed to change anything. So that when they start to beat on you (okay, I know your boss, HE won't beat but HIS boss might) you are covered. You have my sympathies... I've worked under similar conditions (Rachel, we are giving you 750GB for your databases... oh yeah, RAID 5) Rachel --- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote: > This is not a joke.....!!! > > This is from a business critical production database that I was asked > to > 'review' past Friday. > > The report is from v$system_event taken at 10:30am, Aug 9, 2002. > The server (and database) was bounced on Aug 4, 2002 at 9:20am. > > This was the 1st time I was logging into this database. > > SQL> / > > EVENT TOTAL_WAITS TOTAL_TIMEOUTS > TIME_WAITED > AVERAGE_WAIT > ----------------------------------- ----------- -------------- > ----------- > ------------ > control file parallel write 143933 0 > 4080356626 > 28349.0001 > db file scattered read 12540695 0 > 1.2254E+10 > 977.107332 > buffer busy waits 10740450 36 > 8193235928 > 762.839167 > SQL*Net message from client 180769027 0 > 9.9561E+10 > 550.761199 > db file sequential read 298968127 0 > 1.1839E+11 > 395.99129 > enqueue 13500 6435 > 2036785 > 150.872963 > SQL*Net more data from client 52227948 0 > 4093231165 > 78.3724294 > free buffer waits 16 4 > 795 > 49.6875 > log file switch completion 804 43 > 16263 > 20.2276119 > log buffer space 977 0 > 5409 > 5.53633572 > control file single write 17 0 > 51 > 3 > db file parallel write 1749695 0 > 2935317 > 1.67761638 > db file parallel read 8149 0 > 13484 > 1.65468156 > log file single write 1024 0 > 701 > .684570313 > latch free 2007034 1616763 > 1054137 > .525221297 > log file sync 1366242 560 > 526049 > .385033545 > SQL*Net message from dblink 1514480 0 > 451351 > .298023744 > log file sequential read 405415 0 > 82877 > .204425095 > SQL*Net break/reset to dblink 10 0 > 2 > .2 > log file parallel write 2025192 7 > 293332 > .144841576 > SQL*Net break/reset to client 28113 0 > 3221 > .114573329 > db file single write 320 0 > 36 > .1125 > SQL*Net more data from dblink 447044 0 > 11375 > .025444923 > SQL*Net more data to client 11770996 0 > 75680 > .006429362 > control file sequential read 554851 0 > 3261 > .005877254 > SQL*Net more data to dblink 1076 0 > 5 > .00464684 > buffer deadlock 1045 1029 > 1 > .000956938 > SQL*Net message to dblink 1514485 0 > 456 > .000301092 > SQL*Net message to client 180769119 0 > 48736 > .000269604 > > 29 rows selected. > > SQL> > > Here is the environment: > 1)all the file systems for the database, including dump directories > are in a > single disk volume group, 2) all redo logs and control files are > spread > among all the other database files, 3) Hitachi array is in use with > nothing > but RAID-5 for all files (redo as well), 4) the real hard drives > within the > array are either shared with other databases on the same server or > with > other servers, 5) redo logs are of 100MB size and switch 20+ > times/hour when > some of the batch processes run in the evening, 6) no changes are > allowed to > any SQL code, Pro*COBOL code that use 'COPYBOOKs' (Remember those?) > to > interact with tables at single row level (no array processing) using > routines with bunch of parameters (call insert... call update... call > delete...), 7) the array has 32GB of NV cache and that's the max it > can have > (the DB is 180GB, there are 3 other similar ones from just this > server). > > Now the 'icing on the cake': > The server has 3 other critical databases. All 4 running in archive > log > mode. All share the *same* archive log destination. And all databases > are > expected to have same amount of batch processing. The archive log > destination is 8GB in size on the 2nd VG. The DB in question, > generated > 1.8GB to 2+GB of logs in less than an hour during batch processing. > At times > our automated archived log siphoning process encounters some > bottlenecks > from our single IBM/Tivoli TSM Server where the logs are deposited > before > those are purged from archive log destination... > > I was also informed that I will not have much chance to bring about > any > changes in the environment described above. Because, I was told, > ...it is > the corporate decision to use RAID-5 with HDS array and it is 'the > most cost > effective way to address our storage needs'.... and a single VG per > database > helps UNIX support to implement HACMP with much ease... and we can > not meet > our published deadlines if we made any changes and spent time in > testing > those unscheduled changes...... yadi yadi yada.... > > > - Kirti > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). __________________________________________________ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
