Oracle BEST Practices II

1- Put your Database into ARCHIVELOG Mode
2- Use DBMS_STATS for statistic collection
3- Using Recovery Manager(RMAN) for Backup and Recovery needs
4- Resumable Statements and Space Allocation
5- Maintenance of Global Partitioned Indexes
6- Default Tablespaces
7- PL/SQL Bulk Operations
8- Locking Issues
9- Oracle System Event Triggers
10- Autonomous Transactions
11- Defining Application Services for Oracle Database 10g
12- Cost Based Optimizer Best Practices and Advised Strategy

3- Using Recovery Manager(RMAN) for Backup and Recovery needs

The most important job of the Oracle DBA is to ensure that the databases in their care are protected.

RMAN is a utility that backs up, restores, and recovers Oracle databases. Its free!! and automate
all backup process very easy.

You can backup with out stop datbase(ARCHIVELOG mode),which will take a consistent backup even when your DB is up and running.

With recovery manager you can duplicate/clone database from a backup or from an active database.It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

Is a must for save from worst disaster.

4- Resumable Statements and Space Allocation

Oracle provides a means for suspending, and later resuming, the execution of large database operations in the event of space allocation failures. This enables an administrator to take corrective action, instead of the Oracle database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes.

Using the ALTER SESSION statement in client enables resumable semantics for the session

Resumable space allocation is suspended when one of the following conditions occur:

– Out of space condition
– Maximum extents reached condition
– Space quota exceeded condition

For nonresumable space allocation, these conditions result in errors and the statement is rolled back. Suspending a statement automatically results in suspending the transaction. Thus all transactional resources are held through a statement suspend and resume. When the error condition disappears (for example, as a result of user intervention or perhaps sort space released by other queries), the suspended statement automatically resumes execution.

Resumable operations include:

– Queries
– DML Statements
– SQL*Loader operations
– Import operations
– DDL statements

When resumable space allocation is enabled and operation suspends with an out of space error details of the error are written to DBA_RESUMABLE and DBA can optionally be alerted. DBA can either: fix the error and abort the operation.
5- Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
ADD(HASH), COALESCE(HASH) ,DROP ,EXCHANGE ,MERGE , MOVE, SPLIT, TRUNCATE

These indexes can be maintained by appending the clause UPDATE INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:

– The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
– The index doesn’t have to be rebuilt after the operation and updates to the global index are logged.

Examples:
ALTER TABLE DROP PARTITION P1 UPDATE INDEXES;
ALTER TABLE table1 DROP PARTITION table1_p1 UPDATE GLOBAL INDEXES;

UPDATE GLOBAL INDEXES can be used with the following partition DDL statements: ADD, SPLIT, DROP, MERGE, MOVE, EXCHANGE, TRUNCATE, COALESCE
6- Default Tablespaces

Specify default temporary tablespaces when you create a database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement. If you drop all default temporary tablespaces, then the SYSTEM tablespace is used as the default temporary tablespace. You can create bigfile temporary tablespaces. A bigfile temporary tablespaces uses tempfiles instead of datafiles. Also you cannot make a default temporary tablespace permanent or take it offline.
In Oracle 9.0.1 and above a default temporary tablespace can be defined: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ;

Default Permanent Tablespace(Oracle 10>) can be defined: ALTER DATABASE DEFAULT TABLESPACE;

7- PL/SQL Bulk Operations

This technique can be very fast, but also very memory-intensive.

PL/SQL sends SQL statements such as DML and queries to the SQL engine for execution, and SQL returns the result data to PL/SQL. You can minimize the performance overhead of this communication between PL/SQL and SQL by using the PL/SQL language features known collectively as bulk SQL. The FORALL statement sends INSERT, UPDATE, or DELETE statements in batches, rather than one at a time. The BULK COLLECT clause brings back batches of results from SQL. If the DML statement affects four or more database rows, the use of bulk SQL can improve performance considerably.

The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:

– in-bind: When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
– out-bind: When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause
of an INSERT, UPDATE, or DELETE statement.
– define: When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.

Bulk SQL uses PL/SQL collections, such as varrays or nested tables, to pass large amounts of data back and forth in a single operation. This process is known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. Queries can pass back any number of results, without requiring a FETCH statement for each row.

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s