21 | 04 | 2021
Latest Articles
Popular Articles


Shared Server

User Rating:  / 1

Shared Server

Until last year I never used shared server in production, so I am far from a goeroe in this area.

What do I have in mind about when to use shared server

1) When there are lots off database connections let' s say a few thousands
2) of which the majority are non active, let' s say 90 pct are not active
3) the transactions are rather short certainly no batch processing

Setting it up is easy, I configured the below listed instance initialization parameters for an average of 100 active sessions and a maximum of 5000 sessions.
That does not mean you can use them as well, but it might give you an idea.

shared_servers = 150
max_shared_servers = 300
dispatchers = '(PROTOCOL = TCP)(DISPATCHERS = 5)'
sessions = 5500
shared_server_sessions = 5000
processes = 1000
large_pool_size = 2G
db_cache_size =  2G
shared_pool_size =  2G
sga_target = 0M  ( I dislike ASMM certainly with shared server configuration )
pga_aggregate_target = 2G
workarea_size_policy = AUTO

From 10G R1 onwards and when workarea_size_policy = auto the user work area' s ( sort area, hash area, bitmap merge area ) is sized by the instance initialization parameter pga_agregate_target and this whether we are using shared or dedicated server connections.

When using shared server and RDBMS < 10G R1 or when using shared server with 10G R1/R2 but with workarea_size_policy = manual, the sizing of the users work area was / is still done with the old fashionned parameters sort_area_size, sort_area_retained_size,hash_area_size and bitmap_merge_area_size .

Note that the user work area' s ( sort area, hash area, bitmap merge area ) also called the UGA is in the SGA when using shared server. By preference the UGA is located in the large pool, thus we set large_pool_size = pga_aggregate_target. if the UGA or a part from the UGA cannot be located in the large pool it will be put in the shared pool.

I have to admit that even with 10G I faced several ora 4031 at the level of the library cache as soon as I setup shared server. Finally I got it more stable by increasing significantly the shared pool, by turning ASMM of ( sga_target = 0 ) and by turning of the library cache and db cache advisories ( _library_cache_advice = false and db_cache_advice = off )

During non functional tests we faced finally a CPU bottleneck, more precisely latch waits at the level of virtual circuits were noticed whereas we could achieve a better thoughput with a dedicated server setup. ( read "more database work done in the same time" ). Obviously we needed to limit the amount of dedicated server sessions / processes.

It is told one can query v$shared_server_monitor, v$queue, v$circuit, v$dispatcher, v$dispatcher_rate for shared server tuning purposes. To be honest I got more info with v$sgastat.  A "select * v$sgastat order by bytes" lists me the top shared and large pool memory consumers at the bottom end of the listing. The statistic "large pool.session heap" is increasing in equal proportion to the amount of sessions. You can use that for large pool sizing. You may want to throw an eye on the statistics "shared pool free memory" "large pool free memory" and "shared pool "virtual circuits". ( and others )

There are some attributes one can use for dispatcher configurations. I guess they are usefull but am not aware of it. Browse the Administration Guide for that purpose.

To my experience shared server was significantly more stable with compared to