Instructor's Notes
(If applicable)
- Lesson 4: Oracle Architecture Part 3
- In this chapter we will take a close look at the memory structures associated with an instance of a database.
- This will increase your ability to manage and tune the database! Read on…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- In order to understand the concepts taught in this chapter, you will need to know the architecture concepts taught in previous lessons, plus SQL, SQL*Plus (an alternative query tool will do) and how to query the Oracle data dictionary.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- This is a review from the lesson Architecture, Part 1. In that lesson we focused on the operating system files. In Architecture, Part 2 you learned about the processes. In this lesson, we will discuss the memory areas illustrated here (shown as boxes).
- Before we start, note that most of the memory areas are part of a larger area called the System Global Area (SGA). These areas are shared by all users of the database. However, the Program Global Area (PGA), is not part of the SGA. Each user (session) gets their own private PGA area.
- We will discuss the purpose of these memory areas throughout the remainder of this lesson. Let’s get started…
Other Text:
(Examples or comments displayed on slide, if any).
DBWn
CKPT
PMON
Dedicated Servers
to clients
SGA
Buffer Cache
Shared Pool
Redo Buffer
SC
SC
Large Pool
Java Pool
I/O
I/O
RECO
SMON
to remote DB
K
R
DISP
Shared Servers
to clients
Instructor's Notes
(If applicable)
- The PGA, or "Program Global Area" is a "private" memory area. We characterize it as private because the PGA is used by one server process (i.e. user process); there will be one PGA created for each server process. (Note that PGA is sometimes referred to as the Process Global Area.)
- The PGA is a “work area” that contains “global variables and data structures and control information for a server process” (Oracle Metalink Note 223730.1). Metalink Note 223730.1 continues: “Example of such information is the runtime area of a cursor. Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor. Another good example is memory allocated for sorting.“
- In previous releases of Oracle, PGA size was controlled in large part with the SORT_AREA_SIZE parameter. Oracle9i introduces a new automatic PGA memory management feature configured with the parameters WORKAREA_SIZE_POLICY and PGA_AGGREGATE_TARGET. Refer to Chapter 22 of the Oracle9i Database Performance Tuning Guide and Reference for more information on tuning the size of the PGA.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The UGA, or "User Global Area" is another private memory structure used by a server process to maintain its state. A notable point about the UGA is that the UGA will be found within the PGA in dedicated server mode, but is moved to the SGA in shared server mode, causing the size of the SGA to grow when running in shared server mode.
- See the Metalink web site (http://metalink.oracle.com) and the Oracle9i Concepts Guide for more information on this memory structure.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The System Global Area (SGA) is a shared memory area used to hold data that can be used by all users connected to the database. For example, the buffer cache contains cached copies of database file blocks (i.e. table and index data read from files that can be used by all privileged users).
- The SGA itself has three main required components - the Database Buffer Cache, the Shared Pool and the Redo Buffer. We will cover these structures and more as we progress through this lesson.
- As of Oracle9i, the maximum overall size of the SGA is limited by the initialization parameter SGA_MAX_SIZE.
- Also see the Oracle9i Concepts Guide for more information on these memory structures.
Other Text:
(Examples or comments displayed on slide, if any).
The lesson titled “Initialization Parameter Files”, teaches you how to adjust all parameters
Instructor's Notes
(If applicable)
- Dynamic performance view V$SGA contains the current size of the major areas within the SGA. (This can also be displayed with the SQL*Plus command SHOW SGA.)
- The FIXED SIZE is used to hold Oracle internal information. It is typically small; we have no control over its size.
- We will discuss these areas in more detail in this lesson:
- The VARIABLE SIZE is used by many areas including the shared pool, large pool and the java pool.
- DATABASE BUFFERS is memory used by the database buffer cache.
- REDO BUFFERS is memory used for caching redo records.
- Supplemental Notes
- Oracle9i allocates memory in “granules. If the size of your SGA is less than 128MB, then granules are 4MB in size, otherwise they are 16MB. With this in mind, Oracle will – when the size of the memory areas are adjusted – round your requested values based on the granule size being used.
- Mini-Workshop
- Determine the size of the SGA memory structures on your instance by executing the query shown above.
Other Text:
(Examples or comments displayed on slide, if any).
Small internal Oracle area
Shared pool, large pool, java pool
Buffer Cache
Or use SHOW SGA command
Do the mini-workshop
Instructor's Notes
(If applicable)
- The shared pool is one of the large (typically) and important areas of the SGA. (I say large because it is typically a large percentage of the total memory used by the SGA. I say important because an improperly tuned shared pool will degrade overall system performance.)
- It is here that Oracle caches the data dictionary (dictionary cache) and recently executed SQL statements (library cache).
- The shared pool is one of the areas that the DBA will tune to achieve best overall performance from the database. The size of the shared pool is primarily controlled with the SHARED_POOL_SIZE parameter. (Note that Oracle puts many other things into the shared pool, over which we have no control, so the size of the pool will not be equal to the SHARED_POOL_SIZE parameter.)
- Refer to SkillBuilders course Oracle9i New Features for DBAs for tips on using the shared pool advisory for tuning the size of the shared pool.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The library cache is one of the major structures in the Shared Pool. Its main function is to store parsed SQL statements (and PL/SQL blocks).
- Before SQL can be executed, it needs to be translated by the user’s server process. This translation is known as parsing. (Note that a large part of the parse process is optimization - Oracle generates all available access paths for a statement, then chooses the path with the lowest cost. Refer to SkillBuilders’ SQL Tuning For Developers and DBAs course for more information.) The results of the parse are stored in a memory area called a cursor. These cursors live in the library cache of the shared pool. If an SQL statement is run a second time by the same or another server process it will not need re-parsing - as long as the parsed version of the SQL statement is still in the library cache.
- If the SQL statement is not found in the library cache, it will require parsing (or re-parsing). This process has several downsides:
- it uses valuable CPU time
- it requires latches (memory locks) on the library cache. If many server processes are parsing, contention for library cache latches can occur
- the library cache can become fragmented, increasing the time it takes to load large packages into the pool
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Parsed SQL (and PL/SQL) remains in the library cache until:
- free space in the cache is exhausted and new statements need to be parsed. Oracle looks for the least recently used (LRU) statements and steals the memory from those statements.
- an object that a parsed query refers to is altered (e.g. ALTER TABLE x ADD c2 NUMBER).
- the shared pool is manually flushed with the ALTER SYSTEM command. This is generally not advisable.
- the instance is shut down.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Before parsing an SQL statement, the user server process looks to see if the statement is already in the library cache. This is part of the “soft parse”, which includes: syntax check, privilege check, and a SQL hash value (SHV) is computed on the statement text. The SHV is used to identify the statement; the library cache is searched for a statement with the same SHV. If the statement is not found, the server process will then be forced to do a hard parse, which includes generating an execution plan and storing the result in the library cache.
- The SHV will be different (almost always; Oracle actually also does a character-by-character comparison too because two identical strings could actually hash to the same value) for two queries unless the statement text is an exact match. This means that the text, case (uppercase/lowercase), whitespace and any line breaks must match exactly.
- The following two statements, even though they produce the same result, are considered to be different because of the difference in case. The second statement will not be able to re-use the parsed version of the first in the cursor.
- SELECT * FROM test;
- select * from test;
- Notes for this slide continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Continued from the previous page:
- Use procedures, functions and packages to maximize cursor re-use. These stored database programs support parameter passing; thus, each caller can use the SQL within the stored procedure with different values. For example, in a common production system where more than one user is using a GUI to access the database, the very first to use the screen will cause the code to be optimized and stored in the shared pool. Further users will re-use the optimized versions of the statements, saving on the re-parsing overhead.
- Refer to SkillBuilders’ SQL Tuning for Developers and DBAs course for more information on this subject.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The data dictionary cache caches the Oracle data dictionary. This increases the efficiency of accessing the data dictionary. This is important because of the frequency of accessing the data dictionary. Just think, every time an SQL DML statement is parsed the dictionary is accessed to validate table and column references, privileges and to check for indexes.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The buffer cache is a staging area for most database operations. For example, when a session is created, a user's server process may process the SQL statement:
- SELECT * FROM CUSTOMER WHERE CUST_NO = 1
- If the required blocks are not already in the buffer cache, the server process (dedicated or shared) will read the block(s) from disk and store them in the buffer cache (from here the server process can return the correct row(s) to the requestor). This process is called a physical I/O and is sometimes the largest bottleneck in the database because of the delay waiting for physical movement of a disk drive. Note that if the block was already in the cache, a logical I/O is performed. A logical I/O requires that a “latch” (lock on a memory area) be taken. While a logical I/O is faster than a physical I/O (and thus the justification for the buffer cache), too many logical I/O’s will also hurt scalability and performance. When you move past the basic of Oracle administration and into tuning, this is one of the issues you’ll tackle.
- Blocks that have been updated are eventually written back to disk. This is the job of the database writer background process (DBWR). DBWR will write the blocks to the datafile when a checkpoint occurs, when the buffer cache becomes constrained for available blocks, or when the database is shutdown.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Oracle9i now supports two types of block sizes in a single database: standard and non-standard.
- The standard database block size is defined at database creation time by the DB_BLOCK_SIZE initialization parameter and as with previous versions of Oracle it cannot be changed without recreating the database. The standard database block size must be used for the SYSTEM, TEMPORARY, and ROLLBACK tablespaces and is the default block size used for other tablespaces.
- Oracle9i supports up to four non-standard block sizes in a single database. A non-standard block size is simply a different block size from the standard. Turn to the next page for an example and more discussion.
Other Text:
(Examples or comments displayed on slide, if any).
see next page for example
Instructor's Notes
(If applicable)
- In addition to the standard block size (defined by DB_BLOCK_SIZE), Oracle9i supports a maximum of 4 additional non-standard block sizes within a single database. You can choose from the following block sizes: 2k, 4k, 8k, 16k and 32k.
- For example, if the standard block size in our OLTP database is 8k, we may create a 32k non-standard block size cache to hold objects supporting a decision support application.
- A non-standard database block size can be assigned to a tablespace with the CREATE TABLESPACE statement using the BLOCKSIZE parameter. Tablespace definitions can be monitored with the DBA_TABLESPACES and V$DATAFILE views. A block size column has been added to both views to show the assigned block sizes for each tablespace. Don’t worry now about the syntax of the CREATE TABLESPACE statement; you will learn all about that in the Tablespaces lesson.
- Notes for this slide continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
CREATE TABLESPACE hr_data_tbs
BLOCKSIZE 16K
DATAFILE ‘e:\oradata\prod\hr_data_tbs.dbf’
SIZE 500M;
DB_16K_CACHE_SIZE=32000000
Instructor's Notes
(If applicable)
- Notes continued from the previous page:
- Non-standard block sizes of the same size as the standard block size are not permitted. If using non-standard database block sizes with transportable tablespaces, the COMPATIBLE initialization parameter must be set to 9.0 or higher in the target database.
- Before using a non-standard database block size, a subcache must be set up in shared memory. Use the DB_xK_CACHE_SIZE initialization parameters. These subcaches are additional memory areas – in addition to the default (standard) buffer cache. Remember, the total size of the SGA must remain within SGA_MAX_SIZE.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The purpose of using multiple buffer caches is to separate certain datasets from the rest of an application, reducing the contention for the same resources within the database buffer cache.
- The object of the KEEP buffer pool is to retain objects in memory, thus avoiding expensive I/O operations.
- The RECYCLE buffer pool is good for data that is not referenced frequently. The recycle pool will age blocks fast - blocks can be replaced as soon as they are no longer needed. Thus, objects that we do not want cached should be placed in the recycle pool.
- The BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE parameters have been deprecated in Oracle9i and have been replaced with the DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE parameters, which control the size of these memory areas.
- Supplemental Notes
- In Oracle9i the keep and recycle caches are separate memory areas defined in bytes and are not allocated out of the database buffer cache as they were in Oracle8i.
- As with the DB_CACHE_SIZE parameter, the DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE parameters allocate blocks based on the standard database block size defined by DB_BLOCK_SIZE.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> CREATE TABLE pinit (c1 NUMBER)
2 STORAGE (BUFFER_POOL KEEP);
Table created.
SQL> CREATE TABLE dont_cache (c1 NUMBER)
2 STORAGE (BUFFER_POOL RECYCLE);
Table created.
Instructor's Notes
(If applicable)
- "Redo" is data captured during an update to the database. It can be used to reapply changes to the database in the event of a failure, for example the loss of a disk device. Redo is kept in online and archived redo log files.
- The redo log buffer is a staging area for the redo log files. The buffer is written to disk when a commit is executed, when the redo buffer starts to fill and when a checkpoint operation is initiated.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Oracle8i introduced the Large Pool and the Java Pool.
- The Large Pool is an optional structure designed in part to eliminate user session information (UGA) from being stored in the Shared Pool when multi-threaded server is active; this allows the Shared Pool to be used primarily for SQL caching. See the LARGE_POOL_SIZE parameter in the Oracle9i Database Reference manual for sizing information.
- The Java Pool caches the Java class states and Java objects. Note that Oracle uses Java – even if you do not. For example, the UTL_TCP package incorporates Java classes. Thus, the minimum size for the Java pool is 1MB (see the JAVA_POOL_SIZE parameter).
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- So, now you have learned about database files, processes and memory areas. The size of memory areas have an impact on the performance of your database – in the lesson, Initialization Parameter Files, you will learn how to adjust these parameters. (Though you will need some more study – beyond this course – to learn how to tune your instance. a good starting point is chapter 22 of the Oracle9i Database Performance Tuning Guide and Reference.)
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Architecture: Memory Workshop
- What are the major memory structures in the SGA?
- What actions cause the all or a portion of the Library Cache to be flushed?
- Connect to your Oracle instance with the SYSTEM user.
- What is the size of the SGA in your instance?
- What is the size of your:
shared pool
java pool
large pool
keep pool
recycle pool
- Are any sub-caches defined in your instance?
Other Text:
(Examples or comments displayed on slide, if any).
