Oracle Best Practices Part 2

In this series Part 1 I mentioned below best practices;

1- Put your Database into ARCHIVELOG Mode
2- Comment on Tables and Columns similar to your PL/SQL or SQL Source Codes
3- Use DBMS_STATS for statistic collection
4- Using Recovery Manager(RMAN) for Backup and Recovery needs

5- 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.

A statement runs in a resumable mode only when the client explicitly enables resumable semantics for the session using the ALTER SESSION statement. 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
* abort the operation

If a further error is detected, operation will suspend again. Here is a demostration of this feature;

Code listing 67a : Resumable Statements Example

6- 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

7- Default Tablespaces

Default Temporary Tablespace

When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.

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

In Oracle 10.1 and above a default permanent tablespace can be defined;

ALTER DATABASE DEFAULT TABLESPACE ;

8- PL/SQL Bulk Operations

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.

This technique can be very fast, but also very memory-intensive. PL/SQL Bulk Collect was introduced in Oracle 8.1.5, returns result set in single operation can be used with; SELECT INTO – FETCH INTO – RETURNING INTO Bulk collect performance improves as optimum result set size is approached. Thereafter bulk collect performance degrades as result set grows. In Oracle 8.1.6 and above the number of rows returned by FETCH INTO can be restricted using the LIMIT clause;
FETCH c1 BULK COLLECT INTO l_c3 LIMIT 1000;

FORALL Introduced in Oracle 8.1.5, sends INSERT, UPDATE or DELETE statements in batches. This option can only repeat single DML statement and works with PL/SQL collections including TABLE, VARRAY, NESTED TABLE etc. Much faster than equivalent for-loop;

Code listing 67b : PL/SQL Bulk Operations Examples

9- Locking Issues

Oracle automatically implements row level locking as needed, we use FOR UPDATE NOWAIT to acquire a lock on rows if needed. But this pessimistic method decreases concurrency, need to be carefully used. Also this statement either locks all the selected rows or the control is returned without acquiring any lock(i.e. even on rows which are available for locking) after throwing an exception. To illustrate, we open two sessions. In the first session, we lock the row with deptno as 10 using FOR UPDATE NOWAIT.


-- session 1
SELECT * FROM dept
WHERE deptno = 10
FOR UPDATE ;

DEPTNO DNAME
———- ——————————
10 Administration

In the second session, we try to lock two rows (deptno 10 and 20) from the table dept using FOR UPDATE NOWAIT. An exception is thrown after executing the following statement because one of the row (i.e. deptno 10) out of the selected list is already locked by session 1.


-- session 2
SELECT * FROM dept
WHERE deptno IN (10,20)
FOR UPDATE NOWAIT;
SELECT * FROM dept
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

If session 1 ends its transaction by a commit or rollback within 3 seconds no error message returns with this option;


SELECT * FROM dept
WHERE deptno IN (10,20)
FOR UPDATE WAIT 3;

Now we again try to lock two rows (deptno(s) 10 and 20) from the table dept but using the clause FOR UPDATE SKIP LOCKED instead of FOR UPDATE NOWAIT or WAIT. As you can see the following statement has;

1. returned the control without throwing an exception
2. acquired lock on the row (i.e. deptno 20) which is available for locking
3. skipped the row (i.e. deptno 10) that has been locked already by session 1


SELECT * FROM dept
WHERE deptno IN (10,20)
FOR UPDATE SKIP LOCKED;

DEPTNO DNAME
———- ——————————
20 Marketing

10- Oracle System Event Triggers

Introduced in Oracle 8.1.5 and must be created by SYS(as SYSDBA). With these triggers you can capture and log any error happening within the database, or start sql trace for some users after logon;

Code listing 67c : System Event Trigger Examples

11- Autonomous Transactions

Prior to Oracle8i, a COMMIT or ROLLBACK in any program in your session committed or rolled back all changes in your session, there was only one transaction allowed per connection. You can define a PL/SQL block to execute as an “autonomous transaction”, any changes made within that block can be saved or reversed without affecting the outer or main transaction.

Recursive transaction started by main transaction can commit or rollback independently of main transaction with the PRAGMA AUTONOMOUS_TRANSACTION. Committed data unaffected if main transaction rolls back. Often used for auditing, however, by definition, autonomous routines never violate the rules read no database state (RNDS) and write no database state (WNDS) no matter what they do. Below example can be useful when you call the packaged function log_msg from a query;

Code listing 67d : Autonomous Transaction Examples

Some more best practices coming with following parts like Auditing, Transportable Tablespaces, Change Data Capture and Automatic Management Options of Oracle like Automatic Shared Memory Management, Automatic Storage Management, Automatic Workload Repository, Automatic Segment Space Management, Automatic Undo Management, Automatic PGA Memory Management for Dedicated Mode

Continue reading with Part 3

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
Oracle Presentations by Julian Dyke
Oracle® Database Concepts 10g Release 2 (10.2)
http://www.psoug.org/reference/system_trigger.html

Published by H.Tonguç Yılmaz

https://www.linkedin.com/in/tongucyilmaz/