dynamic sampling statistics default value

APPLIES TO:

Oracle Database - Personal Edition - Version 10.1.0.2 and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

• Missing or bad statistics on the X$ / fixed tables can lead to performance degradation or hangs. Various X$ views are protected by latches and as a result can be very expensive to query in large / busy systems.
• Most commonly this issue is seen on the underlying X$ tables for DBA_EXTENTS, V$ACCESS, V$RMAN_BACKUP_JOB_DETAILS, and V$RMAN_STATUS, but any fixed table protected through latching can experience this.
• Another commonly seen symptom is extreme TEMP space usage driven by poor plans against the fixed tables.
• RMAN, Data Guard, Streams, and Grid Control make heavy usage of the fixed tables through the DBA/V$ views and so can often bear the brunt of performance issues.
• These are not an exhaustive list of symptoms.  Any item using X$ views and getting bad plans may be experiencing poor plans because of missing or bad statistics.

CAUSE

Latch contention on large fixed objects is expensive, so without proper statistics, performance degradation is expected when sub-optimal plans happen. (in some cases, there are instance wide effects, such as effectively serializing access to the shared pool)  This is more prevalent in very large, or very busy systems, as the number of times the latch is requested, and/or the length of time the latch is held, will increase with load and volume in the X$ being queried through the views.

Starting with 10.1 (apart from X$ tables(by default)), the optimizer uses dynamic sampling when there are no statistics rather than defaulting to rule based optimization as previously. In the X$ tables, if there are no statistics, then default values are used unless support has set particular underscores.  If the default values or dynamic sampling are used on X$'s, sampling time and/or poor plans resulting in longer time spent can result in contention performance problems and possible 'hang-like' symptoms.

NOTE:  Dynamic sampling is not automatically used for X$ tables when optimizer statistics are missing.

See: Oracle Technical Brief Best Practices for Gathering Optimizer Statistics

This means that these performance issues may be expected behavior.

SOLUTION

As such, it is a standard recommendation to gather fixed objects statistics under load so the optimizer can determine optimal paths.  Some load is required so that the database has representative volume/content for as many of the views as possible.

NOTE: Performance degradation may be experienced while the statistics are gathering.
For example, if gathering of the fixed objects statistics is done under heavy load  this can result in the exact same contention issues, leading to performance degradation or hangs.

There are some cases where having no statistics on a fixed object may produce the best plans, but in general, better plans are achieved by gathering statistics on these tables than by not gathering statistics.  Starting in 12c, if there are not existing fixed objects statistics, they may be gathered during maintenance windows by the automated jobs.  Note that this will only be done if there is enough time after all user objects statistics, all dictionary statistics, and then only if the fixed objects statistics do not currently exist.  It is still better to gather them at a time when representative activity is in a system, and all of the below hold true.

The purpose of this note is to address how to plan for fixed object statistics needs and gathering.

1. Having no statistics (and then using defaults) is better than bad statistics, but representative statistics are what should be the strategic goal

2. Representative statistics can be gathered in non-peak hours, one simply has to plan for the different volumes involved.

1. 1. At a high level, there are 3 basic categories of fixed object tables (the X$ tables under the V$ views) to consider when planning for gathering fixed object statistics:
      • (Relatively) Static Data once the instance is warmed -this is mainly structural data, for example, views covering datafiles, controlfile contents, etc
      • Data that changes session based on the number of sessions connected, for example: v$session, v$access, etc.
      • Volatile data, based on workload mix -- v$sql, v$sql_plan, etc
   2. Choose a time of day that will give a representative sampling for as many of the above categories as possible. If gathering under peak load is not possible, then try to gather after the instance has been warmed up / running for some time so that "Static" data is relatively fixed.  If the instance has a high number of sessions under normal workload, attempt to gather the statistics when there are still a large number of sessions connected (even if the sessions are idle).
   3. There are some fixed tables that are simply very volatile by nature and it will be extremely hard to get accurate statistics on. In general though, in the case of these volatile fixed tables, better plans are achieved by gathering statistics on these tables than by not gathering statistics. 

3. Since its not possible to predict the likelihood of individual environments experiencing noticeable performance degradation, testing is strongly encourage. Performance Degradation has not been able to be replicated in Oracle test instances, but potential for such problems is known to exist.

4. Plan for performance degradation while gathering the statistics. It is possible the degradation could appear to be a hang which lasts the length of stats gathering. It is also possible the instance will experience little to no degradation, particularly on smaller or less loaded systems.  Key points to consider are volume of data in the fixed tables and level of concurrency in the system. 

5. If there are severe issues, diagnose what table gathering is 'stuck' on and lock that table's statistics as a short term workaround. From a long term solution standpoint, it would be preferable to have the statistics but having a running system is likely to be the priority. 

6. If no statistics are gathered, in most cases the instance reverts to default values to determine statistics for the plan when the query is parsed. Dynamic sampling would only engage in specific scenarios where support has set underscores.  Plans may be poor OR change on re-parse as a result, and the instance may or may not have accurate statistics in this manner.  For volatile tables (see 2.3 above) it may be extremely difficult to generate accurate statistics.
 

NOTE: Dynamic sampling is not automatically used for X$ tables when optimizer statistics are missing.

See: Oracle Technical Brief Best Practices for Gathering Optimizer Statistics

7. While X$ tables last only the life of the instance, the statistics, when gathered, are stored to disk and used until deleted or replaced.  They do NOT need to be regathered on instance restart.  They only need to be regathered if workload changes significantly. For example:

原文地址：https://blog.csdn.net/jnrjian/article/details/142416301

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！