Oracle執行計劃突變診斷之統計信息收集問題

Oracle執行計劃突變診斷之統計信息收集問題

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;

最初的報錯，臨時表空間不足，

上述SQL為開發應用SQL, 當執行上述SQL時，通過以下命令監控臨時表空間。

使用 V$TEMPSEG_USAGE 可監視空間使用情況和分配情況：

SELECTsession_num, username, segtype, blocks, tablespace
FROMV$TEMPSEG_USAGE;

使用 V$SORT_SEGMENT 可確定空間真實使用率百分比：

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;

發現一條SQL能把64G的臨時表空間exhaust，查看對應之行劃，發現merge join cartesian

這部分無法回現了。

補：數據庫為新建數據庫，大量基礎表為其他庫同步過來的，應用表為實時入庫的表（MB_BSS_USER_LOCATION），且很清晰記得當時開啟了auto maintaining任務。

查看統計信息任務是否開啟：

select client_name,statusfrom dba_autotask_client;

2.  處理步驟

1      

2      

2.1     查看大表的統計信息

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分析得來，該表并沒有收集過統計信息，且統計信息被鎖。

查看庫中其他表的統計信息。

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

發現還有98張表統計信息被鎖定。

2.2     強制收集對應表統計信息

SQL> exec dbms_stats.gather_table_stats(ownname => 'GZ_SAFETY',tabname=>'MB_BSS_USER_LOCATION', force=>TRUE);
PL/SQL proceduresuccessfully completed

再次查看執行計劃。

--------------------------------------------------------------------------------------------------------
| 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 |

發現笛卡爾積merge join消失，執行計劃正常。

2.3     查看其他表的統計信息情況（分區表）

select table_name,partition_name, last_analyzed, stattype_locked
        from user_tab_statistics wherestattype_locked is not null and object_type in ('PARTITION', 'SUBPARTITION');

因為是測試環境，暫不關注這些表，先把MB_BSS_USER_LOCATION表的統計信息鎖定打開。

SQL> execdbms_stats.unlock_table_stats(ownname => 'GZ_SAFETY',tabname=>'MB_BSS_USER_LOCATION');
PL/SQL procedure successfully completed, 打開后可通過user_tab_statistics.stattype_locked查看。
補：打開對應用戶的統計信息。
DBMS_STATS.UNLOCK_schema_STATS(user);