ITPub博客

首页 > Linux操作系统 > Linux操作系统 > adative cursor sharing 自己测试脚本

adative cursor sharing 自己测试脚本

原创 Linux操作系统 作者:gaolu1234 时间:2013-10-12 14:01:51 0 删除 编辑

建立测试表:
create table acsdemo1 as select * from all_objects  ;

SQL> select count(*) from acsdemo1 ;

  COUNT(*)
----------
     72189
     
     
设置倾斜数据 , 10  和 9 , 9 只有一条      
QL> update acsdemo1 set object_id = 10 where rownum < 72189 ;

72188 rows updated

SQL> commit;

Commit complete

SQL> update acsdemo1 set object_id = 9  where object_id != 10  ;

1 row updated

SQL> commit;

Commit complete

SQL>      
     
建立索引;     
create index ind_acsdemoid on acsdemo1 ( object_id) ;

分析表,并建立直方图:  

exec  DBMS_STATS.GATHER_TABLE_STATS( OwnName  => 'SCOTT' ,TabName  => 'ACSDEMO1',Estimate_Percent  => 100  ,Cascade => TRUE ,No_Invalidate => FALSE,method_opt => 'for all indexed columns');


查询直方图:

 
SQL> select * from user_histograms  where table_name = 'ACSDEMO1' ;

TABLE_NAME                     COLUMN_NAME                                                                      ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------ -------------------------------------------------------------------------------- --------------- -------------- --------------------------------------------------------------------------------
ACSDEMO1                       OBJECT_ID                                                                                      1              9
ACSDEMO1                       OBJECT_ID                                                                                  72189             10

SQL> select * from user_tab_histograms  where table_name = 'ACSDEMO1' ;

TABLE_NAME                     COLUMN_NAME                                                                      ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------ -------------------------------------------------------------------------------- --------------- -------------- --------------------------------------------------------------------------------
ACSDEMO1                       OBJECT_ID                                                                                      1              9
ACSDEMO1                       OBJECT_ID                                                                                  72189             10

SQL>

进行三次 查询, 第一次 9 , 第二次, 第三次 10 : autotrace 出来 :
必须是 绑定变量 , 所以不能用普通的脚本,要使用绑定变量来进行,同时 脚本必须完全相同 :


exec  :oid := 10 ;
select count(*) , max(object_id) from acsdemo1 where object_id = :oid;
select count(*) , max(object_id) from acsdemo1 where object_id = :oid;



SQL> select * from v$sql where sql_text like '%acsdemo1%' ;

SQL_TEXT                                                                         SQL_FULLTEXT                                                                     SQL_ID        
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------
select * from v$sql where sql_text like '%acsdemo1%'                             select * from v$sql where sql_text like '%acsdemo1%'                             5tmx859jruxcy

select count(*) , max(object_id) from acsdemo1 where object_id = :oid            select count(*) , max(object_id) from acsdemo1 where object_id = :oid            6wqm8dsh83zcg
select count(*) , max(object_id) from acsdemo1 where object_id = :oid            select count(*) , max(object_id) from acsdemo1 where object_id = :oid            6wqm8dsh83zcg

SQL>




运行sql :
var  oid number ;
exec  :oid := 9 ;
select count(*) , max(object_id) from acsdemo1 where object_id = :oid;


exec  :oid := 10 ;
select count(*) , max(object_id) from acsdemo1 where object_id = :oid;
 

exec  :oid := 20 ;
select count(*) , max(object_id) from acsdemo1 where object_id = :oid;

exec  :oid := 9 ;
select count(*) , max(object_id) from acsdemo1 where object_id = :oid;



SQL> select a.SQL_ID, a.CHILD_NUMBER , a.EXECUTIONS , a.BUFFER_GETS , a.IS_BIND_SENSITIVE , a.IS_BIND_AWARE  , a.IS_SHAREABLE  from v$sql a  where a.SQL_ID in ('6wqm8dsh83zcg' )  ;

SQL_ID        CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------- ------------ ---------- ----------- ----------------- ------------- ------------
6wqm8dsh83zcg            0          2         396 Y                 N             N
6wqm8dsh83zcg            1         18        2682 Y                 Y             Y
6wqm8dsh83zcg            2          3           6 Y                 Y             N
6wqm8dsh83zcg            3         10          20 Y                 Y             Y

SQL>  




必须使用这个:
 
select * from table(dbms_xplan.display_cursor('6wqm8dsh83zcg',1 ));


分别查询 出各个子游标对应的 执行计划
 
SQL> select * from table(dbms_xplan.display_cursor('6wqm8dsh83zcg',0 ));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6wqm8dsh83zcg, child number 0
-------------------------------------
select count(*) , max(object_id) from acsdemo1 where object_id = :oid
Plan hash value: 1278124634
--------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     1 (100)|
|   1 |  SORT AGGREGATE   |               |     1 |     3 |            |
|*  2 |   INDEX RANGE SCAN| IND_ACSDEMOID |     1 |     3 |     1   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=:OID)

19 rows selected

SQL> select * from table(dbms_xplan.display_cursor('6wqm8dsh83zcg',1 ));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6wqm8dsh83zcg, child number 1
-------------------------------------
select count(*) , max(object_id) from acsdemo1 where object_id = :oid
Plan hash value: 1645313489
--------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |       |       |    41 (100)|
|   1 |  SORT AGGREGATE       |               |     1 |     3 |            |
|*  2 |   INDEX FAST FULL SCAN| IND_ACSDEMOID | 72188 |   211K|    41   (3)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"=:OID)

19 rows selected

SQL> select * from table(dbms_xplan.display_cursor('6wqm8dsh83zcg',2 ));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6wqm8dsh83zcg, child number 2
-------------------------------------
select count(*) , max(object_id) from acsdemo1 where object_id = :oid
Plan hash value: 1278124634
--------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     1 (100)|
|   1 |  SORT AGGREGATE   |               |     1 |     3 |            |
|*  2 |   INDEX RANGE SCAN| IND_ACSDEMOID |     1 |     3 |     1   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=:OID)

19 rows selected

SQL> select * from table(dbms_xplan.display_cursor('6wqm8dsh83zcg',3 ));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6wqm8dsh83zcg, child number 3
-------------------------------------
select count(*) , max(object_id) from acsdemo1 where object_id = :oid
Plan hash value: 1278124634
--------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     1 (100)|
|   1 |  SORT AGGREGATE   |               |     1 |     3 |            |
|*  2 |   INDEX RANGE SCAN| IND_ACSDEMOID |     1 |     3 |     1   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=:OID)

19 rows selected

SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16993226/viewspace-774228/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2012-03-22

  • 博文量
    13
  • 访问量
    18149