|
| sequence_cache_entries | 1 April 1999 |
| I am getting gaps in my sequences. I believe this is due to an insufficient value for sequence_cache_entries. How should this parameter be set? Does increasing this parameter reduce the space in the shared pool that is available for other objects? How much space does each entry use? | ||
 | This parameter is actually redundant from version 7.3. It has had absolutely no effect since the sequence cache was moved into the library cache. The same applies to the hash buckets parameter. You can set them to umpteen zillion and it will not take a byte of memory. Instead, make sure that you mark your sequences for keeping in the library cache with DBMS_SHARED_POOL.KEEP. |
|
| max_dump_file_size | 12 July 1999 |
| I was tracing one SQL statement when I ran out of space on the trace file. If I do a SHOW PARAMETER MAX_DUMP_FILE_SIZE I get 10240, but the actual file size is equal to 5 Megabytes. What is the relationship between 10240 and 5 meg? If I want to have a bigger trace file, what parameter do I have to change? Is it specified in bytes or blocks? | ||
| max_dump_file_size is the correct parameter. The value is in 512-byte blocks on your platform. (I think it is always the same as the operating system specific log block size). The parameter can be set in the init.ora file, or with the ALTER SESSION and ALTER SYSTEM statements (except on NT due to a bug). You can use the special value of UNLIMITED if you want to create a big trace file. |
|
| processes parameter | 21 July 1999 |
| We need to increase the processes parameter. What are the pros and cons of a high value? | ||
|
In Oracle8, there is no negative impact from having too high a processes parameter,
other than the bit of memory that is wasted, which is a few K per slot.
Prior to version 7.3 however, it affected the default value of db_file_multiblock_read_count,
which could bias the optimizer towards or against full table scans.
The formula was:
floor(4 * db_block_buffers / ceiling( processes / 4)) |
|
| _messages | 24 September 1999 |
| I found a paper on the Web on database tuning. It said to reduce my rdbms ipc message waits I need to increase my _messages parameter. Now the $64 question, What table do I look in to see what it is currently set to? My databases are in Oracle 7.2.3 and there is no name like that in the V$PARAMETER table. | ||
|
Up to 7.2.3 (only) you can check for the current value with:
select * from x$ksppi where ksppinm = '_messages'; I think the default was just 2 * processes (despite the comment in X$KSPPI). However, the recommendation is dubious. IPC message waits are routine and having insufficient message buffers will neither reduce the number of waits nor their duration. If a process cannot allocate a message buffer, it will wait for up to 60 seconds on a rdbms ipc message block wait event, and then give an error like this: KSAAMB: fatal error out of msg blks after 60 sec timeout If you get such an error, or message block waits, then and only then would there be justification in increasing the value of the parameter. However, this is most unlikely unless you are doing heavy parallel query work spanning multiple instances of an OPS database. |
|
| sequence_cache_entries | 5 October 1999 |
| I am getting confusing thoughts about sequence_cache_entries. Here is an article from another web site that talks about setting this parameter. But your answer from 1 April says that sequence_cache_entries does not have an impact on SGA size or memory. My 8.0.5 Oracle documentation still lists this as a parameter. I am confused. | ||
| That article is out-of-date and so is the documentation. The parameter was not obsoleted until 8.1 but has been redundant since 7.2. There is no dedicated sequence cache anymore. The sequence numbers are cached in the library cache from 7.3 onwards. This is why sequence numbers can be lost in a flush. |
|
| Other multiblock read parameters | 4 November 1999 |
| Regarding the last paragraph of your tip on multiblock reads, can the maximum effective value for db_file_direct_io_count, sort_multiblock_read_count and hash_multiblock_io_count be determined like was done in the multiblock_read_test.sql script? What is your tuning philosophy for these parameters? | ||
| There are no additional restrictions on the direct I/O count. However, a large sort multiblock read count reduces the merge breadth for the merge phase of a disk sort, which can cause you to need an extra merge phase, and if so that's bad news. By analogy, a large hash multiblock I/O count reduces the number of partitions than can fit into the hash area size, and can cause you to need a second hash join iteration. |
|
| Default parameter values | 17 November 1999 |
| I've found that about 20 parameters (such as _enqueue_hash, _messages and cache_size_threshold) have a value of FALSE in X$KSPPSV.KSPPSTDF indicating that the default value for the parameter has been "over-ridden", when in fact they have not. | ||
| The default values for these parameters are derived from others. They have KSPPSTDF = FALSE because the parameters on which they are based are not defaulting. Note also that there is some non-trivial operating system dependency (read inconsistency) here, particularly with the cpu_count derived parameters. |
|
| Recursive sessions | 24 February 2000 |
| Your fixed_table_hwms.sql script shows that the high-water mark for sessions is often up to twice the high-water mark for processes (or even more). The database connections are dedicated. The Oracle documentation explains that the value for sessions needs to be greater than the value for processes to allow for recursive sessions. What is a recursive session, and when does Oracle create one? Are recursive sessions the reason for the difference in the high-water marks? | ||
|
Recursive sessions are used for lots of things, but they are normally short lived.
They are used whenever something needs to be done with a different identity than that of the user session.
Examples are executing a procedure, auditing an action, making data dictionary changes, and so on.
I think that in older versions of Oracle, the recursive sessions for statement auditing were persistent.
If the high-water marks of processes and session are not of the same order of magnitude then the difference is probably not due to recursive sessions. It is probable that your application is developed with Forms or OCI and explicitly establishes multiple sessions for each process. If so, you should see the same ratio between the current number of sessions and processes as between the high-water marks. |
| Copyright © Ixora Pty Ltd |
|