Oracle Essentials Oracle Database 11g (31 page)

The slogan “trust, but verify” could apply to the functions of security and auditing.

Compliance extends that slogan to “trust, verify, and prove it” and describes the tools necessary to provide proof that your data has been used properly.

Compliance is based on the security and audit features described in previous sections. For the most part, compliance is the result of a new element introduced into the corporate landscape—government requirements. In the United States and elsewhere, compliance is being increasingly required by government regulation, so the ability of the Oracle database to make compliance easy is becoming correspondingly important. Compliance is crucial for many organizations, and the people responsible for guaranteeing compliance are not necessarily in the IT department. Consequently, the implementation of security and audit schemes has had to be simplified and coordinated to address compliance needs.

Oracle has two options specifically designed to address compliance challenges—

Oracle Data Vault and Oracle Audit Vault; these are described in the following sections. The related Flashback Data Archive capability, also mentioned below, is

described in greater detail in Chapter 3.

Oracle Database Vault Option

The Oracle Database Vault Option was introduced in 2006 and restricts DBAs and other highly privileged users from accessing application data to which they should not have access. It can also be set up so that applications’ DBAs are not allowed to manipulate the database or access other applications. A security administrator can use the Oracle Database Vault Option to describe the security scheme that the organization wants to implement, and this option automatically implements the schemes using the features described earlier in this chapter.

Compliance

|

151

Key parameters defined in the Oracle Database Vault Option are called
factors
. A factor is essentially a descriptive dimension that will affect security across the entire database. Factors include things such as specific application programs, locations, or times of day. This option comes with more than 40 factors defined, and users can create their own factors.

Factors are used to define access and audit particular security dimensions. You can create rules that limit types of access to a particular factor and rule sets that combine multiple factor rules together. Once you have defined rule sets, you can create application roles based on these sets, as well as command rules that control whether database commands can be executed, based on the outcome of rule evaluation. For example, you could prevent anyone from dropping a particular table unless the command came from a particular location defined by a factor, or specify that new users can be defined only by the combined actions of two administrators.

Rules can also be used to define database
realms
, which consist of a subset of the schemas and roles that an administrator can administer. This ability is essential if an organization uses its Oracle database to service multiple communities. You can define a realm and give an administrator privileges on that realm without compromising data in other schemas. The overall effect of realms is to allow secure delegation of administrative responsibilities.

All of the rule enforcement is audited as part of the the Oracle Database Vault Option, which provides the type of documentation required for complete compliance.
Figure 6-1
illustrates the various components of the Oracle Database Vault Option solution.

User

Command Rules

Realms

Figure 6-1. Oracle Database Vault Option components

152

|

Chapter 6: Oracle Security, Auditing, and Compliance

Oracle Audit Vault Server

The Oracle Audit Vault Server was introduced in 2007 and collects data from audit files in Oracle and in the underlying operating system. It consolidates this data in a secure repository and provides out-of-the-box compliance reporting. Among the reports provided are privileged user accesses, account management, data access, and failed login attempts. Stored in an Oracle data warehouse schema, the data is easily accessible by business intelligence tools such as Oracle’s BI Publisher.

Because the Oracle Audit Vault Server monitors all incoming audit data, it can generate alerts based on IT policies. For example, policies can be defined to trigger alerts for privileged users’ changes and sensitive data access. Oracle databases dating back to Oracle 9
i
Release 2 can be monitored. A software development kit (SDK) is available for building custom audit collectors.

Flashback Data Archive

Flashback technology was introduced in
Chapter 3,
because this capability is based on rollback segments. Although Flashback was initially introduced with Oracle9
i
, Oracle Database 11
g
enables a particular use of Flashback that can help address compliance issues.

Flashback Data Archive gives youthe ability to see all of the changes that occur to a record throughout its lifetime. This type of history tracking can provide the key information required to demonstrate compliance, as well as to track the source of errors in compliance or usage.

Compliance

|

153

Chapter 7

CHAPTER 7

Oracle Performance

7

As this book illustrates, the Oracle database has a wide range of features. As you gain experience with Oracle, you’ll reap more of the benefits it has to offer. One area on which you will eventually focus is performance tuning, since you will inevitably be forced to wring additional performance from your Oracle database in the face of increasing demands. This chapter gives you the basics you’ll need to understand as you address performance.

Oracle database performance tuning has been extensively documented in the Oracle community. There are numerous books that provide detailed and excellent information; many of these are listed in
Appendix B.
This book is focused more on the concepts of the Oracle database, so we won’t delve too deeply into specific tuning recommendations. Instead, we’ll touch on the importance of tuning and discuss some basic notions of how Oracle uses resources. Here, we’re simply laying a foundation for understanding Oracle performance. This understanding will help you implement the tuning procedures most suited for your own particular implementation. Where appropriate, we’ll provide some basic guidance on how the latest Oracle features help you manage performance.

Certainly, Oracle provides more and better automated tuning options in the current release than it did when we wrote earlier editions of this book. However, getting optimal performance is about more than tuning. There is no substitute for getting your hardware platform properly configured with appropriate CPUs, memory, and especially storage. Good database design is also critical for achieving optimal performance, based on how your lines of business use the system.

Performance Tuning Basics

Performance is one of the trickiest aspects in the operation of your database since so many factors can be involved. There is the database, to be sure. But there are also platform deployment strategies to consider. Today, the infrastructure likely resides
154

across multiple platforms, including database servers and applications servers. There is network and interconnect bandwidth to consider and varying complexity in use among your users.

One of the curious aspects of performance is that “good performance” is defined by its absence rather than by its presence. Youcan recognize bad performance easily, but good performance is usually defined as simply the absence of bad performance.

Performance is simultaneously a very simple topic—any novice user can implicitly understand it—and an extremely complex topic that can strain the ingenuity of the most proficient database administrator.

Before getting into a specific discussion of Oracle performance, it makes sense to define a basic methodology for investigating performance problems.

There are three basic steps to understanding how to address performance issues with your Oracle database:

1. Define performance and performance problems.

2. Check the performance of the Oracle server software.

3. Check the overall performance of the server machine.

Defining Performance and Performance Problems

The first step in performance tuning is to determine if there actually
is
a performance problem. In the previous section, we mentioned the concept of poor performance and how users often are the first to recognize it. But what exactly is poor performance?

Poor performance is inevitably the result of disappointment—a user feels that the system is not performing as expected. Consequently, you must first evaluate how real these expectations are in the first place.

If expectations are realistic—for example, a scenario where performance has degraded from a previous level and the business is impacted—you then need to identify which of the system’s components are causing the problems. You must refine a general statement like “the system is too slow” to identify which types of operations are too slow, what constitutes “too slow,” and when these operations are slowing down. For example, the problem may occur only on specific transactions and at specific times, or all transactions and reports may be performing below the users’

expectations.

Once you’ve defined the performance expected from your system, you can begin to try to determine where your performance problem lies. Performance problems occur when there is a greater demand for a particular resource than the resources available to service that demand, and the system slows down while applications wait to share the resource.

Performance Tuning Basics

|

155

Oracle Server Performance

The first place you’ll likely begin looking for resource bottlenecks is in the Oracle database software using Oracle Enterprise Manager (introduced in
Chapter 5)
to identify less than optimal use of Oracle’s internal resources. Bottlenecks within your database result in sessions waiting unnecessarily, and performance tuning is aimed at removing these bottlenecks.

Oracle’s dynamic performance views provide insight into bottlenecks within your Oracle database. Prior to the introduction of Oracle’s Automatic Workload Repository (AWR), the Automatic Database Diagnostics Monitor (ADDM), and Oracle Enterprise Manager Grid Control in Oracle Database 10
g
, querying the performance views often was the first step database administrators performed in determining bottlenecks. All of these performance views have names that begin with V$, and, from Oracle9
i
on, there are also global views (for all nodes in a Real Application Clusters or RAC database) that begin with GV$. Two views, in particular, identify the sources of these waits; these are invaluable for guiding your analysis:
V$SYSTEM_EVENT

Provides aggregated, systemwide information about the resources for which the whole instance is waiting

V$SESSION_EVENT

Provides cumulative list of events waited for in each session
V$SESSION_WAIT

Provides detailed, session-specific information about the resources for which individual sessions are currently waiting or last waited for
V$SESSION

Provides session information for each current session including event currently or last waited for

You can use these views to pinpoint the resources that are causing the most waits.

Focusing on the resources causing the most waiting can provide large performance improvements.

Oracle Database 10
g
and newer releases provide an enhanced wait model that makes it easier to determine exactly who is waiting for what resource at what time.

You may find that your problem has a simple source, such as a lower-than-expected database buffer cache hit ratio. Since the cache is not working at its optimal level, youcould simply increase the initialization parameter DB_BLOCK_BUFFERS to increase the size of the cache and possibly improve the hit ratio. Youcan monitor the performance of the buffer cache hit ratio in V$METRICNAME.

156

|

Chapter 7: Oracle Performance

Other situations may not be quite so clear cut, using the approach of investigating parameters exposed by views. For example, youcould find that it takes a relatively long time to fetch database rows from the disk. This situation may be caused by contention on the database server’s disks and could be caused by less than optimal placement of Oracle files on disk or by other applications on the server.

AWR, ADDM, and Enterprise Manager

A much better approach today is to use Enterprise Manager (also known as Grid Control for RAC implementations) as the starting point for performance monitoring and management. The Automatic Workload Repository (AWR) captures and stores information about resource utilization by Oracle workloads. By default, statistics are captured every 30 minutes and are stored for 7 days. These statistics are accessible through views, but Enterprise Manager provides a much simpler-to-use interface.