0582-0584

Previous Table of Contents Next

Page 582

To check the amount of free space available and the level of fragmentation on a tablespace, issue the following query:

 select tablespace_name, sum(bytes), max(bytes), count(tablespace_name) from dba_free_space group by tablespace_name order by tablespace_name / 

The results of this query tell how much free space is available within a tablespace (sum), what the size of the largest contiguous extent size is (max), and how many extents of free space make up the tablespace (count). If the number of contiguous blocks is greater than 10 to 15, you should defragment the tablespace. The steps are

  1. Export data from all the tables in the tablespace with the exp utility. For indexes, capture the SQL required to re-create them.
  2. Drop all the objects in the tablespace with the drop table and/or drop index commands.
  3. Drop the tablespace with the drop tablespace command.
  4. Re-create the tablespace with the create tablespace command.
  5. Import the data back into the tablespace with the imp utility. The import re-creates the database tables. Manually re-create indexes by using their SQL scripts.

Just as chained and migrated rows reduce database performance, fragmentation reduces performance by causing the disk drive head to move excessively when it queries a database table. Obviously, fragmented tablespaces should be defragmented whenever possible. To minimize fragmentation, create and drop new tables and indexes ( especially those used as temporary or development tables) only on restricted tablespaces.

Partitioned Tables

This chapter has discussed striping tables, and storing tables and indexes on separate devices. Tables can be forced to take extents to stripe data across multiple devices. Oracle8 provides an option that allows you to partition tables, grouping sets of data in a table together and placing these groups within separate tablespaces and/or devices.

For example, suppose that you needed to create a table called mailing_list. Assuming that this is going to be a very large and heavily accessed table, you should partition the table across two tablespaces, each tablespace located on a separate device. The two tablespaces are mail_ts1 and mail_ts2:

 create table mailing_list (last_name varchar2(30) not null,  first_name varchar2(15) not null,  address varchar2(40) not null,  city varchar2(20) not null,  state varchar2(2) not null,  zip varchar2(5) not null) 

Page 583

 partition by range (last_name)   (partition mail_part1 values less than (`N') tablespace mail_ts1,    partition mail_part2 values less than (maxvalue) tablespace mail_ts2); Table created. 

Views

Views are SQL statements that are treated as virtual tables. This enables you to hide the details of complex table joins and filters so that the code does not have to be used in every statement that performs a similar operation. It is important, however, to keep in mind that the statement is not issued until a SQL statement is executed against the view.

The best performance tuning that can be done on a view is preventative in nature. Run each view that you create through an EXPLAIN PLAN and analyze it for performance. Except in rare circumstances, views that are inefficient and take a long time to return data should not be used. If a view that previously performed acceptably suddenly begins to act sluggish , you should perform another EXPLAIN PLAN or execute SQL*Trace against a query on the view.

Usually, views fail to perform as expected when changes are made ”such as adding or removing indexes ”or when the query is not properly optimized for a large amount of data.

Triggers

Another new feature of Oracle7 that presents a tuning challenge is a database trigger. If you have worked with SQL*Forms/Oracle*Forms or other event-driven processing, you are familiar with triggers. If you have not, they can be difficult to understand.

A trigger occurs when a certain event happens ”such as before or after a database table is modified ”at which time a section of PL/SQL code is executed. If the SQL code contained within the PL/SQL segment is tuned ”based on an EXPLAIN PLAN ”triggers work well. Triggers can cause unexpected problems, however. This is generally the case when they are used by an inexperienced developer or have not been tested adequately.

A common problem with triggers is an infinite loop. One trigger activates another trigger, which activates another trigger, and so forth, until one of the triggers causes a change that sets off the original trigger ”starting the process again. These errors are difficult to find and can create phantom problems. Adequate research and testing before implementing new triggers goes a long way toward heading off trigger problems.

Database Locking

Database locking is important to the DBA, because locks can slow a database. This is a frustrating performance problem to locate because it is often not obvious.

Locks within the database prevent database users in a multiuser environment from changing the same data simultaneously . Database locks ensure the integrity of the data within a database by enforcing concurrency and consistency. Concurrency means that the database ensures that

Page 584

users can read data from a database block without worrying whether the data is currently being written to the database block; a user writing data must wait for the write operations that precede it to complete. Consistency means that a database query returns data exactly as it appeared when the query was originally executed; changes made after the query was issued are not returned.

Types and Classes of Locks

An Oracle7 database has two types of locks: data dictionary locks (DDLs) and data manipulation locks (DMLs). A DDL ensures that the layout of a database object ”its definition ”does not change while it is used within a database query. A DML protects data that multiple users are trying to access simultaneously.

All transactions fall into one of two categories: exclusive or shared. Exclusive transactions do not enable other users to access the data. Shared transactions enable data to be shared with other users, although they cannot change it. Locks are released whenever a commit or a rollback occurs.

Whenever a SQL statement accesses data within a table, a DDL is acquired on the table. The lock prevents the DBA from making changes to a table while it is in use.

DML locks, on the other, are employed against database tables. The five types of DML locks are


RS Locks a specific row in a database table in shared mode, enabling other database queries to access the information ”for example, a SELECT...FOR UPDATE OF... operation
RX Locks a specific row in a database table in exclusive mode, restricting access to the row to the database session that acquired the lock ”for example, an UPDATE operation
S Locks a table in shared mode and prohibits activities other than queries against the table ”for example, a LOCK TABLE...IN SHARE MODE operation
SRX Locks a table in shared mode and provides row-level locks as required to modify and update data ”for example, a LOCK TABLE...IN SHARE ROW EXCLUSIVE MODE operation
X Locks an entire table, preventing access to the table by any session except the current one ”for example, a LOCK TABLE...IN EXCLUSIVE MODE operation

Unresolved Locking Issues

A common database locking situation is unresolved locking, also called a deadlock. In a deadlock, two database operations wait for each other to release a lock.

Oracle7 is designed to detect deadlocks, but it is not always successful. You might encounter transactions that have acquired locks and are waiting on each other to free their locks so that

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net