MV刷新时的Oracle跟踪文件

lymgl2op  于 2023-05-16  发布在  Oracle
关注(0)|答案(1)|浏览(135)

我们只是想弄明白是怎么回事为什么会这样。我们有一个全天刷新实体化视图的作业。每次MV刷新时,也会生成跟踪文件。
下面是一个示例跟踪文件。这个文件中有什么有用的信息?为什么MV刷新会不断产生这些问题?

Trace file /opt/app/oracle/diag/rdbms/ni00/NI00/trace/NI00_ora_1172699.trc
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME:    /opt/app/oracle/product/12.2.0/dbhome_1
System name:    Linux
Node name:      db1.com
Release:        5.4.17-2136.316.7.el8uek.x86_64
Version:        #2 SMP Mon Jan 23 18:37:18 PST 2023
Machine:        x86_64
Instance name: NI00
Redo thread mounted by this instance: 1
Oracle process number: 441
Unix process pid: 1172699, image: oracle@db1.com (TNS V1-V3)

*** 2023-05-15T08:46:12.761019-05:00
*** SESSION ID:(8962.49880) 2023-05-15T08:46:12.761031-05:00
*** CLIENT ID:() 2023-05-15T08:46:12.761035-05:00
*** SERVICE NAME:(SYS$USERS) 2023-05-15T08:46:12.761038-05:00
*** MODULE NAME:(SQL*Plus) 2023-05-15T08:46:12.761042-05:00
*** ACTION NAME:() 2023-05-15T08:46:12.761045-05:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-05-15T08:46:12.761047-05:00
 
Hctx: MV[0] = TEMPLATE43_MV num_steps = 0 
Hctx: TBL[0] = TEMPLATE43_DATA  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[1] = DAILY_RANK  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[2] = RANK_TYPES  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[3] = INDUSTRY_SECTION_DATA_SP500  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[4] = STOCK_DATA  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[5] = YESOP_EARNINGS_SURPRISES  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[6] = ZERN_SURPHIST  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[7] = ZERN_SURPHIST  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[8] = NFM_YESOP_INTRA  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[9] = COMP_NAME_AP  (ins, del, up, dl) = (0 0 0 0)
Hctx: TBL[10] = UBER_MASTER_MV  (ins, del, up, dl) = (0 0 0 0)

除了MV刷新完成的事实之外,这真的告诉我们什么信息。为什么每次刷新都会生成一个?我们是不是该把它关掉?
非常感谢您的一些见解。

k2arahey

k2arahey1#

Oracle支持中存在多个具有这些症状的错误。只有仔细查看跟踪文件才能确定,但它可能是:

Bug 27304410  Small trace file containing "kkzlshrinkmvlog" created upon each mview refresh

Versions confirmed as being affected    
12.2.0.1 (Base Release)

The fix for 27304410 is first included in   
19.1.0
Database Release Update (DB RU) 18.3.0.0.180717 (Jul 2018)
12.2.0.1.180717 (Jul 2018) Database Jul2018 Release Update (DB RU)
18.3.0.0.180717 (Jul 2018) Bundle Patch for Windows Platforms
12.2.0.1.180717 (Jul 2018) Bundle Patch for Windows Platforms

This bug is only relevant when using Query Rewrite (Including Materialized Views)
A small trace file containing "kkzlShrinkMVLog" is produced for every
 refresh. This fix removes the unnecessary tracing.

通常我们忽略这些烦人的跟踪文件太多的问题。在11g之后,它们在最近的版本中确实飙升。只要有一个清理机制,这样你就不会填满你的磁盘,你应该没事。
如果你想修复它,请检查你的补丁级别,并考虑打补丁或升级到解决它的版本之一。建议您与Oracle创建一个票证,让他们首先验证错误。或者,如果提到了查询重写,请尝试禁用查询重写,看看是否能解决这个问题(alter system set query_rewrite_enabled=false

相关问题