Wolfgang, Could you post the link to your wonderful paper/presentation of the CBO = fallacies? It's right to the point here. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -----Original Message----- From: Juan Carlos Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx] Sent: Wednesday, July 21, 2004 2:08 PM To: oracle-l@xxxxxxxxxxxxx Subject: Re: Creating Histograms 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. =20 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 =3D> '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 =20 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=20 select * from dba_objects UNION ALL=20 select * from dba_objects UNION ALL=20 select * from dba_objects; create index idxtest on test (OWNER); SQL> UPDATE TEST SET OWNER =3D 'XXX' WHERE NOT OWNER =3D '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=3D>'ADM',TABNAME=3D>'TEST' CASCADE=3D>TRUE); =20 SQL> SELECT * FROM TEST WHERE OWNER =3D 'CACHITO'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D70 Card=3D44358 = Bytes=3D4036578) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3D70 Card=3D44358 = Bytes=3D4036578) 2 1 INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=3D93 Card=3D44358) 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 =3D 'XXX'; 88713 filas seleccionadas. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D70 Card=3D44358 = Bytes=3D4036578) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3D70 Card=3D44358 = Bytes=3D4036578) 2 1 INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=3D93 Card=3D44358) 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 =3D = 'ADM' AND TABLE_NAME =3D 'TEST' AND COLUMN_NAME =3D 'OWNER'; =20 NUM_BUCKETS ----------- 1 =20 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('ADM','TEST', METHOD_OPT =3D> = 'FOR COLUMNS SIZE 75 OWNER'); Procedimiento PL/SQL terminado correctamente. SQL> SELECT NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS WHERE OWNER =3D = 'ADM' AND TABLE_NAME =3D 'TEST' AND COLUMN_NAME =3D 'OWNER'; =20 As you can see there is only one bucket, because there are too few = values in the column. NUM_BUCKETS ----------- 1 =20 Now we will try again SQL> SELECT * FROM TEST WHERE OWNER =3D 'CACHITO'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D2 Card=3D3 Bytes=3D273) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3D2 Card=3D3 Bytes=3D273) 2 1 INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=3D1 Card=3D3) 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 =20 SQL> SELECT * FROM TEST WHERE OWNER =3D 'XXX'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D140 Card=3D88713 = Bytes=3D8072883) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3D140 Card=3D88713 = Bytes=3D8072883) 2 1 INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=3D186 Card=3D88713) 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 =20 Maybe this is a error in the CBO, so we will force a full scan in the = table =20 SQL> SELECT /*+ FULL(TEST) */ * FROM TEST WHERE OWNER =3D 'XXX'; 88713 filas seleccionadas. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D188 Card=3D88713 = Bytes=3D8072883) 1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=3D188 Card=3D88713 Bytes=3D8072883) 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 =20 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. =20 =20 More about histograms you can read=20 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------