Index Organized Tables

INDEX ORGANIZED TABLES


Oracle Index-organized tables (IOTs) are a unique style of table structure that is stored in a B-tree index structure.Primarily used with high-updates tables, Oracle Index Organized tables structure reduces table fragmentation.An index-organized table must have a primary key. Oracle index-organized tables are best suited for use with queries based on primary key values.

Disadvantages of IOT

• It takes longer to insert into the IOT
• You don't have good secondary index capabilities

It takes longer to put the data away in the IOT (mostly regardless of the arrival order) but it is retrieved much faster with less resources (assuming you have something like the stock quotes or document management system examples) .It is best to use an IOT when you only query the table's contents by specifying the primary key in the WHERE clause.If you query other, non-PK columns in the WHERE clause, you may find the IOT will slow down your performance.

As there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.All non-key columns beyond the column specified in the INCLUDING clause are stored in the overflow segment.


Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:

• Key compression
• Overflow storage area and specific column placement
• Secondary indexes, including bitmap indexes.

Scalar Sub Query Caching

SCALAR SUB QUERY CACHING


A subquery in the FROM clause is called an inline view.A subquery in the WHERE clause is called a nested subquery.

The SQL WITH clause only works on Oracle 9i release 2 and beyond.
Formally, the WITH clause was called subquery factoring.

Depending on the release of Oracle in use, the global temporary tables (GTT) might be a better solution than the WITH clause because indexes can be created on the GTT for faster performance.


What exactly is a scalar subquery? 

It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used.

Oracle makes use of a HASH Table for caching the results of the Scalar subQuery so that it doesn't have to fire the same scalar subQuery each time for the same value passed to the Scalar sub Query.The HASH Table is capable of storing values from 1..255 (Oracle 10G and 11G) and will look in that hash table slot to see if the answer exists. So Depending on the distinct number of input values passed to a Scalar Sub Query, Oracle tries to fetch the data from the Hash Table instead of firing the Sub Query again but this is limited to the number of entries permitted to be stored in the HASH Table.

The Scalar Sub Query Caching helps in reducing the number of times plsql Functions are called from a SQL. So there occurs a context switching between the SQL Engine and the PLSQL Engine. Context Switching is one the reasons for Performance Hindrance.

But Scalar SubQueries involving PLSQL Functions requires the PLSQL Function to be deterministic so that the PLSQL Functions always returns the same output for the same input.

SUB QUERY FACTORING


Making use of the With Clause , Oracle makes use of a optimization approach where by the Query specified in the With Clause is executed only once and this data is stored in the temporary Tablespace( Similar to creating a Global Temporary Table). So irrespective of the number of times the Query within the WITH CLAUSE is called, it will be Executed only once thereby limiting the number of times the data is accessed from disk thereby reducing the execution time of the Query.

On tiny datasets, the time involved in the temporary table setup can take longer than the original query itself so is not a particularly useful mechanism.

Any subqueries we define must be referenced at least once otherwise Oracle will raise an Exception.

SUBQUERY FACTORING AND DML


Subquery factoring is part of the SELECT statement itself, which means it can be used anywhere a SELECT is used. For example, the following statement formats are all valid.

INSERT INTO table_name
WITH subquery_name AS (
        SELECT ...
        FROM   ...
        )
SELECT ...
FROM   subquery_name;

UPDATE table_name
SET    column_name = ( WITH subquery_name AS (
                               SELECT ...
                               FROM   ...
                               )
                       SELECT ...
                       FROM   subquery_name );

DELETE
FROM   table_name
WHERE  column_name IN ( WITH subquery_name AS (
                                SELECT ...
                                FROM   ...
                                )
                        SELECT ...
                        FROM   subquery_name );

INSERT INTO table_name
VALUES ( (WITH subquery_name AS (
                  SELECT ...
                  FROM   ...
                  )
          SELECT ...
          FROM   subquery_name) );


         
SUB QUERIES CAN BE INCLUDED IN VIEWS but there is a small "gotcha". Some DDL-generators wrap the view's SQL in parentheses and this raises an exception when subqueries are used, as follows.

SQL> CREATE VIEW view_with_subquery
  2  AS
  3     (
  4      WITH subquery_name AS (
  5         SELECT SYSDATE AS date_column
  6         FROM   dual
  7         )
  8      SELECT date_column
  9      FROM   subquery_name
 10     );
   )
   *
ERROR at line 10:
ORA-32034: unsupported use of WITH clause
The correct way to code this is to remove the outer parentheses from the SQL, as follows.

SQL> CREATE OR REPLACE VIEW view_with_subquery
  2  AS
  3     WITH subquery_name AS (
  4        SELECT SYSDATE AS date_column
  5        FROM   dual
  6        )
  7     SELECT date_column
  8     FROM   subquery_name;

View created.


http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

The above link gives info on the Scalar Sub query Caching in Oracle Explained by Tom Kyte.

Avoid Sorting

AVOID SORTING


Disk Sorting Excessive I/O which severs as the main purpose for Performance Hindrance when i do a sort via the Order by clause and which is not able to fit inside the sort_Area_size allocated in the PGA.Sorting starts in Oracle PGA RAM (defined by the limits of sort_area_size and pga_aggregate_target 5% session limit), and a "disk sort" is invoked when the sort exceeds the maximum PGA allowed for the session.

A disk sort can far outweigh the unwanted effects of regular disk I/O read activity because a disk sort involves both physical reads and physical writes. First, Oracle must perform physical writes to a session's temporary tablespace for the sort activity it cannot handle in memory. Then, the database must turn right around and read that information back from disk to build the result set requested by the SQL query. So in essence, it's a double whammy especially for large result sets that are the product of heavy sort requests.

Oracle states that "The new sort algorithm shows more performance improvement for in-memory sorts.".Oracle10gRw introduced a new sort algorithm which is using less memory and CPU resources.For sorting in Oracle 10g release 2 there is a hidden parameter called "_newsort_enabled" that turns-on the new sorting algorithm.
We also see that in-memory sorts are CPU-intensive, and that faster processors will also improve in-memory sort performance.

Disk operations are about 10,000 times slower than a row's access in the data buffers.

If the clustering factor-an integer-roughly matches the number of blocks in the table, your table is in sequence with the index order. However, if the clustering factor is close to the number of rows in the table, it indicates that the rows in the table are out of sequence with the index.


In large active tables with a large number of index scans, row re-sequencing can triple the performance of queries.

Also, excessive disk sorting will cause a high value for free buffer waits, paging other tasks' data blocks out of the buffer.

Each sort in a query can consume memory up to the amount specified by SORT_AREA_SIZE, and there can be multiple sorts in a query. Also, if a query is executed in parallel, each PQ slave can consume memory up to the amount specified by SORT_AREA_SIZE for each sort it does.


SORT_AREA_SIZE is also used for inserts and updates to bitmap indexes. Setting this value appropriately results in a bitmap segment being updated only once for each DML operation, even if more than one row in that segment changes.Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.

SORT_AREA_SIZE's are not allocated until they are needed (and GROW to be sort_area_size bytes in size -- they don't start there) and are freed the second the sort is over.

Oracle will always use the cheapest method for sequencing a result set, and the optimizer will use index retrieval (extracting the rows in sorted order) if it consumes fewer resources than a back-end sort. Remember, a sort that cannot fit into RAM will have to be done in the TEMP tablespace, very slow with lots of disk I/O.

As a general rule, the sort_area_size should be large enough that only index creation and ORDER BY clauses using functions should be allowed to use a disk sort.

A Query on the V$sysstat gives info on the sorts performed both in memory and disk sorts and also the number of rows that was sorted.

In general, Oracle sorting occurs under the following circumstances:
 

• SQL using the ORDER BY clause
• SQL using the GROUP BY clause
• When an index is created
• When a MERGE SORT is invoked by the SQL optimizer because inadequate indexes exist for a table join

Other SQL operations that might require disk sorting are:
 

• ANALYZE
• Select DISTINCT
• UNION  causes a SORT to eliminate the duplicates whereas UNIONALL Does not
• INTERSECT
• MINUS
• Sort-Merge joins.

IN MEMORY , ONE PASS AND MULTI PASS SORT

Explanation by Tom Kyte

In memory sort Everything fits in memory, no disk is needed, everything done in ram.

one pass sort We read some amount of data and sort it, the sort work area fills up, we write that to disk. We do it again (read more, sort it) - this too spills to disk. We do this over and over until we've read the entire set of data and sorted each of the chunks. Now we need read a bit of each of the chunks we've sorted on disk and start returning data from there. In the one pass sort, we can read a bit of each chunk and start returning data. If we cannot read a bit of EVERY chunk then....

we are in the realm of the multi-pass sort. We read and merge the first few chunks - resulting in a new set. We read and merge another set of chunks - resulting in a new set. And so on - until we've processed all of the output from the first pass and created a "second pass" of more merged (more sorted) data. Then we merge those in the sort area and start returning data.

Row Chaining

ROW CHAINING

The PCTFREE parameter is used to specify the amount of free space on a data block to reserve for future row expansion. If PCTFREE is set improperly, SQL update statements can cause a huge amount of row fragmentation and chaining.

Improper settings for PCTUSED (e.g., set too small) can cause huge degradations in the performance of SQL insert statements. If a data block is not largely empty, excessive I/O will happen during SQL inserts because the reused Oracle data blocks will become full quickly. Taken to the extreme, improper settings for PCTUSED can create a situation where the free space on the data block is smaller than the average row length for the table. In these cases, Oracle will try five times to fetch a block from the freelist chain. After five attempts, Oracle will raise the high-water mark for the table and grab five fresh data blocks for the insert.

With a true "chained row", part of the DATA of a row is on one block and part of it is on another block.  So, instead of just having a forwarding address on one block -- and the data on another (thats a migrated row), we have data on two or more blocks....

In the case of Row Chaining, Oracle may hinder performance issues only when the requested data is not present in the current block and Oracle has to fetch the requested data from the chained block.

select * from chained_rows  gives information on the chained rows. Even DBA_Tables gives information on chained_cnt.

Any table with a long/long raw will have chained rows.When a table has more than 255 columns, rows that have data after the 255th column are likely to
be chained within the same block. This is called intra-block chaining.With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.


ROW MIGRATION

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently).  A migration means that the entire row will move and we just leave behind the "forwarding address".  So, the original block just has the rowid of the new block and the entire row is moved.

When we FULL SCAN a table, we actually *ignore* the forwarding addresses (the head row piece we call it for a row).  We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there.  Hence, in a full scan migrated rows don't cause us to really do any extra work -- they are meaningless.  Oh sure, the forwarding address is consuming a couple of bytes on the block -- it is overhead -- but frankly, it is meaningless.

When we INDEX READ into a table -- then a migrated row will cause additional IO's.  That is because the index will tell us "goto file X, block Y, slot Z to find this row".  But when we get there we find a message that says "well, really goto file A, block B, slot C to find this row".  We have to do another IO (logical or physical) to find the row.

HOW TO IDENTIFY ROW CHAINING AND ROW MIGRATION

we can detect the Migrated or chained rows in a TABLE or CLUSTER by using the following methods.

Using ANALYZE,REPORT.TXT and V$VIEWS.

Analyze

Before doing this Analyze create the table that can hold chained rows.Execute UTLCHAIN.SQL script found in the ($ORACLE_HOME)/rdbms/admin directory, this can be run to create the CHAINED_ROWS table or else create a similar table with same column and datatypes and sizes as the CHAINED_ROWS table.

SQL>ANALYZE TABLE SCHEMA_NAME.TABLE_NAME LIST CHAINED ROWS;

SQL>SELECT owner_name,table_name,head_rowid from chained_rows where TABLE_NAME = 'YOUR_TABLE_NAME';

REPORT.TXT and V$VIEWS

The Migrated or chained rows can be detected by checking the "table fetch continued row" statistic in V$SYSSTAT or in REPORT.TXT.

Note:REPORT.TXT is a file created during UTLBSTAT and UTLESTAT analyze.

HOW TO ELIMINATE MIGRATED AND CHAINED ROWS

It is VERY important to understand the distinction between migrated and chained rows. Row chaining is UNAVOIDABLE but row migration can be MANAGED and resolved through reorganization.

Note:Chaining if its going to be in all Tables then it becomes design issue and properly the DBlock size is done.

Else to avoid row chaining for very few occasions if you increase the DBlock size the effects of it as follows.

For Eg:

Space in the Buffer cache will be wasted if you are doing random access to small rows and have a large block size .For Eg an Block size of 8 KB and a 50 byte row size the wasted space will be 7950 bytes in the buffer cache in an random access.

The steps involved are:

1)Analyze the table ....list chained rows
2)Copy the rows to another table
3)Delete the rows from the original table
4)insert the rows from step 2 back to original table.

Step 4 eliminates the Migrated rows because Migration only occurs during an UPDATE.

QUERY TO DETECT TABLES WITH MIGRATED OR CHAINED ROWS


select
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
 (select table_name from dba_tab_columns
   where
 data_type in ('RAW','LONG RAW')
 )
and
chain_cnt > 0
order by chain_cnt desc
;

ASSM

AUTOMATIC SEGMENT SPACE MANAGEMENT

Prior to Oracle 9i, each block had to be read so the freelist could be checked to see if there was room in the block. In 9i, the bitmap can be checked reducing the number of blocks read unnecessarily.

Starting from Oracle9i, the PCTUSED, FREELISTS, and FREELIST GROUPS parameters are ignored with locally managed tablespaces and ASSM.The ASSM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition syntax. ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.

In an Locally Managed Tablespace, Oracle moves the tablespace information out of the data dictionary tablespace and stores it directly within the tablespace itself. The second major tablespace enhancement in Oracle9i, was automatic segment space management (ASSM). a.k.a. bitmap freelists.   With ASSM, the linked-list freelists are replaced with bitmaps, a binary array that turns out to be very fast and efficient for managing storage extents and free blocks, thereby improving segment storage internals.

Automatic free space management is only available in locally managed tablespaces. It removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment. The bitmap is stored in separate blocks known as bit mapped blocks (BMBS). This relieves the contention on the segment header that occurs with freelists.

With ASSM, the linked-list freelists are replaced with bitmaps, a binary array that turns out to be very fast and efficient for managing storage extents and free blocks, thereby improving segment storage internals. Usage of ASSM causes some performance hindrance but this has been certainly overcome with the 11G Release 2.ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.

Without multiple freelists, every Oracle table and index had a single data block at the head of the table to manage free blocks for the object and provide data blocks for new rows created by any SQL insert statements. A buffer busy wait occurs when a data block is inside the data buffer cache but is unavailable because it is locked by another DML transaction.  When you want to insert multiple tasks into the same table, the tasks are forced to wait while Oracle assigned free blocks, one at a time.

With ASSM, Oracle claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously, eliminating serialization for free space lookups.Segment space management auto is a good method for handling objects with varying row sizes.

Large objects cannot use ASSM, and separate tablespaces must be created for tables that contain LOB datatypes. You cannot create a temporary tablespace with ASSM. This is because of the transient nature of temporary segments when sorting is performed.

Only locally managed tablespaces can use bitmap segment management.

FREELIST
 

This is a list of blocks kept in the segment header that may be used for new rows being inserted into a table. When an insert is being done, Oracle gets the next block on the freelist and uses it for the insert. When multiple inserts are requested from multiple processes, there is the potential for a high level of contention since the multiple processes will be getting the same block from the freelist, until it is full, and inserting into it. Depending on how much contention you can live with, you need to determine how many freelists you need so that the multiple processes can access their own freelist.

AUTO - This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

The package DBMS_SPACE contains a procedure called SPACE_USAGE that gives information about how space is being used within blocks under the segment high water mark.

LOCALLY MANAGED TABLESPACES

Movement of the space management out of the data dictionary and into the tablespace have two benefits.  First, the tablespace become independent and can be transportable (transportable tablespaces).  Second, locally managed tablespaces remove the O/O contention away from the SYS tablespace.

Locally managed tablespaces with ASSM ignore any specified values for PCTUSED, NEXT, and FREELISTS specified in the table definition.

DB Links

DATABASE LINKS

The Use of DB Links cause a performance degradation becasue of the Network Lag in pushing the huge amount of data from the remote site to the Local site. So the performance of the Query depends upon the activity which is carried out on the data and also on the amount of data which is carried out on the remote site.

The driving site hint forces query execution to be done at a different site than the initiating instance. If your SQL performs a sort, be aware that the sort will be performed on the LOCAL database.In distributed databases when a table has been partitioned into separate instances, a parallel query can be invoked to read the remote tables simultaneously.

Partitioning

PARTITIONING

Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

Here are some suggestions for when to partition a table:

• Tables greater than 2 GB should always be considered as candidates for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only. 
• When the contents of a table need to be distributed across different types of storage devices.

Here are some suggestions for when to consider partitioning an index:

• Avoid rebuilding the entire index when data is removed.
• Perform maintenance on parts of the data without invalidating the entire index. 
• Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

PARTITIONING FOR PERFORMANCE


By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. These features include:

• Partition Pruning
• Partition-Wise Joins

Oracle partition pruning only accesses those data blocks required by the query.Partitioning Pruning refers to the act of eliminating partitions which are not of concern and scanning only the partitions which are of concern and containing the data which is requested thereby eliminating the need to scan the entire table to search for the requested data and scanning only the partitions on which the data falls.Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method.

Partitioning can also improve the performance of multi-table joins by using a technique known as partition-wise joins. Partition-wise joins can be applied when two tables are being joined together and both tables are partitioned on the join key, or when a reference partitioned table is joined with its parent table. Partition-wise joins break a large join into smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.Partitioned database objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy.Storing different partitions in different tablespaces allows the database administrator to do backup and recovery operations on each individual partition, independent of the other partitions in the table.

Bind Variables

USE OF BIND VARIABLES

Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. This process is known as SOFT PARSE. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is know as a HARD PARSE and for OLTP applications can actually take longer to carry out that the DML instruction itself.

When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered. The search for the previously executed sql statement is case sensitive.

Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas.The extra bonus with this problem is that contention caused by hard parsing is pretty much immune to measures such as increasing available memory, numbers of processors and so on, as hard parsing statements is one thing Oracle can't do concurrently with many other operations, and it's a problem that often only comes to light when trying to scale up a development system from a single user working on subset of records to many hundreds of users working on a full data set.

The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are «substitution» variables that are used in place of literals and that have the effect of sending exactly the same SQL to Oracle every time the query is executed.

By reusing the execution plan every time, we reduce the time required for parsing the Query, the latch activity in the SGA and therefore the total CPU activity, which has the effect of allowing our application to scale up to many users on a large dataset.

Every reference to a PL/SQL variable is in fact a bind variable.Whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on.

Eg:- execute immediate ' select emp_name from emp where emp_no = :i' using emp_number

When you put together an SQL statement using Java, or VB, or whatever, you usually use an API for accessing the database; ADO in the case of VB, JDBC in the case of Java. All of these APIs have built-in support for bind variables, and it's just a case of using this support rather than just concatenating a string yourself and submitting it to the database.

The Concept of allowing the Oracle optimizer to re use the sane execution plan might result in some performance degradation because of the data distribution of the column for which the value if passed. Oracle optimizer may use the same execution plan for two different values for the column with different data distributions for the column values. Oracle introduced the concept of Bind Variable peeking to peek into the value of the Bind Variable to decide upon the execution plan.

The implementation of bind variable peeking could cause performance problems, and many shops would disable bind variable peeking by getting permission to set the hidden parameter _optim_peek_user_binds=false.

Adaptive Cursor Sharing


In Oracle Database 11g, the database feature called adaptive cursor sharing enables a SQL statement with bind variables to use multiple execution plans, with each execution plan based on the values of the bind variable(s).Adaptive cursor sharing is enabled by default, and you can't disable it.

Adaptive cursor sharing is a new feature introduced in the Oracle Database 11g release. In earlier releases, DBAs often flushed the shared pool (and worse, sometimes restarted the database) when confronted with situations where the database apparently started using an inappropriate execution plan for a SQL statement, due to the bind peeking effect. In the 11g release, you don't have to do anything—the optimizer automatically changes execution plans when it encounters skewed data. With adaptive cursor sharing, the database uses multiple execution plans for a statement that uses bind variables, ensuring that the best execution plan is always used, depending on the value of the bind variable. Adaptive cursor sharing means that when different bind variable values indicate different amounts of data to be handled by the query, Oracle adapts its behavior by using different execution plans for the query instead of sticking to the same plan for all bind values. Since adaptive cursor sharing works only where literal values are replaced with binds, Oracle encourages you to use the FORCE setting for the cursor_sharing parameter. If you set the parameter to SIMILAR and you have a histogram on a column, the optimizer doesn't perform a literal replacement with bind variables, and thus adaptive cursor sharing won't take place. You must set the cursor_sharing parameter to FORCE for adaptive cursor sharing to work, thus letting the optimizer select the optimal execution plan for different values of the bind variable.

JOINS USED BY THE OPTIMIZER

JOINS USED BY THE OPTIMIZER

With the CBO, the optimizer generates a set of execution plans, according to possible join orders, join methods, and available access paths. The optimizer then estimates the cost of each plan and chooses the one with the lowest cost. The optimizer estimates costs in the following ways:

The cost of a nested loops operation is based on the cost of reading each selected row of the outer table and each of its matching rows of the inner table into memory. The optimizer estimates these costs using the statistics in the data dictionary. The cost of a sort merge join is based largely on the cost of reading all the sources into memory and sorting them.

A smaller sort area size is likely to increase the cost for a sort merge join because sorting takes more CPU time and I/O in a smaller sort area. Sort area size is specified by the initialization parameter SORT_AREA_SIZE.

A larger multiblock read count is likely to decrease the cost for a sort merge join in relation to a nested loop join. If a large number of sequential blocks can be read from disk in a single I/O, then an index on the inner table for the nested loop join is less likely to improve performance over a full table scan. The multiblock read count is specified by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.

With the CBO, the optimizer's choice of join orders can be overridden with the ORDERED hint. If the ORDERED hint specifies a join order that violates the rule for an outer join, then the optimizer ignores the hint and chooses the order. Also, you can override the optimizer's choice of join method with hints.

NESTED LOOP JOINS

Nested loop joins are useful when small subsets of data are being joined and if the join condition is an efficient way of accessing the second table.

It is very important to ensure that the inner table is driven from (dependent on) the outer table. If the inner table's access path is independent of the outer table, then the same rows are retrieved for every iteration of the outer loop, degrading performance considerably. In such cases, hash joins joining the two independent row sources perform better.

A nested loop join involves the following steps:

• The optimizer determines the driving table and designates it as the outer table.
• The other table is designated as the inner table.
• For every row in the outer table, Oracle accesses all the rows in the inner table. The outer loop is for every row in outer table and the inner loop is for every row in the inner table.

The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can be used as a row source for another nested loop join.


HASH JOINS




Hash joins are used for joining large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then scans the larger table, probing the hash table to find the joined rows.


This method is best used when the smaller table fits in available memory. The cost is then limited to a single read pass over the data for the two tables.


The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:

• A large amount of data needs to be joined.
• A large fraction of the table needs to be joined.

However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins. Oracle places limits of the amount of RAM available for hash joins so that no single session will "hog" all of the RAM. For large tables, hash joins requires lots of RAM.According to the research done by the Burleson Consulting Group , the maximum hash size is limited to 5% of the pga_aggregate_target.


The CBO will only choose a hash join if you have allocated Oracle enough RAM area in which to perform the hash join.


Make sure that the smaller table is the driving table (the first table in the from clause when using the ordered hint). This is because a hash join builds the memory array using the driving table.


Oracle recommends that the hash_area_size for the driving table be set at 1.6 times the sum of bytes for the driving table, and you can use the hash_area.sql script to set the hash_area_size for your query session.




SORT MERGE JOIN




Sort merge joins can be used to join rows from two independent sources. Hash joins generally perform better than sort merge joins. On the other hand, sort merge joins can perform better than hash joins if both of the following conditions exist:

• The row sources are sorted already.
• A sort operation does not have to be done.

However, if a sort merge join involves choosing a slower access method (an index scan as opposed to a full table scan), then the benefit of using a sort merge might be lost.


Sort merge joins are useful when the join condition between two tables is an inequality condition (but not a non equality) like <, <=, >, or >=. Sort merge joins perform better than nested loop joins for large data sets. You cannot use hash joins unless there is an equality condition.


The join consists of two steps:


Sort join operation:     Both the inputs are sorted on the join key.
Merge join operation:     The sorted lists are merged together.


The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:

• The join condition between two tables is not an equi-join.
• OPTIMIZER_MODE is set to RULE.
• HASH_JOIN_ENABLED is false.
• Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.
• The optimizer thinks that the cost of a hash join is higher, based on the settings of HASH_AREA_SIZE and SORT_AREA_SIZE.

A happy side-effect of the sort-merge join is that the results are returned sorted by the columns in the join predicates; this feature can be used to dispense with an ORDER BY clause and therefore a subsequent sort.




CARTESIAN JOIN




A Cartesian join is used when one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with every row from the other data source, creating the Cartesian product of the two sets.




SEMI JOIN




A semi-join returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.

ANTI JOIN




An anti-join returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. That is, it returns rows that fail to match (NOT IN) the sub query on the right side.

Oracle Indexes

INDEXING


All data in Oracle - tables, indexes, clusters - is stored in blocks. The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb. Rows in a table are usually much smaller than this, so many rows will generally fit into a single block. So you never read "just one row"; you will always read the entire block and ignore the rows you don't need. Minimizing this wastage is one of the fundamentals of Oracle Performance Tuning.

If the information is in the index, then it doesn't bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.


"B" Tree Stands for Balanced Tree


Contrary to popular belief, b is not for binary; it's balanced.

As you insert new rows into the table, new rows are inserted into index leaf blocks. When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch-block. If the branch-block is also full, it too is split. The process propagates back up the tree until the parent of split has space for one more entry, or the root is reached. A new root is created if the root node splits. Staggeringly, this process ensures that every branch will be the same length.

BITMAP INDEXES


With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)

With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).

B-tree indexes are well suited for OLTP applications in which users' queries are relatively routine (and well tuned before deployment in production), as opposed to ad hoc queries, which are much less frequent and executed during non peak business hours. Because data is frequently updated in and deleted from OLTP applications, bitmap indexes can cause a serious locking problem in these situations.

Index information


The dba_indexes view is populated with index statistics when indexes are analyzed. The dba_indexes view contains a great deal of important information for the SQL optimizer, but there is still more to see. Oracle provides an analyze index xxx validate structure command that provides additional statistics into a temporary table called index_stats. But, the information needs to be saved, as each analyze validate structure command overlays the information.

To get the full picture, you need both pieces. Also, there are certainly some columns that are more important than others:

•CLUSTERING_FACTOR. This is one of the most important index statistics because it indicates how well sequenced the index columns are to the table rows. If clustering_factor is low (about the same as the number of dba_segments.blocks in the table segment) than the index key is in the same order as the table rows and index range scan will be very efficient, with minimal disk I/O. As clustering_factor increases (up to dba_tables.num_rows), the index key is increasingly out of sequence with the table rows. Oracle's cost-based SQL optimizer relies heavily upon clustering_factor to decide whether or not to use the index to access the table.

•HEIGHT. As an index accepts new rows, the index blocks split. Once the index nodes have split to a predetermined maximum level the index will "spawn" into a new level.

•BLOCKS This is the number of blocks consumed by the index. This is dependent on the db_block_size. In Oracle9i and beyond, many DBAs create b-tree indexes in very large block sizes (db_32k_block_size) because the index will spawn less. Robin Schumacher has noted in his book Oracle Performance Troubleshooting notes:
"As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache. Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database."

•PCT_USED. This metric is very misleading because it looks identical to the dba_indexes pct_used column, but has a different meaning. Normally, the pct_used threshold is the freelist unlink threshold, while in index_stats pct_used is the percentage of space allocated in the b-tree that is being used.

Concatenated Index


When frequently accessing two or more columns in conjunction in the WHERE clause, a concatenated index is often more selective than two single indexes.
In the case of a Concatenated Index, The ability of an Oracle Optimizer to use a Concatenated index without reference to leading edge columns is known as the skip-scan feature.A concatenated index that is used for skip-scanning is more efficient than a full table scan. Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated.


Function Based Indexes

You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Functions don't work based on the data stored in the Columns on which the functional index is created.

Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index

Indexing a Virtual Column


It is said that creating a Virtual Column and Indexing the Virtual Column performs much better than a Functional Index on a Table Column however it all depends on the data which is stored in the table. Therefore it is always necessary to test it before replacing any Functional Index with a Virtual Column. Also Virtual column concept can applied only for Oracle Releases after 11G.A virtual column is not free. If you have an existing table, you have to create and maintain the DDL required to create the virtual column, whereas a function-based index can be added, modified, and dropped independently from the table.

Several caveats are associated with virtual columns:
 

• You can define a virtual column only on a regular heap-organized table. You can't define a virtual column on an index-organized table, an external table, a temporary table, object tables, or cluster tables.
• Virtual columns can't reference other virtual columns.
• Virtual columns can reference columns only from the table in which the virtual column is defined.
• The output of a virtual column must be a scalar value (a single value, not a set of values).

Reverse Key Indexes


When sequential data is loaded, the index may encounter I/O-related bottlenecks. During the data loads, one part of the index, and one part of the disk, may be used much more heavily than any other part. To alleviate this problem, you should store your index tablespaces on disk architectures that permit the files to be physically striped across multiple disks.Reverse-key indexes are similar to B-tree indexes except that the bytes of the index key are reversed when an index entry is created.

You cannot use reverse key indexes with bitmap indexes or index-organized tables.

PARTITIONED INDEXES


Indexes, like tables, may be partitioned. There are two possible methods to partition indexes.They are Local and Global Partitioned Indexes.

You may either:

Equipartition the index with the table,  Also known as a local index. For every table partition, there will be an index partition that indexes just that table
partition. All of the entries in a given index partition point to a single table partition and all of the rows in a single table partition are represented in a single index partition.

Partition the index by range , Also known as a global index. Here the index is partitioned by range, and a single index partition may point to any (and all) table partitions.

In the locally partitioned index, the index entries in a given partition, point into exactly one table partition. The globally partitioned index diagram however, shows that the index entries in a global index may point into any or all of the table partitions. Also, note that the number of index partitions may in fact be different than the number of table partitions.

Since global indexes may be partitioned by range only, you must use local indexes if you wish to have a hash or composite partitioned index. The local index will be partitioned using the same scheme as the underlying table.

Prefixed Indexes


Prefixed indexes are indexes that contain keys from the partitioning key as the leading edge of the index.

Non-Prefixed Indexes


Non-prefixed indexes are indexes that do not have the leading column of the partitioning key as the leading column of the index.

Compressed indexes


This is really an option to a normal b-tree index.  It results in fewer leaf nodes, so less overall I/O and less to cache.  All of this means Oracle's optimizer will be just a little more likely to use these than a normal uncompressed index.  There is a cost to all of this, in CPU to uncompress when you access these.  Also, in reading about how the optimizer uses these, and choosing the proper compression level, it begins to look daunting.  Your mileage may vary.

Compressed indexes have the following advantages:

•  Reduced storage
• More rows stored in leaf blocks, which can result in less I/O when accessing a compressed index

Invisible indexes


These are new in 11g.  They are created as a normal index, but invisible to the cost based optimizer.  This can allow you to test out performance of large queries, without impacting the live running application.


Bitmap Join Indexes


The use of bitmap join indexes is restricted; you can only index the columns in the dimension tables.The columns used for the join must be primary key or unique constraints in the dimension tables, and if it is a composite primary key, you must use each of the columns in your join.

You cannot specify REBUILD ONLINE for bitmap indexes or for indexes that enforce referential integrity constraints.

Materialized Views

MATERIALIZED VIEW



A Materialized View Precomputes the data or executes the Query behind the view and has its results precomputed. This is very different from the normal Oracle Views which executes the Query on run time. Since the Query results are already precomputed, Accessing Data from the Materialized View is much faster when compared to Oracle Views. Materialized Views favors the creation of indexes on the precomputed data which is not possible in the case of Oracle Views which uses the indexes on the base tables on which the Oracle View is created.

The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.ENABLE Clause Specify ENABLE to enable the materialized view for query rewrite.

Enabling of query rewrite is subject to the following restrictions:

• You can enable query rewrite only if all user-defined functions in the materialized view are DETERMINISTIC.
• You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include CURRENT_TIME or USER, sequence values (such as the CURRVAL or NEXTVAL pseudo columns), or the SAMPLE clause (which may sample different rows as the contents of the materialized view change).

Restrictions on the Defining Query of a Materialized View The materialized view query is subject to the following restrictions:

• The defining query of a materialized view can select from tables, views, or materialized views owned by the user SYS, but you cannot enable QUERY REWRITE on such a materialized view.
• You cannot define a materialized view with a sub query in the select list of the defining query. You can, however, include sub queries elsewhere in the defining query, such as in the WHERE clause.
• Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.
• Materialized views cannot contain columns of data type LONG.
• You cannot create a materialized view log on a temporary table. Therefore, if the defining query references a temporary table, then this materialized view will not be eligible for FAST refresh, nor can you specify the QUERY REWRITE clause in this statement.
• If the FROM clause of the defining query references another materialized view, then you must always refresh the materialized view referenced in the defining query before refreshing the materialized view you are creating in this statement.

If you are creating a materialized view enabled for query rewrite, then:

• The defining query cannot contain, either directly or through a view, references to ROWNUM, USER, SYSDATE, remote tables, sequences, or PL/SQL functions that write or read database or package state.
• Neither the materialized view nor the master tables of the materialized view can be remote.

QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY


QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database making use of the materialized views  and function based indexes while choosing the best optimized path.To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.

There are three acceptable values for query_rewrite_integrity:

enforced (default) - Presents materialized view with fresh data
trusted - Assumes that the materialized view is current
stale_tolerated - Presents materialized view with both stale and fresh data

Initialization Parameters influencing performance

Initialization parameters which influence the performance of the application are as follows

• OPTIMIZER_DYNAMIC_SAMPLING
• OPTIMIZER_FEATURES_ENABLE
• OPTIMIZER_INDEX_CACHING
• OPTIMIZER_INDEX_COST_ADJ
• OPTIMIZER_MODE
• DB_CACHE_SIZE
• DB_KEEP_CACHE_SIZE
• DB_RECYCLE_CACHE_SIZE
• SHARED_POOL_SIZE
• CURSOR_SHARING
• DB_CACHE_ADVICE
• DB_FILE_MULTIBLOCK_READ_COUNT
• DB_N_CACHE_SIZE
• HASH_AREA_SIZE
• QUERY_REWRITE_ENABLED
• QUERY_REWRITE_INTEGRITY
• RESOURCE_LIMIT
• RESOURCE_MANAGER_PLAN
• SESSION_CACHED_CURSORS
• SORT_AREA_SIZE
• STATISTICS_LEVEL
• WORKAREA_SIZE_POLICY

OPTIMIZER_DYNAMIC_SAMPLING

The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter controls the number of blocks read by the dynamic sampling query. The parameter can be set to a value from 0 to 10.

Optimizer dynamic sampling refers to the ability of the SQL optimizer to take a sample of rows from a table to calculate missing statistics.

Some of the sampling levels that can be used;

level 0 - do not use dynamic sampling

level 1 - Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a sub query or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).

level 2 (default)- Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.

level 4 - Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

levels 5, 6, 7, 8, and 9 - Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.

level 10 - sample all blocks in the table

If you are using the default (which is 2) for OPTIMIZER_DYNAMIC_SAMPLING then dynamic sampling is only used for unanalyzed tables.

Setting it to 0 (off) will result in a default value of stats for unanalyzed tables.

Since Dynamic Sampling is used to analyze the un analyzed Tables and therefore facilitates the optimizer in getting the optimized execution plan based on the statistics available on the tables. Since 9i, the Oracle optimizer is a cost based optimizer and therefore relies on the statistics of the table in finding the optimized Execution plan.

OPTIMIZER_FEATURES_ENABLE

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.

You can browse the Oracle Documentation for more info the different features available with different releases of Oracle.

Whenever you find the performance of an application deteriorating after an upgrade, you can set the optimizer_features_enable parameter to a prior release of Oracle so that until you find the root cause of the problem, we can have the Optimizer use the features of a prior release but generally its not recommended because it is something like even though you upgrade a database you only use the features of a prior release.

OPTIMIZER_INDEX_CACHING

OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.

The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans.

The setting of the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ parameters will not make the plans run faster. It just affects which plan is chosen based on the cost of each plan.Just changing these parameters will not cause the same plan to go faster (or slower). It will just change the COSTS associated with the plan. It might result in a DIFFERENT PLAN being chosen based on the costs and that is where you would see performance differences.

Its better that the parameters be in the default settings.

OPTIMIZER_INDEX_COST_ADJ

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

A small value for this parameter will favour the use of the indexes instead of a full table scan as the cost incurred using the index will be less when compared to a full table scan.

Modifying this parameter could significantly degrade the performance of some queries if the perceived index cost is reduced to an unrealistic level and indexes are chosen when other access methods are more appropriate.

The optimizer_index_cost_adj parameter defaults to a value of 100, but it can range in value from one to 10,000.

OPTIMIZER_MODE

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

•first_rows_n

The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

•first_rows

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

•all_rows

The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

When Optimizer mode is set CHOOSE, its up to the optimizer to choose a cost based optimization approach or a rule based optimization approach and this is biased on the statistics which are present on the database objects which are queried.

The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g. The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle. It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer). The results of this obsolescence are as follows.

•The CHOOSE and RULE options for the OPTIMIZER_MODE parameter still exist but are no longer supported.

•The default value for the OPTIMIZER_MODE parameter is ALL_ROWS.

•The CHOOSE and RULE optimizer hints still exist but are no longer supported.

•Code requiring the RBO must be migrated to use the query optimizer.

DB_CACHE_SIZE

DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).

Staring in Oracle9i we also have separate database cache size parameters for multiple block sizes, including db_nk_cache_size.

The database writer (DBWR) process must sweep all of the blocks in db_cache_size when performing an asynchronous write. Having a huge db_cache_size can cause excessive work for the database writer.

Whenever there is a physical read of data from the data files, the data is first cached in the Database buffer cache, so that on subsequent access of the same data an be fetched from the cache instead of reading from the data files again. This results in a good performance because the data is read from cache instead of from the physical data files. The Data located in the Database Buffer Cache work on the LRU Algorithm and the Least recently used blocks are flushed from the cache , which might result in a physical data read when this data is requested again. Therefore it is necessary that this cache size is appropriately sized based on the recommendations from the AWR Report.

We can also make use of the DB_CACHE_ADVICE view to determine the appropriate size of the DB_CACHE_SIZE Parameter to prevent aging out of the necessary blocks from the cache.

DB_KEEP_CACHE_SIZE

db_cache_size + db_keep_cache_size + db_recycle_cache_size = totally buffer cache size.

This parameter states the size which is required for pinning the objects to the shared pool so that they are not aged out of memory. Small frequently used look up tables can be pinned into the memory so that cost incurred in making a physical read frequently is reduced because of the data accessed from the cache.

DB_RECYCLE_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE specifies the size of the RECYCLE buffer pool. This pool is reserved for table blocks of large tables on which full table scans are performed.

SHARED_POOL_SIZE

The shared pool can further be subdivided into:

*    Control structures

*    Character sets

*    Dictionary cache:-The dictionary cache stores parts fo the data dictionary because Oracle has to query the data dictionary very often as it is fundamental to the functioning of Oracle.

Oracle needs to allocate & deallocate memory as SQL or procedural code is executed based on the individual needs of users' sessions and in accordance to the LRU algorithm.

*    Library cache

    The library cache is further divided into

        Shared SQL Area,

        PL/SQL Procedures and

        Control Structures (Latches and Locks).

Highly accessed memory structures that provide information on object structures to SQL statements being parsed.

CURSOR_SHARING

EXACT: The database doesn't replace any literals, and the optimizer sees the query as it's presented to the optimizer. The optimizer generates a different plan for each execution of the statement, based on the literal values in the statement. The plan would thus be an optimal one, but each statement has its own parent cursor, and therefore a statement that's executed numerous times can use a considerable amount of space in the shared pool. This could potentially lead to latch contention and a slowdown in performance.

FORCE: Regardless of whether there's a histogram, the optimizer will replace the literal values with a bind value and optimize this query as if it were in the following form:

select * from employees where job=:b

The optimizer uses a single plan for each SQL statement, regardless of the literal values. Thus, the execution plan won't be optimal, as the plan is generic, and not based on the literal values. If a query uses literal values, the optimizer will use those values to find the most efficient execution plan. If there are no literals in the SQL statement, it's very hard for the optimizer to figure out the best execution plan. By "peeking" at the value of the bind variables, the optimizer can get a better idea of the selectivity of the where clause condition—it is almost as if literals had been used in the SQL statement. The optimizer peeks at the bind values during the hard parse state. Since the execution plan is based on the specific value of the bind variable that the optimizer happened to peek at, the execution plan may not be optimal for all possible values of the bind variable.

In this example, the optimizer uses bind peeking based on the specific value of the JOB column it sees. In this case, the optimizer uses the value Clerk to estimate the cardinality for the query. When it executes the same statement (with a different value in the JOB column, say, Manager), the optimizer will use the same plan that it generated the first time (JOB=Clerk). Since there is only one parent cursor and just child cursors for the distinct statements, there's less pressure on the shared pool. Note that a child cursor uses far less space in the shared pool than a parent cursor. Often, setting the cursor_sharing parameter to FORCE immediately resolves serious latch contention in the database, making this one of the few magic bullets that can help you quickly reduce latch contention.

SIMILAR (without a histogram on the JOB column): The database will use literal replacement—it uses a system-generated bind value instead of the literal value for the JOB column (Clerk). This is because the absence of a histogram on the JOB column tells the optimizer that the data in the JOB column isn't skewed, and therefore the optimizer chooses the same plan for each execution of the statement, even though the literal values are different. The optimizer thinks it shouldn't make any changes to the execution plans for the statements that differ only in literal values because the data is uniformly distributed. The SIMILAR setting without a histogram on the columns in a query provides the same query performance and a similar impact on the shared pool as when you specify the FORCE setting.

SIMILAR (with a histogram on the JOB column): When the optimizer sees the histogram in the JOB column, it realizes that the column is skewed—this tells the optimizer that the results of the query may vary widely depending on the literal value of the JOB column. Consequently, the optimizer generates a different plan for each statement based on the literal values—thus the plans are very efficient, as in the case when you specify the EXACT setting. The SIMILAR option with a histogram in place does use more space in the shared pool, but not as much as when you use the EXACT setting. The reason for this is that each statement has its own child cursor instead of a parent cursor.

The choice among the various settings of the cursor_sharing parameter really boils down to an assessment of what's more critical to database performance: using the default EXACT setting or SIMILAR (with a histogram on the relevant column) does provide better query performance but leads to the generation of numerous parent cursors (EXACT setting) or child cursors (SIMILAR setting). If there's a severe pressure in the shared pool, and consequent latch contention, the entire database will perform poorly. Under these circumstances, you're better off implementing a system-wide solution by setting the cursor_sharing parameter to FORCE, as this guarantees that there's only a single child cursor for each SQL statement. If you're concerned about the impact of a single SQL statement, just drop the histogram on the relevant columns used in the SQL statement and set the cursor_sharing parameter to FORCE—this will ensure that the optimizer uses system-generated bind values for the column(s) and ensures that the SQL statement uses much less space in the shared pool.

Adaptive Cursor Sharing

In Oracle Database 11g, the database feature called adaptive cursor sharing enables a SQL statement with bind variables to use multiple execution plans, with each execution plan based on the values of the bind variable(s).Adaptive cursor sharing is enabled by default, and you can't disable it.

Adaptive cursor sharing is a new feature introduced in the Oracle Database 11g release. In earlier releases, DBAs often flushed the shared pool (and worse, sometimes restarted the database) when confronted with situations where the database apparently started using an inappropriate execution plan for a SQL statement, due to the bind peeking effect. In the 11g release, you don't have to do anything—the optimizer automatically changes execution plans when it encounters skewed data. With adaptive cursor sharing, the database uses multiple execution plans for a statement that uses bind variables, ensuring that the best execution plan is always used, depending on the value of the bind variable. Adaptive cursor sharing means that when different bind variable values indicate different amounts of data to be handled by the query, Oracle adapts its behavior by using different execution plans for the query instead of sticking to the same plan for all bind values. Since adaptive cursor sharing works only where literal values are replaced with binds, Oracle encourages you to use the FORCE setting for the cursor_sharing parameter. If you set the parameter to SIMILAR and you have a histogram on a column, the optimizer doesn't perform a literal replacement with bind variables, and thus adaptive cursor sharing won't take place. You must set the cursor_sharing parameter to FORCE for adaptive cursor sharing to work, thus letting the optimizer select the optimal execution plan for different values of the bind variable.

DB_FILE_MULTIBLOCK_READ_COUNT

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans thereby reducing the time taken for the execution of a Query. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

The parameter db_file_multiblock_read_count is only applicable for tables/indexes that are full scanned, but it also effects the SQL optimizer in its calculation of the cost of a full-table scan.

If DB_FILE_MULTIBLOCK_READ_COUNT is not set or is set to 0 (zero), then the optimizer uses a value of 8 for costing.

Before 10g R2, DBA's used the db_file_multiblock_read_count initialization parameter to tell Oracle how many block to retrieve in the single I/O operation.

HASH_AREA_SIZE

HASH_AREA_SIZE is relevant to parallel execution operations and to the query portion of DML or DDL statements. It specifies the maximum amount of memory, in bytes, to be used for hash joins.Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead.

The hash_area_size parameter value defaults to 1.5 times sort_area_size and is used for performing hash joins of Oracle tables. The higher the value for hash_area_size, the higher the propensity for the CBO to use a hash join.

SORT_AREA_SIZE

The sort_area_size parameters control the RAM size for dedicated tasks to sort SQL result sets and reduce expensive disk sorts. 

The sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto

SORT_AREA_SIZE is an upper bound on the memory we dynamically allocate for a sort

WORKAREA_SIZE_POLICY

WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.The WORKAREA_SIZE_POLICY parameter tell the server that it should take over PGA memory management. The PGA_AGGREGATE_TARGET parameter specifies the total amount of memory the server can allocate to the PGA.

•AUTO

Work areas used by memory-intensive operators are sized automatically, based on the PGA memory used by the system, the target PGA memory set in PGA_AGGREGATE_TARGET, and the requirement of each individual operator. You can specify AUTO only when PGA_AGGREGATE_TARGET is defined.

•MANUAL

The sizing of work areas is manual and based on the values of the *_AREA_SIZE parameter corresponding to the operation (for example, a sort uses SORT_AREA_SIZE). Specifying MANUAL may result in sub-optimal performance and poor PGA memory utilization.

QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY

QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database making use of the materilized views  and function based indexes while choosing the best optimized path.To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.

There are three acceptable values for query_rewrite_integrity:

enforced (default) - Presents materialized view with fresh data

trusted - Assumes that the materialized view is current

stale_tolerated - Presents materialized view with both stale and fresh data

STATISTICS_LEVEL

This is an important parameter for the collection of statistics on the Database Objects becasue the optimizer chooses the best execution path based on the statistics available on the Database objects as Oracle Optimizer is a cost based optimizer.

STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

•Automatic Workload Repository (AWR) Snapshots

•Automatic Database Diagnostic Monitor (ADDM)

•All server-generated alerts

•Automatic SGA Memory Management

•Automatic optimizer statistics collection

•Object level statistics

•End to End Application Tracing (V$CLIENT_STATS)

•Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)

•Service level statistics

•Buffer cache advisory

•MTTR advisory

•Shared pool sizing advisory

•Segment level statistics

•PGA Target advisory

•Timed statistics

•Monitoring of statistics