[20120421] cursor_sharing=similar和子光标问题.txt

[20120421] cursor_sharing=similar和子光标问题.txt

如果设置cursor_sharing=similar如果存在直方图会产生大量子光标，11GR2的新特性ACS可以很好的解决问题。
自己做一些测试说明cursor_sharing=similar产生大量子光标的问题。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE TABLE t1 AS

SELECT ROWNUM id1,FLOOR(SQRT(ROWNUM)) id2 ,'test' NAME FROM DUAL CONNECT BY LEVEL

SQL> insert into t1 select rownum+9999,100 ,'test' NAME FROM DUAL CONNECT BY LEVEL

SQL> create index i_t1_id2 on t1(id2);

Index created.

BEGIN

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

      OwnName        => user

     ,TabName        => 'T1'

    ,Estimate_Percent  => NULL

    ,Method_Opt        => 'FOR COLUMNS ID2 SIZE 254'

    ,Degree            => 4

    ,Cascade           => TRUE

    ,No_Invalidate     => TRUE);

END;

/

SQL> column low_value format a10

SQL> column high_value format a10

SQL> column data_type format a10

SQL> select column_name,data_type,num_distinct,low_value,high_value,num_buckets,histogram from dba_tab_cols where wner=user and table_name='T1';

COLUMN_NAME                    DATA_TYPE  NUM_DISTINCT LOW_VALUE  HIGH_VALUE NUM_BUCKETS HISTOGRAM

------------------------------ ---------- ------------ ---------- ---------- ----------- ---------------

ID1                            NUMBER            19998 C102       C3026463             1 NONE

ID2                            NUMBER              100 C102       C202               100 FREQUENCY

NAME                           CHAR                  1 74657374   74657374             1 NONE

SQL>

--可以发现ID2建立的直方图是FREQUENCY直方图。

2.检查与建立测试脚本,适当编辑一下：

set head off trimout on trimspool on

column a format a100

spool aa1.sql

select 'set termout off'  a from dual 

union all

select 'select * from t1 where id2= '||rownum||';' a from dual connect by level

union all

select 'set termout on'  a from dual ;

3.测试：

SQL> alter system flush shared_pool;

System altered.

SQL> @aa1.sql

SQL> @dpc

PLAN_TABLE_OUTPUT

---------------------------------------

SQL_ID  6h6rk80d55p2n, child number 109

---------------------------------------

select * from t1 where id2= :"SYS_B_0"

Plan hash value: 3984699272

----------------------------------------------------------------------

| Id  | Operation                   | Name     | E-Rows | Cost (%CPU)|

----------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |        |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T1_ID2 |      1 |     1   (0)|

----------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - :SYS_B_0 (NUMBER): 110

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID2"=:SYS_B_0)

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

30 rows selected.

--可以发现产生110个子光标。

SQL> @share 6h6rk80d55p2n

old  15:           and q.sql_id like ''&1''',

new  15:           and q.sql_id like ''6h6rk80d55p2n''',

SQL_TEXT                       = select * from t1 where id2= :"SYS_B_0"

SQL_ID                         = 6h6rk80d55p2n

ADDRESS                        = 000000009F969990

CHILD_ADDRESS                  = 0000000097A87EF0

CHILD_NUMBER                   = 0

--------------------------------------------------

SQL_TEXT                       = select * from t1 where id2= :"SYS_B_0"

SQL_ID                         = 6h6rk80d55p2n

ADDRESS                        = 000000009F969990

CHILD_ADDRESS                  = 0000000097A3C258

CHILD_NUMBER                   = 1

HASH_MATCH_FAILED              = Y

--------------------------------------------------

...

SQL_TEXT                       = select * from t1 where id2= :"SYS_B_0"

SQL_ID                         = 6h6rk80d55p2n

ADDRESS                        = 000000009F969990

CHILD_ADDRESS                  = 000000009F97F400

CHILD_NUMBER                   = 108

HASH_MATCH_FAILED              = Y

--------------------------------------------------

SQL_TEXT                       = select * from t1 where id2= :"SYS_B_0"

SQL_ID                         = 6h6rk80d55p2n

ADDRESS                        = 000000009F969990

CHILD_ADDRESS                  = 000000009F99D158

CHILD_NUMBER                   = 109

HASH_MATCH_FAILED              = Y

--------------------------------------------------

PL/SQL procedure successfully completed.

--可以想象如果多个字段都存在直方图，这样问题会更加严重。

4.再建立一个例子来测试：

create table t2 as

SELECT ROWNUM ID, id1, id2, 'test' NAME

  FROM (SELECT ROWNUM id1 FROM DUAL CONNECT BY level 

       (SELECT ROWNUM id2 FROM DUAL CONNECT BY level 

SQL> create index i_t2_id1_id2 on t1(id1,id2);

Index created.

BEGIN

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

      OwnName        => user

     ,TabName        => 'T2'

    ,Estimate_Percent  => NULL

    ,Method_Opt        => 'FOR COLUMNS ID2 SIZE 254 FOR COLUMNS ID1 SIZE 254'

    ,Degree            => 4

    ,Cascade           => TRUE

    ,No_Invalidate     => TRUE);

END;

/

SQL> column low_value format a10

SQL> column high_value format a10

SQL> column data_type format a10

SQL> select column_name,data_type,num_distinct,low_value,high_value,num_buckets,histogram from dba_tab_cols where wner=user and table_name='T2';

COLUMN_NAME                    DATA_TYPE  NUM_DISTINCT LOW_VALUE  HIGH_VALUE NUM_BUCKETS HISTOGRAM

------------------------------ ---------- ------------ ---------- ---------- ----------- ---------------

ID                             NUMBER                                                    NONE

ID1                            NUMBER              100 C102       C202               100 FREQUENCY

ID2                            NUMBER              100 C102       C202               100 FREQUENCY

NAME                           CHAR                                                      NONE

--可以发现ID1,ID2建立的直方图是FREQUENCY直方图,NUM_BUCKETS=100.

5.建立测试脚本,适当编辑满足执行需要：

set head off trimout on trimspool on

column a format a100

spool aa2.sql

select 'set termout off'  a from dual 

union all

SELECT 'select * from t2 where id1='||a.id1||' and id2='||b.id2||';' a

  FROM (SELECT ROWNUM id1 FROM DUAL CONNECT BY level 

       (SELECT ROWNUM id2 FROM DUAL CONNECT BY level 

union all

select 'set termout on'  a from dual ;

--使用常量，看看在cursor_sharing=SIMILAR会生产多少子光标。

6.测试

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------------------------------------

cursor_sharing                       string      SIMILAR

SQL> alter system flush shared_pool;

System altered.

SQL> @aa2.sql

SQL> @dpc

PLAN_TABLE_OUTPUT

----------------------------------------

SQL_ID  bnfd8u14j8vhr, child number 9999

----------------------------------------

select * from t2 where id1=:"SYS_B_0" and id2=:"SYS_B_1"

Plan hash value: 2782261673

--------------------------------------------------------------------------

| Id  | Operation                   | Name         | E-Rows | Cost (%CPU)|

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |        |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T2           |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T2_ID1_ID2 |      1 |     1   (0)|

--------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - :SYS_B_0 (NUMBER): 100

   2 - :SYS_B_1 (NUMBER): 100

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID1"=:SYS_B_0 AND "ID2"=:SYS_B_1)

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

31 rows selected.

--child number ＝9999，有10000个子光标。

--如果在生产系统，这个跟没有使用绑定变量效果也许差不多。

7.看来cursor_sharing=SIMILAR,淘汰是必然的。