收集统计数据库信息的隐患有哪些
这篇文章主要讲解了“收集统计数据库信息的隐患有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“收集统计数据库信息的隐患有哪些”吧！
创新互联10多年成都企业网站建设服务;为您提供网站建设,网站制作,网页设计及高端网站定制服务,成都企业网站建设及推广,对成都发电机租赁等多个行业拥有丰富的网站运维经验的网站建设公司。
收集统计信息使得SQL产生硬解析
大多数情况下，表的统计信息不准导致了优化器对于执行计划的错误计算，因此需要对表的统计信息进行更正，
以便让优化器重新选择准确的执行计划。
在进行SQL优化时，通过查看执行计划，表的统计信息以及表的具体情况，去分析是否是由于统计信息不准导致执行计划
有问题，当确定了是统计信息的问题时，不能盲目的去收集统计信息，否则会给数据库带来隐患。
收集统计信息，给数据库带来隐患：
1、对重新收集统计信息的表，对应的一些SQL可能需要重新硬解析生成执行计划。
2、对于重新收集统计信息的表的部分SQL来说，可能会出现收集完统计信息了，但是执行计划更差的情况。
3、收集统计信息，会需要额外的资源开销，在业务高峰期会影响数据库的性能。
用测试来验证
（收集统计信息使得SQL产生硬解析）。
1、创建测试表
SQL> drop table demo purge; Table dropped. SQL> create table demo as select * from dba_objects; Table created.
2、在owner列上创建索引
SQL> create index idx_owner_demo on demo(owner); Index created.
3、收集表的统计信息，并且收集owner列的直方图信息：
begin dbms_stats.gather_table_stats(ownname => 'DEMO', tabname => 'DEMO', estimate_percent => 100, method_opt => 'for columns owner size skewonly', no_invalidate => false, degree => 1, cascade => true); end; /
4、查看SQL的执行计划
查看一下owner为demo和sys的数据情况（主要是为了在不同的where条件，查看执行计划的情况）：
SELECT (SELECT COUNT(*) FROM DEMO) CNT
,OWNER
,COUNT(*)
FROM DEMO
WHERE OWNER IN ('DEMO', 'SYS')
GROUP BY OWNER;
CNT OWNER COUNT(*)
---------- ------------------------------ ----------
87069 DEMO 44
87069 SYS 37815表demo共有87096行记录，其中owner为demo的有44行记录，owner为sys的有37815行记录。
5、为了测试效果，刷新shared pool（除测试外，勿用）
SQL> alter system flush shared_pool; System altered.
6、查看下列SQL的执行计划：
SQL> set autot trace
SQL> select /* demo */* from demo where owner = 'DEMO';
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3014608035
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 4312 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO | 44 | 4312 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER_DEMO | 44 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='DEMO')通过执行计划可以看到，使用了索引范围扫描，cost为3。
此时统计信息是正确的，并且owner列也收集了直方图信息，因此优化器会根据统计信息去生成正确的执行计划，
由于owner='DEMO'的记录只有44行，在返回这44条记录时，采用索引范围扫描的成本最低。
SQL> select /* sys */* from demo where owner = 'SYS';
37815 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4000794843
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37815 | 3619K| 347 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| DEMO | 37815 | 3619K| 347 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')通过执行计划可以看到，使用了全表扫描，cost为347。
由于owner='SYS'的记录有37815行，在返回这37815条记录时，采用全表扫描的成本最低。
7、查看SQL的信息：
SQL> select sql_id,sql_text,child_number,plan_hash_value,parse_calls,loads from v$sql where sql_text like '%/* demo */%'; SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS LOADS ------------- ------------------------------------------------------------ ------------ --------------- ----------- ---------- 45skkr08bw1m8 select /* demo */* from demo where owner = 'DEMO' 0 3014608035 1 1
此时该SQL当前的执行计划的plan_hash_value为3014608035，硬解析了一次（loads表示硬解析次数）。
8、更新表中的数据，但是不收集统计信息：
SQL> update demo set owner = 'DEMO' where object_id < 60000; 59659 rows updated SQL> commit; Commit complete
再一次进行查询：
SQL> select /* demo */* from demo where owner = 'DEMO';
59703 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3014608035
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 4312 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO | 44 | 4312 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER_DEMO | 44 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='DEMO')通过执行计划可以发现，使用了索引范围扫描，cost为3。
此时的执行计划是错误的，返回的数据行数为59659，不适合在使用索引范围扫描，应该使用全表扫描。
但是由于统计信息未更新，所以优化器还是认为表中的数据情况是之前统计信息里的，所以延用了之前的执行计划。
9、查看统计信息的情况
SELECT OWNER ,TABLE_NAME ,OBJECT_TYPE ,STALE_STATS ,TO_CHAR(LAST_ANALYZED, 'yyyy-mm-dd hh34:mi:ss') LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER = 'DEMO' AND TABLE_NAME = 'DEMO'; OWNER TABLE_NAME OBJECT_TYPE STA LAST_ANALYZED ------------------------------ ------------------------------ ------------ --- ------------------- DEMO DEMO TABLE NO 2020-05-12 10:57:46
此时表的数据变化已经超过表数据量的10%，应该在DBA_TAB_STATISTICS中记录下来表demo，
并且把STALE_STATS列的值改为yes。
（STALE_STATS列的值代表了统计信息的情况，yes表示统计信息过期；no表示统计信息未过期）
由于表的数据的变化的情况未被及时的刷新（默认15分钟刷新一次），因此DBA_TAB_STATISTICS视图里的信息也没有更新，
采用手动刷新数据库监控
SQL> exec dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed.
然后再次查看表的统计信息的情况：
OWNER TABLE_NAME OBJECT_TYPE STA LAST_ANALYZED ------------------------------ ------------------------------ ------------ --- ------------------- DEMO DEMO TABLE YES 2020-05-12 10:57:46
列STALE_STATS的值已经变为yes，说明表demo的统计信息已经过期了，需要重新收集统计信息。
10、重新收集统计信息：
begin dbms_stats.gather_table_stats(ownname => 'DEMO', tabname => 'DEMO', estimate_percent => 100, method_opt => 'for columns owner size skewonly', no_invalidate => false, degree => 1, cascade => true); end; /
查看SQL的执行计划
查看一下owner为demo和sys的数据情况：
SELECT (SELECT COUNT(*) FROM DEMO) CNT
,OWNER
,COUNT(*)
FROM DEMO
WHERE OWNER IN ('DEMO', 'SYS')
GROUP BY OWNER;
CNT OWNER COUNT(*)
---------- ------------------------------ ----------
87069 DEMO 59703
87069 SYS 5486表demo共有87096行记录，其中owner为demo的有59703行记录，owner为sys的有5486行记录。
收集完统计信息，再次查看执行计划：
SQL> select /* demo */* from demo where owner = 'DEMO';
59703 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4000794843
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59703 | 5713K| 347 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| DEMO | 59703 | 5713K| 347 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='DEMO')通过执行计划可以看到，使用了全表扫描，cost为347。
此时的执行计划是正确的，返回了59703行，此时不应该在使用索引，应该使用全表扫描。
12、查看SQL的信息：
SQL> select sql_id,sql_text,child_number,plan_hash_value,parse_calls,loads from v$sql where sql_id = '45skkr08bw1m8'; SQL_ID SQL_TEXT CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS LOADS ------------- ------------------------------------------------------------ ------------ --------------- ----------- ---------- 45skkr08bw1m8 select /* demo */* from demo where owner = 'DEMO' 0 4000794843 1 2
查看SQL的信息发现，loads变成了2，说明增加了一次硬解析，也就是说，在正常情况下，如果收集了表的统计信息，那么对于某些SQL来说，会产生硬解析，对于生产库来说，如果盲目的收集统计信息，则会产生大量的硬解析，给数据库带来压力。
感谢各位的阅读，以上就是“收集统计数据库信息的隐患有哪些”的内容了，经过本文的学习后，相信大家对收集统计数据库信息的隐患有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
当前题目：收集统计数据库信息的隐患有哪些
标题网址：http://ybzwz.com/article/igspoi.html
