db_file_multiblock_read_count parameter in oracle
================================================
"Multiple block sizes were implemented to transport tablespaces."
That is what they primarily were implemented to facilitate it .
--------------------------------------------------
Questions:-
===================
1) We have LOB column tables and I see lot of chainig/migration.
If we move the LOB column tables to a bigger blocksize tablespace.
Will that not reduce the migration?
2) I think the The max block size possible is 32k on 9i.
How do reduce the migration for such big column tables as average
row length is more than 100k?
Ans:-
answer from Tom katys :
===================
1) lob columns are stored inline ONLY if they are 4000 bytes or less.
If they are bigger than a varchar2 - the are automagically moved out
of line into the lob segment.
It could be that a simple alter table t move with a larger pctfree
to set aside sufficient space for updates is what is needed
(and that moving to a larger blocksize without changing pctfree
would not *fix a darn thing*).
If you are using lobs - I feel pretty confident in stating
"the average row length is NOT 100K!!!!",
the lobs are stored OUT OF LINE, in the lob segment,
when they exceed 4000 bytes.
(and if they were 100k, just give up, I don't care what your
block size is, it won't matter)
to move indexes to a tablespace of 16k, helps me to keep memory
only for indexes, and control better the index memory usage.
For me (a small database) makes sense.
Consider (if ANYTHING) the keep and recycle pools, but
using multiple block sizes for "tuning" isn't really something recommended.
I've not advised to use multiple blocksizes, use the keep and recycle pools
if you want to segregate things out.
use keep and recycle pools if you want multiple buffer caches
Showing posts with label Performance Tuning. Show all posts
Showing posts with label Performance Tuning. Show all posts
Thursday, May 13, 2010
Sunday, April 11, 2010
How to know the table/index uses information in oracle
How to know the table/index uses information
=======================================
1. by enabling AUDIT .
2. BY QUERY THE FOLLOWING
SELECT *
FROM v$segment_statistics
WHERE owner = 'USER_NAME'
AND object_name = 'TABLE_NAME'
select owner,object_name,sum((case when STATISTIC# = '0' then value end )) "logical reads"
,sum((case when STATISTIC# = '1' then value end )) "buffer busy waits"
,sum((case when STATISTIC# = '2' then value end )) "gc buffer busy"
,sum((case when STATISTIC# = '3' then value end )) "db block changes"
,sum((case when STATISTIC# = '4' then value end )) "physical reads"
,sum((case when STATISTIC# = '5' then value end )) "physical writes"
,sum((case when STATISTIC# = '6' then value end )) "physical reads direct"
,sum((case when STATISTIC# = '7' then value end )) "physical writes direct"
,sum((case when STATISTIC# = '8' then value end )) "null"
,sum((case when STATISTIC# = '9' then value end )) "gc cr blocks received"
,sum((case when STATISTIC# = '10' then value end )) "gc current blocks received"
,sum((case when STATISTIC# = '11' then value end )) "ITL waits"
,sum((case when STATISTIC# = '12' then value end )) "row lock waits"
,sum((case when STATISTIC# = '14' then value end )) "space used"
,sum((case when STATISTIC# = '15' then value end )) "space allocated"
,sum((case when STATISTIC# = '17' then value end )) "segment scans"
from v$segment_statistics
where owner='USER_NAME'
---and object_name='TABLE_NAME'
and object_type='TABLE'
group by owner,object_name
order by "space used" desc--object_name
3. OR ONLY FOR INSERT/UPDATE/DELETE
SELECT * FROM select * from USER_tab_modifications;
=======================================
1. by enabling AUDIT .
2. BY QUERY THE FOLLOWING
SELECT *
FROM v$segment_statistics
WHERE owner = 'USER_NAME'
AND object_name = 'TABLE_NAME'
select owner,object_name,sum((case when STATISTIC# = '0' then value end )) "logical reads"
,sum((case when STATISTIC# = '1' then value end )) "buffer busy waits"
,sum((case when STATISTIC# = '2' then value end )) "gc buffer busy"
,sum((case when STATISTIC# = '3' then value end )) "db block changes"
,sum((case when STATISTIC# = '4' then value end )) "physical reads"
,sum((case when STATISTIC# = '5' then value end )) "physical writes"
,sum((case when STATISTIC# = '6' then value end )) "physical reads direct"
,sum((case when STATISTIC# = '7' then value end )) "physical writes direct"
,sum((case when STATISTIC# = '8' then value end )) "null"
,sum((case when STATISTIC# = '9' then value end )) "gc cr blocks received"
,sum((case when STATISTIC# = '10' then value end )) "gc current blocks received"
,sum((case when STATISTIC# = '11' then value end )) "ITL waits"
,sum((case when STATISTIC# = '12' then value end )) "row lock waits"
,sum((case when STATISTIC# = '14' then value end )) "space used"
,sum((case when STATISTIC# = '15' then value end )) "space allocated"
,sum((case when STATISTIC# = '17' then value end )) "segment scans"
from v$segment_statistics
where owner='USER_NAME'
---and object_name='TABLE_NAME'
and object_type='TABLE'
group by owner,object_name
order by "space used" desc--object_name
3. OR ONLY FOR INSERT/UPDATE/DELETE
SELECT * FROM select * from USER_tab_modifications;
Sunday, March 21, 2010
Oracle Database 10g CBO (Cost based optimizer) and cost model
Oracle Database 10g CBO (Cost based optimizer) and cost model
The CBO chooses the best path for your queries, based on what it knows about your data and by leveraging Oracle database features such as bitmap indexes, function-based indexes, hash joins, index-organized tables, and partitioning, whereas the RBO just follows established rules (heuristics). With the release of Oracle Database 10g, the RBO's obsolescence is official and the CBO has been significantly improved yet again.
The CBO now has a special tuning mode that is used by many other Oracle Database 10g features, including the SQL Tuning Advisor and SQL Access Advisor.
When a SQL Query submitted
SQL queries submitted to the Oracle database engine first run through the parser, which checks syntax and analyzes semantics. The result of this run-through is a set of query blocks that is sent to the optimizer, which comprises three main functional subsystems—specifically, the query transformation engine, the estimator, and the plan generator.
1. Query transformation engine
The query transformation engine applies heuristics- or rules-based algorithms to the query blocks. Some common transformations include select-join, group-by, and distinct-view merging and materialized view rewrite.
Oracle performs numerous query transformations, changing and rewriting queries into equivalent but more optimal forms. Some of these transformations are heuristics- or rules-based transformations, meaning they are based on the structural properties of the query—number of tables, types of join and filters, presence of grouping clauses, for example—and not on selectivity, cardinality, distribution, join order, or related costs of various database operations.
Other transformations, such as materialized view rewrite, star transformation, and OR-expansion, are cost-based transformations in which a query is copied, transformed, and costed repeatedly (and recursively, each time using a new set of transformations), until at the end of the process, one or more transformations is selected to be applied to the original query, if it results in an optimal cost. Here's a summary of the most common transformations:
Heuristics-based transformations.
- Select-join
- Select-join-project view merging
- Group-by and distinct-view merging
- Subquery unnesting
- Filter predicate push-down in a view
- Predicate move-around
- Common subexpression elimination
- Join predicate push down
- Outer to inner join conversion
- Subsumed subquery removal through window function,
Cost-based transformations.
- OR expansion—converts a query with ORs in the WHERE-clause into a UNION ALL of several queries without ORs, which is highly beneficial when the ORs refer to restrictions of different tables
- Materialized view rewrite
- Star transformation
- Conversion of set operator intersect into join and minus into anti-join
A large number of query blocks and interdependence of the transformations themselves add to the complexity of any cost-based transformation strategy. With Oracle Database 10g, the transformation engine has been redesigned as an extensible framework that can support new algorithms as needed, to ensure that Oracle can always quickly select the optimal transformation.
In addition, the Oracle Database 10g transformation framework supports several new state space search algorithms that ensure that the optimizer finds the best plan quickly, even when transformations are complex.
2. The estimator
The estimator measures cardinality (the number of rows); selectivity (the fraction of rows from a row set filtered by predicate); and cost, defined as resources (disk I/O, CPU usage, and memory) of the various operations (scanning a table or joining tables together, for instance) that will yield the data. The accuracy of these estimates depends on statistics—the object statistics and now, in Oracle Database 10g, system statistics. SQL profiles specific to any query are also used at this point to effectively estimate costs.
Oracle Database 10g CBO Uses a New Cost Model
More important,However, the cost model itself has changed. In Oracle9i Database, the CBO used an I/O cost model that evaluated everything primarily in terms of single block reads, largely ignoring CPU costs (or using constants to estimate CPU costs). In Oracle Database 10g, the cost model now includes system statistics that realistically capture CPU and I/O on your system with respect to the Oracle workload.
In Oracle Database 10g, the default cost model is CPU plus I/O, in which the cost unit is time: The CBO estimates execution time for a query by estimating the number of I/O operations, the type of I/O operations, and the number of CPU cycles the database will perform while executing the query. These estimates depend on the existence of system statistics, which the CBO uses to convert the number of CPU cycles and the number of I/Os into execution time. (Note that some operations, such as bitmap merge, sort, or hash joins, may not require any I/O but that all operations include a CPU component.)
Oracle Database 10g gathers two types of system statistics—statistics captured without a workload (noworkload) and statistics captured with a workload. Noworkload statistics capture I/O system performance—average I/O seek time and transfer speed—and CPU speed. When gathering noworkload statistics, the CBO issues sample reads of different sizes from the database's datafiles; it times every read and then uses statistical methods to compute average seek time and transfer speed. This takes from a few seconds to a few minutes. The CBO computes CPU speed in millions of cycles per second.
Workload statistics make the CBO aware of the workload. The system statistics captured during workload conditions identify whether the system is I/O- or CPU-bound; the CBO uses the data to adjust the cost of the plans accordingly. To gather workload statistics, execute these commands at the start and the conclusion of your workload:
dbms_stats.gather_system_stats(gathering_mode=>'start') dbms_stats.gather_system_stats(gathering_mode=>'stop') You can see the value by following query.
SQL> select sname, pname, pval1from sys.aux_stats$; CPUSPEEDNW, IOSEEKTIM, and IOTFRSPEED are noworkload statistics; SREADTIM, MREADTIM, CPUSPEED, MBRC, MAXTHR, and SLAVETHR represent workload statistics.
If both workload and noworkload statistics are available, the optimizer uses workload statistics.
- SREADTIM—single block read time (milliseconds)—is the average time Oracle takes to read a single block.
- MREADTIM—multiblock read time (milliseconds)—is the average time taken to read sequentially.
- MBRC—multiblock read count—is the blocks, on average, read during multiblock sequential reads. The CBO uses MBRC instead of thedb_ multiblock_read_count parameter during query optimization to compute costs for table and fast full index scans.
- MAXTHR—maximum I/O system throughput—is captured only if the database runs parallel queries.
- SLAVETHR—maximum slave I/O throughput—is captured only if the database runs parallel queries.
Another benefit of the Oracle Database 10g cost model is that it enables the CBO to use other techniques to create the fastest plan possible—for example, by reordering predicates in a query. Because the CBO knows the costs associated with a given query in terms of CPU and because the main ingredient in the cost of a predicate is CPU, the CBO can calculate costs with alternative predicate orderings and reorganize the predicates in the most efficient order.
For example, assume that for the following select statement, b <> 0 returns all rows in table t:
select * from t where a > 0 and b <> If predicate order is a > 0 and b <> 0, then the predicate a > 0 will be evaluated just once.
The algorithms for ordering predicates consider the costs of various combinations of the predicate order and selectivity when determining the best order. This feature provides considerable improvement, especially for nonselective and expensive predicates.
3. The plan generator
The plan generator recombines the query in various ways, constructing alternative plans by using different join methods and join orders. The more table joins in the original query, the more alternative plans are generated and the longer the time it may take the CBO to find the "best" plan. Conversely, if there are few joins in a particular query or the joins are in the best order to begin with, the CBO will find the optimal plan quickly.
Ultimately, the CBO picks the best plan—the one with the lowest estimated cost relative to other plans. But precisely how the CBO measures the cost of processing any SQL statement—the cost model—is influenced by many factors, including several initialization parameters , starting with the optimizer_mode. The optimizer_mode sets the overall performance goal for the CBO in terms of throughput (the minimal time to return all rows) versus response time (the minimal time to return the first rows).
Conclusion
Oracle's cost-based optimizer is a strategic subsystem of Oracle Database. Given that the CBO improves with each new release of Oracle Database, you'll want to consider the ramifications that changes to the CBO may have on your Oracle database applications. If you're migrating an existing system to Oracle Database 10g, be sure to back up all the statistics your system uses for daily operations and test these statistics out with your applications in a test environment built around the new version of the database before moving into production.
Annexure - A
Initialization Parameters that Affect the CBO
select * from v$sys_optimizer_env ;
Optimizer_mode sets an overall performance goal for the CBO in terms of throughput versus response time. In Oracle Database 10g, the default setting (all_rows), primes the CBO to select for overall best throughput—the optimizer will choose the plan that delivers all the rows in a query in the shortest possible overall time. On the other hand, the first_rows setting will cause the optimizer to provide fastest response time by choosing the plan that delivers an initial subset of rows more quickly, and the remainder of the query in a longer period of time than the all_rows setting.
The "first_rows_n" setting (new as of Oracle9i Database) lets you balance the possible variances in this continuum (throughput versus response) more directly, by letting you set the number of rows to 1, 10, 100, or 1000. In Oracle Database 10g, if the query contains a rownum predicate, the CBO automatically switches to first_rows_n mode, deriving the value of n from the rownum predicate.
Another important optimizer initialization parameter is optimizer_dynamic_sampling. Statistics on database objects (size of tables, indexes, number of rows in tables, number of distinct values in columns, percentage of table below the high-water mark, and so on) play a key role in estimating costs (see Table 2), and if the CBO can't find up-to-date statistics to work with during the cost estimation phase of statement processing, it can dynamically sample statistical data at that time—if this parameter is set. The default level in Oracle Database 10g is 2, which means the CBO will use dynamic sampling on nonanalyzed tables to collect necessary statistics from them while optimizing the query.
Annexure - B
SQL profile
================
One of the artifacts produced by the SQL tuning mode is a SQL profile—auxiliary statistics specific to a given statement—that can be persisted to the data dictionary and used at runtime (during normal CBO operations) to improve SQL performance. SQL profiles are particularly useful for packaged applications (or any application for which you cannot access the source code).
SQL profiles use sampling to collect additional information; partial execution techniques to verify and adjust cost estimates; and execution history information for the SQL statement to modify parameter settings, if needed.
Saturday, October 10, 2009
Oracle Performance Improvement Method
Oracle Performance Improvement Method
========================================
1. Perform the following initial standard checks:
--------------------------------------------------
A.
Get candid feedback from users. Determine the performance project’s scope
and subsequent performance goals, as well as performance goals for the
future. This process is key in future capacity planning.
B.
Get a full set of operating system, database, and application statistics from the
system when the performance is both good and bad. If these are not available,
then get whatever is available. Missing statistics are analogous to missing
evidence at a crime scene: They make detectives work harder and it is more
time-consuming.
C.
Sanity-check the operating systems of all systems involved with user
performance. By sanity-checking the operating system, you look for hardware
or operating system resources that are fully utilized. List any over-used
resources as symptoms for analysis later. In addition, check that all hardware
shows no errors or diagnostics.
2. Check for the top ten most common mistakes with Oracle, and determine if any of
these are likely to be the problem. List these as symptoms for later analysis. These
are included because they represent the most likely problems. ADDM
automatically detects and reports nine of these top ten issues.
3. Build a conceptual model of what is happening on the system using the symptoms
as clues to understand what caused the performance problems.
4. Propose a series of remedy actions and the anticipated behavior to the system,
then apply them in the order that can benefit the application the most. ADDM
produces recommendations each with an expected benefit. A golden rule in
performance work is that you only change one thing at a time and then measure
the differences. Unfortunately, system downtime requirements might prohibit
such a rigorous investigation method. If multiple changes are applied at the same
time, then try to ensure that they are isolated so that the effects of each change can be independently validated.
5. Validate that the changes made have had the desired effect, and see if the user's
perception of performance has improved. Otherwise, look for more bottlenecks,
and continue refining the conceptual model until your understanding of the
application becomes more accurate.
6. Repeat the last three steps until performance goals are met or become impossible
due to other constraints.
This method identifies the biggest bottleneck and uses an objective approach to
performance improvement. The focus is on making large performance improvements
by increasing application efficiency and eliminating resource shortages and
bottlenecks. In this process, it is anticipated that minimal (less than 10%) performance gains are made from instance tuning, and large gains (100% +) are made from isolating application inefficiencies.
Following analysis assumes that statistics for both the operating system and the database have been gathered.
1. Is the response time/batch run time acceptable for a single user on an empty or
lightly loaded system?
If it is not acceptable, then the application is probably not coded or designed
optimally, and it will never be acceptable in a multiple user situation when system
resources are shared. In this case, get application internal statistics, and get SQL
Trace and SQL plan information. Work with developers to investigate problems in
data, index, transaction SQL design, and potential deferral of work to
batch/background processing.
2. Is all the CPU being utilized?
If the kernel utilization is over 40%, then investigate the operating system for
network transfers, paging, swapping, or process thrashing. Otherwise, move onto
CPU utilization in user space. Check to see if there are any non-database jobs
consuming CPU on the system limiting the amount of shared CPU resources, such
as backups, file transforms, print queues, and so on. After determining that the
database is using most of the CPU, investigate the top SQL by CPU utilization.
These statements form the basis of all future analysis. Check the SQL and the
transactions submitting the SQL for optimal execution. Oracle provides CPU
statistics in V$SQL and V$SQLSTATS.
If the application is optimal and there are no inefficiencies in the SQL execution,
consider rescheduling some work to off-peak hours or using a bigger system.
3. At this point, the system performance is unsatisfactory, yet the CPU resources are
not fully utilized.
In this case, you have serialization and unscalable behavior within the server. Get the WAIT_EVENTS statistics from the server, and determine the biggest
serialization point. If there are no serialization points, then the problem is most
likely outside the database, and this should be the focus of investigation.
Elimination of WAIT_EVENTS involves modifying application SQL and tuning
database parameters. This process is very iterative and requires the ability to drill
down on the WAIT_EVENTS systematically to eliminate serialization points.
========================================
1. Perform the following initial standard checks:
--------------------------------------------------
A.
Get candid feedback from users. Determine the performance project’s scope
and subsequent performance goals, as well as performance goals for the
future. This process is key in future capacity planning.
B.
Get a full set of operating system, database, and application statistics from the
system when the performance is both good and bad. If these are not available,
then get whatever is available. Missing statistics are analogous to missing
evidence at a crime scene: They make detectives work harder and it is more
time-consuming.
C.
Sanity-check the operating systems of all systems involved with user
performance. By sanity-checking the operating system, you look for hardware
or operating system resources that are fully utilized. List any over-used
resources as symptoms for analysis later. In addition, check that all hardware
shows no errors or diagnostics.
2. Check for the top ten most common mistakes with Oracle, and determine if any of
these are likely to be the problem. List these as symptoms for later analysis. These
are included because they represent the most likely problems. ADDM
automatically detects and reports nine of these top ten issues.
3. Build a conceptual model of what is happening on the system using the symptoms
as clues to understand what caused the performance problems.
4. Propose a series of remedy actions and the anticipated behavior to the system,
then apply them in the order that can benefit the application the most. ADDM
produces recommendations each with an expected benefit. A golden rule in
performance work is that you only change one thing at a time and then measure
the differences. Unfortunately, system downtime requirements might prohibit
such a rigorous investigation method. If multiple changes are applied at the same
time, then try to ensure that they are isolated so that the effects of each change can be independently validated.
5. Validate that the changes made have had the desired effect, and see if the user's
perception of performance has improved. Otherwise, look for more bottlenecks,
and continue refining the conceptual model until your understanding of the
application becomes more accurate.
6. Repeat the last three steps until performance goals are met or become impossible
due to other constraints.
This method identifies the biggest bottleneck and uses an objective approach to
performance improvement. The focus is on making large performance improvements
by increasing application efficiency and eliminating resource shortages and
bottlenecks. In this process, it is anticipated that minimal (less than 10%) performance gains are made from instance tuning, and large gains (100% +) are made from isolating application inefficiencies.
Following analysis assumes that statistics for both the operating system and the database have been gathered.
1. Is the response time/batch run time acceptable for a single user on an empty or
lightly loaded system?
If it is not acceptable, then the application is probably not coded or designed
optimally, and it will never be acceptable in a multiple user situation when system
resources are shared. In this case, get application internal statistics, and get SQL
Trace and SQL plan information. Work with developers to investigate problems in
data, index, transaction SQL design, and potential deferral of work to
batch/background processing.
2. Is all the CPU being utilized?
If the kernel utilization is over 40%, then investigate the operating system for
network transfers, paging, swapping, or process thrashing. Otherwise, move onto
CPU utilization in user space. Check to see if there are any non-database jobs
consuming CPU on the system limiting the amount of shared CPU resources, such
as backups, file transforms, print queues, and so on. After determining that the
database is using most of the CPU, investigate the top SQL by CPU utilization.
These statements form the basis of all future analysis. Check the SQL and the
transactions submitting the SQL for optimal execution. Oracle provides CPU
statistics in V$SQL and V$SQLSTATS.
If the application is optimal and there are no inefficiencies in the SQL execution,
consider rescheduling some work to off-peak hours or using a bigger system.
3. At this point, the system performance is unsatisfactory, yet the CPU resources are
not fully utilized.
In this case, you have serialization and unscalable behavior within the server. Get the WAIT_EVENTS statistics from the server, and determine the biggest
serialization point. If there are no serialization points, then the problem is most
likely outside the database, and this should be the focus of investigation.
Elimination of WAIT_EVENTS involves modifying application SQL and tuning
database parameters. This process is very iterative and requires the ability to drill
down on the WAIT_EVENTS systematically to eliminate serialization points.
Monday, September 28, 2009
Emergency Performance improbment Method
The Emergency Performance Method is as follows:
1. Survey the performance problem and collect the symptoms of the performance
problem. This process should include the following:
■ User feedback on how the system is underperforming. Is the problem
throughput or response time?
■ Ask the question, "What has changed since we last had good performance?"
This answer can give clues to the problem. However, getting unbiased
answers in an escalated situation can be difficult. Try to locate some
reference points, such as collected statistics or log files, that were
taken before and after the problem.
■ Use automatic tuning features to diagnose and monitor the problem. In
addition, you can use Oracle Enterprise Manager performance features to
identify top SQL and sessions.
2. Sanity-check the hardware utilization of all components of the application system.
Check where the highest CPU utilization is, and check the disk, memory usage,
and network performance on all the system components. This quick process
identifies which tier is causing the problem. If the problem is in the application,
then shift analysis to application debugging. Otherwise, move on to database
server analysis.
3. Determine if the database server is constrained on CPU or if it is spending time
waiting on wait events. If the database server is CPU-constrained, then investigate
the following:
■ Sessions that are consuming large amounts of CPU at the operating system
level and database; check V$SESS_TIME_MODEL for database CPU usage
■ Sessions or statements that perform many buffer gets at the database level;
check V$SESSTAT and V$SQLSTATS
■ Execution plan changes causing sub-optimal SQL execution; these can be
difficult to locate
■ Incorrect setting of initialization parameters
■ Algorithmic issues as a result of code changes or upgrades of all components
If the database sessions are waiting on events, then follow the wait events listed in
V$SESSION_WAIT to determine what is causing serialization.
The V$ACTIVE_SESSION_HISTORY view contains a sampled history of session activity which can be used to perform diagnosis even after an incident has ended and the system has returned to normal operation. In cases of massive contention for the library cache,it might not be possible to logon or submit SQL to the database. In this case, use historical data to determine why there is suddenly contention on this latch. If most waits are for I/O, then examine V$ACTIVE_SESSION_HISTORY to determine the SQL being run by the sessions that are performing all of the inputs and outputs.
4. Apply emergency action to stabilize the system. This could involve actions that
take parts of the application off-line or restrict the workload that can be applied to the system. It could also involve a system restart or the termination of job in
process. These naturally have service level implications.
5. Validate that the system is stable. Having made changes and restrictions to the
system, validate that the system is now stable, and collect a reference set of
statistics for the database. Now follow the rigorous performance method described
earlier in this book to bring back all functionality and users to the system. This
process may require significant application re-engineering before it is complete.
1. Survey the performance problem and collect the symptoms of the performance
problem. This process should include the following:
■ User feedback on how the system is underperforming. Is the problem
throughput or response time?
■ Ask the question, "What has changed since we last had good performance?"
This answer can give clues to the problem. However, getting unbiased
answers in an escalated situation can be difficult. Try to locate some
reference points, such as collected statistics or log files, that were
taken before and after the problem.
■ Use automatic tuning features to diagnose and monitor the problem. In
addition, you can use Oracle Enterprise Manager performance features to
identify top SQL and sessions.
2. Sanity-check the hardware utilization of all components of the application system.
Check where the highest CPU utilization is, and check the disk, memory usage,
and network performance on all the system components. This quick process
identifies which tier is causing the problem. If the problem is in the application,
then shift analysis to application debugging. Otherwise, move on to database
server analysis.
3. Determine if the database server is constrained on CPU or if it is spending time
waiting on wait events. If the database server is CPU-constrained, then investigate
the following:
■ Sessions that are consuming large amounts of CPU at the operating system
level and database; check V$SESS_TIME_MODEL for database CPU usage
■ Sessions or statements that perform many buffer gets at the database level;
check V$SESSTAT and V$SQLSTATS
■ Execution plan changes causing sub-optimal SQL execution; these can be
difficult to locate
■ Incorrect setting of initialization parameters
■ Algorithmic issues as a result of code changes or upgrades of all components
If the database sessions are waiting on events, then follow the wait events listed in
V$SESSION_WAIT to determine what is causing serialization.
The V$ACTIVE_SESSION_HISTORY view contains a sampled history of session activity which can be used to perform diagnosis even after an incident has ended and the system has returned to normal operation. In cases of massive contention for the library cache,it might not be possible to logon or submit SQL to the database. In this case, use historical data to determine why there is suddenly contention on this latch. If most waits are for I/O, then examine V$ACTIVE_SESSION_HISTORY to determine the SQL being run by the sessions that are performing all of the inputs and outputs.
4. Apply emergency action to stabilize the system. This could involve actions that
take parts of the application off-line or restrict the workload that can be applied to the system. It could also involve a system restart or the termination of job in
process. These naturally have service level implications.
5. Validate that the system is stable. Having made changes and restrictions to the
system, validate that the system is now stable, and collect a reference set of
statistics for the database. Now follow the rigorous performance method described
earlier in this book to bring back all functionality and users to the system. This
process may require significant application re-engineering before it is complete.
Is all the CPU being utilized ?
Is all the CPU being utilized?
If the kernel utilization is over 40%, then investigate the operating system for
network transfers, paging, swapping, or process thrashing. Otherwise, move onto
CPU utilization in user space. Check to see if there are any non-database jobs
consuming CPU on the system limiting the amount of shared CPU resources, such
as backups, file transforms, print queues, and so on. After determining that the
database is using most of the CPU, investigate the top SQL by CPU utilization.
These statements form the basis of all future analysis. Check the SQL and the
transactions submitting the SQL for optimal execution. Oracle provides CPU
statistics in V$SQL and V$SQLSTATS.
you have serialization and unscalable behavior within the server. Get
the WAIT_EVENTS statistics from the server, and determine the biggest serialization point.
If the kernel utilization is over 40%, then investigate the operating system for
network transfers, paging, swapping, or process thrashing. Otherwise, move onto
CPU utilization in user space. Check to see if there are any non-database jobs
consuming CPU on the system limiting the amount of shared CPU resources, such
as backups, file transforms, print queues, and so on. After determining that the
database is using most of the CPU, investigate the top SQL by CPU utilization.
These statements form the basis of all future analysis. Check the SQL and the
transactions submitting the SQL for optimal execution. Oracle provides CPU
statistics in V$SQL and V$SQLSTATS.
you have serialization and unscalable behavior within the server. Get
the WAIT_EVENTS statistics from the server, and determine the biggest serialization point.
Performance Checklist for new Application
Performance Checklist for new Application
=========================================
For a new Application, build a list of tasks that—if performed correctly—will
increase the chance of optimal performance in production and—if there is a
problem—enable rapid debugging of the application:
1. When you create the control file for the production database, allow for growth by
setting MAXINSTANCES, MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, and
MAXLOGHISTORY to values higher than what you anticipate for the rollout. This
results in more disk space usage and bigger control files, but saves time later
should these need extension in an emergency.
2. Set block size to the value used to develop the application. Export the schema
statistics from the development/test environment to the production database if the
testing was done on representative data volumes and the current SQL execution
plans are correct.
3. Set the minimal number of initialization parameters. Ideally, most other
parameters should be left at default. If there is more tuning to perform, this shows
up when the system is under load.
4. Be prepared to manage block contention by setting storage options of database
objects. Tables and indexes that experience high INSERT/UPDATE/DELETE rates
should be created with automatic segment space management. To avoid
contention of rollback segments, automatic undo management should be used.
5. All SQL statements should be verified to be optimal and their resource usage
understood.
6. Validate that middleware and programs that connect to the database are efficient
in their connection management and do not logon/logoff repeatedly.
7. Validate that the SQL statements use cursors efficiently. Each SQL statement
should be parsed once and then executed multiple times. The most common
reason this does not happen is because bind variables are not used properly and
WHERE clause predicates are sent as string literals. If the precompilers are used to
develop the application, then make sure that the parameters MAXOPENCURSORS,
HOLD_CURSOR, and RELEASE_CURSOR have been reset from the default values
prior to precompiling the application.
8. Validate that all schema objects have been correctly migrated from the
development environment to the production database. This includes tables,
indexes, sequences, triggers, packages, procedures, functions, Java objects,
synonyms, grants, and views. Ensure that any modifications made in testing are
made to the production system.
9. As soon as the system is rolled out, establish a baseline set of statistics from the database and operating system. This first set of statistics validates or corrects any assumptions made in the design and rollout process.
10. Start anticipating the first bottleneck (there will always be one) and follow the
Oracle performance method to make performance improvement.
=========================================
For a new Application, build a list of tasks that—if performed correctly—will
increase the chance of optimal performance in production and—if there is a
problem—enable rapid debugging of the application:
1. When you create the control file for the production database, allow for growth by
setting MAXINSTANCES, MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, and
MAXLOGHISTORY to values higher than what you anticipate for the rollout. This
results in more disk space usage and bigger control files, but saves time later
should these need extension in an emergency.
2. Set block size to the value used to develop the application. Export the schema
statistics from the development/test environment to the production database if the
testing was done on representative data volumes and the current SQL execution
plans are correct.
3. Set the minimal number of initialization parameters. Ideally, most other
parameters should be left at default. If there is more tuning to perform, this shows
up when the system is under load.
4. Be prepared to manage block contention by setting storage options of database
objects. Tables and indexes that experience high INSERT/UPDATE/DELETE rates
should be created with automatic segment space management. To avoid
contention of rollback segments, automatic undo management should be used.
5. All SQL statements should be verified to be optimal and their resource usage
understood.
6. Validate that middleware and programs that connect to the database are efficient
in their connection management and do not logon/logoff repeatedly.
7. Validate that the SQL statements use cursors efficiently. Each SQL statement
should be parsed once and then executed multiple times. The most common
reason this does not happen is because bind variables are not used properly and
WHERE clause predicates are sent as string literals. If the precompilers are used to
develop the application, then make sure that the parameters MAXOPENCURSORS,
HOLD_CURSOR, and RELEASE_CURSOR have been reset from the default values
prior to precompiling the application.
8. Validate that all schema objects have been correctly migrated from the
development environment to the production database. This includes tables,
indexes, sequences, triggers, packages, procedures, functions, Java objects,
synonyms, grants, and views. Ensure that any modifications made in testing are
made to the production system.
9. As soon as the system is rolled out, establish a baseline set of statistics from the database and operating system. This first set of statistics validates or corrects any assumptions made in the design and rollout process.
10. Start anticipating the first bottleneck (there will always be one) and follow the
Oracle performance method to make performance improvement.
SQL Parsing in Oracle
Parsing is the process of interpreting a SQL statement and
creating an execution plan for it. This process has many phases, including syntax
checking, security checking, execution plan generation, and loading shared
structures into the shared pool.
There are two types of parse operations:
■ Hard Parsing: A SQL statement is submitted for the first time, and no match is
found in the shared pool. Hard parses are the most resource-intensive and
unscalable, because they perform all the operations involved in a parse.
■ Soft Parsing: A SQL statement is submitted for the first time, and a match is
found in the shared pool. The match can be the result of previous execution by
another user. The SQL statement is shared, which is good for performance.
However, soft parses are not ideal, because they still require syntax and
security checking, which consume system resources.
To ensure that SQL is shared, use bind variables and do not use string literals with SQL statements.
For example:
Statement with string literals:
--------------------------------
SELECT * FROM employees
WHERE last_name LIKE 'KING';
Statement with bind variables:
-------------------------------
SELECT * FROM employees
WHERE last_name LIKE :1;
creating an execution plan for it. This process has many phases, including syntax
checking, security checking, execution plan generation, and loading shared
structures into the shared pool.
There are two types of parse operations:
■ Hard Parsing: A SQL statement is submitted for the first time, and no match is
found in the shared pool. Hard parses are the most resource-intensive and
unscalable, because they perform all the operations involved in a parse.
■ Soft Parsing: A SQL statement is submitted for the first time, and a match is
found in the shared pool. The match can be the result of previous execution by
another user. The SQL statement is shared, which is good for performance.
However, soft parses are not ideal, because they still require syntax and
security checking, which consume system resources.
To ensure that SQL is shared, use bind variables and do not use string literals with SQL statements.
For example:
Statement with string literals:
--------------------------------
SELECT * FROM employees
WHERE last_name LIKE 'KING';
Statement with bind variables:
-------------------------------
SELECT * FROM employees
WHERE last_name LIKE :1;
Different Index Type
There are several index types available, and each index has benefits for certain
situations.
The following list gives performance ideas associated with each index type.
1. B-Tree Indexes
-------------
These indexes are the standard index type, and they are excellent for
primary key and highly-selective indexes. Used as concatenated indexes, B-tree
indexes can be used to retrieve data sorted by the index columns.
2. Bitmap Indexes
--------------
These indexes are suitable for low cardinality data. Through
compression techniques, they can generate a large number of rowids with minimal
I/O. Combining bitmap indexes on non-selective columns allows efficient AND and OR
operations with a great number of rowids with minimal I/O. Bitmap indexes are
particularly efficient in queries with COUNT(), because the query can be satisfied within the index.
3. Function-based Indexes
--------------------
These indexes allow access through a B-tree on a value derived from a function on the base data. Function-based indexes have some limitations with regards to the use of nulls, and they require that you have the query optimizer enabled.
Function-based indexes are particularly useful when querying on composite columns
to produce a derived result or to overcome limitations in the way data is stored in the database. An example is querying for line items in an order exceeding a certain value derived from (sales price - discount) x quantity, where these were columns in the table. Another example is to apply the UPPER function to the data to allow case-insensitive searches.
4. Partitioned Indexes
-----------------
Partitioning a global index allows partition pruning to take place
within an index access, which results in reduced I/Os. By definition of good range or
list partitioning, fast index scans of the correct index partitions can result in very fast query times.
5. Reverse Key Indexes
-------------------
These indexes are designed to eliminate index hot spots on insert
applications. These indexes are excellent for insert performance, but they are limited in that they cannot be used for index range scans.
6. Index-Organized Tables or Appending Columns to an Index
--------------------------------------------------------
One of the easiest ways to speed up a query is to reduce the number of logical I/Os by eliminating a table access from the execution plan. This can be done by appending to the index all columns referenced by the query. These columns are the select list
columns, and any required join or sort columns. This technique is particularly useful
in speeding up online applications response times when time-consuming I/Os are
reduced. This is best applied when testing the application with properly sized data for the first time.
The most aggressive form of this technique is to build an index-organized table (IOT). However, you must be careful that the increased leaf size of an IOT does not
undermine the efforts to reduce I/O.
situations.
The following list gives performance ideas associated with each index type.
1. B-Tree Indexes
-------------
These indexes are the standard index type, and they are excellent for
primary key and highly-selective indexes. Used as concatenated indexes, B-tree
indexes can be used to retrieve data sorted by the index columns.
2. Bitmap Indexes
--------------
These indexes are suitable for low cardinality data. Through
compression techniques, they can generate a large number of rowids with minimal
I/O. Combining bitmap indexes on non-selective columns allows efficient AND and OR
operations with a great number of rowids with minimal I/O. Bitmap indexes are
particularly efficient in queries with COUNT(), because the query can be satisfied within the index.
3. Function-based Indexes
--------------------
These indexes allow access through a B-tree on a value derived from a function on the base data. Function-based indexes have some limitations with regards to the use of nulls, and they require that you have the query optimizer enabled.
Function-based indexes are particularly useful when querying on composite columns
to produce a derived result or to overcome limitations in the way data is stored in the database. An example is querying for line items in an order exceeding a certain value derived from (sales price - discount) x quantity, where these were columns in the table. Another example is to apply the UPPER function to the data to allow case-insensitive searches.
4. Partitioned Indexes
-----------------
Partitioning a global index allows partition pruning to take place
within an index access, which results in reduced I/Os. By definition of good range or
list partitioning, fast index scans of the correct index partitions can result in very fast query times.
5. Reverse Key Indexes
-------------------
These indexes are designed to eliminate index hot spots on insert
applications. These indexes are excellent for insert performance, but they are limited in that they cannot be used for index range scans.
6. Index-Organized Tables or Appending Columns to an Index
--------------------------------------------------------
One of the easiest ways to speed up a query is to reduce the number of logical I/Os by eliminating a table access from the execution plan. This can be done by appending to the index all columns referenced by the query. These columns are the select list
columns, and any required join or sort columns. This technique is particularly useful
in speeding up online applications response times when time-consuming I/Os are
reduced. This is best applied when testing the application with properly sized data for the first time.
The most aggressive form of this technique is to build an index-organized table (IOT). However, you must be careful that the increased leaf size of an IOT does not
undermine the efforts to reduce I/O.
Need to know for Oracle Database architecture
The following questions should stimulate thought on Oracle Database architecture.
These questions demonstrate how business requirements
can influence the architecture, ease of implementation, and overall
performance and availability of a system.
For example:
■ How many users will the system support?
■ What will be the user interaction method?
■ Where are the users located?
■ What is the network speed?
■ How much data will the user access, and how much of that data is largely read
only?
■ What is the user response time requirement?
■ Do users expect 24 hour service?
■ Must all changes be made in real time?
■ How big will the database be?
■ What is the required throughput of business transactions?
■ What are the availability requirements?
■ Do skills exist to build and administer this application?
■ What compromises will be forced by budget constraints?
These questions demonstrate how business requirements
can influence the architecture, ease of implementation, and overall
performance and availability of a system.
For example:
■ How many users will the system support?
■ What will be the user interaction method?
■ Where are the users located?
■ What is the network speed?
■ How much data will the user access, and how much of that data is largely read
only?
■ What is the user response time requirement?
■ Do users expect 24 hour service?
■ Must all changes be made in real time?
■ How big will the database be?
■ What is the required throughput of business transactions?
■ What are the availability requirements?
■ Do skills exist to build and administer this application?
■ What compromises will be forced by budget constraints?
Hardware Components for oracle Database
The main hardware components include:
■ CPU
■ Memory
■ I/O Subsystem
■ Network
CPU :
There can be one or more CPUs, and they can vary in processing power from
simple CPUs found in hand-held devices to high-powered server CPUs. Sizing of
other hardware components is usually a multiple of the CPUs on the system.
Memory
Database and application servers require considerable amounts of memory to
cache data and avoid time-consuming disk access.
I/O Subsystem
The I/O subsystem can vary between the hard disk on a client PC and
high performance disk arrays. Disk arrays can perform thousands of I/Os each second
and provide availability through redundancy in terms of multiple I/O paths and hot
pluggable mirrored disks.
Note: These factors are based on Oracle Server Performance
group’s experience of tuning unscalable systems.
Network
All computers in a system are connected to a network, from a modem line to a
high speed internal LAN. The primary concerns with network specifications are
bandwidth (volume) and latency (speed).
■ CPU
■ Memory
■ I/O Subsystem
■ Network
CPU :
There can be one or more CPUs, and they can vary in processing power from
simple CPUs found in hand-held devices to high-powered server CPUs. Sizing of
other hardware components is usually a multiple of the CPUs on the system.
Memory
Database and application servers require considerable amounts of memory to
cache data and avoid time-consuming disk access.
I/O Subsystem
The I/O subsystem can vary between the hard disk on a client PC and
high performance disk arrays. Disk arrays can perform thousands of I/Os each second
and provide availability through redundancy in terms of multiple I/O paths and hot
pluggable mirrored disks.
Note: These factors are based on Oracle Server Performance
group’s experience of tuning unscalable systems.
Network
All computers in a system are connected to a network, from a modem line to a
high speed internal LAN. The primary concerns with network specifications are
bandwidth (volume) and latency (speed).
Parameters Affecting Memory Usage
The memory required by any one Oracle session depends on many factors.
Typically the major contributing factors are:
■ Number of open cursors
■ Memory used by PL/SQL, such as PL/SQL tables
■ SORT_AREA_SIZE initialization parameter
In Oracle, the PGA_AGGREGATE_TARGET initialization parameter gives greater control
over a session's memory usage.
Typically the major contributing factors are:
■ Number of open cursors
■ Memory used by PL/SQL, such as PL/SQL tables
■ SORT_AREA_SIZE initialization parameter
In Oracle, the PGA_AGGREGATE_TARGET initialization parameter gives greater control
over a session's memory usage.
Oracle Performance Tuning Features
Automatic Oracle Performance Tuning Features
--------------------------------------------
■ Automatic Workload Repository (AWR) collects, processes, and maintains
performance statistics for problem detection and self-tuning purposes.
■ Automatic Database Diagnostic Monitor (ADDM) analyzes the information
collected by the AWR for possible performance problems with the Oracle
database.
■ SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL
statements without modifying any statements.
■ SQLAccess Advisor provides advice on materialized views, indexes, and
materialized view logs.
■ End to End Application tracing identifies excessive workloads on the system by
specific user, service, or application component.
■ Server-generated alerts automatically provide notifications when impending
problems are detected. See Oracle Database Administrator's Guide for information
about monitoring the operation of the database with server-generated alerts.
■ Additional advisors that can be launched from Oracle Enterprise Manager, such as
memory advisors to optimize memory for an instance. The memory advisors are
commonly used when automatic memory management is not set up for the
database. Other advisors are used to optimize mean time to recovery (MTTR),
shrinking of segments, and undo tablespace settings. For information about using
advisors that are available with Oracle Enterprise Manager, see Oracle Database 2
Day + Performance Tuning Guide.
■ The Database Performance page in Oracle Enterprise Manager displays host,
instance service time, and throughput information for real time monitoring and
diagnosis. The page can be set to refresh automatically in selected intervals or
manually. For information about the Database Performance page, see Oracle
Database 2 Day + Performance Tuning Guide.
(Manually) Oracle Performance Tuning Features
------------------------------------------
■ V$ Performance Views
The V$ views are the performance information sources used by all Oracle performance
tuning tools. The V$ views are based on memory structures initialized at instance
startup. The memory structures, and the views that represent them, are automatically
maintained by Oracle throughout the life of the instance. See Oracle Database Reference for information about dynamic performance views
--------------------------------------------
■ Automatic Workload Repository (AWR) collects, processes, and maintains
performance statistics for problem detection and self-tuning purposes.
■ Automatic Database Diagnostic Monitor (ADDM) analyzes the information
collected by the AWR for possible performance problems with the Oracle
database.
■ SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL
statements without modifying any statements.
■ SQLAccess Advisor provides advice on materialized views, indexes, and
materialized view logs.
■ End to End Application tracing identifies excessive workloads on the system by
specific user, service, or application component.
■ Server-generated alerts automatically provide notifications when impending
problems are detected. See Oracle Database Administrator's Guide for information
about monitoring the operation of the database with server-generated alerts.
■ Additional advisors that can be launched from Oracle Enterprise Manager, such as
memory advisors to optimize memory for an instance. The memory advisors are
commonly used when automatic memory management is not set up for the
database. Other advisors are used to optimize mean time to recovery (MTTR),
shrinking of segments, and undo tablespace settings. For information about using
advisors that are available with Oracle Enterprise Manager, see Oracle Database 2
Day + Performance Tuning Guide.
■ The Database Performance page in Oracle Enterprise Manager displays host,
instance service time, and throughput information for real time monitoring and
diagnosis. The page can be set to refresh automatically in selected intervals or
manually. For information about the Database Performance page, see Oracle
Database 2 Day + Performance Tuning Guide.
(Manually) Oracle Performance Tuning Features
------------------------------------------
■ V$ Performance Views
The V$ views are the performance information sources used by all Oracle performance
tuning tools. The V$ views are based on memory structures initialized at instance
startup. The memory structures, and the views that represent them, are automatically
maintained by Oracle throughout the life of the instance. See Oracle Database Reference for information about dynamic performance views
Excessive CPU usage Problem
Excessive CPU usage usually means that there is little idle CPU on the system.
This could be caused by an inadequately-sized system, by untuned SQL
statements, or by inefficient application programs.
This could be caused by an inadequately-sized system, by untuned SQL
statements, or by inefficient application programs.
Latch contention Problem
Rarely is latch contention tunable by reconfiguring the instance. Rather, latch
contention usually is resolved through application changes.
contention usually is resolved through application changes.
Slow physical I/O Problems
Generally, this is caused by poorly-configured disks. However, it could also be
caused by a significant amount of unnecessary physical I/O on those disks issued
by poorly-tuned SQL.
caused by a significant amount of unnecessary physical I/O on those disks issued
by poorly-tuned SQL.
Oracle Database 10g new performance Features
The new and updated performance features in Oracle Database 10g Release 2 (10.2)
include:
■ Active Session History Reports
---------------------------------
Active Session History (ASH) reports contains ASH information that can be used
to identify blocker and waiter identities and their associated transaction identifiersand SQL for a specified duration.
■ Automatic PGA Memory Management
---------------------------------
A new view has been added to monitor dynamic PGA memory usage for each
Oracle process. See for descriptions of the V$PROCESS_MEMORY view.
■ Automatic Shared Memory Management
---------------------------------
Automatic Shared Memory Management simplifies the configuration of System
Global Area (SGA) memory-related parameters through self-tuning algorithms.
Automatic Shared Memory Management has been enhanced and the streams pool
is now auto tuned as part of the automatic SGA management.
■ Automatic Tuning of Multiblock Read Count
----------------------------------
The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now
automatically tuned to use a default value when this parameter is not set
explicitly. see for more information on the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
■ Automatic Workload Repository Reports
----------------------------------
Automatic Workload Repository (AWR) reports display statistics for a range of
snapshot Ids. Two new reports, awrsqrpt.sql and awrsqrpi.sql, have been
added to view statistics for a particular SQL statement.
■ Configurable Automatic Workload Repository SQL Collection
---------------------------------
The Automatic Workload Repository (AWR) collects, processes, and maintains
performance statistics for problem detection and self-tuning purposes, including
SQL statements that are producing the highest load on the system. This feature
has been enhanced to enable the configuration of the number of Top SQL to flush
for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory,
and Version Count).
■ Database Replay
-------------------
You can capture a database workload on a production system and replay it on a
test system to ensure that system changes, such as database upgrades, will yield
desired results.
■ Enhanced End to End Application Tracing
------------------------------------
End to End Application Tracing identifies the source of an excessive workload,
such as a high load SQL statement. This feature has been enhanced to enable SQL
tracing on a session, instance wide, or an entire database level.
■ Improved System Statistics
----------------------------
The V$SYSSTAT view has added rows to capture the total number of physical
I/O’s performed by any Oracle process. Additionally, a new event has been added to reduce the memory used formaintaining statistics on events in the Other wait class.
■ SQL Access Advisor
---------------------------
The SQL Access Advisor and its related DBMS_ADVISOR package now recommend
function-based indexes, the recommendation process can now be interrupted and
there are improvements in the Oracle Enterprise Manager.
■ SQL Performance Analyzer
---------------------------
The SQL Performance Analyzer enables you to forecast the impact of system
changes on SQL performance by testing these changes using a SQL workload on a
test system.
■ SQL Profiles
--------------------------
The DBMS_SQLTUNE package now also offers the ability to allow SQL with text
differing only in its literal values to share a SQL Profile by normalizing literal text values to bind variables.
■ SQL Tuning Advisor
---------------------------
Note: Only workload capture is currently supported in this release.
Captured workloads can be preprocessed and replayed on Oracle
Database 11g Release 1 (11.1) and subsequent releases.
Note: Only SQL workload capture is currently supported in this
release. Captured SQL workloads can be executed, and their
performance can be measured and compared, on Oracle Database 11g
Release 1 (11.1) and subsequent releases.
It is now possible to monitor the execution progress of the SQL Tuning Advisor
using the new V$ADVISOR_PROGRESS view.
■ SQL Tuning Sets
---------------------
SQL Tuning Sets can now be exported to or imported from another system using
the DBMS_SQLTUNE package procedures.
■ V$SQLSTATS View
---------------------
A new view, V$SQLSTATS, returns performance statistics for SQL cursors.
V$SQLSTATS contains a subset of columns that appear in V$SQL and
V$SQLAREA. However, the V$SQLSTATS view differs from V$SQL and
V$SQLAREA in that it is faster, more scalable, and has a greater data retention. It is recommended that V$SQLSTATS be used in place of V$SQL when fetching
statistics for SQL cursors.
include:
■ Active Session History Reports
---------------------------------
Active Session History (ASH) reports contains ASH information that can be used
to identify blocker and waiter identities and their associated transaction identifiersand SQL for a specified duration.
■ Automatic PGA Memory Management
---------------------------------
A new view has been added to monitor dynamic PGA memory usage for each
Oracle process. See for descriptions of the V$PROCESS_MEMORY view.
■ Automatic Shared Memory Management
---------------------------------
Automatic Shared Memory Management simplifies the configuration of System
Global Area (SGA) memory-related parameters through self-tuning algorithms.
Automatic Shared Memory Management has been enhanced and the streams pool
is now auto tuned as part of the automatic SGA management.
■ Automatic Tuning of Multiblock Read Count
----------------------------------
The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now
automatically tuned to use a default value when this parameter is not set
explicitly. see for more information on the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
■ Automatic Workload Repository Reports
----------------------------------
Automatic Workload Repository (AWR) reports display statistics for a range of
snapshot Ids. Two new reports, awrsqrpt.sql and awrsqrpi.sql, have been
added to view statistics for a particular SQL statement.
■ Configurable Automatic Workload Repository SQL Collection
---------------------------------
The Automatic Workload Repository (AWR) collects, processes, and maintains
performance statistics for problem detection and self-tuning purposes, including
SQL statements that are producing the highest load on the system. This feature
has been enhanced to enable the configuration of the number of Top SQL to flush
for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory,
and Version Count).
■ Database Replay
-------------------
You can capture a database workload on a production system and replay it on a
test system to ensure that system changes, such as database upgrades, will yield
desired results.
■ Enhanced End to End Application Tracing
------------------------------------
End to End Application Tracing identifies the source of an excessive workload,
such as a high load SQL statement. This feature has been enhanced to enable SQL
tracing on a session, instance wide, or an entire database level.
■ Improved System Statistics
----------------------------
The V$SYSSTAT view has added rows to capture the total number of physical
I/O’s performed by any Oracle process. Additionally, a new event has been added to reduce the memory used formaintaining statistics on events in the Other wait class.
■ SQL Access Advisor
---------------------------
The SQL Access Advisor and its related DBMS_ADVISOR package now recommend
function-based indexes, the recommendation process can now be interrupted and
there are improvements in the Oracle Enterprise Manager.
■ SQL Performance Analyzer
---------------------------
The SQL Performance Analyzer enables you to forecast the impact of system
changes on SQL performance by testing these changes using a SQL workload on a
test system.
■ SQL Profiles
--------------------------
The DBMS_SQLTUNE package now also offers the ability to allow SQL with text
differing only in its literal values to share a SQL Profile by normalizing literal text values to bind variables.
■ SQL Tuning Advisor
---------------------------
Note: Only workload capture is currently supported in this release.
Captured workloads can be preprocessed and replayed on Oracle
Database 11g Release 1 (11.1) and subsequent releases.
Note: Only SQL workload capture is currently supported in this
release. Captured SQL workloads can be executed, and their
performance can be measured and compared, on Oracle Database 11g
Release 1 (11.1) and subsequent releases.
It is now possible to monitor the execution progress of the SQL Tuning Advisor
using the new V$ADVISOR_PROGRESS view.
■ SQL Tuning Sets
---------------------
SQL Tuning Sets can now be exported to or imported from another system using
the DBMS_SQLTUNE package procedures.
■ V$SQLSTATS View
---------------------
A new view, V$SQLSTATS, returns performance statistics for SQL cursors.
V$SQLSTATS contains a subset of columns that appear in V$SQL and
V$SQLAREA. However, the V$SQLSTATS view differs from V$SQL and
V$SQLAREA in that it is faster, more scalable, and has a greater data retention. It is recommended that V$SQLSTATS be used in place of V$SQL when fetching
statistics for SQL cursors.
Subscribe to:
Posts (Atom)