- Shopping Bag ( 0 items )
Get peak performance from your Oracle Database.
When it comes to getting optimal performance from your Oracle database,turn to the only advanced Oracle guide to carry Oracle Corporation's official seal. Oracle8 Advanced Tuning & Administration gives you the high-level tools necessary to expertly manage and tune your database throughout its lifecycle. The author addresses...
Ships from: fallbrook, CA
Usually ships in 1-2 business days
Get peak performance from your Oracle Database.
When it comes to getting optimal performance from your Oracle database,turn to the only advanced Oracle guide to carry Oracle Corporation's official seal. Oracle8 Advanced Tuning & Administration gives you the high-level tools necessary to expertly manage and tune your database throughout its lifecycle. The author addresses the advanced techniques and strategies that enable administrators and application developers to successfully manage a database's performance—no matter how quickly it grows.
Building on the basics to provide a migration path for DBAs and tuners,Oracle8 Advanced Tuning & Administration covers:
As a bonus,this book also includes 100 DBA practice questions to help you evaluate your personal training needs. Take your Oracle database to the next level of power and efficiency with Oracle8 Advanced Tuning & Administration.
How to Manage the Query Server Pool
A query, if run at different times, could get processed by a different number of query server processes depending on the resources available in the query server pool. Oracle maintains the query server pool based on the settings of several init.ora parameters. The parameters are
- PARALLEL-MIN_SERVERS. This parameter sets the number of query server processes started when the instance starts. These query server processes are available to the query pool, eliminating the performance penalties of frequent query server process startups and shutdowns. PARALLEL_MIN_SERVERS sets the minimum size of the query server pool at any time.
- PARALLEL_MAX_SERVERS. As needed, Oracle adds query server processes to the query server pool. Oracle stops adding new query server processes when the PARALLEL_MAX_SERVERS setting is reached. This parameter sets the maximum size of the query server pool.
- PARALLEL_SERVER_IDLE_TIME. When a query server process is no longer in use, it goes into an idle state and is available for subsequent operations. If a query server process is idle for longer than the PARALLEL_SERVER_IDLE_TIME setting (expressed in minutes), the query server process will be terminated (provided the size of the query server pool remains at least PARALLEL_MIN_SERVERS).
- PARALLEL_MIN_PERCENT. This parameter allows you to prevent queries from running if the requested degree of parallelism is not available. If the requested number of query server processes is not available, and the PARALLEL_MIN_PERCENT parameter is set, Oracle calculates theminimum allowable number of server processes. For example, if eight query server processes are requested, and PARALLEL_MIN_PERCENT is set to 50, then at least four query server processes must be available for the query. lf the number of query server processes available is less than the calculated minimum, the query terminates with an error. If the calculated minimum number of query server processes is available, the query will run with the available query server processes, reducing the effective parallelism of the query.
PARALLEL_MIN_SERVERS, PARALLEL_MAX_SERVERS, and PARALLEL_SERVER_IDLE_TIME determine how many query server processes are in the query server pool at any time. The number of available query server processes will be bounded at the low end by PARALLEL_MIN_SERVERS, and at the high end by PARALLEL_MAX_SERVERS. PARALLEL_SERVER_IDLE_TIME helps minimize the number of unused query server processes in the query server pool. You can monitor the changes in the size of the query server pool via the techniques described in the "How to Monitor the Parallel Query Option" section later in this chapter.
If the number of available query server processes is less than the number requested and the PARALLEL_MIN_PERCENT parameter is not set, only the available number of query server processes is used by the operation, reducing the effective parallelism of the statement. If the number of available query server processes is not sufficient to parallelize the query, Oracle processes the query sequentially. If you have set a value for PARALLEL_MIN_PERCENT, you can prevent queries from running unless they can be properly parallelized using the available resources. You may choose to use this option if you have a limited time window in which to run the query and would rather have the query fail than overrun the available time window.
If you are using Oracle Parallel Server, your queries can be parallelized across multiple instances on different nodes. The PARALLEL_DEFAULT_MAX_INSTANCES parameter defines the maximum number of instances involved in processing a parallelized query. The query can be parallelized across multiple instances, in addition to being parallelized within a single instance. You can involve multiple CPUs and multiple SGAs in the processing of a single query.
Defining the Degree of Parallelism at the Table Level
You can enable parallelism at two levels: the table level and the query level. To enable PQO for any allowable statement (see the earlier "What Are the Options?" section) that uses a specific table or cluster, specify the degree of parallelism via the create table, alter table, create cluster, or alter cluster command. Degree of parallelism settings at the table level override any instance settings.
To set the degree of parallelism at the table level, use the parallel clause of the alter table and create table commands. in the following example, the COMPANY table is defined to have a degree of parallelism of four.
alter table COMPANY
Queries that use the COMPANY table will now use a degree of parallelism of four. if you are using the Oracle Parallel Server, you can split the processing of a query across multiple instances. You can use the instances portion of the parallel clause to define the number of instances to involve in processing the query, as shown in the following listing. Up to four processes per instance will service the parallelized operation, for an effective 20-way parallelism.
alter table COMPANY
parallel(degree 4 instances 5);
You may wish to define a table as always being processed serially-with no parallelization of its queries. You can indicate a serially processed table via the noparallel clause of the alter table and create table commands, as shown in the following listing.
alter table COMPANY noparallel;
You may want to specify a table as noparallel if it is not large enough to benefit greatly from parallelization. If this is the case, parallelizing queries against the table wastes the resources available in the query server pool. Since the query server pool is limited in size for the instance, you need to make sure that the available resources are properly used by the tables in the database.
If you do not set a degree parameter for a table, Oracle will determine the degree of parallelism to use based on the size of the table, the distribution of the data across the available disks, and the number of CPUs available on the server....
Part II: Administration of a Growing Database.
Planning for Your Database Growth.
Memory and CPU Tuning Issues.
Hardware Tuning Issues.
Managing the Space Inside Your Database.
Database Shutdown/Startup Kit.
Part III: Tuning a Growing Database.
Managing for Performance.
EXPLAINing SQL Treatment Tuning.
The Top 13 SQL Tuning Tips.
Part IV: Advanced Tuning Options.
When All Else Fails: New and Improved Tuning Options.
Part V: DBA Practice Questions.
Oracle DBA Practice Questions.
Analysis of the Practice Questions.