Home » RDBMS Server » Performance Tuning » Histograms: 32 character limit generates inaccurate stats
Histograms: 32 character limit generates inaccurate stats [message #376234] Tue, 16 December 2008 09:47 Go to next message
zaff
Messages: 50
Registered: July 2008
Member
Hi guys,

The following entry is in the crontab :

$ crontab -l | grep fix
0 7 * * * /usr/mvf/fix_uid_bug.sh >/dev/null


The contents of the script executed are as follows:

$ cat /usr/mvf/fix_uid_bug.sh
#!/bin/bash
. /usr/mvf/.bashrc

echo "execute sys.fix_uid_bug;"| sqlplus dbadmin/dbadmin


And the contents of the stored procedure that the script runs is listed below:

SQL> set pagesize 0;

SQL> select text from dba_source where name='FIX_UID_BUG';

procedure fix_uid_bug
as
n1 pls_integer;
n2 pls_integer;

begin

select num_rows into n1 from dba_tables where table_name='T1';

select num_rows into n2 from dba_tables where table_name = 'T2';

if n1 <> 0 then

DBMS_STATS.SET_COLUMN_STATS(ownname=>'DBADMIN', tabname=>'T1',colname=>'COL1',distcnt=>n1,density=>1/n1);

end if;

if n2 <> 0 then
DBMS_STATS.SET_COLUMN_STATS(ownname=>'DBADMIN', tabname=>'T2',colname=>'COL2',distcnt=>n2,density=>1/n2);

end if;

execute immediate 'alter system flush shared_pool';

end;


we were informed that this script is run to address a deficiency in Oracle whereby the statistics gathering only looks at the first 32 bytes of a character column when creating the statistics histogram, and since study_uid’s are generally over 32 characters in length (with many of the first 32 characters being the same), this produces skewed statistics, adversely affecting the query planner and producing suboptimal, slow query plans.

Is there a better way of obtaining accurate stats rather than using the process above i.e. manual updates of stats?

Is there a way of increasing the 32 character limit on histograms?

Thanks in advance,
Zabair

[Updated on: Tue, 16 December 2008 09:58]

Report message to a moderator

Re: Histograms: 32 character limit generates inaccurate stats [message #376240 is a reply to message #376234] Tue, 16 December 2008 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version (4 decimals)?

Regards
Michel
Re: Histograms: 32 character limit generates inaccurate stats [message #376244 is a reply to message #376240] Tue, 16 December 2008 10:31 Go to previous messageGo to next message
zaff
Messages: 50
Registered: July 2008
Member
Sorry, i knew i forgot something: 10.2.0.2

Regards,
Zaff
Re: Histograms: 32 character limit generates inaccurate stats [message #376258 is a reply to message #376234] Tue, 16 December 2008 13:11 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Is there a way of increasing the 32 character limit on histograms?
Nope.
Seems it is still classified under Enhancement Request.
Previous Topic: Simple query taking too long - looking for Index Suggestion
Next Topic: Post SHRINK Actions : REBUILD INDEX & CBO Stats ?
Goto Forum:
  


Current Time: Mon Dec 05 03:05:38 CST 2016

Total time taken to generate the page: 0.26056 seconds