归档日志量增长异常分析_结论为物化视图无法使用增量刷新导致

对于客户的问题故障进行总结

1）问题现象

归档日志，每小时切换最低60于次，每天产生归档日志720g,由于归档日志过多，定时清理归档不及时，导致Arch磁盘组空间极易消耗殆尽，导致业务无法操作，业务连续性收到影响。

2）短期处理

手工处理，临时清理一天前归档，保障业务连续性

RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';

3)问题定位

使用Logminer工具进行挖掘，在2019年03月05日晚上9点，与2019年03月06日早上7点，日志挖掘top3输出结果相同。

对于数据库来说，不同时间段，业务连续性的对同一个表insert,delete，JOB定时调用可能实现。
USERNAME    OWNER    NAME    TYPE_NAME    OPERATION    COUNT
BJ_SELECTION    BJ_SELECTION    YD_BARGAIN_PRICE    TABLE    INSERT    1481521
BJ_SELECTION    BJ_SELECTION    YD_BARGAIN_PRICE    TABLE    DELETE    1481521
BJ_SELECTION    　    　    　                              INTERNAL    4444594

JOB视图查询
SQL> select job,log_user,last_date,next_date,broken,interval,what from dba_jobs where WHAT like '%YD_BARGAIN_PRICE%';
       JOB LOG_USER                         LAST_DATE       NEXT_DATE           B      INTERVAL      WHAT
---------- ----------------------------- ----------------------------- -------------------
      1365 BJ_SELECTION                   2019-03-06 09:29:53
2019-03-06 09:29:59 N
SYSDATE + NUMTODSINTERVAL(2,'SECOND')
dbms_refresh.refresh('"BJ_SELECTION"."YD_BARGAIN_PRICE"');       
      1366 BJ_TEST_SELECTION              2019-03-06 09:30:02
2019-03-06 09:30:04 N
SYSDATE + NUMTODSINTERVAL(2,'SECOND')
dbms_refresh.refresh('"BJ_TEST_SELECTION"."YD_BARGAIN_PRICE"');
JOB间隔2s，执行物化视图刷新。

通过SQL视图查询，发现以下两个物化视图，确实间隔3s刷新
SQL>
 select owner,MVIEW_NAME,UPDATABLE,UPDATE_LOG,MASTER_LINK,REWRITE_ENABLED,REWRITE_CAPABILITY,REFRESH_MODE,REFRESH_METHOD,FAST_REFRESHABLE
,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from dba_mviews where owner in ('BJ_TEST_SELECTION','BJ_SELECTION') and MVIEW_NAME ='YD_BARGAIN_PRICE'
OWNER                MVIEW_NAME       U UP MAST R REWRITE_C REFRES REFRESH_ FAS LAST_REF LAST_REFRESH_DATE
-------------------- ---------------- - -- ---- - --------- ------ -------- --- -------- -------------------
BJ_SELECTION         YD_BARGAIN_PRICE N         N GENERAL   DEMAND FORCE    NO  COMPLETE 2019-03-06 10:22:18
BJ_TEST_SELECTION    YD_BARGAIN_PRICE N         N GENERAL   DEMAND FORCE    NO  COMPLETE 2019-03-06 10:22:28

SQL>
 select owner,MVIEW_NAME,UPDATABLE,UPDATE_LOG,MASTER_LINK,REWRITE_ENABLED,REWRITE_CAPABILITY,REFRESH_MODE,REFRESH_METHOD,
FAST_REFRESHABLE,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from dba_mviews where owner in ('BJ_TEST_SELECTION','BJ_SELECTION')
 and MVIEW_NAME ='YD_BARGAIN_PRICE'

OWNER                MVIEW_NAME       U UP MAST R REWRITE_C REFRES REFRESH_ FAS LAST_REF LAST_REFRESH_DATE
-------------------- ---------------- - -- ---- - --------- ------ -------- --- -------- -------------------
BJ_SELECTION         YD_BARGAIN_PRICE N         N GENERAL   DEMAND FORCE    NO  COMPLETE 2019-03-06 10:22:26
BJ_TEST_SELECTION    YD_BARGAIN_PRICE N         N GENERAL   DEMAND FORCE    NO  COMPLETE 2019-03-06 10:22:33

查询发现，对象的创建时间与归档日志量开始激增时间点匹配
select owner,object_name,object_type,status,created from dba_objects where object_name='YD_BARGAIN_PRICE' and owner='BJ_SELECTION';
OWNER           OBJECT_NAME          OBJECT_TYPE         STATUS  CREATED
--------------- -------------------- ------------------- ------- -------------------
BJ_SELECTION    YD_BARGAIN_PRICE     TABLE               VALID   2019-03-01 13:03:29
BJ_SELECTION    YD_BARGAIN_PRICE     MATERIALIZED VIEW   VALID   2019-03-01 13:03:30

GET_DDL物化视图创建语法
SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','YD_BARGAIN_PRICE','BJ_SELECTION') ddl_text from dual;
DDL_TEXT
--------------------------------------------------------------------------------
分析函数将无法使用快速增量刷新
  CREATE MATERIALIZED VIEW "BJ_SELECTION"."YD_BARGAIN_PRICE" ("ID", "PROJECT_ID", "HOS_ID", "PRODUCT_ID", "PRICE", "UPD
ATE_TIME", "BID_DATE")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BJ_TEST_SELECTION"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + NUMTODSINTERVAL(2,'SECOND')
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS select  r.ID,r.PROJECT_ID,r.HOS_ID,r.PRODUCT_ID ,
                                   r.PRICE,r.UPDATE_TIME,r.BID_DATE from (
                                                                SELECT
                                                                  ID,
                                                                  PRODUCT_ID,
                                                                  ORG_PRICE AS PRICE,
                                                                  PROJECT_ID,
                                                                  HOS_ID,
                                                                  UPDATE_TIME,
                                                                  BID_DATE,
                                                                  row_number() over(partition by PROJECT_ID, HOS_ID
, PRODUCT_ID order by UPDATE_TIME desc) rid
                                                                FROM YD_BARGAIN_ITEM
                                                                WHERE HOS_FLAG = '3'
                                                              ) r where r.rid='1';
                                          
SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','YD_BARGAIN_PRICE','BJ_TEST_SELECTION') ddl_text from dual;
   CREATE MATERIALIZED VIEW "BJ_TEST_SELECTION"."YD_BARGAIN_PRICE"••••••
发现信息：
物化视图涉及的对象是当前用户下同一个对象

查询物化视图涉及的基表
SQL>  select owner,object_name,object_type,status,created from dba_objects where owner='BJ_SELECTION' and object_name='YD_BARGAIN_ITEM';
OWNER           OBJECT_NAME          OBJECT_TYPE         STATUS  CREATED
--------------- -------------------- ------------------- ------- -------------------
BJ_SELECTION    YD_BARGAIN_ITEM      TABLE               VALID   2018-12-22 13:59:12
1.两个物化视图，间隔2s刷新导致的日志量激增
2.物化视图底层涉及的基表是相同的

4）问题解决

在于业务人员沟通需求后，建议业务SQL调整为直接访问基表，无需物化视图中间环节处理。在业务SQL修改后，确认业务SQL的执行效率能满足需要，删除停用原物化视图，问题得到最终解决。

5）对比

2019年03月06日，中午12点停用两个物化视图刷新后，归档日志切换恢复正常。

Redo日志大小对比	20190303 13:00-14:00	20190306 13:00-14:00
Redo size:	5597015.3（异常时）	9370.6（恢复后）