OS & Oracle

Automation usually is a good thing, but sometimes it can make thing goes strange.
I came across this situation just now, even I thought maybe it should be an bug in CBO, before I discovered that the cause is the automatic statistics gathering.
Below is the output of this case.

SQL> delete from A a  2  WHERE exists  [...]

For the following statement, 9i, 10g  and 11g output the different cardinality and cost.

create table t1
nologging
as
select
trunc(dbms_random.value(0,25)) n1,
rpad(’x',40) ind_pad,
trunc(dbms_random.value(0,20)) n2,
lpad(rownum,10,’0′) small_vc,
rpad(’x',200) padding
from
all_objects
where
rownum <= 10000
;
update t1 set n1 = [...]

Two important and related concepts inside CBO is selectivity and cardinality. The cardinality means that how many rows should be returned by CBO after evaluating the predicates. And this values is always equal to (number of input rows)* selectivity. So we can guess that selectivity means that how many percents of the input rows will [...]

SQL tuning is the most important part of performance tuning. Cost based optimizer, which is used to evaluate the cost for the SQL statement and determine which execution plan is suitable for that specified statement, plays a key role in SQL tuning.
So, what is Cost? From the Oracle Document, cost is defined as below:

Cost = [...]