物化视图相关的性能改进 (r7笔记第58天)

今天早上开发的一个同事找到我说他早上做了一个统计查询,但是感觉速度很慢,已经过了一个小时了还没有反应。想让我看看是什么情况。 我通过v$session查到有一个会话确实已经持续了近一个小时,查看sql语句是一个create table select * from xxx这样格式的语句。也就是通过关联查询创建出一个所谓的临时表来。 语句如下: create table APP_BI_ENCRYPT_QUERY.t_result_1312 as select t1.SECURITY_PHONE as MOBILE_PHONE, t1.SECURITY_EMAIL as OTHER_EMAIL, t2.* from USER_TEST_INFORAMATIONS t1, bidata.TMP_CN06 t2 where t1.CN_MASTER = t2.CN 其中一个表是TMP_CN06,这个表中的数据是临时从应用端得到的数据,大概有30多万条,另外一个就是一个视图USER_TEST_INFORAMATIONS,这个视图里面包含有12个物化视图。 所以我的初步感觉速度慢就是因为统计信息导致。 带着疑问查看了执行计划,发现统计信息缺失有较大的出入,TMP_CN06中目前有30多万的数据,但是通过统计信息得到只有8万多。

********** TABLE GENERAL INFO *****************
TABLE_NAME                     PAR TABLESPACE STATUS  INI_TRANS    NUM_ROWS     BLOCKS EMPTY_BLOCKS LOG MON ROW_MOVE LAST_ANALYZED
------------------------------ --- ---------- ------ ----------  ---------- ---------- ------------ --- --- -------- -------------------
TMP_CN06                       NO  BIDATA_DATA VALID           1       80953      13157            0 YES YES DISABLED 2015-12-14 18:22:38
Plan hash value: 192997736
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT             |                               |       |       |  2145K(100)|          |
|   1 |  LOAD AS SELECT                    |                               |       |       |            |          |
|   2 |   NESTED LOOPS                     |                               |   949K|   143M|  2142K  (1)| 07:08:25 |
|   3 |    TABLE ACCESS FULL               | TMP_CN06                      | 80953 |  1106K|  2294   (1)| 00:00:28 |
|   4 |    VIEW                            | USER_TEST_INFORAMATIONS       |     1 |   145 |    26   (0)| 00:00:01 |
|   5 |     UNION ALL PUSHED PREDICATE     |                               |       |       |            |          |
|   6 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC00_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN             | ACC00_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|   8 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC02_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN             | ACC02_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
。。。

带着疑问对统计信息进行了初步的收集。现在表中的数据已经有30多万了。

TABLE_NAME                     PAR TABLESPACE STATUS  INI_TRANS    NUM_ROWS     BLOCKS EMPTY_BLOCKS LOG MON ROW_MOVE LAST_ANALYZED
------------------------------ --- ---------- ------ ----------  ---------- ---------- ------------ --- --- -------- -------------------
TMP_CN06                       NO  BIDATA_DAT VALID           1      339774      13157            0 YES YES DISABLED 2015-12-25 10:17:05

然后就开始对物化视图的统计信息进行了收集,因为物化视图的统计信息也是过期了。 exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'ACCSTAT', TABNAME =>'ACC00_USER_TEST_INFORAMATIONS' ,CASCADE =>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',DEGREE =>4, GRANULARITY =>'ALL'); exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'ACCSTAT', TABNAME =>'ACC02_USER_TEST_INFORAMATIONS' ,CASCADE =>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1',DEGREE =>4, GRANULARITY =>'ALL'); 。。。 然后开启了sql monitor进行了监控,尝试创建一个测试表来看看性能。比如sql_id为2998bdn9nqf45 set linesize 150 col comm format a200 set long 99999 SELECT dbms_sqltune.report_sql_monitor( sql_id => '2998bdn9nqf45', report_level => 'ALL', type=>'HTML' ) comm FROM dual; 但是从sql monitor的结果报告来看,效果还是不够好,因为产生了大量的io等待事件,对于这个问题进行了关联分析,发现早上的高峰期里,会有大量的全表扫描在这个视图上,所以性能也会大大受到影响。之前的那个问题还没考虑好怎么处理,又来一波。 之前比较纠结的sql语句是下面的样子,做了全模糊,看起来优化空间极小。后面再做处理。 SELECT "UIN","CN_MASTER","USERFROM" FROM "USER_TEST_INFORAMATIONS" "B" WHERE "UIN">501900128 AND ( R EGEXP_LIKE ("USERFROM",'dj2','i') OR REGEXP_LIKE ("USERFROM",'jd','i')) 这个时候查看sar的结果,发现在查询性能较差的时间段,其实CPU,IO的消耗还是不大。 09:30:01 AM all 1.58 0.00 0.43 3.82 0.00 94.17 09:40:01 AM all 1.74 0.00 0.43 3.91 0.00 93.92 09:50:01 AM all 0.84 0.00 0.23 3.94 0.00 94.99 10:00:01 AM all 0.36 0.00 0.14 4.06 0.00 95.43 10:10:01 AM all 0.36 0.00 0.13 4.05 0.00 95.46 10:20:01 AM all 0.29 0.00 0.14 4.06 0.00 95.52 10:30:01 AM all 6.15 0.00 0.30 4.15 0.00 89.40 10:40:01 AM all 3.79 0.00 0.18 4.11 0.00 91.92 10:50:01 AM all 2.02 0.00 0.27 2.67 0.00 95.04 11:00:01 AM all 4.20 0.00 0.30 1.91 0.00 93.59 11:10:01 AM all 4.48 0.00 0.18 1.16 0.00 94.19 11:20:01 AM all 1.25 0.00 0.19 1.16 0.00 97.40 那么这个时候,如果还想做点什么,并行就是一个一剂良药,尤其是对大数据量尤其有效。 原本的并行度为1, select table_name,degree from dba_tables where table_name like '%USER_TEST_INFORAMATIONS'; TABLE_NAME DEGREE ------------------------------ -------------------- ACC00_USER_TEST_INFORAMATIONS 1 ACC02_USER_TEST_INFORAMATIONS 1 ACC04_USER_TEST_INFORAMATIONS 1 。。。 12 rows selected. 然后设置了每个物化视图并行度为4,再次查看效果。 alter table ACC00_USER_TEST_INFORAMATIONS parallel 4; alter table ACC02_USER_TEST_INFORAMATIONS parallel 4; 。。。 查看执行计划如下。和原本的执行计划产生了较大的差别,索引扫描从范围扫描变为了快速全扫描。

Plan hash value: 1716701289
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  |  Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |        |       |   489K(100)|          |        |      |            |
|   1 |  SORT AGGREGATE              |                         |     1  |    43 |            |          |        |      |            |
|   2 |   PX COORDINATOR             |                         |        |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)       | :TQ10000                |     1  |    43 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE           |                         |     1  |    43 |            |          |  Q1,00 | PCWP |            |
|   5 |      NESTED LOOPS            |                         |   3985K|   163M|   489K  (3)| 01:37:49 |  Q1,00 | PCWP |            |
|   6 |       VIEW                   | USER_TEST_INFORAMATIONS |    625M|    15G|   488K  (3)| 01:37:42 |  Q1,00 | PCWP |            |
|   7 |        UNION-ALL             |                         |        |       |            |          |  Q1,00 | PCWP |            |
|   8 |         PX BLOCK ITERATOR    |                         |     52M|  1043M| 42122   (3)| 00:08:26 |  Q1,00 | PCWC |            |
|*  9 |          INDEX FAST FULL SCAN| ACC00_IND_CCMNN         |     52M|  1043M| 42122   (3)| 00:08:26 |  Q1,00 | PCWP |            |
|  10 |         PX BLOCK ITERATOR    |                         |     52M|  1043M| 39510   (3)| 00:07:55 |  Q1,00 | PCWC |            |
|* 11 |          INDEX FAST FULL SCAN| ACC02_IND_CCMNN         |     52M|  1043M| 39510   (3)| 00:07:55 |  Q1,00 | PCWP |            |
|  12 |         PX BLOCK ITERATOR    |                         |     52M|  1043M| 41502   (3)| 00:08:19 |  Q1,00 | PCWC |            |

谓词信息里面有一句很特别就是cn字段开始走了索引,而在最开始的语句中是走全表扫描。 32 - access("T1"."CN_MASTER"="T2"."CN") 再次运行这个语句。 create table accstat.test_1225 as select t1.SECURITY_PHONE as MOBILE_PHONE, t1.SECURITY_EMAIL as OTHER_EMAIL, t2.* from USER_TEST_INFORAMATIONS t1, bidata.TMP_CN06 t2 where t1.CN_MASTER = t2.CN; 查看session的情况,就会发现存在了5个关联的session,可见并行度4起作用了。 至于效果如何呢。发现效率大大提升,已经提升至2分钟了,然后我就可以放心大胆的交给同事去操作了。当然从他那边的反馈来说,速度也是杠杠的。 SQL> @b.sql Table created. Elapsed: 00:01:56.82 所以通过这个案例也可以看出在资源平衡的情况下,可以适度使用并行资源,尤其空闲浪费不如合理利用。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-12-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第七篇(r2笔记99天)

在数据迁移完成之后,开始了例行的后期数据库维护,早上一来就发现了一个sql执行时间很长了。达到了37279秒。最后在改进调优之后执行速度在1分钟以内。 这个速度...

35880
来自专栏数据库新发现

关于dirty buffer

SQL> select VIEW_DEFINITION from v$fixed_view_definition where VIEW_NAME = 'GV$B...

10730
来自专栏杨建荣的学习笔记

impdp ORA-39002,ORA-39166,ORA-39164的问题及解决(r2第6天)

今天在做imp和impdp的性能测试时,发现如果表中存在lob字段,加载真是慢的厉害,每秒钟大概1000条的样子,按照这种速度,基本上不用干活了。 比如5千万条...

33870
来自专栏杨建荣的学习笔记

深度解析dba_segments和sys.seg$中的细节差异(上) (r5笔记第27天)

今天在查看系统空间使用情况的时候,发现一个细节的问题,自己死磕了一把,还是发现了不少有价值的东西。 事情的起因是我在使用脚本在某个环境中查看每个用户所占有的空间...

36180
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第九篇(r3笔记第34天)

生产环境中有一些sql语句是不定时炸弹,不声不响的运行着,可能相关的表很大,运行时间达数小时,甚至数天。 上周在生产环境中发现一条sql语句,运行时间几乎是按照...

34850
来自专栏杨建荣的学习笔记

关于等待事件"read by other session"(r3笔记第89天)

在查看数据库负载的时候,发现早上10点开始到12点的这两个钟头,系统负载异常的高。于是抓取了一个awr报告。 Snap IdSnap TimeSessions...

30090
来自专栏杨建荣的学习笔记

SQL*Loader-805的解决(r2笔记36天)

使用sql*loader是大型项目中数据迁移的利器。如果是外部系统,其他数据库到oracle的数据迁移,使用文本式文件是最兼容的方式。 sqlldr的加载效率是...

32080
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第六篇(r2笔记91天)

生产环境中有大量的sql语句在运行,尽管有awr,ash做数据的收集统计,但是dba的调优工作大多数情况都是在问题已经发生后做排查的,有些sql语句可能执行的时...

28040
来自专栏数据和云

追本溯源:Oracle 只读表空间的探索实践

作者简介 ? 胡中豪 云和恩墨西区交付工程师,多年一线 DBA 经验,曾服务于运营商、电网、政府行业、银行等行业客户;擅长数据库故障处理、性能优化、实施升级 本...

29530
来自专栏杨建荣的学习笔记

一个SQL性能问题的优化探索(二)(r11笔记第38天)

继续前几天的一个案例一个SQL性能问题的优化探索(一)(r11笔记第33天) 如下的SQL语句存在索引字段CARD_NO,但是执行的时候却走了全表扫描,因为这是...

36080

扫码关注云+社区

领取腾讯云代金券