Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Large SGA - performance problems
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 OracleCorporation
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 fragmentsPHNE_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
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 patchPHNE_6978 STREAMS cumulative 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 patchPHNE_7916 rcp(1) 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 fragmentsPHNE_6190 ocd(1M) 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
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 patchPHNE_6979 STREAMS cumulative 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/
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).
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.
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