Re: Creating Histograms
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jul 2004 14:08:13 -0400
Hi Donald I was in the same process, for the advanced tuning course I'm
doing, t his is what I found, I hope be useful, but what I found, is that
CBO is smarter (or bugged) and in more than one test I couldn't get to use
the histogram.
Any comment about this will be appreciated.
From my new (and more serious ) paper, not like the previous
1.A Histograms
Histograms could affect nevatively performance, you have to verify they have
a positive effect in your system.
To create histograms you execute the following command, the size parameter
specifies the number of buckets, depending of the amount of distinct values
you give distinct value to bucket.
EXEC DBMS_STATS.GATHER_TABLE_STATS ('scott','emp', METHOD_OPT => 'FOR
COLUMNS SIZE 10 sal');
You can view histogram information with the following views DBA_HISTOGRAMS,
DBA_PART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS, DBA_TAB_COLSTATISTICS
1.A.i Bucket Size
Oracle divides the distinct value in bands, called buckets.
From Documentation
?If the number of frequently occurring distinct values in a column is
relatively small, then set the number of buckets to be greater than that
number. The default number of buckets for a histogram is 75, This valuesp
rovides an appropiate level of detail for most data distributions however,
because the number of buckets in the histograms, and the data distribution,
bot affect a histogram?s usefulness, you might need to experiment with
different number of buckets to obtain optimal results.?
For example
We will create a test table, gather statistics and create two groups one
small of 3 records and other of 88713 records, we will run a test with and
without histograms.
create table test as
select * from dba_objects UNION ALL
select * from dba_objects UNION ALL
select * from dba_objects;
create index idxtest on test (OWNER);
SQL> UPDATE TEST SET OWNER = 'XXX' WHERE NOT OWNER = 'CACHITO';
SQL> COMMIT;
SQL> SELECT OWNER,COUNT(*) FROM TEST GROUP BY OWNER;
OWNER COUNT(*)
------------------------------ ----------
CACHITO 3
XXX 88713
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ADM',TABNAME=>'TEST'
CASCADE=>TRUE);
SQL> SELECT * FROM TEST WHERE OWNER = 'CACHITO';
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=44358 Bytes=4036578)
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=70 Card=44358 Bytes=4036578)
2 1
INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=93 Card=44358)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1213 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> SELECT * FROM TEST WHERE OWNER = 'XXX';
88713 filas seleccionadas.
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=44358 Bytes=4036578)
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=70 Card=44358 Bytes=4036578)
2 1
INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=93 Card=44358)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13132 consistent gets
0 physical reads
0 redo size
4304202 bytes sent via SQL*Net to client
65557 bytes received via SQL*Net from client
5916 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
88713 rows processed
SQL>
SQL> SELECT NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS WHERE OWNER = 'ADM' AND
TABLE_NAME = 'TEST' AND COLUMN_NAME = 'OWNER';
NUM_BUCKETS
-----------
1
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('ADM','TEST', METHOD_OPT => 'FOR
COLUMNS SIZE 75 OWNER');
Procedimiento PL/SQL terminado correctamente.
SQL> SELECT NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS WHERE OWNER = 'ADM' AND
TABLE_NAME = 'TEST' AND COLUMN_NAME = 'OWNER';
As you can see there is only one bucket, because there are too few values in
the column.
NUM_BUCKETS
-----------
1
Now we will try again
SQL> SELECT * FROM TEST WHERE OWNER = 'CACHITO';
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=273)
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=3 Bytes=273)
2 1
INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=1 Card=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1213 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> SELECT * FROM TEST WHERE OWNER = 'XXX';
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=140 Card=88713 Bytes=8072883)
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=140 Card=88713 Bytes=8072883)
2 1
INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=186 Card=88713)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13132 consistent gets
0 physical reads
0 redo size
4304202 bytes sent via SQL*Net to client
65557 bytes received via SQL*Net from client
5916 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
88713 rows processed
Maybe this is a error in the CBO, so we will force a full scan in the table
SQL> SELECT /*+ FULL(TEST) */ * FROM TEST WHERE OWNER = 'XXX';
88713 filas seleccionadas.
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=188 Card=88713 Bytes=8072883)
1 0
TABLE ACCESS (FULL) OF 'TEST' (Cost=188 Card=88713 Bytes=8072883)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7060 consistent gets
0 physical reads
0 redo size
4304202 bytes sent via SQL*Net to client
65557 bytes received via SQL*Net from client
5916 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
88713 rows processed
But it seems not to be, a full scan cost 40 more, so it seems this is not a
good place to use histograms.-
Meanwhile the CBO is becoming smarter, the more test one have to develop to
verify the things one do, are really improving the performance.
More about histograms you can read
http://www.dba-oracle.com/art_otn_cbo_p4.htm
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
