 |
|
|
|
 |
|
Other Database Structures |
|
|
So far, you’ve seen that the basic building block for Oracle database systems is the table. Most
database applications will have dozens, even hundreds, of tables used to store user data. You’ve
also looked at several database building blocks besides tables. Chapter 1, “Oracle Architecture and
Storage,” introduced several database structures, such as tablespaces, that you’ll consider a little
more deeply here. Chapter 5, “Oracle Security,” introduced the security facilities that protect the
database and ensure that only authorized individuals are able to change appropriate parts of the
database. In Chapter 6, “The Oracle Data Dictionary,” we introduced the Oracle data dictionary
that uses hundreds of system tables, indexes, and other structures to manage security, users,
application data tables, and everything else within the database. Chapter 10, “Indexes,” described
various indexing structures available to the database designer to speed access to specific table data
and prevent duplicate rows in our tables. Chapter 11, “Constraints,” showed the ability of the
database to hold declarative business rules and enforce them to ensure integrity of table data.
In this chapter, you’ll examine many other components of the Oracle database that designers,
developers, and administrators may need to use to achieve application design requirements. Some
of these components are merely introduced here and then covered in depth in another chapter.
Others are treated in more depth directly.
Tablespaces
Tablespaces, as a logical grouping of physical files that provide a place for storing objects such as
tables and indexes, were introduced in Chapter 1, “Oracle Architecture and Storage.” At that point
we mentioned that tablespaces can be either online or offline (for maintenance) and that a tablespace
can be made read-only. Read-only tablespaces protect the data in the tablespace from modification
but also eliminate the need for repeatedly backing up the data files of that tablespace. A single
backup after making a tablespace read-only will be good for as long as the tablespace is left in the
read-only state. We also introduced the concept of a transportable tablespace as a means of moving
or copying all of the data in a tablespace to another Oracle database.In this chapter we’ll extend the discussion of tablespaces by introducing two additional types of
tablespaces that can be important for the developer to understand—temporary tablespaces and undo
tablespaces.
Temporary Tablespaces
Adeveloper will not generally need to create or even specify temporary tablespaces, but their configuration
can have performance impacts on some aspects of the application. By understanding how and when
temporary tablespaces are used by Oracle, you will be better able to diagnose some performance problems
or, better still, design and build the application to avoid them.
Some SQL statements require data to be sorted. SELECT statements with ORDER BY or GROUP BY clauses
frequently require sorts. The optimizer may choose to perform joins between multiple tables via a sort
operation. Index builds are another case where the column data from the table has to be put into sorted
order for the index.
When an SQL statement requires a small sort, Oracle will attempt to perform that sort within memory.
Remember in Chapter 1 that we discussed the PGA memory allocation that is used for private use by a
process. Prior to Oracle9i, database administrators had to predefine a maximum amount of memory to
be used by processes for sort or hash operations. Any sort larger than that defined maximum could not
be completed in memory and therefore had to be sorted with the assistance of some temporary disk
storage. With Oracle9i and Oracle 10g the concept doesn’t change but the specification becomes both
easier and more dynamic. Rather than specifying the maximum memory available to each user process,
the DBA can specify the maximum available to all users and let Oracle sort it out among them.
In either scenario there will be times when the amount of data that needs to be sorted is larger than the
memory Oracle is able to allocate to the sorting process. When this occurs Oracle will assign the process
one or more extents (disk allocations) from the user’s assigned temporary tablespace. As data is sorted
and the amount of sort memory is reached, the intermediate sort results are written to the temporary
tablespace and the process begins sorting the next set of data. When all of the data has been sorted and
stored into temporary extents, the sort process must retrieve it with a simple ordered merge of values
from the extents to create the final sorted result set.
Each user, among other attributes, is assigned a temporary tablespace to be used for any required disk
sort operations. The temporary tablespace is shared by all of the users who have been assigned it. The
database may have more than one temporary tablespace created, and users can be balanced across
the available temporary tablespaces to even the workload.
Oracle 10g simplifies this potential allocation problem greatly by allowing the DBA to assign various
temporary tablespaces to a new construct, the TABLESPACE GROUP. Users may then be assigned the
TABLESPACE GROUP as their sort location. This will better distribute sort operations across all available
temporary tablespaces.The implications of this process to the programmer are twofold. Whenever possible, try to avoid performing
large sort operations within the application. Any operation that needs to work with a large amount of data is
going to require more extensive resources than a smaller amount of data. Sort operations can sometimes be
avoided through the use of indexes (see Chapter 10, “Indexes”), partitioning (examined in the following
section), materialized views (introduced in this chapter and explored more completely in Chapter 26,
“Business Intelligence Query”), global temporary tables (discussed later in this chapter), and alternative SQL
coding techniques.
242
Chapter 12
But sometimes large sort operations cannot be avoided. In these cases it is important to verify that users
will have adequate temporary tablespace space available for their sorts to complete. If certain users are
more likely to perform large, disk-based sorts, then it will be wise to assign users temporary space with
this in mind. If a particular report is going to require much more temporary space than average, it may
be advisable to perform that report from a specific user account that has been assigned a large temporary
tablespace.
Typically this assignment will be the DBA’s responsibility, but a developer can help avoid eventual
problems by identifying necessary large sorts early in the development and testing phases and consulting
with the DBA to structure assignments accordingly.SapereOnLine |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
