Statistics Collection

Page 18

Oracle Database 10g comes with a wealth of features that make life easier for both the old school and the new school DBAs, as you'll see in this chapter. These new features are designed to reduce the overall cost of managing the database and to make it easier for us to actually use at least three days of our generous two week vacation allotment! In this chapter we will look at these specific topics:

Using new statistics-collection features

Flushing the database buffer cache

Using the Database Resource Manager new features

Firing up the new job scheduler (known as The Scheduler)

Learning all about user-configurable default tablespaces

Using tablespace groups and multiple default temporary tablespaces

Renaming tablespaces

Dropping databases

Taking advantage of new LOB storage limitations

Using Automatic Undo retention

Shrinking segments online

Using new online redefinition features

Oracle Database 10g offers some new features to help you collect database statistics. These new features include automated collection of statistics, collection of data dictionary statistics, new behaviors associated with the dbms_stats package, and new features related to monitoring tables in the database. Let's look at these new features in more detail next.

Automated Statistics Collection

By default Oracle will create a job at database creation time that automatically collects database statistics. This job is scheduled in the database job scheduler and can be seen using the DBA_SCHEDULER_JOBS view. The scheduled job runs the stored program dbms_stats.gather_database_stats_job_proc. The statistics collection job runs as a single job scheduler program that is assigned to a window which executes the program within two different job windows. The first job window runs the statistics collection job Monday through Friday at 10 P.M. The second job