strange case with dictionary views queries on 10.2.0.3 amplified by rac - suboptimal plan (no predicate pushed into union all)
- From: Grzegorz Goryszewski <grzegorzof@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 17 Oct 2011 18:07:37 +0200
Hi,
on my 10.2.0.3 RAC I've got problem with dictionary views queries,
here's simple case:
select /*+ gather_plan_statistics */ * from dba_segments where owner =
'MYUSERL' and segment_name = 'T' --> that is heap table
the problem part is like that:
15 | UNION-ALL | | 2
| | 1547K|00:02:50.25 | 227K| 148K| |
| |
|* 16 | TABLE ACCESS FULL | TAB$ | 2
| 121K| 202K|00:00:51.64 | 42399 | 38268 | |
| |
| 17 | TABLE ACCESS FULL | TABPART$ | 2
| 407K| 995K|00:00:43.36 | 10966 | 9600 | |
| |
| 18 | TABLE ACCESS FULL | CLU$ | 2
| 10 | 20 |00:00:00.05 | 49513 | 38869 | |
| |
|* 19 | TABLE ACCESS FULL | IND$ | 2
| 9522 | 20938 |00:01:45.48 | 56275 | 30987 | |
| |
| 20 | TABLE ACCESS FULL | INDPART$ | 2
| 110K| 304K|00:00:00.66 | 3706 | 3617 | |
| |
|* 21 | TABLE ACCESS FULL | LOB$ | 2
| 725 | 1470 |00:00:31.22 | 64635 | 26767 | |
| |
| 22 | TABLE ACCESS FULL | TABSUBPART$ | 2
| 8256 | 7670 |00:00:00.10 | 244 | 238 | |
| |
| 23 | TABLE ACCESS FULL | INDSUBPART$ | 2
| 6644 | 13888 |00:00:00.16 | 144 | 138 | |
| |
| 24 | TABLE ACCESS FULL | LOBFRAG$ | 2
| 10 | 134 |00:00:00.01 | 6 | 2 | |
| |
I've included whole plan here http://pastebin.com/SvYyDJNd
normally there is predicate pushed into union-all (for every union all
table) and there is index access on every SOME$ table .
But in my case there is FTS on all dict tables.
I can see optimal plan on other 10.2.0.3 databases.
Dictionary statistics are up to date .
Support tells :
please confirm does issue is consistent only with specific segment type ?
is this a LOB segment ?
we see in 10053
-----------------
SELECT 'LOBSEGMENT' "'LOBSEGMENT'",21 "21",8 "8","L"."LOBJ#" "LOBJ#",
"L"."FILE#" "FILE#","L"."BLOCK#" "BLOCK#","L"."TS#" "TS#"
FROM "SYS"."LOB$" "L"
WHERE BITAND("L"."PROPERTY",64)=0 OR BITAND("L"."PROPERTY",128)=128
we see index cost less but we used FTS
One row Card: 1.00
Best:: AccessPath: IndexUnique Index: I_LOB2
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0
so index cost is lower but even that not used :).
And because problem is on RAC we are observing 'gc cr multiblock request' .
I've got both cases 10053 traces (good and bad) , so can provide You
with details if needed.
Looks like good exercise after OOW2011 party :) .
Regards
GregG
----------------------------------------------------------------
Dom pod miastem lub mieszkanie w centrum znajdziesz wlasnie tu!
http://linkint.pl/f2a58
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » strange case with dictionary views queries on 10.2.0.3 amplified by rac - suboptimal plan (no predicate pushed into union all) - Grzegorz Goryszewski
