统计信息不准导致执行计划出错跑不出结果，优化后只要1分钟
来源：互联网 发布：数据库统计选课门数 编辑：程序博客网 时间：2024/04/28 06:14
一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。
优化前：
SELECT 20131023, "A2"."ORG_ID", COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'DP' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'BOX' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'ONU' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'OBD' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')) FROM "CRM_SZ"."AAA" "A2", "CRM_SZ"."BBB" "A1" WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE" GROUP BY "A2"."ORG_ID"执行计划：Plan hash value: 2627707252 ---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 1065 | 3 (34)| 00:00:01 || 1 | SORT GROUP BY | | 1 | 1065 | 3 (34)| 00:00:01 || 2 | NESTED LOOPS OUTER| | 1 | 1065 | 2 (0)| 00:00:01 || 3 | TABLE ACCESS FULL| AAA | 1 | 539 | 2 (0)| 00:00:01 ||* 4 | INDEX FULL SCAN | IX_MO_CON_VALUE | 1 | 526 | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("A1"."RES_ID"(+)="A2"."RES_CODE") filter("A1"."RES_ID"(+)="A2"."RES_CODE")cbo估算错了，rows全是1，导致走nl
手工count了一把：
select count(*) from "CRM_SZ"."AAA" ;--1365564
select count(*) from "CRM_SZ"."BBB";--119949
走nl那岂不是sb啦。
第一次优化后：
SELECT/*+use_hash(A1,A2) swap_join_inputs(A1)*/20131023, "A2"."ORG_ID", COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'DP' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'BOX' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'ONU' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'OBD' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')) FROM "CRM_SZ"."AAA" "A2", "CRM_SZ"."BBB" "A1" WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE" GROUP BY "A2"."ORG_ID"63 rows selected.Elapsed: 00:00:47.64Execution Plan----------------------------------------------------------Plan hash value: 3074972763------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 1065 | 4 (50)| 00:00:01 || 1 | SORT GROUP BY | | 1 | 1065 | 4 (50)| 00:00:01 ||* 2 | HASH JOIN RIGHT OUTER| | 1 | 1065 | 3 (34)| 00:00:01 || 3 | INDEX FULL SCAN | IX_MO_CON_VALUE | 1 | 526 | 0 (0)| 00:00:01 || 4 | TABLE ACCESS FULL | AAA | 1 | 539 | 2 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("A1"."RES_ID"(+)="A2"."RES_CODE")Statistics---------------------------------------------------------- 1065 recursive calls 3 db block gets 13375 consistent gets 16369 physical reads 0 redo size 4862 bytes sent via SQL*Net to client 791 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 12 sorts (memory) 1 sorts (disk) 63 rows processed第二次优化后：
SELECT/*+use_hash(A1,A2) full(A1) full(A2) parallel(A1,5) parallel(A2,5) swap_join_inputs(A1)*/20131023, "A2"."ORG_ID", COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'DP' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'BOX' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'ONU' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE" WHEN 'OBD' THEN "A2"."RES_CODE" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')), COUNT(DISTINCT NLSSORT(CASE WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN "A1"."RES_ID" END, 'nls_sort=''BINARY''')) FROM "CRM_SZ"."AAA" "A2", "CRM_SZ"."BBB" "A1" WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE" GROUP BY "A2"."ORG_ID";63 rows selected.Elapsed: 00:00:10.21Execution Plan----------------------------------------------------------Plan hash value: 4044842257-------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |-------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 1065 | 6 (34)| 00:00:01 | | | || 1 | PX COORDINATOR | | | | | | | | || 2 | PX SEND QC (RANDOM) | :TQ10004 | 1 | 1065 | 6 (34)| 00:00:01 | Q1,04 | P->S | QC (RAND) || 3 | SORT GROUP BY | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,04 | PCWP | || 4 | PX RECEIVE | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,04 | PCWP | || 5 | PX SEND HASH | :TQ10003 | 1 | 1065 | 6 (34)| 00:00:01 | Q1,03 | P->P | HASH || 6 | SORT GROUP BY | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,03 | PCWP | || 7 | PX RECEIVE | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,03 | PCWP | || 8 | PX SEND HASH | :TQ10002 | 1 | 1065 | 6 (34)| 00:00:01 | Q1,02 | P->P | HASH || 9 | SORT GROUP BY | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,02 | PCWP | ||* 10 | HASH JOIN RIGHT OUTER| | 1 | 1065 | 5 (20)| 00:00:01 | Q1,02 | PCWP | || 11 | PX RECEIVE | | 1 | 526 | 2 (0)| 00:00:01 | Q1,02 | PCWP | || 12 | PX SEND HASH | :TQ10000 | 1 | 526 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH || 13 | PX BLOCK ITERATOR | | 1 | 526 | 2 (0)| 00:00:01 | Q1,00 | PCWC | || 14 | TABLE ACCESS FULL| BBB | 1 | 526 | 2 (0)| 00:00:01 | Q1,00 | PCWP | || 15 | PX RECEIVE | | 1 | 539 | 2 (0)| 00:00:01 | Q1,02 | PCWP | || 16 | PX SEND HASH | :TQ10001 | 1 | 539 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH || 17 | PX BLOCK ITERATOR | | 1 | 539 | 2 (0)| 00:00:01 | Q1,01 | PCWC | || 18 | TABLE ACCESS FULL| AAA | 1 | 539 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |-------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 10 - access("A1"."RES_ID"(+)="A2"."RES_CODE")Statistics---------------------------------------------------------- 585 recursive calls 4 db block gets 14267 consistent gets 13126 physical reads 808 redo size 4888 bytes sent via SQL*Net to client 840 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 23 sorts (memory) 0 sorts (disk) 63 rows processed优化前，执行计划走了nl，1个多小时查不出结果
第一次优化后，首次00:00:47.64可以出结果
第二次优化后，首次00:00:10.21出结果，重复执行（有缓存）的情况下3s出结果。
0 0
- 统计信息不准导致执行计划出错跑不出结果，优化后只要1分钟
- 分区表imdp 导入 收集统计信息 自动 采样导致执行计划card 不准
- 执行计划之统计信息
- 直方图统计导致错误的执行计划
- 直方图统计导致错误的执行计划
- 统计信息自动收集时间窗口导致分区表执行计划错误
- 统计信息失准导致CBO选错执行计划一例
- sqlplus得到执行计划的统计信息
- ORACLE 执行计划 统计信息介绍
- 修改统计信息改变执行计划(ORACLE)
- oracle -执行计划的统计信息
- 6.2.3 收集执行计划统计信息
- 表统计信息过旧导致执行慢的sql优化
- 利用DBMS_STATS包修改统计信息，欺骗优化器，生成糟糕的执行计划
- 统计信息不准_导致笛卡尔积_temp表空间爆满
- dblink导致执行计划出错，hint也无效
- 使用dmbs_stats.gather_table_stats做分析后执行计划不准的解决方法
- oracle执行计划与统计信息的一些总结
- printk打印不能显示到终端的问题
- 长得高大纵观Pubertywhat你需要知道的关于长高在青春期和为什么
- NodeJS+Express+mongoose+Jade+bootstrap+jquery+inspector(调试工具)开发网站demo总结
- An overview of Openvswitch implementation
- IOS-设计图过长时的界面设计
- 统计信息不准导致执行计划出错跑不出结果，优化后只要1分钟
- MFC遇到的函数
- 珠宝首饰护理技巧
- 深入浅出解析OpenFlow
- window MFC消息泵
- LeetCode——Palindrome Partitioning
- test
- 简单解析PAT、PMT的程序
- test
