Shulou(Shulou.com)06/01 Report--

Original link http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

Translator: Woqu Technology Hu Hongwei

Although the optimizer needs accurate statistics to select the optimal execution plan, there are scenarios where collecting statistics is difficult, expensive, or not done in a timely manner, and an alternative strategy is needed.

unstable table

An unstable table is a table in which data changes dramatically over time. For example, an order queue table that is empty at the beginning of the day fills up with orders over time, and once an order is processed it is removed from the order table, and the end of the day table becomes empty again.

If you rely on a job that automatically collects statistics to maintain statistics for such tables, statistics will often show that the table is empty. Because this table is empty at night, and the job that collects statistics only starts executing at night. However, over the course of the day, this table may have hundreds or thousands of records.

In this case, it is best to collect a representative set of statistics as the table is populated and lock them in. Locking statistics prevents automatically collected statistics from overwriting them. In addition, you can rely on dynamic sampling to gather statistics for these tables. The optimizer uses dynamic sampling to gather basic statistics about tables when compiling sql statements before optimizing a statement. Although the statistics collected by dynamic sampling are not of the same quality as the statistics collected entirely by the DBMS_STATS package, they are good enough in most cases.

global temporary tables

In the application context, global temporary tables are often used to store intermediate results. Global temporary tables share their definitions at the system level with all users with appropriate permissions, but the data content inside is independent and private between sessions. For this table, physical storage is not allocated until data is inserted.

A global temporary table can be transaction-specific (delete row records on commit) or session-specific (retain row records on commit). Collecting statistics for a transaction-specific table causes the table to be emptied. Conversely, it is possible to collect statistics for a global temporary table (row records are preserved), but in previous database versions this was not a good idea because all sessions using global temporary tables had to share the same set of statistics, so many systems relied on dynamically sampled statistics.

However, in oracle version 12c, it is now possible for each session that uses global temporary tables to have its own separate statistics. Whether statistics on global temp tables are shared depends on a new option in the DBMS_STATS package GLOBAL_TEMP_TABLE_STATS. By default this option is set to sessions, meaning that each session that uses global temporary tables has its own separate statistics. The optimizer uses session statistics first and shared statistics only if session statistics do not exist.

Figure 13: Changing the default: from global temporary tables not sharing statistics to sharing statistics

If you are upgrading from 11g to 12c, but your database application has not been modified to take advantage of session statistics from global temporary tables, you may want to keep global temporary tables in the same way they defaulted before the upgrade by setting the GLOBAL_TEMP_TABLE_STATS option of DBMS_STATS to shared mode (or at least until the application is upgraded).

Online statistics collection automatically creates session-level statistics when populating a global temporary table using a direct path approach (row records are retained at commit), which reduces the need to run additional statistics collection and does not affect statistics for other sessions.

Figure 14: Populating a global temporary table using direct path causes session-level statistics to be automatically collected.

meditope

Intermediate tables are often viewed as part of an ETL process or a complex transaction. These tables are written once, read once, and then cleared or deleted. The cost of collecting statistics in this case outweighs the benefit, since statistics are used only once. Instead, dynamic sampling should be used for these scenarios. It is recommended that you lock the statistics on these intermediate tables to prevent automatic statistics collection tasks from collecting statistics on them again.

Collect other types of statistics

Since the cost-based optimizer is now the only one supported, statistics are required for all tables in the database, including all dictionary tables (those whose owner is sys, system, etc. and which are located in the system, sysaux tablespaces), as well as x$tables used by dynamic performance views.

Data dictionary statistics

Statistics on the data dictionary tables are maintained by automatic statistics collection tasks running on the nightly maintenance window. It is strongly recommended that you allow automatic statistics gathering jobs to maintain data dictionary statistics, even if you turn off automatic statistics gathering jobs on your main app account. You can do this by changing the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using the DBMS_STATS.SET_GLOBAL_PREFS stored procedure.

exec dbms_stats.set_global_prefs('autostats_target','oracle')

Internal Object Statistics

Starting with oracle database 12c, internal object statistics are collected by automatic statistics collection tasks if they have not been collected before. Prior to this release, databases did not collect internal object statistics. Unlike other database tables, dynamic sampling is not automatically applied to sql statements containing x$tables when statistics are missing, in which case the optimizer uses predefined statistics defaults. These defaults may not be representative and may lead to non-optimal execution schedules, which can lead to serious performance issues, and for this reason we strongly recommend that you collect internal object statistics manually.

You can collect internal object statistics using the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS stored procedure. Because of the transient nature of the x$table, it is important to collect internal object statistics when the system has a certain representative load. In large systems, this is not always feasible because collecting statistics requires additional resources. You can't collect internal object statistics at peak system load. You should collect internal object statistics after the system warms up, when the three important types of internal object tables are populated.

Structured data e.g. covering data files, views controlling file contents, etc.

session-based data such as v$session, v$access, etc.

Workload data e.g. v$sql, v$sql_plan etc.

If you make a major database or application upgrade, implement a new module, or change the database configuration, it is strongly recommended that you re-collect internal object statistics. For example, if you increase the size of the SGA, all the x$tables containing buffer cache and shared pool information may change significantly. For example, x$tables for v$buffer_pool or v$shared_pool_advice.

system statistics

System statistics allow the optimizer to more accurately calculate the cost of each step in the execution plan by using information about actual hardware system execution sql, such as CPU speed and IO performance.

System statistics are turned on by default and are automatically initialized with default values that are representative of most systems.

concluding

For oracle optimizer to accurately determine the cost of executing a plan, accurate statistics must be available for all objects (tables and indexes) involved in sql statements, and accurate system statistics must be available. This two-part series of white papers explains in detail what statistics are necessary, how they are used, and the different methods of collecting statistics.

Through a combination of automated statistics gathering tasks and other techniques described in this white paper, a DBA can maintain an accurate set of statistics for their environment to ensure that the optimizer has the necessary information to select an optimal execution plan. Once a statistics collection policy has been implemented, if the policy is to be changed, it must be done in a controlled manner, utilizing key characteristics such as pending statistics to ensure that there is no adverse impact on application performance.

references

Oracle White Paper: Understanding Optimizer Statistics with Oracle Database 12c Release 2

Oracle White Paper: Optimizer with Oracle Database 12c Release 2

Oracle White Paper: Database 12c Real Application Testing Overview

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.