In this article, I will discuss various concepts and parameters about the cache mechanism in Oracle as also mentioned by Tom KYTE in his book “Expert Oracle” under the title “Terminology”.
If we were to put the concepts I’ll mention in order,
– Free List
Segment, in Oracle terminology, is the concept that represents a particular space on the Disc. In this context we may consider the Disc as a sum of the segments. There are different segment types present in Oracle. To touch upon these segment types;
– Cluster: Cluster is the segment type that has the ability to store charts. Also, The clusters have two different types. These are the B* Tree and Hash cluster types. Clusters are usually areas that hold the data formed by the joining of more than one tables on the same database block and the data emerged as a consequence of the joining. That joined data is stored on the clusters as if it is a one single table. From that point of view, we can infer that the joined data is stored physically in the same place.
– Table : Table segment is the segment structure that hosts database tables. It is the most general segment structure and they are used as connected with the index segments.
– Table Partition: This segment structure is used for the partition processes in Oracle. It is very similar to Table segment structure. Partition segment structure holds data particles that are separated by a particular rule inside the table.
– Index Partition: Index Partition shows similarities with Table partition segment structure but the difference is it holds indexes.
– Lob Partition, Lob Subpartition, Lobindex and Lobsegment: Lobindex and lobsegments hold objects that have large data structures. We may consider variants that have LOB data type as an example. In a table including LOB partition, lobsegment structure would also be partitioned. Lobsegment is used for this reason. (in order to spare areas to lobpartitions).
Actually there is no segment structure such as Lobindex present in Oracle, usually index partitions may be named Lobindex by Orcale.
– Nested Table: It’s a segment type which is assigned inside Nested Tables. Nested tables are a special type of the normal tables. These tables host master/detail style joins.
create or replace type my_type is table of varchar2(50); create table my_nested_table (tab_id number, colA my_type)
– Rollback : Rollback segments are segments that are created manually by DBAs. The descriptions about how the feedbacks will be given are held in this segments.
You can write the command
select * from user_segments;
to view the segments you created on your Databases.
PCTFREE and PCTUSED
PCTFREE value is the parameter that shows us how much empty space Oracle will leave on every data block to use for update processes in the future. When creating a table, if not specified otherwise, it is set as default 10%. If we want more area than 10% to be left, we have to specify this value manually while creating the table. Giving high amounts to this value on tables that we think to be updated constantly, will guarantee the storing of related data in the same datablock. Otherwise, fragmentation will happen. Thus, we might not use our disc capacity efficiently.
PCTUSED value however, shows the information of whether the datablock will be added to the freelist according to the usage ratio. If we do not specify it while creating the table, it is set as default 40%. That means as long as used space does not go over 40%, hold the related datablock in the freelist in order for new insertion precesses to be executed. If the usage ratio in the datablock reaches 40%, related datablock is extracted from the freelist.
It would be the rightest thing to set these parameters manually according to tables’ structres. For example, if there’re only insert and delete done in the table we created or the updates made are not towards adding a new column on rows, in this situation bringing PCTFREE value closer to zero and PCTUSED to 100 would be logical. Related data’s being physically in the same place would decrease our I/O number and increase our performance. In any case, keeping PCTUSED value high would prevent our disc from being fragmented and will make us use the disc efficiently.
LOGGING and NOLOGGING
Under normal circumstances, In Oracle every object is created as LOGGING, which means that in every operation where that object is present, it will create a redolog. NOLOGGING however, tells us that the process executed on the object will not create a redo.
Only a number of specific processes are not affected by NOLOGGING. These are, the moments objects are created, rebuilding of database objects and direct loading of path using SQL*Loader. This option except for a few specific situations, does not prevent objects from creating redologs. As an example, let’s assume that we created a table as NOLOGGING and insert an entry to the table. In this case it can be seen that the insertion process is logged but creation processes, because of the “as Select NOLOGGING” expression, is not logged. Although it is an exceptional situation, that kind of a case is possible to experience. However, under normal circumstances, we can suppose that there’s log creation during the creation of objects.
MAXTRANS and INITTRANS
Every datablock in the segments has a block header. Also the head parts of the blocks are transaction tables. The entries in this table show which transactions lock which row or element on the datablock. With INITTRANS parameter we determine the size of the transaction table. If not manually specified, this parameter is assigned as default 2. MAXTRANS value tells us how much will the transaction table grow in maximum when needed. Both MAXTRANS and INITTRANS can take values between 1-255. After The MAXTRANS scale is set, needed space is allocated in the cache by Oracle for transaction table. (In every transaction entry, there are also 23-24 bytes of headers covering up space.
REFERENCES: Tom Kyte, Expert Oracle- tahiti.oracle.com – Oracle SQL High Performance Tuning Prentence Hall