Page 790
The init.ora parameter, SORT_DIRECT_WRITES, determines the sorting behavior regarding using the database buffer cache or not. If set to AUTO, the default, and if SORT_AREA_SIZE >= 10 x Sort Direct Writes Buffer, the Sort Direct Write Buffer is used. If set to FALSE, sort writes are buffered in the database buffer cache before being written back out to disk. These are normal sort buffer writes. If set to TRUE, sort writes are always sort direct writes. VLDBs, DSSs, and Data Warehouses should normally have this set to TRUE (or at least left at the default AUTO).
The following are other fine-tuning parameters that impact sort performance:
SORT_READ_FAC is a ratio representing the amount of time to read one Oracle block divided by the block transfer rate. It must be <= DB_FILE_MULTIBLOCK_READ_COUNT. The formula to set it is
(average seek time + average latency time + block transfer time) / block transfer rate
This parameter takes into account operating system and hardware (disk) device characteristics for sort reads. However, what if you used mixed disks having, of course, mixed access times (access time = seek time + latency time)? Do you take the least common denominator approach and set this to the characteristics of the slowest disk?
CAUTION |
Unless you have a very homogeneous hardware (disk) configuration, and have intimate hardware expertise, I would advise you to not set this parameter. If done improperly, it can hurt much more than help. |
SORT_SPACEMAP_SIZE represents the size in bytes of the sort space map, which is a map per sort (per context) of the multiple sort run addresses. The formula to set it is
(total sort bytes / SORT_AREA_SIZE) + 64
total sort bytes requires that you need to know the size in bytes of the columns being sorted, multiplied by the number of rows. You can use the entire table size in bytes, but this will be an overestimate (not necessarily a bad thing).
Query the V$SYSSTAT view as follows:
SQL> SELECT M.VALUE / D.VALUE "Memory Sort Ratio" 2> FROM V$SYSSTAT M, V$SYSSTAT D 3> WHERE M.NAME = `sorts (memory)' 4> AND D.NAME = `sorts (disk)';
From report.txt, the Statistics section, compute the Memory Sort Ratio as the total of `sorts (memory)' divided by the total of `sorts (disk)'.
Page 791
NOTE |
Guideline: If either method of computing Memory Sort Ratio yields a ratio less than .95, you need to increase SORT_AREA_SIZE (and SORT_AREA_RETAINED_SIZE) by at least the deficit percentage, which is calculated as .95 minus Memory Sort Ratio. For example, if your Memory Sort Ratio is .78, your deficit percentage is .95 _ .78 = .17. This implies you need to increase your sort parameters by at least 17 percent.n |
This section, rather than rehashing the architecture of the MTS, offers a series of brief explanations and guidelines to help you tune your MTS configuration, especially regarding memory performance optimization. As a refresher, recall that the MTS relocates session user memory, the User Global Area (UGA), to the SGA shared pool, where it is shared by many sessions. Generally, you would use the MTS when you want to increase the throughput (for example, in terms of transactions per second) of a database system, and specifically when the system is under heavy (concurrent) load. In other words, MTS is intended for an OLTP system with many hundreds of concurrent users, at least greater than 200.
CAUTION |
If you were to use MTS for a system that is not as heavily loaded as described, the overhead of using MTS would outweigh its minimal benefits. That is, for lightly loaded systems, MTS can actually hurt performance. So, use MTS as advised mainly for heavily loaded OLTP systems. |
TIP |
Set MTS_SERVERS = 1/100 (the number of concurrent transactions), where the number of concurrent transactions equals the number of concurrent users times the number of transactions per user. Set MTS_MAX_SERVERS = 1/10 (MTS_SERVERS). Increase these in increments of magnitude. Similarly, set MTS_DISPATCHERS = MTS_SERVERS and MTS_MAX_DISPATCHERS = MTS_MAX_SERVERS. |
To monitor and tune shared servers, use the following:
SQL> SELECT BUSY / (BUSY+IDLE) "Shared Servers Busy" 2> FROM V$SHARED_SERVER; SQL> SELECT SERVERS_HIGHWATER 2> FROM V$MTS;
For dispatchers:
SQL> SELECT BUSY / (BUSY+IDLE) "Dispatchers Busy" 2> FROM V$DISPATCHER; SQL> SELECT SUM(WAIT)/SUM(TOTALQ) "Dispatcher Waits" 2> FROM V$QUEUE 3> WHERE TYPE = `DISPATCHER'; SQL> SELECT COUNT(*) "Number of Dispatchers" 2> FROM V$DISPATCHER;
Page 792
NOTE |
Guideline: If Shared Servers Busy is greater than .50, or SERVERS_HIGHWATER is close to or equal to MTS_MAX_SERVERS, increase MTS_MAX_SERVERS. If the Dispatchers Busy is greater than .50, the Dispatcher Waits is greater than 0, or the Number of Dispatchers is close to or equal to MTS_MAX_DISPATCHERS, increase MTS_MAX_DISPATCHERS. Usually, if you increase one, you should probably also increase the other.n |
As with the "Tuning the MultiThreaded Server (MTS)" section, this section won't go into much detail rehashing the software engineering and functional details of Oracle's locking mechanisms. (See Chapter 25, "Integrity Management," for the background information on Oracle locking.) Rather, you will look at some brief background and useful tuning advice. Locks in Oracle, as with any RDBMS, are memory-based structures. Like latches, they are made up of two basic logical structures: a gate and a queue. If you have only the gate, you have a latch. If you have a gate and a queue, along with a queuing process, you have a lock.
The central goals of tuning are to not have unnecessary waiting, not experience deadlock, and not actually run out of locks during critical processing. The granularity of Oracle's locks is row level. That is, for INSERT, UPDATE, and DELETE statements, locks are held at row level by default. SELECT statements (queries) hold no locks by default, unless explicitly specified in the code. DDL also holds locks, but the ability to performance-tune them is limited; so instead, let's concentrate on the dominant issue of performance-tuning DML locking.
NOTE |
Transactions hold locks until they commit or roll back.n |
Locks may cause unnecessary waits as a direct result of poor application coding with regard to locking. Unnecessary lock waits may be caused by coding locks at unnecessarily high granularityfor example, locking a table when you need only a row. If the code involves unnecessarily long transactions, this will cause undue lock waits. An example is a persistent client/server connection held for the duration of a transaction. Many times, this type of coding can be chopped into two or more transactions of shorter duration. Finally, if code is not committed (or rolled back) on a regular basis, the locks remain unduly held. When coding, ensure the following: