Previous | Table of Contents | Next |
This process works well and saves you time by storing data in memory to avoid slow data transfers from disk. Let me challenge you with an opportunity to save even more time and show your database that you know more than it does. Unless you have an extremely large amount of real memory, you have to limit your database buffer cache to some reasonable value. The LRU algorithm therefore looks at data from a short-term (maybe several minutes) point of view. You, however, know your applications and users very well and understand the big picture. You know that there are several relatively small tables that are used frequently (perhaps they are used to look up values to make calls against your big data tables). The LRU algorithm may call these rows into the database cache at the beginning of one of these queries, but they are eventually pushed out as you move in row after row of data from that large main table. When the next user issues a query, you have to start this process over again. However, Oracle7.1 and later gives you the option of caching a table in memory. This has the effect of bringing the entire table into memory and keeping it near the most recently used end of the database buffer cache. You have to be careful, because too many cached tables can fill up the buffer cache making that slow disk access a necessity for every row read. However, used with good judgment, this can be a useful tool to speed up databases that are suited to benefit from it. An example of how to do this would be:
SELECT /*+ CACHE(jgreene.lookups) */ parameter FROM jgreene.lookups;
The next area of the SGA to explore is the redo log buffer. As discussed in Chapter 2, updates to the Oracle data files are recorded in the data files and also separately in the redo log files. This record of the transaction enables Oracle to recover data in the event of the complete loss of a disk drive. The redo log transactions are stored in memory to optimize their write performance. Because most operating systems read and write blocks that are several kilobytes in size, it is more efficient to queue up an entire block of data and then write it.
Because the redo log buffer is designed for writing as opposed to reading, it uses the first-in, first-out approach to storage. Redo log entries are added in the order they are received and the log writer process of Oracle comes along and takes one or more blocks of these records and writes them to the online redo log files. The redo log buffer then serves as the way-station for redo log entries, sitting between the Oracle process that creates the database update transaction and the redo log files on disk.
The third of the five areas within the SGA is the shared pool. You have seen the benefits of storing data that has recently been read from disk or needs to be written to disk (data and log files) in memory. The developers of Oracle looked at the services performed by a database and came up with a number of other things that should be stored in memory areas to improve overall performance. The shared pool contains three of these performance improving memory areas (see Figure 10.2):
Figure 10.2. The shared pool memory areas.
The first of these shared pool area stores what Oracle calls shared SQL. The structured query language (SQL) is a standard way of interacting with the database that is common to Oracle, IBMs DB2, Informix, and a high percentage of the multi-user databases on the market today. An example of an SQL statement would be the following, which pulls back all the rows and columns from the fictitious big_payroll table.
select * from big_payroll
Oracle performs a fair bit of work to service this request. For example, Oracle needs to figure out what are the columns in the big_payroll table, whether there is an index that can be used to speed the query, whether the user has access privileges to this data, and many other bits of information to get the data for the user. In many databases, there are a series of queries that are frequently used (an order entry system may routinely pull up a list of valid product codes, for example). The shared SQL area stores the analyzed (or parsed) query for any user to access without having to re-parse the query. Queries are stored in this area, which is also referred to as the library cache, using a modified form of the LRU algorithm.
The second part of the shared pool area is the data dictionary cache. In all queries and transactions with the database, the database management system needs to determine where the data is. This includes such details as what the object (table) names are, what tablespaces they are located in, where their records are within the data file, and what their columns are. In addition, database security requires Oracle to verify that the user has the appropriate access permissions for that database object. All this dictionary data information is stored within various system tables in the database. Such frequently used information is a wonderful candidate for caching. The data dictionary cache stores this for rapid access by the RDBMS.
Sometimes the data dictionary cache is referred to as the row cache. To me, row cache would be what I would call the area that stores rows of data from the database (the database buffer cache). However, I am not the designer of the system or the names used to refer to the components of the system.
The final area within the shared pool contains the cursors. Cursors are actually stored within the shared SQL area, but they are conceptually different so lets look at them separately. The previous select statement causes the results of its query to be displayed onscreen. What if you want to store the results in a memory area so you can manipulate the data in some way? Basically speaking, that is what a cursor does. It stores data retrieved from the database for further processing. Oracle creates its own internal cursors (known as recursive cursors) when it performs statements such as CREATE TABLE. Statements such as this cause a number of updates to various data dictionary tables (which are a series of SQL statements, referred to as recursive calls, that Oracle takes care of behind the scenes). Both of these forms of cursors take up space in the shared SQL area and should be considered when you are sizing the shared pool. You should also note that part of the storage required to support cursors is in the PGA (discussed in the next section).
Previous | Table of Contents | Next |