Oracle Essentials Oracle Database 11g (25 page)

PLAN, followed by the keyword FOR and the SQL statement whose execution plan you want to view, the Oracle cost-based optimizer returns a description of the execution plan it will use for the SQL statement and inserts this description into a database table. You can subsequently run a query on that table to get the execution plan, as

shown in SQL*Plus in Figure 4-5.

The execution plan is presented as a series of rows in the table, one for each step taken by Oracle in the process of executing the SQL statement. The optimizer also includes some of the information related to its decisions, such as the overall cost of each step and some of the statistics that it used to make its decisions.

The optimizer writes all of this information to a table in the database. By default, the optimizer uses a table called PLAN_TABLE; make sure the table exists before you use EXPLAIN PLAN. (The
utlxplan.sql
script included with your Oracle database
118

|

Chapter 4: Oracle Data Structures

SQL> EXPLAIN PLAN FOR

2 SELECT DNAME, ENAME FROM EMP, DEPT

3 WHERE EMP.DEPTNO = DEPT.DEPTNO

4 ORDER BY DNAME;

Explained.

SQL> SELECT OBJECT_NAME, OPERATION, OPTIONS FROM PLAN_TABLE ORDER BY ID; OBJECT_NAME OPERATION OPTIONS

------------------------------ ------------------------------ --------------------------

SELECT STATEMENT

SORT ORDER BY

NESTED LOOPS

EMP TABLE ACCESS FULL

DEPT TABLE ACCESS BY INDEX ROWID

SYS_C004911 INDEX UNIQUE SCAN

6 rows selected.

Figure 4-5. Results of a simple EXPLAIN PLAN statement in SQL*Plus
creates the default PLAN_TABLE table.) You can specify that EXPLAIN PLAN uses a table other than PLAN_TABLE in the syntax of the statement. For more information about the use of EXPLAIN PLAN, please refer to your Oracle documentation.

There are times when youwant to examine the execution plan for a single statement. In such cases, the EXPLAIN PLAN syntax is appropriate. There are other times when youwant to look at the plans for a group of SQL statements. For these situations, you can set up a trace for the statements you want to examine and then use the second utility, TKPROF, to give you the results of the trace in a more readable format in a separate file. At other times, youmight also use Oracle’s SQL Trace facility to generate a file containing the SQL generated when using TKPROF in tuning applications.

You must use the EXPLAIN keyword when you start TKPROF, as this will instruct the utility to execute an EXPLAIN PLAN statement for each SQL statement in the trace file. Youcan also specify how the results delivered by TKPROF are sorted. For instance, youcan have the SQL statements sorted on the basis of the physical I/Os they used; the elapsed time spent on parsing, executing, or fetching the rows; or the total number of rows affected.

The TKPROF utility uses a trace file as its raw material. Trace files are created for individual sessions. You can start collecting a trace file either by running the target application with a switch (if it’s written with an Oracle product such as Developer) or by explicitly turning it on with an EXEC SQL call or an ALTER SESSION SQL

statement in an application written with a 3GL. The trace process, as youcan probably guess, can significantly affect the performance of an application, so you should turn it on only when you have some specific diagnostic work to do.

Understanding the Execution Plan

|

119

You can also view the execution plan through Enterprise Manager for the SQL statements that use the most resources. Tuning your SQL statements isn’t a trivial task, but with the EXPLAIN PLAN and TKPROF utilities you can get to the bottom of the decisions made by the cost-based optimizer. It takes a bit of work to understand exactly how to read an execution plan, but it’s better to have access to this type of information than not. In large-scale system-development projects, it’s quite common for developers to submit EXPLAIN PLANs for the SQL they’re writing to a DBA as a formal step toward completing a form or report. While time-consuming, this is the best way to ensure that your SQL is tuned before going into production.

SQL Advisors

Oracle Database 10
g
added a tool called the SQL Tuning Advisor. This tool performs advanced optimization analysis on selected SQL statements, using workloads that have been automatically collected into the Automatic Workload Repository or that you have specified yourself. Once the optimization is done, the SQL Tuning Advisor makes recommendations, which could include updating statistics, adding indexes, or creating a
SQL profile
. This profile is stored in the database and is used as the optimization plan for future executions of the statement, which allows you to

“fix” errant SQL plans without having to touch the underlying SQL.

The tool is often used along with the SQL Access Advisor since that tool provides advice on materialized views and indexes. Oracle Database 11
g
introduces a SQL

Advisor tool that combines functions of the SQL Tuning Advisor and the SQL

Access Advisor (and now includes a new Partition Advisor). The Partition Advisor component advises on how to partition tables, materialized views, and indexes in order to improve SQL performance.

Data Dictionary Tables

The main purpose of the Oracle data dictionary is to store data that describes the structure of the objects in the Oracle database. Because of this purpose, there are many views in the Oracle data dictionary that provide information about the attributes and composition of the data structures within the database.

All of the views listed in this section actually have three varieties, which are identified by their prefixes:

DBA_

Includes all the objects in the database. A user must have DBA privileges to use this view.

USER_

Includes only the objects in the user’s own database schema.

120

|

Chapter 4: Oracle Data Structures

ALL_

Includes all the objects in the database to which a particular user has access. If a user has been granted rights to objects in another user’s schema, these objects will appear in this view.

This means that, for instance, there are three views that relate to tables: DBA_

TABLES, USER_TABLES, and ALL_TABLES.

Some of the more common views that directly relate to the data structures are

described in Table 4-2.

Table 4-2. Data dictionary views about data structures

Data dictionary view

Type of information

ALL_TABLES

Information about the object and relational tables

TABLES

Information about the relational tables

TAB_COMMENTS

Comments about the table structures

TAB_HISTOGRAMS

Statistics about the use of tables

TAB_PARTITIONS

Information about the partitions in a partitioned table

TAB_PRIVS*

Different views detailing all the privileges on a table, the privileges granted by the user, and the privileges granted to the user

TAB_COLUMNS

Information about the columns in tables and views

COL_COMMENTS

Comments about individual columns

COL_PRIVS*

Different views detailing all the privileges on a column, the privileges granted by the user, and the privileges granted to the user

LOBS

Information about large object (LOB) datatype columns

VIEWS

Information about views

INDEXES

Information about the indexes on tables

IND_COLUMNS

Information about the columns in each index

IND_PARTITIONS

Information about each partition in a partitioned index

PART_*

Different views detailing the composition and usage patterns for partitioned tables and indexes CONS_COLUMNS

Information about the columns in each constraint

CONSTRAINTS

Information about constraints on tables

SEQUENCES

Information about sequence objects

SYNONYMS

Information about synonyms

TAB_COL_STATISTICS

Statistics used by the cost-based analyzer

TRIGGERS

Information about the triggers on tables

TRIGGER_COLS

Information about the columns in triggers

Data Dictionary Tables

|

121

Chapter 5

CHAPTER 5

Managing Oracle

5

Many Oracle users and developers are not actively aware of the system and database management activities that go on around them. But effective management is vital to providing a reliable, available, and secure platform that delivers optimal performance. This chapter focuses on how you can manage Oracle to ensure these virtues for your environment.

Much of the management responsibility usually falls upon the database administrator.

Users and developers of Oracle also need to be aware of some of the techniques described here. The DBA is typically responsible for the following management tasks:

• Installing and upgrading the database and options

• Creating tables and indexes

• Creating and managing tablespaces

• Managing control files, online redo logs, archived redo logs, job queues, and server processes

• Creating, monitoring, and tuning data-loading procedures

• Adding users and groups and implementing security procedures

• Implementing backup, recovery, information lifecycle management, and high availability plans

• Monitoring database performance and exceptions

• Reorganizing and tuning the database

• Troubleshooting database problems

• Coordinating with Oracle Worldwide Customer Support Services Particularly in smaller companies, DBAs are also often called upon to take part in database schema design and security planning. DBAs in large enterprises may also help set up replication strategies, disaster and high-availability strategies, hierarchical storage management procedures, and the linking of database event monitoring (e.g., specific database tasks and status) into enterprise network monitors.

122

Oracle’s feature list has grown with each database release. Yet managing Oracle can be much less labor-intensive today than it was in the past. While database releases highlighted in early editions of this book described the novelty of an easier-to-use management interface, producing better versions of Oracle Enterprise Manager (EM) was only part of the effort to simplify management underway within Oracle Server Development. The database itself has now become more self-tuning and self-managing.

Initially, this effort was focused mostly on better management of single instances of the Oracle database. Oracle Database 10
g
expanded its capabilities with a focus on
grid computing
. Grid computing highlighted the need for effective management of scores of computers and database instances.

Manageability of a grid must take into account disk virtualization, resource pooling, provisioning of computer resources, dynamic workload management, and dynamic control of changing grid components. Oracle’s grid initiative resulted in many significant changes in managing the database geared toward significantly reducing this complexity. While targeted at simplifying grid management, most of these improvements also provide great impact in simplifying management of more traditional Oracle database implementations.

As a consequence of the grid initiative and self-tuning and self-managing initiatives, readers of early editions of this book will find a large number of management changes in this chapter and in others throughout this book.

All of the tasks we’ve just described come under the heading of managing the database. Many of the provisioning duties, including installation, initial configuration, and cloning, are discussed in
Chapter 3.
Security issues are discussed in
Chapter 6.

This chapter explores the following aspects of managing Oracle:

• Using Oracle Enterprise Manager, which provides an easy-to-use interface and underlying framework for many database-management tasks, including new database capabilities

• Managing database fragmentation, which can affect database performance

• Performing backup and recovery operations and information lifecycle management, which are the foundation of database integrity protection

• Working with Oracle Support

In subsequent chapters, we’ll cover other related topics in more depth, including security, performance, and high availability. You will need an understanding of all of these areas as youplan and implement effective management strategies for your Oracle database environment.

Managing Oracle