Release 2 (9.2.0.1.0) for OS/390
Part Number A97313-01
 Contents |
 Index |
| Oracle9i Enterprise Edition System Administration Guide Release 2 (9.2.0.1.0) for OS/390 Part Number A97313-01 |
|
The performance of your Oracle server depends on a number of factors. This chapter describes some of those factors and provides recommendations for improving and tuning your system to promote better Oracle performance.
The following topics are included:
Under the OSDI environment, an Oracle9i for OS/390 database server can be configured to use multiple address spaces. This greatly expands the amount of virtual memory available to support higher workload levels, especially in the form of additional users. The first address space that is created is known as the AS1 or Control Address Space. The additional address spaces are Auxiliary Address Spaces. After the Oracle instance startup is completed, the user sessions are evenly distributed among the configured address spaces, or regions, based on available virtual memory.
The Oracle regions will consume CPU resources as users communicate with the database service to establish or terminate a session and to perform other special activities belonging to the Oracle regions, such as such as opening and closing files, database file I/O, and network connections. Additionally, the Control Address Space owns the Oracle background tasks, which consume CPU resources when they are active.
Background tasks in the Control Address Space use small amounts of CPU resources to perform normal background processing, such as:
Typically, the Oracle regions consume very moderate CPU resources compared with those of the client regions. In a special case, however, the Oracle regions can use significant amounts of CPU resources. This situation occurs when a client uses the Oracle parallel execution feature. When this feature is used, the portion of database work that is parallelized (executed in parallel) will be performed by parallel execution slaves in the form of special subtasks within the Oracle regions, whereas the portion of work that is not parallelized (not executed in parallel) will be performed by the client task, as normally occurs. For details on how and where these parallel slaves are created, please refer to "Oracle Parallel Execution".
When executing database operations in normal mode (in other words, without parallel execution), most resources are charged to the client address space, whether it is a TSO, batch, CICS, IMS, or Oracle Net address space. The first four examples represent clients executing locally in the OS/390 image. The last example is the case of a remote user accessing the Oracle9i for OS/390 server from another node in a network by using Oracle Net services.
Under OSDI, the Oracle Net address space performs both the protocol related networking function and the actual, non-parallel-executed database work on behalf of the remote user.
Network clients are dispatched within the Net region as preemptable SRBs. An SRB is a lightweight OS/390 facility to dispatch processes. When used in conjunction with a new mechanism for prioritizing work, this scheme provides a very efficient and manageable means of execution.
Further, a preemptable SRB can be associated with an enclave, an artifice that assigns priority to a unit of work independent of any specific OS/390 address space or process. Multiple processes (both tasks and preemptable SRBs) can be executed on behalf of an enclave and are granted resources according to installation-defined goals for the associated work.
An OS/390 system component called Workload Manager (WLM) monitors work that is executed under this scheme, and dynamically adjusts task and preemptable SRB dispatching priorities and other factors to ensure that defined goals are met. Refer to "Dispatching Priority" for a detailed description of how to handle Oracle dispatching priorities for both local and remote clients.
A Net service under OSDI creates enclave SRBs for dispatching all network client requests. The CPU time associated with their database work is therefore attributed to the Net address space. Using WLM in goal mode, the Net region can run at a high dispatching priority to ensure good performance for the networking service, whereas the dispatching priority of the remote client database requests can be managed separately according to their own needs.
The Oracle server allocates nearly all storage areas above the 16M line. Only one small data area and a few load modules are allocated below the line. All other data areas, including the SGA, PGA, context areas, sort work areas, working storage, and most Oracle load modules are located above the 16M line. The sizes of these data areas depend on the settings of various INITORA and OSDI parameters and on the user workload.
An OS/390 exit called IEFUSI might be installed on your system. The IEFUSI exit prevents started tasks or batch jobs from getting the maximum region size when REGION=0M is specified. If an IEFUSI exit is implemented, it is specified in the SMFPRMxx member of SYS1.PARMLIB that is used during OS/390 initialization. To effectively run Oracle with an IEFUSI exit installed, ensure that the exit is coded to allow batch jobs or started tasks with the names of your Oracle regions to allocate a large amount of virtual memory above the 16M line.
Because Oracle allocates only the amount of memory it needs, you can safely allow Oracle to allocate any amount of memory up to the two gigabyte limit per address space that is imposed by 31-bit addressing conventions.
An excellent method for reducing the real storage requirements for Oracle is to place reentrant modules in OS/390 link pack areas. Most Oracle modules are link-edited with AMODE set to 31 and RMODE set to ANY. Place such modules in the extended pageable link pack area (EPLPA) above the 16M line. Some modules are linked with RMODE set to 24. Place them in the pageable link pack area (PLPA) below the 16M line. Current versions of OS/390 automatically load modules from the link pack area libraries into the appropriate link pack areas.
Keep in mind that under the current OS/390 implementation, placing modules into LPA reduces the private area of every address space in the system. In other words, there is a trade-off between code sharing and virtual memory availability.
Only the subsystem code module (ORASSI) is automatically shared, and it is shared by all Oracle subsystems and services.
The Oracle database and Net regions run different programs from the Oracle AUTHLOAD library. Each Oracle database address space has its own copy of ORARASC and a few other modules, and the Net address space has ORANET8 and several others modules as well. There is no sharing of this code, even between address spaces of the same database service. Due to an operating system restriction, you cannot put ORARASC into LPA -- doing so makes it impossible to run any other copy of ORARASC (for example, an ORARASC at a different maintenance level), whether from LPA or not. ORARASC is also quite small in size, so it is not necessary for it to be shared. The Net modules, such as ORANET8, are also quite small in size. In addition, because a single Net service can be used to access multiple database services, typically only one Net service will be deployed. As a result, sharing of Net code is also typically unnecessary.
If you are running multiple Oracle database regions (from either the same or different instances), an excellent candidate for LPA usage is the Oracle kernel (in other words, the ORACLE module), because it is quite large in size. As described above, prior consideration should be given to the impact on any non Oracle workloads that may be constrained by virtual storage.
If your installation will run multiple concurrent local users, you can place the following modules from the Oracle CMDLOAD into the link pack:
LIBCLNTS contains the interface routines for all Oracle client accesses. This includes tools, utilities, precompilers, precompiler applications and Access Managers. LIBCLNTS is an excellent candidate because of it's relatively large size. Place it in the EPLPA above the 16M line.
SQLPLUS is the primary Oracle batch and interactive SQL processor. It is used for user database queries, updates, table creates and drops, and so forth. In many systems, this module is used heavily enough to warrant link pack area placement. Place it in the ELPA.
Do not place other load modules from the Oracle libraries into the link pack area, because they are referenced infrequently, link edited RMODE 24, or not reentrant.
The Oracle server makes static-fixed, static-variable, and dynamic virtual memory allocations as the Oracle regions are started up and begin providing database services to users. Static-fixed memory allocations are storage areas that are always allocated in the regions including space for the Oracle load modules, working storage, and OS/390 data areas. Static-variable memory -- mainly the SGA -- differ from one warm start of the server to the next, depending on initialization parameter values. Dynamic memory allocations occur as users connect to the instance and access information that is stored in the server. The primary factors determining the number of concurrent Oracle users that can be supported under OS/390 are the user memory allocation requirements (depending on the application design), the INITORA and OSDI parameter values, the amount of virtual memory that Oracle regions are allowed to allocate, and the amount of central storage that is available for use by the Oracle regions.
Exhausting virtual memory in an address space will lead to any of a number of types of failures, because it is impossible to predict which system activity requests for memory are going to be denied.
This scenario is best avoided by configuring a database instance with enough address spaces to contain the largest expected workload in terms of memory required. Doing this requires an understanding of the workload as well as of the database address space topography on OS/390.
In addition to carefully configuring server address spaces, you can use certain database region parameters that provide controls designed to reduce the likelihood of exhausting address space memory. These are discussed in "Limiting Sessions in a Server Address Space", and "Limiting Memory Allocations in a Server Address Space".
Each database address space starts out with a given amount of private virtual memory: 2048 megabytes less the memory that is used or reserved by OS/390 for shared access by all address spaces: SQA, CSA, LPA, and the OS/390 nucleus and related data. The sizes of these spaces, and thus the amount of private memory remaining in each address space, varies from one OS/390 system to another. You may need to consult with your systems staff to determine the available private area size on your system.
Once you know the private area size of your system, you must subtract from it the amount of memory that will be allocated in each address space for purposes other than Oracle sessions. This memory allocation includes the Oracle SGA, the Oracle kernel -- usually named ORACLE -- and other minor load modules (ORARASC, ORARSSRB, ORADIE), and the load modules and data structures of the OS/390 Oracle infrastructure, including the IBM Language Environment (LE) interface. The size of the Oracle SGA is determined primarily by parameters that you specify in the INITORA file and is displayed during Oracle startup. (For additional discussion of the SGA, refer to section "The Oracle SGA on OS/390".) The size of the Oracle kernel and other modules can be determined using ISPF browse on the load library that contains it.
After the foregoing are subtracted, the remaining private memory in each server address space is available for Oracle sessions. The maximum amount of memory that is required by a given session depends mainly on the behavior of the application: the number of cursors opened, the specific SQL statements used, PL/SQL and/or Java requirements (if any), and so forth. In addition, there are several INITORA parameters (SORT_AREA_SIZE, HASH_AREA_SIZE, and so forth) and a database region parameter (INIT_STACK_SIZE) that affect the memory resources that are allocated during each session execution. This can be quite difficult to estimate in advance of running the application. The most reliable way to determine memory requirements is to review the Oracle session SMF records (which contain a session memory high-water mark) and analyze them to determine the average peak session memory. For more information on Oracle SMF records refer to "Interpreting an Oracle SMF Record".
Once you know the average session memory requirement you can calculate the number of sessions that will fit in one address space as:
where:
|
N |
is the desired result |
|
P |
is the available private memory per address space |
|
S |
is the average peak session memory |
If we let T be the total number of concurrent Oracle sessions to be supported then you need (T / N) server address spaces. This number should be rounded to the next higher whole number, and to allow for reasonable variability in workload level, it may be advisable to add one more address space. In doing so, keep in mind that an SQA cost is associated with starting additional address spaces, which is discussed in the section "The Oracle SGA on OS/390".
Oracle Corporation recommends that you specify the number of address spaces calculated here as the INIT_ADR_SPACES parameter (so the address spaces all start when the service is started). A somewhat higher number can be specified as MAXAS (maximum address spaces) on the DEFINE SERVICE command. This makes it possible to start additional address spaces dynamically if the initial estimate proves to be low. There is no cost for having additional address spaces in the MAXAS parameter until those address spaces are actually started. Note that MAXAS must be equal to or greater than INIT_ADR_SPACES. Care should be taken to specify a high enough value to accommodate unpredictable workload growth or spikes.
Note that using more than one address space results in the Oracle server becoming a "cross-memory address space" in OS/390 terms. These address spaces are not available for reuse when the Oracle server terminates. The OS/390 PARMLIB parameter RSVNONR specifies the number of address space numbers to reserve for use as they become unavailable. If you use multiple address spaces for the Oracle server, then you should increase the value specified for this parameter. Specifying too small a value, or letting RSVNONR take the default value could result in an unscheduled IPL if the number of address space IDs becomes exhausted. For example, you could stop the Oracle server for maintenance and then become unable to restart without an IPL. For more information, refer to the OS/390 Initialization and Tuning Reference, SG28-1752.
An Oracle server instance has a single System Global Area (SGA) regardless of the number of address spaces or regions configured. The Oracle SGA is shared across all of regions of a server using an OS/390 service called IARVSERV that allows one address space to "view" a range of private virtual memory that belongs to another address space. The Oracle SGA belongs to the first server address space (primary region) and is viewed (shared) by any other regions that are configured for that server. The virtual address range of the Oracle SGA must be reserved in each of the auxiliary regions to support the viewing mechanism. This is why the Oracle SGA size is subtracted from the private area size in every server address space of a given instance (not just the primary region) in the memory calculations of the previous section, "Determining the Number of Oracle Address Spaces".
Sizing the Oracle SGA and, most significantly, sizing the database buffer cache and the shared pool, are important instance tuning activities. The numerous INITORA parameters that do this and the general considerations for specifying their appropriate values are covered in the Oracle9i Database Reference and the Oracle9i Database Performance Book Set. A few guidelines for configuring some critical INITORA performance parameters are also presented in section "INITORA Parameters". The following paragraphs describe some OS/390-specific issues to be aware of when tuning the Oracle SGA.
Because the SGA is not permanently pagefixed on OS/390 as it is on some other systems, there is little benefit in reserving SGA expansion space with the SGA_MAX_SIZE parameter. When you specify SGA_MAX_SIZE, the indicated maximum size is reserved (in virtual memory) in all server address spaces even if it is not all used.
Keep in mind that the Oracle SGA is mapped in all of the server address spaces of a given instance as discussed above. This means that increasing a server's SGA size reduces the virtual memory available for Oracle sessions in every server region for that instance. If you do this, you may need to increase the number of regions in order to support your peak workload. The relationship is not linear. A 25% increase in SGA size may require more than a 25% increase in the number of server regions. When you make a significant change in the Oracle SGA size, repeat the calculations described in the previous section to determine the number of server address spaces that you need.
Another factor in Oracle SGA sizing is the overhead of the IARVSERV memory sharing mechanism. Currently, OS/390 must reserve 32 bytes of ESQA (Extended System Queue Area) for each "view" of each 4K page of memory shared. SQA is an expensive resource because it is page-fixed (always backed by real memory) and because it is globally addressable, using up an address range that would otherwise be part of the private area of each address space. Exhausting OS/390 SQA is a situation best avoided, so you should calculate the SQA overhead for your Oracle SGA and discuss this with your OS/390 systems staff before attempting to start the server.
As an example, an Oracle server configured to run in 10 address spaces with a 512 megabyte SGA requires
or 40 Megabytes, of SQA, a significant amount.
Each Oracle server user requires some extent of private memory to be used as a save area during normal execution. This area is known as the user stack.
When a user session is initiated, the connection is routed to a particular Oracle region. This region will then acquire a stack area based on the INIT_STACK_SIZE parameter. If the user requires more stack, additional extents are dynamically allocated and freed when they are no longer required. The actual stack requirement is dependent on the type of database call being used (SQL, PL/SQL, Java, OCI) and its complexity.
To fine tune this parameter, you might want to use the Oracle session SMF records analysis method described in the "Database Server Address Space Configuration" section (that was presented earlier in this chapter), or you can run your workload by varying the INIT_STACK_SIZE settings and then comparing the CPU usage of the various tests. The lower the INIT_STACK_SIZE value, the higher the potential CPU overhead that is caused by dynamic stack expansion.
As a starting point, you can use the Oracle Corporation recommended minimum value of 128K.
The MAX_SESSIONS parameter also plays a role in managing virtual memory use. This is a hard limit on the number of sessions that can be active in one server address space, and it defaults to 1024 sessions. If a new bind (client connection) is routed to an address space that is at the MAX_SESSIONS limit, the server waits until some existing session unbinds (disconnects) before accepting the new session.
The idea behind MAX_SESSIONS is to keep the address space from accepting so many sessions that virtual memory is exhausted and unpredictable failures occur. The assumption is that it is better not to let an application connect and get started than to let it connect and incur transaction failure partway through its processing. A good value to use for MAX_SESSIONS is the value N (maximum sessions per address space) that was calculated in the section "Database Server Address Space Configuration".
Both INITORA and database region parameter values must be set high enough to allow the required number of users to connect to the server. Please refer to the discussion about the INITORA PROCESSES parameter in section "PROCESSES".
Two more database region parameters provide additional control over memory consumption in a server address space. The MAX_SESSION_MEM parameter allows you to impose a limit on the total virtual memory allocated to any single session in the Oracle server. This applies to all session-private memory requests made by the server, including the C stack and "heap" areas.
The limit is imposed on all sessions, including background processes and even parallel query slaves. If a session requests memory that would take it over the limit, the session receives an error (usually an ORA-04030) and the current transaction is rolled back.
Care should be taken not to choose too small a session limit. STARTUP processing in the current Oracle release requires about 10 megabytes of session memory. Note that session memory usage is reported in the Oracle SMF record, which can be used to help determine an appropriate limit amount.
Another parameter, REGION_MEM_RESERVE, allows you to limit the total memory allocated to all sessions and the Oracle SGA in an address space before exhausting address space private area. The "reserve" amount you specify remains available for internal implementation and OS/390 system function use.
Specifying an adequate reserve amount prevents the situation of exhausting address space memory and significantly reduces the impact of memory consumption problems. Requests for memory that would exceed the aggregate limit are rejected, resulting in an error and transaction rollback in the affected session.
The amount of real storage ("working set size") that is required by the Oracle regions is very workload dependent, varying significantly with transaction complexity and rate, user concurrency, and locality of program and data reference. A lightly-loaded instance might require only 50K of working set for every megabyte of virtual memory that is allocated, while an instance that is supporting a much higher workload might require 750K of working set for every megabyte of virtual memory that is allocated. In general, Oracle load modules and the SGA remain in central storage while the context areas, sort work areas, and other individual session-related areas are more likely to be paged out to expanded or auxiliary storage when they are not heavily used.
The amount of virtual memory that is consumed by the server regions may vary significantly at run time because memory usage levels are very dynamic and fluctuate according to user workload. This is especially true when users connect and/or disconnect frequently or when users execute applications that open and close a large number of cursors, and when sorts are performed. Region size limitations can therefore become important, even with a small number of users connected to the instance. A region size limit that is too small prevents users from connecting to the server or from accessing information. Oracle Corporation recommends that you allow the server to allocate as much virtual memory as required and that you avoid imposing any region size limitations on the software. Other OS/390 facilities can be used to control the amount of central and expanded storage that is used by the server. The easiest way to allow Oracle9i to use the maximum amount of virtual storage is to specify the REGION=0M keyword parameter on the EXEC statement in the region startup JCL. Refer to "IEFUSI Exit".
This section deals with methods of optimizing your Oracle performance under OS/390. Before tuning your Oracle server specifically for running on OS/390, you should tune it for optimal performance independent of the operating system. For information on tuning methods that are independent of the operating system, refer to the Oracle9i Database Performance Book Set.
Certain INITORA parameters can be used to optimize Oracle performance. Some of these parameters are discussed here, including:
Oracle compares SQL statements and PL/SQL blocks that are issued directly by users and applications, as well as recursive SQL statements that are issued internally by Oracle. If two identical statements are issued, then the SQL or PL/SQL area that is used to process the first occurrence of the statement is shared. This means that it is used for the subsequent executions of all identical statements (no matter which users they belong to), thus reducing memory usage and improving performance.
To promote or maximize statement reusability and shareability, different execution arguments can be passed at run time by using bind variables. But even with bind variables, two SQL statements that are doing exactly the same job may not be considered identical. In addition to being identical, character-by-character in the SQL text, the bind variables in the SQL statements must match in name and data type. For example, the following two statements cannot use the same shared SQL area:
SELECT * FROM emp WHERE deptno = :department_no; SELECT * FROM emp WHERE deptno = :d_no;
Generally, therefore, only truly identical statements can be shared. However, non-identical statements that are considered "similar" can also share SQL areas when the CURSOR_SHARING parameter is set to FORCE, frequently solving shared pool memory allocation problems and minimizing statement parsing overhead. Two separate occurrences of a SQL statement or a PL/SQL block are considered "similar" and can use a shared SQL area if they differ only in the literals. For example, the following two statements are considered similar:
INSERT INTO T VALUES(1, 'foo', 4) INSERT INTO T VALUES(2, 'bar', 7)
The CURSOR_SHARING parameter may solve some performance problems. It has the following values: FORCE, SIMILAR, and EXACT (default). Setting CURSOR_SHARING to FORCE or SIMILAR will force similar statements to share SQL by replacing literals with system-generated bind variables. Replacing literals with bind variables improves cursor sharing with reduced memory usage, faster parses, and reduced latch contention.
The size of the buffer cache is one of the most significant tuning adjustments controlling Oracle performance. Although these tuning parameters are very dependant on the application, the following two guidelines are for adjusting buffer cache size:
The first guideline is easy to follow. The Oracle9i Database Performance Book Set describes a method for estimating the optimal number of buffers for an instance. Alternatively, you can simply increase the DB_CACHE_SIZE parameter value until you get the preferred hit ratio while your user applications are running.
The second guideline establishes limits for the size of the buffer cache. As Oracle memory usage increases, paging and swapping rates might increase as well, until OS/390 is so busy paging that it cannot complete any useful work. In general, maximum throughput first increases, then decreases, as the buffer cache size is increased. Throughput increases as the cache hit ratio improves, but decreases as Oracle uses too much memory and as the system paging rate goes up. These two trends balance at a point of optimal performance that is different for every Oracle instance and for every OS/390 installation.
Because the buffer cache is an architectural feature for avoiding disk I/O operations, you can, to some extent, trade off cache size for I/O rate. An instance with a small cache and a high performance I/O environment might perform as well as an instance with a large cache and a poorly performing I/O environment. Disk environments supported by storage processors with ample cache and high DASD cache hit rates can provide superior I/O performance that can alleviate some of the performance penalty of low buffer cache hit rates. On OS/390 systems with a lot of I/O capacity but with little central and expanded storage available, concentrate on obtaining optimal Oracle I/O performance by adjusting the layout of the database files to minimize device and path contention, and to maximize DASD cache hit rates. On systems with adequate memory, you can increase the size of the buffer cache to achieve an 85 to 95 percent hit ratio.
Be aware that Oracle9i performs both full and incremental checkpoints, and therefore, care should be exercised to properly size ALL log files. The size of the smallest redo log is one of the key factors in determining the incremental checkpoint frequency. Ensure, therefore, that all log files are evenly sized so that checkpoint overhead can be effectively controlled. Also, fewer (but larger) log files are preferred to many (but smaller) log files.
To minimize checkpoint activity, set LOG_CHECKPOINT_INTERVAL to a large value, such as 5,000,000. This value should not be lower than the number of 4K blocks in your largest log file. Also set both FAST_START_IO_TARGET and FAST_START_MTTR_TARGET to zero to reduce incremental checkpoints to a minimum. This way, checkpoint overhead is practically limited to those times when LGWR switches from one log file to the next. This might, however, increase database recovery time by requiring more data to be processed upon recovery. For more information about minimizing checkpoint activity, refer to the Oracle9i Database Performance Book Set.
|
Note: In Oracle9i, the LOG_CHECKPOINT_INTERVAL and FAST_START_IO_TARGET parameters have been deprecated in favor of the FAST_START_MTTR_TARGET parameter. |
This parameter defaults to a value of 25,165,824 bytes, or 24M. If you do not use Java at the Oracle kernel level, then you can reduce your SGA size considerably by specifying the minimum value, 0.
The PROCESSES parameter needs to be set as accurately as possible to get the best performance from your instance. Many internal operational values that control Oracle performance are derived from the PROCESSES value. You can monitor the actual use of this and other Oracle resources to fine-adjust the SGA size and to optimize its performance by querying the V$RESOURCE_LIMIT view.
In general, if [the database region parameter MAX_SESSIONS] multiplied by [the number of Oracle address spaces started] is greater than [the PROCESSES parameter value], then you will never hit MAX_SESSIONS, because the instance specified PROCESSES limit will be reached first. When this occurs, any attempt to create a new session will immediately receive an Oracle kernel error message indicating that there is no more room for sessions. It will then be the user's responsibility to reschedule those requests for later. On the other hand, if PROCESSES is greater than the OSDI controlled limit (the number of address spaces times MAX_SESSIONS), an Oracle region might hit MAX_SESSIONS. If this happens, any additional session request will be put on hold without getting an error. In this case, the server waits until some existing session unbinds (disconnects) and then automatically accepts the new session.
From the perspective of virtual memory allocation, all of the user infrastructure at the OSDI level, such as LE context, is created for each session when it is first used, and the session ID translation table is allocated dynamically as needed. However, the INITORA PROCESSES, SESSIONS, and TRANSACTIONS parameters do cause resources to be statically allocated in the SGA, and the PMON background process scans the whole list many times during each housekeeping interval. The best strategy is therefore to keep INITORA PROCESSES small and to set MAX_SESSIONS appropriately, depending on the desired effect.
SQL and PL/SQL are extremely complex and powerful languages for database processing, requiring a statement to prepare for execution. This is known as "cursor parsing". To help an application run optimally, it is necessary to analyze how parsing works.
Two kinds of parse calls exist, hard and soft. A "hard parse" occurs when the SQL or PL/SQL statement is not found in the shared SQL area (shared pool), so a complete parsing is required (for example, retrieving object descriptions from the data dictionary, checking the user's privileges on those objects, calling the optimizer to generate the execution plan, and so forth). This is the most expensive kind of parsing, and should be minimized for repeated execution. This is automatically done, provided there is enough space in the shared pool to keep the parsed representation between executions. As a result of a hard parse, the private portion of a SQL statement (in the session's heap memory) as well as the shared portion (in the shared pool) are created. The private portion is linked to the shared portion. This is called "a statement cursor" or simply "a cursor". To execute any SQL statement in the shared pool, a cursor is always required.
The other type of parse call is known as a "soft parse", and is performed when the statement is already in the shared pool, but the session has lost the "link" to the shared portion, so that the private portion must be rebuilt and linked to its shared portion again. This happens when the cursor was closed for whatever reason. Of course, this soft parse is not as expensive as a full or hard parse, but still requires some processing. The user must be authenticated again to run the SQL statement, and all name translations must be done once more. All system statistics, including hard and soft parse call activity, are maintained by Oracle and can be displayed from the V$SYSSTAT view.
To eliminate soft parsing in COBOL, C, or other 3GL applications, the precompiler option HOLD_CURSOR=YES should be used. Other options, such as RELEASE_CURSOR and MAXOPENCURSORS, can be used in conjunction with this to achieve optimal results. Refer to the appropriate Precompiler Programmer's Guide for details.
For non-3GL programs (when you do not have the same degree of control over cursors) such as Oracle Forms, the cursors will automatically be closed when a new form is called. So if you subsequently return to the caller, at least a soft parse will be performed for each cursor. Other third-party tools may produce the same effect.
In this case, you should enable a special cursor caching mechanism that will keep a copy of the user's cursors even though they are closed. You can specify SESSION_CACHED_CURSORS=50 in the INITORA as a starting point and check the statistic 'session cursor cache hits' from V$SYSSTAT to see your gains, and you can make further adjustment if necessary. For every hit that you get, you save one soft parse call. But be aware that this is done at the expense of increased memory allocation for every session in the SGA.
This parameter does not generally need to be set, because a good default value is computed from the PROCESSES parameter value. Oracle Corporation recommends that you set PROCESSES accurately and let this parameter assume its default value unless auditing is enabled. Refer to the Oracle9i Database Performance Book Set for more information.
Shared pool requirements are dependent on a number of factors, primarily the number and complexity of SQL (and PL/SQL) statements executed by users who are logged on. But there is also a per-user per-cursor memory cost even for shared cursors (statements), so the requirements depend on the number of concurrent users as well. You will need approximately 250 bytes of memory in the shared pool, per concurrent user, for each open cursor that the user has, whether the cursor is shared or not. This is in addition to the actual SQL (and PL/SQL) context areas.
Having a shared pool that is configured with insufficient space might lead to poor or erratic response times and to increased CPU costs during application execution.
On the other hand, the shared pool should not normally be over-allocated. Maintaining an unnecessarily large code inventory will incur CPU overhead and will increase the SGA size. Shared pool tuning is a continuous and iterative process. Your requirements will vary as the SQL and PL/SQL workload evolves.
All the SGA-related structures, including the shared pool, can be displayed by querying the V$SGASTAT view. In particular, this view shows the amount of free memory in the shared pool, so it can be used to monitor the shared pool usage at regular intervals for tuning purposes. For a detailed discussion on the effect of the SHARED_POOL_SIZE parameter on your application performance, and how to size it properly, refer to the Oracle9i Database Performance Book Set.
Sort operations are extremely common in a vast majority of applications and database activities, so a careful tuning of the SORT_AREA_SIZE parameter is very desirable. This value determines the maximum amount of memory that can be allocated by a single sort operation within an instance. If additional memory (over and above SORT_AREA_SIZE) is required, then the sorted rows are written to disk, in other words, temporary segments are created. Even though the sort areas are allocated out of the Oracle region private memory, they are assigned to the allocating session heap memory, and they are not shared among users. When a sort is completed, the memory retained by that process for another sort is reduced to the value specified by the SORT_AREA_RETAINED_SIZE parameter, both for in-memory and for on-disk sorts. The released (if any) portion is made available for other purposes within the same session, such as regular statement execution. Be aware that some extent of a sort area can be retained after sort completion to hold the sorted data as long as the cursor is kept open for subsequent result fetch. Finally, sort areas are not completely released and returned to the operating system for allocation by other users until the owner's session is closed.
On a regular basis, for monitoring purposes as well as every time SORT_AREA_SIZE is adjusted, you should check the 'sorts (disk)' and 'sorts (memory)' statistics in the V$SYSSTAT view. The goal is to minimize the temporary table usage without incurring excessive memory contention. Please refer to "Index Creation" and "Sort Area Size" for additional recommendations that apply to general users.
This parameter does not generally need to be set, because a good default value is computed from the PROCESSES parameter value. Oracle Corporation recommends that you set PROCESSES accurately and let this parameter assume its default value.
I/O operations are performed using an OS/390 facility called the Media Manager. This multiblock I/O facility utilizes the extended count key data channel command set, and is generally the fastest access method for performing disk I/O available in OS/390. Another advantage of the Media Manager is that it requires only a small amount of SQA memory.
The Oracle logs are moderately active files, sustaining a variable I/O rate depending on the DML work being performed by the users of the database. In general, high levels of DML activity cause frequent I/O operations to the log file. These I/O operations are sequential writes. Other I/O activity on the same device can elongate the device response times by adding head seek time, which otherwise would not occur. Do not place your log files on devices with other actively accessed data sets unless your database is primarily accessed by retrieval operations (which do not cause I/O to the log files).
Log files are more likely to restrict Oracle performance if a single, poorly designed, DML intensive batch program is running in your system.
A simple application design change relieves the single-user log file I/O type of bottleneck. A batch program that is designed to commit numerous one-row DML operations at once will generate a lower I/O rate to the redo log file and can allow the database to sustain more DML operations per second than a batch program that commits one such operation at a time.
When log archiving is enabled, configure Oracle to use a minimum of four log files. These log files need to be allocated on two different DASD volumes in flip-flop fashion, with files one and three allocated on one volume, and files two and four allocated on the other volume.
This arrangement avoids I/O contention between the Log Writer (LGWR) and the log archive batch job. With all log files on one volume, LGWR and the archive job both access the volume at the same time, potentially causing an I/O bottleneck. By allocating the files across two volumes, LGWR writes to one volume while the archive job reads from the other volume.
The system tablespace can become an I/O bottleneck when all rollback segments and temporary tables are allocated in the system tablespace. This occurs if you place a significant update-intensive or disk-sort-intensive workload on your Oracle instance without a properly laid out database.
You can avoid this bottleneck by creating additional database files using new tablespaces to contain these heavily accessed objects. You also need to configure Oracle properly by recreating the rollback segments in the desired tablespaces, and you need to ALTER your users to change their temporary tablespace assignments from the system tablespace to your newly created temporary tablespaces. Configure these tablespaces to allocate space in large extents (using the DEFAULT STORAGE clause) to minimize space management overhead. For more information, refer to the sections on rollback segments, temporary tablespaces, and tuning sorts in the Oracle9i Database Performance Book Set.
The Workload Manager (WLM) is an OS/390 facility that allows installations to effectively manage their Oracle as well as non-Oracle workloads based on business priorities. Goals can be defined to reflect business priorities. The system manages the amount of resources, such as CPU and storage that are necessary for a given workload, to achieve its goal.
Like other WLM managed workloads, Oracle workloads should be assigned to appropriate service classes based on attributes such as subsystem name, service name, user name, and transaction name. Service class structure and importance are determined by the business needs of an installation. Workloads should also be classified into report classes to facilitate monitoring and validation of an installation's workload management policies.
Service classes allow you to control the priority of your Oracle instances relative to other workloads. Service classes should be defined for your Oracle instances based on the performance requirements of the instances. You usually do not need to define a service class for each Oracle instance, because multiple Oracle instances with similar performance requirements can typically be mapped to a given service class (production instances versus test and development instances, for example). Report classes provide more granular reporting capability for different Oracle instances within a given service class and should be used where necessary to monitor CPU, memory, and I/O resources that are consumed by individual Oracle instances in the reports that are generated by SMF/RMF and other measurement subsystems.
The service class of the Oracle regions determines the relative dispatching priority of the background processes and other special tasks within the Oracle instances. The Oracle regions typically consume very moderate amounts of CPU resources. Normally, the bulk of the CPU resources that are consumed to process database requests are incurred by the client address spaces for local requests (and Oracle Net SRB enclaves in the case of remote requests) and should be managed accordingly (refer to "Local Clients" and "Remote Clients"). Oracle, therefore, typically does not need to run at a high priority, but you may want to consider the special conditions that are associated with the parallel execution feature that is discussed below in the last paragraph of this section.
In general, the Oracle regions can be configured for lower dispatching priority (or lower importance) than high priority CICS and TSO workloads, and at about the same priority as high importance (or non-discretionary) batch workload. For example, CICS users for a given Oracle instance should be configured for higher priority (or importance) than the corresponding regions of the Oracle instance. Similarly, in the case of TSO, higher priority (or importance) should be assigned to first or second period TSO workloads than to the Oracle regions.
If the Oracle dispatching priority is set too low, and if the system suffers from significant CPU contention (indicated by high processor delay in the Oracle regions), then some important Oracle internal requests might not get immediately processed, or the background tasks might not get dispatched often enough to perform the required work. For example, the buffer pool might become filled with modified buffers, and users might need to wait for Oracle to get dispatched and write out some database blocks to allow user processing to continue. The following scenario illustrates this situation:
You see the 'free buffer waits' event (from a UTLBSTAT/UTLESTAT, STATSPACK, or similar report) showing a significant total value (the unit is hundredths of second) relative to the report interval during a DML-intensive period (update, delete, or insert operations). First, consider enlarging the buffer pool, or pools, to trade memory for I/O requests (you can have multiple buffer pools in Oracle9i). Assuming that no significant I/O bottlenecks are affecting the database files (fix them first if any occur), and if the AS1 or Control Address Space CPU delay is low, then it might be necessary to increase DB_WRITER_PROCESSES to schedule more parallel I/O. If the AS1 CPU delay is significant, however, you probably need to set the dispatching priority to a higher value first. Be aware that checkpoint activity also forces modified buffers to disk, adding to the I/O stress. You should also compare the 'physical writes' and 'physical writes non checkpoint' statistics from your report to make sure that the write activity is not being unnecessarily inflated by a poorly tuned checkpoint mechanism. Refer to the LOG_CHECKPOINT_INTERVAL discussion in section "INITORA Parameters" and to the Oracle9i Database Performance Book Set for information on minimizing checkpoint overhead and for information on the UTLBSTAT/UTLESTAT and STATSPACK script utilities.
On the other hand, whenever users take advantage of the parallel execution feature that runs under special subtasks in the Oracle regions, the dispatching priority of the Oracle regions becomes an increasingly important tuning issue. In this case, Oracle dispatching priority determines how quickly these special requests are serviced and how much those Oracle users impact the overall throughput of the OS/390 system.
Because the Oracle regions run non-swappable, all workload controls that impact swapping or multiprogramming levels will have no effect on these regions.
The following considerations apply to resource intensive Oracle workloads within a TSO environment:
CICS and IMS workloads can be managed using service classes and can be classified using attributes such as userid, transaction name, luname, and subsystem instance name.
Because batch workloads are typically discretionary in nature, Oracle batch jobs do not need to be separately classified. However, Oracle batch jobs can be distinguished from other batch jobs by establishing separate service classes, as described for the TSO environment.
When Oracle batch jobs are run under a certain service class, consider their priority relative to other Oracle and non-Oracle workloads. In a normal to heavily loaded system, if Oracle batch jobs run at a lower priority than others, the Oracle jobs might be swapped out for lengthy periods. If an Oracle job is swapped out while holding a critical latch, it may adversely impact the performance of other Oracle users.
Special services classes should be considered for privileged users or special jobs such as those described below.
The database administrator (DBA) frequently needs priority access to the database in order to perform functions on behalf of all Oracle users. Granting higher relative importance to these types of work shortens the elapsed time for these functions to the benefit of all users. Except for database import and export, DBA functions generally do not require large amounts of Oracle and system resources compared to those of the user community.
Import and export functions are good candidates for higher relative importance when they involve the entire database. If the performance parameters of your system force swapping among long running batch jobs, you might want to consider non-swappable status for import and export.
Import and export performance can be optimized by maximizing the size of the buffer that is used to transfer rows to and from the export file. The buffer needs to be large enough to hold approximately 1000 table rows to get the best performance from these utilities. In addition, you can improve performance by increasing the number of buffers that are available for reading and writing the export file. Use the DCB BUFNO JCL parameter to increase the number of buffers. The I/O operations that are issued by QSAM and BSAM will not generate a channel program using more than 30 buffers or more than approximately 240 KB. For I/O bound processes such as Export and Import, you should specify a BUFNO that allocates approximately 480 KB of buffers. This value will give you the maximum amount of overlap between two maximum I/O channel programs. Refer to Oracle9i Database Utilities for more information.
The direct path in SQL*Loader is much more efficient than the conventional path. When using the DIRECT option, SQL*Loader is generally I/O bound on the input data file. To reduce the elapsed time that is required for a load operation, you need to increase the number of buffers that are available for reading the input file by adding a DCB BUFNO parameter to the input file allocation. Performance improvements occur as the number of buffers is increased to 200, although 48 buffers yield a significant improvement in the data load rate.
If you cannot use the DIRECT option, then specify the largest bind array size (using the ROWS parameter) that you can. An array size of approximately 1000 rows improves performance significantly over the default size of 64 rows.
The creation of indexes on large tables can consume significant resources. Consider higher relative importance and non-swappable status for these functions.
In addition to OS/390 tuning parameters, you need to consider special session settings to support index creation in large tables. Increasing the SORT_AREA_SIZE parameter value can substantially reduce the elapsed times of index creation jobs. This can be done selectively at the session level by using the ALTER SESSION SQL command so that other non-critical jobs will still use the INITORA specified value.
You can use SYNCSORT, DFSORT, or another OS/390 sort utility to sort the data by key before loading it into Oracle. Once the data is sorted, load the data into Oracle and create the index with the NOSORT option. For large data loads, this technique can save significant amounts of time when loading data and creating the index.
Remote clients that access an Oracle server through the Oracle Net service are dispatched on a lightweight unit of work called an enclave SRB within the Net address space. The performance characteristics of such work can be effectively managed when used with WLM in goal mode. Enclave transactions are managed separately from one another as well as from the Oracle Net address space they run in.
The Net startup option ENCLAVE (CALL|SESS) controls how the database request from the client is handled (described under "PARM"). With ENCLAVE(SESS) specified in the PARM value used at Net startup, classification of the work is done once when a new remote connection is made. Oracle Net presents WLM with attributes for workload classification. Some of the network specific attributes that can be used for classification include protocol, host name, or IP address. The list of WLM attributes available for classification is shown in Table 16-1, "Workload Manager Attributes and Values". The enclave will be deleted at session termination (logoff) time. Because the classification happens only once per session, only velocity goals are appropriate for the enclave's service class.
If ENCLAVE(CALL) is specified in the PARM value used at Net startup, then the enclave is deleted when the request from the client is finished (when Net needs more data from the client). Deleting the enclave reports the transaction completion to WLM, providing response time and transaction counts to any workload monitors such as RMF. The next request arriving from the client will be classified into a new enclave. The values available for classification are the same as with ENCLAVE(SESS) above, and are shown in Table 16-1, "Workload Manager Attributes and Values". Because the classification is done for each network request, response time goals should be used for the enclave's service class.
If there is no WLM policy active, then all of the Oracle Net work will be dispatched in SRBs that will be executed using the dispatching priority of the Oracle Net address space. If you are running in WLM compatibility mode but have a WLM policy active, it is possible to manage the enclaves in a performance group (PGN) distinct from Oracle Net by adding the SRVCLASS= parameter to the IEAICSxx member of PARMLIB. However, since you must still build a WLM policy and activate it in order to get this capability, it is recommended that you run in goal mode.
"Subsystem Type" is not strictly an attribute. WLM has several predefined subsystem types (JES for example). You must define a new subsystem type of "OSDI" to WLM if you desire WLM monitoring of OSDI work. Please refer to the IBM manual, OS/390 MVS Planning: Workload Management, C28-1761, for information on how to do this, and for information on how to utilize the attributes listed above to manage work. Generally, WLM is configured using ISPF and the IWMARIN0 REXX exec.
If you choose to run in goal mode without a policy active, or run without a section in the policy for the OSDI subsystem, then the client work will be assigned the service class SYSOTHER, which has a discretionary goal. Performance is likely to be unsatisfactory. Running in compatibility mode will result in the enclave running in a preemptable SRB at the dispatching priority of Oracle Net. Note that this means the CPU time for the user client work will be charged to the Oracle Net address space as SRB time.
The following is an example of a WLM classification rules ISPF panel:
Subsystem-Type Xref Notes Options Help -------------------------------------------------------------------------- Modify Rules for the Subsystem Type Row 1 to 4 of 4 Command ===> ____________________________________________ SCROLL ===> PAGE Subsystem Type . : OSDI Fold qualifier names? Y (Y or N) Description . . . OSDI SubSystem Type Action codes: A=After C=Copy M=Move I=Insert rule B=Before D=Delete row R=Repeat IS=Insert Sub-rule More ===> -------Qualifier------------- -------Class-------- Action Type Name Start Service Report DEFAULTS: ORACLES ________ ____ 1 SI ORAC ___ ________ ________ ____ 2 NET 010.100 ___ ________ ________ ____ 3 LU 001.080 ___ ORACLEM ________ ____ 3 LU 001.081 ___ ORACLEH ________ **********************************BOTTOM OF DATA *******************************
This rule assigns the service class ORACLEM to all work arriving from a client at IP address 10.100.1.80, and assigns ORACLEH to all work from the client at IP address 10.100.1.81. Note that the service class ORACLES is assigned as the default service class to Net workloads that cannot be classified by the above rules. It is very important to specify a default service class. Without a default service class, an error in the classification rules could result in no rules matching. In this case, the request will be assigned service class SYSOTHER, which has a discretionary goal. This will result in undesirable performance characteristics.
If ENCLAVE(CALL) is specified in the PARM value at Net startup, you should specify response goals, or percentile response goals for the service classes used by Oracle enclaves.
The following shows a sample screen defining a service class with three periods. The first period has a response time goal of 15 ms. at importance 1. This gives short requests high priority access to the CPU. If the request takes more than 50 CPU service units, the enclave is migrated to a second period at importance 3. If the request is still running after 500 service units, it is then migrated to a third period at importance 5. This design of service class goals is only feasible if the ENCLAVE(CALL) parameter is used. It has the advantage of providing fast, high priority response to short requests, while treating longer requests at low, batch-like priorities.
Service-Class Xref Notes Options Help ---------------------------------------------------------------------- Modify a Service Class Row 1 to 2 of 2 Command ===> _________________________________________________________ Service Class Name . . . . . : ORACLEH Description . . . . . . . . . Oracle Mid Tier #1 Workload Name . . . . . . . . ORACLE (name or ?) Base Resource Group . . . . . ________ (name or ?) Specify BASE GOAL information. Action Codes: I=Insert new period, E=Edit period, D=Delete period. ---Period--- ---------------------Goal--------------------- Action # Duration Imp. Description __ __ 1 50 1 Average response time of 00:00:00.015 __ 2 500 3 Average response time of 00:00:00.500 __ 3 5 Execution velocity of 10 ******************************* Bottom of data **********************
Oracle supports two major programming languages in the database: PL/SQL and Java. A large portion of Oracle customers use both PL/SQL and Java to build database applications. By adding Java to the server, Oracle Corporation has opened up the range of things that can be done in the server. Note that computational operations were typically being done outside the server on clients using C/C++. Now, much of that logic can be moved into the server with the benefits of reduced network latency and round trips, improved performance, and portability.
How do you decide whether to use PL/SQL or Java? Because PL/SQL and Java are two fundamentally different languages, the relative performance of the two is difficult to compare. The results of such a comparison will depend on the specific conditions of customer applications. Two important ideas, from a performance point of view, are: PL/SQL and JAVA can coexist, and both languages have been built around different design points and are therefore better suited for different tasks.
For SQL intensive applications, PL/SQL is generally faster than Java. This is especially true when applications execute tight loops around SQL, when a large amount of data needs to be converted from SQL types to Java types, or when PL/SQL can use bulk operations. Similarly, for applications (such as triggers) that are characterized more by the speed of entry into the PL/SQL or Java engines than by the execution speed of the application, PL/SQL is generally faster. PL/SQL was also optimized in Oracle8i to perform some string operations, such as concatenation. PL/SQL is significantly faster both transparently (from internal optimizations) and via new features (such as bulk SQL). Applications (or parts thereof) that are highly SQL intensive should be written in PL/SQL.
Java is a general purpose object-oriented programming language with a rich type system, a component model, and other facilities that supports multi-tier distributed computing standards (CORBA and EJB). In contrast with PL/SQL, which shares the same type system as SQL, Java has a much more general purpose and richer type system that was designed to represent arbitrarily complex data structures such as multi-dimensional arrays, graphs, and so forth. Because it is an object-oriented language, Java provides elegant facilities to inherit from existing Java types and to build complex class hierarchies and arbitrarily deep nested hierarchies quite easily. Further, the Java VM is much better tuned to execute programs which have little or no SQL, and it can use native numeric machine data types rather than the more precise (but often slower) Oracle SQL data types that are used by PL/SQL.
For applications that involve complex object-oriented or highly CPU-intensive "number-crunching" operations, Java can be faster than PL/SQL. For example, Java has native support for floating point operations whereas PL/SQL uses SQL numbers. Furthermore, when natively compiled using the Java compiler (NCOMP), Java's performance can be improved significantly.
For computation intensive programs, pure Java execution (method calls, data manipulation, algorithmic operations, and so forth) can be faster than PL/SQL. On the other hand, PL/SQL is generally more optimized for SQL access. As a net result, the overall performance of the application will depend to some extent on the relative balance between computational operations and SQL access. Finally, if the run-time performance of your application is dominated by long running SQL statements, then the choice between PL/SQL and Java will not make a significant difference to the overall run time. Nevertheless, if you are primarily doing SQL access, PL/SQL is a simpler and more efficient choice.
The Oracle parallel execution feature enables multiple server tasks to process a single piece of work concurrently. Except for the Parallel SQL*Loader special case, these parallel execution slaves run as subtasks within the Oracle regions. The parallel execution feature enables all of the following:
The parallel execution feature can dramatically reduce elapsed time for a given workload, as long as the necessary CPU capacity is available.
Although the percent increase in CPU time on a SQL statement basis is typically small due to some overhead that is required to parallelize, the elapsed time during which the CPU is consumed is much shorter than for non-parallel operation. This means that the percent of the machine that is used is significantly greater for a shorter time interval. For example, a non-parallel query might use 10 percent of the CPU for a period of 60 seconds, while a parallel query for the same query might use 99 percent of the CPU for a period of approximately 6 seconds. Monitoring and managing overall CPU utilization is therefore an important part of the tuning effort.
Any Oracle region is capable of starting parallel slaves as needed, within the limits specified by certain INITORA parameters and depending on user requests. The number of slaves (if any) that are specified in PARALLEL_MIN_SERVERS is automatically created in the AS1 during normal instance startup. These AS1 slaves can do parallel work for any database user, regardless of the particular region to which the session in question is assigned. Additional slaves, up to the PARALLEL_MAX_SERVERS value, will be dynamically added as required. Even though these on-demand started slaves can be created anywhere, depending on the requesting region (not necessarily AS1), they all are part of the parallel slave pool and can provide service to users in any instance region upon completion of the work for which they were originally created.
The OSDI DISPLAY SESSION command can be used to display the parallel execution slaves that are started in an Oracle instance.
Example:
F MYORA8,DISPLAY SESSION JOBNAME(P*)
or
F MYORA8,D SESS JOB(P*)
The parallel execution slaves are identified by a JOBNAME in the 'Pnnn' form where nnn is a number in the 000 to 999 range.
The parallel execution feature works best on files that are allocated across many disk drives. This allows the parallel execution slaves to maximize concurrent access to the data files and to minimize device contention. The temporary tablespace that is used for sorting (queries and index creation) should also be allocated across several disks. This tablespace should be defined so that its space is allocated in large extents to minimize space management overhead. Note that the above technique is sometimes referred to as "Striping" and should not be confused with the OS/390 feature known as Extended Facility Striped Data Sets.
Often, the best method for determining the optimal degree of parallelism is to:
For the initial estimate of degree of parallelism for a single user, use one or two times the number of system CPUs, depending on the available system capacity. This is a good estimate for sort-intensive operations because they tend to be CPU bound. If the operation is I/O intensive (that is, more scanning of the data than sorting), the number of disk drives involved in the scans is a good starting point.
If memory is abundant, setting SORT_AREA_SIZE to a large value can be beneficial.
For example:
SORT_AREA_SIZE=1024000
Using a sort area much larger than 1M, however, may not provide a significant benefit unless it is large enough to completely eliminate the need of a temporary table on disk. Also be aware that a parallel operation using a sort will acquire as many sort areas as the parallel degree that is specified.
If memory is a concern on the system (insufficient memory or high paging), you might want to decrease the SORT_AREA_SIZE.
Memory use increases with sorting (for example, with certain selects and create index). This increase can be substantial and is dependent upon the value of SORT_AREA_SIZE and the degree of parallelism. However, this increase is over a shorter period of time, typically resulting in lower main storage occupancy (working set X elapsed time) and lower average overall storage use by Oracle. Paging rate variability can increase and must be monitored.
The V$PQ_SESSTAT, V$PQ_SLAVE, and V$PQ_SYSSTAT views can be used to view statistics on parallel workloads. Set RELEASE_CURSOR=YES in precompiled programs for parallel queries. This prevents parallel server tasks from being retained by a cursor when the query is complete.
The resource trade-off with the parallel query option is CPU and memory versus elapsed time and throughput. The goal is maximum gain in elapsed time or throughput with minimum cost of CPU or memory.
Ideal candidates for parallel execution are:
Candidates that might be unsuitable for parallel execution are:
Whether you are writing new SQL statements or tuning problematic statements in an existing application, your methodology for tuning database operations essentially concerns CPU and disk I/O resources.
Focus your tuning efforts on statements where the benefit of tuning demonstrably exceeds the cost of tuning. Use tools such as TKPROF, the SQL trace facility, SQL Analyze, Oracle Trace, and the Enterprise Manager Tuning Pack to find the problem statements and stored procedures. In addition, you can query the V$SORT_USAGE view to see the session and SQL statement associated with a temporary segment.
The statements with the most potential to improve performance, if tuned, include:
In the V$SQLAREA view, you can find those statements still in the cache that have done a great deal of disk I/O and buffer gets. (Buffer gets show approximately the amount of CPU resource used.)
Remember that application design is fundamental to performance. No amount of SQL statement tuning can make up for inefficient application design. If you encounter SQL statement tuning problems, then perhaps you need to change the application design.
You can use two strategies to reduce the resources consumed by a particular statement:
Statements may use more resources because they do the most work, or because they perform their work inefficiently--or they may do both. However, the lower the resource used per unit of work (per row processed), the more likely it is that you can significantly reduce resources used only by changing the application itself. That is, rather than changing the SQL, it may be more effective to have the application process fewer rows, or process the same rows less frequently.
These two approaches are not mutually exclusive. The former is clearly less expensive, because you should be able to accomplish it either without program change (by changing index structures) or by changing only the SQL statement itself rather than the surrounding logic.
For more information on identifying and tuning problematic SQL statements, refer to the Oracle9i Database Performance Book Set.
The SQL trace facility provides performance information about individual SQL statement execution. It can provide event-based statistics (parses, executions, fetches, physical and logical reads, row counts, and so forth) as well as time-based statistics (CPU and elapsed times). When a trace data set is processed by the TKPROF utility to convert it to a readable format, the SQL statement execution plan is also reported. In addition to enabling the SQL trace facility, you should activate the timed statistics gathering to make the trace data more meaningful for tuning purposes.
One of the methods to enable the SQL trace facility (including time-based data) is to place the following statements in the INITORA file:
SQL_TRACE=TRUE TIMED_STATISTICS=TRUE
When the SQL trace facility is enabled this way, it takes effect at the instance level so that a trace data set is generated each time that a user logs on to the instance or each time that a program is executed. Trace data sets can be generated in the form of spool files belonging to a particular Oracle region or can be written directly into regular disk data sets. After a session is finished, its corresponding trace data set is closed and made available for processing.
If you do not want to log trace data for every user, you can issue the following SQL commands in the session for which you want to gather data,
ALTER SESSION SET SQL_TRACE=TRUE; ALTER SYSTEM SET TIMED_STATISTICS=TRUE;
and omit these parameters from the INITORA definition. Other methods are available to enable a SQL trace for a given session from another user's session. For more information about the SQL trace facility, refer to the Oracle9i Database Performance Book Set.
If the Oracle trace files destination is SYSOUT (see the TRACE_DSNAME database region parameter), the following considerations apply. Otherwise, the trace data sets can be processed directly by TKPROF.
A trace file in the spool queue needs to be prepared for the subsequent TKPROF processing by copying the trace data (using SDSF) into a regular sequential file without the ASA control characters present in spool files. This is required because TKPROF cannot read an output file directly from the spool, nor can it recognize ASA control characters.
The ASA control characters can be eliminated by "printing" the trace data to a pre-allocated DD name instead of to a dsname. In this case, SDSF copies the data as is and does not keep control characters.
Example:
ALLOC F(trc) DA(trace.dataset) NEW CAT LRECL(136) RECFM(V,B) - SPACE(2 2) TRACKS BLKSIZE(4096)
Then enter ISPF/SDSF and browse the trace file you want to copy. From the command line, issue:
PT F trc PT PT CLOSE TSO FREE F(trc)
The TKPROF program translates the Oracle trace data set into readable form. To invoke the TKPROF program from ISPF option 6, use:
CALL 'oran.orav.CMDLOAD(TKPROF)' '/DSN/trace.dataset /DSN/output.dataset [options]'
where:
The trace.dataset and output.dataset are not subject to FNA processing. The full data set names must be specified using /DSN/ notation. For more information about the TKPROF utility, refer to the Oracle9i Database Performance Book Set.
Alternatively, you can invoke the TKPROF utility in batch mode, as follows:
//MYSTEP EXEC PGM=TKPROF, // PARM='/DD/IN /DD/OUT [options]' //STEPLIB DD DISP=SHR,DSN=oran.orav.CMDLOAD //ORA$LIB DD DISP=SHR,DSN=oran.orav.MESG //SYSUDUMP DD SYSOUT=* //SYSOUT DD SYSOUT=*,DCB=(LRECL=350,BLKSIZE=3500,RECFM=VB) //SYSERR DD SYSOUT=*,DCB=(LRECL=350,BLKSIZE=3500,RECFM=VB) //ORA@sid DD DUMMY //ORAPRINT DD SYSOUT=* //IN DD DISP=SHR,DSN=trace.dataset //OUT DD SYSOUT=*,DCB=(LRECL=350,BLKSIZE=3500,RECFM=VB) //SYSIN DD DUMMY
This section discusses how performance of Oracle Access Manager for CICS is affected by the following:
Several Oracle Access Manager for CICS thread definition parameters can significantly impact the performance of certain transactions connecting to an Oracle database instance from CICS.
Impacted transactions include:
User transactions occur whenever a CICS user presses a program function [PF] key or the [Enter] key on their terminal.
CICS transactions occur whenever a CICS user initiates a transaction to CICS by typing a transaction identifier in the upper left corner of the terminal screen and pressing a [PF] key or [Enter]. For pseudo-conversational CICS transactions, each user transaction is a CICS transaction. A CICS transaction can have Oracle database transactions imbedded within it.
Oracle database transactions explicitly terminate when the user issues a COMMIT or ROLLBACK SQL statement, and implicitly terminate when the user disconnects from the instance. Oracle database transactions can be imbedded within a CICS transaction.
Only use Oracle COMMIT when coordinated recovery between non-Oracle and Oracle database resources is not a requirement.
Threads are assigned to CICS transactions for the duration of the transaction. Some types of Oracle database transactions can continue for a long time, preventing multiple users from using the same thread.
Multiple users of pseudo-conversational CICS transactions (normally programmed in Pro*COBOL or Pro*C) can effectively share threads because each user transaction corresponds to one CICS transaction and one Oracle database transaction. When the user transaction completes, the Oracle database transaction completes and the thread that was in use is released. Each pseudo-conversational transaction user spends more time outside transactions than actually running transactions (that is, think time is higher than response time). Therefore, each thread generally supports multiple users of this type of application program.
When a user enters a conversational CICS transaction, the CICS transaction is not terminated until the user leaves the environment. A CICS transaction of this type generally consists of multiple user inputs for multiple Oracle database transactions. Therefore, each conversational transaction obtains a thread for a longer duration. This must be taken into account when defining the number of threads.
A CICS transaction runs under the same CICS subtask for the duration of the transaction. For pseudo-conversational Pro*COBOL or Pro*C CICS transactions, threads using CICS subtasking are efficient. Each CICS transaction gets assigned to a thread, executes under a CICS subtask, and then frees the thread when the transaction ends. However, conversational CICS transactions are assigned to the same thread until the user ends the transaction by exiting it. This implies that each conversational transaction monopolizes a CICS subtask until the transaction ends. In other words, each thread supports only 1 to 1.5 conversational transactions depending on how often the users exit the transaction.
Some CPU resources are required to connect Oracle Access Manager for CICS threads to the Oracle database instance. For the best performance, minimize the number of connections by setting up your thread definitions to use the autologon facility. This facility is implemented with the AUTH thread definition parameter. Pre-authorized threads improve transaction performance.
The most efficient way to define thread authorization is by using the AUTH option authorization string, protecting the threads from being torn down by setting PROTECT to YES and specifying only transaction codes in the TRANSAC parameter that can use the authorization string. In this situation, the threads are connected to the instance when the Oracle Access Manager for CICS adapter is started, and do not have to reconnect until the next startup.
The second most efficient thread authorization scheme is to use the program or transid AUTH options, protect the threads from tear-down, and define the threads only for the program or transaction code specified. This scheme causes the thread to connect to the instance when the first transaction is assigned to the thread. All subsequent transactions use the same connection because they have the same transaction code or program name.
The least efficient way to define threads is without an AUTH parameter so the transactions explicitly connect to the instance. Oracle Access Manager for CICS thread logon processing is more efficient than explicit SQL CONNECT statement processing, especially when the same connection can be used by multiple CICS transactions.
Threads defined with PROTECT set to YES remain connected as long as the Oracle Access Manager for CICS adapter is started. CPU resources are required to connect threads, so thread protection can reduce overall CPU requirements.
Thread protection can cause the Oracle Access Manager for CICS adapter to use more memory in the CICS region. Protected threads remain connected even when they are not in use by a CICS transaction, and connected threads require slightly more memory than disconnected threads.
In general, protect threads that are used frequently and that are automatically connected using the autologon facility. There is no performance advantage to protecting threads that are not pre-authorized because thread protection simply maintains the connection to the Oracle database instance.
Table 9-3 shows the base code storage requirements for Oracle Access Manager for CICS.
| Usage | Below 16M | Above 16M |
|---|---|---|
|
ORACICS |
0 bytes |
28K |
|
CICADPX |
24 bytes |
0K |
|
LIBCLNTS |
0 bytes |
24M* |
|
* Note: LIBCLNTS is loaded in above-the-line storage, not managed by CICS. |
||
Table 9-4 shows the adapter storage requirements for Oracle Access Manager for CICS, which are the same for both the local and remote configurations. The total variable storage requirement per active Oracle server/CICS transaction is 30,748 bytes.
For the thread table, a fixed amount of storage is used. This calculation is used to determine a thread table's fixed storage requirements (located above the 16M line):
8176 bytes + (1128 bytes x MAXTHRDS)
Use the same calculation for both the local and remote configurations.
Table 16-4 shows the connected thread storage requirements for Oracle Access Manager for CICS.
All buffer storage for connected threads under CICS release 4.1 or higher is obtained above the 16M line.
This section describes the storage requirements for Oracle Access Manager for IMS TM.
| CSA | Storage |
|---|---|
|
Base requirement |
500 Bytes |
|
Control region RTT and Dependent region RTT |
Varies according to customer definition |
|
Total |
500 Bytes + RTT size |
All CSA allocations are ECSA (above 16M).
| Control Region | Storage |
|---|---|
|
Base requirement |
7524K |
All control region allocations are extended private (above 16M).
| Dependent Region | Storage |
|---|---|
|
Base requirement |
7524K |
|
Total |
7524K + application storage |
All dependent region allocations are extended private (above 16M).
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|