SELECT version_count, sql_id, sql_text
WHERE version_count > 100
ORDER BY version_count desc ;
version_count sql_id sql_text
983 fsu97v8y9c21y SELECT OLD_NEW$$, COUNT(*) FROM "GHMES"."MLOG$_MES_QM_MATERIAL" WHERE SNAPTIME$$ > :1 AND SNAPTIME$$ <= :2 GROUP BY OLD_NEW$$
"MAS$".SNAPTIME$$ > :1 ) AS OF SNAPSHOT (:2) MAS$)
Bug 8865718 - Recursive cursors for MV refresh not shared [ID 8865718.8]
Bug 8865718 Recursive cursors for MV refresh not shared
This note gives a brief overview of bug 8865718.
The content was last updated on: 13-NOV-2012
Click here for details of each of the sections below.
Oracle Server (Rdbms)
Range of versions believed to be affected
Versions >= 10 but BELOW 12.1
Versions confirmed as being affected
Generic (all / most platforms affected)
This issue is fixed in
12.1 (Future Release)
220.127.116.11 (Server Patch Set)
18.104.22.168.2 (Patch Set Update)
22.214.171.124 Bundle Patch 6 for Exadata Database
126.96.36.199.4 (Patch Set Update)
10.2.0.5.3 Patch Set Update
188.8.131.52 Patch 3 on Windows Platforms
184.108.40.206 Patch 24 on Windows Platforms
10.2.0.5 Patch 7 on Windows Platforms
Internal Error May Occur (ORA-600)
Performance Of Certain Operations Affected
Shared Pool Affected
Cursor not shared due to FLASHBACK_CURSOR
Query Rewrite (Including Materialized Views)
Certain recursive statements generated during a MATERIALIZE VIEW refresh
that access dictionary tables are not shared (due to FLASHBACK_CURSOR) when they
could be. This can cause progressive performance degradation or even
ORA-600 [kksfbc-new-child-thresh-exceeded] errors for those statements.
The recursive SQL contains affected contains "AS OF SNAPSHOT" clauses
and has high version counts.
The recursive SQL is against dictionary tables (like sumdelta$) and not against
the MV tables themselves.
Note: It is normal for cursors using "AS OF SNAPSHOT" clauses
not to be shared during MV refresh - this issue is specific to certain
dictionary related cursors used during Materialized View refresh which
are a special case and can be shared under specific circumstances. In
particular note that this fix only affects certain dictionary SQL - "AS OF"
statements accessing the MV tables themselves are still not shared with this
fix - this is normal and expected
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25702/viewspace-750748/，如需转载，请注明出处，否则将追究法律责任。