Re: Creating Histograms

From: Juan Carlos Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Wed, 21 Jul 2004 14:08:13 -0400
Message-Id: <40FEB10D.000015.00336@CACHITOSS>

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 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

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

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

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

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Wed Jul 21 2004 - 13:12:36 CDT