Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Performance challenge.

Re: Performance challenge.

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Thu, 19 Apr 2007 00:23:49 +0200
Message-ID: <4ef2fbf50704181523l2b7c40abje4059e46b2e5055f@mail.gmail.com>


On 4/17/07, Powell, Mark D <mark.powell_at_eds.com> wrote: (snip)
> Are you generating histograms? The behavior you describe where it appears
> to matter what values were used in an SQL statement as to how following
> submissions of the statement perform makes me think that bind variable
> peeking could be in play. Try eliminating the histograms and see what
> happens. You can always put them back.
(snip)

I agree, and since the current frenzy stats collection made me think about the min/max range problem (you insert new rows above the max - say populated by a sequence or by sysdate, and then select them - but the CBO "couldn't know they are" there until you update low_value and high_value), I made a quick test on 9.2.0.8:

create table t as select mod (rownum, 10) x from dual connect by level <= 100;

select x, count(*) from t group by x;

         X COUNT(*)
---------- ----------

         0         10
         1         10
         2         10
         3         10
         4         10
         5         10
         6         10
         7         10
         8         10
         9         10

exec dbms_stats.gather_table_stats (user, 't', method_opt=>'for all columns size 1');

select * from t where x = 9;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=20)    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=10 Bytes=20)

select * from t where x = 11;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=20)    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=10 Bytes=20)

exec dbms_stats.gather_table_stats (user, 't', method_opt=>'for all columns size 254');

select * from t where x = 9;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=20)    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=10 Bytes=20)

select * from t where x = 11;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2)    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=2) (note Card=1)

exec dbms_stats.gather_table_stats (user, 't', method_opt=>'for all columns size 3');

select * from t where x = 9;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=20)    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=10 Bytes=20)

select * from t where x = 11;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=20)    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=10 Bytes=20)

So, without histograms, the cardinality is not different when selecting outside the min/max range, so, at least with an equality predicate, you don't need an exactly up-to-date low_value/high_value.

Same with Height-Balanced histograms, not with Frequency Histograms.

Normally FH are not used in OLTP systems, but maybe they are used in this "Matrix One" product ... shot in the dark, I agree.

-- 
Alberto Dell'Era
"dulce bellum inexpertis"
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 18 2007 - 17:23:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US