Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to run Gather Stats For Whole Database.

540301Feb 26 2009 — edited Feb 26 2009
Hi DBAs,


I want to run the Gather Stats for All database schemas (50% or more). Please advise the the best way to run that.


Thanks
-Samar-
This post has been answered by 247514 on Feb 26 2009
Jump to Answer

Comments

Pavan Kumar
Hi,

You can get the all information about from oracle Documentation.
Refer : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm

- Pavan Kumar N
1 person found this helpful
181444
Samar, why didn't you list your Oracle version? On 10g Oracle includes a task to automatically gathter the optimizer statistics.

See your Supplied Packages and Types manual on 9.2 for dbms_stats to see you options. The gather_system_stats and gather_schema_stats procedures might be of interest.

HTH -- Mark D Powell --
1 person found this helpful
540301
The Database Version is 10.2.0.4 on RHEL 4.7 (32 bit). Due to some securtiy issues, DB Control/ Grid Control was not configured. Please let me know about the automated way as well as how I can run the Stats manually.

Thanks for reviewing the post and suggestions.

Regards
-Samar-
247514
Answer
You can check this part of Oracle document
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#PFGRF30102

also note,

Enabling Automatic Statistics Gathering

Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. You can verify that the job exists by viewing the DBA_SCHEDULER_JOBS view:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
In situations when you want to disable automatic statistics gathering, the most direct approach is to disable the GATHER_STATS_JOB as follows:

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
Marked as Answer by 540301 · Sep 27 2020
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 26 2009
Added on Feb 26 2009
#general-database-discussions
4 comments
329 views