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 write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk.
When Oracle requires a block it does a buffer get. First it checks to see if it already has the block it needs in memory. If so, the in-memory version is used. If it does not have the block in memory then it will read it from disk into memory. So a buffer get represents the number of times Oracle had to access a block.
SMON (System MONitor) is an Oracle background process created when you start a database instance. The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents. SMON must always be running for an instance.
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. The PGA is used to process SQL statements and to hold logon and other session information.
The db_cache_size initialization parameter (formerly db_block_buffers) governs the size of the RAM data buffer cache region. Staring in Oracle9i we also have separate database cache size parameters for multiple blocksizes, including db_nk_cache_size.
Oracle Metric Buffer Cache Hit Ratio Tips. The buffer hit ratio (BHR) indicates the current ratio of buffer cache hits to total requests, essentially the probability that a data block will be in-memory on a subsequent block re-read. A correctly tuned buffer cache can significantly improve overall database performance.
PMON (Process MONitor) is an Oracle background process created when you start a database instance. The PMON process will free up resources if a user process fails (eg. release database locks). PMON normally wakes up every 3 seconds to perform its housekeeping activities. PMON must always be running for an instance.
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.
When a checkpoint occurs, Oracle ensures all modified buffers are written from the data buffer to disk files. Checkpoint checks every three seconds to see whether the amount of memory exceeds the value of the PGA_AGGREGATE_LIMIT parameter,and if so,takes action.
PL/SQL is a block-structured language whose code is organized into blocks. A PL/SQL block consists of three sections: declaration, executable, and exception-handling sections. In a block, the executable section is mandatory while the declaration and exception-handling sections are optional. A PL/SQL block has a name.
A block is a unit of code that provides execution and scoping boundaries for variable declarations and exception handling. PL/SQL allows you to create anonymous blocks (blocks of code that have no name) and named blocks, which may be packages, procedures, functions, triggers, or object types.
In computing (specifically data transmission and data storage), a block, sometimes called a physical record, is a sequence of bytes or bits, usually containing some whole number of records, having a maximum length; a block size. Data thus structured are said to be blocked.
The default block size is 8k in Oracle. This is the most common. Sometimes, people create the database with 16k block size for datawarehouses. You can also find some 32k block size, but less common which means more bugs.
DBA_EXTENTS describes the extents comprising the segments in all tablespaces in the database. USER_EXTENTS describes the extents comprising the segments owned by the current user's objects. This view does not display the OWNER , FILE_ID , BLOCK_ID , or RELATIVE_FNO columns.
An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.
A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
- Arvind209. Answered On : Jun 29th, 2008.
- Component of PL/SQL Block are Begin(Optional), Declare(Mandatory), Exception(Optional), END(Mandatory).
PL/SQL has these advantages:
- Tight Integration with SQL.
- High Performance.
- High Productivity.
- Portability.
- Scalability.
- Manageability.
- Support for Object-Oriented Programming.
- Support for Developing Web Applications.
Description. The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Blocks in the buffer cache are placed on linked lists (cache buffer chains) which hang off a hash table.
The Oracle latch free wait event occurs when a session needs a latch, tries to get the latch, but fails because someone else has it. So, it sleeps with a wait on latch free, wakes up and tries again.
"gc buffer busy acquire": A session cannot pin the buffer in the buffer cache because another session is reading the buffer from the cache of another instance.
The row cache object latch can also indicate SQL with excessive hard parsing or excessive reliance on data dictionary information such as views row-level security, synonyms, etc. The general solution for row cache latch waits is to increase shared_pool_size.
