首页 » ORACLE » About like 5% selectivity引起的不完美执行计划

About like 5% selectivity引起的不完美执行计划

CBO优化器的内部有两个重要的标准:selectivity and cardinality,cardinality 就CBO在处理完后返回的行数,selectivity是返回数据范围百分比,cardinality = (number of input rows)* selectivity,所以selectivity 的直接影响了CBO表访问路径,在like操作时比如绑定变量或join 表 column like column这种非常量的比较时CBO目前还没有那么伟大只是一个理想值5%,但有时big table 本来返回不足3%或更少时5%的大的Card就有可能在与第三张表关连时产生错误的评估,比如少量数据hash join +full table scan

下面做个实验分别掩饰绑定变量和 join column like column

sys@GGS>select org_name from icme.icme_org where org_code='000200180006001000010103';

Execution Plan
----------------------------------------------------------
Plan hash value: 3331258105

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    35 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ICME_ORG         |     1 |    35 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ICME_ORG_CODE |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

sys@GGS>select org_name from icme.icme_org where org_code like '000200180006001000010103';

Execution Plan
----------------------------------------------------------
Plan hash value: 3331258105

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    35 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ICME_ORG         |     1 |    35 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ICME_ORG_CODE |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

sys@GGS>var b varchar2(200);
sys@GGS>execute :b:='000200180006001000010103'

PL/SQL procedure successfully completed.

sys@GGS>select org_name from icme.icme_org where org_code like :b;

Execution Plan
----------------------------------------------------------
Plan hash value: 2212388312

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |  9357 |   319K|   505   (1)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ICME_ORG         |  9357 |   319K|   505   (1)| 00:00:07 |
|*  2 |   INDEX RANGE SCAN          | PK_ICME_ORG_CODE |  1684 |       |    11   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

sys@GGS>select 9357/num_rows from dba_tables where table_name='ICME_ORG' and OWNER='ICME';

            9357/NUM_ROWS
-------------------------
.049998129812393466098842

–可以看到产生了Card 9357,刚好约为5% 。设置隐藏参数_like_with_bind_as_equality=true,默认false

sys@GGS>alter session set "_like_with_bind_as_equality"=true;

sys@GGS>select COLUMN_NAME,density from dba_tab_col_statistics where owner='ICME' and table_name='ICME_ORG' ;


COLUMN_NAME                                      DENSITY
------------------------------ -------------------------
ORG_YUAN_CONTACT_PHONE                                 0
ORG_SECOND_NAME                                       .5
ORG_RESP_CONTACT_PHONE                                 0
ORG_CONTACT_ADDRESS                                    0
ORG_ID                             .00000539738226959924
PARENT_ORG_ID                        .000137570504883753
ORG_CODE                           .00000539738226959924
ORG_SEQ                              .000284333238555587
ORG_NAME                             .000275482093663912
ORG_BUINESS_CODE                   .00000539738226959924
ORG_PASSWORD                                          .2
ORG_TYPE_ID                        .00000269868631991499
ADMIN_ID                             .000721500721500722
CHECK_STUDENT                                         .5


sys@GGS>select org_name from icme.icme_org where org_code like :b;


Execution Plan
----------------------------------------------------------
Plan hash value: 2212388312

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     2 |    70 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ICME_ORG         |     2 |    70 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_ICME_ORG_CODE |     2 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORG_CODE" LIKE :B)
       filter("ORG_CODE" LIKE :B)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys@GGS>exec :b:='00020023';

sys@GGS>select org_name from icme.icme_org where org_code like :b||'%';

19996 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2212388312

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     2 |    70 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ICME_ORG         |     2 |    70 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_ICME_ORG_CODE |     2 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORG_CODE" LIKE :B||'%')
       filter("ORG_CODE" LIKE :B||'%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       9493  consistent gets
          0  physical reads
          0  redo size
     476847  bytes sent via SQL*Net to client
      15155  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      19996  rows processed

references

In situations in which the LIKE pattern is expected to match very few rows, it is possible to set the hidden parameter _like_with_bind_as_equality to TRUE. This makes the

optimizer treat the predicate as though it were COLUMN = :BIND. In other words, the optimizer uses the column’s density as the selectivity instead of a fixed five percent.”

_like_with_bind_as_equality=true可以使用绑定变量的LIKE 转换为=:BIND的操作,使用column的density做为selectivity

Casd=2 是怎么来的呢,做个10053

########################################
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost

*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
 Bind#0
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=2b0736ad1d48  bln=32  avl=08  flg=05
  value="00020023"

  PARAMETERS WITH ALTERED VALUES
  ******************************
  _pga_max_size                       = 1024000 KB
  _smm_max_size                       = 378880 KB
  optimizer_index_caching             = 90
  _like_with_bind_as_equality         = true

Table Stats::
  Table: ICME_ORG  Alias: ICME_ORG
    #Rows: 187147  #Blks:  2304  AvgRowLen:  81.00
  Index: PK_ICME_ORG_CODE  Col#: 3
    LVLS: 2  #LB: 908  #DK: 188038  LB/K: 1.00  DB/K: 1.00  CLUF: 82996.00

  Access Path: index (IndexOnly)
    Index: PK_ICME_ORG_CODE
    resc_io: 3.00  resc_cpu: 21964
    ix_sel: 1.0741e-05  ix_sel_with_filters: 1.0741e-05    
    Cost: 3.00  Resp: 3.00  Degree: 0
    SORT resource      Sort statistics
      Sort width:        2218 Area size:     1048576 Max Area size:   387973120
      Degree:               1
      Blocks to Sort:       1 Row size:           21 Total Rows:              2
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 8566955
      Total Temp space used: 0

###########################################

Note
1.0741e-05=0.000010741
Card = index DK * ix_sel_with_filters = 188038*0.000010741 = 2.019716158 (Just my guess)

接着实验 5% selectivity 与第三张表关连的问题


sys@GGS>SELECT  
  2         t2.org_name
  3    FROM icme.icme_org t1, icme.icme_org t2 
  4   WHERE     t1.org_id = 100019697
  5         AND t2.org_code LIKE t1.org_code||'%';


Execution Plan
----------------------------------------------------------
Plan hash value: 2241655122

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  9357 |   612K|   507   (1)| 00:00:07 |
|   1 |  NESTED LOOPS                |                  |  9357 |   612K|   507   (1)| 00:00:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ICME_ORG         |     1 |    32 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ICME_ORG      |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| ICME_ORG         |  9357 |   319K|   505   (1)| 00:00:07 |
|*  5 |    INDEX RANGE SCAN          | PK_ICME_ORG_CODE |  1684 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."ORG_ID"=100019697)
   5 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
       filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

tip:

关于就返回1条数据,Cardinality 确是9357,Cardinality =187147* 5% ,做10053 event

****** finished trying bitmap/domain indexes ******
  Best NL cost: 506.71
          resc: 506.71 resc_io: 506.00 resc_cpu: 6070310
          resp: 506.71 resp_io: 506.00 resp_cpu: 6070310
Join Card:  9357.35 = outer (1.00) * inner (187147.00) * sel (0.05)
Join cardinality for HJ/SMJ (no post filters):  187147.00, outer: 1.00, inner: 187147.00, sel: 1 
Join Card - Rounded: 9357 Computed: 9357.35
Best:: JoinMethod: NestedLoop
       Cost: 506.71  Degree: 1  Resp: 506.71  Card: 9357.35  Bytes: 67
***********************


sys@GGS>SELECT /*+gather_plan_statistics anbob*/
  2         t3.ic_code,
  3         t3.name,
  4         t3.sex,
  5         t2.org_name
  6    FROM icme.icme_org t1, icme.icme_org t2, icme.icme_student t3
  7   WHERE     t1.org_id = 100019697
  8         AND t2.org_code LIKE t1.org_code || '%'
  9         AND t3.org_id = t2.org_id;

Plan hash value: 1682151602

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |     20 |00:00:00.01 |   54500 |
|*  1 |  HASH JOIN                    |                  |      1 |  74694 |     20 |00:00:00.01 |   54500 |
|   2 |   NESTED LOOPS                |                  |      1 |   9357 |      1 |00:00:00.01 |       8 |
|   3 |    TABLE ACCESS BY INDEX ROWID| ICME_ORG         |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX UNIQUE SCAN         | PK_ICME_ORG      |      1 |      1 |      1 |00:00:00.01 |       2 |
|   5 |    TABLE ACCESS BY INDEX ROWID| ICME_ORG         |      1 |   9357 |      1 |00:00:00.01 |       5 |
|*  6 |     INDEX RANGE SCAN          | PK_ICME_ORG_CODE |      1 |   1684 |      1 |00:00:00.01 |       4 |
|   7 |   TABLE ACCESS FULL           | ICME_STUDENT     |      1 |   1478K|   1490K|00:00:01.49 |   54492 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."ORG_ID"="T2"."ORG_ID")
   4 - access("T1"."ORG_ID"=100019697)
   6 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
       filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      54499  consistent gets
          0  physical reads
          0  redo size
       1421  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

tip:
如果1条数据的结果集与另一张1478K记录的表关连选把用hash_join and TFS ICME_STUDENT的执行计划很不不好,业务上是返回这个机构的人员只有20人,说明这是一个特例通常不可能是1条记录还自

关连。org_code是个树code,child是parent code向后延伸4位+1. e.g. parent code=001,child code=0010001.

用cardinality hint提示CBO后


SELECT  /*+ gather_plan_statistics anbob cardinality(t2 1) */
       t3.ic_code,
       t3.name,
       t3.sex,
       t2.org_name
  FROM icme.icme_org t1, icme.icme_org t2, icme.icme_student t3
 WHERE     t1.org_id = 100019697
       AND t2.org_code LIKE t1.org_code || '%'
       AND t3.org_id = t2.org_id


--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time  | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      0 |        |      0 |00:00:00.01 |       0 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | ICME_STUDENT      |      1 |     14 |     20 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS                 |                   |      1 |      1 |     22 |00:00:00.01 |      13 |
|   3 |    NESTED LOOPS                |                   |      1 |      1 |      1 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ICME_ORG          |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  5 |      INDEX UNIQUE SCAN         | PK_ICME_ORG       |      1 |      1 |      1 |00:00:00.01 |       2 |
|   6 |     TABLE ACCESS BY INDEX ROWID| ICME_ORG          |      1 |      1 |      1 |00:00:00.01 |       5 |
|*  7 |      INDEX RANGE SCAN          | PK_ICME_ORG_CODE  |      1 |   1684 |      1 |00:00:00.01 |       4 |
|*  8 |    INDEX RANGE SCAN            | IDX_STUDENT_ORGID |      1 |     14 |     20 |00:00:00.01 |       5 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."ORG_ID"=100019697)
   7 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
       filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
   8 - access("T3"."ORG_ID"="T2"."ORG_ID")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
       1421  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed


sys@GGS>SELECT     /*+ cardinality(t2 1)*/
  2         t3.ic_code,
  3         t3.name,
  4         t3.sex,
  5         t2.org_name
  6    FROM icme.icme_org t1, icme.icme_org t2, icme.icme_student t3
  7   WHERE     t1.org_id = 37000000
  8         AND t2.org_code LIKE t1.org_code || '%'
  9         AND t3.org_id = t2.org_id;

241593 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4009788969

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |     1 |   101 |   516   (1)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | ICME_STUDENT      |    14 |   378 |     9   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                   |     1 |   101 |   516   (1)| 00:00:07 |
|   3 |    NESTED LOOPS                |                   |     1 |    74 |   507   (1)| 00:00:07 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ICME_ORG          |     1 |    32 |     2   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | PK_ICME_ORG       |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| ICME_ORG          |     1 |    42 |   505   (1)| 00:00:07 |
|*  7 |      INDEX RANGE SCAN          | PK_ICME_ORG_CODE  |  1684 |       |     1   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN            | IDX_STUDENT_ORGID |    14 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."ORG_ID"=37000000)
   7 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
       filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
   8 - access("T3"."ORG_ID"="T2"."ORG_ID")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     270480  consistent gets
          0  physical reads
          0  redo size
    8458866  bytes sent via SQL*Net to client
     177658  bytes received via SQL*Net from client
      16108  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     241593  rows processed

sys@GGS>SELECT     
  2         t3.ic_code,
  3         t3.name,
  4         t3.sex,
  5         t2.org_name
  6    FROM icme.icme_org t1, icme.icme_org t2, icme.icme_student t3
  7   WHERE     t1.org_id = 37000000
  8         AND t2.org_code LIKE t1.org_code || '%'
  9         AND t3.org_id = t2.org_id;

241593 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1682151602

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  | 74694 |  7367K| 12527   (2)| 00:02:31 |
|*  1 |  HASH JOIN                    |                  | 74694 |  7367K| 12527   (2)| 00:02:31 |
|   2 |   NESTED LOOPS                |                  |  9357 |   676K|   507   (1)| 00:00:07 |
|   3 |    TABLE ACCESS BY INDEX ROWID| ICME_ORG         |     1 |    32 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PK_ICME_ORG      |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| ICME_ORG         |  9357 |   383K|   505   (1)| 00:00:07 |
|*  6 |     INDEX RANGE SCAN          | PK_ICME_ORG_CODE |  1684 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL           | ICME_STUDENT     |  1478K|    38M| 12003   (2)| 00:02:25 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."ORG_ID"="T2"."ORG_ID")
   4 - access("T1"."ORG_ID"=37000000)
   6 - access("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')
       filter("T2"."ORG_CODE" LIKE "T1"."ORG_CODE"||'%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      77329  consistent gets
          0  physical reads
          0  redo size
   10225534  bytes sent via SQL*Net to client
     177658  bytes received via SQL*Net from client
      16108  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     241593  rows processed

Note:

cardinality hint 后发现icme_org在记录少与另一张表关连时走了正常的索引,CR block 有原来的54499 变成了加hint后的24,但是当ICME_ORG表如果返回一个大的结果集时原来的HINT就通用了
如org_id=37000000时,有hint cr block是270480而没hint走全表是77329.所以我改用重写sql

sys@GGS>SELECT  
  2              t2.org_name
  3    FROM icme.icme_org t1, icme.icme_org t2 
  4   WHERE     t1.org_id = 37000000
  5         AND t2.org_code LIKE t1.org_code||'%';

19996 rows selected.
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |  9357 |   612K|   507   (1)| 00:00:07 |
|   1 |  NESTED LOOPS                |                  |  9357 |   612K|   507   (1)| 00:00:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ICME_ORG         |     1 |    32 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ICME_ORG      |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| ICME_ORG         |  9357 |   319K|   505   (1)| 00:00:07 |
|*  5 |    INDEX RANGE SCAN          | PK_ICME_ORG_CODE |  1684 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       9496  consistent gets
          0  physical reads
          0  redo size
     476847  bytes sent via SQL*Net to client
      15155  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      19996  rows processed

sys@GGS>select org_id,org_name   from icme.icme_org 
           start with org_id=37000000 connect by prior org_id=parent_org_id ;

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                          |   187K|  4386K|   198   (2)| 00:00:03 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|                          |       |       |            |          |
|   2 |   INDEX FAST FULL SCAN                  | IDX_ICME_ORG_ID_PID_NAME |   187K|  4386K|   198   (2)| 00:00:03 |
--------------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        896  consistent gets
          0  physical reads
          0  redo size
     602504  bytes sent via SQL*Net to client
      15155  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      19996  rows processed

--先改写第一部份,改用start with后cr blocks有原来的 9496降到896,再结合实际的业务人员是挂在组构的叶子上的,so 改写上面的与第三个表关连后的sql为

sys@GGS>WITH c
  2       AS (    SELECT org_id, org_name
  3                 FROM icme.icme_org
  4                WHERE CONNECT_BY_ISLEAF = 1
  5           START WITH org_id = 37000000
  6           CONNECT BY PRIOR org_id = parent_org_id)
  7  SELECT t3.ic_code,
  8         t3.name,
  9         t3.sex,
 10         c.org_name
 11    FROM c, icme.icme_student t3
 12   WHERE c.org_id = t3.org_id;

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                          |  1493K|   131M|       | 15652   (2)| 00:03:08 |
|*  1 |  HASH JOIN                                 |                          |  1493K|   131M|    13M| 15652   (2)| 00:03:08 |
|   2 |   VIEW                                     |                          |   187K|    11M|       |   198   (2)| 00:00:03 |
|*  3 |    FILTER                                  |                          |       |       |      |            |          |
|*  4 |     CONNECT BY NO FILTERING WITH START-WITH|                          |       |       |      |            |          |
|   5 |      INDEX FAST FULL SCAN                  | IDX_ICME_ORG_ID_PID_NAME |   187K|  4386K|      |   198   (2)| 00:00:03 |
|   6 |   TABLE ACCESS FULL                        | ICME_STUDENT             |  1478K|    38M|      | 12003   (2)| 00:02:25 |
-------------------------------------------------------------------------------------------------------------------------------

Tip:
到这里剩下的工作就是调ICME_STUDENT 的访问表结构了,如分区,索引..
加hint的sql时一定要考虑通用性,上篇也提到过优化的顺序改写sql优先与使用hint

Summary:
1,where 条件 like 绑定变量时CBO默认会取5% 的selectivity,so,执行计划中erows>arows
2,当多表之间以like 方式关连时,CBO默认也是会以5% 的selectivity计算card,当card大于实际的时再与其它表关连有可能会产生TFS替换INDEX SCAN的低效执行计划。

打赏

目前这篇文章有3条评论(Rss)评论关闭。

  1. tacy lee | #1
    2012-11-27 at 14:08

    小建议:在summary加上like容易导致selectivity计算错误,summary写的文不对题啊,看半天才明白,用的时候要注意

    • admin | #2
      2012-11-28 at 14:20

      感谢指正

  2. admin | #3
    2012-11-22 at 16:41

    以上测试在10205版本

    在11G中的Extended Statistics 也许可以解决