统计信息不准导致执行计划出错跑不出结果,优化后只要1分钟
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了统计信息不准导致执行计划出错跑不出结果,优化后只要1分钟,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含10049字,纯文字阅读大概需要15分钟。
内容图文
一天查看数据库长会话,发现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
一天查看数据库长会话,发现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.64 Execution 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.21 Execution 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出结果。
内容总结
以上是互联网集市为您收集整理的统计信息不准导致执行计划出错跑不出结果,优化后只要1分钟全部内容,希望文章能够帮你解决统计信息不准导致执行计划出错跑不出结果,优化后只要1分钟所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。