Oracle執(zhí)行計(jì)劃突變?cè)\斷之統(tǒng)計(jì)信息收集問題

Oracle執(zhí)行計(jì)劃突變?cè)\斷之統(tǒng)計(jì)信息收集問題

1.  情形描述

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ù)庫(kù)為新建數(shù)據(jù)庫(kù)，大量基礎(chǔ)表為其他庫(kù)同步過來的，應(yīng)用表為實(shí)時(shí)入庫(kù)的表（MB_BSS_USER_LOCATION），且很清晰記得當(dāng)時(shí)開啟了auto maintaining任務(wù)。

查看統(tǒng)計(jì)信息任務(wù)是否開啟：

select client_name,statusfrom dba_autotask_client;

2.  處理步驟

1      

2      

2.1     查看大表的統(tǒng)計(jì)信息

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ì)信息被鎖。

查看庫(kù)中其他表的統(tǒng)計(jì)信息。

select count(distinct table_name) fromuser_tab_statistics where stattype_locked is not null;

發(fā)現(xiàn)還有98張表統(tǒng)計(jì)信息被鎖定。

2.2     強(qiáng)制收集對(duì)應(yīng)表統(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ì)劃正常。

2.3     查看其他表的統(tǒng)計(jì)信息情況（分區(qū)表）

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(cè)試環(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);