| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Unexpected Tkprof Row Count
mccmx_at_hotmail.com (Matt) wrote in message news:<cfee5bcf.0409010055.72f239b5_at_posting.google.com>...
> >
> > May we see the query in question?
> >
>
> I wanted to avoid posting the whole query because it makes use of 3
> layers of nested views and OLAP queries. It would take a long time to
> plough through...!
>
> I just wanted to get some feedback about possible causes of this row
> count anomoly...
>
> However here is view definition of the part of the query which is
> responsible for the section of the Tkprof output in question
> (PS_TM_PEFF_V_CCEH)...
>
that gave me enough to demonstrate what is happening. the count is "wrong" as far as I can, i can make it be any number I want -- and it reproduces up through at least 10gR1.
Here is the test case:
drop table t1; drop table t2; drop table t3; create table t1 ( x int, a date, b date );create table t2 ( x int, a date, b date ); create table t3 ( x int );
insert into t1 (x,a,b) values ( 1, sysdate-1, sysdate+1 ); insert into t2 (x,a,b) values ( 2, sysdate-.9, sysdate+.9 ); insert into t3 values ( 2 );
create or replace view v
as
select /*+ USE_NL(t2 t1) */ t2.x, sum(t1.x) sumx
from t1, t2
where t1.x(+) = t2.x
and ((t2.a >= t1.a and (t2.b <= t1.b or t1.b is null)) or t1.a is
null)
group by t2.x
/
create index t1_idx on t1(x); create index t2_idx on t2(x); create index t3_idx on t3(x);
@trace
select /*+ first_rows use_nl(t3 v) */ * from t3 just_1, v where
just_1.x = v.x(+);
insert into t3 select 2 from all_objects where rownum < 100;
select /*+ first_rows use_nl(t3 v) */ * from t3 with_100, v where
with_100.x = v.x(+);
It sort of emulates your example, paired down.
The traces from the two queries show:
select /*+ first_rows use_nl(t3 v) */ *
from
t3 just_1, v where just_1.x = v.x(+)
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.02 0 6 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 15 0
1
total 4 0.00 0.02 0 21 0
1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 65
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=202 us)
1 TABLE ACCESS FULL T3 (cr=7 pr=0 pw=0 time=93 us)
1 VIEW (cr=8 pr=0 pw=0 time=130 us)
1 SORT GROUP BY (cr=8 pr=0 pw=0 time=120 us)
1 FILTER (cr=8 pr=0 pw=0 time=82 us)
1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=73 us)
1 TABLE ACCESS FULL T2 (cr=7 pr=0 pw=0 time=35 us)
0 TABLE ACCESS BY INDEX ROWID T1 (cr=1 pr=0 pw=0 time=28
us)
0 INDEX RANGE SCAN T1_IDX (cr=1 pr=0 pw=0 time=21
us)(object id 63162)
Now, that looks "OK", but if I explode out that outer table:
select /*+ first_rows use_nl(t3 v) */ *
from
t3 with_100, v where with_100.x = v.x(+)
call count cpu elapsed disk query current
rows
Parse 1 0.02 0.01 0 6 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 8 0.00 0.00 0 22 0
100
total 10 0.02 0.01 0 28 0
100
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 65
Rows Row Source Operation
------- ---------------------------------------------------
100 NESTED LOOPS OUTER (cr=22 pr=0 pw=0 time=205 us) 100 TABLE ACCESS FULL OBJ#(63160) (cr=14 pr=0 pw=0 time=2342 us) 100 VIEW (cr=8 pr=0 pw=0 time=1283 us) 100 SORT GROUP BY (cr=8 pr=0 pw=0 time=603 us)
1 FILTER (cr=8 pr=0 pw=0 time=83 us)
1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=73 us)
1 TABLE ACCESS FULL OBJ#(63159) (cr=7 pr=0 pw=0 time=36
us)
0 TABLE ACCESS BY INDEX ROWID OBJ#(63158) (cr=1 pr=0 pw=0
time=27 us)
0 INDEX RANGE SCAN OBJ#(63162) (cr=1 pr=0 pw=0 time=20
us)(object id 63162)
We can see I can make it 100 as the result of the sort/group by step (or any number I feel like)...
but the important thing to note is the cr= numbers (which you won't see in your version). they show the addition cr's in this second query were 100% due to the larger "TABLE ACCESS FULL OBJ#(63160)" -- it wasn't that we ran the view 100 times for the nested loops outer -- but that seems to be what contributed to the wrong statistics count. Received on Wed Sep 01 2004 - 10:26:28 CDT
 |
 |