Working with performance metrics in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to monitor performance using dynamic performance views, Enterprise Manager, and optimizer statistics.

1. Using dynamic performance views

Oracle Database 10 g contains hundreds of dynamic performance views. These views provide information on system and session statistics, instance activity, and file input/output.

Dynamic performance views provide you with real-time information about the current state of the database. Each view is reset as the instance shuts down, and started again as a new instance is opened.

This means that the data in a view is usually not useful until the instance has been running for some time.

Each performance view offers a snapshot of the state of the database at a particular moment in time.

Performance views are most useful when you are using several of them to track performance trends, or comparing the state of the view against some baseline, rather than as isolated sets of results.

There are two sets of dynamic performance views.

• V$ views
• GV$ views

V$ views

V$ views are read by Oracle Enterprise Manager, and can be accessed once the instance has started. Some views can only be read when the database is open, while others can be read as long as the database is mounted.

GV$ views

GV$ views correspond to V$ views. In Real Application Clusters, querying a GV$ view retrieves the V$ view information from all qualified instances. Each GV$ view contains an extra column called INST_ID, which contains the numbers of each database instance.

Question

What are the characteristics of dynamic performance views?

Options:

0. The data they contain is persistent across instances
0. They contain a snapshot of real-time data
0. You can only view data from one instance
0. You can view data from several instances

Answer

Dynamic performance views contain real-time data, and you can view data from several instances.

Option 1 is incorrect. Dynamic performance views reset when the instance closes and start counting up again when the instance starts up.

Option 2 is correct. The data returned by the dynamic performance view tells you what is happening in the database at that moment. Since each view is a snapshot, you should compare several views to get a good picture of your database.

Option 3 is incorrect. The V$ view returns data from the current database, but you can also use the GV$ view to return data from other qualified instances.

Option 4 is correct. The GV$ view returns information from all qualified instances. Each instance is identified by an ID number.

2. Using Enterprise Manager's Performance page

The Performance page in Enterprise Manager is a useful source of information about system performance. It is divided into three main areas:

• Host
• Sessions
• Instance Throughput

Host

The Host section of the page charts the length of the run queue and the memory paging rate. Both of these metrics describe how busy the server running the database is.

Sessions

The Sessions chart gives an overview of instance performance, showing grouped metrics from multiple categories. If one category is consuming a significant portion of the wait time for the instance, that category is where troubleshooting should start.

Instance Throughput

The Instance Throughput section of the page gives information about current sessions, redo generation, and datafile read activity.

Suppose the Sessions graph shows that a lot of the active sessions are working on user input/output issues. You want to find out why this is.

You select the User I/O link.

The Active Sessions Waiting: User I/O graph shows the number of active sessions that are being kept open by user input/output issues, and gives more detail on the particular issues involved.

In this case, the graph shows that most sessions are working on reading sequential data.

Question

Match each area of the Performance page to the information it provides.

Options:

0. Sessions
0. Host
0. Instance Throughput

Targets:

0. An overview of instance performance
0. Paging rate
0. Redo generation, and datafile
   read activity.

Answer

The Sessions section gives an overview of system performance. The Host section shows the paging rate. The Instance Throughput section shows redo generation and datafile read activity.

The Sessions section of the page contains a graph showing how many sessions are running in the current instance, and what type they are. This includes the number of user input/output sessions.

The Host section of the performance page shows how busy the server is, charting the run queue length and paging rate.

The Instance Throughput section of the page shows how busy the instance is. One graph charts logons and transactions, the other charts physical reads and redo file sizes.

Question

Use Enterprise Manager to get real-time information about the number of active user input/output sessions.

Select the option that supplies this information.

Options:

0. Click the Administration link, and then click the User I/O link beside the Sessions graph.
0. Click the Performance link, and then click the User I/O link beside the Sessions graph.

Answer

You click the Performance link, and then click the User I/O link beside the Sessions graph.

3. Gathering optimizer statistics

Optimizer statistics for tables and indexes are stored in the data dictionary. These statistics are not intended to provide real-time data.

Instead, they provide the optimizer with a snapshot of data storage and distribution, which it uses to make decisions on how to access data.

This includes information about the number of rows in each table, the number of datablocks used by tables and indexes, and the number of deleted leaf rows in indexes.

As the data in the tables is changed, the values for these metrics also change, and the statistics gathered by the optimizer become outdated.

Oracle recommends that you collect statistics frequently as the volatility of the tables in your database increases.

For the optimizer to work properly, there should never be more than a ten per cent difference between the state of the database and the latest set of statistics.

You can use Enterprise Manager to gather statistics manually. To save doing this repeatedly, you can also create jobs that will gather statistics automatically at regular intervals.

Question

What are the characteristics of optimizer statistics for tables and indexes?

Options:

0. They are stored in the data dictionary
0. They include information on the number of rows in each table
0. They provide real-time data
0. They record the number of blocks used by tables and indexes

Answer

Optimizer statistics are stored in the data dictionary, include information on the number of rows in each table, and record the number of blocks used by tables and indexes.

Option 1 is correct. Optimizer statistics are stored in the data dictionary, where they can be accessed by all incoming requests.

Option 2 is correct. Optimizer statistics describe the structure and size of database objects, including the number of rows in each table and the average length of those rows. This information indicates the most efficient methods to use when executing queries.

Option 3 is incorrect. Optimizer statistics are gathered at intervals - they do not perform live tracking of changes made to the database.

Option 4 is correct. Optimizer statistics record the number of rows in each table, the average length of the rows, the number of blocks used by database objects, and similar information.

Suppose you have made a lot of changes to the JOBS table in the HR schema, and you want to collect new statistics.

First, you open the Maintenance page.

You now want to start the utility that will gather the optimizer statistics.

You click Gather Statistics.

This starts the Gather Statistics wizard, which allows you to collect, estimate, or delete statistics.

You click Next to continue.

The Default Method page allows you to specify which task the wizard should perform - collecting, estimating, or deleting statistics.

The compute statistics method is selected by default, so you click Next to continue.

The Object Selection page allows you to specify which database elements you want to collect statistics for - schemas, tables, table partitions, indexes, index partitions, or an entire database.

You select Table s from the drop-down list, and click Add.

On the Tables page, you type in the HR schema name and click Go, and a list of the tables in the schema is displayed.

Now you need to select the relevant table.

You select the JOBS table, and click OK.

This returns you to the Object Selection page, where it now specifies that statistics will be gathered about the JOBS table.

You can specify how the statistics will be gathered - for example, whether the wizard will use a sample of the table or take data from the entire table, the level of granularity, and the number of histograms to create. You can also add more objects to be surveyed.

You click Next to continue.

The Schedule Analysis page allows you to name the job and add a description. It also allows you to schedule the job to run immediately, at a later date, or at regular intervals.

You have noticed that changes are made to the JOBS table almost every day, and the statistics gathered during non-working hours are regularly out of date by the end of the day. You decide to schedule this job to take place every day at the same time.

You select Interval in the Repeat section, and Days from the drop-down list.

If you schedule the job to run immediately, as is the default, the job will repeat every day at the same time. You can also choose to schedule the repeat jobs to run at a particular time each day.

You click Next to continue.

The final page of the wizard reviews the settings you have chosen. It also includes the SQL code that will be used to gather the statistics.

You click Submit to start the job.

The new optimizer statistics for the HR.JOBS table have been gathered. The job will repeat every day at the same time.

Question

You have just finished a major update of the EMPLOYEES table in the HR schema, and you want to start gathering optimizer statistics for this table.

Select the option that starts the wizard.

Options:

0. Select the Maintenance page, and select Gather Statistics.
0. Select the Maintenance page, and select Schedule Job.

Answer

You select the Maintenance page, and select Gather Statistics.

Question

You have selected the REGIONS and COUNTRIES tables for statistics-gathering. You anticipate further changes to these tables in the coming weeks, and want the statistics associated with them to be updated automatically every day.

Select the option that submits a regular update of the table statistics.

Options:

0. Click Next, then the Interval option button. Then click Next again and Submit Job.
0. Click Next, then the Schedule option button, and Daily. Then click Next again and Submit Job.

Answer

You click Next, and then click the Interval option. Then you click Next again before clicking Submit Job.

Summary

Oracle Database 10 g contains hundreds of dynamic performance views, which offer real-time information about the functioning of your database. The views reset when an instance stops, and start again when a new instance starts. V$ views offer information about the current instance, while GV$ views contain information about all instances of the database.
More performance information is available on the Performance page of Enterprise Manager. This page is divided into three sections, Host, Sessions, and Instance Throughput, each charting different types of activities. You can click into these sections to find out more about particular categories of the charted information.

The optimizer tool gathers information about database objects to improve queries. You can use Enterprise Manager to extract these statistics manually or to schedule automatic collection.