Page 689
Based on the range of your transaction rates, you should be able to define which tables fall into which category. For example, if your busiest table has 100 INSERTs per second and 600 UPDATEs per second, it has a transaction rate of 700 operations per second. If your least volatile table has 40 operations per second, your transaction range is 660 operations (700_40). Dividing this into three equal ranges of 220 operations, you would classify them as follows:
For high activity tables, you should use tablespaces to which you assign either many hash locks or fine grain locks. If the activity is INSERT only, you should use multiple freelist groups on the table and index segments, and then assign either hash locks in conjunction with manually allocated extents or assign fine grain locks with a grouping factor in the GC_FILES_TO_ LOCKS parameter for the associated datafiles.
You should separate the medium and low activity tables into their own tablespaces. Although you may need to perform some fine-tuning later, you should choose two values for the number of hash locks you will use. The number for the high activity tables' datafiles should be 10 times the number used for the datafiles holding the low activity tables. For example, you might initially allocate one thousand PCM locks for high activity tables and just one hundred for low activity tables.
If you still need a large number of locks to cover the datafiles using the above the approach, you have other options to try to reduce them. First, you can use releasable locks for the datafiles belonging to the tablespaces for the medium activity as well as the high activity tables, or for all the tables in the Overlap column. Second, you can try a different partitioning scheme or apply an additional type of partitioning to the functional partitions you already have. You definitely have to use one of these techniques if you have more locks than your system can handle, or too many for it to be able to start up your instances in a reasonable amount of time.
NOTE |
You may also want to test your instance recovery time if you increase the number of PCM, and related DLM, locks. The greater the number of locks, the longer the DLM reconfiguration may take, particularly if an entire node fails, stopping both the instance and the DLM on that node. |
Department and Line of Business (LOB) partitioning works if you have groups of users who tend to access their own subsets of data even if they are in the same tables. To extend the human resources example from the previous section, the company may have a number of offices where the personnel are handled by a local human resource department, or there may be an HR group for native employees and another for international workers.
Page 690
You can use a different instance for each department or LOB if such natural partitioning exists. Due to the nature of this partitioning scheme, it is likely that the users on each instance will perform practically all of the DML, as well as the queries for their own data. Consequently, you should only need a few PCM locks for each shared table. The best way to accomplish this is to use multiple freelist groups and allocate extents for each instance in its own datafile or set of datafiles. You then only need to assign a very few hash PCM locks to each of these datafiles. In some cases, you might find that a single lock is all you need on some files, although if any of the rows are occasionally shared by other instances, this may not provide sufficiently good performance.
If you don't have any natural subsets of users and one or more critical tables that they all need to modify, you may need to resort to physical partitioning of the table. This is not as elegant a solution as departmental or LOB partitioning because you also have to control how the users connect to the database. With departmental or LOB partitions, the users are already working in different locations or different administrative units. A new user will belong to one of these groups and can be assigned to the appropriate instance. To enforce physical partitioning, you need a mechanism to ensure that a user connects to the correct instance, depending on what part of the data she needs to access.
An example of physical partitioning would be the creation of multiple employee tables instead of a single table. For a four-instance database, you might have a table for employees whose last names start with letters in the range A through E, a table for F through J, one for K through Q, and a fourth for R through Z. You would place these tables in separate datafiles, even in different tablespaces.
If your users can be partitioned along the same lines, that is, they work only on records for the subset of employees maintained in one of the four tables, you might be able to implement this scheme quite easily, because it is very similar to Department or LOB partitioning. The only major difference is when a query needs to report on all employees, or, at least, employees in more than one partition. Oracle8 solves this problem if you use its partitioning mechanism, although partitioning for parallel server in this manner is not its primary use. Otherwise, you need to maintain a UNION or UNION ALL view for such access.
If you cannot partition your users on the same criteria as your tables, you will need your application to route the user to the correct instance based on the data being processed. This may even require that the users make a new connection for each transaction so that they will always work on the desired instance based on the record of interest. Although this may well be impractical in a standard client-server architecture, it is possible to build applications around this model using three, or more, tier architectures.
In multi-tier architectures for Parallel Server, a Transaction Processing (TP) monitor sits between the users and the database server. This monitor controls the routing of the clients' SQL statements and returns information back to the users.