This is a follow up to my previous post “Faster commit time with fewer sessions“. I’ve put together a test case against an HP-UX server running 10.2.0.3 of Oracle that shows commits six times faster with shared servers than with dedicated servers.
In this test I have an 8 core server. I increased the large pool to 500 megabytes and set the shared servers to 16 and dispatchers to 32. This enabled shared servers. Then I ran 160,000 inserts and commits from 128 simultaneous sqlplus sessions once with dedicated servers and once with shared. Shared servers ran in about 1/6th of the time as dedicated.
In my first presentation, “Focusing Your Oracle Database Tuning Efforts For PeopleSoft Applications” which is available on my resources page I introduce my script for a session profile. I used this profile script based on V$ tables to compare the waits and CPU used with dedicated and shared servers.
Dedicated:
TIMESOURCE SECONDS PERCENTAGE ------------------------------ ---------- ---------- TOTAL_TIME 617 100 UNACCOUNTED_TIME 487 79 log file sync 51 8 CPU 36 6 SQL*Net message from client 22 4 events in waitclass Other 16 3 latch: cache buffers chains 4 1
Shared:
TIMESOURCE SECONDS PERCENTAGE ------------------------------ ---------- ---------- TOTAL_TIME 106 100 UNACCOUNTED_TIME 98 92 log file sync 5 5 CPU 3 3
So, total time of 617 seconds for dedicated versus 106 for shared. Interestingly in my previous post I was testing against 11.2.0.3 on 32 bit windows and didn’t have the unaccounted time. Unaccounted time is usually wait for the CPU or for paging. I’m pretty sure this is really the time the log file sync sits in the CPU queue.
Here is CPU with dedicated servers:
CPU use dedicated servers
Here is the CPU use with shared servers:
- CPU use shared servers
The CPU wasn’t pegged at 100% in the shared servers case so LGWR was free to process commits at the rate the I/O system was capable of supporting.
Here are the top events from the AWR report in both cases.
Dedicated:
Top 5 Timed Events
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| log file sync | 153,329 | 5,857 | 38 | 8.2 | Commit |
| CPU time | 5,043 | 7.1 | |||
| latch free | 33,256 | 1,023 | 31 | 1.4 | Other |
| latch: cache buffers chains | 26,058 | 563 | 22 | .8 | Concurrency |
| latch: enqueue hash chains | 19,766 | 550 | 28 | .8 | Other |
Shared:
Top 5 Timed Events
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| CPU time | 1,049 | 79.9 | |||
| log file sync | 169,941 | 625 | 4 | 47.6 | Commit |
| log file parallel write | 31,951 | 43 | 1 | 3.3 | System I/O |
| latch: shared pool | 23,044 | 15 | 1 | 1.2 | Concurrency |
| latch free | 14,431 | 9 | 1 | .7 | Other |
Notice how the log file sync = commit time is almost ten times as long with dedicated servers.
I had to change these parameters to setup shared servers:
large_pool_size = 500M sga_max_size = 2544M sga_target = 2544M dispatchers = "(PROTOCOL=TCP)(DISPATCHERS=32)" shared_servers = 16 max_shared_servers = 16
I created a 500 meg large pool and added 500 meg to the sga_max_size and sga_target parameters. I set dispatchers at 32 but I don’t think this was that significant. The key was shared_servers and max_shared_servers at 16. This kept the commits from overloading the 8 cores.
Here are the tnsnames.ora entries:
DEDICATED = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = xxxxxx)(Port = 1522)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = xxxxxxx)) ) SHARED = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = xxxxxx)(Port = 1522)) ) (CONNECT_DATA = (SERVER = SHARED) (SID = xxxx)) )
Interestingly, if I didn’t specify (SERVER = DEDICATED) the connection defaulted to shared. So, potentially we could just set the six parameters on our production database, bounce it, and the web servers would start using shared servers and run much faster. Time will tell, but this test was pretty convincing and easy to setup.
– Bobby
