QUESTION 48-系统检测工作负载后给出的建议值来生成column group/执行计划/cardinatlity 基数行不准的问题

You want to capture column group usage and gather extended statistics for better cardinality estimates for the CUSTOMERS table in the SH schema. Examine the following steps:
1. Issue the SELECT DBMS_STATS.CREATE_EXTENDED_STATS (‘SH’, ‘CUSTOMERS’) FROM dual statement.
2. Execute the DBMS_STATS.SEED_COL_USAGE (null, ‘SH’, 500) procedure.
3. Execute the required queries on the CUSTOMERS table.
4. Issue the SELECT DBMS_STATS.REPORT_COL_USAGE (‘SH’, ‘CUSTOMERS’) FROM dual statement. Identify the correct sequence of steps.
A. 3, 2, 1, 4
B. 2, 3, 4, 1
C. 4, 1, 3, 2
D. 3, 2, 4, 1
Correct Answer: B

Step 1 (2). Seed column usage
Oracle must observe a representative workload, in order to determine the appropriate column groups. Using the new procedure DBMS_STATS.SEED_COL_USAGE, you tell Oracle how long it should observe the workload.
Step 2: (3) You don't need to execute all of the queries in your work during this window. You can simply run explain plan for some of your longer running queries to ensure column group information is recorded for these queries.
Step 3. (1) Create the column groups
At this point you can get Oracle to automatically create the column groups for each of the tables based on the usage information captured during the monitoring window. You simply have to call the DBMS_STATS.CREATE_EXTENDED_STATS function for each table.This function requires just two arguments, the schema name and the table name. From then on, statistics will be maintained for each column group whenever statistics are gathered on the table.
Note:
* DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the database has processed for a given object.
* The Oracle SQL optimizer has always been ignorant of the implied relationships between data columns within the same table. While the optimizer has traditionally analyzed the distribution of values within a column, he does not collect value-based relationships between columns.
* Creating extended statisticsHere are the steps to create extended statistics for related table columns withdbms_stats.created_extended_stats:
1 - The first step is to create column histograms for the related columns.
2 – Next, we run dbms_stats.create_extended_stats to relate the columns together.
Unlike a traditional procedure that is invoked via an execute (“exec”) statement, Oracle extended statistics are created via a select statement.

 

您希望捕获列组的使用情况，并收集扩展的统计信息，以便更好地估计SH模式中的CUSTOMERS表的基数。检查以下步骤:

1. 发出SELECT DBMS_STATS。CREATE_EXTENDED_STATS (' SH '， ' CUSTOMERS ') FROM dual statement。

2. 执行DBMS_STATS。SEED_COL_USAGE (null， ' SH '， 500)过程。

3.在CUSTOMERS表上执行所需的查询。

4. 发出SELECT DBMS_STATS。REPORT_COL_USAGE (' SH '， ' CUSTOMERS ') FROM dual statement。确定正确的步骤顺序。

A. 3 2 1 4

B. 2 3 4 1

c, 4 1 3 2

D. 3, 2, 4, 1

正确答案是:B

步骤1(2).种子列使用

Oracle必须观察具有代表性的工作负载，以便确定适当的列组。使用新的过程DBMS_STATS。SEED_COL_USAGE告诉Oracle它应该观察工作负载多长时间。

步骤2:(3)在此窗口期间，您不需要执行工作中的所有查询。您可以简单地为一些较长时间运行的查询运行explain plan，以确保为这些查询记录列组信息。

步骤3。(1)创建列组

此时，您可以让Oracle根据监视窗口中捕获的使用信息自动为每个表创建列组。您只需调用DBMS_STATS即可。每个表的CREATE_EXTENDED_STATS函数。这个函数只需要两个参数，模式名和表名。从那时起，每当在表上收集统计信息时，将维护每个列组的统计信息。

注意:

* DBMS_STATS。REPORT_COL_USAGE报告列使用信息，并记录数据库为给定对象处理的所有SQL操作。

* Oracle SQL优化器总是忽略同一表中数据列之间的隐含关系。虽然优化器传统上分析列内值的分布，但他不收集列之间基于值的关系。

以下是创建相关表列扩展统计信息的步骤:

1 -第一步是为相关列创建列直方图。

2 -接下来，我们运行dbms_stats。create_extended_stats将列关联在一起。

与通过execute (" exec ")语句调用的传统过程不同，Oracle扩展统计信息是通过select语句创建的。