Hp Unix,Oracle and Jbase

Hi friends,



T24 implementation is going on our environmnet. T24 Application(R07) and JBASE 5.0.4 is configured on Itanium HP Unix server (HP Unix 11.23).We are using Oracle 10g as database and it is also running on HP Unix 11.23. Here we are facing very poor performance.For improving the performance any special settings has to be done in the servers (Patch sets, Kernel Parameter etc........) , Oracle, jbase etc........



Thanks in advance .

View this long thread from jBASE google group.


EB.PHANTOM

Thanks for your Response.

I have gone through the jbase google group, but no where it is mentioned about the reason and the solution regarding the slow performance of t24.

A - KEY PARAMETER FILE SETTINGS IN ORACLE
The following is a list of the key parameters and their adjusted settings that should be used for a database installation for use with the GLOBUS application. The sizings are based on Benchmarking settings and may need to be adjusted for smaller installations.







Misc Settings



Recommended Setting



db_block_size


=


4096



Specifies the size (in bytes) of Oracle database blocks. Typical values are 2048, 4096 and 8192. The value for DB_BLOCK_SIZE in effect at the time you create the database determines the size of the blocks. The value must remain set to its initial value.







db_file_multiblock_read_count


=


16



One of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table; the multiblock read count, and whether parallel execution is being utilized for the operation.



Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.



The maximum value is always less than the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum. Dynamic, either alter session or alter system.







query_rewrite_enabled


=


TRUE



Allows query rewrite globally across the database.

Values:







True


= Oracle costs the query with rewrite and without rewrite and chooses the method with the lower cost.



False


= Oracle does not use rewrite.



Force


= Oracle always uses rewrite and does not evaluate the cost before doing so. Use force when you know that the query will always benefit from rewrite and when reduction in compile time is important.







Query_rewrite_integrity


=


TRUSTED



Determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.







Enforced


= Oracle enforces and guarantees consistency and integrity.



Trusted


= Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle.



Stale-tolerated


= Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.







Compatible


=


9.2.0.1.0



Allows you to use a new release, while at the same time guaranteeing backward compatibility with an earlier release. This is helpful if it becomes necessary to revert to the earlier release.



This parameter specifies the release with which the Oracle server must maintain compatibility. It allows you to take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality in your environment. Some features of the current release may be restricted.







star_transformation_enabled


=


FALSE



Determines whether a cost-based query transformation will be applied to star queries.







True


= the optimizer will consider performing a cost-based query transformation on the star query.



False


= the transformation will not be applied



Temp-disable


= the optimizer will consider performing a cost-based query transformation on the star query but will not use temporary tables in the star transformation.







Fast_start_mttr_target


=


0



Enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. The range of this parameter 0 to 3600 seconds Oracle:



Is overridden by FAST_START_IO_TARGET

Is overridden by LOG_CHECKPOINT_INTERVAL







undo_management


=


AUTO



Undo management specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.

Real Application Clusters: Multiple instances must have the same value.







undo_retention


=


5



Undo retention specifies (in seconds) the amount of committed undo information to retain in the database. You can use UNDO_RETENTION to satisfy queries that require old undo information to rollback changes to produce older images of data blocks. You can set the value at instance startup.



The UNDO_RETENTION parameter works best if the current undo tablespace has enough space for the active transactions. If an active transaction needs undo space and the undo tablespace does not have any free space, then the system will start reusing undo space that would have been retained. This may cause long queries to fail. Be sure to allocate enough space in the undo tablespace to satisfy the space requirements for the current setting of this parameter.







undo_tablespace


=


UNDOTBS1



UNDO_TABLESPACE specifies the undo tablespace to be used when an instance starts up. If this parameter is specified when the instance is in manual undo management mode, an error will occur and startup will fail.



If the UNDO_TABLESPACE parameter is omitted, the first available undo tablespace in the database is chosen. If no undo tablespace is available, the instance will start without an undo tablespace. In such cases, user transactions will be executed using the SYSTEM rollback segment. You should avoid running in this mode under normal circumstances.



You can replace an undo tablespace with another undo tablespace while the instance is running.

Dynamic: ALTER SYSTEM System Resources







open_cursors


=


300



OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. This parameter also constrains the size of the PL/SQL cursor cache, which PL/SQL uses to avoid having to re-parse as statements are re-executed by a user.



It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another.

Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.







job_queue_processes


=


10



JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999).

Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package.



Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.







session_cached_cursors


=


0



SESSION_CACHED_CURSORS lets you specify the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor.



Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.



Dynamic: ALTER SESSION







Processes


=


150



PROCESSES specify the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.







dml_locks


=


24220



A DML_LOCK is a lock obtained on a table that is undergoing a DML operation (insert, update, delete). DML_LOCKS specifies the maximum number of DML locks--one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users were modifying data in two tables, then six entries would be required.



The default value assumes an average of four tables referenced for each transaction. For some systems, this value may not be enough.



Enqueues are shared memory structures that serialize access to database resources. If you set the value of DML_LOCKS to 0, enqueues are disabled and performance is slightly increased. However, you cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE.

Oracle holds more locks during parallel DML than during serial execution. Therefore, if your database supports a lot of parallel DML, you may need to increase the value of this parameter. Static.







enqueue_resources


=


2440



ENQUEUE_RESOURCES sets the number of resources that can be concurrently locked by the lock manager. An enqueue is a sophisticated locking mechanism that permits several concurrent processes to share known resources to varying degrees. Any object that can be used concurrently can be protected with enqueues. For example, Oracle allows varying levels of sharing on tables: two processes can lock a table in share mode or in share update mode.



One difference between enqueues and latches is that latches do not entail an ordered queue of waiting processes, as do enqueues. Processes waiting for latches can either use timers to wake up and retry or (in multiprocessors) spin.



At database startup time, Oracle allocates the number of enqueues specified by the ENQUEUE_RESOURCES parameter. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and is usually adequate, as long as its value is greater than DML_LOCKS + 20. For three or fewer sessions, the default value is the number of database files + 20. For 4 to 10 sessions, the default value is the number of database files + ((SESSIONS - 3) * 5) + 20. For more than 10 sessions, it is the number of database files + ((SESSIONS - 10) * 2) + 55.



If you explicitly set ENQUEUE_RESOURCES to a value higher than DML_LOCKS + 20, then Oracle uses the value you provide. Oracle will automatically allocate additional enqueues from the shared pool as needed if the number specified by ENQUEUE_RESOURCES is exceeded. You can check resource usage by querying V$RESOURCE_LIMIT.







max_rollback_segments


=


1211



MAX_ROLLBACK_SEGMENTS specifies the maximum size of the rollback segment cache in the SGA. The number specified signifies the maximum number of rollback segments that can be kept online (that is, status of ONLINE) simultaneously by one instance.



Memory Settings

NOTE: Memory settings should be adjusted according to the amount of resource available to the oracle database.







java_pool_size


=


83886080



JAVA_POOL_SIZE specifies the size (in bytes) of the Java pool, from which the Java memory manager allocates most Java state during runtime execution. This memory includes the shared in-memory representation of Java method and class definitions, as well as the Java objects that are migrated to the Java session space at end-of-call.



If JAVA is not being used in the database this can be set to 1mb. The minimum recommended value is 1000000.



Static: LARGE_POOL_SIZE = integer [K | M | G]







large_pool_size


=


104857600



LARGE_POOL_SIZE lets you specify the size (in bytes) of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers. (Parallel execution allocates buffers out of the large pool when PARALLEL_AUTOMATIC_TUNING is set to true). Default Value: 0 if both of the following are true:



The pool is not required by parallel execution DBWR_IO_SLAVES is not set.



Otherwise, derived from the values of PARALLEL_MAX_SERVERS, PARALLEL_THREADS_PER_CPU, CLUSTER_DATABASE_INSTANCES, DISPATCHERS, and DBWR_IO_SLAVES.



Range of Values 300 KB to at least 2 GB (actual maximum is operating system-specific)

Dynamic: ALTER SYSTEM



Caution: When Oracle derives a default value, it adds 250K for each session for the shared server if DISPATCHERS is configured. The final derived value also includes a port-specific amount of memory for backup I/O buffers. The total derived default value can either be too large to allocate or can cause performance problems. In that case, set LARGE_POOL_SIZE to a number sufficiently small so that the database can start.







shared_pool_size


=


1073741824



SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures.



If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution message buffers from the shared pool. Larger values improve performance in multi-user systems. Smaller values use less memory.



Default Value:
32-bit platforms: 8 MB, rounded up to the nearest granule size
64-bit platforms: 64 MB, rounded up to the nearest granule size
You can monitor utilization of the shared pool by querying the view V$SGASTAT.







log_buffer


=


4194304



LOG_BUFFER specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.



In general, larger values for LOG_BUFFER reduce redo log file I/O, particularly if transactions are long or numerous. In a busy system, a value 65536 or higher is reasonable.

Default Value: 512 KB or 128 KB * CPU_COUNT, whichever is greater







pga_aggregate_target


=


25165824



PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance. You must set this parameter to enable the automatic sizing of SQL working areas used by memory-intensive SQL operators such as sort, group-by, hash-join, bitmap merge, and bitmap create.



Oracle uses this parameter as a target for PGA memory. Use this parameter to determine the optimal size of each work area allocated in AUTO mode (in other words, when WORKAREA_SIZE_POLICY is set to AUTO.



Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.



When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET. Dynamic: ALTER SYSTEM



Default Value: 0 (automatic memory management is turned OFF by default).







sort_area_size


=


256000



Specifies in bytes the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.



Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never occur. Only one memory area of SORT_AREA_SIZE exists for each user process at any time.



SORT_AREA_SIZE is also used for inserts and updates to bitmap indexes. Setting this value appropriately results in a bitmap segment being updated only once for each DML operation, even if more than one row in that segment changes.



Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.



The default is adequate for most OLTP operations. You might want to adjust this parameter for decision support systems, batch jobs, or large CREATE INDEX operations.



Note:

Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility.







db_cache_size


=


214748366



Specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE parameter).



The value must be at least the size of one granule (smaller values are automatically rounded up to the granule size). A value of zero is illegal because zero is the size of the DEFAULT pool for the standard block size, which is the block size for the SYSTEM tablespace.







sga_max_size


=


2147483648



Specifies the maximum size of SGA for the lifetime of the instance. Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on. This is a static value and must take into account the amount of resource available to the Oracle database.