How To Configure Client Failover For Dataguard Connections Using Database Services

For details one should refer to Note:

How To Configure Client Failover For Dataguard Connections Using Database Services (Doc ID 1429223.1)

Also see the MAA Whitepaper “Client Failover Best Practices for Data Guard 11g Release 2“ for further Details and Reference.

>> http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr2-client-failover-173305.pdf

Ex: we have a 2 Node Primary-RAC Setup where we have defined a service called say ‘APPLE’ to connect to the Primary Instance. We now want to use the same service on Standby and then use a common TNS Connection Alias to connect to the Database regardless of which of them is the Primary Database at any point in time ( i.e After a role transition we will have the old standby as the newprimary but we don’t change the tns connection string at client)

1) we define the same service on standby to startup when in primary role only

> srvctl add service -d <STBY> -s APPLE -l primary -r <STBY_Instance_1>, <STBY_Instance_2>,-P basic -e select -m basic -z 10 -w 2 -k 2

2) Define the Common TNS Alias in the format below =>>

APPLE =
 (DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)
    (DESCRIPTION =
      (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
          (ADDRESS_LIST=
            (LOAD_BALANCE=on)
              (ADDRESS = (PROTOCOL = TCP)(HOST = <Primary-CLUSTER-scan>)(PORT = 1521)))
                (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = APPLE))
     )
     (DESCRIPTION =
       (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
          (ADDRESS_LIST=
            (LOAD_BALANCE=on)
              (ADDRESS = (PROTOCOL = TCP)(HOST = <Stby cluster SCAN>)(PORT = 1521)))
                (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = APPLE))
      )
 )

When a new connection is made using the above Oracle Net alias the following logic is used:

a) Oracle Net contacts DNS and resolves <Primary cluster scan> to a total of 3 IP addresses.

b) Oracle Net randomly picks one of the 3 IP address and attempts to make a connection. If the connection attempt to the IP address does not respond in 3 seconds
(TRANSPORT_CONNECT_TIMEOUT) the next IP address is attempted. All 3 IP addresses will be tried a total of four times (initial attempt plus RETRY_COUNT in the above example).

c) If the connection to primary site is unsuccessful, it then contacts DNS and resolves <standby cluster scan> to 3 addresses.

d) The same sequence is performed for the standby scan as it was for the primary scan.

Note that the above is true only for Oracle Database 11g Release 2 clients.

Using the above TNS Connection Alias it will ensure upon a switchover/failover that connections are always routed to the correct Primary OPEN Instance

 

How to Ensure the Standby is started in the Correct Mode post a CRS restart / node reboot / Switchover

Issue

How to configure a 11.2 RAC environment in such a way that every time the database is bounce (either via Server reboot, CRS reboot, or manual effort), it starts up in the correct state based on the database role at the time.

Reason and Fix

In 10gR2  with a DG Broker / RAC configuration,  Clusterware would mount the database, and the DMON process would start and decide which database to open and which to mount depending on its role.

Now, in 11gR1+  the Broker just tells Clusterware to start the database and Clusterware determines whether to open the database or  not based on the start_options in the Clusterware resource.

However, during a role transition, the Broker only updates the role of the database in the CRS resource, but not the startup options.  So, these have to be manually configured post-transition as per the following MOS article.

CRS Configuration for RAC Primary or Standby Managed via Data Guard Broker [ID 1271003.1]

So in Case of an Active DG setup , you can modify the start option for standby to ‘open’

srvctl modify database -d <db_unique_name here> -n <db_name here> -s open -r PHYSICAL_STANDBY

Here crs will open the standby in read only mode and initiate managed recovery during a Role transition or Normal restart/reboot


 

 

Steps to Create guaranteed restore ponts on all standby databases and Production databases before starting release.

==========================
1.- Create guaranteed restore point on DR
==========================

select flashback_on, log_mode from v$database;

alter database recover managed standby database cancel;

CREATE RESTORE POINT before_release GUARANTEE FLASHBACK DATABASE;

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name    FROM gv$restore_point;

alter database recover managed standby database using current logfile disconnect;

=====================================
2. Configure guaranteed restore point on prod:
=====================================

select flashback_on, log_mode from v$database;

CREATE RESTORE POINT before_release GUARANTEE FLASHBACK DATABASE;

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name
FROM gv$restore_point;

FOR ROLLBACK

=======================================
– Flashback database on Primary.
=======================================

srvctl stop database -d prod

connect as sys

startup mount;

flashback database to restore point before_release;

 alter database open resetlogs;

shutdown immediate;

srvctl start database -d prod

srvctl start service -d prod

=======================================
– Flashback database on Standby
=======================================

alter database recover managed standby database cancel;

shutdown immediate; (srvctl stop database -d stby)

startup mount; (srvctl start instance -d stby -i stby1 -o mount)

flashback database to restore point before_release;

alter database recover managed standby database using current logfile disconnect;

– check transport of archivelogs

SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR, APPLIED_THREAD#, APPLIED_SEQ#, SYNCHRONIZED, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS;

– If flashback does not work, perform failover.

##############################
2 — Drop restore point on Prod, DR
##############################

———
PROD
———

DROP RESTORE POINT before_release;

———–
DR
———-

DROP RESTORE POINT before_release;

SGA Memory POOL space usage queries

 

some useful SQL’s you can use to Monitor SGA Memory Pool usage and HistoricalSGA usage to see if the SGA needs to be resized

USEFUL In diagnosing the ORA- Memory related errors ->>

ORA-04031: unable to allocate 7864344 bytes of shared memory (“large pool”,”unknown object”,”large pool”,”PX msg pool”)

Following query is useful for monitoring ASMM (Current) :

select component, current_size/1024/1024 “CURRENT_SIZE in MB”,
min_size/1024/1024 “MIN_SIZE in MB”,
user_specified_size/1024/1024 “USER_SPECIFIED_SIZE in MB”,
last_oper_type “TYPE” from v$sga_dynamic_components;

select * from V$SGA_DYNAMIC_COMPONENTS;
select * from V$MEMORY_DYNAMIC_COMPONENTS;
select * from V$SGA_RESIZE_OPS;
select * from V$MEMORY_RESIZE_OPS;

TO Check for SHARED POOL / LARGE POOL/ Buffer cache  -> RESIZE / SHRINKS (HISTORICAL DATA using Date ranges etc..)

select snap_id,instance_number,oper_type,oper_mode,STATUS , TO_char(start_time,’DD-MM-YYYY:HH24:MI:SS’), TO_char(end_time,’DD-MM-YYYY:HH24:MI:SS’),INITIAL_SIZE/(1024*1024*1024) INITIAL_SIZE , TARGET_SIZE/(1024*1024*1024) TARGET_SIZE , FINAL_SIZE/(1024*1024*1024) FINAL_SIZE
from
DBA_HIST_MEMORY_RESIZE_OPS
where
component like ‘%large%pool%’
order by start_time desc

select snap_id,instance_number,oper_type,oper_mode,STATUS , TO_char(start_time,’DD-MM-YYYY:HH24:MI:SS’), TO_char(end_time,’DD-MM-YYYY:HH24:MI:SS’),INITIAL_SIZE/(1024*1024*1024) INITIAL_SIZE , TARGET_SIZE/(1024*1024*1024) TARGET_SIZE , FINAL_SIZE/(1024*1024*1024) FINAL_SIZE
from
DBA_HIST_MEMORY_RESIZE_OPS
where
component like ‘%buffer%cache%’ and TO_char(start_time,’DD-MM-YYYY:HH24:MI:SS’) between ’10-10-2012:00:00:00′ and ’10-10-2012:01:20:00′
order by start_time desc

-- To check for LARGE POOL RESIZES where Current Size > 1GB
select BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,d.component,
d.current_size/1024/1024 current_size,d.MAX_SIZE/1024/1024 MAX_SIZE,d.LAST_OPER_TYPE from  DBA_HIST_MEM_DYNAMIC_COMP d join dba_hist_snapshot s on ( d.snap_id=s.snap_id )
where d.component=’large pool’
and d.current_size > 1000000
order by 2 desc ;

select BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,
d.current_size/1024/1024 mb,d.MAX_SIZE/1024/1024 mb,d.LAST_OPER_TYPE from  DBA_HIST_MEM_DYNAMIC_COMP d join dba_hist_snapshot s on ( d.snap_id=s.snap_id )
where d.component=’shared pool’ and d.instance_number=4
and d.current_size > 1000000000
order by 2 desc ;

DEFINITIONS

DBA_HIST_MEMORY_TARGET_ADVICE displays memory target advice history. This view contains snapshots of V$MEMORY_TARGET_ADVICE.

DBA_HIST_MEMORY_RESIZE_OPS displays memory resize operations history. This view contains snapshots of V$MEMORY_RESIZE_OPS.
DBA_HIST_MEM_DYNAMIC_COMP displays historical memory component sizes. This view contains snapshots of V$MEMORY_DYNAMIC_COMPONENTS

 

 

Useful Parallel Server Monitoring SQL’s for Real time and Historical data Analysis

 

I have shared below some SQL queries which I mainly for PARALLEL Server Usage within the Database to troubleshoot issues with Parallel query performance etc..

 

select to_char(sysdate,’DD-MM-YYYY HH24:MI:SS’) from dual;

col username for a12
col “QC SID” for A6
col “SID” for A6
col “QC/Slave” for A8
col “Req. DOP” for 9999
col “Actual DOP” for 9999
col “Slaveset” for A8
col “Slave INST” for A9
col “QC INST” for A6
set pages 300 lines 300
col wait_event format a30

prompt  =================================================
prompt  ==== General Parallel Server Usage ====
prompt  =================================================

SELECT DECODE(px.qcinst_id,NULL,username, ‘ – ‘
||lower(SUBSTR(pp.SERVER_NAME, LENGTH(pp.SERVER_NAME)-4,4) ) )”Username”,
DECODE(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” ,
TO_CHAR( px.server_set) “SlaveSet”,
TO_CHAR(s.sid) “SID”,
TO_CHAR(px.inst_id) “Slave INST”,
DECODE(sw.state,’WAITING’, ‘WAIT’, ‘NOT WAIT’ ) AS STATE,
CASE sw.state
WHEN ‘WAITING’
THEN SUBSTR(sw.event,1,30)
ELSE NULL
END AS wait_event ,
DECODE(px.qcinst_id, NULL ,TO_CHAR(s.sid) ,px.qcsid) “QC SID”,
TO_CHAR(px.qcinst_id) “QC INST”,
px.req_degree “Req. DOP”,
px.degree “Actual DOP”
FROM gv$px_session px,
gv$session s ,
gv$px_process pp,
gv$session_wait sw
WHERE px.sid   =s.sid (+)
AND px.serial# =s.serial#(+)
AND px.inst_id = s.inst_id(+)
AND px.sid     = pp.sid (+)
AND px.serial# =pp.serial#(+)
AND sw.sid     = s.sid
AND sw.inst_id = s.inst_id
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID;

prompt  =================================================
prompt  ==== Info from pq_sysstat ====
prompt  =================================================

select substr(statistic, 1, 20)  “STATISTIC”,
substr(value, 1, 20)        “VALUE”
from v$pq_sysstat;

col username for a12
col “QC SID” for A6
col “SID” for A6
col “QC/Slave” for A8
col “Req. DOP” for 9999
col “Actual DOP” for 9999
col “Slaveset” for A8
col “Slave INST” for A9
col “QC INST” for A6
col sqlid FOR a16
set pages 300 lines 300
col wait_event format a30

prompt  =================================================
prompt  ==== What IS the CURRENT PARALLEL slave usage====
prompt  =================================================

select inst_id, qcsid,count(*) from gv$px_session group by inst_id,qcsid;

prompt  ===================================================================
prompt  ==== how many parallel execution servers are currently in use======
prompt  ===================================================================

SELECT
inst_id,
statistic,
value
from
gv$pq_sysstat
where
statistic like ‘Servers Busy%’;

prompt  =================================================
prompt  =============== PARALLEL SESSION     ============
prompt  =================================================

set linesize 500 pagesize 80
col “QC/Slave” form a7
col username form a8
col “SlaveSet” form a10
col “QC INST” form a4 heading “QC|INST”
col “QC SID” form a4 heading “QC|SID”
col “Slave INST” form a5 heading “Slave|INST”
col “SID” form a4
col “Actual DOP” form 9999 heading “Actual|DOP”
col “Req. DOP” form 9999 heading “Req.|DOP”
col “SPID” form a8

SELECT DECODE(px.qcinst_id,NULL,s.username, ‘ – ‘||lower(SUBSTR(pp.SERVER_NAME, LENGTH(pp.SERVER_NAME)-4,4) ) )”Username”,
DECODE(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” ,
TO_CHAR( px.server_set) “SlaveSet”,
TO_CHAR(s.sid) “SID”,
to_char(p.spid) “SPID”,
TO_CHAR(px.inst_id) “Slave INST”,
DECODE(sw.state,’WAITING’, ‘WAIT’, ‘NOT WAIT’ ) AS STATE,
CASE sw.state
WHEN ‘WAITING’
THEN SUBSTR(sw.event,1,30)
ELSE NULL
END AS wait_event ,
TO_CHAR(px.qcinst_id) “QC INST”,
DECODE(px.qcinst_id, NULL ,TO_CHAR(s.sid) ,px.qcsid) “QC SID”,
px.req_degree “Req. DOP”,
px.degree “Actual DOP”,
sql_id,
sql_child_number
–ql_plan_hash_value,
FROM gv$px_session px,
gv$session s ,
gv$px_process pp,
gv$session_wait sw,
gv$process p
WHERE px.sid   =s.sid (+)
AND px.serial# =s.serial#(+)
AND px.inst_id = s.inst_id(+)
AND px.sid     = pp.sid (+)
AND px.serial# =pp.serial#(+)
AND sw.sid     = s.sid
AND sw.inst_id = s.inst_id
and s.inst_id = p.inst_id
and s.paddr = p.addr
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID;

–set pages 300 lines 300
col wait_event format a30
col operation_name format a30
col target format a30

select
sw.SID as RCVSID,
decode(pp.server_name,
NULL, ‘A QC’,
pp.server_name) as RCVR,
sw.inst_id as RCVRINST,
case  sw.state WHEN ‘WAITING’ THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
decode(bitand(p1, 65535),
65535, ‘QC’,
‘P’||to_char(bitand(p1, 65535),’fm000′)) as SNDR,
bitand(p1, 16711680) – 65535 as SNDRINST,
decode(bitand(p1, 65535),
65535, ps.qcsid,
(select
sid
from
gv$px_process
where
server_name = ‘P’||to_char(bitand(sw.p1, 65535),’fm000′) and
inst_id = bitand(sw.p1, 16711680) – 65535)
) as SNDRSID,
decode(sw.state,’WAITING’, ‘WAIT’, ‘NOT WAIT’ ) as STATE
from
gv$session_wait sw,
gv$px_process pp,
gv$px_session ps
where
sw.sid = pp.sid (+) and
sw.inst_id = pp.inst_id (+) and
sw.sid = ps.sid (+) and
sw.inst_id = ps.inst_id (+) and
p1text  = ‘sleeptime/senderid’ and
bitand(p1, 268435456) = 268435456
order by
decode(ps.QCINST_ID,  NULL, ps.INST_ID,  ps.QCINST_ID),
ps.QCSID,
decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP),
ps.SERVER_SET,
ps.INST_ID;

prompt  =================================================
prompt  =============== real time V$PX_SESSION view outputs     ============
prompt  =================================================

WITH px_session AS (SELECT qcsid, qcserial#, MAX (degree) degree,
MAX (req_degree) req_degree,
COUNT ( * ) no_of_processes
FROM v$px_session p
GROUP BY qcsid, qcserial#)
SELECT s.sid, s.username, degree, req_degree, no_of_processes,
sql_text
FROM v$session s JOIN px_session p
ON (s.sid = p.qcsid AND s.serial# = p.qcserial#)
JOIN v$sql sql
ON (sql.sql_id = s.sql_id
AND sql.child_number = s.sql_child_number);

prompt  =================================================
prompt  ======== PARALLEL query operations      =========
prompt  =================================================

Select
decode(px.qcinst_id,NULL,username,
‘ – ‘||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )”Username”,
decode(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” ,
to_char( px.server_set) “SlaveSet”,
to_char(px.inst_id) “Slave INST”,
substr(opname,1,30)  operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) “QC SID”,
to_char(px.qcinst_id) “QC INST”
from gv$px_session px,
gv$px_process pp,
gv$session_longops s
where px.sid=s.sid
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by
decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
px.QCSID,
decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID;

prompt  =================================================
prompt  ======== how was the statement parellelized======
prompt  =================================================

break on tq_id on server_type
SELECT inst_id,dfo_number, tq_id, server_type, process, num_rows, bytes
FROM gv$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC , process;

prompt  =========================================================
prompt  ==== PARALLEL Activity Overview OF the Environments ?====
prompt  =========================================================

COLUMN name format a100
break on inst_id skip 1

select inst_id,name,value from gv$sysstat
where upper(name) like ‘%PARALLEL OPERATIONS%’
or upper(name) like ‘%PARALLELIZED%’ or upper(name) like ‘%PX%’
order by inst_id, value desc;

prompt  =================================================
prompt  ============= PX Buffer Advise ===========
prompt  =================================================

SELECT       inst_id,
statistic,
value
FROM gv$px_buffer_advice
ORDER BY
inst_id,       statistic;

prompt  =================================================
prompt  ============= Check Parallel query stats for active SQL’s from gv$sql_monitor ===========
prompt  =================================================

select distinct sql_id, px_maxdop, px_servers_requested requested,px_servers_allocated allocated from gv$sql_monitor
where sql_exec_start > sysdate-10/1440;

UNDO Usage Monitoring

UNDO Usage Monitoring in a DW environment is very important the thing to monitor is whether UNDO Extents are being released (expired) for use by other transactions..

There are a set of scripts which once can use provided under MOS Note: Common Diagnostic Scripts for AUM problems (Doc ID 746173.1) which provide very useful diags on UNDO Usage

I also use my own simple script to get REAL Time Monitoring on UNDO Usage within the Database so I can review what SQL’s were running and how much UNDO was in use.

 SCRIPT to run every 10 mins collecting UNDO usage :
while sleep 600
do
sqlplus -S ‘/ as sysdba’ << EOF >> /../../undo_monitoring.log
  col tablespace_name for a10
 col mb for 999,999,999
  set lin 180
   alter session set nls_date_format= ‘dd-mon-yyyy hh24:mi:ss’;

   select sysdate,TABLESPACE_NAME,status,round(sum(BYTES)/1024/1024) mb  from dba_undo_extents  group by TABLESPACE_NAME,status;
   SELECT sysdate,a.sid, a.sql_id,a.username, b.xidusn, b.used_urec, b.used_ublk   FROM gv\$session a, gv\$transaction b   WHERE a.saddr = b.ses_addr and a.inst_id=b.inst_id;

EOF

Monitoring the Performance of the Automatic PGA Memory Management

I have been using the below script for Active PGA usage in a Database as I have had lot of issues at Customers with Massive Parallelism blowing out PGA (Direct path reads)
and so the script gets me the real time PGA Usage

##########
script
##########

———————————————–

select to_char(sysdate,’DD-MM-YYYY HH24:MI:SS’) from dual;

prompt  =================================================
prompt  ==== v$pgastat info ====
prompt  =================================================

column name format a40
column value format 99999999999999999999
select name, value from v$pgastat;
set lines 200

prompt  =================================================
prompt  ==== V$PROCESS real time data  ====
prompt  =================================================

SELECT PROGRAM, PGA_USED_MEM/(1024*1024) “PGA_USED_MEM”, PGA_ALLOC_MEM/(1024*1024) “PGA_ALLOC_MEM”, PGA_FREEABLE_MEM/(1024*1024) “PGA_FREEABLE_MEM”,
PGA_MAX_MEM/(1024*1024)  “PGA_MAX_MEM”
FROM
V$PROCESS
where PGA_MAX_MEM/(1024*1024) > 50
order by PGA_MAX_MEM desc ;

prompt  =================================================
prompt  ==== GV$PROCESS_MEMORY real time data  ====
prompt  =================================================

select INST_ID,PID,SERIAL#,CATEGORY, ALLOCATED, USED/(1024*1024) “Used_MB”, MAX_ALLOCATED/(1024*1024) “MAX_ALLOCATED_MB”
from
GV$PROCESS_MEMORY
where
Used is not null and MAX_ALLOCATED/(1024*1024) > 50
order by MAX_ALLOCATED_MB desc;

prompt  =================================================
prompt  ==== workload execution profile  ====
prompt  =================================================

col c1 heading ‘Workarea|Profile’ format a35
col c2 heading ‘Count’ format 999999999
col c3 heading ‘Percentage’ format 99999
select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3
from
(
select name,value count,(sum(value) over ()) total
from
v$sysstat
where
name like ‘workarea exec%’
);

prompt  =================================================
prompt  ==== V$SQL_WORKAREA real time info  ====
prompt  =================================================

SELECT *
FROM (SELECT workarea_address, operation_type, policy, estimated_optimal_size/(1024*1024*1024) estimated_optimal_size_gb
FROM V$SQL_WORKAREA
ORDER BY estimated_optimal_size DESC)
WHERE ROWNUM <= 10;

prompt  =================================================
prompt  ==== Gv$sql_workarea_histogram info on passes  ====
prompt  =================================================

SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc, onepass_count, round(onepass_count*100/total, 2) onepass_perc, multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
(
SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM
Gv$sql_workarea_histogram
WHERE low_optimal_size >= 64*1024);

—————————————————————————————-

#################
DETAILS on the Views used above to Monitor the Performance of the Automatic PGA Memory Management
#################

SQL> col VALUE for 99999999999999999999999999999999999999
SQL> set lines 200
SQL> SELECT * FROM V$PGASTAT;

NAME                                                                                               VALUE UNIT
—————————————————————- ————————————— ————
aggregate PGA target parameter                                                                6442450944 bytes
aggregate PGA auto target                                                                     2236879872 bytes
global memory bound                                                                            644239360 bytes
total PGA inuse                                                                               4643407872 bytes
total PGA allocated                                                                           5441014784 bytes
maximum PGA allocated                                                                        13053722624 bytes
total freeable PGA memory                                                                      277413888 bytes
process count                                                                                        270
max processes count                                                                                  626
PGA memory freed back to OS                                                               11167367626752 bytes
total PGA used for auto workareas                                                              681655296 bytes

NAME                                                                                               VALUE UNIT
—————————————————————- ————————————— ————
maximum PGA used for auto workareas                                                           8055233536 bytes
total PGA used for manual workareas                                                                    0 bytes
maximum PGA used for manual workareas                                                         1223434240 bytes
over allocation count                                                                            1534477
bytes processed                                                                           15883374120960 bytes
extra bytes read/written                                                                  18453620585472 bytes
cache hit percentage                                                                                  46 percent    >>>> PGA Small ???
recompute count (total)                                                                          3501785

19 rows selected.

The main statistics displayed in V$PGASTAT are as follows:

¦ aggregate PGA target parameter: This is the current value of the initialization
parameter PGA_AGGREGATE_TARGET. The default value is 20% of the SGA size. If you
set this parameter to 0, automatic management of the PGA memory is disabled.
¦ aggregate PGA auto target: This gives the amount of PGA memory Oracle Database can use for work areas running in automatic mode. This amount is
dynamically derived from the value of the parameter PGA_AGGREGATE_TARGET and
the current work area workload. Hence, it is continuously adjusted by Oracle. If this value is small compared to the value of PGA_AGGREGATE_TARGET, then a lot of
PGA memory is used by other components of the system (for example, PL/SQL or
Java memory) and little is left for sort work areas. You must ensure that enough
PGA memory is left for work areas running in automatic mode.
¦ global memory bound: This gives the maximum size of a work area executed in
AUTO mode. This value is continuously adjusted by Oracle Database to reflect the
current state of the work area workload. The global memory bound generally
decreases when the number of active work areas is increasing in the system. As a rule of thumb, the value of the global bound should not decrease to less than one
megabyte. If it does, then the value of PGA_AGGREGATE_TARGET should probably be
increased.
¦ total PGA allocated: This gives the current amount of PGA memory allocated
by the instance. Oracle Database tries to keep this number less than the value of
PGA_AGGREGATE_TARGET. However, it is possible for the PGA allocated to exceed
that value by a small percentage and for a short period, when the work area
workload is increasing very rapidly or when the initialization parameter PGA_
AGGREGATE_TARGET is set to a too small value.
¦ total freeable PGA memory: This indicates how much allocated PGA memory
which can be freed.
¦ total PGA used for auto workareas: This indicates how much PGA memory is
currently consumed by work areas running under automatic memory
management mode. This number can be used to determine how much memory is
consumed by other consumers of the PGA memory (for example, PL/SQL or
Java): PGA other = total PGA allocated – total PGA used for auto workareas
¦ over allocation count: This statistic is cumulative from instance startup. Over-allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is
too small to accommodate the PGA other component in the previous equation plus
the minimum memory required to execute the work area workload. When this
happens, Oracle Database cannot honor the initialization parameter PGA_AGGREGATE_TARGET, and extra PGA memory must be allocated. If over-allocation
occurs, you should increase the value of PGA_AGGREGATE_TARGET using the
information provided by the advice view V$PGA_TARGET_ADVICE.

V$PROCESS This view has one row for each Oracle process connected to the instance.
The columns PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM and PGA_MAX_MEM can
be used to monitor the PGA memory usage of these processes. For example:

SQL> SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM FROM V$PROCESS;

V$PROCESS_MEMORY This view displays dynamic PGA memory usage by named component categories for each Oracle process. This view will contain up to six rows
for each Oracle process, one row for:
¦ Each named component category: Java, PL/SQL, OLAP, and SQL.
¦ Freeable: memory that has been allocated to the process by the operating system,
but not to a specific category.
¦ Other: memory that has been allocated to a category, but not to one of the named
categories.
You can use the columns CATEGORY, ALLOCATED, USED, and MAX_ALLOCATED to
dynamically monitor the PGA memory usage of Oracle processes for each of the six
categories.

select INST_ID,CATEGORY, ALLOCATED, USED, MAX_ALLOCATED from GV$PROCESS_MEMORY

select INST_ID,PID,SERIAL#,CATEGORY, ALLOCATED, USED/(1024*1024) “Used_MB”, MAX_ALLOCATED/(1024*1024) “MAX_ALLOCATED_MB” from GV$PROCESS_MEMORY where Used is not null order by USED desc

2    126    180    JAVA    3856688    3.6542510986328125    4.40497589111328125
2    166    233    JAVA    3554592    3.3853912353515625    4.09759521484375
1    133    4    JAVA    3551608    3.385162353515625    4.62749481201171875
2    117    174    JAVA    3555920    3.384368896484375    4.631622314453125
1    148    238    JAVA    3304680    3.14263916015625    4.417755126953125
1    36    210    JAVA    3020944    2.8735809326171875    3.77861785888671875
3    113    152    JAVA    3013184    2.87197113037109375    3.77121734619140625
2    126    180    PL/SQL    4304744    2.55957794189453125    4.6701507568359375
1    36    210    PL/SQL    3409464    2.04129791259765625    3.32293701171875
1    140    28    JAVA    2775056    1.697906494140625    3.4982452392578125
3    113    152    PL/SQL    3379832    1.6282196044921875    3.33892822265625

V$SQL_WORKAREA_HISTOGRAM This view shows the number of work areas executed with optimal memory size, one-pass memory size, and multi-pass memory size since
instance startup. Statistics in this view are subdivided into buckets that are defined by
the optimal memory requirement of the work area. Each bucket is identified by a
range of optimal memory requirements specified by the values of the columns LOW_
OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE

Consider a sort operation that requires 3 MB of memory to run optimally (cached).
Statistics about the work area used by this sort are placed in the bucket defined by
LOW_OPTIMAL_SIZE = 2097152 (2 MB) and HIGH_OPTIMAL_SIZE = 4194303 (4 MB
minus 1 byte), because 3 MB falls within that range of optimal sizes. Statistics are
segmented by work area size, because the performance impact of running a work area
in optimal, one-pass or multi-pass mode depends mainly on the size of that work area.

SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM GV$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0

“LOW_KB”                      “HIGH_KB”                     “OPTIMAL_EXECUTIONS”          “ONEPASS_EXECUTIONS”          “MULTIPASSES_EXECUTIONS”
“2″                           “4″                           “7654062″                     “0″                           “0″
“64″                          “128″                         “411311″                      “0″                           “0″
“128″                         “256″                         “1545021″                     “2″                           “0″
“256″                         “512″                         “746672″                      “20″                          “0″
“512″                         “1024″                        “2396131″                     “2588″                        “0″
“1024″                        “2048″                        “380986″                      “11313″                       “104″
“2048″                        “4096″                        “80924″                       “14659″                       “2113″
“4096″                        “8192″                        “47235″                       “18510″                       “9951″
“8192″                        “16384″                       “26254″                       “19324″                       “10765″
“16384″                       “32768″                       “11238″                       “13712″                       “16807″
“32768″                       “65536″                       “6724″                        “5606″                        “7701″
“65536″                       “131072″                      “3590″                        “9058″                        “3688″
“131072″                      “262144″                      “2006″                        “9863″                        “1929″
“262144″                      “524288″                      “914″                         “4161″                        “1443″
“524288″                      “1048576″                     “81″                          “1481″                        “30″
“1048576″                     “2097152″                     “3″                           “284″                         “191″
“2097152″                     “4194304″                     “0″                           “78″                          “48″
“4194304″                     “8388608″                     “94″                          “54″                          “0″
“8388608″                     “16777216″                    “0″                           “36″                          “1″  >> seems to be bad as it needs more 16GB for work area where PGA was 6 GB
“16777216″                    “33554432″                    “0″                           “4″                           “0″
“33554432″                    “67108864″                    “0″                           “0″                           “1″  >> seems to be bad as it needs more 33GB for work area where PGA was 6 GB
“2″                           “4″                           “6979357″                     “0″                           “0″
“64″                          “128″                         “112252″                      “0″                           “0″
“128″                         “256″                         “33750″                       “0″                           “0″
“256″                         “512″                         “37511″                       “0″                           “0″
“512″                         “1024″                        “243631″                      “2″                           “0″
“1024″                        “2048″                        “126156″                      “19″                          “0″
“2048″                        “4096″                        “7061″                        “63″                          “0″
“4096″                        “8192″                        “7780″                        “316″                         “4″
“8192″                        “16384″                       “1466″                        “148″                         “2″
“16384″                       “32768″                       “1010″                        “14″                          “0″
“32768″                       “65536″                       “99″                          “39″                          “0″
“65536″                       “131072″                      “69″                          “37″                          “0″
“131072″                      “262144″                      “47″                          “53″                          “0″
“262144″                      “524288″                      “911″                         “532″                         “0″
“524288″                      “1048576″                     “0″                           “37″                          “0″
“1048576″                     “2097152″                     “0″                           “18″                          “0″
“2097152″                     “4194304″                     “0″                           “2″                           “0″
“4194304″                     “8388608″                     “0″                           “129″                         “0″
“8388608″                     “16777216″                    “0″                           “86″                          “0″
“2″                           “4″                           “7773737″                     “0″                           “0″
“64″                          “128″                         “405278″                      “0″                           “0″
“128″                         “256″                         “2073511″                     “9″                           “0″
“256″                         “512″                         “1075082″                     “35″                          “0″
“512″                         “1024″                        “2772341″                     “5114″                        “0″
“1024″                        “2048″                        “398772″                      “9882″                        “4″
“2048″                        “4096″                        “79350″                       “17464″                       “3944″
“4096″                        “8192″                        “45323″                       “21829″                       “8454″
“8192″                        “16384″                       “26425″                       “30496″                       “12207″
“16384″                       “32768″                       “10896″                       “26602″                       “21948″
“32768″                       “65536″                       “2960″                        “6686″                        “8028″
“65536″                       “131072″                      “2267″                        “11674″                       “3139″
“131072″                      “262144″                      “1913″                        “10035″                       “1012″
“262144″                      “524288″                      “522″                         “6495″                        “2379″
“524288″                      “1048576″                     “2″                           “1567″                        “68″
“1048576″                     “2097152″                     “0″                           “285″                         “58″
“2097152″                     “4194304″                     “0″                           “81″                          “0″
“4194304″                     “8388608″                     “0″                           “7″                           “0″
“8388608″                     “16777216″                    “0″                           “40″                          “0″
“33554432″                    “67108864″                    “0″                           “1″                           “0″
“2″                           “4″                           “5383694″                     “0″                           “0″
“64″                          “128″                         “114682″                      “0″                           “0″
“128″                         “256″                         “40738″                       “1″                           “0″
“256″                         “512″                         “51120″                       “5″                           “0″
“512″                         “1024″                        “314845″                      “5″                           “0″
“1024″                        “2048″                        “109685″                      “63″                          “0″
“2048″                        “4096″                        “7385″                        “59″                          “0″
“4096″                        “8192″                        “8773″                        “169″                         “2″
“8192″                        “16384″                       “2146″                        “165″                         “0″
“16384″                       “32768″                       “2594″                        “34″                          “0″
“32768″                       “65536″                       “468″                         “62″                          “0″
“65536″                       “131072″                      “77″                          “36″                          “0″
“131072″                      “262144″                      “46″                          “37″                          “0″
“262144″                      “524288″                      “1043″                        “352″                         “0″
“524288″                      “1048576″                     “0″                           “12″                          “6″
“1048576″                     “2097152″                     “0″                           “5″                           “25″
“2097152″                     “4194304″                     “0″                           “2″                           “0″

>>> Querying V$SQL_WORKAREA_HISTOGRAM: Percent Optimal
You can also use V$SQL_WORKAREA_HISTOGRAM to find the percentage of times work
areas were executed in optimal, one-pass, or multi-pass mode since startup. This query
only considers work areas of a certain size, with an optimal memory requirement of at least 64 KB.

SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc, onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM Gv$sql_workarea_histogram
WHERE low_optimal_size >= 64*1024);

“OPTIMAL_COUNT”               “OPTIMAL_PERC”                “ONEPASS_COUNT”               “ONEPASS_PERC”                “MULTIPASS_COUNT”             “MULTIPASS_PERC”

“13779324″                    “97.33″                       “261887″                      “1.85″                        “116077″                      “0.82″

This result shows that 97.33% of these work areas have been able to run using an
optimal amount of memory. The rest (1.85%) ran one-pass AND 0.82%   ran multi-pass. Such behavior is preferable, for the following reasons:

¦ Multi-pass mode can severely degrade performance. A high number of multi-pass work areas has an exponentially adverse effect on the response time of its
associated SQL operator.

¦ Running one-pass does not require a large amount of memory; only 22 MB is required to sort 1 GB of data in one-pass mode.

>>> V$SQL_WORKAREA_ACTIVE You can use this view to display the work areas that are
active (or executing) in the instance. Small active sorts (under 64 KB) are excluded
from the view. Use this view to precisely monitor the size of all active work areas and
to determine if these active work areas spill to a temporary segment

SELECT INST_ID,to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) “MAX MEM”,
NUMBER_PASSES PASS,
trunc(TEMPSEG_SIZE/1024) TSIZE
FROM GV$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

Column ESIZE indicates the maximum amount of memory that the PGA memory manager expects the operation to use

V$SQL_WORKAREA Oracle Database maintains cumulative work area statistics for each
loaded cursor whose execution plan uses one or more work areas. Every time a work area is deallocated, the V$SQL_WORKAREA table is updated with execution statistics for
that work area.

V$SQL_WORKAREA can be joined with V$SQL to relate a work area to a cursor. It can even be joined to V$SQL_PLAN to precisely determine which operator in the plan uses a work
area.

———-
Querying V$SQL_WORKAREA
———-

The following query finds the top 10 work areas requiring most cache memory:

SELECT *
FROM (SELECT workarea_address, operation_type, policy, estimated_optimal_size/(1024*1024*1024) estimated_optimal_size_gb
FROM V$SQL_WORKAREA
ORDER BY estimated_optimal_size DESC)
WHERE ROWNUM <= 10;

“WORKAREA_ADDRESS”            “OPERATION_TYPE”              “POLICY”                      “ESTIMATED_OPTIMAL_SIZE_GB”
“000000074B5675F8″            “SORT (v2)”                   “AUTO”                        “408.9348907470703125″
“0000000724978D70″            “SORT (v2)”                   “AUTO”                        “291.12615966796875″
“000000031E5445E8″            “SORT (v2)”                   “AUTO”                        “6.77559947967529296875″
“000000074113D3B8″            “GROUP BY (HASH)”             “AUTO”                        “1.99999904632568359375″
“00000005E2ED0998″            “GROUP BY (HASH)”             “AUTO”                        “1.99999904632568359375″
“0000000347ECF5C8″            “HASH-JOIN”                   “AUTO”                        “1.99999904632568359375″
“0000000347ECF630″            “GROUP BY (HASH)”             “AUTO”                        “1.99999904632568359375″
“0000000347ECF698″            “GROUP BY (HASH)”             “AUTO”                        “1.99999904632568359375″
“00000007488A3560″            “GROUP BY (HASH)”             “AUTO”                        “1.99999904632568359375″
“000000031E544310″            “HASH-JOIN”                   “AUTO”                        “1.99999904632568359375″

>> WHERE > ESTIMATED_OPTIMAL_SIZE > Estimated size (in bytes) required by this work area to execute the operation completely in memory (optimal execution). Derived from either optimizer statistics or previous executions.

The following query finds the cursors with one or more work areas that have been executed in one or even multiple passes:

col sql_text format A80 wrap

SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
sum(MULTIPASSES_EXECUTIONS) mpass_cnt
FROM GV$SQL s, GV$SQL_WORKAREA wa
WHERE s.address = wa.address
GROUP BY sql_text
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0
ORDER BY mpass_cnt DESC

Using the hash value and address of a particular cursor, the following query displays the cursor execution plan, including information about the associated work areas.

col “O/1/M” format a10
col name format a20

SELECT operation, options, object_name name, trunc(bytes/1024/1024) “input(MB)”,
trunc(last_memory_used/1024) last_mem,
trunc(estimated_optimal_size/1024) optimal_mem,
trunc(estimated_onepass_size/1024) onepass_mem,
decode(optimal_executions, null, null,
optimal_executions||’/'||onepass_executions||’/'||
multipasses_executions) “O/1/M”
FROM V$SQL_PLAN p, V$SQL_WORKAREA w WHERE p.address=w.address(+)
AND p.hash_value=w.hash_value(+)
AND p.id=w.operation_id(+)
AND p.address=’88BB460C’
AND p.hash_value=3738161960;

OPERATION OPTIONS NAME input(MB) LAST_MEM OPTIMAL_ME ONEPASS_ME O/1/M
———— ——– ——– ——— ——– ———- ———- ——
SELECT STATE HASH GROUP BY 4582 8 16 16 16/0/0
HASH JOIN SEMI 4582 5976 5194 2187 16/0/0
TABLE ACCESS FULL ORDERS 51 TABLE ACCESS FUL LINEITEM 1000

You can get the address and hash value from the V$SQL view by specifying a pattern in the query. For example:

SELECT address, hash_value FROM GV$SQL
WHERE sql_text LIKE ‘%insert /*+ parallel(ROS_INSTRUMENT,24)  parallel(ROS_INSTRUMENT,24)  APPEND NOLOGGING */ FIRST WHEN SK_ROS_INSTRUMENT_ID <= 4435937885%’;

———————
V$PGA_TARGET_ADVICE This view predicts how the statistics cache hit percentage
and over allocation count in V$PGASTAT will be impacted if you change the value of the initialization parameter PGA_AGGREGATE_TARGET

>>> DBA_HIST_PGA_TARGET_ADVICE
———————

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;

SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;

2    3    4
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
———- ————– ——————–
768             46                37610
1536             46                37610
3072             47                24077
4608             63                 7592
6144             77                 1704        >>> where we are >>>
7373             84                  105
8602             86                   52
9830             88                    6
11059             89                    2
12288             90                    0
18432             92                    0

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
———- ————– ——————–
24576             92                    0
36864             92                    0
49152             92                    0

14 rows selected.

SQL> alter system set pga_aggregate_target=8G scope=both;

System altered.

SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;
2    3    4
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
———- ————– ——————–
1024            100                    0
2048            100                    0
4096            100                    0
6144            100                    0
8192            100                    0
9830            100                    0
11469            100                    0
13107            100                    0
14746            100                    0
16384            100                    0
24576            100                    0

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
———- ————– ——————–
32768            100                    0
49152            100                    0
65536            100                    0

14 rows selected.

——————-
V$PGA_TARGET_ADVICE_HISTOGRAM
——————-

This view predicts how the statistics displayed by
the performance view V$SQL_WORKAREA_HISTOGRAM will be impacted if you change the
value of the initialization parameter PGA_AGGREGATE_TARGET. You can use the dynamic
view V$PGA_TARGET_ADVICE_HISTOGRAM to view detailed information on the predicted number of optimal, one-pass and multi-pass work area executions for the set of PGA_
AGGREGATE_TARGET values you use for the prediction. The V$PGA_TARGET_ADVICE_HISTOGRAM view is identical to the V$SQL_WORKAREA_
HISTOGRAM view, with two additional columns to represent the PGA_AGGREGATE_TARGET
values used for the prediction. Therefore, any query executed against the V$SQL_
WORKAREA_HISTOGRAM view can be used on this view, with an additional predicate to
select the desired value of PGA_AGGREGATE_TARGET

SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
estd_optimal_executions estd_opt_cnt,
estd_onepass_executions estd_onepass_cnt,
estd_multipasses_executions estd_mpass_cnt
FROM v$pga_target_advice_histogram
WHERE pga_target_factor = 2
AND estd_total_executions != 0
ORDER BY 1;

LOW_KB    HIGH_KB ESTD_OPT_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT
———- ———- ———— —————- ————–
2          4          946                0              0
64        128          419                0              0
128        256          364                0              0
256        512         8641                0              0
512       1024           23                0              0
1024       2048          235                0              0
4096       8192            1                0              0

7 rows selected.

Ex:

The output of this query might look like the following.

LOW_KB HIGH_KB ESTD_OPTIMAL_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT
—— ——- —————- —————- ————–
8     16     156107 0 0
16     32     148 0 0
32     64     89 0 0
64     128     13 0 0
128     256     58 0 0
256     512     10 0 0
512     1024     653 0 0
1024     2048     530 0 0
2048     4096     509 0 0
4096     8192     227 0 0
8192     16384     176 0 0
16384     32768     133 16 0
32768     65536     66 103 0
65536     131072     15 47 0
131072     262144     0 48 0            >>> all optimal
262144     524288     0 23 0

The output shows that increasing PGA_AGGREGATE_TARGET by a factor of 2 will allow all work areas under 16 MB to execute in optimal mode.

V$SYSSTAT and V$SESSTAT

Statistics in the V$SYSSTAT and V$SESSTAT views show the total number of work areas
executed with optimal memory size, one-pass memory size, and multi-pass memory size. These statistics are cumulative since the instance or the session was started.
The following query gives the total number and the percentage of times work areas
were executed in these three modes since the instance was started:

SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
FROM (SELECT name, value cnt, (sum(value) over ()) total
FROM V$SYSSTAT WHERE name like ‘workarea exec%’);

The output of this query might look like the following:

SQL> SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
FROM (SELECT name, value cnt, (sum(value) over ()) total
FROM V$SYSSTAT WHERE name like ‘workarea exec%’);  2    3

PROFILE                                                                 CNT
—————————————————————- ———-
PERCENTAGE
———-
workarea executions – optimal                                      13345463
99

workarea executions – onepass                                        112545
1

workarea executions – multipass                                       55374
0

——————
DBA_HIST_SQL_WORKAREA_HSTGRM
——————

SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc, onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM DBA_HIST_SQL_WORKAREA_HSTGRM
WHERE low_optimal_size >= 64*1024);

—————
PGA usage statistics:
—————

select * from v$pgastat;

—————
Determine a good setting for pga_aggregate_target:
—————

select * from v$pga_target_advice order by pga_target_for_estimate;

—————
Show the maximum PGA usage per process:
—————

select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process