ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle bug 8865718产生大量不可共享物化视图sql

oracle bug 8865718产生大量不可共享物化视图sql

原创 Linux操作系统 作者:nmgzw 时间:2019-07-02 18:57:06 0 删除 编辑
SELECT version_count, sql_id, sql_text
          FROM   v$sqlarea
           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$) 

查看v$sql_shared_cursor发现,不可共享原因为:
FLASHBACK_CURSOR:          14

BUG说明:

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.
 
Affects:
 Product (Component)
   Oracle Server (Rdbms)

Range of versions believed to be affected
 
Versions >= 10 but BELOW 12.1

Versions confirmed as being affected
 
11.2.0.1
  11.1.0.7
  11.1.0.6
  10.2.0.5
  10.2.0.4


Platforms affected

 Generic (all / most platforms affected)


Fixed:
  This issue is fixed in

   12.1 (Future Release)
    11.2.0.2 (Server Patch Set)
    11.2.0.1.2 (Patch Set Update)
    11.2.0.1 Bundle Patch 6 for Exadata Database
    11.1.0.7.4 (Patch Set Update)
    10.2.0.5.3 Patch Set Update
    11.2.0.1 Patch 3 on Windows Platforms
    11.1.0.7 Patch 24 on Windows Platforms
    10.2.0.5 Patch 7 on Windows Platforms

  Symptoms:
 

Related To:

 Internal Error May Occur (ORA-600)
  Performance Of Certain Operations Affected
  Shared Pool Affected
  ORA-600 [kksfbc-new-child-thresh-exceeded]
  Cursor not shared due to FLASHBACK_CURSOR

Query Rewrite (Including Materialized Views)
  V$SQL_SHARED_CURSOR


Description

 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.
 
Rediscovery Notes:
 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-10-18

  • 博文量
    71
  • 访问量
    65027