| 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: About CBO cost in oracle10g
Thanks Jonathan;
I do the research because another test.
I have some test about in and exists :
The first,I create a big table and a small table with index:
create table big as select * from all_objects;
insert /*+ append */ into big select * from big;
commit;
insert /*+ append */ into big select * from big;
commit;
insert /*+ append */ into big select * from big;
commit;
insert /*+ append */ into big select * from big;
create index big_idx on big(object_id);
create table small as select * from all_objects where rownum < 100; create index small_idx on small(object_id);
And then I analyze all of them:
analyze table big compute statistics
for table
for all indexes
for all indexed columns
/
analyze table small compute statistics
for table
for all indexes
for all indexed columns
/
With sql_trace ,I got the following result under rbo and cbo:
With rbo,Oracle chose a NL ,and got result quickly. Oracle Chose FTS to small table,I think it's good.
select /*+ rule */ count(subobject_name)
from big
where object_id in ( select object_id from small )
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.01 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.04 0.04 8 1696 0
1
total 4 0.04 0.05 8 1696 0
1
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 120
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1696 r=8 w=0 time=40677 us)
1584 TABLE ACCESS BY INDEX ROWID BIG (cr=1696 r=8 w=0 time=36437
us)
1684 NESTED LOOPS (cr=108 r=0 w=0 time=12631 us)
99 VIEW (cr=4 r=0 w=0 time=1144 us)
99 SORT UNIQUE (cr=4 r=0 w=0 time=982 us)
99 TABLE ACCESS FULL SMALL (cr=4 r=0 w=0 time=400 us)
1584 INDEX RANGE SCAN BIG_IDX (cr=104 r=0 w=0 time=6053
us)(object id 15290)
But with CBO:
I found,Oracle choose FTS to big table.
And got a slowly explain and much more disk & query read.
In CBO,Oracle Choose "NESTED LOOPS SEMI",I donn't know the exactly internal operation of it ,but the explain is bad,i think.
select count(subobject_name)
from big
where object_id in ( select object_id from small )
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 1.50 1.47 1038 2087 0
1
total 4 1.50 1.48 1038 2087 0
1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 120
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2087 r=1038 w=0 time=1478050 us)
1584 NESTED LOOPS SEMI (cr=2087 r=1038 w=0 time=1475992 us)
166576 TABLE ACCESS FULL BIG (cr=2085 r=1038 w=0 time=470339 us)
1584 INDEX RANGE SCAN SMALL_IDX (cr=2 r=0 w=0 time=425865
us)(object id 15292)
And then I trace it with 10053 event.
In Join Order section,Oracle try every possible join order.
But when using Big table with outer table,Oracle just give a cheaper
cost,and choose it.
I donn't know how Oracle got it.So I try to find out how Oracle calculate cost .
I test it in Oracle9i and Oracle10g,All do the same.
Thanks again for you help.
 |
 |