Sage-Tips

What is parsing in Oracle database?

When Oracle get a SQL query it needs to execute some tasks before being able to really execute the query. These tasks make up what is called parsing. To execute this job and limit the number of data that it must rebuilt each time, Oracle uses a shared memory area named the shared pool.

Why PGA is used in Oracle?

The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process.

What is difference between SGA and PGA?

Basic Memory Structures All server and background processes share the SGA. Examples of data stored in the SGA include cached data blocks and shared SQL areas. A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process.

Why Oracle database does keep spare blocks?

Buffer Cache Overview Oracle will keep frequently accessed blocks in memory to avoid the much slower physical I/O to access the blocks. Those data blocks that are not frequently used will be replaced over time with other database blocks.

What is PGA in Oracle?

A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is nonshared 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.

What is hard parse in Oracle?

A hard parse occurs when a SQL statement has to be loaded into the shared pool. In this case, the Oracle Server has to allocate memory in the shared pool and parse the statement. Each time a particular SQL cursor is parsed, this count will increase by one.

What is stored in PGA?

A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process.

What is PGA and UGA in Oracle?

pga and uga is always a server side concept. they are oracle memory regions. pga is PROCESS global area, it is in dedicated and shared servers (never in the SGA) the uga is USER global area, it is session memory and it is in the PGA when using dedicated server, in the SGA when using shared server.

What is UGA in Oracle?

The UGA is memory allocated to hold a stock of user-detailed information, such as program variables, sorting and hash joins. In a Shared Server environment (the MTS), Oracle allocates UGA memory inside the Shared Pool.

What is library cache in Oracle?

Oracle’s library cache is nothing more than an area in memory, specifically one of three parts inside the shared pool. The library cache is composed of shared SQL areas, PL/SQL packages and procedures, various locks & handles, and in the case of a shared server configuration, stores private SQL areas.

What is large pool in Oracle?

The large pool is an optional memory area. It provides an area of memory from which large allocations can be made. Oracle’s backup and restore utilities typically allocate buffers that are hundreds of kilobytes in size. These will be allocated in the large pool if one is present.

What is dirty buffer in Oracle?

A dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWR has written the blocks to disk. The database buffer cache is organized in two lists: the write list and the least-recently-used (LRU) list.

What is the use of SGA in Oracle?

SGA is used to store database information that is shared by database processes. It contains data and control information for the Oracle Server and is allocated in the virtual memory if the computer where Oracle resides.

What is SGA_target and why is it important?

It is significant that SGA_TARGET includes the entire memory for the SGA, in contrast to earlier releases in which memory for the internal and fixed SGA was added to the sum of the configured SGA memory parameters. Thus, SGA_TARGET gives you precise control over the size of the shared memory region allocated by the database.

What is system Global Area (SGA)?

SGA (System or Shared Global Area) SGA is used to store database information that is shared by database processes. It contains data and control information for the Oracle Server and is allocated in the virtual memory if the computer where Oracle resides.

How can I limit the amount of virtual memory the SGA uses?

Oracle Database can set limits on how much virtual memory the database uses for the SGA. It can start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter.