We're just trying to understand the how and why. We have jobs that refresh materialized views all throughout the day. Every time there is an MV refresh, a trace file also gets generated.
An example trace file is below. What, if anything, is useful in this file? Why are these constantly being generated with MV refreshes?
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: [email protected] (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)
What information does this really tell us, aside from the fact an MV refresh was done. Why is one generated every time a refresh is done? Is that something we should turn off?
Many thanks for some insight.
There are several bugs on Oracle Support with these symptoms. Only a closer review of the trace file would say for sure, but it could be:
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.
Usually we just ignore these annoying too-much-trace-file kinds of issues. They really skyrocketed in recent versions after 11g. Just have a cleanup mechanism so you don't fill up your disk and you should be okay.
If you want to fix it, check your patch level and consider patching or upgrading to one of the versions where it's been addressed. It would be advisable to create a ticket with Oracle to get them to verify the bug first. Or, given the mention of query rewrite, try disabling query rewrite and see if that gets around it (alter system set query_rewrite_enabled=false)