利用TSTATS管理统计信息

### 利用 TSTATS 管理统计信息 在数据库管理中，统计信息的管理对于查询性能至关重要。TSTATS 是一种管理对象统计信息的方法，与 Oracle 建议的标准方法截然不同，但它能有效将执行计划的更改纳入变更控制流程。 #### 1. 查询示例与统计信息调整 先来看两个查询示例： ```sql SELECT COUNT (*) FROM statement_part t WHERE transaction_date = DATE '2013-01-13'; ``` 执行计划如下： | Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop | | --- | --- | --- | --- | --- | --- | --- | | 0 | SELECT STATEMENT | | 1 | 29 (0)| | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | PARTITION RANGE SINGLE| | 50 | 29 (0)| 5 | 5 | |* 3 | INDEX FAST FULL SCAN | STATEMENT_PART_IX1 | 50 | 29 (0)| 5 | 5 | ```sql SELECT /*+ full(t) */ COUNT (*) FROM statement_part t WHERE transaction_date = DATE '2013-01-06'; ``` 执行计划如下： | Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop | | --- | --- | --- | --- | --- | --- | --- | | 0 | SELECT STATEMENT | | 1 | 69 (0)| | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | PARTITION RANGE SINGLE| | 50 | 69 (0)| 2 | 2 | |* 3 | TABLE ACCESS FULL | STATEMENT_PART | 50 | 69 (0)| 2 | 2 | 收集和调整统计信息后，全表扫描的原始成本因舍入问题有所变化。但将 `TSTATS.ADJUST_TABLE_STATS` 纳入分区维护过程后，全表扫描成本不变，无提示的查询仍使用索引。 #### 2. 临时表的挑战 临时表对 TSTATS 方法来说是个难题，主要有以下两个原因： - 难以通过独立作业轻松收集临时表的统计信息，因为作业执行时临时表可能为空。 - 永久表大小通常不会有数量级的变化，而临时表可能一次存储 100 万行，不久后仅存储 3 行。 当临时表在 ETL 过程中用作暂存表时，这些问题尤为明显。实际上，永久表有时也会在 ETL 过程中用作暂存表，上述问题同样适用于此类表。 #### 3. 动态采样的利弊 动态采样是处理基数大幅变化的一种实用方法，尽管它与 TSTATS 的理念相悖。除非明确禁用，否则对没有对象统计信息的表（包括临时表）会自动进行动态采样。 动态采样的优点： - 非常智能，能理解列相关性等，无需担心扩展统计信息。 - 解析语句时很可能得到不错的执行计划，且下次解析时若临时表内容不同，也能得到适合新工作负载的计划。 不过，动态采样也有潜在缺点： - 动态采样得到的执行计划和性能无法准确预测。 - 一个会话创建的执行计划可能在临时表内容不同时被同一或不同会话使用。 曾在为 AJAX 应用实现 TSTATS 时，起初忽视了这些问题，希望稳定非临时表语句的执行计划，依赖动态采样处理临时表语句。但后来发现不行，最终放弃动态采样，为临时表编造统计信息。 #### 4. 为临时表编造统计信息 思路很简单，使用 `DBMS_STATS.SET_TABLE_STATS`、`DBMS_STATS.SET_COLUMN_STATS` 和 `DBMS_STATS.SET_INDEX_STATS` 为全局临时表及其关联列和索引设置统计信息，这样能确保涉及临时表的执行计划固定且可测试。但问题是要确定编造哪些统计信息。 来看一个将临时表与永久表合并的示例： ```sql CREATE GLOBAL TEMPORARY TABLE payments_temp AS SELECT * FROM payments WHERE 1 = 0; MERGE /*+ cardinality(t 3) */ INTO payments p USING payments_temp t ON (p.payment_id = t.payment_id) WHEN MATCHED THEN UPDATE SET p.employee_id = t.employee_id ,p.special_flag = t.special_flag ,p.paygrade = t.paygrade ,p.payment_date = t.payment_date ,p.job_description = t.job_description WHEN NOT MATCHED THEN INSERT (payment_id ,special_flag ,paygrade ,payment_date ,job_description) VALUES (t.payment_id ,t.special_flag ,t.paygrade ,t.payment_date ,t.job_description); ``` 执行计划如下： | Id | Operation | Name | Rows | Cost (%CPU)| | --- | --- | --- | --- | --- | | 0 | MERGE STATEMENT | | 3 | 5 (0)| | 1 | MERGE | PAYMENTS | | | | 2 | VIEW | | | | | 3 | NESTED LOOPS OUTER | | 3 | 5 (0)| | 4 | TABLE ACCESS FULL | PAYMENTS_TEMP | 3 | 2 (0)| | 5 | TABLE ACCESS BY INDEX ROWID| PAYMENTS | 1 | 1 (0)| | 6 | INDEX UNIQUE SCAN | PAYMENTS_PK | 1 | 0 (0)| ```sql MERGE /*+ cardinality(t 10000) */ INTO payments p USING payments_temp t ON (p.payment_id = t.payment_id) WHEN MATCHED THEN UPDATE SET p.employee_id = t.employee_id ,p.special_flag = t.special_flag ,p.paygrade = t.paygrade ,p.payment_date = t.payment_date ,p.job_description = t.job_description WHEN NOT MATCHED THEN INSERT (payment_id ,special_flag ,paygrade ,payment_date ,job_description) VALUES (t.payment_id ,t.special_flag ,t.paygrade ,t.payment_date ,t.job_description); ``` 执行计划如下： | Id | Operation | Name | Rows | Cost (%CPU)| | --- | --- | --- | --- | --- | | 0 | MERGE STATEMENT | | 10000 | 176 (1)| | 1 | MERGE | PAYMENTS | | | | 2 | VIEW | | | | | 3 | HASH JOIN OUTER | | 10000 | 176 (1)| | 4 | TABLE ACCESS FULL| PAYMENTS_TEMP | 10000 | 2 (0)| | 5 | TABLE ACCESS FULL| PAYMENTS | 120K| 174 (1)| 这两个 `MERGE` 语句仅 `CARDINALITY` 提示的参数不同，一个是 3，另一个是 10000。可以看到，两种情况下 CBO 都选择临时表作为连接的驱动表。基数为 3 时，CBO 倾向于使用嵌套循环连接；基数估计为 10000 时，更倾向于哈希连接。 为了让 CBO 选择合理的执行计划而无需代码提示，需要回答以下两个问题： - 临时表有 10000 行时使用嵌套循环连接会有多糟糕？ - 临时表只有 3 行时使用哈希连接会有多糟糕？ ```sql MERGE /*+ cardinality(t 3) leading(t) use_hash(p) no_swap_join_inputs(p) */ INTO payments p USING payments_temp t ON (p.payment_id = t.payment ```