Alireza Kamrani
09/08/2024
In this post, I presented some essential techniques for investigating and discovering performance issues in Oracle databases.
PROACTIVE METHODS TO GATHER INFORMATION ON A HANGING SYSTEM
On some systems a hang can occur when the DBA is not available to run diagnostics or at times it may be too late to collect the relevant diagnostics. In these cases, the following methods may be used to gather diagnostics:
- As an alternative to the manual collection method notes above, it is also possible to use the HANGFG script as described in the following note to collect the information:
Document 362094.1 HANGFG User Guide
Additionally, this script can collect information with lower impact on the target database. - Procwatcher
Procwatcher is a tool that examines and monitors Oracle database and/or clusterware processes at a specific interval
The following notes explain how to use Procwatcher:
Document 459694.1 Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes - Document 1352623.1 How To Troubleshoot Database Contention With Procwatcher
- OSWatcher contains a built in analyzer that allows the data that has been collected to be automatically analyzed, pro-actively looking for cpu, memory, io and network issues. It is recommended that all users install and run OSW since it is invaluable for looking at issues on the OS and has very little overhead. It can also be extremely useful for looking at OS performance degradation that may be seen when a hang situation occurs.
Refer to the following for download, user guide and usage videos on OSWatcher:Document 301137.1 OSWatcher User Guide.
Current Infrastructure (AWR, Time Model, ASH)
With AWR a rich and complex set of database performance statistics are capture which can then be used by performance advisors and the performance engine of ADDM.
Oracle recommends using ADDM to perform the performance analysis for you rather than analyzing every new statistic and metric available manually.
NOTE:
To enable most of the new statistical gathering and advisors, ensure that the parameter STATISTICS_LEVEL is set to TYPICAL (recommended) or ALL.
Statistics_level =ALL should not be set,specially on production box.
It may cause high cpu utilzation and slowness.
It should only be set on test boxes, under recommedation of Oracle support, or be set for specific session to debug specific issues.
See this for more:
Document 250655.1 How to use the Automatic Database Diagnostic Monitor
Use of the Automatic Workload Repository and related features requires licenses for Oracle Diagnostics Pack which provides automatic performance diagnostic and advanced system monitoring functionality.
Automatic Workload Repository (AWR)
The Automatic Workload Repository (AWR) is an infrastructure that provides information to different manageability components.
AWR consists of two components:
in-memory statistics accessible through V$ dynamic views, and AWR snapshots saved in the database that represent the persistent and historical portion.
Some important things must be highlighted:
- AWR data is flushed from memory to disk using internal kernel calls and a dedicated background process (MMON).
- Data collecting doesn't require manual configuration, like Statspack snapshots in previous releases.
- AWR provides automatic purging of data (7 days by default)
- AWR snapshots can be preserved so that they are not purged. (This can be used to store baselines for further comparisons)
AWR snapshots can be generated at will using the following syntax:
EXECUTE dbms_workload_repository.create_snapshot();
Time Model
Oracle database introduces a different way of store statistics and metrics regarding the time consumed by the different sessions connected. This data is key in a speedy analysis of performance problems, providing a different perspective on time usage within the database.
The following describes these characteristics in detail:
- Time Model
With the new time model the time of every operation is stored in a bucket of time. Thus, one can identify either at database level or at session level how the time distribution looks. Examples of these buckets are: "DB CPU", "DB Time", "background elapsed time", "sql execute elapsed time", etc. See the following views for more details:
V$SYS_TIME_MODEL , V$SESS_TIME_MODEL
The time reported is the total elapsed or CPU time (in microseconds). Any timed operation will buffer at most 5 seconds of time data. Specifically, this means that if a timed operation (such as SQL execution) takes a long period of time to perform, the data published to this view is at most missing 5 seconds of the time accumulated for the operation
1) background elapsed time
2) background cpu time
3) RMAN cpu time (backup/restore)
1) DB time
2) DB CPU
2) connection management call elapsed time
2) sequence load elapsed time
2) sql execute elapsed time
2) parse time elapsed
3) hard parse elapsed time
4) hard parse (sharing criteria) elapsed time
5) hard parse (bind mismatch) elapsed time
3) failed parse elapsed time
4) failed parse (out of shared memory) elapsed time
2) PL/SQL execution elapsed time
2) inbound PL/SQL rpc elapsed time
2) PL/SQL compilation elapsed time
2) Java execution elapsed time
2) repeated bind elapsed time
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SESS_TIME_MODEL.html
- Wait Classes
To enable easier high-level analysis of the wait events and allow a more accurate diagnosis, the time events are grouped now by "wait classes" based on the solution space that applies to fixing a problem with that wait event. See the following views for more details:
V$SYSTEM_WAIT_CLASS, V$SESSION_WAIT_CLASS - Metrics
Metrics are automatically calculated based on basic statistics to track the rates of changes in the activity of the Database. Most of the metrics are calculated every 60 seconds. A good example of metrics is the frequently used "cache hit ratios". Cache hit ratios are calculated and stored in memory. See the following views for more details:
V$SYSMETRIC, V$SESSMETRIC, V$FILEMETRIC, V$EVENTMETRIC, V$WAITCLASSMETRIC, V$METRICNAME, - OS Statistics
CPU and memory statistics are gathered by default. This helps ADDM determine how the database activity is related to the bottleneck found. See the following views for more details:
V$OSSTAT - Active Session History (ASH)
One of the components of the AWR repository is the Active Session History (ASH) which collects samples of active sessions every second (waiting for non-idle events, or on the CPU working) from v$sessions (inactive sessions are not captured). This information provides ADDM with the data to drill-down on problems identified. In the "db file scattered read" example provided, the ASH data helps the advisor identify the specific blocks and files that are referenced the most when the wait event was present.
V$ACTIVE_SESSION_HISTORY - DBA_HIST_ACTIVE_SESS_HISTORY
Performance Tuning Advisors
Automatic Database Diagnostic Monitor (ADDM)
ADDM is a powerful self-diagnostic engine built directly into the kernel of the Oracle database. Using the AWR infrastructure, ADDM is able to holistically analyze the system, identify the major problem in the system (not the symptoms), and recommend corrective action. To focus the attention on the actual problems, ADDM also shows the areas of the system that have no problems.
ADDM is invoked automatically every time that a new AWR snapshot is generated (by default every 60 minutes) and the results of the analysis are stored in the advisory framework tagged with the snapshot id. Therefore, the DBA can find historical executions of the advisor and its recommendations. ADDM can be invoked manually for a different set of snapshots by using the ADDM report generator bundled with the software. To generate a new ADDM report, execute the following script and pick the sample interval for the analysis:
$ORACLE_HOME/rdbms/admin/addmrpt.sql
SQL Tuning Advisor
The optimizer uses the current object's statistics and the SQL structure to generate the execution plan for a SQL statement. It is important that the optimizer determines the execution plan in a timely manner and that the execution of the plan for the statement performs well. Therefore, if the statistics don't reflect the real characteristics of the objects, or the query is poorly written, or there are access structures missing (e.g. indexes) the optimizer may generate a suboptimal plan.
In addition to this "normal" mode of the optimizer, it can also be called in "tuning mode" using the SQL Tuning Advisor (STA). In the tuning mode the optimizer has plenty of time and resources to identify the BEST plan available. When statistics are not available, or when access path cannot be used because lack of indexes defined, the STA is able to collect auxiliary statistics on the objects involved or simulate new execution plan assuming the existence of indexes. Specifically the STA verify the following to generate the best execution plan:
- Detect stale or missing statistics (Statistics check optimization mode): Verify validity of statistics and generate auxiliary statistics as needed to compensate deviations.
- Determine a new execution plan and create a SQL profile (Plan Tuning optimization mode): Verify cost, selectivity and cardinality using dynamic sampling. This ensures that actual execution reflects the calculations made by the optimizer.
- Detect missing indexes(Access analysis optimization mode): Determine the need to create, modify or eliminate access structures (indexes, materialized views)
- Restructure SQL(SQL analysis optimization mode): Identify SQL structure modifications that may lead to a better execution plan.
As a result the SQL Tuning Advisor may provide suggestions is terms of: refreshing object statistics or creating access objects. It can also suggest creating a SQL Profile for the statement when a better plan is identified. Once the SQL profile is created and "applied" to the system this new plan will be used AUTOMATICALLY every time that the statement is executed without having to modify the application.
SQL Access Advisor
The Access Advisor identifies possible access paths to the data using indexes or materialized viewes to improve the performance of access to the data. The SQL Access Advisor takes an actual workload input (or it can derive an hypothetical one) and recommends the access structures needed for a faster execution. To identify SQL statements that can benefit from having better access paths the SQL Access Advisor can take the workload directly from the SQL Cache (V$SQL), or from user-defined workloads (in the form of input tables or SQL Tuning Sets). This advisor take the following into consideration:
- Simultaneous effect of using indexes, materialized views, or the combination of both
- Storage creation parameters
- Combination of single indexes into one index
- Dropping unused indexes
- Modifying existing indexes
SQL plan management
SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans.
SQL plan management prevents performance regressions caused by plan changes.
A secondary goal is to gracefully adapt to changes such as new optimizer statistics or indexes by verifying and accepting only plan changes that improve performance.
Note:SQL plan baselines cannot help when an event has caused irreversible execution plan changes, such as dropping an index.
SQL Plan Baselines
SQL plan management uses a mechanism called a SQL plan baseline, which is a set of accepted plans that the optimizer is allowed to use for a SQL statement.
In this context, a plan includes all plan-related information (for example, SQL plan identifier, set of hints, bind values, and optimizer environment) that the optimizer needs to reproduce an execution plan. The baseline is implemented as a set of plan rows and the outlines required to reproduce the plan. An outline is a set of optimizer hints used to force a specific plan.
The main components of SQL plan management are as follows:
- Plan capture
This component stores relevant information about plans for a set of SQL statements. - Plan selection
This component is the detection by the optimizer of plan changes based on stored plan history, and the use of SQL plan baselines to select appropriate plans to avoid potential performance regressions. - Plan evolution
This component is the process of adding new plans to existing SQL plan baselines, either manually or automatically. In the typical use case, the database accepts a plan into the plan baseline only after verifying that the plan performs well.
New SQL plan management
Real-time SPM quickly detects and repairs SQL performance problems caused by execution plan changes.
If a SQL statement has been running well, but a plan change causes it to perform poorly, real-time SPM detects this immediately. If it establishes that a previous plan will perform better, it will reinstate it using a SQL plan baselines.
This automates what some DBAs do already: they create SQL plan baselines to target individual SQL statements with intermittent performance issues and enforce a plan that is known to be good.
Automatic SQL plan management
Oracle Database 19c introduced automatic SQL plan management. In this release, a background task looks for alternative execution plans if a SQL performance regression is found. Inside the background task, SQL test execution is used to establish which plan is best, and a SQL plan baseline is accepted to enforce it.
♦️Oracle Database 23ai introduces a new 'flavor' of SPM automation called real-time SPM.
It's licensed for Oracle Database 23ai Enterprise Edition and is also available in Oracle Autonomous Database 19c.
How does real-time SPM work?
Over time, the database captures SQL statement performance and execution plan data for the SQL used by the application. New SQL is captured periodically and stored in the automatic SQL tuning set (ASTS).
When a SQL statement is hard parsed, the optimizer uses the ASTS to detect when a brand new plan has been chosen (i.e., the SQL statement is known, but the plan has not been used before). If this is the case, the optimizer compares the performance of the new plan with a previous (reference) plan found in the ASTS. Both plans are captured (and visible in DBA_SQL_PLAN_BASELINES), and the best-performing plan becomes an accepted SQL plan baseline.
A new plan must be executed at least once, allowing the database to gather SQL performance data before real-time SPM compares new plan performance with the reference plan. The foregroundprocess implements these steps: the session parsing and executing the new plan. This is why DBA_SQL_PLAN_BASELINES refers to foreground verification (see below).
How can I tell real-time SPM is working?
The DBA_SQL_PLAN_BASELINES view has the information you need.
When real-time kicks in, the FORGROUND_LAST_VERIFIED column is populated. The column ORIGIN will show the value FOREGROUND-CAPTURE for SQL plan baselines created by real-time SPM. The NOTES column contains XML-based information on what plans were compared and the performance statistics seen.
How is real-time SPM enabled and disabled?
Check your current setting (in Autonomous Database you will see AUTO or ON if you have not changed the default):
SELECT parameter_value spm_status
FROM dba_sql_management_config
WHERE parameter_name = 'AUTO_SPM_EVOLVE_TASK';
You enable real-time SPM using the automatic SPM task parameter:
BEGIN
DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','AUTO');
END;
/
Disable with:
BEGIN
DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','OFF');
END;
/
Can I use it with pre-existing SQL plan baselines, SQL patches or SQL profiles?
Yes. Automatic SPM works even if you have pre-existing plan management objects. Manually created (or auto-captured) SQL plan baselines will control plans as expected, and SQL patches and SQL profiles will interact with execution plans and SQL plan baselines as they have previously.
Should I use it with SPM auto capture?
No. Automatic SPM is distinct from auto capture. You should use real-time SPM with optimizer_capture_sql_plan_baselines set to FALSE.
Does real-time SPM eliminate all SQL performance regressions?
Real-time SPM significantly reduces the incidence of performance regressions, but it won't eliminate SQL performance regressions entirely.
- Like SPM in general, real-time SPM requires the application to use repeatable SQL statements using bind variables. Ad-hoc, highly dynamic SQL is not in scope.
- Real-time SPM requires that a SQL statement had a SQL execution plan that performed well in the past and was captured in the ASTS. The ASTS task captures SQL from the cursor cache periodically. It is enabled automatically when real-time SPM is enabled.
- A poorly performing plan will be executed at least once so that the optimizer can gather SQL performance data.
- In rare cases, real-time SPM may accept a previous plan that ultimately doesn't perform well (even though there are checks to avoid this as much as possible). To resolve this condition, a 'reverse verification' check is performed under certain circumstances. The steps are:
- During hard parse, if the optimizer believes a previously rejected ‘new’ plan is best (based on cost), reverse verification kicks in.
- The accepted plan is nevertheless executed (as expected). Once complete, reverse verification double-checks the performance of this plan against the previously rejected new plan. If the rejected plan’s statistics are better, it is reinstated.
- A hard parse may not happen immediately, so there may be a time lag before reverse verification resolves the issue.
- Non-reproducible plans cannot be used. For example, dropping an index may render an old plan non-reproducible and unusable (if the plan relies on the index). Ultimately, changing the application schema can induce performance regressions that cannot be repaired using plans captured in the past.
- Real-time SPM doesn’t always intervene in cases where plans need to change due to data skew. Instead, adaptive cursor sharing (ACS) will kick in. For this reason, performance regressions may be experienced for some skew-sensitive SQL statements until ACS resolves the correct plan for each set of bind values.
- Before reinstating an old plan, real-time SPM will parse it to confirm that the cost has not changed significantly. If it has, the plan will not be used. This avoids reinstating a plan that was (for example) executed on completely different data. The optimizer avoids reinstating a plan if something has changed significantly since the SQL performance data was captured in ASTS. It also ensures that the 'old' plan is still reproducible.
- Real-time SPM only resolves performance issues accociated with execution plan changes.
If you’re interested in testing real-time SPM, you will find it hard to trick it into action by manipulating optimizer statistics or changing bind values on skewed data. The checks mentioned in bullet point seven will see to that (because changing statistics will change cost estimates). As per bullet point six, ACS is given room to breathe when resolving plans to deal with skewed data.
https://blogs.oracle.com/optimizer/post/what-is-realtime-spm
Reporting :
If reporting is still required then you should use AWR reports instead of statspack
Under some circumstances understanding the underlying statistics may be desirable and the reports bundled with the database can be used for that purpose.
AWR provides a comprehensive report similar to the Statspack report, but with additional information not available in Statspack.
STATSPACK is supported and still available in Oracle new versions. In addition, the AWR report has the following advantages over Statspack:
- The repository is created and maintained automatically
- The report can be generated in text or html mode
- The report displays additional information regarding wait classes, metrics, OS stats, etc.
Statspack is still a capable and usable tool on 19c.
Yes this is old fashioned, but it’s free and it does the job for those DBA who know how to read the reports.
To generate an AWR report simply execute the following script and pick the two snapshots you want to use for the sample (Oracle recommends using snapshots whose timestamps are no more than 60 minutes apart from each other):
$ORACLE_HOME/rdbms/admin/awrrpt.sql
You will also be asked for the format of the report (text or html) along with the report name.
Alireza Kamrani
09/08/2024
No comments:
Post a Comment