Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Houston, do I have a problem?

Houston, do I have a problem?

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Sun, 11 Aug 2002 14:43:18 -0800
Message-ID: <F001.004B18AA.20020811144318@fatcity.com>


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

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). Received on Sun Aug 11 2002 - 17:43:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US