[20160713]为什么光标不能共享.txt

[20160713]为什么光标不能共享.txt

--这个是生产系统遇到的问题，有1条语句产生的子光标很多，但是直接查询v$sql仅仅有1个(CHILD_NUMBER很大)。
--检查为什么不能共享的原因是ROLL_INVALID_MISMATCH。

ROLL_INVALID_MISMATCH reason indicates that, after a fresh statistics, using dbms_stats.auto_invalidate
value for the parameter auto_invalidate, have been gathered on objects (table/index), the underlying cursor has been
invalidated within a time interval determined by the hidden parameter _optimizer_invalidation_period, and thereby a new
execution plan has been compiled.

--仔细检查发现这个语句访问的表几乎每天晚上10点都分析1次，这是这个原因导致光标无法共享。

1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING         VERSION        BANNER
------------------- -------------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select dbms_stats.get_param('no_invalidate') c30 from dual;
C30
------------------------------
DBMS_STATS.AUTO_INVALIDATE

SYS@book>  &r/hide _optimizer_invalidation_period
NAME                           DESCRIPTION                                                 DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------ ----------------------------------------------------------- ------------- ------------- ------------
_optimizer_invalidation_period time window for invalidation of cursors of analyzed objects TRUE          18000         18000

SYS@book> alter system set "_optimizer_invalidation_period"=20 scope=memory ;
System altered.

--//修改为20秒，不然要等18000秒(5小时),太长了。

2.测试：
SCOTT@book> create table dept1 as select * from dept;
Table created.

SCOTT@book> variable x number;
SCOTT@book> exec :x :=10 ;
PL/SQL procedure successfully completed.

SCOTT@book> select * from dept1 where deptno=:x;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  09kmg21b52xpa, child number 0
-------------------------------------
select * from dept1 where deptno=:x
Plan hash value: 3143138670
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEPT1 |      1 |    30 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT1@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO"=:X)

3.重新分析看看：
SCOTT@book> exec sys.dbms_stats.gather_table_stats ( user,'dept1',No_Invalidate => dbms_stats.AUTO_INVALIDATE);
PL/SQL procedure successfully completed.

--//退出会话，在进入才有效，不然依旧使用原来的光标。这也许是保留多个子关标的原因。

SCOTT@book> variable x number;
SCOTT@book> exec :x :=10 ;
PL/SQL procedure successfully completed.

SCOTT@book> select * from dept1 where deptno=:x;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  09kmg21b52xpa, child number 1
-------------------------------------
select * from dept1 where deptno=:x
Plan hash value: 3143138670
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEPT1 |      1 |    20 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT1@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO"=:X)

4.检查为什么不能共享：
SCOTT@book> @ &r/share 09kmg21b52xpa
SQL_TEXT                       = select * from dept1 where deptno=:x
SQL_ID                         = 09kmg21b52xpa
ADDRESS                        = 000000007D11E500
CHILD_ADDRESS                  = 000000007D075E88
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1468372438</invalidation_window><ksugctm>1468372440</ksugctm></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select * from dept1 where deptno=:x
SQL_ID                         = 09kmg21b52xpa
ADDRESS                        = 000000007D11E500
CHILD_ADDRESS                  = 000000007BB0C918
CHILD_NUMBER                   = 1
ROLL_INVALID_MISMATCH          = Y
REASON                         =
--------------------------------------------------
PL/SQL procedure successfully completed.

5.重复分析：
--退出会话再进入：
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  09kmg21b52xpa, child number 2
-------------------------------------
select * from dept1 where deptno=:x
Plan hash value: 3143138670
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEPT1 |      1 |    20 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

--//child number=2

SCOTT@book> @ &r/share 09kmg21b52xpa
SQL_TEXT                       = select * from dept1 where deptno=:x
SQL_ID                         = 09kmg21b52xpa
ADDRESS                        = 000000007D11E500
CHILD_ADDRESS                  = 000000007D075E88
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window
Exceeded(2)</reason><size>0x0</size><details>already_processed</details></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window
Exceeded(3)</reason><size>2x4</size><invalidation_window>1468372438</invalidation_window><ksugctm>1468372440</ksugctm></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select * from dept1 where deptno=:x
SQL_ID                         = 09kmg21b52xpa
ADDRESS                        = 000000007D11E500
CHILD_ADDRESS                  = 000000007BB0C918
CHILD_NUMBER                   = 1
ROLL_INVALID_MISMATCH          = Y
REASON                         = <ChildNode><ChildNumber>1</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1468372719</invalidation_window><ksugctm>1468372722</ksugctm></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select * from dept1 where deptno=:x
SQL_ID                         = 09kmg21b52xpa
ADDRESS                        = 000000007D11E500
CHILD_ADDRESS                  = 000000007BAA1858
CHILD_NUMBER                   = 2
ROLL_INVALID_MISMATCH          = Y
REASON                         =
--------------------------------------------------
PL/SQL procedure successfully completed.

SYS@book> @ &r/sharepool/shp4 09kmg21b52xpa 0
old  17:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  17:  WHERE kglobt03 = '09kmg21b52xpa'  or kglhdpar='09kmg21b52xpa' or kglhdadr='09kmg21b52xpa' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007D075E88 000000007D11E500 select * from dept1 where deptno=:x      000000007C4C3760 000000007CCE9400       4528      12144       4740     21412      21412 1448179370 09kmg21b52xpa          0
子游标句柄地址 000000007BB0C918 000000007D11E500 select * from dept1 where deptno=:x      000000007D58B188 000000007CCE9818       8600       8088       4740     21428      21428 1448179370 09kmg21b52xpa          1
子游标句柄地址 000000007BAA1858 000000007D11E500 select * from dept1 where deptno=:x      000000007D5EB438 000000007CBF31D0       8600       8088       4740     21428      21428 1448179370 09kmg21b52xpa          2
父游标句柄地址 000000007D11E500 000000007D11E500 select * from dept1 where deptno=:x      000000007CA76F78 00                     8792          0          0      8792       8792 1448179370 09kmg21b52xpa      65535

SYS@book> SELECT sql_text,sql_id,CHILD_NUMBER,invalidations FROM v$sql WHERE sql_id = '09kmg21b52xpa';
SQL_TEXT                                                     SQL_ID        CHILD_NUMBER INVALIDATIONS
------------------------------------------------------------ ------------- ------------ -------------
select * from dept1 where deptno=:x                          09kmg21b52xpa            0             0
select * from dept1 where deptno=:x                          09kmg21b52xpa            1             0
select * from dept1 where deptno=:x                          09kmg21b52xpa            2             0

--可以发现原来的光标依旧有效。INVALIDATIONS=0

6.如果重新分析使用No_Invalidate => false：
SCOTT@book> exec sys.dbms_stats.gather_table_stats ( user,'dept1',No_Invalidate => false);
PL/SQL procedure successfully completed.

SYS@book> SELECT sql_text,sql_id,CHILD_NUMBER,invalidations FROM v$sql WHERE sql_id = '09kmg21b52xpa';
SQL_TEXT                                                     SQL_ID        CHILD_NUMBER INVALIDATIONS
------------------------------------------------------------ ------------- ------------ -------------
select * from dept1 where deptno=:x                          09kmg21b52xpa            0             1
select * from dept1 where deptno=:x                          09kmg21b52xpa            1             1
select * from dept1 where deptno=:x                          09kmg21b52xpa            2             1

--//INVALIDATIONS=1.等20秒重新登录执行：
SCOTT@book> variable x number;
SCOTT@book> exec :x :=10 ;

PL/SQL procedure successfully completed.

SCOTT@book> select * from dept1 where deptno=:x;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  09kmg21b52xpa, child number 0
-------------------------------------
select * from dept1 where deptno=:x
Plan hash value: 3143138670
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEPT1 |      1 |    20 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

SYS@book> SELECT sql_text,sql_id,CHILD_NUMBER,invalidations FROM v$sql WHERE sql_id = '09kmg21b52xpa';
SQL_TEXT                                                     SQL_ID        CHILD_NUMBER INVALIDATIONS
------------------------------------------------------------ ------------- ------------ -------------
select * from dept1 where deptno=:x                          09kmg21b52xpa            0             1

--仅仅保留CHILD_NUMBER＝0.其他删除。如果建立索引呢？

SCOTT@book> create unique index pk_dept1 on dept1(deptno);
Index created.

SCOTT@book> select * from dept1 where deptno=:x;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  09kmg21b52xpa, child number 0
-------------------------------------
select * from dept1 where deptno=:x
Plan hash value: 1055957780
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT1    |      1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT1 |      1 |       |     0   (0)|          |
-----------------------------------------------------------------------------------------

SYS@book> SELECT sql_text,sql_id,CHILD_NUMBER,invalidations FROM v$sql WHERE sql_id = '09kmg21b52xpa';
SQL_TEXT                                                     SQL_ID        CHILD_NUMBER INVALIDATIONS
------------------------------------------------------------ ------------- ------------ -------------
select * from dept1 where deptno=:x                          09kmg21b52xpa            0             2

--//INVALIDATIONS变成了2次。

7.从以上分析造成原因是经常分析该表，并且No_Invalidate => dbms_stats.AUTO_INVALIDATE.如果分析没有意义，可以lock该表的统计信息。

SCOTT@book> column SUBPARTITION_NAME noprint
SCOTT@book> select * from DBA_TAB_STATS_HISTORY WHERE OWNER=USER AND TABLE_NAME='DEPT1';
OWNER  TABLE_NAME PARTITION_NAME STATS_UPDATE_TIME
------ ---------- -------------- ----------------------------------
SCOTT  DEPT1                     2016-07-13 09:12:14.022994 +08:00
SCOTT  DEPT1                     2016-07-13 09:15:29.847880 +08:00
SCOTT  DEPT1                     2016-07-13 09:28:08.413371 +08:00

--这样只要查询经常分析的表，看看是否可以lock，减少这种情况导致的光标不能共享。
SELECT OWNER, TABLE_NAME, COUNT (*)
    FROM DBA_TAB_STATS_HISTORY
    where owner not in ('SYS')
GROUP BY OWNER, TABLE_NAME
ORDER BY 3 DESC;

时间： 2024-07-30 10:53:28

[20160713]为什么光标不能共享.txt的相关文章

[20171019]关于光标共享问题.txt

[20171019]关于光标共享问题.txt --//如果sql语句光标不能共享,查看v$sql_shared_cursor视图. --//别人问的问题,如果存在两个因素是否显示2个原因.自己还是测试看看. 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                   

[20160407]光标共享TOP_LEVEL_RPI_CURSOR

[20160407]光标共享问题TOP_LEVEL_RPI_CURSOR.txt --以前做测试出现,光标不能共享TOP_LEVEL_RPI_CURSOR,不知道什么意思,链接: -- http://blog.itpub.net/267265/viewspace-765072/ --今天看blog,终于明白表示什么意思?参考链接: -- http://www.peasland.net/2016/04/05/vsql_shared_cursor-top_level_rpi_cursor/ --为了

0821Cache Buffers chains与共享模式疑问4

[20170821]Cache Buffers chains与共享模式疑问4.txt --//昨天别人问的问题,就是在读读模式下,访问相同数据块,11.2.0.4不再出现cache buffers chains latch等待事件. --//我查询我以前写的博客,链接如下:http://blog.itpub.net/267265/viewspace-1822491/ --//也就是oracle在这样模式下不再采用EXCLUSIVE模式获取cache buffers chains latch.当时

[20170621]Session Cursor Caching 2.txt

[20170621]Session Cursor Caching 2.txt --//当sql执行时,第一次要经历硬分析,第二次软分析,如果session_cached_cursors设置的化,还可以绕过软分析,也有人叫"软软分析". --//摘录链接的一段话: --//www.toadworld.com/platforms/oracle/b/weblog/archive/2017/04/12/session-cursor-caching-part-one When a SQL sta

[20161230]查看父游标中sql语句.txt

[20161230]查看父游标中sql语句.txt --上午巡检完,无聊,测试使用oradebug下查看sql语句在父游标中的内容.sql语句在执行第一次硬解析时生成父子游标,其中父游标chunk --中保存sql语句,测试通过oradebug下如何查看: 1.环境: SCOTT@book> select * from dept where deptno=10;     DEPTNO DNAME          LOC ---------- -------------- -----------

[20161219]关于LANGUAGE_MISMATCH.txt

[20161219]为什么光标不共享(LANGUAGE_MISMATCH).txt --生产系统看看那种情况出现比较多,写一个脚本: select sum(decode(UNBOUND_CURSOR,'Y',1,0))    UNBOUND_CURSOR, sum(decode(SQL_TYPE_MISMATCH,'Y',1,0))    SQL_TYPE_MISMATCH, sum(decode(OPTIMIZER_MISMATCH,'Y',1,0))    OPTIMIZER_MISMATC

[20120915]10046事件与执行计划改变.txt

    使用10046事件来跟踪解决oracle的许多问题,是非常常用的手段,但是实际上可能出现跟踪的sql执行计划与原来不同的情况,自己应该引起注意. 测试如下: 1.测试环境建立: SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition

[20160517]11GR2Cursor_Sharing=force的bug

[20160517]11GR2Cursor_Sharing=force的bug.txt --链接https://jonathanlewis.wordpress.com/2016/05/16/cursor_sharing-problem/,重复测试: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING         VERSION    BANNER ------------------- ---------- --------------------------

javascript语法

     alert(new Date().toLocaleString());       varnum = new Number();       num =11;      alert(num);       var a= "a";      alert(a);       var b= 'b';      alert(b);       var a= "123";       if(isNaN(a)) {      alert("是一个数字&quo