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