Tuesday, September 4, 2007

Performance monitoring in Oracle 10g

Performance monitoring in Oracle 10g

Learning objective

After completing this topic, you should be able to repair invalid and unusable objects.

1. Performance monitoring methodologies

The database administrator should monitor Oracle Database 10 g constantly to identify performance bottlenecks and other problems.

There are some common problem areas in databases which should be monitored carefully.

  • Application code problems
  • Device contention
  • Memory allocation
  • Network bottlenecks
  • Resource contention
Application code problems
If the application code contains errors or badly written sequences, they will show up in the database's performance as delays and other problems. As you monitor your database, you should be on the lookout for problems that can be traced back to the application code.
Device contention
Your database will often have several devices associated with it, from scanners and barcode readers for inputting data to printers and tape drives for outputting data. Since there are likely to be fewer such devices than there are users in the network, there may be delays as multiple threads try to access devices simultaneously.
Memory allocation
The servers that host the database and deal with user requests have a limited amount of memory, which must be allocated to elements such as the System Global Area, the data dictionary, and so on. You should constantly monitor the performance of these elements to ensure that they have enough memory available to them, otherwise system performance will be impaired.
Network bottlenecks
You should monitor the performance of the network that connects the database and users, because there may be bottlenecks that delay the transmission of messages.
Resource contention
Because there will be multiple users using the database, they will sometimes be competing for shared and limited resources. You should track the use of these resources, making sure that the delays to individual users are not too long.

Question

Which aspects of a database should be monitored?

Options:

  1. Application code
  2. Input/output devices
  3. Memory allocation
  4. Table indexes

Answer

You should monitor the application code, input/output devices, and system memory allocation.

Option 1 is correct. You should monitor the database performance for problems that may be caused by bugs in the application code.

Option 2 is correct. There may be multiple users attempting to use input and output devices simultaneously, and you should monitor how the system handles this contention.

Option 3 is correct. You should monitor the processes and database elements that have specific memory allocated to them, to make sure that some are not operating below par because of memory shortages while other processes have more memory than they need.

Option 4 is incorrect. The performance of the database should not really be affected by table indexes. In fact, indexes should speed up the retrieval of data.

Sometimes you will begin monitoring a database element in response to user complaints.
For example, a user may complain that the database is taking a long time to respond to SQL queries.

You then try to find out which part of the database is causing the delay, for example whether the network carrying the communications is slow, or there are too many requests being made on the database, so that you can start to fix the problem.

This is called reactive monitoring.

Although reactive monitoring will sometimes be necessary, you should focus your energies on proactive monitoring.

This means detecting issues before they can develop into problems, and before users start complaining or errors start appearing in the alert log.

The proactive approach to performance monitoring and system maintenance is more efficient in the long run, because it is easier to solve problems while they are still small.

Oracle Database 10 g includes server-generated alerts and an Automatic Database Diagnostic Monitor (ADDM) to support proactive monitoring.

Question

Identify the characteristics of reactive and proactive monitoring.

Options:

  1. Addresses issues before problems start
  2. Addresses problems as they arise
  3. Responds to user complaints and errors in the alert log
  4. Uses server-generated alerts and ADDM

Targets:

  1. Proactive monitoring
  2. Reactive monitoring

Answer

Proactive monitoring addresses issues before problems start, and uses server-generated alerts and ADDM. Reactive monitoring addresses problems as they arise and responds to user complaints and errors in the alert log.

2. Database and instance metrics

The performance of your database can be measured at hundreds of different points, depending on precisely what information you need to know.

The data dictionary, for example, contains information about the status of database objects, and the amount of space they use.

You can get information about the real-time performance of your database from v$ views. These views, also known as dynamic performance views, contain information about input/output device throughput, instance activity, memory usage, and wait events.

Oracle also collects data about the distribution of data within the database. This information is used by the optimizer, part of the server instance that decides what is the most efficient way to find a piece of data.

These data distribution statistics are therefore known as optimizer statistics.

Suppose a user is searching for a particular row in a table.

In a small table, the most efficient method is to scan the table itself, and go directly to the row in question.

In a longer table, however, it would be quicker to search the index, and jump from there to the correct block.

The optimizer collects statistics on each table, including

  • total number of rows
  • average length of rows
  • empty space allocated to the table

It also tracks the number of chained rows, or rows that exist in two or more datablocks. These statistics allow the optimizer to reduce the time needed for queries.

Question

Match the performance metrics with their sources.

Options:

  1. Data dictionary
  2. Dynamic performance views
  3. Optimizer statistics

Targets:

  1. Object status
  2. Input/output device throughput
  3. Number of rows in a table

Answer

The data dictionary provides information about object status. The dynamic performance views provide information about input/output device throughput. The optimizer statistics provide information about the number of rows in a table.

The data dictionary contains information about the space taken up by database objects, including procedures and indexes, and their current status.

The dynamic performance views provide real-time performance metrics, including the level of instance activity, the wait times for database access, and the throughput of input/output devices.

The optimizer stores information about tables to ensure that data retrieval is as fast as possible. This includes data on the number of rows in a table, and the average row length.

3. Repairing invalid and unusable objects

The stored procedures, indexes, and other objects in your database must be in a VALID state to be used.

One of the most important statistics gathered by the data dictionary is the object status report, which tells you which objects are invalid or unusable.

Some objects are invalid because of faulty application code. These objects were invalid as soon as they were created, and will remain invalid even if they are recompiled.

If an object was originally valid, but has become invalid, it can be fixed by recompiling it.

Suppose you have discovered that one of the procedures in the HR schema is invalid. You want to find the invalid procedure and restore it.

You select the Administration tabbed page.

On the Administration tabbed page you can choose to view various types of objects from the schema, as well as information about the instance and data storage.

You click the Procedures link.

On the Procedures page you choose which objects you are going to search for. Because you opened this page from the Procedures link, the default is to search for procedures, but you can choose to search for other object types instead. You must then specify the schema you wish to search within. You can also search for a particular object by name.

You enter HR as the schema, and click Go. This lists the procedures in that schema.

The ADD_JOB_HISTORY procedure in the schema is invalid. You want to restore it, so you first select it.

You select Compile in the Actions drop-down list, and click Go.

The procedure is now valid.

Note

Most objects will automatically recompile when they are called, so you don't have to recompile them manually. However, the users will be delayed while the object is recompiling, so it is good practice to recompile any invalid objects before they are needed.

Question

You have discovered that the EMP_DETAILS_VIEW is currently invalid.

Select the option that restores the view to valid status.

Options:

  1. With the EMP_DETAILS_VIEW selected, open the Actions drop-down list and select Compile. Then click Go.
  2. With the EMP_DETAILS_VIEW selected, open the Actions drop-down list and select Reorganise. Then click Go.

Answer

With the EMP_DETAILS_VIEW selected, open the Actions drop-down list and select Compile. Then click Go.

You can also compile a procedure using SQL. This code updates the UPDATE_JOBS index in the HR schema. 

ALTER INDEX hr.update_jobs compile

ALTER PROCEDURE s chemaname.procedurename COMPILE;

ALTER INDEX s chemaname.indexname COMPILE;

PL/SQL packages are recompiled in two steps - first the package is compiled, then the body.

ALTER PACKAGE schemaname.packagename COMPILE;
ALTER PACKAGE schemaname.packagename COMPILE BODY;

Question

You have discovered that the GIVERAISE procedure in the HR schema is currently invalid, and want to correct it before it is called by a user.

Select the SQL code that restores the procedure.

Options:

  1. ALTER PROCEDURE hr.giveraise COMPILE;
  2. ALTER PROCEDURE hr.giveraise REBUILD;
  3. COMPILE PROCEDURE hr.giveraise ;
  4. REBUILD PROCEDURE hr.giveraise ;

Answer

You use the command ALTER PROCEDURE hr.giveraise COMPILE; to recompile the procedure, making it valid again.

Option 1 is correct. You use the COMPILE command with the ALTER PROCEDURE command to rebuild the procedure, making it valid again.

Option 2 is incorrect. You use the COMPILE command, not the REBUILD command, with the ALTER PROCEDURE command to recompile the procedure, making it valid again.

Option 3 is incorrect. You use the ALTER PROCEDURE command, with the COMPILE command, to recompile the object, making it valid again.

Option 4 is incorrect. You use the ALTER PROCEDURE command, with the COMPILE command, to recompile the object, making it valid again.

If an index is found to be unusable, it must be rebuilt. This involves recreating the index in a new location, and then deleting the old index.

Suppose the DEPT_LOCATION_IX index has become unusable. You have located the index in Enterprise Manager.

You select Reorganize in the Actions drop-down list, and click Go to recreate the index.

This starts the index recreation process.
The Reorganize Objects: Objects page lists the indexes to be rebuilt. Only HR.DEPT_LOCATION_IX is listed, but you can add other indexes here if necessary.

You click Next to continue.

The Options page allows you to change the rebuild options. By default, the table that is indexed can't be changed while the index is being rebuilt, and the index will be recreated in the same tablespace, but you can change these and other settings.

When you click Next on this page, Enterprise Manager will generate a reorganization script.

The Impact Report page contains information about the script that has been generated. You click Next to continue.

You can add a name and description for the job on the Schedule page. Enterprise Manager will automatically generate a name if you don't add one.

You can also choose to schedule the reorganization to happen later. The default is for the job to run immediately.

You click Next to continue.

The Summary page contains a brief reminder of the choices you have made, and a summary of the SQL script that will run.

You click Submit Job to start the reorganization.

A message displays to confirm that the job has been submitted.

You can also recreate the index in SQL, using the REBUILD command. 

ALTER INDEX hr.dept_location_ix REBUILD

ALTER INDEX schemaname. index name REBUILD [ONLINE] [TABLESPACE tablespace name]

The TABLESPACE option allows you to specify where the new index will be built. If this is not specified, the new index will be built in the same tablespace as the old one. 

ALTER INDEX hr.dept_location_ix
REBUILD TABLESPACE new

The ONLINE clause means that the indexed table will be available for updates while the index is being rebuilt. If this clause is not included, users must wait until the new index has been built before making any changes to the source table. 

ALTER INDEX hr.dept_location_ix
REBUILD ONLINE

Question

The CUST_LNAME_IX index of the OE schema has become unusable. Which of these SQL commands do you use to make it usable again, while allowing other users to make changes to the table it indexes?

Options:

  1. ALTER INDEX oe.cust_lname_ix COMPILE;
  2. ALTER INDEX oe.cust_lname_ix REBUILD;
  3. ALTER INDEX oe.cust_lname_ix REBUILD ONLINE;
  4. ALTERINDEX oe.cust_lname_ix REBUILD TABLESPACE;

Answer

You use the command ALTER INDEX oe.cust_lname_ix REBUILD ONLINE; to keep the indexed table available while the index is being rebuilt.

Option 1 is incorrect. The COMPILE command is used to rebuild other object types, but not indexes.

Option 2 is incorrect. The REBUILD command will rebuild the index, but the indexed table will not be available to other users until the new index is complete.

Option 3 is correct. The ONLINE option of the REBUILD command means that the indexed table is available to users while the new index is being created.

Option 4 is incorrect. The TABLESPACE option of the REBUILD command allows you to specify where the new index will be built, but doesn't keep the indexed table available during the rebuilding process.

Summary

The database administrator should monitor the database constantly. Potential problem areas include resource and device contention, memory allocation, network delays, and problems with the application code. Waiting until problems are reported before trying to see what caused them is known as reactive monitoring. You should focus on proactive monitoring, which is identifying and resolving issues before they affect your users.

There are hundreds of different performance metrics that you can use to monitor your database. The data dictionary provides information about the status of database objects. The dynamic views tell you about the real-time performance of your database. The optimizer, a tool which plots the faster way to answer user queries, gathers information about the size and format of database tables.

One of the most important pieces of information you can gather is the status of database objects. Invalid and unusable objects can be recompiled and rebuilt in Enterprise Manager, or using SQL code.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)