Sql Query To Check the PGA Space Utilization

Query to check the PGA Space Consumption.

select name, value
from v$sesstat ss, v$statname sn
where sn.name like '%ga memory%'
and ss.statistic# = sn.statistic#
  5  and ss.sid =&i;
Enter value for i: 1001
old   5: and ss.sid =&i
new   5: and ss.sid =1001

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session uga memory                                                   442688
session uga memory max                                              3931248
session pga memory                                                  1527080
session pga memory max                                              5655848


select category,allocated,used,max_allocated
from v$process_memory
where pid = (
        select  pid
        from    v$process
        where   addr = (
                select  paddr
                from    V$session
                where   sid = &i
            )
        )
 12  ;
Enter value for i: 1001
old   9:                 where   sid = &i
new   9:                 where   sid = 1001

CATEGORY         ALLOCATED       USED MAX_ALLOCATED
--------------- ---------- ---------- -------------
SQL                   1000        184         42864
PL/SQL               30584      25568         30584
Other              1505168                  1505168

select pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem
from v$process
where addr = (
           select  paddr
           from    V$session
           where   sid =&i
    )
  8  ;
Enter value for i: 1001
old   6:            where   sid =&i
new   6:            where   sid =1001

PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------ ------------- ---------------- -----------
     1425760       1536752                0     1536752

test.db.com:test>select NAME,VALUE,UNIT from v$pgastat
  2  order by VALUE desc;

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
bytes processed                                                  7.3362E+14 bytes
PGA memory freed back to OS                                      2.8569E+14 bytes
extra bytes read/written                                         4.8353E+12 bytes
maximum PGA allocated                                            1.1684E+10 bytes
aggregate PGA target parameter                                   6291456000 bytes
total PGA allocated                                              5351336960 bytes
total PGA inuse                                                  4372375552 bytes
maximum PGA used for auto workareas                              3277716480 bytes
aggregate PGA auto target                                        1727096832 bytes
total freeable PGA memory                                         666107904 bytes
global memory bound                                               629145600 bytes
maximum PGA used for manual workareas                               6993920 bytes
recompute count (total)                                             3452863
total PGA used for auto workareas                                   2445312 bytes
max processes count                                                    1000
over allocation count                                                  1000
process count                                                           779
cache hit percentage                                                  99.34 percent
total PGA used for manual workareas                                       0 bytes

19 rows selected.

select n.name,sum(s.value) value
from v$sesstat s,v$statname n
where s.STATISTIC#=n.STATISTIC#
and n.name like 'workarea executions%'
  5  group by n.name;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
workarea executions - multipass                                           0
workarea executions - optimal                                       2302483
workarea executions - onepass                                           104

NOTE.
     When a PGA fails to handle the sorting operation of a session then it use the temp segment space, this phenomena known as One Pass and if PGA looks the temp segment consistently to fulfill the session's sort operation then it called as Multi Pass.   

select SID,OPERATION_TYPE,EXPECTED_SIZE/1024,ACTUAL_MEM_USED/1024,MAX_MEM_USED/1024,NUMBER_PASSES,TEMPSEG_SIZE/1024
  2  from v$sql_workarea_active;

  SID OPERATION_TYPE       EXPECTED_SIZE/1024 ACTUAL_MEM_USED/1024 MAX_MEM_USED/1024 NUMBER_PASSES TEMPSEG_SIZE/1024
----- -------------------- ------------------ -------------------- ----------------- ------------- -----------------
 1390 GROUP BY (SORT)                    1206                 1054              1054             0
 1362 HASH-JOIN                          1675                  563               563             0


References.

http://jonathanlewis.wordpress.com/2009/06/07/pga-leaks/

log file sync wait event

As per Oracle Metalink Note(Doc ID 34592.1)

log file sync:
When a user session(foreground process)COMMITs (or rolls back), the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk.

This may be described further as the time user session/foreground process spends waiting for redo to be flushed to make the commit durable. Therefore, we may think of these waits as commit latency from the foreground process (or commit client generally). 

Reducing Waits / Wait times:

Here are 3 main general tuning tips to help you reduce waits on "log file sync":

• Tune LGWR to get good throughput to disk . eg: Do not put redo logs on RAID 5.
• If there are lots of short duration transactions see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be "piggybacked" by Oracle reducing the overall number of commits by batching transactions can have a very beneficial effect.
• See if any of the processing can use the COMMIT NOWAIT option (be sure to understand the semantics of this before using it).
• See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.
• Check to see if redologs are large enough. Enlarge the redologs so the logs switch between 15 to 20 minutes.

For more detailed analysis for reducing waits on LOG FILE SYNC please see below:

The overall wait time for LOG FILE SYNC may be broken down into subsections or components.
If your system still shows high "log file sync" wait times after ensuring the general tuning tips above are completed, you should break down the total wait time into the individual components, then tune those components that make up the largest time.

The log file sync wait may be broken down into the following components:
1. Wakeup LGWR if idle
2. LGWR gathers the redo to be written and issue the I/O
3. Time for the log write I/O to complete
4. LGWR I/O post processing
5. LGWR posting the foreground/user session that the write has completed
6. Foreground/user session wakeup


Tuning advice based on log file sync component breakdown above:
Steps 2 and 3 are accumulated in the "redo write time" statistic. (i.e. as found under STATISICS section of Statspack and AWR)
Step 3 is the "log file parallel write" wait event.
Steps 5 and 6 may become very significant as the system load increases. This is because even after the foreground has been posted it may take a some time for the OS to schedule it to run. May require monitoring from O/S level.

Data Guard Perspective:

For Data Guard with synchronous (SYNC) transport and commit WAIT defaults, the above tuning steps still apply, except step 3 also includes the time for the network write and the RFS/redo write to the standby redo logs.

Finding sessions who are causing excessive redo logs

In my production system I was getting the disk critical alert in the response of I tried to find out which session was causing the excessive amount of redo logs correspondingly the heavily archive logs creation. I used the below query to find out the same.


set line 300 pagesize 20select s.sid, s.serial#, s.username, s.program, i.block_changes
from v$session s, v$sess_io i
where s.sid = i.sid
and
rownum < 15
order by 5 desc, 1, 2, 3, 4;

SID          SERIAL# USERNAME PROGRAM                                                     BLOCK_CHANGES
---------- ---------- --------------- ------------------------------------------          -------------
       11854      54279 XXX             perl@xyz.com (TNS V1-V3)                           110280689

      1345          1                             oracle@db1.xyz.com (SMON)                        55428886
       777       4079     ABC              perl@dfe.com (TNS V1-V3)                           821177
       899      60423   bcfA679          perl@int.com (TNS V1-V3)                            100109
      1185      20245  sxf1                 perl@log.com (TNS V1-V3)                           28844
........
.............


select s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
from v$session s, v$transaction t
where s.taddr = t.addr
order by 5 desc, 6 desc, 1, 2, 3, 4;

SID    SERIAL# USERNAME      PROGRAM                                           USED_UBLK  USED_UREC
---------- ---------- -------------- --------------------------------------    ---------- ----------
       118      54279 XXX                perl@xyz.com (TNS V1-V3)                 954335  110280689
       703      9899   sdsd                 oracle@db1.xyz.com (TNS V1-V3)       1            17
       417       5719                          httpd@fcweb12.xyz.com (TNS V1-V3) 1            1
.....
.....

Thanks for your time... cheers  :)

Oracle Wait Events.

In this post I'll mention the description of some common Oracle wait events.

Wait Event: 

An event can be defined as a particular function, or task, that the Oracle Kernel performs on behalf of the user session or its own BG process. Task such as read/write data block to and fro datafiles., waiting for latch acquisitions before accessing or manipulating data in the memory.

Every session that is connected to an Oracle instance needs resources to perform its task. A resource may be a data buffer, a latch, an enqueue (lock), a pin, or a database object. Whenever a session has to wait for something , the wait time is tracked and charged to the event that is associated with that wait.

For example, a session that needs an index block if is not in the SGA memory, will make a read call to the OS and waits for the delivery of the block. The wait time is charged to the db file sequential read event.

If a session may have completed the last instruction and is now idle, waiting for user input. In this case, the wait time is charged to the SQL*Net message from client event. 

In short, when a session is not using the CPU, it may be waiting for a resource, an action to complete. 

Hence, events that are associated with all such waits are known as wait events.

Buffer Busy Waits.

The buffer busy waits event occurs when a session wants to access a data block in the buffer cache which is currently in use by another session. The other session is either reading the same block into the buffer cache from the datafile or it is modifying the one in the buffer cache.

In order to guarantee that the reader session has a consistent image of the block with either all of the changes or none of the changes, the session modifying the block MARKS THE BLOCK HEADER WITH A FLAG   letting other sessions know that a change is taking place and to wait until the complete change is applied.

Control File Parallel Write.

The control file parallel write event occurs when the session waits for the completion of the write requests to all of the control files. The server process issues these write requests in parallel. The Checkpoint (CKPT) process writes the checkpoint position in the online redo logs to the control files every 3 secs. Oracle uses this information during database recovery operation. Also, when you perform DML operations using either NOLOGGING or UNRECOVERABLE option, Oracle records the unrecoverable SCN in the control files.

DB File Parallel Read.

This event occurs during the database recovery operation when database blocks that need changes as a part of recovery are read in parallel from the datafiles. This event also occurs when a process reads multiple non-

contiguous single blocks from one or more datafiles.

DB File Single Write.
The db file single write event is occurred by DBWR. It occurs when Oracle is updating data file headers, typically during a checkpoint.

DB File Parallel Write.

This event belongs to the DBWR process, as it is the only process that writes the dirty blocks to the datafiles. 

DBWR compiles a set of dirty locks into a "WRITE BATCH". It issues multiple IO requests to write batch to the datafiles and waits on this event until the I/O requests are completed.

DB File Scattered Read.

This event happens when the session issues an I/O request to read multiple data blocks. The blocks read from the datafiles which are scattered into the buffer cache. The event typically occurs during full table scans or index fast full scans.

DB File Sequential Read.

The event occurs when reading from an index, rollback or undo segments, table access by rowid, rebuilding control files, dumping data file headers, or the data file headers. This is a single block read operation.

Direct Path Read.

The direct path read event occurs when Oracle is reading data blocks directly into the session’s PGA instead of the buffer cache in the SGA.  Direct read I/O is normally used while accessing the temp segments that reside on the disks. These operations include sorts, parallel queries, and hash joins.

Direct Path Write.

The direct path write wait event when Oracle writes buffers from the session’s PGA to the datafiles. This operation is normally used when writing to temporary segments, in direct data loads (inserts with APPEND hint, or CTAS), or in parallel DML operations.

Enqueue.

An enqueue is a shared memory structure used by Oracle to serialize access to the database resources. The process must acquire the enqueue lock on the resource to access it. The process will wait on this event if the request to acquire the enqueue is not successful because might be some other session is holding a lock on the resource. The processes wait in queue for their turn to acquire the requested enqueue. A simple example of such an enqueue wait is a session waiting to update a row when some other session has updated the row and not yet committed (or rolled back) its transaction and has a lock on it in an exclusive mode.

There are various types of enqueue to serialize access to various resources, uniquely identified by a two-character enqueue name. For example:

ST Enqueue for Space Management Transaction 

SQ Enqueue for Sequence Numbers

TX Enqueue for a Transaction

Free Buffer Waits.

This event occurs when the session cannot find free buffers in the database buffer cache to read in data blocks or to build a consistent read (CR) image of a data block. This could mean either the database buffer cache is too small, or the dirty blocks in the buffer cache are not getting written to the disk fast enough. The process will signal DBWR to free up dirty buffers but will wait on this event.

Latch Free.
The latch free wait occurs when the process waits to acquire a latch that is currently held by other process. Like enqueue, Oracle uses latches to protect data structures. One process at a time can either modify or inspect the data structure after acquiring the latch. Other processes needing access to the data structure must wait till they acquire the latch.

Unlike enqueue, processes requesting latch do not have to wait in a queue. If the request to acquire a latch fails, the process sleeps and requests the latch again. The short sleep time is called “spin”. the process sleeps for a short time and tries to acquire the latch again, sleeping for successively longer periods until the latch is obtained.

Library Cache Lock.
A session must acquire a library cache lock on an object to prevent other sessions from accessing it at the same time or to locate an object in the library cache.

Log Buffer Space.

The log buffer space wait occurs when the session has to wait for space to become available in the log buffer to write new information. The LGWR process periodically writes to redo log files from the log buffer and makes those log buffers available for reuse. This wait indicates that the application is generating redo information faster than LGWR process can write it to the redo files. Either the log buffer is too small, or redo log files are on disks with I/O contention.

Log File Parallel Write.

The log file parallel write wait occurs when the session waits for LGWR process to write redo from log buffer to all the log members of the redo log group. This event is typically posted by LGWR process.

Log File Sequential Read.

The log file sequential read wait occurs when the process waits for blocks to be read from the online redo logs files. The ARCH process encounters this wait while reading from the redo log files.

Log File Sync.

When a user session completes a transaction, either by a commit or a rollback, the session’s redo information must be written to the redo logs by LGWR process before the session can continue processing. The process waits on this event while LGWR process completes the I/O to the redo log file.

SQL*Net Message From Client.

This wait event is posted by the session when it is waiting for a message from the client to arrive. Generally, this means that the session is sitting idle. Excessive wait time on this event in batch programs that do not interact with an end user at a keyboard may indicate some inefficiency in the application code or in the network layer. However, the database performance is not degraded by high wait times for this wait event.

SQL*Net Message To client.

This wait event is posted by the session when it is sending a message to the client. The client process may be too busy to accept the delivery of the message, causing the server session to wait, or the network latency delays may be causing the message delivery to take longer.

Row Cache Lock.

The dictionary cache is known as row cache because it keeps the information at row level, as opposed to the buffer cache, which keeps the information at block level. The locks, which protect the definition of the data dictionary objects, are called row cache locks. Normally, DDL statements require row cache lock, and the session will wait for the row cache lock to lock the data dictionary information.