OraFAQ Forum: Performance Tuning » Oracle 8.1.7 statistics problem

Home » RDBMS Server » Performance Tuning » Oracle 8.1.7 statistics problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle 8.1.7 statistics problem [message #225029]

Fri, 16 March 2007 11:44

zoltanp
Messages: 58
Registered: March 2005
Location: Hungary

Member

I have a partitioned table in our database.
I'm not able to create a usable statistics on this table,
becouse with statistics the optimizer not use the indexes.
When i delete statistics everything is OK.

Thanks,
Zolt�n Patalenszki

Report message to a moderator

Re: Oracle 8.1.7 statistics problem [message #225030 is a reply to message #225029]

Fri, 16 March 2007 11:50

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

How did you collect the stats.
Post the exact statement.

Report message to a moderator

Re: Oracle 8.1.7 statistics problem [message #225038 is a reply to message #225030]

Fri, 16 March 2007 12:27

zoltanp
Messages: 58
Registered: March 2005
Location: Hungary

Member

That is my procedure:

CREATE OR REPLACE PROCEDURE Stati(p_sema VARCHAR2,
p_szazalek VARCHAR2, p_cpu VARCHAR2)
IS
v_verzio VARCHAR2(25);
v_mennyi NUMBER;
BEGIN
DBMS_STATS.gather_schema_stats(ownname=>p_sema,
estimate_percent=>p_szazalek, method_opt=> 'FOR ALL COLUMNS SIZE 1', DEGREE=>p_cpu,
CASCADE=>TRUE,
options=>'GATHER');
END;
/

Thanks,

Zolt�n Patalenszki

Report message to a moderator

Re: Oracle 8.1.7 statistics problem [message #225048 is a reply to message #225038]

Fri, 16 March 2007 14:29

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

gather_schema_stats used have some bugs in 8i (could not recollect the exact bug).
try this for the concerned partitioned table.

exec dbms_stats.gather_table_stats('OWNER','TABLE_NAME',method_opt=>'FOR  ALL INDEXED COLUMNS SIZE 250',cascade=>'TRUE');

If this does not work
look into query. May the CBO is doing the right job (no need for index scan).

Report message to a moderator

Previous Topic:	Delete statement and performance
Next Topic:	how to rewrite the outer join statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 08:36:09 CDT 2024