不好意思。最近几天都比较忙,现在继续研究。
我试过单独对tab$的索引重新收集统计信息,还是不行,对tab$才能解决问题。
有问题的语句如下:
select u.name, o.name
from
obj$ o,user$ u, tab$ t, (select obj# objnum# from tab$ where ts#=:1 and
bitand(property,64+512+8192)=0 and bitand(flags,536870912)
=0 union all select distinct t.obj# objnum# from tabpart$ tp,tab$ t
where tp.ts#=:1 and t.obj#=tp.bo# and bitand(t.property,
64+512+8192)=0 and bitand(t.flags,536870912)=0 union all select
distinct t.obj# objnum# from tabcompart$ tcp,tabsubpart$ tsp,tab$ t
where tsp.ts#=:1 and tsp.pobj#=tcp.obj# and t.obj#=tcp.bo# and
bitand(t.property,64+512+8192)=0 and bitand(t.flags,536870912)
=0 union all select t.obj# objnum# from ind$ i,tab$ t where i.ts#=:1
and i.type#=4 and t.obj#=i.bo# and bitand(t.property,8192)=0 union
all select distinct t.obj# objnum# from indpart$ ip,ind$ i,tab$ t
where ip.ts#=:1 and i.type#=4 and i.obj#=ip.bo# and
t.obj#=i.bo# and bitand(t.property,8192)=0 union all select distinct
t.obj# objnum# from indcompart$ icp,indsubpart$ isp,ind$ i,tab$ t
where isp.ts#=:1 and i.type#=4 and isp.pobj#=icp.obj# and i.obj#=
icp.bo# and t.obj#=i.bo# and bitand(t.property,8192)=0 ) tin
where tin.objnum# = o.obj# and o.owner# = u.user# and t.obj#=o.obj# order
by bitand(t.property,4294967296) desc, tin.objnum# desc
在我数据库sql_id为7jrp85khm4tka,根据这个sql_id抓取了该sql执行时候的绑定变量,如下:
SYS@proc> select hash_value,sql_id,NAME,POSITION,DATATYPE_STRING,LAST_CAPTURED,value_string FROM v$sql_bind_capture where sql_id='7jrp85khm4tka';
HASH_VALUE SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPT VALUE_STRING
---------- ------------- ------------------------------------------------------------ ---------- ------------------------------ --------- --------------------------------------------------
2704434762 7jrp85khm4tka :1 1 NUMBER 11-DEC-16 9
2704434762 7jrp85khm4tka :1 2 NUMBER 11-DEC-16 9
2704434762 7jrp85khm4tka :1 3 NUMBER 11-DEC-16 9
2704434762 7jrp85khm4tka :1 4 NUMBER 11-DEC-16 9
2704434762 7jrp85khm4tka :1 5 NUMBER 11-DEC-16 9
2704434762 7jrp85khm4tka :1 6 NUMBER 11-DEC-16 9
发现绑定变量的值都是9,不知道有没有问题。
另外,附上执行计划。
select * from table(dbms_xplan.display_cursor('7jrp85khm4tka'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7jrp85khm4tka, child number 0
-------------------------------------
select u.name, o.name from obj$ o,user$ u, tab$ t, (select obj#
objnum# from tab$ where ts#=:1 and
bitand(property,64+512+8192)=0 and bitand(flags,536870912)=0
union all select distinct t.obj# objnum# from tabpart$ tp,tab$ t
where tp.ts#=:1 and t.obj#=tp.bo# and
bitand(t.property,64+512+8192)=0 and
bitand(t.flags,536870912)=0 union all select distinct t.obj# objnum#
from tabcompart$ tcp,tabsubpart$ tsp,tab$ t where tsp.ts#=:1 and
tsp.pobj#=tcp.obj# and t.obj#=tcp.bo# and
bitand(t.property,64+512+8192)=0 and
bitand(t.flags,536870912)=0 union all select t.obj# objnum# from
ind$ i,tab$ t where i.ts#=:1 and i.type#=4 and t.obj#=i.bo#
and bitand(t.property,8192)=0 union all select distinct t.obj#
objnum# from indpart$ ip,ind$ i,tab$ t where ip.ts#=:1 and i.type#=4
and i.obj#=ip.bo# and t.obj#=i.bo# and
bitand(t.property,8192)=0 union all select distinct t.obj# o
Plan hash value: 3413565305
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 194 (100)| |
| 1 | SORT ORDER BY | | 32 | 2176 | 194 (4)| 00:00:03 |
|* 2 | HASH JOIN | | 32 | 2176 | 193 (3)| 00:00:03 |
| 3 | NESTED LOOPS | | 32 | 1632 | 190 (3)| 00:00:03 |
| 4 | NESTED LOOPS | | 945 | 35910 | 158 (4)| 00:00:02 |
| 5 | INDEX FAST FULL SCAN | I_OBJ2 | 86965 | 2802K| 153 (0)| 00:00:02 |
| 6 | VIEW | | 1 | 5 | 0 (0)| |
| 7 | UNION-ALL PARTITION | | | | | |
|* 8 | TABLE ACCESS CLUSTER | TAB$ | 1 | 22 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
| 10 | SORT UNIQUE | | 1 | 27 | 4 (25)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 27 | 3 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | I_TABPART_BOPART$ | 2 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS CLUSTER | TAB$ | 1 | 19 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| |
| 16 | SORT UNIQUE | | 1 | 34 | 6 (17)| 00:00:01 |
|* 17 | HASH JOIN | | 29 | 986 | 5 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 27 | 3 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | TABCOMPART$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 20 | TABLE ACCESS CLUSTER | TAB$ | 1 | 19 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| |
|* 22 | TABLE ACCESS FULL | TABSUBPART$ | 32 | 224 | 2 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 24 | 3 (0)| 00:00:01 |
|* 24 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 2 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
|* 26 | TABLE ACCESS CLUSTER | IND$ | 1 | 11 | 1 (0)| 00:00:01 |
| 27 | SORT UNIQUE | | 1 | 34 | 6 (17)| 00:00:01 |
| 28 | NESTED LOOPS | | 1 | 34 | 5 (0)| 00:00:01 |
| 29 | NESTED LOOPS | | 3 | 34 | 5 (0)| 00:00:01 |
| 30 | NESTED LOOPS | | 1 | 26 | 3 (0)| 00:00:01 |
|* 31 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 2 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
|* 33 | TABLE ACCESS CLUSTER | IND$ | 1 | 13 | 1 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | I_INDPART_BOPART$ | 3 | | 1 (0)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID | INDPART$ | 2 | 16 | 2 (0)| 00:00:01 |
| 36 | SORT UNIQUE | | 1 | 78 | 4 (25)| 00:00:01 |
| 37 | NESTED LOOPS | | 1 | 78 | 3 (0)| 00:00:01 |
| 38 | NESTED LOOPS | | 1 | 78 | 3 (0)| 00:00:01 |
| 39 | MERGE JOIN CARTESIAN | | 1 | 65 | 3 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | 1 | 52 | 2 (0)| 00:00:01 |
| 41 | NESTED LOOPS | | 1 | 52 | 2 (0)| 00:00:01 |
| 42 | TABLE ACCESS FULL | INDCOMPART$ | 1 | 26 | 2 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | I_INDSUBPART_POBJSUBPART$ | 1 | | 0 (0)| |
|* 44 | TABLE ACCESS BY INDEX ROWID| INDSUBPART$ | 1 | 26 | 0 (0)| |
| 45 | BUFFER SORT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 46 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 1 (0)| 00:00:01 |
|* 47 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| |
|* 48 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 0 (0)| |
|* 49 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 13 | 0 (0)| |
| 50 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 1 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| |
| 52 | TABLE ACCESS FULL | USER$ | 92 | 1564 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OWNER#"="U"."USER#")
8 - filter(("TS#"=:1 AND BITAND("PROPERTY",8768)=0 AND BITAND("FLAGS",536870912)=0))
9 - access("OBJ#"="O"."OBJ#")
12 - filter("TP"."TS#"=:1)
13 - access("TP"."BO#"="O"."OBJ#")
14 - filter((BITAND("T"."PROPERTY",8768)=0 AND BITAND("T"."FLAGS",536870912)=0))
15 - access("T"."OBJ#"="TP"."BO#")
filter("T"."OBJ#"="O"."OBJ#")
17 - access("TSP"."POBJ#"="TCP"."OBJ#")
19 - filter("TCP"."BO#"="O"."OBJ#")
20 - filter((BITAND("T"."PROPERTY",8768)=0 AND BITAND("T"."FLAGS",536870912)=0))
21 - access("T"."OBJ#"="TCP"."BO#")
filter("T"."OBJ#"="O"."OBJ#")
22 - filter("TSP"."TS#"=:1)
24 - filter(BITAND("T"."PROPERTY",8192)=0)
25 - access("T"."OBJ#"="O"."OBJ#")
26 - filter(("I"."BO#"="O"."OBJ#" AND "I"."TYPE#"=4 AND "I"."TS#"=:1 AND "T"."OBJ#"="I"."BO#"))
31 - filter(BITAND("T"."PROPERTY",8192)=0)
32 - access("T"."OBJ#"="O"."OBJ#")
33 - filter(("I"."BO#"="O"."OBJ#" AND "I"."TYPE#"=4 AND "T"."OBJ#"="I"."BO#"))
34 - access("I"."OBJ#"="IP"."BO#")
35 - filter("IP"."TS#"=:1)
43 - access("ISP"."POBJ#"="ICP"."OBJ#")
44 - filter("ISP"."TS#"=:1)
46 - filter(BITAND("T"."PROPERTY",8192)=0)
47 - access("T"."OBJ#"="O"."OBJ#")
48 - access("I"."OBJ#"="ICP"."BO#")
49 - filter(("I"."BO#"="O"."OBJ#" AND "I"."TYPE#"=4 AND "T"."OBJ#"="I"."BO#"))
51 - access("T"."OBJ#"="O"."OBJ#")