| Team Fly | |
Schema objects were discussed earlier in this chapter, and you saw that we can manage schema objects through OEM. There are also some things that you may want to do with your own SQL scripts that run as scheduled jobs. When managing schemas, you need to ensure that those physical objects that take up a great deal of space, do in fact have enough space to grow. This includes tables, indexes, clusters, and partitioned tables. Manage this space through the tablespace that they are implemented in and ensure that there is enough room to grow in the tablespace. Ensuring that the extent sizes are large enough that you do not need to allocate too many extents is something that you need to monitor. But, do not become reorg-happy. You do not need to reorg a table if it is in hundreds of extents. You only need to reorg if there are a large number of chained or migrated rows. Indexes, on the other hand, will need to be reorged more frequently. We will find out more about managing space in the next section.
Figure 3-2 shows an example of how the SH.Customers table can be managed through OEM. Note the Storage tab that allows you to change the table's storage parameters. You should also try to maintain statistics on your tables and indexes so they are up-to-date. This will assist the optimizer make better decisions when choosing access paths for your queries and can be used to validate the structures. In Oracle Database 10g, a scheduler job called gather_stats_job will run during a maintenance window between 10:00 P.M. and 6:00 A.M., by default, and will run statistics for those objects in cases where they have not been collected yet or are stale. Setting the Oracle Database 10g initialization parameter statistics_level to typical (the default) will allow Oracle to automatically update statistics as a background task on a regular basis and is the recommended approach for gathering statistics. In pre-Oracle Database 10g releases, the DBMS_STATS package should be run manually or can use the Monitoring keyword in a CREATE or ALTER table. Monitoring is a deprecated feature in Oracle Database 10g and the keyword (along with ''nomonitoring") will be ignored.
Logical schema objects that do not take up a lot of space need to be watched to ensure they are not invalid. Triggers, views, synonyms, procedures, functions, and packages are examples of the objects that should be valid. You can check this with the SQL statement that follows.
select owner, object_name, object_type from dba_objects where status ^= 'VALID';
We've looked at many of the database objects that will require your attention. Let's now explore one area that requires special attention due to the size of today's databases.
| Team Fly | |