whether a query performs a full table scan, you must obtain the query's execution plan. When an execution plan indicates that a full table scan is being performed, consider creating an index that the query can use.

NEW TERM
An execution plan is the method that the Oracle RDBMS uses to perform the tasks for an SQL statement. For example, if a table has no indexes, the execution plan to query the table will require that every row in the table be read; this is known as a full table scan.

Be alert to the possibility of overindexing a table. Remember that Oracle automatically maintains a table's indexes whenever the contents of the table change because of an INSERT, UPDATE, or DELETE statement. Your goal should be to optimize the most frequent queries without forcing Oracle to maintain an inordinate number of indexes.

Tuning an Oracle Database

Oracle performance-tuning efforts can be classified in three ways:

• Efforts that are application independent. For example, you don't have to modify or rebuild any software to tune the System Global Area.
• Efforts that are mostly application independent. For instance, by inspecting an application's queries, you may identify indexes that you should create to improve query performance.
• Efforts that affect the application software. You might discover that the underlying database design is inefficient or that the application software makes unnecessary SQL requests. Making such changes is very expensive in terms of budget and schedule. Trying to restructure a system after it's been constructed is very difficult!

Your best bet is to focus on tuning efforts that have a minimal effect on the existing application.

Increasing the Size of the SGA
Among its other uses, the SGA functions as Oracle's cache. If you increase the number of data block buffers in the SGA, you increase the higher probability that an SQL statement will find the block that it needs in memory—thereby reducing the number of disk reads it needs to locate a block. However, be sure that the SGA isn't so large that it's swapped out of memory by the operating system. The number of database buffers used by an Oracle database is one of the initialization parameters that are used when an Oracle database is started. The new value for an initialization parameter doesn't take effect until the next time the database is started.

Reducing Disk Contention
To tune disk I/O, you must first determine if disk I/O requests are balanced across all the server's disk drives. Use operating system commands and utilities to identify the average

number of I/O requests serviced by each disk drive. Your objectives should include the following:

• Lower the overall number of disk I/O requests by adding more memory to the server. By adding more RAM, you'll be able to increase the size of the Oracle SGA and thereby reduce overall disk I/O, assuming no other changes are made to the database.
• Lower the average number of I/O requests per drive by adding disk drives to the server. You can then relocate a tablespace's datafiles to a new disk drive by using the ALTER TABLESPACE statement with the RENAME option.
• Lower the average number of I/O requests per disk controller by adding another disk controller to the server.
• Balance disk I/O requests so that each drive is servicing the same number of I/O requests. This tuning method requires both analysis and experimentation to determine the optimal distribution of Oracle datafiles and redo log files.

Summary

This lesson covers the following principles regarding the management of Oracle users, database roles and privileges, and performance tuning:

• You can manage users, roles, and privileges with the point-and-click interface offered by Personal Oracle Navigator and Security Manager. You also can use SQL commands in SQL Worksheet or SQL*Plus to create and modify users, roles, and privileges.
• A profile is a set of system-level quotas that can be levied on a user or role. These quotas include the amount of idle time and the number of logical reads performed for a single query.
• To maximize network performance before implementing an application, plan to use stored procedures and functions in the database to reduce the number of SQL requests on the network, and minimize superfluous SQL requests from the client application.
• To improve Oracle database performance, tune the SGA, reduce data segment fragmentation, and add non-unique indexes where appropriate.
• To improve the server's performance, add memory so that more data block buffers can be added to the SGA; also add more disk drives to reduce the average number of I/O requests per disk drive.
• Oracle uses two optimizers: the rule-based optimizer and the cost-based optimizer.
• The rule-based optimizer looks at a query's syntax and the existence of indexes to determine the most efficient method for retrieving the qualified rows.

• The cost-based optimizer looks at a query's syntax, the existence of indexes, and the table and index statistics to determine the most efficient method for retrieving the qualified rows.
• Use the EXPLAIN_PLAN statement to determine a query's execution plan.
• You can generate a trace file for a specific Oracle session by executing ALTER SESSION SET SQL_TRACE TRUE when the Oracle session begins.
• tkprof is a utility that converts a binary Oracle trace file to a listing file containing each SQL statement executed during an Oracle session.

What Comes Next?

On Day 20, "Using Oracle Database Designer and Oracle Designer/2000 in Application Development," you will learn about the use two database design tools. Oracle Database Designer is used to develop a logical data model and automatically implement the physical database by generating the SQL DDL statements that create tables, indexes, and other objects. Oracle Designer/2000 is a comprehensive, multi-user design tool that enables you to design and create databases and applications for a large, complex enterprise.

Q&A

Q Can a database role be granted to another database role?
A Yes. You can grant a role to another role or to an Oracle user. However, you should avoid creating an unnecessarily complex set of roles; the use of roles should simplify the management of database privileges, not make it more complex.

Workshop

The purpose of the Workshop is to allow you to test your knowledge of the material discussed in the lesson. See if you can correctly answer the questions in the quiz and complete the exercise before you continue with tomorrow's lesson.

Quiz

1. True or false? A database role can be granted to both users and other database roles.
   
2. True or false? Adding an index to a table always improves the performance of
   an application.
   
3. True or false? Any Oracle user can create a public synonym as long as the synonym points to a table that is owned by that user.