Oracle执行计划突变诊断之统计信息收集问题
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); (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |