物化视图相关的性能改进

今天早上开发的一个同事找到我说他早上做了一个统计查询，但是感觉速度很慢，已经过了一个小时了还没有反应。想让我看看是什么情况。
我通过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 |
|  10 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC04_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN             | ACC04_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|  12 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC11_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN             | ACC11_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|  14 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC13_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 15 |       INDEX RANGE SCAN             | ACC13_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|  16 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC15_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 17 |       INDEX RANGE SCAN             | ACC15_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|  18 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC20_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 19 |       INDEX RANGE SCAN             | ACC20_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|  20 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC22_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 21 |       INDEX RANGE SCAN             | ACC22_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|  22 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC24_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 23 |       INDEX RANGE SCAN             | ACC24_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|  24 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC31_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 25 |       INDEX RANGE SCAN             | ACC31_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|  26 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC33_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 27 |       INDEX RANGE SCAN             | ACC33_IND_CCMNN               |     1 |       |     1   (0)| 00:00:01 |
|  28 |      MAT_VIEW ACCESS BY INDEX ROWID| ACC35_USER_TEST_INFORAMATIONS |     1 |   145 |     1   (0)| 00:00:01 |
|* 29 |       INDEX RANGE SCAN             | ACC35_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 |            |
|* 13 |          INDEX FAST FULL SCAN| ACC04_IND_CCMNN         |    52M|  1043M| 41502   (3)| 00:08:19 |  Q1,00 | PCWP |            |
|  14 |         PX BLOCK ITERATOR    |                         |    52M|  1043M| 41689   (3)| 00:08:21 |  Q1,00 | PCWC |            |
|* 15 |          INDEX FAST FULL SCAN| ACC11_IND_CCMNN         |    52M|  1043M| 41689   (3)| 00:08:21 |  Q1,00 | PCWP |            |
|  16 |         PX BLOCK ITERATOR    |                         |    52M|  1043M| 40706   (3)| 00:08:09 |  Q1,00 | PCWC |            |
|* 17 |          INDEX FAST FULL SCAN| ACC13_IND_CCMNN         |    52M|  1043M| 40706   (3)| 00:08:09 |  Q1,00 | PCWP |            |
|  18 |         PX BLOCK ITERATOR    |                         |    52M|  1043M| 40485   (3)| 00:08:06 |  Q1,00 | PCWC |            |
|* 19 |          INDEX FAST FULL SCAN| ACC15_IND_CCMNN         |    52M|  1043M| 40485   (3)| 00:08:06 |  Q1,00 | PCWP |            |
|  20 |         PX BLOCK ITERATOR    |                         |    52M|  1043M| 39589   (3)| 00:07:56 |  Q1,00 | PCWC |            |
|* 21 |          INDEX FAST FULL SCAN| ACC20_IND_CCMNN         |    52M|  1043M| 39589   (3)| 00:07:56 |  Q1,00 | PCWP |            |
|  22 |         PX BLOCK ITERATOR    |                         |    52M|  1043M| 39510   (3)| 00:07:55 |  Q1,00 | PCWC |            |
|* 23 |          INDEX FAST FULL SCAN| ACC22_IND_CCMNN         |    52M|  1043M| 39510   (3)| 00:07:55 |  Q1,00 | PCWP |            |
|  24 |         PX BLOCK ITERATOR    |                         |    52M|  1043M| 41337   (3)| 00:08:17 |  Q1,00 | PCWC |            |
|* 25 |          INDEX FAST FULL SCAN| ACC24_IND_CCMNN         |    52M|  1043M| 41337   (3)| 00:08:17 |  Q1,00 | PCWP |            |
|  26 |         PX BLOCK ITERATOR    |                         |    52M|  1043M| 40486   (3)| 00:08:06 |  Q1,00 | PCWC |            |
|* 27 |          INDEX FAST FULL SCAN| ACC31_IND_CCMNN         |    52M|  1043M| 40486   (3)| 00:08:06 |  Q1,00 | PCWP |            |
|  28 |         PX BLOCK ITERATOR    |                         |    52M|  1043M| 41790   (3)| 00:08:22 |  Q1,00 | PCWC |            |
|* 29 |          INDEX FAST FULL SCAN| ACC33_IND_CCMNN         |    52M|  1043M| 41790   (3)| 00:08:22 |  Q1,00 | PCWP |            |
|  30 |         PX BLOCK ITERATOR    |                         |    52M|  1043M| 39711   (3)| 00:07:57 |  Q1,00 | PCWC |            |
|* 31 |          INDEX FAST FULL SCAN| ACC35_IND_CCMNN         |    52M|  1043M| 39711   (3)| 00:07:57 |  Q1,00 | PCWP |            |
|* 32 |       INDEX RANGE SCAN       | IND_TMP_CN06_CN         |     1 |    16 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------
谓词信息里面有一句很特别就是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
所以通过这个案例也可以看出在资源平衡的情况下，可以适度使用并行资源，尤其空闲浪费不如合理利用。

时间： 2024-09-01 05:36:08

物化视图相关的性能改进的相关文章

物化视图刷新原理与性能诊断

参考文档:Materialized View Refresh: Locking, Performance, Monitoring (文档 ID 258252.1) How to Monitor the Progress of a Materialized View Refresh (MVIEW) (文档 ID 258021.1) 1.名词解释: 基表 指的是英文里面的Master Table和Master Materialized View,并不只是只一个表,而是创建MView的时候所需要用到的

Oracle 11g r2物化视图日志新增的COMMIT SCN语句概述

在11.2中,物化视图日志也新增了一些功能,对于本地物化视图而言,现在可以利用COMMIT SCN来替代以前版本中的TIMESTAMP方式来进行刷新. 看一个简单的例子: SQL> SELECT * FROM V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle Database11gEnterprise Edition Release11

MySQL中海量数据统计处理及模拟物化视图

一 物化视图 物化视图,它是用于预先计算并保存表连接或聚集等耗时较多的操作 的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果 . 只有大型数据库oracle10g,db2才支持这个功能,而MySQL5.1暂时还没有这个功能. 本人通过事件调度和存储过程模拟了物化视图.下面大家一起来讨论吧.^-^ 二 准备知识 1)存储过程:玩过数据库的人,都知道他是啥~,~ 2)事件调度:在 MySQL5.1开始才有的新功能.说白了就是个定时器.跟java里的timer差不多. Sq

SQL Server 优化---为什么索引视图（物化视图）需要with(noexpand)强制查询提示

原文:SQL Server 优化---为什么索引视图(物化视图)需要with(noexpand)强制查询提示   本文出处:http://www.cnblogs.com/wy123/p/6694933.html    第一次通过索引视图优化SQL语句,以及遇到的一些问题,记录一下.   语句分析 最近开发递交过来一个查询统计的SQL,说是性能有问题,原本执行需要4-5秒钟,这个业务本身对性能要求又比较critical,期望是在1s之内在用尽各种办法之后(执行计划,统计信息,索引,改写SQL,临时

oracle物化视图

转自: http://www.cnblogs.com/BradMiller/archive/2011/04/24/2026321.html oracle物化视图 一.oracle物化视图基本概念 物化视图首先需要创建物化视图日志, oracle依据用户创建的物化视图日志来创建物化视图日志表, 物化视图日志表的名称为mlog$_后面跟基表的名称, 如果表名的长度超过20位,则只取前20位,当截短后出现名称重复时,oracle会自动在物化视图日志名称后面加上数字作为序号. 创建物化视图日志在建立时有

Oracle物化视图详解

一. 理论部分 Oracle的物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表.物化视图存储基于远程表的数据,也可以称为快照. Oracle的物化视图提供了强大的功能,可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果.物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能:物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性

创建增量同步Oracle物化视图问题

我们采用Oracle的物化视图增量刷新机制定时将数据库A上的某个表的数据同步到另一个数据库B上. 我们常用的最简单的实现步骤是这样.首先在数据库A上确认该表有主键,然后建立在这个表上建立 物化视图日志如"CREATE MATERIALIZED VIEW LOG ON T_tablename;",再到数据库B上创建 数据库链接和快速刷新的物化视图如"create materialized view mv_tablename refresh fast on demand star

什么是Oracle物化视图

Oracle的物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表.物化视图存储基于 远程表的数据,也可以称为快照. 物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的 得到结果.物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能:物化视图对应用透明,增加和删除物化视图不会影响应用程序 中SQL语句的正确性和有效性:物化视图需要占用存储空间:当基表发生变化

Oracle 物化视图和物化视图日志

一.相关概念 物化视图是将查询预先定义在结构中,并手动或者定期刷新将结果存储在物化视图段中,也就是说跟普通视图不同,它是需要存储空间的,从而不需要重新或者反复的执行sql语句,支持增量刷新,快速获取结果,提高数据获取的效率. 物化视图类型根据刷新模式,可分为on demand.on commit .on demand 是需要刷新时才进行刷新,可以通过job或者手动进行刷新:on commit 是DML型的刷新,一旦事务commit立即刷新. 物化视图的刷新方式有四种:fast.complete.