Oracle執(zhí)行計(jì)劃突變?cè)\斷之統(tǒng)計(jì)信息收集問題
成都創(chuàng)新互聯(lián)公司長期為數(shù)千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為沁源企業(yè)提供專業(yè)的網(wǎng)站制作、成都網(wǎng)站制作，沁源網(wǎng)站改版等技術(shù)服務(wù)。擁有十載豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。
DB version：11.2.0.4
重慶分公司
重慶分公司
WITH SQL1 AS
(SELECT LAC,
CI,
TO_NUMBER(C.LONGITUDE) LONGITUDE,
TO_NUMBER(C.LATITUDE) LATITUDE
FROM MB_SYS_CELL_INFO C
WHERE C.CONTY_NAME = '道孚縣'),
SQL2 AS
(SELECT DISTINCT IMSI, LAC, CI
FROM MB_BSS_USER_LOCATION
WHERE HOUR IN (16, 15, 14, 13)
AND TIME = TO_TIMESTAMP('20170621','YYYYMMDD')),
SQL3 AS
(SELECT C.LONGITUDE, C.LATITUDE,WM_CONCAT(C.SITE_NAME) SITE_NAME
FROM (SELECT DISTINCT TO_NUMBER(A.LONGITUDE)LONGITUDE,
TO_NUMBER(A.LATITUDE)LATITUDE,
A.SITE_NAME
FROM MB_SYS_CELL_INFO A
WHERE A.CONTY_NAME = '道孚縣') C
GROUP BY C.LONGITUDE, C.LATITUDE)
SELECT SQL1.LONGITUDELNG,
SQL1.LATITUDE LAT,
COUNT(DISTINCT SQL2.IMSI) COUNT,
TO_CHAR(SQL3.SITE_NAME）SITE_NAME
FROM SQL1, SQL2, SQL3
WHERE SQL2.LAC = SQL1.LAC AND SQL2.CI =SQL1.CI AND SQL1.LONGITUDE = SQL3.LONGITUDE AND SQL1.LATITUDE = SQL3.LATITUDEGROUP BY SQL1.LONGITUDE, SQL1.LATITUDE, TO_CHAR(SQL3.SITE_NAME) ORDER BY COUNTDESC;最初的報(bào)錯(cuò)，臨時(shí)表空間不足，
上述SQL為開發(fā)應(yīng)用SQL, 當(dāng)執(zhí)行上述SQL時(shí)，通過以下命令監(jiān)控臨時(shí)表空間。
使用 V$TEMPSEG_USAGE 可監(jiān)視空間使用情況和分配情況：
SELECTsession_num, username, segtype, blocks, tablespace FROMV$TEMPSEG_USAGE;
使用 V$SORT_SEGMENT 可確定空間真實(shí)使用率百分比：
SELECT(s.tot_used_blocks/f.total_blocks)*100 as pctused FROM(SELECT SUM(used_blocks) tot_used_blocks FROMV$SORT_SEGMENT WHEREtablespace_name='TEMP') s, (SELECTSUM(blocks) total_blocks FROMDBA_TEMP_FILES WHEREtablespace_name='TEMP') f;
發(fā)現(xiàn)一條SQL能把64G的臨時(shí)表空間exhaust，查看對(duì)應(yīng)之行劃，發(fā)現(xiàn)merge join cartesian
這部分無法回現(xiàn)了。
補(bǔ)：數(shù)據(jù)庫為新建數(shù)據(jù)庫，大量基礎(chǔ)表為其他庫同步過來的，應(yīng)用表為實(shí)時(shí)入庫的表（MB_BSS_USER_LOCATION），且很清晰記得當(dāng)時(shí)開啟了auto maintaining任務(wù)。
查看統(tǒng)計(jì)信息任務(wù)是否開啟：
select client_name,statusfrom dba_autotask_client;
1
2
select table_name, partition_name,last_analyzed, STATTYPE_LOCKED fromuser_tab_statistics where table_name = 'MB_BSS_USER_LOCATION'; STATTYPE_LOCKED VARCHAR2(5) Type ofstatistics lock: ■ DATA ■ CACHE ■ ALL
last_analyzed, STATTYPE_LOCKED分析得來，該表并沒有收集過統(tǒng)計(jì)信息，且統(tǒng)計(jì)信息被鎖。
查看庫中其他表的統(tǒng)計(jì)信息。
select count(distinct table_name) fromuser_tab_statistics where stattype_locked is not null;
發(fā)現(xiàn)還有98張表統(tǒng)計(jì)信息被鎖定。
SQL> exec dbms_stats.gather_table_stats(ownname => 'GZ_SAFETY',tabname=>'MB_BSS_USER_LOCATION', force=>TRUE); PL/SQL proceduresuccessfully completed
再次查看執(zhí)行計(jì)劃。
-------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 32608 | 41343 | 00:08:17 | | 1 | SORT ORDER BY | | 16 | 32608 | 41343 | 00:08:17 | | 2 | HASH GROUP BY | | 16 | 32608 | 41343 | 00:08:17 | | 3 | VIEW |VM_NWVW_1 | 16 | 32608 | 41341 | 00:08:17 | | 4 | HASH GROUP BY | | 16 | 33744 | 41341 | 00:08:17 | | * 5 | HASH JOIN | | 16 | 33744 | 41340 | 00:08:17 | | * 6 | HASH JOIN | | 1 | 2069 | 138 | 00:00:02 | | * 7 | TABLE ACCESS FULL |MB_SYS_CELL_INFO | 448 | 18368 | 68 | 00:00:01 | | 8 | VIEW | | 448 | 908544 | 70 | 00:00:01 | | 9 | SORT GROUP BY | | 448 | 26880 | 70 | 00:00:01 | | 10 | VIEW | | 448 | 26880 | 69 | 00:00:01 | | 11 | HASH UNIQUE | | 448 | 22400 | 69 | 00:00:01 | | * 12 | TABLE ACCESS FULL |MB_SYS_CELL_INFO | 448 | 22400 | 68 | 00:00:01 | | 13 | PARTITION RANGE SINGLE | | 3237748 | 129509920 |41192 | 00:08:15 | | 14 | PARTITION LIST INLIST | | 3237748 | 129509920 |41192 | 00:08:15 | | * 15 | TABLE ACCESS FULL | MB_BSS_USER_LOCATION | 3237748 |129509920 | 41192 | 00:08:15 |
發(fā)現(xiàn)笛卡爾積merge join消失，執(zhí)行計(jì)劃正常。
select table_name,partition_name, last_analyzed, stattype_locked
from user_tab_statistics wherestattype_locked is not null and object_type in ('PARTITION', 'SUBPARTITION');因?yàn)槭菧y試環(huán)境，暫不關(guān)注這些表，先把MB_BSS_USER_LOCATION表的統(tǒng)計(jì)信息鎖定打開。
SQL> execdbms_stats.unlock_table_stats(ownname => 'GZ_SAFETY',tabname=>'MB_BSS_USER_LOCATION'); PL/SQL procedure successfully completed, 打開后可通過user_tab_statistics.stattype_locked查看。 補(bǔ)：打開對(duì)應(yīng)用戶的統(tǒng)計(jì)信息。 DBMS_STATS.UNLOCK_schema_STATS(user);
