OraFAQ Forum: Performance Tuning » SQL performance tunning :- DBMS

Home » RDBMS Server » Performance Tuning » SQL performance tunning :- DBMS_STATS procedure errored out (Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production)

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

SQL performance tunning :- DBMS_STATS procedure errored out [message #616061]

Thu, 12 June 2014 02:13

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

Hi All,

I am trying to gather stats on one of my table,but encountered with below error

Code: [Select all] [Show/ hide]

ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9389
ORA-06512: at line 1

I used below query to gather the stats :-

Code: [Select all] [Show/ hide]

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('schema_name',table_name', cascade => true);

but when i am removing 'cascade => true' , the procedure is getting completed succesfully.

Any pointer, helps a lot.

Thanks

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616062 is a reply to message #616061]

Thu, 12 June 2014 02:18

John Watson
Messages: 8979
Registered: January 2010
Location: Global Village

Senior Member

It works for me, once I correct your missing quote:

Code: [Select all] [Show/ hide]

orcla>
orcla> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('scott','emp', cascade => true);

PL/SQL procedure successfully completed.

orcla>

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616065 is a reply to message #616061]

Thu, 12 June 2014 02:43

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Check with your DBA if you are allowed to execute this package.

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616068 is a reply to message #616062]

Thu, 12 June 2014 02:47

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

Hi John,

Thanks for your reply, after giving quote also i got the same output.

Thanks

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616070 is a reply to message #616065]

Thu, 12 June 2014 02:49

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

Michel,

I was able to run this procedure for my other tables and it is completing successfully.

Thanks

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616075 is a reply to message #616068]

Thu, 12 June 2014 03:11

John Watson
Messages: 8979
Registered: January 2010
Location: Global Village

Senior Member

msinha8 wrote on Thu, 12 June 2014 08:47

Hi John,

Thanks for your reply, after giving quote also i got the same output.

Thanks

Show me. Use copy/paste, enclosed within [code] tags, just as did.

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616086 is a reply to message #616075]

Thu, 12 June 2014 04:15

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

John,

Here we have the script :-

Code: [Select all] [Show/ hide]

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('XXCS','XXCS_NORM_ALARM', cascade => true);

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616088 is a reply to message #616086]

Thu, 12 June 2014 04:19

John Watson
Messages: 8979
Registered: January 2010
Location: Global Village

Senior Member

Please think. What you have posted is useless for diagnosis.
You claim that you can analyze some tables but not others. Show what happens when you run a command successfully, and when you don't.

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616096 is a reply to message #616088]

Thu, 12 June 2014 04:55

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

John,

Appologies for that.

Please see the attached screen shot of the execution and final status of two query which i have ran through TOAD.

Attachment: stats_err_ss.pdf
(Size: 157.78KB, Downloaded 2426 times)

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616107 is a reply to message #616096]

Thu, 12 June 2014 05:29

John Watson
Messages: 8979
Registered: January 2010
Location: Global Village

Senior Member

I have no idea what TOAD does. From what I have read about the problems people have with it, I suspect the people who wrote TOAD have no idea either. If you want to test something, use SQL*Plus.

("those who live by the GUI, die bt the GUI" - BlackSwan, I believe)

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616194 is a reply to message #616107]

Fri, 13 June 2014 06:16

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

John,

Please see below , i have ran the same query in SQl*Plus :-

Code: [Select all] [Show/ hide]

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('XXCS', 'XXCS_NORM_ALARM', cascade   => TRUE);
BEGIN DBMS_STATS.GATHER_TABLE_STATS ('XXCS', 'XXCS_NORM_ALARM', cascade   => TRUE); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9389
ORA-06512: at line 1


SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('XXCS', 'XXCS_MSS_TRANSACTIONS', cascade   => TRUE);

PL/SQL procedure successfully completed.

SQL>

Please help me to trace the issue.

Regards

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616211 is a reply to message #616194]

Fri, 13 June 2014 08:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

your obfuscation is not helping!

post results from SQL below

show user
desc XXCS.XXCS_NORM_ALARM

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616219 is a reply to message #616211]

Fri, 13 June 2014 10:26

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

Please see results below

Code: [Select all] [Show/ hide]

SQL> show user
USER is "SYSTEM"
SQL>  desc XXCS.XXCS_NORM_ALARM;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ALM_ID                                    NOT NULL NUMBER(12)
 SITE_NAME                                          VARCHAR2(50)
 RECEIVER                                           VARCHAR2(25)
 DESCRIPTION                                        VARCHAR2(255)
 SOURCE                                             VARCHAR2(255)
 TIME_RECEIVED                                      DATE
 TIME_OCCURRED                                      DATE
 TIME_DIALOUT                                       DATE
 CONTROLLER                                         VARCHAR2(255)
 SUB_CONTROLLER                                     VARCHAR2(35)
 ALM_TYPE                                           VARCHAR2(25)
 ALM_STATE                                          VARCHAR2(25)
 ALM_PRIORITY                                       VARCHAR2(25)
 CALLER_ID                                          VARCHAR2(25)
 RECEIVER_ID                                        VARCHAR2(25)
 SRC_PROBABLE                                       VARCHAR2(35)
 DESC_PROBABLE                                      VARCHAR2(35)
 RECEIVER_HOST                                      VARCHAR2(35)
 FILE_ID                                            NUMBER(12)
 FIELD1                                             VARCHAR2(35)
 FIELD2                                             VARCHAR2(255)
 FIELD3                                             VARCHAR2(35)
 FIELD4                                             VARCHAR2(35)
 FIELD5                                             VARCHAR2(35)
 FIELD6                                             VARCHAR2(35)
 FIELD7                                             VARCHAR2(35)
 FIELD8                                             VARCHAR2(35)
 FIELD9                                             VARCHAR2(35)
 FIELD10                                            VARCHAR2(35)
 FIELD11                                            VARCHAR2(35)
 FIELD12                                            VARCHAR2(35)
 FIELD13                                            VARCHAR2(35)
 FIELD14                                            VARCHAR2(35)
 FIELD15                                            VARCHAR2(35)
 UARD_SITE_NAME                                     VARCHAR2(35)
 CHANNEL_SITE_ID                                    NUMBER
 UARD_AREA_CODE                                     VARCHAR2(35)
 UARD_PHONE                                         VARCHAR2(35)
 CUST_ID                                            NUMBER
 COMMENTS                                           VARCHAR2(150)
 ALARM_COUNT_24_HRS                                 NUMBER
 ALARM_COUNT_7_DAYS                                 NUMBER
 CREATE_DATE                                        DATE
 CONTROLLER_INSTANCE                                VARCHAR2(35)
 REQUEST_ID                                         NUMBER
 PROCESSED_FLAG                                     VARCHAR2(15)
 SR_REFERENCE                                       NUMBER
 WO_REFERENCE                                       NUMBER
 ATTRIBUTE1                                         VARCHAR2(150)
 ATTRIBUTE2                                         VARCHAR2(150)
 ATTRIBUTE3                                         VARCHAR2(150)
 ATTRIBUTE4                                         VARCHAR2(150)
 ATTRIBUTE5                                         VARCHAR2(150)
 ATTRIBUTE6                                         VARCHAR2(150)
 ATTRIBUTE7                                         VARCHAR2(150)
 ATTRIBUTE8                                         VARCHAR2(150)
 ATTRIBUTE9                                         VARCHAR2(150)
 ATTRIBUTE10                                        VARCHAR2(150)
 ID1                                                NUMBER
 ID2                                                NUMBER
 ID3                                                NUMBER
 ID4                                                NUMBER
 ID5                                                NUMBER
 CREATION_DATE                                      DATE
 CREATED_BY                                         NUMBER
 LAST_UPDATE_DATE                                   DATE
 LAST_UPDATED_BY                                    NUMBER
 LAST_UPDATED_LOGIN                                 NUMBER
 ROUTING_GROUP                                      VARCHAR2(60)
 TIME_RECEIVED_CH                                   VARCHAR2(30)
 TIME_OCCURRED_CH                                   VARCHAR2(30)
 TIME_DIALOUT_CH                                    VARCHAR2(30)
 EMAIL_ADDRESS                                      VARCHAR2(2000)
 EMAIL_PROCESSED                                    VARCHAR2(1)
 EMAIL_ALERT_ID                                     NUMBER
 NORM_SOURCE_ID                                     NUMBER
 NORM_DESC_ID                                       NUMBER
 RAW_NORM_MAPPING_ID                                NUMBER
 RAW_SOURCE_DESC_ID                                 NUMBER
 TASK_ID                                            NUMBER
 TASK_NUMBER                                        NUMBER
 NOTE_TYPE                                          VARCHAR2(30)
 LAST_NOTE_TYPE                                     VARCHAR2(30)
 COMPANY_NAME                                       VARCHAR2(255)
 LAST_COMPANY_NAME                                  VARCHAR2(255)
 RTN_DATE                                           DATE

Please let me know if you need more information.

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616220 is a reply to message #616219]

Fri, 13 June 2014 10:38

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Is this the same use you use for "EXECUTE DBMS_STATS.GATHER_TABLE_STATS"?

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616221 is a reply to message #616220]

Fri, 13 June 2014 11:43

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

Yes,

I am using same user for "EXECUTE DBMS_STATS.GATHER_TABLE_STATS".

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616223 is a reply to message #616221]

Fri, 13 June 2014 11:48

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have you a FBI (Function Based Index) defined on this table?

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616225 is a reply to message #616223]

Fri, 13 June 2014 12:05

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

I have checked that, but it is not defined on my table.

Code: [Select all] [Show/ hide]

SQL> show user
USER is "SYSTEM"
SQL> SELECT   table_name,
  2           index_name,
  3           index_type,
  4           status,
  5           FUNCIDX_STATUS
  6    FROM   dba_indexes
  7   WHERE   table_name = 'XXCS_NORM_ALARM' AND index_type LIKE 'FUN%';

no rows selected

SQL>

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616230 is a reply to message #616061]

Fri, 13 June 2014 15:48

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

msinha8 wrote on Thu, 12 June 2014 12:43

I am trying to gather stats on one of my table,but encountered with below error

I am not sure if it's a bug in 9i, I can't replicate your issue since I don't have the previous century's DB version. However, there was a similar issue addressed in OTN forum and a bug ID 5645718 was provided here https://community.oracle.com/thread/2444728?start=0&tstart=0 See if it helps. Contact Oracle support if you get some information from them.

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616238 is a reply to message #616230]

Sat, 14 June 2014 00:37

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This bug is about error "ORA-01476: divisor is equal to zero" nothing related to the current problem.

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616239 is a reply to message #616225]

Sat, 14 June 2014 00:38

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Can you execute the statement from the table owner?

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616453 is a reply to message #616239]

Tue, 17 June 2014 02:36

msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad

Member

All,

I found on metalink(Doc ID 409109.1),and according to that, it's bug and suggested us to use :-

Code: [Select all] [Show/ hide]

exec dbms_stats.gather_table_stats(ownname=>'schema_name',tabname=>'table_name_1',method_opt=>'FOR ALL INDEXED COLUMNS size 1',estimate_percent=>20,degree=>1,granularity=>'ALL',cascade=>TRUE);

And above statement executed without any errors and alternatively, below piece of code is also working fine :-

Code: [Select all] [Show/ hide]

exec dbms_stats.gather_table_stats(ownname=>'schema_name',tabname=>'table_name_1',estimate_percent=>dbms_stats.auto_sample_size,cascade   => TRUE)

But i want to know what is the meaning of the paramater value passed for 'method_opt'(in first statement) and 'estimate_percent'(in both statement)

And also when i tried with estimate_percent=> 100, it fails again. Why?

Regards,

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616455 is a reply to message #616453]

Tue, 17 June 2014 02:50

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

msinha8 wrote on Tue, 17 June 2014 13:06

what is the meaning of the paramater value passed for 'method_opt'(in first statement)

https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt.

Quote:

and 'estimate_percent'(in both statement)

Check out for parameters in the documentation http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm

Report message to a moderator

Re: SQL performance tunning :- DBMS_STATS procedure errored out [message #616456 is a reply to message #616453]

Tue, 17 June 2014 02:52

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

And also when i tried with estimate_percent=> 100, it fails again. Why?

If it is a bug then it is a bug... Smile

For the meaning of the parameters, refer to the documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68582

Report message to a moderator

Previous Topic:	General question on dbms_stats
Next Topic:	DBA_HIST_SEG_STAT view

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 11 15:07:20 CDT 2025