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

Home -> Community -> Usenet -> c.d.o.server -> Re: Large SGA - performance problems

Re: Large SGA - performance problems

From: Glen Case <gtbm_at_ix.netcom.com>
Date: 1997/01/03
Message-ID: <5ajkot$5g0@dfw-ixnews9.ix.netcom.com>

In <5air7c$mee_at_swing.st.statoil.no> erlend_at_bitcon.no (Erlend Dyrnes) writes:
>
>
>Hi net!
>
>I'm pondering about some rather irritating performance problems we
>have, and wondering whether you have any suggestions for me to
>consider.
>
>Here's the thing:
>
>We run 2 HP9000 K420 machines in a HP cluster. Both equipped with 1GB
>of memory, 2 processors and fibre channel for internal communication.
>Disk ? Enough! One of them is our database server, running Oracle
>7.2.3, and SQL*Net v1 (Yes, I know!). The database serves the other
>HP over the fibre channel into a Powerhouse application.
>
>Database characteristics:
>
>Sizes:
>
>TABLESPACE_NAME MB COUNT(*)
>------------------------------ ---------- ----------
>GEN_FREE_TEXT 800 1
>INDEX01 2400 6
>RBS 800 2
>SYSTEM 100 1
>TEMP 400 1
>TOOLS 50 1
>USERS 50 1
>VMS 2000 5
>
>SGA:
>
>SQLDBA> show sga
>Total System Global Area 287680840 bytes
> Fixed Size 47936 bytes
> Variable Size 164589064 bytes
> Database Buffers 122880000 bytes
> Redo Buffers 163840 bytes
>
>optimizer_mode CHOOSE
>
>We have optimizer statistics.
>
>Tables, views and indices :
>
>Tables: 351
>Views: 40
>Indices: 607
>
>Concurrent users (approx) : 80
>
>
>OK, and now the problem:
>
>Every now and then a user generate a table scan (one way or another).
>This table scan tends to "hang" the database for a while. The database
>process uses ~100% CPU, and nothing else is allowed to happen on the
>database during the table scan. Not even a sqlplus session login. The
>hangs may last from 5 seconds up to over 1 minute. As of now we can
>cope, but as we increase number of concurrent users, I'am afraid the
>problem will be a nightmare.
>
>I ask myself :
>
>1. Is the hangs due to the very large SGA ?
> Is such a large SGA generally a good idea ?
>2. Should I use the cost-based optimizer in Oracle 7.2.3 ?
>
>And now I ask you:
>
>Am I asking myself the right questions ?
>Any other experience on this matter you would like to share with me ?
>
>Regards.
>
>erlend
>
>
>
>
>Erlend Dyrnes: Senior systems consultant, MBS Fjerndata AS, Bergen,
 Norway
> System manager (HP-UX) , application configuration
> email: Erlend.Dyrnes_at_mbs.no or k38122_at_ddb.be.statoil.no
> - I do *not* know what it means to miss New Orleans
>

I ran into similar problems. You didn't mention the o/s release level. i contacted oracle support and they have a list of problems with H/P release level 10. they sent me a document showing the patches required for each o/s level. hope this helps you.

October 4, 1996      

Dear Hewlett-Packard/Oracle Customer:   

Hewlett-Packard and Oracle are committed to the success of our customers. To achieve the stable, high-performance platforms that today's fast-paced business environment demands, we are continuing to increase the scope of our joint development and support activities. As part of that overall effort, we are sending you this package of information. It provides the latest software revision information available to ensure that your HP/Oracle information systems solution has the greatest possible stability and performance.   

The enclosed material includes lists of both HP operating system and networking software patches and Oracle updates recommended for each indicated release. HP and Oracle worked together to identify these items and we strongly recommend they be installed on all systems running HP-UX and Oracle software at your earliest convenience. While not every patch applies to every configuration, these patches apply to the majority of HP/Oracle environments and were selected to maximize your system availability and to avoid any potential for unplanned downtime.   

These patches and upgrades are available through normal support channels. Specific instructions for obtaining them are contained elsewhere in this package. In the future, recommended and critical patches for HP-UX releases will be delivered regularly in easy to install bundles via the Extension Software CD-ROM. See the enclosed data sheet for more information about this program.   

In addition to those listed, there may be other software patches or updates available to address the specific needs of your current environment. If you have any questions about any patches and updates or their installation, please contact your Hewlett-Packard or Oracle response center.   

We hope you find this information to be helpful and we value your continued partnership with both our companies.      

                         Sincere regards,  
  
  
                         Hewlett-Packard Company  Oracle  
Corporation

    HP 9000 Series 800 Recommended Patches (Updated: 8/17/96)   

                       HP-UX Release 10.20  
  
PHKL_7764  Data loss when truncating VxFS (JFS) files  
PHKL_7765  10.X hpux(1M) for kernels larger than 13 MBytes  
PHKL_7777  Fix for unmountable VxFS (JFS) file system  
PHKL_7871  LVM lvreduce kernel inconsistency panic fix  
PHKL_7900  JFS KI, page fault, deadlock, & setuid fixes  
PHKL_7972  scsi1 and scsi3 patch  
PHKL_8085  LVM Cumulative Patch  
PHKL_8204  Fix for system hang during panic  
PHKL_8270  Data corruption on MP systems with LVM mirroring  
PHKL_8295  Fix for NFS server hang  
PHKL_8332  Data loss with UFS files using fragments  
PHNE_6190 10.X ocd(1M) cumulative patch PHNE_7601 Mux and Pty cumulative patch   
                       HP-UX Release 10.10  
  
PHCO_7271  fbackup(1M)/frecover(1M) cumulative patch  
PHCO_7466  LVM commands cumulative patch  
PHCO_7799  libc cumulative patch  
PHKL_6663  Improve select() performance  
PHKL_6676  Hang during Reboot on MP machines  
PHKL_6681  patch for process management and signals  
PHKL_6717  Fix for page deallocation error in MEMLOGP  
PHKL_7018  VxFS (JFS) cumulative patch  
PHKL_7091  HSC/GSC/wsio SCSI subsystem defect fixes  
PHKL_7095  fix for process hangs in semop(2)  
PHKL_7208  VxFS (JFS) vx_remove patch  
PHKL_7245  Fix in smmap for file systems that don't do ACLs  
PHKL_7302  Fix for data page fault in set_purge_SIDS()  
PHKL_7351  increase maximum number of async ports  
PHKL_7477  patch for vfork() panic  
PHKL_7853  tape driver cumulative patch  
PHKL_7853  tape driver cumulative patch  
PHKL_7881  HP-PB Fast-Wide SCSI cumulative patch  
PHKL_8083  LVM Cumulative Patch  
PHKL_8152  SCSI disk cumulative patch   
PHKL_8269  Data corruption on MP systems with LVM mirroring  
PHKL_8293  cumulative file system and VM patch  
PHNE_7180  LAN products patch  
PHNE_7355  STREAMS cumulative patch  

PHNE_7600 Mux and Pty cumulative patch PHNE_7750 Transport cumulative patch   
                       HP-UX Release 10.01  
  
PHCO_5803  fsck_hfs(1M) patch for possible corruption  
PHCO_5835  vxdump misses some directories and files  
PHCO_7272  fbackup(1M)/frecover(1M) cumulative patch  
PHCO_7798  libc cumulative patch  
PHCO_8205  cumulative LVM commands patch  
PHKL_5775  Panic on MP system when abort of busy print job  
PHKL_6010  Fixes panic data page fault in vfork()  
PHKL_6098  K-Series panic when installing SE SCSI card  
PHKL_6298  Fix data corruption from DDS to shared memory  
PHKL_6661  Improve select() performance  
PHKL_6686  O_SYNC performance and utime flexibility  
PHKL_6784  Fix for bmap() panic and data corruption  
PHKL_6871  Fix data corruption with FDDI  
PHKL_7054  fix for process hangs in semop(2)  
PHKL_7214  panic: Spinlock deadlock!  
PHKL_7218  fixes LVM panic on bad block relocation  
PHKL_7243  Fix in smmap for file systems that don't do ACLs  
PHKL_7300  Fixes for shared-memory and memory-mapped file  
PHKL_7358  Fixes conditional trap panic and system hang  
PHKL_7475  patch for vfork() and spinlock panics  
PHKL_7530  Fix for data page fault in hdl_mprot_getprotids  
PHKL_7636  HSC/GSC+ SCSI cumulative patch  
PHKL_7847  LVM cumulative patch  
PHKL_7861  tape driver cumulative patch  
PHKL_7879  HP-PB Fast/Wide cumulative SCSI patch  
PHKL_8060  process management and signals cumulative patch   
PHKL_8124  SCSI disk, diags, and jukebox cumulative patch  
PHKL_8268  Data corruption on MP systems with LVM mirroring  
PHKL_8283  VxFS (JFS), LVM, and HFS (UFS) cumulative patch  
PHKL_8330  VxFS (JFS) hang and UFS data corruption   
PHNE_6528  LAN products patch  
PHNE_6709  ARPA Transport cumulative patch  
PHNE_6978 STREAMS cumulative patch
PHNE_7075 Cumulative Mux and Pty Patch   
                       HP-UX Release 9.04  
  
PHCO_3827  Volume Group activation fails due to vgcfgrestore  
PHCO_5544  Fixes vgcfgbackup core dump problem  
PHCO_6738  cumulative fbackup(1M) and frecover(1M) patch  
PHCO_7748  libc cumulative patch  
PHKL_3457  MP system hangs when "core" file hard linked  
PHKL_3871  data segmentation trap in dumpswap() debug code  
PHKL_4589  Fixes panic in unp_externalize()  
PHKL_4675  data segmentation panic in kmem_free()  
PHKL_4700  disc2 driver patch for hangs and panics  
PHKL_5023  patches for HP-PB HP-FL disk drives  
PHKL_5139  cumulative async driver with KI instrumentation  
PHKL_5350  loss after fsync+crash, mangled directories  
PHKL_5710  panic: wait_for_lock_spinner: Already own lock!  
PHKL_5777  LVM can corrupt stream data  
PHKL_6093  Fix for bmap() panic and data corruption  
PHKL_6143  Fixes boot hang and potential SDS data corruption  
PHKL_6284  Allow raw read to span contiguous memory chunks  
PHKL_6496  fixes LVM auto resync and lv_fixrootlv panic  
PHKL_6761  NFS and filesystem kernel megapatch  
PHKL_6870  Fix for data corruption with FDDI  
PHKL_6873  HP-PB Fast-Wide SCSI cumulative patch  
PHKL_7036  cumulative LVM kernel patch  
PHKL_7058  file locking patch including NFS lock hang  
PHKL_7373  Disk, LVM, and autochanger/magneto-optic fixes  
PHKL_7643  HP-PB SE SCSI and tape driver cumulative patch  
PHKL_8230  sysmemunreserve() panic and kalloc() w/NOWAIT  
PHNE_6757  mux and pty cumulative patch  
PHNE_6856  LAN and DLPI drivers cumulative patch  
PHNE_7317  cumulative ARPA Transport patch  
PHNE_7916 rcp(1) cumulative patch
PHSS_8253 ld(1) and dld.sl(5) cumulative patch

    HP 9000 Series 700 Recommended Patches (Updated: 9/26/96)   

                       HP-UX Release 10.20  
  
PHKL_7763 Data loss when truncating VxFS (JFS) files  
PHKL_7765 hpux(1M) for kernels larger than 13 MBytes  
PHKL_7776 Fix for unmountable VxFS (JFS) file system  
PHKL_7870 LVM lvreduce kernel inconsistency panic fix  
PHKL_7899 JFS KI, page fault, deadlock, & setuid fixes  
PHKL_8084 LVM Cumulative Patch  
PHKL_8203 Fix for system hang during panic  
PHKL_8294 Fix for NFS server hang  
PHKL_8331 Data loss with UFS files using fragments  
PHNE_6190 ocd(1M) cumulative patch
PHNE_7601 Mux and Pty cumulative patch   
                       HP-UX Release 10.10  
  
PHCO_7271 fbackup(1M)/frecover(1M) cumulative patch  
PHCO_7466 LVM commands cumulative patch  
PHCO_7799 libc cumulative patch  
PHKL_6662 Improve select() performance  
PHKL_6677 Hang during Reboot of MP machines  
PHKL_6682 patch for process management and signals  
PHKL_6716 Fix for page deallocation error in MEMLOGP  
PHKL_7017 VxFS (JFS) cumulative patch  
PHKL_7090 SCSI subsystem defect fixes  
PHKL_7094 fix for process hangs in semop(2)  
PHKL_7207 VxFS (JFS) vx_remove patch  
PHKL_7244 Fix in smmap for file systems that don't do ACLs  
PHKL_7301 Fix for data page fault in set_purge_SIDS()  
PHKL_7350 increase maximum number of async ports  
PHKL_7476 patch for vfork() panic  
PHKL_7882 SCSI tape driver cumulatve patch  
PHKL_8082 LVM Cumulative Patch  
PHKL_8292 cumulative file system and VM patch  
PHNE_7179 LAN products patch  
PHNE_7356 STREAMS cumulative patch  

PHNE_7600 Mux and Pty cumulative patch PHNE_7749 ARPA Transport cumulative patch   
                       HP-UX Release 10.01  
  
PHCO_5803 fsck_hfs(1M) patch for possible corruption  
PHCO_5835 vxdump misses some directories and files  
PHCO_7272 fbackup(1M)/frecover(1M) cumulative patch  
PHCO_7798 libc cumulative patch  
PHCO_8205 LVM commands cumulative patch  
PHKL_5774 Canceled print job panics system on MP system  
PHKL_6011 Fixes panic data page fault in vfork()  
PHKL_6297 Fix data corruption from DDS to shared memory  
PHKL_6660 Improve select() performance  
PHKL_6685 O_SYNC performance and utime flexibility  
PHKL_6783 Fix for bmap() panic and data corruption  
PHKL_6872 Fix for data corruption with FDDI  
PHKL_7053 fix for process hangs in semop(2)  
PHKL_7213 panic: Spinlock deadlock!  
PHKL_7217 fixes LVM panic on bad block relocation  
PHKL_7242 Fix in smmap for file systems that don't do ACLs  
PHKL_7299 Fixes for shared-memory and memory-mapped file  
PHKL_7357 Fixes conditional trap panic and system hang  
PHKL_7474 patch for vfork() and spinlock panics  
PHKL_7529 Fix for data page fault in hdl_mprot_getprotids  
PHKL_6547 GSC+ SCSI high availability (HA) functionality  
PHKL_7846 LVM cumulative patch  
PHKL_7953 tape driver cumulative patch  
PHKL_8059 process management and signals cumulative patch  
PHKL_8282 VxFS (JFS), LVM, and HFS (UFS) cumulative patch  
PHKL_8329 VxFS (JFS) hang and UFS data corruption  
PHNE_6527 LAN products patch  
PHNE_6708 ARPA Transport cumulative patch  
PHNE_6979 STREAMS cumulative patch
PHNE_7075 Cumulative Mux and Pty Patch

          Obtaining HP-UX Patches from Hewlett-Packard   

HP-UX patches are available from HP through the normal support and patch delivery processes. The patches are also available on HP SupportLine (HPSL) for immediate Internet access. The patches can be downloaded from HPSL via email, ftp, or the World Wide Web (WWW).    To download the patches from HPSL via email:   

     send PXXX_nnnn   

To download the patches from HPSL via the World Wide Web follow these steps:   

   1.Connect to the HP SupportLine World Wide Web service home

     page at URL:  
          http://us.external.hp.com/  

   2.Select Browse Patches from the HPSL home page.    3.Select Retrieve a Specific Patch from the Patch Browsing and      Downloading page.
   4.Enter the appropriate patch name in the Patch Name field.  
   5.Select Retrieve Patch Binary.  
   6.Press Retrieve.  
  

To download the patches from HPSL via ftp, follow these steps:   

   1.Connect to HPSL via ftp:

          ftp us.external.hp.com
   2.Enter your assigned ftp login at the Name prompt (if you do

     not have an assigned ftp login, follow the registration  
     process outlined below).  

   3.Enter your assigned ftp password at the Password prompt.    4.Download the patch:

          get hp-ux_patches/s800/10.X/PXXX_nnnn /tmp/PXXX_nnnn    5.Disconnect from HPSL:

          quit   

In order to access HPSL via ftp you must first obtain an HPSL account and configure the account to use ftp. To set up the account, follow these steps:   

   1.Connect to HPSL via telnet:

          telnet us.external.hp.com
   2.Type hpslreg at the login prompt.    3.Answer the questions and be sure to write down the system

     handle and password that you are assigned at the end of the  
     registration process.  This information will be requested  
     each time you access HP SupportLine.  
  

To configure the account for ftp access, follow these steps:   

   1.Connect to HPSL via telnet:

          telnet us.external.hp.com
   2.Type hpsl at the login prompt.
   3.Enter your assigned system handle at the Please enter system      handle prompt.

   4.Enter your assigned password at the Enter password prompt.  
   5.Select Patch support information from the Top menu.  
   6.Select Retrieve patch file transfer login from the Patch  
     Support Information menu.  

   7.Set the transfer method to ftp.
   8.Write down the patch file transfer login; it will be used      when accessing HPSupportLine via ftp.    9.Exit HP SupportLine by typing exit.   

You may now connect to HP SupportLine via ftp as described above.

       Oracle Revisions for HP-UX and Ordering Information   

The following Oracle version releases incorporate changes that will enhance the stability and performance of your Hewlett-Packard/Oracle environment.   

HP-UX Revision Oracle Update

10.x            7.3.2.2 or  
                later             
9.0x/10.x       7.2.3  
9.0x/10.x       7.1.6.2  
  
  

Oracle recommends you update to Oracle 7.3.2.2 or the latest release available if you are operating in the HP-UX 10.x environment. Alternatively, if you are currently using Oracle 7.2 or Oracle 7.1 and cannot update to Oracle 7.3 at this time please order the appropriate 7.2 or 7.1 version/release for your environment. Oracle Applications are currently certified with Oracle 7.1.6; please contact Oracle Support for information concerning the most current version, prior to updating.   

Please call Oracle US Support, press option 4 (non-technical issues) and select option 1 "Product Availability and Software Updates" to process your update request. You will need to provide the following information:     

For your future reference the Oracle Product Availability Fax Back can be accessed by calling the Oracle US Support, selecting option 1 (fax back) and option 3 (Product Availability). Product Availability can also be accessed at the Oracle Client Relations website:
http://support.us.oracle.com/client_relations/americas/usa/       

Please Note: The release notes shipped with 7.3.2.2 for Hewlett-Packard HP-UX has two errors in the 'Required Oracle Patches' section:   

   1.302702 should be 382702.
   2.387431 should not have been listed. Received on Fri Jan 03 1997 - 00:00:00 CST

Original text of this message

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