OraFAQ Forum: Server Administration » Gather schema statistics

Home » RDBMS Server » Server Administration » Gather schema statistics (10.2.0.4.0)

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

Gather schema statistics [message #530726]

Thu, 10 November 2011 01:02

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Hi all,

I have one doubt regarding collecting schema statistics in database using command.

which is the right way to collect schema statistics among the below 2 commands?

1.EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'BALA',
METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',DEGREE => 8,CASCADE => TRUE);

2.exec dbms_stats.gather_table_stats (user, 'test');

Please clarify

Regards,

Report message to a moderator

Re: Gather schema statistics [message #530734 is a reply to message #530726]

Thu, 10 November 2011 01:25

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

Senior Member
Account Moderator

The second one does NOT collect schema statistics.

Regards
Michel

Report message to a moderator

Re: Gather schema statistics [message #530744 is a reply to message #530734]

Thu, 10 November 2011 02:32

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thanks Michel.

So it means,we should use the other command to collect schema statistics.

Quote:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'BALA',
METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',DEGREE => 8,CASCADE => TRUE);

Here in this above command , can you tell me what number we should give in place of DEGREE parameter?

Report message to a moderator

Re: Gather schema statistics [message #530747 is a reply to message #530744]

Thu, 10 November 2011 03:04

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

Senior Member
Account Moderator

If you don't know how to set a parameter, let the default value.

Regards
Michel

Report message to a moderator

Re: Gather schema statistics [message #530753 is a reply to message #530747]

Thu, 10 November 2011 04:57

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Hi,

I am getting below error while trying to gather table statistics.

SQL>
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'DEMO,TABNAME=>'PARTS_EXCLUDED_TAB','PARTS_EXCLUDED_AUDIT_TAB','PART_PRICE_TAB','PART_TAB','PART_DESCRIPTION_TAB',METHOD_OPT= >'FOR ALL INDEXED COLUMNS',DEGREE => 8,CASCADE => TRUE);
BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'RIMSPO',TABNAME=>'PARTS_EXCLUDED_TAB','PARTS_EXCLUDED_AUDIT_TAB','PART_PRICE_TAB','PART_TAB','PART_DESCRIPTION_TAB',METHOD_OPT= >'FOR ALL INDEXED COLUMNS',DEGREE => 8,CASCADE => TRUE); END;

Quote:

*
ERROR at line 1:
ORA-06550: line 1, column 85:
PLS-00312: a positional parameter association may not follow a named
association
ORA-06550: line 1, column 7:

Please tell me how to fix this error.

Thanks

Report message to a moderator

Re: Gather schema statistics [message #530757 is a reply to message #530753]

Thu, 10 November 2011 05:11

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

GATHER_TABLE_STATS accepts, for a TABNAME parameter, name of TABLE (singular, not plural; one table, not many of them).

Report message to a moderator

Re: Gather schema statistics [message #530759 is a reply to message #530753]

Thu, 10 November 2011 05:14

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

Senior Member
Account Moderator

Do not put a positional parameter after a named one.
Rewrote your call.

Regards
Michel

Report message to a moderator

Re: Gather schema statistics [message #530761 is a reply to message #530759]

Thu, 10 November 2011 05:20

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thank you both for helping me gathering table statistics.

Before seeing your answers, i gathered tables one by one and it works.

Your answers proved that there is no way of gathering multiple tables at a time
using DBMS_STATS.GATHER_TABLE_STATS package

Thank you..

Report message to a moderator

Re: Gather schema statistics [message #530773 is a reply to message #530761]

Thu, 10 November 2011 06:03

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

Senior Member
Account Moderator

The documentation proves it more accurately.

Regards
Michel

Report message to a moderator

Previous Topic:	user quota issue (2 Merged)
Next Topic:	10g vs 11g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 17:48:46 CST 2025