Lab objective: examine pga usage for dedicated vs shared server connection
concept guide: [read it once before proceeding to this blog]
https://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT7776
https://docs.oracle.com/database/121/TGDBA/tune_pga.htm#TGDBA347
Snipet of understandings:
UGA part of pga - user session memory, the session memory which must be available for the duration of the session.
Shared server:
UGA will be located in SGA [this includes session memory,persistent area and runtime area for select,since the doc says dml/ddl runtime goes to pga)
Dedicated server:
UGA will be in PGA
SQL work area part of pga, such as sort area,hash area,bitmap merge area all stay in the pga irrespective of the type of connection.
Setup:
1) Follow previous blog on shared server setup [http://oracledbaplanner.blogspot.com/2019/01/configure-shared-server-on-oracle-121.html]
2) Frame a query and get ready to dump the heapdump for pga for the session we ran this query
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;
3) question, how will you dump heap dump for a shared server connection, since it mightnt have any process associated with it. We have to dump the heapdump from the same session (such that a shared server will pick it up) where we are running this sql. To keep this simple, let us do the heapdump for both the dedicated and shared server connection from the same session and observe how they are different.
Queries to be used:
col server for a10
col service_name for a12
col username for a9
col program for a45
col module for a15
set lines 300
set pages 3000
alter session set nls_date_Format='DD/MM/YYYY HH24:MI:SS';
select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;
Options to take heapdump:
1) << this might not work for shared server case from another session
ORADEBUG SETMYPID or ORADEBUG SETORAPID <PID> or ORADEBUG SETOSPID <SPID>
ORADEBUG UNLIMIT
ALTER SESSION SET TRACEFILE_IDENTIFIER='PGA_UGA';
ORADEBUG DUMP HEAPDUMP 5
ORADEBUG CLOSE_TRACE
2)
alter session set events 'immediate trace name heapdump level 5';
There is also a mention about heapdump level 29,1025 for PGA. But let us examine level 5 for now.
Exercise:
A) Dedicated connection examination:
SQL> select server,service_name,sid,serial#,username,program,module,sql_id,prev_sql_id,logon_time,sql_exec_start,process
2 from v$session
where username='SCOTT'
order by server; 3 4
SERVER SERVICE_NAME SID SERIAL# USERNAME PROGRAM MODULE SQL_ID PREV_SQL_ID LOGON_TIME
SQL_EXEC_START PROCESS
---------- ------------ ---------- ---------- --------- --------------------------------------------- --------------- ------------- ------------- -------------------
------------------- ------------------------
DEDICATED oratest 261 5 SCOTT sqlplus@host.oracle.com (TNS V1-V3) SQL*Plus 6nar286qywyfa 10/03/2019 21:27:11
9538
SQL>
suppress the records for the query... by doing term off
sql_id = 6nar286qywyfa
so we have a shared sql area for this sql_id in shared pool.
Simillarly we have a cursor (pointer) for this sql_id in pga (UGA).
We will dump memory info now for this process.
select pid,spid,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where addr=(select paddr from v$session where sid=261);
SQL> select pid,spid,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where addr=(select paddr from v$session where sid=261); 2 3
PID SPID PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ ------------ ------------- ---------------- -----------
49 9540 1941437 3085525 1048576 14030037
===== actual session screen commands...
[oracle@host ~]$ sqlplus scott@oratest
SQL*Plus: Release 12.1.0.1.0 Production on Sun Mar 10 21:27:09 2019
...
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @<labpath>/ugaexamination_lab.sql
SQL> alter session set events 'immediate trace name heapdump level 5';
Session altered.
SQL> exit
B) Shared server connection examination:
Note: restarted the db here to avoid cache & shared pool effect.
[oracle@host ~]$ tnsping oratest_shared
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 10-MAR-2019 21:31:40
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
<ORACLE_HOME>/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = oratest)))
OK (10 msec)
SQL> select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server; 2 3 4
SERVER SERVICE_NAME SID SERIAL# USERNAME PROGRAM MODULE SQL_ID LOGON_TIME
SQL_EXEC_START PROCESS
---------- ------------ ---------- ---------- --------- --------------------------------------------- --------------- ------------- -------------------
------------------- ------------------------
NONE oratest 19 7 SCOTT sqlplus@host.oracle.com (TNS V1-V3) SQL*Plus 10/03/2019 21:32:09 9725
SQL>
SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server; 2 3 4
SERVER SERVICE_NAME SID SERIAL# USERNAME SQL_ID LOGON_TIME SQL_EXEC_START PROCESS
---------- ------------ ---------- ---------- --------- ------------- ------------------- ------------------- ------------------------
NONE oratest 19 7 SCOTT 10/03/2019 21:32:09 9725
SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
2 from v$circuit c
3 ,v$session s
4 ,v$process p1
5 ,v$process p2
where c.saddr=s.saddr
6 7 and c.dispatcher=p1.addr
and c.server=p2.addr(+);
8
CIRCUIT DISP_ SS_NAME SID SERIAL# STATUS QUEUE
---------------- ----- --------- ---------- ---------- ---------------- ----------------
0000000098434028 D000 NO_SERVER 19 7 NORMAL NONE
SQL>
right now, there is no process this session is attached to, hence I am wondering how do we compute its pga, its not possibe since the shared server connection will move from one process to another (example dispatcher to shared server and to another shared server etc...), hence everytime we dump the information.. I think we will get only the pga of a dispatcher or shard server processes + UGA of the shared server connection. So this is going to work as per defination. Let us see...
select pid,spid,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where addr=(select paddr from v$session where sid=19);
SQL> select pid,spid,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where addr=(select paddr from v$session where sid=19); 2 3
PID SPID PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ ------------ ------------- ---------------- -----------
21 9609 1618357 1709269 0 1709269
SQL>
SQL> !ps -ef|grep 9609
oracle 9609 1 0 21:30 ? 00:00:00 ora_d000_oratest
oracle 9758 8856 0 21:36 pts/0 00:00:00 /bin/bash -c ps -ef|grep 9609
oracle 9760 9758 0 21:36 pts/0 00:00:00 grep 9609
<<<< right now the connection is idle, hence there wont be any process memory.
SQL> select server,service_name,sid,serial#,username,program,module,sql_id,prev_sql_id,logon_time,sql_exec_start,process
2 from v$session
where username='SCOTT'
order by server; 3 4
SERVER SERVICE_NAME SID SERIAL# USERNAME PROGRAM MODULE SQL_ID PREV_SQL_ID LOGON_TIME
SQL_EXEC_START PROCESS
---------- ------------ ---------- ---------- --------- --------------------------------------------- --------------- ------------- ------------- -------------------
------------------- ------------------------
NONE oratest 19 7 SCOTT sqlplus@host.oracle.com (TNS V1-V3) SQL*Plus 6nar286qywyfa 10/03/2019 21:32:09
9725
SQL>
so we executed the sql... it matches with the dedicated one. Let us go check its heapdump.. anyway see again the server is NONE now, so idle. Let us try it anyways. Being this is very spotaneous, i couldnt check which shared server took the process load of heapdump. But I saw in the trace dump directory s000 went ahead with update. So I belive the s000 is the one which took the process load. I also verified it by further probes below...
SQL> !ps -ef|grep ora_s|grep oratest
oracle 9599 1 0 21:30 ? 00:00:00 ora_smon_oratest
oracle 9615 1 0 21:30 ? 00:00:03 ora_s000_oratest <<<<<<<<<< only one shared server which is s000
oracle 9633 1 0 21:30 ? 00:00:00 ora_smco_oratest
oracle 9855 8856 0 21:46 pts/0 00:00:00 /bin/bash -c ps -ef|grep ora_s|grep oratest
SQL> sho parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer
shared_servers integer 1
SQL> select * from V$SHARED_SERVER_MONITOR;
MAXIMUM_CONNECTIONS MAXIMUM_SESSIONS SERVERS_STARTED SERVERS_TERMINATED SERVERS_HIGHWATER CON_ID
------------------- ---------------- --------------- ------------------ ----------------- ----------
1 1 0 0 1 0 <<< HWM is @ 1, so no new startups of shared server
SQL>
SQL> select pid,spid,PROGRAM,pname,BACKGROUND,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from v$process where pname like 'S%';
PID SPID PROGRAM PNAME B PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ --------------------------------------------- ----- - ------------ ------------- ---------------- -----------
16 9599 oracle@host.oracle.com (SMON) SMON 1 1361061 1774805 65536 1774805
24 9615 oracle@host.oracle.com (S000) S000 1025213 2216501 983040 10408501
28 9633 oracle@host.oracle.com (SMCO) SMCO 1 1128053 1250517 0 1250517
SQL>
drwxr-x---. 6 oracle oracle 131072 Mar 10 21:44 .
-rw-r-----. 1 oracle oracle 403 Mar 10 21:44 oratest_s000_9615.trm
-rw-r-----. 1 oracle oracle 87919 Mar 10 21:44 oratest_s000_9615.trc
[oracle@host trace]$ date
Sun Mar 10 21:45:59 CST 2019
[oracle@host trace]$
==ACTUAL SCREEN of shared server connection:
[oracle@host ~]$ sqlplus scott@oratest_shared
...
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @<labpath>/ugaexamination_lab.sql
SQL> alter session set events 'immediate trace name heapdump level 5';
Session altered.
SQL> exit
######### Analyze the difference
so we are good to examine the difference... what we will learn here is, the s000 was idle before, it did only one processing which is the sql as dedicated. Hence its pga is valid to be considered.
[oracle@host lab10mar19]$ ls -altr *PGAHEAP
-rw-r-----. 1 oracle oracle 87919 Mar 10 21:54 oratest_s000_9615.trc_SHARED_PGAHEAP
-rw-r-----. 1 oracle oracle 81928 Mar 10 21:54 oratest_ora_9540.trc_DEDICATED_PGAHEAP
[oracle@host lab10mar19]$
for an idle shared server.. the memory usage is...
SQL> select pid,spid,PROGRAM,pname,BACKGROUND,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from v$process where pname like 'S000%';
PID SPID PROGRAM PNAME B PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ ------------------------------------------------ ----- - ------------ ------------- ---------------- -----------
24 10690 oracle@host.oracle.com (S000) S000 930629 1053909 0 1053909
SQL>
post processing a session... [dont worry abt spid change, i did bounce the db to reverify this figures].
SQL> select pid,spid,PROGRAM,pname,BACKGROUND,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from v$process where pname like 'S%';
PID SPID PROGRAM PNAME B PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ --------------------------------------------- ----- - ------------ ------------- ---------------- -----------
24 9615 oracle@host.oracle.com (S000) S000 1025213 2216501 983040 10408501
for 10690 spid - the verification one:
PID SPID PROGRAM PNAME B PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ ------------------------------------------------ ----- - ------------ ------------- ---------------- -----------
24 10690 oracle@host.oracle.com (S000) S000 1025213 2216501 983040 10408501
#### Let us use heap dump analyzer (credits to Tanel Poder, https://blog.tanelpoder.com/2009/01/02/oracle-memory-troubleshooting-part-1-heapdump-analyzer/)
I applied the heap dump analyzer to see a summary of heap distribution and observed the below....
dedicated:
shared:
The dedicated pga size does differ from heapdump trace value vs shared server heap dump is matching close (in its usage as well).
Learning: On dedicated connection heapdump we see uga heaps vs on the shared server connection we dont see the uga heap. This is in line with oracle documentation. The uga should have been allocated from the oracle sga, may be in next exercise we will see which memory area is hosting this uga (shared pool or large pool).
Thanks for paying a visit, your feedback is welcome.
concept guide: [read it once before proceeding to this blog]
https://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT7776
https://docs.oracle.com/database/121/TGDBA/tune_pga.htm#TGDBA347
Snipet of understandings:
UGA part of pga - user session memory, the session memory which must be available for the duration of the session.
Shared server:
UGA will be located in SGA [this includes session memory,persistent area and runtime area for select,since the doc says dml/ddl runtime goes to pga)
Dedicated server:
UGA will be in PGA
SQL work area part of pga, such as sort area,hash area,bitmap merge area all stay in the pga irrespective of the type of connection.
Setup:
1) Follow previous blog on shared server setup [http://oracledbaplanner.blogspot.com/2019/01/configure-shared-server-on-oracle-121.html]
2) Frame a query and get ready to dump the heapdump for pga for the session we ran this query
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;
3) question, how will you dump heap dump for a shared server connection, since it mightnt have any process associated with it. We have to dump the heapdump from the same session (such that a shared server will pick it up) where we are running this sql. To keep this simple, let us do the heapdump for both the dedicated and shared server connection from the same session and observe how they are different.
Queries to be used:
col server for a10
col service_name for a12
col username for a9
col program for a45
col module for a15
set lines 300
set pages 3000
alter session set nls_date_Format='DD/MM/YYYY HH24:MI:SS';
select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;
Options to take heapdump:
1) << this might not work for shared server case from another session
ORADEBUG SETMYPID or ORADEBUG SETORAPID <PID> or ORADEBUG SETOSPID <SPID>
ORADEBUG UNLIMIT
ALTER SESSION SET TRACEFILE_IDENTIFIER='PGA_UGA';
ORADEBUG DUMP HEAPDUMP 5
ORADEBUG CLOSE_TRACE
2)
alter session set events 'immediate trace name heapdump level 5';
There is also a mention about heapdump level 29,1025 for PGA. But let us examine level 5 for now.
Exercise:
A) Dedicated connection examination:
SQL> select server,service_name,sid,serial#,username,program,module,sql_id,prev_sql_id,logon_time,sql_exec_start,process
2 from v$session
where username='SCOTT'
order by server; 3 4
SERVER SERVICE_NAME SID SERIAL# USERNAME PROGRAM MODULE SQL_ID PREV_SQL_ID LOGON_TIME
SQL_EXEC_START PROCESS
---------- ------------ ---------- ---------- --------- --------------------------------------------- --------------- ------------- ------------- -------------------
------------------- ------------------------
DEDICATED oratest 261 5 SCOTT sqlplus@host.oracle.com (TNS V1-V3) SQL*Plus 6nar286qywyfa 10/03/2019 21:27:11
9538
SQL>
suppress the records for the query... by doing term off
sql_id = 6nar286qywyfa
so we have a shared sql area for this sql_id in shared pool.
Simillarly we have a cursor (pointer) for this sql_id in pga (UGA).
We will dump memory info now for this process.
select pid,spid,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where addr=(select paddr from v$session where sid=261);
SQL> select pid,spid,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where addr=(select paddr from v$session where sid=261); 2 3
PID SPID PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ ------------ ------------- ---------------- -----------
49 9540 1941437 3085525 1048576 14030037
===== actual session screen commands...
[oracle@host ~]$ sqlplus scott@oratest
SQL*Plus: Release 12.1.0.1.0 Production on Sun Mar 10 21:27:09 2019
...
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @<labpath>/ugaexamination_lab.sql
SQL> alter session set events 'immediate trace name heapdump level 5';
Session altered.
SQL> exit
B) Shared server connection examination:
Note: restarted the db here to avoid cache & shared pool effect.
[oracle@host ~]$ tnsping oratest_shared
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 10-MAR-2019 21:31:40
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
<ORACLE_HOME>/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = oratest)))
OK (10 msec)
SQL> select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server; 2 3 4
SERVER SERVICE_NAME SID SERIAL# USERNAME PROGRAM MODULE SQL_ID LOGON_TIME
SQL_EXEC_START PROCESS
---------- ------------ ---------- ---------- --------- --------------------------------------------- --------------- ------------- -------------------
------------------- ------------------------
NONE oratest 19 7 SCOTT sqlplus@host.oracle.com (TNS V1-V3) SQL*Plus 10/03/2019 21:32:09 9725
SQL>
SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server; 2 3 4
SERVER SERVICE_NAME SID SERIAL# USERNAME SQL_ID LOGON_TIME SQL_EXEC_START PROCESS
---------- ------------ ---------- ---------- --------- ------------- ------------------- ------------------- ------------------------
NONE oratest 19 7 SCOTT 10/03/2019 21:32:09 9725
SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
2 from v$circuit c
3 ,v$session s
4 ,v$process p1
5 ,v$process p2
where c.saddr=s.saddr
6 7 and c.dispatcher=p1.addr
and c.server=p2.addr(+);
8
CIRCUIT DISP_ SS_NAME SID SERIAL# STATUS QUEUE
---------------- ----- --------- ---------- ---------- ---------------- ----------------
0000000098434028 D000 NO_SERVER 19 7 NORMAL NONE
SQL>
right now, there is no process this session is attached to, hence I am wondering how do we compute its pga, its not possibe since the shared server connection will move from one process to another (example dispatcher to shared server and to another shared server etc...), hence everytime we dump the information.. I think we will get only the pga of a dispatcher or shard server processes + UGA of the shared server connection. So this is going to work as per defination. Let us see...
select pid,spid,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where addr=(select paddr from v$session where sid=19);
SQL> select pid,spid,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where addr=(select paddr from v$session where sid=19); 2 3
PID SPID PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ ------------ ------------- ---------------- -----------
21 9609 1618357 1709269 0 1709269
SQL>
SQL> !ps -ef|grep 9609
oracle 9609 1 0 21:30 ? 00:00:00 ora_d000_oratest
oracle 9758 8856 0 21:36 pts/0 00:00:00 /bin/bash -c ps -ef|grep 9609
oracle 9760 9758 0 21:36 pts/0 00:00:00 grep 9609
<<<< right now the connection is idle, hence there wont be any process memory.
SQL> select server,service_name,sid,serial#,username,program,module,sql_id,prev_sql_id,logon_time,sql_exec_start,process
2 from v$session
where username='SCOTT'
order by server; 3 4
SERVER SERVICE_NAME SID SERIAL# USERNAME PROGRAM MODULE SQL_ID PREV_SQL_ID LOGON_TIME
SQL_EXEC_START PROCESS
---------- ------------ ---------- ---------- --------- --------------------------------------------- --------------- ------------- ------------- -------------------
------------------- ------------------------
NONE oratest 19 7 SCOTT sqlplus@host.oracle.com (TNS V1-V3) SQL*Plus 6nar286qywyfa 10/03/2019 21:32:09
9725
SQL>
so we executed the sql... it matches with the dedicated one. Let us go check its heapdump.. anyway see again the server is NONE now, so idle. Let us try it anyways. Being this is very spotaneous, i couldnt check which shared server took the process load of heapdump. But I saw in the trace dump directory s000 went ahead with update. So I belive the s000 is the one which took the process load. I also verified it by further probes below...
SQL> !ps -ef|grep ora_s|grep oratest
oracle 9599 1 0 21:30 ? 00:00:00 ora_smon_oratest
oracle 9615 1 0 21:30 ? 00:00:03 ora_s000_oratest <<<<<<<<<< only one shared server which is s000
oracle 9633 1 0 21:30 ? 00:00:00 ora_smco_oratest
oracle 9855 8856 0 21:46 pts/0 00:00:00 /bin/bash -c ps -ef|grep ora_s|grep oratest
SQL> sho parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer
shared_servers integer 1
SQL> select * from V$SHARED_SERVER_MONITOR;
MAXIMUM_CONNECTIONS MAXIMUM_SESSIONS SERVERS_STARTED SERVERS_TERMINATED SERVERS_HIGHWATER CON_ID
------------------- ---------------- --------------- ------------------ ----------------- ----------
1 1 0 0 1 0 <<< HWM is @ 1, so no new startups of shared server
SQL>
SQL> select pid,spid,PROGRAM,pname,BACKGROUND,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from v$process where pname like 'S%';
PID SPID PROGRAM PNAME B PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ --------------------------------------------- ----- - ------------ ------------- ---------------- -----------
16 9599 oracle@host.oracle.com (SMON) SMON 1 1361061 1774805 65536 1774805
24 9615 oracle@host.oracle.com (S000) S000 1025213 2216501 983040 10408501
28 9633 oracle@host.oracle.com (SMCO) SMCO 1 1128053 1250517 0 1250517
SQL>
drwxr-x---. 6 oracle oracle 131072 Mar 10 21:44 .
-rw-r-----. 1 oracle oracle 403 Mar 10 21:44 oratest_s000_9615.trm
-rw-r-----. 1 oracle oracle 87919 Mar 10 21:44 oratest_s000_9615.trc
[oracle@host trace]$ date
Sun Mar 10 21:45:59 CST 2019
[oracle@host trace]$
==ACTUAL SCREEN of shared server connection:
[oracle@host ~]$ sqlplus scott@oratest_shared
...
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @<labpath>/ugaexamination_lab.sql
SQL> alter session set events 'immediate trace name heapdump level 5';
Session altered.
SQL> exit
######### Analyze the difference
so we are good to examine the difference... what we will learn here is, the s000 was idle before, it did only one processing which is the sql as dedicated. Hence its pga is valid to be considered.
[oracle@host lab10mar19]$ ls -altr *PGAHEAP
-rw-r-----. 1 oracle oracle 87919 Mar 10 21:54 oratest_s000_9615.trc_SHARED_PGAHEAP
-rw-r-----. 1 oracle oracle 81928 Mar 10 21:54 oratest_ora_9540.trc_DEDICATED_PGAHEAP
[oracle@host lab10mar19]$
for an idle shared server.. the memory usage is...
SQL> select pid,spid,PROGRAM,pname,BACKGROUND,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from v$process where pname like 'S000%';
PID SPID PROGRAM PNAME B PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ ------------------------------------------------ ----- - ------------ ------------- ---------------- -----------
24 10690 oracle@host.oracle.com (S000) S000 930629 1053909 0 1053909
SQL>
post processing a session... [dont worry abt spid change, i did bounce the db to reverify this figures].
SQL> select pid,spid,PROGRAM,pname,BACKGROUND,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from v$process where pname like 'S%';
PID SPID PROGRAM PNAME B PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ --------------------------------------------- ----- - ------------ ------------- ---------------- -----------
24 9615 oracle@host.oracle.com (S000) S000 1025213 2216501 983040 10408501
for 10690 spid - the verification one:
PID SPID PROGRAM PNAME B PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------------------ ------------------------------------------------ ----- - ------------ ------------- ---------------- -----------
24 10690 oracle@host.oracle.com (S000) S000 1025213 2216501 983040 10408501
#### Let us use heap dump analyzer (credits to Tanel Poder, https://blog.tanelpoder.com/2009/01/02/oracle-memory-troubleshooting-part-1-heapdump-analyzer/)
I applied the heap dump analyzer to see a summary of heap distribution and observed the below....
dedicated:
shared:
The dedicated pga size does differ from heapdump trace value vs shared server heap dump is matching close (in its usage as well).
Learning: On dedicated connection heapdump we see uga heaps vs on the shared server connection we dont see the uga heap. This is in line with oracle documentation. The uga should have been allocated from the oracle sga, may be in next exercise we will see which memory area is hosting this uga (shared pool or large pool).
Thanks for paying a visit, your feedback is welcome.
No comments:
Post a Comment