Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Burned by DBMS_STATS **AGAIN**
I tried to see if that was indeed the issue by saving SYS.HIST_HEAD$ after
an ANALYZE and repopulating the DENSITY column after rebuilding the stats
with DBMS_STATS, but the explain plan of the icky SQL in question didn't
change. I then tried doing the same with the remaining columns that differ
in HIST_HEAD$ between ANALYZE and DBMS_STATS -- BUCKET_CNT, SAMPLE_SIZE,
MINIMUM, MAXIMUM, SPARE2, and AVGCLN -- but no change in results.
So, without further hacking, I think I'm left with using ANALYZE for the forseeable future.
Did I mention a new/improved query in this 3rd party app? They added an ORDERED hint causing a 7-sec query to now blow up after using all 2GB of TEMP available. The explain plan estimates TEMP usage at about 29GB on a MERGE SORT CARTESIAN (if I interpreted it correctly)...
Thank God tomorrow's Beer Day at the Beer Ale Lager Lovers Society! :9 http://society.servebeer.com
Rich
Rich Jesse System/Database Administrator rich.jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
-----Original Message-----
Sent: Wednesday, April 09, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L
A-ha....now try the same thing with a column that is NULL in all rows.
There's one difference between ANALYZE and DBMS_STATS, but I'm not sure if that accounts for the explain plan diffs I'm seeing. It would account for the fact that I wasn't seeing any of those columns in DBA_IND_COLUMNS for this particular schema though.
Stay tuned to this thread for further developments.
Rich
Rich Jesse System/Database Administrator rich.jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA "AAAAAAAAAAAAAAAAAAAAAAAAAAAUGH! Tension breaker, had to be done." -- Chainsaw Summer School
-----Original Message-----
Sent: Tuesday, April 08, 2003 5:09 PM
To: Multiple recipients of list ORACLE-L
I've just checked on my 8.1.7.4
create table t1 (n1 number, v1 varchar2(10)); create index t1_idx on t1(n1);
insert into t1
select rownum rownum
from all_objects
where rownum <= 100;
analyze table T1 compute statistics;
select * from user_tab_histograms
where table_name = 'T1';
This gives me two rows each
for N1 and V1.
Similarly
select low_value, high_value
from user_tab_columns
where table_name = 'T1';
gives me one row for each column,
with the high and low value set.
analyze table XXX compute statistics;
should be the equivalent of
'for table for ALL columns size 1';
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Apr 09 2003 - 14:24:04 CDT