Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Performance challenge.
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-lReceived on Wed Apr 18 2007 - 17:23:49 CDT
![]() |
![]() |