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

1.情形描述

DB version：11.2.0.4

WITHsql1AS
(SELECTLAC,CI,TO_NUMBER(C.LONGITUDE)LONGITUDE,TO_NUMBER(C.LATITUDE)LATITUDE
FROMMB_SYS_CELL_INFOC
WHEREC.CONTY_NAME='道孚县'),sql2AS
(SELECTDISTINCTIMSI,LAC,CI
FROMMB_BSS_USER_LOCATION
WHEREHOURIN(16,15,14,13)
ANDTIME=TO_TIMESTAMP('20170621','YYYYMMDD')),sql3AS
(SELECTC.LONGITUDE,C.LATITUDE,WM_CONCAT(C.SITE_NAME)SITE_NAME
FROM(SELECTDISTINCTTO_NUMBER(A.LONGITUDE)LONGITUDE,TO_NUMBER(A.LATITUDE)LATITUDE,A.SITE_NAME
FROMMB_SYS_CELL_INFOA
WHEREA.CONTY_NAME='道孚县')C
GROUPBYC.LONGITUDE,C.LATITUDE)
SELECTsql1.LONGITUDELNG,sql1.LATITUDELAT,COUNT(DISTINCTsql2.IMSI)COUNT,TO_CHAR(sql3.SITE_NAME）SITE_NAME
FROMsql1,sql2,sql3
WHEREsql2.LAC=sql1.LACANDsql2.CI=sql1.CIANDsql1.LONGITUDE=sql3.LONGITUDEANDsql1.LATITUDE=sql3.LATITUDEGROUPBYsql1.LONGITUDE,sql1.LATITUDE,TO_CHAR(sql3.SITE_NAME)ORDERBYCOUNTDESC;

最初的报错，临时表空间不足，

上述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)*100aspctused
FROM(SELECTSUM(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任务。

查看统计信息任务是否开启：

selectclient_name,statusfromdba_autotask_client;

2.处理步骤

1

2

2.1查看大表的统计信息

selecttable_name,partition_name,last_analyzed,STATTYPE_LOCKEDfromuser_tab_statistics
wheretable_name='MB_BSS_USER_LOCATION';
STATTYPE_LOCKEDVARCHAR2(5)Typeofstatisticslock:
■DATA
■CACHE
■ALL

last_analyzed,STATTYPE_LOCKED分析得来，该表并没有收集过统计信息，且统计信息被锁。

查看库中其他表的统计信息。

selectcount(distincttable_name)fromuser_tab_statisticswherestattype_lockedisnotnull;

发现还有98张表统计信息被锁定。

2.2强制收集对应表统计信息

sql>execdbms_stats.gather_table_stats(ownname=>'GZ_SAFETY',tabname=>'MB_BSS_USER_LOCATION',force=>TRUE);
PL/sqlproceduresuccessfullycompleted

再次查看执行计划。

--------------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost|Time|
--------------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||16|32608|41343|00:08:17|
|1|SORTORDERBY||16|32608|41343|00:08:17|
|2|HASHGROUPBY||16|32608|41343|00:08:17|
|3|VIEW|VM_NWVW_1|16|32608|41341|00:08:17|
|4|HASHGROUPBY||16|33744|41341|00:08:17|
|*5|HASHJOIN||16|33744|41340|00:08:17|
|*6|HASHJOIN||1|2069|138|00:00:02|
|*7|TABLEACCESSFULL|MB_SYS_CELL_INFO|448|18368|68|00:00:01|
|8|VIEW||448|908544|70|00:00:01|
|9|SORTGROUPBY||448|26880|70|00:00:01|
|10|VIEW||448|26880|69|00:00:01|
|11|HASHUNIQUE||448|22400|69|00:00:01|
|*12|TABLEACCESSFULL|MB_SYS_CELL_INFO|448|22400|68|00:00:01|
|13|PARTITIONRANGESINGLE||3237748|129509920|41192|00:08:15|
|14|PARTITIONLISTINLIST||3237748|129509920|41192|00:08:15|
|*15|TABLEACCESSFULL|MB_BSS_USER_LOCATION|3237748|129509920|41192|00:08:15|

发现笛卡尔积merge join消失，执行计划正常。

2.3查看其他表的统计信息情况（分区表）

selecttable_name,stattype_locked
fromuser_tab_statisticswherestattype_lockedisnotnullandobject_typein('PARTITION','SUBPARTITION');

因为是测试环境，暂不关注这些表，先把MB_BSS_USER_LOCATION表的统计信息锁定打开。

sql>execdbms_stats.unlock_table_stats(ownname=>'GZ_SAFETY',tabname=>'MB_BSS_USER_LOCATION');
PL/sqlproceduresuccessfullycompleted,打开后可通过user_tab_statistics.stattype_locked查看。
补：打开对应用户的统计信息。
DBMS_STATS.UNLOCK_schema_STATS(user);