MEMORY STRUCTURES in Font

Generate Data Matrix in Font MEMORY STRUCTURES

sys@ORA9IR2> / TCH FILE# DBABLK D ---------- ---------- ---------- 2 1 1617 X 0 1 1618 X sys@ORA9IR2> exec dbms_lock.sleep(3.2); PL/SQL procedure successfully completed. sys@ORA9IR2> / TCH FILE# DBABLK D ---------- ---------- ---------- 3 1 1617 X 0 1 1618 X sys@ORA9IR2> exec dbms_lock.sleep(3.2); PL/SQL procedure successfully completed. sys@ORA9IR2> / TCH FILE# DBABLK D ---------- ---------- ---------- 4 1 1617 X 0 1 1618 X I expect output to vary by Oracle release you may well see more than two rows returned. You might observe TCH not getting incremented every time. On a multiuser system, the results will be even more unpredictable. Oracle will attempt to increment the TCH once every three seconds (there is a TIM column that shows the last update time to the TCH column), but it is not considered important that the number be 100 percent accurate, as it is close. Also, Oracle will intentionally cool blocks and decrement the TCH count over time. So, if you run this query on your system, be prepared to see potentially different results. So, in Oracle8i and above, a block buffer no longer moves to the head of the list as it used to; rather, it stays where it is in the list and has its touch count incremented. Blocks will naturally tend to move in the list over time, however. I put the word move in quotes because the block doesn t physically move; rather, multiple lists are maintained that point to the blocks and the block will move from list to list. For example, modified blocks are pointed to by a dirty list (to be written to disk by DBWn). Also, as they are reused over time, when the buffer cache is effectively full, and some block with a small touch count is freed, it will be placed back into approximately the middle of the list with the new data block. The whole algorithm used to manage these lists is fairly complex and changes subtly from release to release of Oracle as improvements are made. The actual full details are not relevant to us as developers, beyond the fact that heavily used blocks will be cached, and blocks that are not used heavily will not be cached for long.

Starting in Oracle9i, you can have multiple database blocksizes in the same database. Previously, all blocks in a single database were the same size and in order to have a different blocksize, you had to rebuild the entire database. Now you can have a mixture of the default blocksize (the blocksize you used when you initially created the database; the size that is used for the SYSTEM and all TEMPORARY tablespaces) and up to four other blocksizes. Each unique blocksize must have its own buffer cache area. The Default, Keep, and Recycle pools will only cache blocks of the default size. In order to have nondefault blocksizes in your database, you will need to have configured a buffer pool to hold them. In this example, my default blocksize is 8KB. I will attempt to create a tablespace with a 16KB blocksize: ops$tkyte@ORA10G> create tablespace ts_16k 2 datafile size 5m 3 blocksize 16k; create tablespace ts_16k * ERROR at line 1: ORA-29339: tablespace blocksize 16384 does not match configured blocksizes ops$tkyte@ORA10G> show parameter 16k NAME TYPE VALUE ------------------------------------ ----------- -----------------------------db_16k_cache_size big integer 0 Right now, since I have not configured a 16KB cache, I cannot create such a tablespace. I could do one of a couple things right now to rectify this situation. I could set the DB_16K_ CACHE_SIZE parameter and restart the database. I could shrink one of my other SGA components in order to make room for a 16KB cache in the existing SGA. Or, I might be able to just allocate a 16KB cache if the SGA_MAX_SIZE parameter was larger than my current SGA size.

Note Starting in Oracle9i, you have the ability to resize various SGA components while the database is up and running. If you want the ability to grow the size of the SGA beyond its initial allocation, you must have set the SGA_MAX_SIZE parameter to some value larger than the allocated SGA. For example, if after startup your SGA size was 128MB and you wanted to add an additional 64MB to the buffer cache, you would have had to set the SGA_MAX_SIZE to 192MB or larger to allow for the growth.

In this example, I will shrink my DB_CACHE_SIZE since I currently have it set rather large: