Home » RDBMS Server » Performance Tuning » dbms_stats
dbms_stats [message #129745] Tue, 26 July 2005 08:48 Go to next message
balavignesh
Messages: 180
Registered: March 2005
Senior Member
hai ,

we are using statspack to tune the performance of the db.based on the report generated, we have to generate the statistics for the objects to trigger CBO.we have used dbms_stats.get_table_stats function for the same.

it worked out good while gathering the statistics for one table which has 100 rows and did nt work out while gathering for the table having 3 rows and blown the errors as follows.

ERROR at line 1:
ORA-20000: Unable to get values for table TEST
ORA-06512: at "SYS.DBMS_STATS", line 2647
ORA-06512: at line 10

our procedure is as follows

------------------------------------------
declare

numrows integer;
numblks integer;
avglen integer;

begin


dbms_stats.get_table_stats('SYSTEM','TEST',null,null,null,numrows,numblks,avglen);
--dbms_stats.get_table_stats('TCMS_DATA','AUDITLOG',null,null,null,numrows,numblks,avglen);


dbms_output.put_line('numrows='||numrows||'numblks='||numblks||'avglen='||avglen);

end;
------------------------------------------

we have given the execute privilege for that package to public even then does not work out.how to fix this...?plz help.

regards
bala

[Updated on: Tue, 26 July 2005 08:49]

Report message to a moderator

Re: dbms_stats [message #129751 is a reply to message #129745] Tue, 26 July 2005 09:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
May be those tables are never been analyzed before!.
analyze those tables and try again.
And Do not have any custom objects in SYSETM schema.
Re: dbms_stats [message #129753 is a reply to message #129745] Tue, 26 July 2005 09:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
and
>>it worked out good while gathering the statistics for one table
YOu are not gathering any statistics here.
you are just displaying the statistics that is already generated.
TO gather the stats you are supposed to use GATHER_TABLE_STATS
Re: dbms_stats [message #129758 is a reply to message #129753] Tue, 26 July 2005 10:45 Go to previous message
balavignesh
Messages: 180
Registered: March 2005
Senior Member
hai mahesh,

it worked out.
thanks a lot for ur timely help.

regards
bala
Previous Topic: How increase OLTP regarding more CPU?
Next Topic: monitoring the instance specialy shared pool
Goto Forum:
  


Current Time: Fri Mar 29 05:18:44 CDT 2024