PL/SQL – SQL TUNING

PL/SQL – SQL TUNING

INDEX

            An index is an ordered list of contents of a column, or a group of columns of a table.

• Duplicate Index: Indexes that allow duplicate values for the indexed columns.
• Unique Index: Indexed that deny duplicate values for the indexed columns.
• Simple Index: Index created on a single column of a table.
• Composite Index: Index created on more than one column of a table.
• Reverse Key Indexes: Reverses each byte of the column being indexed while keeping the column order. Helps in avoiding performance degradation in Indexes where modifications to the index are concentrated on a small set of blocks. A Reverse Key Index can be built in to a normal index using the keywords REBUILD NOREVERSE.

ALTER INDEX <indexname> REBUILD NOREVERSE;

• Bitmap Indexes: Advantages of BI are greatest for low cardinality columns i.e. columns in which the number of distinct values is small compared to the number of rows in the table.
• Function Based Index: A function based index cannot be created on a LOB column, REF, nested table column or the object type contains a LOB, REF, or nested table.
• Key Compressed Index: Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. Key Compression can be useful when in a non-unique index the ROWID is appended to make the key unique.
• Partitioning Indexes: Similar to partitioning tables. Like table partitions, index partition could be in different Tablespaces. Partitioned Indexes are more complicated than partitioned tables.

CLUSTERS

            The concept of Cluster is where members records are stored PHYSICALLY near parent records. Clusters are used to store data from different tables in the same physical data blocks. They are appropriate to use if the records from those tables are frequently queried together.

• Cluster Key: The columns within the cluster index are called the Cluster Key. (I.e. the set of columns that the tables in the cluster have in common).

SEQUENCES

            Sequence is an Oracle Object that generates numeric values. Value generated can have maximum of 38 digits. Generate is ASC or DESC order, provides intervals between numbers.

SNAPSHOTS

            Is a recent copy of a table from database or in some cases, a subset of rows/columns of a table. A snapshot is created on the destination system with the create snapshot SQL command. The remote table is immediately defined and populated from the master table.

HINTS

            The optimizer in Oracle Engine uses hints as suggestions for choosing an execution plan for the statement. A statement block can have only one  comment containing hints and that comment must follow the SELECT, UPDATE, INSERT and DELETE keyword.

{SELECT|UPDATE|INSERT|DELETE} /*+ hint [text]  [hint [text]] ….. */

Hints by functional category

Optimization Goals and Approaches ALL_ROWS and FIRST_ROWS CHOOSE RULE	Join Order Hints ORDERED STAR
Access Methods Hints AND EQUAL CLUSTER FULL HASH INDEX and NO_INDEX INDEX_ASC and INDEX_DESC INDEX_COMBINE INDEX_FFS ROWID	Query Transformation Hints EXPAND_GSET_TO_UNION FACT and NOFACT MERGE NO_MERGE NO_EXPAND REWRITE and NOREWRITE STAR_TRANSFORMATION USE_CONCAT
Join Operation Hints DRIVING_SITE HASH_SJ, MERGE_SJ and NL_SJ LEADING USE_HASH and USE_MERGE USE_NL	Parallel Execution Hints PARALLEL and NOPARALLEL PARALLEL_INDEX PQ_DISTRIBUTE NOPARALLE_INDEX
Other Hints       APPEND and NOAPPEND      CACHE and NOCACHE                            CURSOR_SHARING_EXACT                                DYNAMIC_SAMPLING          NESTED_TABLE_GET_REFS                   UNNEST and NO_UNNEST       ORDERED_PREDICATES       PUSH_PRED and NO_PUSH_PRED       PUSH_SUBQ and NO_PUSH_SUBQ

Hints can be any of the optimizer mode values (CHOOSE, RULE, FIRST_ROWS, ALL_ROWS) or they can be one of the access paths mentioned above.

Access Path                                        Description

Basic Access Path Hints

1. ROWID                        -           Uses ROWID scan for retrieval
2. CLUSTER                     -           Uses a cluster key scan
3. hash                            -           Uses a hash index scan
4. INDEX_ASC                 -           Uses an index scan and scan in ascending order
5. index_desc                  -           Uses an index scan and scans in descending order
6. AND_EQuAL                -           Uses multiple indexes and merges their results

7.ordered                          -           Uses the order of the tables in the FROM clause to  be the order of the join.

8.use_nl                            -           Uses the nested loops method for joining tables

9.use_merge                     -           Uses the sort-merge method for joining tables

10.                                                  FULL    -           Uses a full table Scan

Additional Access Path for Hints (Version 7.3 and Later)

1. CACHE                         -           Tells Oracle to treat the table as a cached table,    keeping its blocks in the SGA after a full scan for later quick access.
2. HASH_AJ                     -           Specifies type of join to use during an antijoin.
3. MERGE_AJ                  -           Specifies type of join to use during an antijoin.
4. NO_CACHE                  -           Marks blocks as “least recently used” so they get removed from SGA soon.
5. NONPARALLEL            -           Allows disabling parallelism of a query.
6. ROWID                        -           Uses TABLE ACCESS BY ROWID operation
7. STAR                            -           Uses a composite key/start query execution path when resolving a join.
8. USE_CONCAT              -           Forces OR conditions in the WHERE clause to be compounded as UNION ALL.
9. USE_HASH                  -           Uses a hash join

CONCURRENCY CONTROL IN ORACLE

The technique employed by the Oracle engine to protect table data when several people are accessing it is called Concurrency control. Oracle uses method called Locking to implement concurrency control when multiple users access a table to manipulate its data at the same time.

LOCKS

            Locks are mechanisms used to ensure data integrity while allowing maximum concurrent access to data. Oracle’s locking is fully automatic and requires no user intervention. The Oracle engine automatically locks table data while executing SQL statements. This type of locking is called Implicit Locking.

Types of Locks           

The type of lock to be placed on a resource depends on the operation being performed on that resource. Operations on tables can be distinctly grouped into the following two categories.

-       Read Operation: SELECT statements

-       Write Operation: INSERT, UPDATE, DELETE statements.

Two types of Locks supported by Oracle are:

• Shared Locks: are placed on resources whenever a READ operation (SELECT) is performed. Multiple shared Locks can be simultaneously set on a resource.
• Exclusive Locks: are placed on resources whenever Write operations (INSERT,UPDATE and DELETE) are performed. Only one exclusive lock can be placed on a resource at a time i.e. the first user who acquires an exclusive lock will continue to have the sole ownership of the resource, and no other user can acquire an exclusive lokc on that resource.

LEVELS OF LOCKS

            Three levels of locking:    Row Level, Page Level, Table Level

Releasing the Locks

            Locks are released under the following circumstances:

• The transaction is committed successfully using the Commit verb
• A rollback is performed
• A rollback to a savepoint will release locks set after the specified savepoint.

Deadlock

            A deadlock occurs when two users have a lock, each on a separate object, and, they want to acquire a lock on each other’s object. When this happens, the first user has to wait for the second user to release the lock, but the second user will not release it until the lock on the first user’s object is freed. At this point, both the users are at an impasse and cannot proceed with their business. In such a case, Oracle detects the deadlock automatically and solves the problem by aborting one of the two transactions.

ERROR HANDLING IN PL/SQL

            When an SQL statement fails, the Oracle Engine is the first to recognize this as an Exception condition. The Oracle engine immediately tries to handle the exception condition and resolve it. This is done by raising a built-in Exception Handler. An exception handler is nothing but a code block in memory that will attempt to resolve the current exception condition.        

                                                            ORACLE’S NAMED EXCEPTION HANDLERS

DUP_VAL_ON_INDEX Raised when an insert or update attempts to create two rows with duplicate values in column/s constrained by a unique index.	TIMEOUT_ON_RESOURCE Raised when Oracle has been waiting to access a resource beyond the user-defined timeout limit.
LOGIN_DENIED Raised when an invalid username/password was used to log onto oracle.	NOT_LOGGED_ON Raised when PL/SQL issues an Oracle call without being logged onto Oracle.
NO_DATA_FOUND Raised when a select statement returns zero rows.	PROGRAM_ERROR Raised when PL/SQL has an internal problem.
TOO_MANY_ROWS Raised when a select statement returns more than one row	VALUE_ERROR Raised when the data type or data size is invalid.
OTHERS Stands for all other exceptions not explicitly named.