|Oracle7 Parallel Server Concepts and Administrator's Guide||
Figure 11 - 1. Instances Concurrently Inserting to a Table
To take advantage of this capability, you must actively manage free space in your database using several structures which are defined in this chapter.
For each database object (a table, cluster, or index), Oracle keeps track of blocks with space available for inserts (or for updates which may cause rows to exceed space available in their original block). A user process that needs free space can look in the master free list of blocks that contain free space. If the master free list does not contain a block with enough space to accommodate the user process, Oracle allocates a new extent.
New extents that are automatically allocated to a table add their blocks to the master free list. This can eventually result in contention for free space among multiple instances on a parallel server because the free space contained in automatically allocated extents cannot be reallocated to any group of free lists. You can have more control over free space if you specifically allocate extents to instances; in this way you can minimize contention for free space.
A segment thus comprises a set of extents allocated for a specific type of data structure. For example, each table's data is stored in its own data segment, while each index's data is stored in its own index segment.
The extents of a segment are all stored in the same tablespace; they may or may not be contiguous on disk. The segments can span files, but individual extents cannot.
Note that although you can allocate additional extents, the blocks themselves are allocated separately.
Figure 11 - 2 shows a segment which consists of three extents containing 10K, 20K, and 30K of space, respectively. The high water mark is in the middle of the second extent, thus the segment contains 20K of used space (to the left of the high water mark), and 40K of unused space (to the right of the high water mark.
Figure 11 - 2. High Water Mark
See Also: Oracle7 Server Concepts for further information about segments and extents.
The following figure shows the blocks of a file in which the master free list is stored in the segment header block. Three instances are pinging this block in their effort to obtain free space. Because there is only one free list, there is only one insertion point. Process free lists can help to reduce this contention by spreading this insertion point over multiple blocks, each of which will be accessed less often.
Figure 11 - 3. Contention for Master Free List
Process free lists must be specifically enabled by the user. You can specify the number of process free lists desired by setting the FREELISTS parameter when you create a table, index or cluster. The maximum value of the FREELISTS parameter depends on the Oracle block size on your system. In addition, for each free list, you need to store a certain number of bytes in a block to handle overhead.
Additional Information: The reserved area and the number of bytes required per free list depend upon your platform. For more information, see your Oracle system-specific documentation.
A process free list can be drawn from the master free list of blocks, or you can partition multiple free lists so that extents are allocated to specific database instances. The free list is tied to the instance--it determines where a row can be inserted. Process free lists are stored in the segment header block of a file, if no free list groups are allocated.
Blocks in free lists contain free space greater than PCTFREE (the percentage of a block to be reserved for updates to existing rows). In general, blocks included in process free lists for a database object must satisfy the PCTFREE and PCTUSED constraints described in the chapter "Data Blocks, Extents, and Segments" in Oracle7 Server Concepts.
As illustrated in Figure 11 - 4, process free lists provide free data blocks from available extents to different instances. However, without free list groups, the instances must nonetheless all ping the segment header block in order to access the free lists.
Figure 11 - 4. Process Free Lists
Process free lists thus help to solve the problem of multiple users in a single instance environment by easing contention on segment header blocks in an instance. By contrast, a parallel server has multiple instances, and the process free lists alone cannot solve the problem of contention here. Free list groups, however, effectively reduce pinging between instances.
Attention: In Oracle Parallel Server, we recommend that you always use free list groups, along with process free lists.
Each free list group provides free data blocks to accommodate inserts or updates on tables and clusters, and is associated with instance(s) at startup.
The table was created with one initial extent, after which extents 2 and 5 were allocated to instance X, extents 3 and 4 were allocated to instance Y, and extent 6 was allocated automatically (not to a particular instance). Notice the following:
Figure 11 - 5. Groups of Free Lists for a Table
The SQL statements include:
You can use these SQL options with the initialization parameter INSTANCE_NUMBER to associate data blocks with instances.
See Also: Oracle7 Server SQL Reference for complete syntax of these statements.
Each table has a master free list of blocks with available space, and can also contain multiple free lists. Before looking in the master free list, a user process scans the appropriate free list to locate a block that contains enough space.
In general, all tables should have the same number of free list groups, but the number of free lists within a group may vary, depending on the type and amount of activity of each table.
Partitioning free space can particularly improve the performance of applications that have a high volume of concurrent inserts, or updates requiring new space, from multiple instances. Performance improvements also depend, of course, on your operating system, hardware, data block size, and so on.
Note: In a multi-instance environment, information about multiple free lists and free list groups is not preserved upon import. If you use Export and Import to back up and restore your data, it will be difficult to import the data so that it is partitioned again.
See Also: "Free Lists with Import and Export Utilities" .
"Application Analysis" for more information on partitioning data.
Figure 11 - 6. How Free Lists and Free List Groups Are Assigned
* Free lists are partitioned as follows: If there are 3 instances and 35 free list groups, then instance 1 will handle the first twelve free list groups, instance 2 the next twelve, and instance 3 the remaining eleven. The actual free list group block is determined by hashing oracle_pid by the number of free list groups.
Groups of free lists allow you to associate instances with different sets of data blocks for concurrent inserts and updates requiring new space. This reduces contention for the segment header block, which contains information about the master free list of free blocks. For tables that do not have multiple free list groups, the segment header also contains information about free lists for user processes. You can use free list groups to locate the data that an instance inserts and accesses frequently in extents allocated to that instance.
Data partitioning can reduce contention for data blocks. Often the PCM locks that cover blocks in one free list group tend to be held primarily by the instance using that free list group, because an instance that modifies data is usually more likely to reuse that data than other instances. However, if multiple instances take free space from the same extent, they are more likely to contend for blocks in that extent if they subsequently modify the data that they inserted.
instance_number modulo number_of_free_list_groups
Note: "Modulo" (or "rem" for "remainder") is a formula for determining which free list group should be used by calculating a remainder value. In the following example there are 2 free list groups and 10 instances. To determine which free list group instance 6 will use, the formula would read 6 modulo 2 = 0. Six divided by 2 is 3 with zero remainder, so instance 6 will use free list group 0. Similarly, instance 5 would use free list group 1 because 5 modulo 2 = 1. Five is divisible by 2 with a remainder of 1.
If there are more free list groups than MAXINSTANCES, then a different hashing mechanism is used.
If multiple instances share one free list group, they share access to every extent specifically allocated to any instance sharing that free list group.
In a massively parallel system, however, MAXINSTANCES could be many times larger than FREELIST GROUPS so that many instances share one group of free lists.
See Also: "Associating Instances, Users, and Locks with Free Lists" .
If a table has multiple free lists but does not have multiple free list groups, or has fewer free list groups than the number of instances, then each free list is shared by user processes from different instances.
Figure 11 - 7 shows multiple extents in separate files. The GC_FILES_TO_LOCKS parameter allocates 10 locks to files 8 and 10, and 10 locks to files 9 and 11. Extents A and C are in the same free list group, and extents B and D are in another free list group. One set of PCM locks is associated with files 8 and 10, and a different set of PCM locks is associated with files 9 and 11. You do not need separate locks for files which are in the same free list group (such as files 8 and 10, or files 9 and 11).
Figure 11 - 7. Extents and Free List Groups
See Also: "Setting GC_FILES_TO_LOCKS: PCM Locks for Each Datafile" .
Note: False pinging will not be eliminated.
See Also: "Pre-allocating Extents (Optional) .
Remember that locks are owned by instances. Blocks are allocated on a per-instance basis--and that is why they are allocated to free list groups. Within an instance, blocks can be allocated to different free lists.
Using this method, you can either explicitly allocate the !blocks value, or else leave the balance of new blocks still covered by the existing PCM lock. If you choose the latter, remember that there still may be contention for the existing PCM lock by allocation to other instances. The blocks returned may be covered by a number of PCM locks.
See Also: "Dynamically Allocating Extents" .
Consider the following example, in which there are 4 blocks per lock (!4). Locks have been allocated before the block content has been entered. If we have filled datablock D2, held by Lock 2, and then allocate another range of 4 blocks, only the number of blocks which fits within the lock boundary will actually be allocated: in this case, blocks 7 and 8. Both of these are protected by your current lock. With the high water mark at 8, when instance 2 allocates a range of blocks, all four blocks 9 to 12 are allocated, covered by lock 3. The next time instance 1 allocates blocks it will get blocks 13 to 16, covered by lock 4.
Figure 11 - 8. A File with High Water Mark Moving as Blocks Are Allocated
The example in this section assumes that GC_FILES_TO_LOCKS has the following setting for both instances:
GC_FILES_TO_LOCKS = "1000!5"
With the EACH option specified, each file in file_list is allocated #locks number of PCM locks. Within each file, !blocks specifies the number of contiguous data blocks to be covered by each lock.
Figure 11 - 9 shows the incremental process by which the segment grows:
Figure 11 - 9. Allocating Blocks Within an Extent
Copyright © 1996 Oracle Corporation.
All Rights Reserved.