Rehearse 12c clusterware with "predict"

Oracle 12c clusterware will actually let you rehearse with SRVCTL & CRSCTL commands using "predict" & "eval" options. Exploring the predict option in this post.

[oracle@ol6-121-rac1 bin]$ ./srvctl predict -h

The SRVCTL predict command evaluates the consequences of resource failure.

Usage: srvctl predict database -db <database_name> [-verbose]
Usage: srvctl predict service -db <database_name> -service <service_name> [-verbose]
Usage: srvctl predict asm [-node <node_name>] [-verbose]
Usage: srvctl predict diskgroup -diskgroup <diskgroup_name> [-verbose]
Usage: srvctl predict filesystem -device <volume_device> [-verbose]
Usage: srvctl predict vip -vip <vip_name> [-verbose]
Usage: srvctl predict network [-netnum <network_number>] [-verbose]
Usage: srvctl predict listener -listener <listener_name> [-verbose]
Usage: srvctl predict scan -scannumber <scan_ordinal_number> [-netnum <network_number>] [-verbose]
Usage: srvctl predict scan_listener -scannumber <scan_ordinal_number> [-netnum <network_number>] [-verbose]
Usage: srvctl predict oc4j [-verbose]

Lets take a snap of the current resources and its status before executing the predict commands so that we ll understand why it predicts so..

[oracle@ol6-121-rac1 bin]$ ./crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details    
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.asm
               ONLINE  ONLINE       ol6-121-rac1             Started,STABLE
               ONLINE  ONLINE       ol6-121-rac2             Started,STABLE
ora.net1.network
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.ons
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       ol6-121-rac1             169.254.149.190 192.
                                                             168.1.101,STABLE
ora.cdbrac.db
      1        ONLINE  ONLINE       ol6-121-rac1             Open,STABLE
      2        ONLINE  ONLINE       ol6-121-rac2             Open,STABLE
ora.cvu
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       ol6-121-rac1             Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.ol6-121-rac1.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.ol6-121-rac2.vip
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
--------------------------------------------------------------------------------


Now lets see the usage in detail.The output will summarize the behavior in case of the resource failure for which the "predict" commands are run.

• For a local resource it will list out what are the associated resources that will be stopped along with it.
• For a cluster resource it will list out how the fail-over will happen to a remote node(very helpful for 3+ node cluster). This will also be a very easy way to understand the dependency among st the resources.

[oracle@ol6-121-rac1 bin]$ srvctl predict database -db cdbrac -verbose
Database cdbrac will be stopped on nodes ol6-121-rac1,ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict asm -node ol6-121-rac1 -verbose
Resource ora.asm will be stopped
Resource ora.mgmtdb will be stopped
Resource ora.DATA.dg will be stopped
Resource ora.MGMTLSNR will be started on node ol6-121-rac2
Database cdbrac will be stopped on node ol6-121-rac1
Resource ora.mgmtdb will be stopped
Resource ora.mgmtdb will be started on node ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict diskgroup -diskgroup DATA -verbose
Resource ora.DATA.dg will be stopped

[oracle@ol6-121-rac1 bin]$ srvctl predict vip -vip ol6-121-rac1
VIP ol6-121-rac1 will be started on node ol6-121-rac2
Listener LISTENER will be stopped

[oracle@ol6-121-rac1 bin]$ srvctl predict network -netnum 1
Listener LISTENER_SCAN3 will be started on node ol6-121-rac2
Listener LISTENER will be stopped
Listener LISTENER_SCAN1 will be stopped
Listener LISTENER_SCAN2 will be stopped
Listener LISTENER_SCAN3 will be stopped
Resource ora.cvu will be started on nodes ol6-121-rac2,ol6-121-rac1
Network number 1 will be started on node ol6-121-rac1
Network number 1 will be stopped
Resource ora.cvu will be stopped
VIP ol6-121-rac1 will be started on nodes ol6-121-rac2,ol6-121-rac1
VIP ol6-121-rac2 will be started on node ol6-121-rac1
Resource ora.ons will be stopped
VIP scan1 will be started on node ol6-121-rac1
VIP scan2 will be started on nodes ol6-121-rac2,ol6-121-rac1
VIP scan3 will be started on node ol6-121-rac2
Listener LISTENER_SCAN1 will be started on node ol6-121-rac1
Listener LISTENER_SCAN2 will be started on nodes ol6-121-rac2,ol6-121-rac1
VIP scan1 will be stopped
VIP scan2 will be stopped
VIP scan3 will be stopped
Listener LISTENER will be started on node ol6-121-rac1
Resource ora.ons will be started on node ol6-121-rac1

[oracle@ol6-121-rac1 bin]$ srvctl predict listener -listener LISTENER
Listener LISTENER will be stopped

[oracle@ol6-121-rac1 bin]$ srvctl predict listener -listener MGMTLSNR
Resource ora.MGMTLSNR will be started on node ol6-121-rac2
Resource ora.mgmtdb will be started on node ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict scan -scannumber 1
VIP scan1 will be started on node ol6-121-rac1
Listener LISTENER_SCAN1 will be started on node ol6-121-rac1
[oracle@ol6-121-rac1 bin]$ srvctl predict scan -scannumber 2
VIP scan2 will be started on node ol6-121-rac2
Listener LISTENER_SCAN2 will be started on node ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict scan_listener -scannumber 1
Listener LISTENER_SCAN1 will be started on node ol6-121-rac1
VIP scan1 will be started on node ol6-121-rac1
[oracle@ol6-121-rac1 bin]$ srvctl predict scan_listener -scannumber 2
Listener LISTENER_SCAN2 will be started on node ol6-121-rac2
VIP scan2 will be started on node ol6-121-rac2

[oracle@ol6-121-rac1 bin]$ srvctl predict oc4j
Resource ora.oc4j will be started on node ol6-121-rac1

From the above o/p except few exceptions the rest makes sense. For example, in case of the diskgroup failure the o/p says the diskgroup resource will be stopped. But it did not mention anything about the failure of the instances running the in the same node which is not correct.

Planning the same with "eval" in a different post.
Happy Reading!!

12c Clusterware - An outlook

Thanks to Tim Hall. With the help of his notes i was able to successfully install my 1st 12C Grid Infrastructure. Except for few tweaking to the network interface cards. I just followed his notes as it is. In virtual box my guest is oel 6.5 and host is Windows 8.I tried executing few 11.2 commands and 12.1 just takes it like a vanilla.

[oracle@ol6-121-rac1 bin]$ ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online


[root@ol6-121-rac1 bin]# crsctl stat res -t -init
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details    
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       ol6-121-rac1             Started,STABLE
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.crf
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.crsd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.cssd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.cssdmonitor
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.ctssd
      1        ONLINE  ONLINE       ol6-121-rac1             ACTIVE:0,STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.drivers.acfs
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.evmd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.gipcd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.gpnpd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.mdnsd
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.storage
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
--------------------------------------------------------------------------------

The only new addition to the init daemon is the ora.storage.  The following the o/p of resource status.

[oracle@ol6-121-rac1 bin]$ ./crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.asm
               ONLINE  ONLINE       ol6-121-rac1             Started,STABLE
               ONLINE  ONLINE       ol6-121-rac2             Started,STABLE
ora.net1.network
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.ons
               ONLINE  ONLINE       ol6-121-rac1             STABLE
               ONLINE  ONLINE       ol6-121-rac2             STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       ol6-121-rac1             169.254.149.190 192.
                                                             168.1.101,STABLE
ora.cdbrac.db
      1        ONLINE  ONLINE       ol6-121-rac1             Open,STABLE
      2        ONLINE  ONLINE       ol6-121-rac2             Open,STABLE
ora.cvu
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       ol6-121-rac1             Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.ol6-121-rac1.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.ol6-121-rac2.vip
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       ol6-121-rac2             STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       ol6-121-rac1             STABLE
--------------------------------------------------------------------------------

We could see couple of new resources here as well.
1. ora.MGMTLSNR
2. ora.mgmtdb

One point i noticed the term STABLE in state_details does not have anything to represent the health of a resource. As per documentation this is the INTERNAL_STATE of a resource.

INTERNAL_STATE
An internally managed, read-only attribute that describes what, if any, action the policy engine is currently executing on the resource. Possible values and their meanings are as follows:

STARTING: The policy engine is currently starting the resource

STOPPING: The policy engine is currently stopping the resource

CLEANING: The policy engine is currently cleaning the resource

STABLE: The policy engine is not currently executing any action on the resource

Note, however, that the resource can still be locked as part of some other command.

After finishing this post i wanted to list all the daemons part of 12c CRS. While i was looking for further information on evmlogger, my bad!! i found out that a similar article is already published in Oracle support. 1517182.1
Happy reading!!!

Flushing a single statement from Shared Pool

While testing performance issue of a single SQL statement, we would often want to enforce a hard parse of the to SQL statement to check, how the plan is generated after any changes. Though the old method of flushing the complete shared_pool is still possible,

ALTER SYSTEM FLUSH SHARED_POOL;

From 11g, we have the option of just flushing a single SQL statement from the shared pool which restricts any impact only with that particular statement. This is possible with the package DBMS_SHARED_POOL. We need to create this package by calling the script dbmspool.sql.

SQL> @?\rdbms\admin\dbmspool.sql

Package created.

Grant succeeded.

Once the package is created, we may use the PURGE procedure of the same package to flush the SQL. To demonstrate, am running a statement,

SQL> select * from scott.emp;

Now i could see the sql loaded to the shared pool.

SQL> select sql_id from v$sql where sql_text like 'select * from scott.emp';

SQL_ID
-------------
ggqns3c1jz86c

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_text='select * from scott.emp';

ADDRESS  HASH_VALUE
-------- ----------
272BDF88   52404428

Now based on the ADDRESS & HASH_VALUE am purging this SQL from shared_pool using the below statement. Here 'C' stands for cursor.

SQL> exec dbms_shared_pool.purge ('272BDF88,52404428','C');

PL/SQL procedure successfully completed.

After the purge the SQL is flushed out of the pool and will be hard parsed when executed again.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_text='select * from scott.emp';

no rows selected

SQL> select sql_id from v$sql where sql_text like 'select * from scott.emp';

no rows selected

Additional references: Doc Id : 457309.1

SQL Plan Management

SQL Plan Management:

Purpose: 

Prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.

Factors affecting plan Management:

• New optimizer version
• Changes to optimizer statistics and optimizer parameters
• Changes to schema and metadata definitions
• Changes to system settings
• SQL profile creating

Steps:

1. Capture Plan Baseline: 2 ways

* Automatic Plan Capture:
Setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE

* Manual Plan Capture

• Loading Plans from SQL Tuning Sets and AWR Snapshots.
• Loading Plans from the Cursor Cache

2. Selecting SQL Baseline

OPTIMIZER_USE_SQL_PLAN_BASELINES

3. Evolving base line plans: 2 ways
* Manual Method: Plans loaded from SQL tuning set or cursor cache.

* Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

To review SQL Plan Baselines

select * from table( 

    dbms_xplan.display_sql_plan_baseline( 

        sql_handle=>'SYS_SQL_209d10fabbedc741', 

        format=>'basic'));

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL Management Base:

Set supplied PL/SQL packages to facilitate the disk usage, retention time and movement of baselines 
across environments.

Tried out example

Test:: [Extended from the example above]
1. Dropped the SQL Plan baseline which was created for FTS.
2. Now i dropped the index SPM_TST_TAB_IDX from the table.
3. Now we have SQL Plan baseline created for the index range scan. But in the table the index is dropped.
4. Now when i ran the SQL statement, a new SQL Plan baseline is created and is used.
5. But the new baseline is not a accepted one.

Oracle Documentation
DBMS_SPM

How CBO infers stale statistics

Oracle CBO infers STALE statistics primarily from view *_TAB_MODIFICATIONS. This particular view shows the changes in your table but not immediately. Lets check

SQL> create table OBJECTS as select * from dba_objects;
Table created.

SQL> insert into objects select * from dba_objects;
72565 rows created.

SQL> commit;
Commit complete.

SQL> select * from user_tab_modifications where table_name='OBJECTS';
no rows selected

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS

This is because, the monitoring information is in the shared_pool and is flushed by Oracle only at a periodical intervals. When i manually flush it,

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> select table_name,inserts, timestamp from user_tab_modifications where table_name='OBJECTS';
TABLE_NAME                        INSERTS TIMESTAMP
------------------------------ ---------- ---------
OBJECTS                             72565 21-DEC-14

Now i see 72565 records inserted. To further test I run inserts, followed by few deletions.

SQL> insert into objects select * from dba_objects;
72565 rows created.

SQL> delete from objects where rownum<1000;
999 rows deleted.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> select table_name,inserts, deletes from user_tab_modifications where table_name='OBJECTS';
TABLE_NAME                        INSERTS  DELETES
------------------------------ ---------- ----------
OBJECTS                            145130        999

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS

So if a table is under monitoring(YES for MONITORING in DBA_TABLES) the DML's are monitored  which is flushed to the MODIFICATIONS table when we call the procedure DBMS_STAT. FLUSH_DATABASE_MONITORING_INFO manually.

The same flush also happens when GATHER_*_STATS procedure is called, Let's test that. Please note the STALE_STATS is NULL before collecting statistics as shown before.

SQL> exec dbms_stats.gather_table_stats('STATS','OBJECTS');
PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications where table_name='OBJECTS';
no rows selected

As the document says, the *_TAB_MODIFICATIONS view is populated with the modifications for tables set MONITORING to TRUE and is purged immediately after the statistics is collected.

Actually the statistics staleness recorded in DBA_TAB_STATISTICS uses this data from *_TAB_MODIFICATIONS to identify if the statistics is stale or not. For example,

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS                        NO

SQL> update objects set owner='SRI';
216696 rows updated.

SQL> commit;
Commit complete.

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS                        NO

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS                        YES

In the above case, initially the STALE_STATS is NO. Then i Updated the whole table. But again when i check the stats is not updated as stale as the monitoring data is not flushed. When i flush it manually, it is set to YES. We can also control for what % of change this STALE_STAT should be changed to YES with the help of STALE_PCT from DBA_TAB_PREFS.

Manual creation of Oracle RAC DB on Windows

Manual RAC DB creation on Windows.

Assumption:
1. The clusterware is installed and is up and running on both the nodes.
2. The Oracle Home is installed on both the nodes.

Steps:
1. First we need a shared storage to store the Oracle DB files, Redo Logs, Control Files etc. So we need to create ASM diskgroups on which the mentioned files can be accomodated. CREATE ASM DISKGROUPS

2. Create the pwd(orapwd)file and service(oradim) utility. Please note the service to be created for the instance names so, SID will change between the 1st and 2nd node.

3. Create the init.ora parameters as we do for normal single instance and create the DB using CREATE DATABASE statement. Please exercise caution while mentioning

• MAXINSTANCES
• MAXLOGFILES
• MAXLOGMEMBERS

3. Make sure to point the control file to the right file name. Just pointing the control_files parameter to a disk group will not help. After DB creation find the exact file name of the control_files from 

select * from v$controlfile;

Update the value in init.ora
At this point you ll have one instance fully functional in open mode.

4. Now make the necessary parameter changes to the init.ora.

• Add cluster_database=true
• Add cluster_database_instance=n where n is the number of instances
• Then add instance_name, instance_number, Undo_tablespace & Thread specific to instances

5. Create respective Undo tablespace & Threads for the second instance.

6. Copy the init.ora & pwd file to node 2 and start the instance. The instance should come up fine w/o issues.

7. Once started, stop the instances and add them to the OCR throught the srvctl command so that the instances can be controlled as a clustered resource.

8. Create a centric spfile and update the same in OCR so that the agent of the CRS knows which file to refer to start the DB.

Hope this helps!!!

PGA Study

PGA Study:

Every time when we talk about memory of a Oracle DB the emphasis is more to the SGA than the PGA. Last month when I was asked to look after an issue for an application which is exhausting the server memory due to high consumption of the PGA, then i realised that I lack the basic details of PGA and assured myself with a detailed study about the same. In this blog am hoping to cover the need for PGA, Sizing of PGA and performance impact due to improper PGA sizing.

What is PGA?
From documentation, A PGA is a memory region that contains data and control information for a server process. It is non-shared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total memory used by all individual PGAs is known as the total instance PGA memory, and the collection of individual PGAs is referred to as the total instance PGA, or just instance PGA. You use database initialization parameters to set the size of the instance PGA, not individual PGAs.

An analogy for a PGA is a temporary countertop workspace used by a file clerk. The file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done.

What are contents of PGA?
1. Session memory(UGA):
* Holds session variables, Logon Information. In Shared server this is stored in SGA.
2. Private SQL area:
* Holds bind variable values, query state information & query execution work areas.PGA while using dedicated & SGA while using shared.
* A cursor is a name or handle to a specific private SQL area. You can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.
* The client process is responsible for managing private SQL areas. The allocation and deallocation of private SQL areas depends largely on the application, although the number of private SQL areas that a client process can allocate is limited by the initialization parameter OPEN_CURSORS.
* Sub-divided to
a. Run-time area: contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan. The run-time area is freed when the SQL statement is closed.
b. Persistent area: contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.
3. SQL Work area:
* Work area is a private allocation of PGA memory used for memory-intensive operations. Few ex: Sort area for a sort operation, hash area for hash join to build hash table, bitmap merge area for bitmap merge.

Documentation Reference

Enough of long texts now we ll jump in to few queries for a better understanding.
From sesstat or mystat we can view what is the Pga and Uga allocated for a session.

Here below, we see the PGA stats for my current session.

SQL> select sid||'--'||name||'--'||value from v$mystat natural join v$statname where name like '%memory%' and sid=sys_context('USERENV','SID');

SID||'--'||NAME||'--'||VALUE
--------------------------------------------------------------------------------
130--session uga memory--1302772
130--session uga memory max--1302772
130--session pga memory--1970404
130--session pga memory max--1970404
130--redo k-bytes read (memory)--0
130--redo k-bytes read (memory) by LNS--0
130--workarea memory allocated--1204
130--sorts (memory)--0

Now we ll see the allocation at the process level.

SQL> select s.sid,p.pid from v$session s,v$process p where s.paddr=p.addr and sid=sys_context('USERENV','SID');
       SID        PID
---------- ----------
       130         22

SQL> select pid,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem from v$process where pid=22;
       PID PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------ ------------- ---------------- -----------
        22       660114       1280022                0     2263062

SQL> select * from v$process_memory where pid=22;
       PID    SERIAL# CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- ---------- --------------- ---------- ---------- -------------
        22         13 SQL                  46760      11856       1262732
        22         13 PL/SQL                2024        136          2024
        22         13 Other              1231238                  1231238

SQL> select sum(allocated),sum(max_allocated) from v$process_memory where pid=22;
SUM(ALLOCATED) SUM(MAX_ALLOCATED)
-------------- ------------------
       1280022            2487838

We can see from v$process & v$process_memory and equate sum of individual allocated memory with the PGA_ALLOC_MEM from v$process, though we see a slight difference in the max allocated. Below view gives us an overview at the instance level.

SQL> select * from v$pgastat;
NAME                                                    VALUE UNIT
-------------------------------------------------- ---------- ------------
aggregate PGA target parameter                      306184192 bytes
aggregate PGA auto target                           254923776 bytes
global memory bound                                  61236224 bytes
total PGA inuse                                      22934528 bytes
total PGA allocated                                  29167616 bytes
maximum PGA allocated                                52337664 bytes
total freeable PGA memory                                   0 bytes
process count                                              26
max processes count                                        32
PGA memory freed back to OS                                 0 bytes
total PGA used for auto workareas                           0 bytes
maximum PGA used for auto workareas                   1341440 bytes
total PGA used for manual workareas                         0 bytes
maximum PGA used for manual workareas                       0 bytes
over allocation count                                       0
bytes processed                                      53265408 bytes
extra bytes read/written                                    0 bytes
cache hit percentage                                      100 percent
recompute count (total)                                  1428

    Documentation Reference

In next post, Ill discuss about few parameters which determines the size of the PGA allocation to a DB.