国产99久久精品_欧美日本韩国一区二区_激情小说综合网_欧美一级二级视频_午夜av电影_日本久久精品视频

最新文章專題視頻專題問答1問答10問答100問答1000問答2000關(guān)鍵字專題1關(guān)鍵字專題50關(guān)鍵字專題500關(guān)鍵字專題1500TAG最新視頻文章推薦1 推薦3 推薦5 推薦7 推薦9 推薦11 推薦13 推薦15 推薦17 推薦19 推薦21 推薦23 推薦25 推薦27 推薦29 推薦31 推薦33 推薦35 推薦37視頻文章20視頻文章30視頻文章40視頻文章50視頻文章60 視頻文章70視頻文章80視頻文章90視頻文章100視頻文章120視頻文章140 視頻2關(guān)鍵字專題關(guān)鍵字專題tag2tag3文章專題文章專題2文章索引1文章索引2文章索引3文章索引4文章索引5123456789101112131415文章專題3
問答文章1 問答文章501 問答文章1001 問答文章1501 問答文章2001 問答文章2501 問答文章3001 問答文章3501 問答文章4001 問答文章4501 問答文章5001 問答文章5501 問答文章6001 問答文章6501 問答文章7001 問答文章7501 問答文章8001 問答文章8501 問答文章9001 問答文章9501
當(dāng)前位置： 首頁 - 科技 - 知識(shí)百科 - 正文

統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致執(zhí)行計(jì)劃出錯(cuò)跑不出結(jié)果，優(yōu)化后只要1分鐘

來源：懂視網(wǎng) 責(zé)編：小采 時(shí)間：2020-11-09 15:00:58
文檔

統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致執(zhí)行計(jì)劃出錯(cuò)跑不出結(jié)果，優(yōu)化后只要1分鐘

統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致執(zhí)行計(jì)劃出錯(cuò)跑不出結(jié)果，優(yōu)化后只要1分鐘:一天查看數(shù)據(jù)庫長(zhǎng)會(huì)話，發(fā)現(xiàn)1個(gè)sql跑得很慢，1個(gè)多小時(shí)不出結(jié)果，花了點(diǎn)時(shí)間把它給優(yōu)化了。 優(yōu)化前： SELECT 20131023, A2.ORG_ID, COUNT(DISTINCT NLSSORT(CASE A2.RES_TYPE WHEN DP THEN A2.RES_CODE END, nls_sort=
推薦度：
導(dǎo)讀統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致執(zhí)行計(jì)劃出錯(cuò)跑不出結(jié)果，優(yōu)化后只要1分鐘:一天查看數(shù)據(jù)庫長(zhǎng)會(huì)話，發(fā)現(xiàn)1個(gè)sql跑得很慢，1個(gè)多小時(shí)不出結(jié)果，花了點(diǎn)時(shí)間把它給優(yōu)化了。 優(yōu)化前： SELECT 20131023, A2.ORG_ID, COUNT(DISTINCT NLSSORT(CASE A2.RES_TYPE WHEN DP THEN A2.RES_CODE END, nls_sort=

一天查看數(shù)據(jù)庫長(zhǎng)會(huì)話，發(fā)現(xiàn)1個(gè)sql跑得很慢，1個(gè)多小時(shí)不出結(jié)果，花了點(diǎn)時(shí)間把它給優(yōu)化了。 優(yōu)化前： 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

一天查看數(shù)據(jù)庫長(zhǎng)會(huì)話，發(fā)現(xiàn)1個(gè)sql跑得很慢，1個(gè)多小時(shí)不出結(jié)果，花了點(diǎn)時(shí)間把它給優(yōu)化了。

優(yōu)化前：

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"

執(zhí)行計(jì)劃：
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估算錯(cuò)了，rows全是1，導(dǎo)致走nl
手工count了一把：
select count(*) from "CRM_SZ"."AAA" ;--1365564
select count(*) from "CRM_SZ"."BBB";--119949
走nl那豈不是sb啦。

第一次優(yōu)化后：

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
第二次優(yōu)化后： 
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

優(yōu)化前，執(zhí)行計(jì)劃走了nl，1個(gè)多小時(shí)查不出結(jié)果
第一次優(yōu)化后，首次00:00:47.64可以出結(jié)果
第二次優(yōu)化后，首次00:00:10.21出結(jié)果，重復(fù)執(zhí)行（有緩存）的情況下3s出結(jié)果。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

文檔

統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致執(zhí)行計(jì)劃出錯(cuò)跑不出結(jié)果，優(yōu)化后只要1分鐘

統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致執(zhí)行計(jì)劃出錯(cuò)跑不出結(jié)果，優(yōu)化后只要1分鐘:一天查看數(shù)據(jù)庫長(zhǎng)會(huì)話，發(fā)現(xiàn)1個(gè)sql跑得很慢，1個(gè)多小時(shí)不出結(jié)果，花了點(diǎn)時(shí)間把它給優(yōu)化了。 優(yōu)化前： SELECT 20131023, A2.ORG_ID, COUNT(DISTINCT NLSSORT(CASE A2.RES_TYPE WHEN DP THEN A2.RES_CODE END, nls_sort=
推薦度：
標(biāo)簽： 信息 計(jì)劃 不出
  • 熱門焦點(diǎn)

最新推薦

猜你喜歡

熱門推薦

專題
Top
主站蜘蛛池模板： 欧美视频一区二区在线观看 | 亚洲欧美日韩在线精品一区二区 | 亚洲第八页 | 黄色在线观看网站 | 麻豆国产 | 91亚洲国产成人久久精品网址 | 美女网站全黄 | 亚洲欧美日韩在线观看二区 | 国产黄色在线看 | 国产精品麻豆a在线播放 | 日韩午夜电影 | 亚洲美女一区二区三区 | 亚洲视频在线观看 | 亚洲精品美女久久久aaa | 成人a毛片免费视频观看 | 日韩经典第一页 | 国内精品视频一区二区三区 | 中文字幕第13亚洲另类 | 国产精品ⅴ视频免费观看 | 国产综合欧美 | 国产精品黄大片观看 | 中日韩美中文字幕 | 大香伊人久久 | 国产一精品一aⅴ一免费 | 国产欧美精品区一区二区三区 | 久久一区二区三区免费 | 精品国产亚一区二区三区 | 精品视频在线观看视频免费视频 | 欧美专区亚洲 | 国产高清精品久久久久久久 | 欧美在线视频一区二区三区 | 亚洲另类在线观看 | 国产精品免费网站 | 亚洲精品国产成人7777 | 久久久久777777人人人视频 | 九草在线播放 | 精品在线免费播放 | 天天做天天爱夜夜爽毛片毛片 | 永久在线毛片免费观看 | 伊人情人综合成人久久网小说 | 香蕉视频免费在线看 |