Oracle-L: Exe Loading too slow on DB Server

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
Date: Tue, 02 Apr 2002 19:33:20 -0800
Message-ID: <F001.00439773.20020402193320@fatcity.com>

Are there any Best Practices of Tuning Solaris 8 for HIGH-End heavily Loaded Machines ?

A Brief Description of the Issue & Actions Below :-

Machine E6500
RAM 20 GB
CPUs = 20
SWAP = 26 GB After Migration from Oracle 7.3.4 to 8.1.7.2 on Solaris 2.8

sqlplus becomes SLOW as the Number of Unix Users Connecting to the Database Increases to about 400 whereafter even after all users are logged out the response continues to be Slow even after the machine is Booted i.e. ON Issuing the Command $ sqlplus it took about 5 minutes for the following Message to appear : -

" SQL*Plus: Release 8.1.7. 0.0 - Production on Tue Mar 19 09:56:05 2002

Enter user-name: "

NOTE - This Slowness is Observed BOTH When Issuing "sqlplus" Command DIRECTLY on the DB Server & Also from the APP Server Via SQL*Net

NOTE - In Oracle 7 NO such Slowness was Observed when the Total of 1200 Connect to the Database

Executed truss sqlplus with the Following Output :- $ truss sqlplus execve("/arch/oracle/bin/sqlplus", 0xFFBEFBBC, 0xFFBEFBC4) argc = 1 mmap(0x00000000, 8192, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_ANON, -1, 0) = 0xFF3A0000 resolvepath("/usr/lib/ld.so.1", "/usr/lib/ld.so.1", 1023) = 16 stat("/arch/oracle/bin/sqlplus", 0xFFBEF8F8) = 0 open("/var/ld/ld.config", O_RDONLY) Err#2 ENOENT open("/arch/oracle/lib/libclntsh.so.8.0", O_RDONLY) = 3 fstat(3, 0xFFBEF28C) = 0 mmap(0x00000000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE, 3, 0) = 0xFF390000 mmap(0x00000000, 6717440, PROT_READ|PROT_EXEC, MAP_PRIVATE, 3, 0) = 0xFEC00000 mmap(0xFF224000, 209704, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 3, 6373376) =

0xFF224000
mmap(0xFF258000, 58832, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED|MAP_ANON, -1, 0) =

0xFF258000

munmap(0xFF216000, 57344)                       = 0

memcntl(0xFEC00000, 672560, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0 close(3) <- WAIT HAPPENING AT THIS POINT

ACTIONS
ON RE-Creating file libclntsh.so.8.0 using getclntsh on the APP Server Response Message " SQL*Plus: Release 8.1.7.2. ...." became FAST ONLY for a FEW Minutes whereafter it again Became Slow

Copied libclntsh.so.8.0 to /usr/lib Dir Both on APP & DB Servers & Set LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib whereafter the response to sqlplus became for Establishment of upto 500 Process Connections Directly on the DB Server .

On Adding the Following Parameters to the /etc/system the Slowdown of "sqlplus" Connect to the Database Started Occuring after 850 Process Connections Instead of 500 (Previously) When Executing Directly on the Database Server machine .

set tcp:tcp_conn_hash_size=262144

set rlim_fd_max=8092
set rlim_fd_cur=8092
set autoup=900
set tune_t_fsflushr=1
set bufhwm=8000

INCREASED the Semaphores to the Following Values :-

/etc/system File on the DB & APP Servers :-

set shmsys:share_page_table=1
set maxuprc=20000
set maxusers=1500

set shmsys:shminfo_shmseg=200
set shmsys:shminfo_shmmin=2
set shmsys:shminfo_shmmni=5000
set shmsys:shminfo_shmmax=25769803776

forceload: sys/shmsys
forceload: sys/shmsys

set semsys:seminfo_semmsl=1000
set semsys:seminfo_semaem=16384
set semsys:seminfo_semmap=700000
set semsys:seminfo_semmni=60000
set semsys:seminfo_semmns=100000
set semsys:seminfo_semmnu=10000
set semsys:seminfo_semume=100
set semsys:seminfo_semvmx=32767

Because of Increase of SEMAPHORES ( semmns to 100,000 from 20,000 ) the Slowdown of "sqlplus" to Establish a Connect to Database started Occuring after 1050 Process Connections Instead of 850 (Previously) .

We are yet to try the Settings of Reducing swapfs_minfree , noatime Option with mount , inode cache Hit ratio as advised Below

-----Original Message-----
Sent: Wednesday, March 27, 2002 12:11 AM To: VIVEK_SHARMA
Servers

You may need more swap. I've had the best luck with multiple 1-Gbyte swap partitions on different devices.

V> After Migration from Oracle 7.3.4 to 8.1.7.2 on Solaris 2.8 sqlplus
V> becomes SLOW as the Number of Unix Users Connecting to the Database
V> Increases to about 400 whereafter even after all users are logged out
V> the response continues to be Slow even after the machine is Booted

Some suggestions are below. You may be spending too much time scanning memory for dirty pages.

If you have an application that's an incredible swap hog, or the system is really slowing down, try adding the lines below to /etc/system and rebooting. I run with these settings and they've never caused me trouble.

Swap
System keeps 1/8th of all memory for swap, which is too much for
a 4GB system. Reduce that to 32 Mbytes (4096 8K pages). set swapfs_minfree=4096
*
Memory management
*
To decrease the amount of memory fsflush scans during any scan
interval, we must modify the kernel variable autoup. The default
is 30. For firewalls with 128MB of RAM or more, increase this
value. The end result is less time spent managing buffers,
and more time spent servicing packets. set autoup = 120

Run "mount" to see how your filesystems are set up. You can mount filesystems with "noatime" turned on, meaning don't bother updating the access time whenever a file is opened. We use this under Solaris-8, and it makes a *huge* difference if you're doing something to a large number of small or medium sized files.

Check the size of your inode caches, which keep track of previously-accessed files. Run the DNLC script as root to see your hit-rate percentage. If it's under 90-95%, you need to up the cache size. The easiest way to do that is setting maxusers in /etc/system to something like 2048.

Karl Vogel               
vogelke_at_dnaco.net

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

Received on Tue Apr 02 2002 - 21:33:20 CST