利用TSTATS管理统计信息

### 利用 TSTATS 管理统计信息 在数据库管理中，统计信息对于查询优化器（CBO）生成高效的执行计划至关重要。本文将深入探讨如何使用 TSTATS 包来管理统计信息，特别是处理时间相关的列统计信息和复杂的统计问题。 #### 时间相关列统计信息的挑战 在工资应用程序中，热门值会随时间变化。每月会添加一个新的标准发薪日，同时移除一个旧的发薪日。对于这种基于时间的直方图，手动更新直方图是一个可行的方案。在更新直方图时，手动操作与收集统计信息有很大区别。手动更新时，更改是严格控制的，不会导致执行计划出现不良变化；而收集统计信息则可能带来不确定性。 为了保持直方图的时效性，工资应用程序的维护过程每月至少需要调用一次 TSTATS 包中的自定义过程。这也说明了 TSTATS 通常不是一个即插即用的解决方案，需要根据具体应用进行定制。 #### 相关列和表达式 为了处理谓词中的相关列和表达式，可以设置扩展统计信息。同时，也可以在扩展统计信息上创建直方图，并且扩展统计信息可能是基于时间的。然而，手动设置列组统计信息的直方图并非易事。理论上，可以使用 SYS_OP_COMBINED_HASH 函数来设置直方图的值，但实际操作中可能无法实现。此外，如果直方图中缺少值，CBO 将不会使用扩展统计信息。 在某些复杂场景中，扩展统计信息可能不太实用。例如，在一个应用程序中，有大量相关列，需要创建数百个扩展统计信息。此时，可以考虑伪造某些列的统计信息。以 PAYMENTS 表为例，JOB_DESCRIPTION 列与 PAYGRADE 列高度相关。如果应用程序中的所有查询在涉及 JOB_DESCRIPTION 列的谓词时也会涉及 PAYGRADE 列，那么可以将 JOB_DESCRIPTION 列的 NUM_DISTINCT 值设置为一个非常低的值，以减少 CBO 对基数估计的错误。 #### 使用样本数据处理复杂统计问题 现实生活中的数据具有不可预测性，每月的异常情况比例可能不同。为了消除这种变化，可以手动创建样本数据并收集统计信息。收集样本数据的统计信息比直接伪造统计信息更容易。一旦收集了样本数据的列统计信息，就可以将其复制到实际的表中。 在处理 PAYMENTS 表时，需要对以下列进行分析： 1. **PAYGRADE 列分析**：处理 PAYGRADE 1 员工工资的代码与处理 PAYGRADE 2 到 PAYGRADE 10 员工工资的代码不同。为了生成有效的执行计划，直方图应显示 PAYGRADE 1 的行数比其他工资等级少 100 倍。具体来说，PAYGRADE 1 的选择性为 1/901，PAYGRADE 2 到 PAYGRADE 10 的选择性为 100/901。 2. **PAYMENT_DATE 列分析**：大多数工资支付在每月的 20 号进行（如果 20 号是周末，则为前一个星期五）。如果 PAYMENT_DATE 列没有直方图，正常发薪日的选择性会过低。因此，直方图应显示在 12 个标准发薪日中，每个发薪日的支付约占总支付的 1/12，其他日期的选择性非常小。 3. **PAYGRADE 和 PAYMENT_DATE 相关性**：公司中最资深的两名员工（PAYGRADE 1）在每月的最后一个工作日发薪，而其他员工在 20 号左右发薪。如果处理这两名高管工资的特殊代码包含涉及 PAYGRADE 和 PAYMENT_DATE 的谓词，CBO 可能无法选择合适的执行计划。可以设置 PAYGRADE 和 PAYMENT_DATE 列的列组统计信息，显示在他们的特殊发薪日向 PAYGRADE 1 员工支付两笔工资。 4. **JOB_DESCRIPTION 列分析**：有 13 种工作描述和 10 种工资等级，但并非所有组合都存在。如果应用程序从未独立使用 JOB_DESCRIPTION 列的谓词，可以告诉 CBO 只有两个 JOB_DESCRIPTION 值。这种方法可以避免 CBO 过度降低基数估计。 #### 伪造样本数据 在确定了有问题的列并决定了 CBO 应如何处理这些列后，可以伪造符合模型的数据。以下是创建和填充 SAMPLE_PAYMENTS 表的代码： ```sql CREATE TABLE sample_payments ( paygrade INTEGER ,payment_date DATE ,job_description CHAR (20) ); CREATE OR REPLACE PACKAGE tstats AS -- Other procedures cut PROCEDURE amend_time_based_statistics ( effective_date DATE DEFAULT SYSDATE); -- Other procedures cut END tstats; / CREATE OR REPLACE PACKAGE BODY tstats AS PROCEDURE amend_time_based_statistics ( effective_date DATE DEFAULT SYSDATE) IS distcnt NUMBER; density NUMBER; nullcnt NUMBER; srec DBMS_STATS.statrec; avgclen NUMBER; BEGIN -- Step 1: Remove data from previous run DELETE FROM sample_payments; -- Step 2: Add data for standard pay for standard employees INSERT INTO sample_payments (paygrade, payment_date, job_description) WITH payment_dates AS ( SELECT ADD_MONTHS (TRUNC (effective_date, 'MM') + 19 ,1 - ROWNUM) standard_paydate FROM DUAL CONNECT BY LEVEL <= 12) ,paygrades AS ( SELECT ROWNUM + 1 paygrade FROM DUAL CONNECT BY LEVEL <= 9) ,multiplier AS ( SELECT ROWNUM rid FROM DUAL CONNECT BY LEVEL <= 100) SELECT paygrade ,CASE MOD (standard_paydate - DATE '1001-01-06', 7) WHEN 5 THEN standard_paydate - 1 WHEN 6 THEN standard_paydate - 2 ELSE standard_paydate END payment_date ,'AAA' job_description FROM paygrades, payment_dates, multiplier; -- Step 3: Add data for paygrade 1 INSERT INTO sample_payments (paygrade, payment_date, job_de ```