Oracle Training. Oracle Tuning. Oracle DBA Hosting. Oracle License Negotiation. Oracle Tips by Burleson Consulting Updates with where constraints that contain correlated subqueries are one of the most confounding problems of the SQL update statement.
This is because of the rule that an update statement may only contain a single table name. Hence, we not add a correlated subquery when the values of other table rows influence our update decision. To illustrate, consider the following SQL that gives a 10 percent raise to all employees who did not receive a bonus last year.
We expect to see a full-table scan against the emp table. Restrictions for referential integrity occur whenever a DML operation on one table could cause a recursive DML operation on another table. These restrictions also apply when, in order to perform an integrity check, it is necessary to see simultaneously all changes made to the object being modified.
Table lists all of the operations that are possible on tables that are involved in referential integrity constraints. Table Referential Integrity Restrictions.
Delete on tables having a foreign key with delete cascade is not parallelized because parallel execution servers will try to delete rows from multiple partitions parent and child tables. DML on tables with self-referential integrity constraints is not parallelized if the referenced keys primary keys are involved.
For DML on all other columns, parallelism is possible. If any deferrable constraints apply to the table being operated on, the DML operation will not be parallelized. A DML operation will not be parallelized if the affected tables contain enabled triggers that may get fired as a result of the statement.
This implies that DML statements on tables that are being replicated will not be parallelized. Relevant triggers must be disabled in order to parallelize DML on the table.
Note that, if you enable or disable triggers, the dependent shared cursors are invalidated. A DML operation cannot be parallelized if it is in a distributed transaction or if the DML or the query operation is against a remote object. Example Distributed Transaction Parallelization. The query operation is executed serially without notification because it references a remote object. When the SQL statement is parallelized, these functions are executed on a per-row basis by the parallel execution server.
Because of this, not all functions will generate correct results if executed in parallel. User-written table functions can appear in the statement's FROM list.
These functions act like source tables in that they output rows. Table functions are initialized once during the statement at the start of each parallel execution process. All variables are entirely private to the parallel execution process. Other parts of a query or subquery can sometimes execute in parallel even if a given function execution must remain serial.
For a parallel DML statement, any function call that cannot be executed in parallel causes the entire DML statement to be executed serially. AS SELECT statement, function calls in the query portion are parallelized according to the parallel query rules in the prior paragraph. The query may be parallelized even if the remainder of the statement must execute serially, or vice versa.
In addition to parallel SQL execution, Oracle can use parallelism for the following types of operations:. Like parallel SQL, parallel recovery and propagation are performed by a parallel execution coordinator and multiple parallel execution servers. Parallel load, however, uses a different mechanism. The behavior of the parallel execution coordinator and parallel execution servers may differ, depending on what kind of operation they perform SQL, recovery, or propagation.
For example, if all parallel execution servers in the pool are occupied and the maximum number of parallel execution servers has been started:. For a given session, the parallel execution coordinator coordinates only one kind of operation. A parallel execution coordinator cannot coordinate, for example, parallel SQL and parallel recovery or propagation at the same time.
Oracle Database Backup and Recovery Basics for information about parallel media recovery. Oracle Database Performance Tuning Guide for information about parallel instance recovery. Oracle Database Advanced Replication for information about parallel propagation. Parallel execution is enabled by default.
The initial computed values of the parallel execution parameters should be acceptable for the majority of installations. These parameters affect memory usage and the degree of parallelism used for parallel operations.
The parameters can also be manually tuned, increasing or decreasing their values to suit specific system configurations or performance goals. For example:. You can also manually tune parallel execution parameters; however, Oracle recommends using default settings for parallel execution. Manual tuning of parallel execution is more complex than using default settings for two reasons: manual parallel execution tuning requires more attentive administration than automated tuning, and manual tuning is prone to user-load and system-resource miscalculations.
By default, Oracle automatically sets parallel execution parameters, as shown in Table For most systems, you do not need to make further adjustments to have an adequately tuned parallel execution environment. Table Parameters and Their Defaults. Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle increases the number of processes from the number created at instance startup up to this value.
If you set this parameter too low, some queries may not have a parallel execution process available to them during query processing. If you set it too high, memory resource shortages may occur during peak periods, which can degrade performance. Note that you can set some parameters in such a way that Oracle will be constrained. Note that the DOP applies directly only to intraoperation parallelism. If interoperation parallelism is possible, the total number of parallel execution servers for a statement can be twice the specified DOP.
No more than two operations can be performed simultaneously. When multiple users employ parallel execution at the same time, available CPU, memory, and disk resources may be quickly exhausted. Oracle provides several ways to deal with resource utilization in conjunction with parallel execution, including:. The adaptive multiuser algorithm, which reduces the DOP as the load on the system increases. By default, the adaptive multiuser algorithm is enabled, which optimizes the performance of systems with concurrent parallel SQL execution operations.
The Database Resource Manager, which enables you to allocate resources to different groups of users. The parallel execution coordinator determines the DOP by considering several specifications. The coordinator:. Hints, PARALLEL clauses, table or index definitions, and default values only determine the number of parallel execution servers that the coordinator requests for a given operation.
The actual number of parallel execution servers used depends upon how many processes are available in the parallel execution server pool and whether interoperation parallelism is possible. You can specify hints in a SQL statement to set the DOP for a table or index and for the caching behavior of the operation.
The default DOP is used when you ask to parallelize an operation but you do not specify a DOP in a hint or within the definition of a table or index.
The default DOP is appropriate for most applications. For parallelizing by partition, the number of partitions that will be accessed, based on partition pruning. For parallel DML operations with global index maintenance, the minimum number of transaction free lists among all the global indexes to be updated.
The minimum number of transaction free lists for a partitioned global index is the minimum number across all index partitions. This is a requirement to prevent self-deadlock. These factors determine the default number of parallel execution servers to use.
However, the actual number of processes used is limited by their availability on the requested instances during run time.
You can retry the query when the system is less busy. With the adaptive multiuser algorithm, the parallel execution coordinator varies the DOP according to the system load.
Oracle determines the load by calculating the number of active Oracle Server processes. If the number of server processes currently allocated is larger than the optimal number of server processes, given the number of available CPUs, the algorithm reduces the DOP.
This reduction improves total system throughput by avoiding overallocation of resources. Oracle can perform an operation in parallel as long as at least two parallel execution servers are available. If too few parallel execution servers are available, your SQL statement may execute slower than expected. You can specify the minimum percentage of requested parallel execution servers that must be available in order for the operation to execute.
This strategy ensures that your SQL statement executes with a minimum acceptable parallel performance. If the minimum percentage of requested parallel execution servers is not available, the SQL statement does not execute and returns an error ora For example, if you specify 50 for this parameter, then at least 50 percent of the parallel execution servers requested for any parallel operation must be available in order for the operation to succeed.
If 20 parallel execution servers are requested, then at least 10 must be available or an error is returned to the user. In Oracle Real Application Clusters, instance groups can be used to limit the number of instances that participate in a parallel operation.
You can create any number of instance groups, each consisting of one or more instances. You can then specify which instance group is to be used for any or all parallel operations. Parallel execution servers will only be used on instances which are members of the specified instance group. To optimize performance, all parallel execution servers should have equal workloads. For SQL statements parallelized by block range or by parallel execution servers, the workload is dynamically divided among the parallel execution servers.
This minimizes workload skewing, which occurs when some parallel execution servers perform significantly more work than the other processes. For the relatively few SQL statements parallelized by partitions, if the workload is evenly distributed among the partitions, you can optimize performance by matching the number of parallel execution servers to the number of partitions or by choosing a DOP in which the number of partitions is a multiple of the number of processes. See "Limitation on the Degree of Parallelism" for details regarding this topic.
For example, suppose a table has 10 partition, and a parallel operation divides the work evenly among them. You can use 10 parallel execution servers DOP equals 10 to do the work in approximately one-tenth the time that one process would take. You might also use five processes to do the work in one-fifth the time, or two processes to do the work in one-half the time.
If, however, you use nine processes to work on 10 partitions, the first process to finish its work on one partition then begins work on the 10th partition; and as the other processes finish their work, they become idle. This configuration does not provide good performance when the work is evenly divided among partitions. When the work is unevenly divided, the performance varies depending on whether the partition that is left for last has more or less work than the other partitions.
Similarly, suppose you use four processes to work on 10 partitions and the work is evenly divided. In this case, each process works on a second partition after finishing its first partition, but only two of the processes work on a third partition while the other two remain idle. This formula does not take into account the possibility that some processes might have to wait while others finish working on the last partitions. By choosing an appropriate DOP, however, you can minimize the workload skew and optimize performance.
However, not all of these methods apply to all types of SQL statements. Parallelization has two components: the decision to parallelize and the DOP. To determine the DOP, Oracle looks at the reference objects:. Parallel query looks at each table and index, in the portion of the query being parallelized, to determine which is the reference table. The basic rule is to pick the table or index with the largest DOP. For parallel DDL, the reference object that determines the DOP is the table, index, or partition being created, rebuilt, split, or moved.
The query uses the maximum DOP taken from all of the table declarations involved in the query and all of the potential indexes that are candidates to satisfy the query the reference objects.
If a table has both a parallel hint specification in the query and a parallel declaration in its table specification, the hint specification takes precedence over parallel declaration specification.
See Table for precedence rules. Update, merge, and delete parallelism are not possible within a partition, nor on a nonpartitioned table. See "Limitation on the Degree of Parallelism" for a possible restriction.
Use a parallel clause in the definition of the table being updated or deleted the reference object. The hint also applies to the underlying scan of the table being changed. If the statement contains subqueries or updatable views, then they may have their own separate parallel hints or clauses. The DOP is determined by the same rules as for the queries.
A parallel execution server can update or merge into, or delete from multiple partitions, but each partition can only be updated or deleted by one parallel execution server. If the DOP is less than the number of partitions, then the first process to finish work on one partition continues working on another partition, and so on until the work is finished on all partitions.
If the DOP is greater than the number of partitions involved in the operation, then the excess parallel execution servers will have no work to do. Because the tables being queried are usually not the same as the table being inserted into, the hint enables you to specify parallel directives specifically for the insert operation. In this context, maximum query directive means that among multiple tables and indexes, the table or index that has the maximum DOP determines the parallelism for the query operation.
A rebuild of a partitioned index is never parallelized. If a parallel clause exists in a table definition, it determines the parallelism of DDL statements as well as queries. If the DDL statement contains explicit parallel hints for a table, however, those hints override the effect of parallel clauses for that table.
Oracle can parallelize both parts of the statement. At least one of the tables specified in the query requires one of the following: a full table scan or an index range scan spanning multiple partitions. The scan operation cannot be parallelized if, for example:. Table shows how various types of SQL statements can be parallelized and indicates which methods of specifying parallelism take precedence.
The DOP specifies the number of available processes, or threads, used in parallel operations. Each parallel thread can use one or two query processes, depending on the query's complexity. The adaptive multiuser feature adjusts the DOP based on user load. For example, you might have a table with a DOP of 5. This DOP might be acceptable with 10 users. It is best to use the parallel adaptive multiuser feature when users process simultaneous parallel execution operations. The adaptive multiuser algorithm has several inputs.
The algorithm first considers the number of active Oracle Server processes as calculated by Oracle. When a system is overloaded and the input DOP is larger than the default DOP, the algorithm uses the default degree as input. The system then calculates a reduction factor that it applies to the input DOP.
For example, using a CPU system, when the first user enters the system and it is idle, it will be granted a DOP of The next user will be give a DOP of eight, the next four, and so on.
If the system settles into a steady state of eight users issuing queries, all the users will eventually be given a DOP of 4, thus dividing the system evenly among all the parallel users.
If you are sure you want to execute in parallel and want to avoid setting the DOP for a table or modifying the queries involved, you can force parallelism with the following statement:.
All subsequent queries will be executed in parallel provided no restrictions are violated. This clause overrides any parallel clause specified in subsequent statements in the session, but is overridden by a parallel hint. In typical OLTP environments, for example, the tables are not set parallel, but nightly batch scripts may want to collect data from these tables in parallel. By setting the DOP in the session, the user avoids altering each table in parallel and then altering it back to serial when finished.
The adaptive multiuser feature also uses the default DOP to compute the target number of query server processes that should exist in a system. When a system is running more processes than the target number, the adaptive algorithm reduces the DOP of new queries as required. This increases the default DOP and allow better utilization of hardware resources.
Most parallel operations need at most twice the number of query server processes as the maximum DOP attributed to any table in the operation. This might not be enough for parallel queries on tables with higher DOP attributes.
If users initiate too many concurrent operations, Oracle might not have enough query server processes. This contention can reduce system throughput to a level lower than if parallel execution were not used. Verify that the machine has sufficient swap space exists on the machine to add more processes. Limiting the total number of query server processes might restrict the number of concurrent users who can execute parallel operations, but system throughput tends to remain stable.
To increase the number of concurrent users, you must restrict the resource usage of each individual user. You can achieve this by using the parallel adaptive multiuser feature or by using resource consumer groups. You can limit the amount of parallelism available to a given user by establishing a resource consumer group for the user.
Do this to limit the number of sessions, concurrent logons, and the number of parallel processes that any one user or group of users can have. Each query server process working on a parallel execution statement is logged on with a session ID. Each process counts against the user's limit of concurrent sessions.
For example, to limit a user to 10 parallel execution processes, set the user's limit to One process is for the parallel coordinator and the other 10 consist of two sets of query server servers.
This would allow one session for the parallel coordinator and 10 sessions for the parallel execution processes. This parameter lets you specify in a single instance the number of processes to be started and reserved for parallel operations. The syntax is:. The n variable is the number of processes you want to start and reserve for parallel operations. This way, when a query is issued the processes are likely to be available. It is desirable, however, to recycle query server processes periodically since the memory these processes use can become fragmented and cause the high water mark to slowly increase.
Parallel execution requires memory resources in addition to those required by serial SQL execution. Additional memory is used for communication and passing data between query server processes and the query coordinator.
Oracle Database allocates memory for query server processes from the shared pool. Tune the shared pool as follows:. Remember that larger values improve performance in multiuser systems, but smaller values use less memory.
You must also take into account that using parallel execution generates more cursors. If the cursor hit ratio is poor, increase the size of the pool. This happens only when you have a large number of distinct queries.
Instead, it leaves some space. When the query runs, Oracle tries to get what it needs. Oracle uses the KB and needs another 16KB when it fails. The error does not report the cumulative amount that is needed. The best way of determining how much more memory is needed is to use the formulas in "Adding Memory for Message Buffers". After you determine the initial setting for the shared pool, you must calculate additional memory requirements for message buffers and determine how much additional space you need for cursors.
The message buffers allow query server processes to communicate with each other. Oracle uses a fixed number of buffers for each virtual connection between producer query servers and consumer query servers. Connections increase as the square of the DOP increases. For this reason, the maximum amount of memory used by parallel execution is bound by the highest DOP allowed on your system.
A simple SQL statement requires only one group. However, if your queries involve subqueries which will be processed in parallel, then Oracle uses an additional group of query server processes. If your system is a cluster or MPP, then you should account for the number of instances because this will increase the DOP. Add this amount to your original setting for the shared pool. However, before setting a value for either of these memory structures, you must also consider additional memory for cursors, as explained in the following section.
Parallel execution plans consume more space in the SQL area than serial execution plans. You should regularly monitor shared pool resource use to ensure that the memory used by both messages and cursors can accommodate your system's processing requirements. The formulas in this section are just starting points. Whether you are using automated or manual tuning, you should monitor usage on an on-going basis to make sure the size of memory is not too large or too small.
To do this, tune the shared pool using the following query:. Compare the high water mark to the parallel execution message pool size to determine if you allocated too much memory. For example, in the first output, the value for large pool as shown in px msg pool is 38,, or 38 MB. The Buffers HWM from the second output is 3,, which when multiplied by a parallel execution message size of 4, is 14,,, or approximately 15 MB.
In this case, the high water mark has reached approximately 40 percent of its capacity. This parameter enables users to wait for an acceptable DOP, depending on the application in use. Setting this parameter to values other than 0 zero causes Oracle to return an error when the requested DOP cannot be satisfied by the system at a given time.
The first group of parameters discussed in this section affects memory and resource consumption for all parallel operations, in particular, for parallel execution. These parameters are:. To control resource consumption, you should configure memory at two levels:.
At the Oracle level, so the system uses an appropriate amount of memory from the operating system. At the operating system level for consistency. On some platforms, you might need to set operating system parameters that control the total amount of virtual memory available, summed across all processes. The SGA is typically part of real physical memory. The SGA is static and of fixed size; if you want to change its size, shut down the database, make the change, and restart the database.
Oracle allocates the shared pool out of the SGA. A large percentage of the memory used in data warehousing operations is more dynamic. This memory comes from process memory PGA , and both the size of process memory and the number of processes can vary greatly. The default value is os specific, but is typically 2K.
This value should be adequate for most applications, however, increasing this value can improve performance. Consider increasing this value if you have adequate free memory in the shared pool or if you have sufficient operating system memory and can increase your shared pool size to accommodate the additional amount of memory required.
Parallel inserts, updates, and deletes require more resources than serial DML operations. For this reason, you may need to increase the value of several additional initialization parameters. These parameters do not affect resources for queries. The parallel coordinator uses the two-phase commit protocol to commit transactions; therefore, the number of transactions being processed increases by the DOP.
The default assumes no parallelism. For example, if you have a DOP of 20, you will have 20 more new server transactions or 40, if you have two server sets and 1 coordinator transaction. If you do not set this parameter, Oracle sets it to a value equal to 1. This discussion does not apply if you are using server-managed undo.
This parameter controls the DOP used when recovering terminated transactions. Terminated transactions are transactions that are active before a system failure. This feature is available by default. One way of doing this is to use fine-grained striping across multiple disks. For example, use a stripe size of 16 KB. A simpler approach is to isolate redo logs on their own disk. This parameter specifies the maximum number of DML locks.
Its value should equal the total number of locks on all tables referenced by all users. Table shows the types of locks acquired by coordinator and parallel execution server processes for different types of parallel DML statements.
Using this information, you can determine the value required for these parameters. Consider a table with partitions running with a DOP of This parameter sets the number of resources that can be locked by the lock manager.
Oracle allocates more enqueue resources as needed. When you perform parallel updates, merges, and deletes, the buffer cache behavior is very similar to any OLTP system running a high volume of updates. Set the database block size when you create the database. If you are creating a new database, use a large block size such as 8 KB or 16 KB. The recommended value for this parameter is eight for 8 KB block size, or four for 16 KB block size.
The default is 8. This parameter determines how many database blocks are read with a single operating system READ call. The upper limit for this parameter is platform-dependent. In this case, each platform uses the highest value possible. Maximum values generally range from 64 KB to 1 MB. Figure illustrates how asynchronous read works. Asynchronous operations are currently supported for parallel table scans, hash joins, sorts, and serial table scans.
However, this feature can require operating system specific configuration and may not be supported on all platforms. See Oracle Database Installation Guide for more information. Determine whether a problem pertains to optimization, such as inefficient plans that might require reanalyzing tables or adding hints, or whether the problem pertains to execution, such as simple operations like scanning, loading, grouping, or indexing running much slower than published guidelines. Determine whether the problem occurs when running in parallel, such as load imbalance or resource bottlenecks, or whether the problem is also present for serial operations.
Performance expectations are based on either prior performance metrics for example, the length of time a given query took last week or on the previous version of Oracle or scaling and extrapolating from serial execution times for example, serial execution took 10 minutes while parallel execution took 5 minutes.
If the performance does not meet your expectations, consider the following questions:. You should use index hints if your system is CPU-bound. After setting your basic goals and answering these questions, you need to consider the following topics:. Does parallel execution's actual performance deviate from what you expected?
If performance is as you expected, could there be an underlying performance problem? Perhaps you have a desired outcome in mind to which you are comparing the current outcome. Perhaps you have justifiable performance expectations that the system does not achieve. You might have achieved this level of performance or a particular execution plan in the past, but now, with a similar environment and operation, the system is not meeting this goal. If performance is not as you expected, can you quantify the deviation?
For data warehousing operations, the execution plan is key. Then, as you analyze and reanalyze the data, upgrade Oracle, and load new data, over time you can compare new execution plans with old plans.
Take this approach either proactively or reactively. Alternatively, you might find that plan performance improves if you use hints. You might want to understand why hints are necessary and determine how to get the optimizer to generate the desired plan without hints. Try increasing the statistical sample size: better statistics can give you a better plan.
See Oracle Database Performance Tuning Guide for information on preserving plans throughout changes to your system, using plan stability and outlines. If there has been a change in the execution plan, determine whether the plan is or should be parallel or serial. Did you analyze all the tables? Perhaps you need to use hints in a few cases. Verify that the hint provides better performance. See utlxplp. If the execution plan is or should be serial, consider the following strategies:. Use an index.
Sometimes adding an index can greatly improve performance. Consider adding an extra column to the index. Perhaps your operation could obtain all its data from the index, and not require a table scan. Verify that the hint provides better results. Compute statistics. If you do not analyze often and you can spare the time, it is a good practice to compute statistics.
This is particularly important if you are performing many joins, and it will result in better plans. Alternatively, you can estimate statistics. If you use different sample sizes, the plan may change. Generally, the higher the sample size, the better the plan. With a large query referencing five or six tables, it may be difficult to determine which part of the query is taking the most time.
You can isolate bottlenecks in the query by breaking it into steps and analyzing each step. If the cause of regression cannot be traced to problems in the plan, the problem must be an execution issue.
For data warehousing operations, both serial and parallel, consider how the plan uses memory. There are several guidelines for using the parallel hint :. See optimizing the degree for a parallel hint. The parallel hint accepts a table name an an optional "degree" argument to tell Oracle how many factotum slave processes to use in the parallel hinted query:. Setting parallel at the table level avoids the need to use hints, but it has the downside of causing the optimizer to start favoring full scan operations.
The recommended approach for using Parallel query is to add a parallel hint to all SQL statements that perform a full-table scan and would benefit from parallel query. Here is an example of a "bad" parallel hint because the parallel hint is with an index hint.
This parallel hint is invalid because the table is aliased and the parallel hint does not use the alias:. You can also have multiple parallel hints within a query. The following example shows two parallel hints within a query, one for each table The number of processors dedicated to service a SQL request is ultimately determined by Oracle Query Manager, but the programmer can specify the upper limit on the number of simultaneous processes.
0コメント