Home » RDBMS Server » Performance Tuning » dbms_stats
dbms_stats [message #118024] Mon, 02 May 2005 13:47 Go to next message
balavignesh
Messages: 180
Registered: March 2005
Senior Member
hai all,
i tried to execute the procedure
dbms_stats.get_table_stats to get the table statistics, but it is blowing some errors...help out in this issue.

SQL> variable NUMROWS number
SQL> variable NUMBLKS number
SQL> variable AVGRLEN number
SQL> begin
2 SYS.DBMS_STATS.GET_TABLE_STATS('SYSTEM','PROBLEM','','','',NUMROWS=>:numrows,
3 NUMBLKS=>:numblks,AVGRLEN=>:avgrlen);
4 end;
5 /
begin
*
ERROR at line 1:
ORA-20000: Unable to get values for table PROBLEM
ORA-06512: at "SYS.DBMS_STATS", line 2647
ORA-06512: at line 2

regards
bala
Re: dbms_stats [message #118067 is a reply to message #118024] Mon, 02 May 2005 22:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Did you gather the table stats before you tried to get them? Please see the demonstration below that first tries to get the table stats without having gathered them first, then gathers them, then sucessfully get them. Also, you shoul probably have your tables in another schema, instead of system.

-- table and data for testing:
scott@ORA92> CREATE TABLE PROBLEM (col1 NUMBER)
  2  /

Table created.

scott@ORA92> INSERT INTO PROBLEM VALUES (1)
  2  /

1 row created.

scott@ORA92> COMMIT
  2  /

Commit complete.


-- unsuccessful attempt to get table stats:
scott@ORA92> VARIABLE numrows NUMBER
scott@ORA92> VARIABLE numblks NUMBER
scott@ORA92> VARIABLE avgrlen NUMBER
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> BEGIN
  2    DBMS_STATS.GET_TABLE_STATS
  3  	 ('SCOTT', 'PROBLEM', NULL, NULL, NULL, :numrows, :numblks, :avgrlen);
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-20000: Unable to get values for table PROBLEM
ORA-06512: at "SYS.DBMS_STATS", line 2582
ORA-06512: at line 2



   AVGRLEN
----------



   NUMBLKS
----------



   NUMROWS
----------


-- gather table stats:
scott@ORA92> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'PROBLEM')

PL/SQL procedure successfully completed.


-- now you can get the table stats that have been gathered:
scott@ORA92> VARIABLE numrows NUMBER
scott@ORA92> VARIABLE numblks NUMBER
scott@ORA92> VARIABLE avgrlen NUMBER
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> BEGIN
  2    DBMS_STATS.GET_TABLE_STATS
  3  	 ('SCOTT', 'PROBLEM', NULL, NULL, NULL, :numrows, :numblks, :avgrlen);
  4  END;
  5  /

PL/SQL procedure successfully completed.


   AVGRLEN
----------
         3


   NUMBLKS
----------
         1


   NUMROWS
----------
         1

scott@ORA92> 


Re: dbms_stats [message #118175 is a reply to message #118067] Tue, 03 May 2005 08:40 Go to previous message
balavignesh
Messages: 180
Registered: March 2005
Senior Member
hai,

thanks a lot. it's working fine now.

regards
bala
Previous Topic: Explain Plan Doubt
Next Topic: max size of sga in oracle 81 windows 2000 server
Goto Forum:
  


Current Time: Sat Sep 19 01:12:43 CDT 2020