Oracle执行计划突变诊断之统计信息收集问题

1.  情形描述

DB version：11.2.0.4

WITH SQL1 AS(SELECT LAC,CI,TO_NUMBER(C.LONGITUDE) LONGITUDE,TO_NUMBER(C.LATITUDE) LATITUDEFROM MB_SYS_CELL_INFO CWHERE C.CONTY_NAME = '道孚县'),
SQL2 AS(SELECT DISTINCT IMSI, LAC, CIFROM MB_BSS_USER_LOCATIONWHERE 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_NAMEFROM (SELECT DISTINCT TO_NUMBER(A.LONGITUDE)LONGITUDE,TO_NUMBER(A.LATITUDE)LATITUDE,A.SITE_NAMEFROM MB_SYS_CELL_INFO AWHERE A.CONTY_NAME = '道孚县') CGROUP BY C.LONGITUDE, C.LATITUDE)
SELECT SQL1.LONGITUDELNG,SQL1.LATITUDE LAT,COUNT(DISTINCT SQL2.IMSI) COUNT,TO_CHAR(SQL3.SITE_NAME）SITE_NAMEFROM SQL1, SQL2, SQL3WHERE 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_statisticswhere 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_lockedfrom 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);