Oracle执行计划突变诊断之统计信息收集问题
Oracle执行计划突变诊断之统计信息收集问题
成都创新互联公司长期为数千家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为沁源企业提供专业的网站制作、成都网站制作,沁源网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。
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);
网站名称:Oracle执行计划突变诊断之统计信息收集问题
本文路径:http://scyanting.com/article/iegiis.html