Re: Strange behaviour of CBO in 9ir2

From: Mike <mike.jones_at_xenicom.com>
Date: 11 Nov 2004 09:43:31 -0800
Message-ID: <6cdd54ab.0411110943.67ad3c58@posting.google.com>

I have an additional test case that to me makes the behaviour even more strange...

I have changed the OR to (or 1=0) the tkprof is below... Why the massive discrepensy? does this replicate on anyone else's 9ir2 accounts?

select count(*)
from has_permission_unit_test main
where main.primary_key <= :i_volume
and
(
  main.security_node_id in
  ( select np.node_id
    from plt_security_nodes sn, plt_node_permissions np

    where sn.node_id = np.node_id
    and   np.user_id in
    ( select user_id
      from   plt_user_delegates
      where delegate_user_id = 'MJONES'
      and   sysdate between effective_from and effective_to
      and   is_deleted = 0
      union all
      select 'MJONES' user_id
      from dual

    )
    connect by prior sn.parent_node_id = sn.node_id   )
  or
  ( 1=0)
)

call     count       cpu    elapsed       disk      query    current  
     rows

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

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      0.23       0.22       2367       2417          0  
        1

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

total        4      0.23       0.22       2367       2417          0  
        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 120

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=2417 r=2367 w=0 time=221874 us)

1000 HASH JOIN SEMI (cr=2417 r=2367 w=0 time=221421 us) 1000 TABLE ACCESS FULL HAS_PERMISSION_UNIT_TEST (cr=2370 r=2367 w=0 time=187011 us)

500 VIEW (cr=47 r=0 w=0 time=27240 us)

    500     FILTER  (cr=47 r=0 w=0 time=26834 us)
    517      CONNECT BY WITH FILTERING (cr=36 r=0 w=0 time=25865 us)
    511       COUNT  (cr=18 r=0 w=0 time=9345 us)
    511        HASH JOIN  (cr=18 r=0 w=0 time=8925 us)
    511         INDEX FAST FULL SCAN NODE_AND_USER (cr=6 r=0 w=0
time=555 us)(object id 151390)
   1520         INDEX FAST FULL SCAN NODE_AND_PARENT_NODE (cr=12 r=0
w=0 time=1259 us)(object id 151391)
      6       HASH JOIN  (cr=18 r=0 w=0 time=11470 us)
    511        CONNECT BY PUMP  (cr=0 r=0 w=0 time=457 us)
    511        HASH JOIN  (cr=18 r=0 w=0 time=8297 us)
    511         INDEX FAST FULL SCAN NODE_AND_USER (cr=6 r=0 w=0
time=447 us)(object id 151390)
   1520         INDEX FAST FULL SCAN NODE_AND_PARENT_NODE (cr=12 r=0
w=0 time=1211 us)(object id 151391)
      1      UNION-ALL  (cr=11 r=0 w=0 time=194 us)
      0       TABLE ACCESS BY INDEX ROWID PLT_USER_DELEGATES (cr=8 r=0
w=0 time=81 us)
      0        INDEX RANGE SCAN PUD_PK (cr=8 r=0 w=0 time=71
us)(object id 129898)
      1       FILTER  (cr=3 r=0 w=0 time=59 us)
      1        TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=53 us)

elect /* with or */ count(*)
from has_permission_unit_test main
where main.primary_key <= :i_volume
and
(
  main.security_node_id in
  ( select np.node_id
    from plt_security_nodes sn, plt_node_permissions np

    where sn.node_id = np.node_id
    and   np.user_id in
    ( select user_id
      from   plt_user_delegates
      where delegate_user_id = :"SYS_B_0"
      and   sysdate between effective_from and effective_to
      and   is_deleted = :"SYS_B_1"
      union all
      select :"SYS_B_2" user_id
      from dual

    )
    connect by prior sn.parent_node_id = sn.node_id   )
  or
  ( :"SYS_B_3"=:"SYS_B_4")
)

call     count       cpu    elapsed       disk      query    current  
     rows

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

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      8.80       8.64       2367     881488          0  
        1

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

total        4      8.80       8.64       2367     881488          0  
        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 120

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=881488 r=2367 w=0 time=8642076 us)

1000 FILTER (cr=881488 r=2367 w=0 time=8641569 us) 1000 TABLE ACCESS FULL OBJ#(150327) (cr=2370 r=2367 w=0 time=190079 us)

428 FILTER (cr=879118 r=0 w=0 time=8437852 us) 83925 CONNECT BY WITH FILTERING (cr=879114 r=0 w=0 time=8375104 us)

 218708      COUNT  (cr=439557 r=0 w=0 time=3040562 us)
 218708       NESTED LOOPS  (cr=439557 r=0 w=0 time=2859509 us)
 218708        INDEX FULL SCAN OBJ#(151390) (cr=1712 r=0 w=0
time=239609 us)(object id 151390)
 218708        TABLE ACCESS BY INDEX ROWID OBJ#(129797) (cr=437845 r=0
w=0 time=1933981 us)
 218708         INDEX UNIQUE SCAN OBJ#(129895) (cr=219137 r=0 w=0
time=966898 us)(object id 129895)
   2568      HASH JOIN  (cr=439557 r=0 w=0 time=4021904 us)
 218708       CONNECT BY PUMP  (cr=0 r=0 w=0 time=191606 us)
 218708       NESTED LOOPS  (cr=439557 r=0 w=0 time=2839104 us)
 218708        INDEX FULL SCAN OBJ#(151390) (cr=1712 r=0 w=0
time=242267 us)(object id 151390)
 218708        TABLE ACCESS BY INDEX ROWID OBJ#(129797) (cr=437845 r=0
w=0 time=1914775 us)
 218708         INDEX UNIQUE SCAN OBJ#(129895) (cr=219137 r=0 w=0
time=939408 us)(object id 129895)
      1     UNION-ALL  (cr=4 r=0 w=0 time=73 us)
      0      TABLE ACCESS BY INDEX ROWID OBJ#(129800) (cr=1 r=0 w=0
time=17 us)
      0       INDEX RANGE SCAN OBJ#(129898) (cr=1 r=0 w=0 time=15
us)(object id 129898)
      1      FILTER  (cr=3 r=0 w=0 time=46 us)
      1       TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=42 us)

********************************************************************************

Thanks for any help or advice,

Mike. Received on Thu Nov 11 2004 - 11:43:31 CST