Faster commit time with shared servers

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

CPU use dedicated servers

Here is the CPU use with shared servers:

CPU use 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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.