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, tablespaceFROMV$TEMPSEG_USAGE;

使用 V$SORT_SEGMENT 可确定空间真实使用率百分比：

SELECT(s.tot_used_blocks/f.total_blocks)*100 as pctusedFROM(SELECT SUM(used_blocks) tot_used_blocksFROMV$SORT_SEGMENTWHEREtablespace_name='TEMP') s,(SELECTSUM(blocks) total_blocksFROMDBA_TEMP_FILESWHEREtablespace_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);