Evaluate the shared server vs dedicated server execution differences on a concurrent job situation

Lab objective: Evaluate the shared server vs dedicated server execution differences on a concurrent job situation

This time, we have to spawn 10 connections concurrently in dedicated & in shared mode.

Queries to be used for this excercise:

To monitor the pga/uga per group of connection:

---monitor_pgauga.sql

set lines 300
set pages 3000
set feedback off
select c.server,a.name,count(c.sid),sum(b.value)
from v$statname a, v$sesstat b,v$session c
where c.username='SCOTT'
and b.sid=c.sid
and a.statistic#=b.statistic#
and lower(a.name) in ('session uga memory','session uga memory max','session pga memory','session pga memory max')
group by c.server,a.name
order by 1,2;
exit;

---ugaexamination_lab.sql

set term off
select a.owner,a.object_id,b.object_name
from dba_objects a,dba_objects b
where a.object_id=b.object_id
and a.object_id > 10000;
dbms_lock.sleep(1);
select a.owner,a.object_id,b.object_name
from dba_objects a,dba_objects b
where a.object_id=b.object_id
and a.object_id > 10000;
dbms_lock.sleep(1);
select a.owner,a.object_id,b.object_name
from dba_objects a,dba_objects b
where a.object_id=b.object_id
and a.object_id > 10000;
exit;

---- shell script to generate the workload of dedicated connection

##wrapper_launch_dedicated_conn.sh
#!/bin/ksh
export ORAENV_ASK=NO
. oraenv testinst
sqlplus -s '/as sysdba' @create_snap.sql
i=1
while [[ $i -le $1 ]];
do
dt=`date +%d%m%y_%H%M%S`
sqlplus -s scott/<PWD>@oraded @ugaexamination_lab.sql &
(( i++ ))
done
statprcnt=`ps -ef|grep -i sqlplus|grep -i ugaexamination_lab.sql|wc -l`
while [[ ${statprcnt} -gt 0 ]];
do
sleep 3
statprcnt=`ps -ef|grep -i sqlplus|grep -i ugaexamination_lab.sql|wc -l`
done
sqlplus -s '/as sysdba' @create_snap.sql
exit

---- shell script to monitor the pga/uga utilisation

###monitor_pgauga.sh
#!/bin.ksh
export ORAENV_ASK=NO
. oraenv testinst
while :
do
echo "###################"
dt=`date +%d%m%y_%H%M%S`
echo "zzzzzz $dt"
sqlplus -s '/as sysdba' @monitor_pgauga.sql
dt1=`date +%d%m%y_%H%M%S`
echo "$dt1: going to sleep rountine 1 for 1 secs.. press ctrl+c here if you intend to kill the script"
sleep 1
dt2=`date +%d%m%y_%H%M%S`
echo "$dt2: going to sleep rountine 1 for 1 secs.."
sleep 1
done


---- shell script to generate the workload of shared connection

##wrapper_launch_shared_conn.sh
#!/bin/ksh
export ORAENV_ASK=NO
. oraenv testinst
sqlplus -s '/as sysdba' @create_snap.sql
i=1
while [[ $i -le $1 ]];
do
dt=`date +%d%m%y_%H%M%S`
sqlplus -s scott/<PWD>@orashared @ugaexamination_lab.sql &
(( i++ ))
done
statprcnt=`ps -ef|grep -i sqlplus|grep -i ugaexamination_lab.sql|wc -l`
while [[ ${statprcnt} -gt 0 ]];
do
sleep 3
statprcnt=`ps -ef|grep -i sqlplus|grep -i ugaexamination_lab.sql|wc -l`
done
sqlplus -s '/as sysdba' @create_snap.sql
exit

===> Round 1, launch the below shell scripts for dedicated connection test.

screen 1:
./monitor_pgauga.sh

example output:
###################
zzzzzz 170319_205801
170319_205801: going to sleep rountine 1 for 1 secs.. press ctrl+c here if you intend to kill the script
170319_205802: going to sleep rountine 1 for 1 secs..
###################
zzzzzz 170319_205804

SERVER    NAME                                                             COUNT(C.SID) SUM(B.VALUE)
--------- ---------------------------------------------------------------- ------------ ------------
DEDICATED session pga memory                                                         10     13941264
DEDICATED session pga memory max                                                     10     14400016
DEDICATED session uga memory                                                         10      3375896
DEDICATED session uga memory max                                                     10      4015872
170319_205804: going to sleep rountine 1 for 1 secs.. press ctrl+c here if you intend to kill the script
170319_205805: going to sleep rountine 1 for 1 secs..
###################


screen 2:
./wrapper_launch_dedicated_conn.sh 10

example output:
##################
The Oracle base has been set to /u01/app/oracle

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

[oracle@host testdir]$

===> Bounce the DB

===> Round 2, lauch the below shell scripts for shared server connection test.
screen 1:

./monitor_pgauga.sh

170319_212839: going to sleep rountine 1 for 1 secs..
###################
zzzzzz 170319_212840

SERVER    NAME                                                             COUNT(C.SID) SUM(B.VALUE)
--------- ---------------------------------------------------------------- ------------ ------------
SHARED    session pga memory                                                          7     47667512
SHARED    session pga memory max                                                      7     52927448
SHARED    session uga memory                                                          7      8654272
SHARED    session uga memory max                                                      7      8760544
170319_212841: going to sleep rountine 1 for 1 secs.. press ctrl+c here if you intend to kill the script
170319_212842: going to sleep rountine 1 for 1 secs..
###################


screen 2:

./wrapper_launch_shared_conn.sh 10

example output:
The Oracle base has been set to /u01/app/oracle

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

[oracle@host testdir]$

Differences:

Fact 1 ) Examine session response time:

Dedicated conn lasted for : 170319_205804 to 170319_205954 [until the last connection exited] => 110 seconds
Shared conn lasted for : 170319_212624 to 170319_212921  [until the last connection exited] => 177 seconds

So there is about 67 seconds latency on shared server.

Fact 2) Examine memory utilisation:
Dedicated: [last but 1 snap view - when all 10 connections were active] - here the uga resides in pga

SERVER    NAME                                                             COUNT(C.SID) SUM(B.VALUE)
--------- ---------------------------------------------------------------- ------------ ------------
DEDICATED session pga memory                                                         10    111852048
DEDICATED session pga memory max                                                     10    116111888
DEDICATED session uga memory                                                         10    101117584
DEDICATED session uga memory max                                                     10    101264424

So overall PGA use is 110.73 MB.

Shared: [last snap with 10 conn view]

SERVER    NAME                                                             COUNT(C.SID) SUM(B.VALUE)
--------- ---------------------------------------------------------------- ------------ ------------
SHARED    session pga memory                                                         10     69367664
SHARED    session pga memory max                                                     10     78215024
SHARED    session uga memory                                                         10     12713760
SHARED    session uga memory max                                                     10     12814800

If we observe above
pga max: 78215024
uga max: 12814800
total: 86.81 MB

So we save about 23.9 MB in shared server vs dedicated server. Not sure if we increase the connection, we will have this difference increase as well. Though I took awr snaps, I noticed the current shell's environment variables ruined my snapshots (though the test is valid), hence I am not able to figure out which subpool in shared pool catered to shared server connection.

Thanks