Home » RDBMS Server » Server Administration » Gather schema statistics (10.2.0.4.0)
Gather schema statistics [message #530726] Thu, 10 November 2011 01:02 Go to next message
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,
Re: Gather schema statistics [message #530734 is a reply to message #530726] Thu, 10 November 2011 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The second one does NOT collect schema statistics.

Regards
Michel
Re: Gather schema statistics [message #530744 is a reply to message #530734] Thu, 10 November 2011 02:32 Go to previous messageGo to next message
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?

Re: Gather schema statistics [message #530747 is a reply to message #530744] Thu, 10 November 2011 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't know how to set a parameter, let the default value.

Regards
Michel
Re: Gather schema statistics [message #530753 is a reply to message #530747] Thu, 10 November 2011 04:57 Go to previous messageGo to next message
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
Re: Gather schema statistics [message #530757 is a reply to message #530753] Thu, 10 November 2011 05:11 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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).
Re: Gather schema statistics [message #530759 is a reply to message #530753] Thu, 10 November 2011 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not put a positional parameter after a named one.
Rewrote your call.

Regards
Michel
Re: Gather schema statistics [message #530761 is a reply to message #530759] Thu, 10 November 2011 05:20 Go to previous messageGo to next message
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..


Re: Gather schema statistics [message #530773 is a reply to message #530761] Thu, 10 November 2011 06:03 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The documentation proves it more accurately.

Regards
Michel
Previous Topic: user quota issue (2 Merged)
Next Topic: 10g vs 11g
Goto Forum:
  


Current Time: Thu Mar 28 05:05:51 CDT 2024