Clustering Factor - a key metrics for SQL Tuing in Oracle 11g

We will discuss the following points in this article:

1) What is clustering factor?
2) How the clustering factor is being calculated?
3) How to interpret clustering factor?
4) Bad Clustering factor Vs Good Clustering factor
5) Demonstration
6) FAQ

What is clustering factor?
The clustering factor is a number which represent the degree to which data is randomly distributed in a table. In simple terms it is the number of “block switches” while reading a table using an index. Alternatively, The clustering factor is a measure of the ordered-ness of an index in comparison to the table that it is based on. It is used to check the cost of a table lookup following an index access (multiplying the clustering factor by index’s selectivity gives you the cost of the operation).

Note:  A table with a high clustering factor is out-of-sequence with the rows and large index range scans will consume lots of I/O.

How the clustering factor is being calculated?
To calculate the clustering factor of an index during the gathering of index statistics, Oracle does the following:
   - For each entry in the index Oracle compares the entry's table rowid block with the block of the previous index entry. If the block is different, Oracle increments the clustering factor by 1.
   - The minimum possible clustering factor is equal to the number of blocks identified through the index's list of rowid's -- for an index on a column or columns containing no nulls, this will be equal to the number of blocks in the table that contain data. The maximum clustering factor is the number of entries in the index.

How to interpret clustering factor? 
So this means that Oracle now has a statistic to allow it to estimate how many table blocks would be associated with an index range scan.

If the clustering factor is close to the number of entries in the index, then an index range scan of 1000 index entries may require nearly 1000 blocks to be read from the table.

If the clustering factor is close to the number of blocks in the table, then an index range scan of 1000 index entries may require only 50 blocks to be read from the table.

This can be compared with the cost of reading the entire table up to the high-water mark (using the more efficient multiblock i/o mechanism) to determine whether a full table scan or an index range scan offers the most efficient access mechanism.

Note that where extensive deletes have occurred from the table there may be blocks with no rows in. These will be accounted for in the clustering factor because those blocks will not appear in the index's rowid list. The full table scan will still read all table blocks up to the high water mark, regardless of whether they contain rows or not. So in an extreme case it is possible that Oracle could see from the index and table statistics that although a table has 1,000,000 blocks below the high water mark, reading 100% of the rows in the table might only require reading 10 of those blocks. Providing that "Not Null" constraints tell Oracle that all table rows are present in the index, a query such as "select * from big_table_with_few_rows" might be more efficiently satisfied with an index range scan than with a full table scan.

Note also that calculating the clustering factor is done without reference to the table at all -- it is based solely on information contained in the index.


Bad Clustering factor Vs Good Clustering factor

Fig-1 : Bad CF

The above diagram explains that how scatter the rows of the table are. The first index entry (from left of index) points to the first data block and second index entry points to second data block. So while making index range scan or full index scan, optimizer have to switch between blocks and have to revisit the same block more than once because rows are scatter. So the number of times optimizer will make these switches is actually termed as “Clustering factor”.

Fig-2 : Good CF

The above image represents "Good CF”. In an event of index range scan, optimizer will not have to jump to next data block as most of the index entries points to same data block. This helps significantly in reducing the cost of your SELECT statements.

Clustering factor is stored in data dictionary and can be viewed from dba_indexes (or user_indexes)

Click here to read about Selectivity, Clustering, and Histograms (Burleson Consulting)

Demonstration:

SQL> create table CF_TEST as select * from all_objects;
Table created.

SQL> create index obj_id_indx on CF_TEST(object_id);
Index created.

SQL> select clustering_factor from user_indexes where index_name='OBJ_ID_INDX';

CLUSTERING_FACTOR
-----------------
             2165
SQL> 
SQL> SQL> select count(*) from CF_TEST;

  COUNT(*)
----------
    103090

SQL> select blocks from user_segments where segment_name='OBJ_ID_INDX';

    BLOCKS
----------
       256

The above example shows that index has to jump 2165 times to give you the full data had you performed full table scan using the index.

Note:
- A good CF is equal (or near) to the values of number of blocks of table.
- A bad CF is equal (or near) to the number of rows of table.

Myth:
Rebuilding of index can improve the CF.

Tip:
The clustering of data within the table can be used to improve the performance of statements that perform range scan–type operations. By determining how the column is being used in the statements, indexing these column(s) may provide a great benefit.

The clustering factor records the number of blocks that will be read when scanning the index. If the index being used has a large clustering factor, then more table data blocks have to be visited to get the rows in each index block (because adjacent rows are in different blocks). If the clustering factor is close to the number of blocks in the table, then the index is well ordered, but if the clustering factor is close to the number of rows in the table, then the index is not well ordered. The clustering factor is computed by the following (explained briefly):

• The index is scanned in order.
• The block portion of the ROWID pointed at by the current indexed valued is compared to the previous indexed value (comparing adjacent rows in the index).
• If the ROWIDs point to different TABLE blocks, the clustering factor is incremented (this is done for the entire index).

The CLUSTERING_FACTOR column in the USER_INDEXES view gives an indication as to how organized the data is compared to the indexed columns. If the value of the CLUSTERING_FACTOR column value is close to the number of leaf blocks in the index, the data is well ordered in the table. If the value is not close to the number of leaf blocks in the index, then the data in the table is not well ordered. The leaf blocks of an index store the indexed values as well as the ROWIDs to which they point.

For example, say the CUSTOMER_ID for the CUSTOMERS table is generated from a sequence generator, and the CUSTOMER_ID is the primary key on the table. The index on CUSTOMER_ID would have a clustering factor very close to the number of leaf blocks (well ordered). As the customers are added to the database, they are stored sequentially in the table in the same way the sequence numbers are issued from the sequence generator (well ordered). An index on the CUSTOMER_NAME column would have a very high clustering factor, however, because the arrangement of the customer names is random throughout the table.

The clustering factor can impact SQL statements that perform range scans. With a low clustering factor (relative to the number of leaf blocks), the number of blocks needed to satisfy the query is reduced. This increases the possibility that the data blocks are already in memory. A high clustering factor relative to the number of leaf blocks may increase the number of data blocks required to satisfy a range query based on the indexed column.

Note: Using the "alter table xxx shrink space" command will change the clustering_factor for the primary index on the table.  This, in turn, could cause dynamic statistics to generate different SQL execution plans.  Hence, you should rebuild or coalesce your indexes whenever you issue the alter table shrink space command.  This will ensure that no SQL changes plans.

FAQ:
1) The clustering_facotr column in the user_indexes view is a measure of how organized the data is compared to the indexed column, is there any way i can improve clustering factor of a index. or how to improve it?

Ans:
Already we discussed. Again trying to elaborate.

It tells us how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered.  If it approaches the number of rows in the table, the rows are randomly ordered.  In such a case (clustering factor near the number of rows), it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.

Note that typically only 1 index per table will be heavily clustered (if any).  It would be extremely unlikely for 2 indexes to be very clustered. If you want an index to be very clustered -- consider using index organized tables.  They force the rows into a specific physical location based on their index entry.

Otherwise, a rebuild of the table is the only way to get it clustered (but you really don't want to get into that habit for what will typically be of marginal overall improvement).

2) What is well order of Index as part of clustering factor?
Ans:
In general, if all of the index entries in a given leaf block point to the same block, then
the table is well ordered with regards to this index.

If all of the index entries in a given leaf block point to different blocks in the table , then
the table is not well ordered with respect to this index.

when an index should be rebuilt?

 Concept:

An Oracle server index is a schema object that can speed up the retrieval of rows by using a pointer.

You can create indexes on one or more columns of a table to speed SQL statement execution on that table. If you do not have an index on the column, then a full table scan occurs.

You can reduce disk I/O by using a rapid path access method to locate data quickly. By default, Oracle creates B-tree indexes.

After a table experiences a large number of inserts, updates, and deletes, the index can become unbalanced and fragmented and can hinder query performance.

How to determine an index needs to be rebuilt?

We must first get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command.

The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer. 

The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.

Below is a sample output from INDEX_STATS Table.

SQL> ANALYZE INDEX IDX_EMP_ACCT VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

NAME                      HEIGHT    LF_ROWS    LF_BLKS    DEL_LF_ROW
---------------------- -----------   ----------      ----------   ----------------
DX_EMP_ACCT           2             1                     3               6

1 row selected.

There are two rules of thumb to help determine if the index needs to be rebuilt.
1)     If the index has height greater than four, rebuild the index.
2)     The deleted leaf rows should be less than 20%.

If it is determined that the index needs to be rebuilt, this can easily be accomplished by the ALTER INDEX <INDEX_NAME> REBUILD | REBULID ONLINE command. It is not recommended, this command could be executed during normal operating hours. The alternative is to drop and re-create the index. Creating an index uses the base table as its data source that needs to put a lock on the table. The index is also unavailable during creation.

 In this example, the HEIGH column is clearly showing the value 2. This is not a good candidate for rebuilding. For most indexes, the height of the index will be quite low, i.e. one or two. I have seen an index on a 2 million-row table that had height two or three. An index with height greater than four may need to be rebuilt as this might indicate a skewed tree structure. This can lead to unnecessary database block reads of the index. Let’s take another example.

SQL> ANALYZE INDEX IDX_EMP_FID VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)
*100 as ratio FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- -------
IDX_EMP_FID                                  1          189         62        32.80

1 row selected.

In this example, the ratio of deleted leaf rows to total leaf rows
is clearly above 20%. This is a good candidate for rebuilding.
Let’s rebuild the index and examine the results

SQL> ANALYZE INDEX IDX_EMP_FID REBUILD;

Statement processed.

SQL> ANALYZE INDEX IDX_EMP_FID VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*
100 as ratio FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
------------------------------ ---------- ---------- ---------- -------
IDX_EMP_FID                                  1          127         0        0

1 row selected.

Examining the INDEX_STATS table shows that the 62 deleted leaf rows were dropped from the index. Notice that the total number of leaf rows went from 189 to 127, which is a difference of 62 leaf rows (189-127). This index should provide better performance for the application.

Script to rebuild indexes:

It is very difficult to write a script that will identify indexes that will benefit from rebuilding because it depends on how the indexes are used.  For example, indexes that are always accessed vis an index unique scan" will never need rebuilding, because the "dead space" does not interfere with the index access. 

Only indexes that have a high number of deleted leaf blocks and are accessed in these ways will benefit from rebuilding:

• index fast full scan
• index full scan
• index range scan

Getting statistically valid: proof from a volatile production system would be a phenomenal challenge.  In a large production system, it would be a massive effort to trace LIO from specific queries to specific indexes before and after the rebuild.

Still you can use below script to rebuild index after all verification:

Select 'alter index ' || owner || '.' || index_name || ' rebuild online;'

  from all_indexes

 where owner='XXX'

 and index_type not in ('DOMAIN', 'BITMAP','FUNCTION-BASED NORMAL','IOT - TOP')

 order by owner, index_name;

Note: Only rebuilt B-tree indexes as a global concept.

Is deleted leaf blocks are reused?

Yes. but depends upon how soon data will be reinserted and while B-Tree will balance the tree will reuse it.

Sample Test:

SQL> create table test_empty_block (id number, value varchar2(10));
Table created.
SQL> begin
2 for i in 1..10000 loop
3 insert into test_empty_block values (i, 'Bowie');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create index test_empty_block_idx on test_empty_block (id);
Index created.


SQL> delete test_empty_block where id between 1 and 9990;
9990 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select lf_blks, del_lf_rows from index_stats;
LF_BLKS DEL_LF_ROWS
---------- -----------
21             9990


Now reinsert a similar volume but after the last current values
SQL> begin
2 for i in 20000..30000 loop
3 insert into test_empty_block values (i, 'Bowie');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select lf_blks, del_lf_rows from index_stats;
LF_BLKS DEL_LF_ROWS
---------- -----------
21             0Note all empty blocks have been reused and deleted rows cleanout.

Following select statement was executed after the 9990 deletions in previous example

SQL> select /*+ index test_empty_blocks */ * from test_empty_blocks
where id between 1 and 100000;
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_EMPTY_BLOCKS'
2 1 INDEX (RANGE SCAN) OF 'TEST_EMPTY_BLOCKS_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
549 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

See more from Oracle Doc ID 1373415.1

Oracle PL/SQL programming common mistakes

Points covered:

1) Formatting data in views
2) Hardcoding local Varchar2 variable size
3) Ignoring exceptions
4) Not using bound variables for changing parameters
5) Storing empty LOBs
6) Too many levels of views
7) Transactional control in non-autonomous procedures
8) Using Sequence nextval without curval
9) Using bound variables for constants
10) Using derived column values for existence checks
11) Using non-dedicated packages for continuous jobs
12) Wrapping everything into stored procedures
13) Use of Truncate for Global Temporary Table(GTT)
14) Using non-deterministic functions directly in conditions
15) Catch-all error handling

I obsrved lots of time in pl/sql code, there are some common mistakes while writing codes for various environments. If codes are written for OLTP applications, these kind of mist be avoided. Here are my descriptions for all the above points with root cause analysis and solution.

1) Formatting data in views

Severity: Makes system harder to use

Symptoms:
• to_char used on date or numeric values in views
• strings concatenated in view code to form pretty printed values

Why is this bad?
• data format cannot be easily modified on the front-end, since some information may be lost
• values cannot be easily modified (i.e. applying time zone shifting becomes much harder)
• filtering based on underlying date or string values becomes much more processor-heavy and requires full table scans and/or substring matching/comparisons.
• internationalisation becomes much harder — instead of translating elements and then combining them, translation engines must analyse and translate/reformat formatted data

Solutions:
• Format data on the front-end, not in the database.
• Perform formatting in queries coming from the front end, specifying exactly what the front end needs
— but database views should not suppose any specific data format.


2) Hardcoding local Varchar2 variable size

Severity: Makes system harder to use

Symptoms
• PL/SQL function or procedure declares local Varchar2 variables for temporary storage of table values, with hard-coded length
• Views declared with Varchar2 types with hard-coded length

Why is this bad?
• Code is error prone, because hard-coded values may not allow for enough space to store the entire value coming from a database table.
• Even if the size is correct, if the underlying type ever changes, errors such as ORA-06502 ‘Character string buffer too small’ may start appearing in procedures.

Solution:

use %TYPE to reference the underlying column type instead of hard-coding the type and size for local variables.

Exceptions
• variables and fields not related to underlying table data
• fields or variables that combine several table fields

3) Ignoring exceptions

Severity: Risk of data corruption

Symptom

This is a typical example:

begin
...
Exception When others then
 NULL;
end;

This kind of code is written when errors such as attempts to insert a duplicate record or modify a nonexisting row should not affect the transaction. It is also common in triggers that must be allowed to fail without effecting the operation which caused them (best-effort synchronisation with an external resource) Less frequently, this code is written by junior developers who do not knowing what to do in case of an error, so they just disregard exceptions.

Why is this bad?

Serious errors such as storage problems or table mutations might be hidden from the calling code

Solutions
• If you do not want any errors to affect current transaction, execute the code in an autonomous transaction and log errors to an error table/log table. For critical functions implement some sort of administrative notifications for those errors. For low priority functions, check the log table to periodically for errors.
• If you want to ignore certain exceptions, because they can be solved by re-processing, handle only those specific exceptions.

Exception

low-risk functions where any errors can safely be ignored

4) Not using bound variables for changing parameters

Severity: Reduced performance

Symptom
Frequently executing the same query with different parameter values, but specifying parameter values literally, without using bound variables.

Why is this bad?
• Database engine will have to compile the query every time and will not be able to cache the statement.
• If care is not taken to prevent SQL injection, may open a security hole in the system.

Solution
For all parameters that are genuinely changing, use a bound variable instead of specifying the value literally.

Exceptions
• Ad-hoc queries that are run only once or infrequently
• Parameters where statement caching is pointless for different values

5) Storing empty LOBs

Severity: Reduced performance

Symptom
Empty CLOB values used instead of NULL for CLOB fields that do not hold a value

Why is this bad?
Oracle allocates space for EMPTY CLOBs. In tables with large number of empty CLOB columns, this can take up significant storage space.

Solution
Use NULL instead of EMPTY CLOB

6) Too many levels of views

Severity: Reduced performance

Symptom
A large hierarchy of views containing sub-views or subqueries is in place. Such hierarchy is usually established as several layers of abstraction over abstraction, typically when adding new core features to underlying models, but keeping client API for backward compatibility using a set of higher-level views.

Why is this bad?
Optimiser will give up and run full table scans even if indexes could be used after typically 8 or 9 levels of nesting.

Solutions
• Flatten the structure so that it has less than 8 levels. Use joins instead of subqueries where possible
• Use materialised views to cut off a part of the hierarchy
• If materialised views cannot be used for performance reasons, use an aggregated table maintained by triggers to do the same.

7) Transactional control in non-autonomous procedures

Severity: Risk of data corruption

Symptom
Commit or rollback statement in a stored procedure or function without PRAGMA AUTONOMOUS TRANSACTION

Why is this bad?
Effectively prevents stored procedures from being used in a wider context — rolling back or committing inside a stored procedure will delete/permanently write data that was used in a wider transaction, in the middle of that transaction.

May cause issues that are very hard to trace/debug - you will not be able to check if data was processed correctly when the procedure rolled back. audit logs will contain references to records which, from the logical point of view, never existed.

May cause inconsistent data — since rolling back/committing will split a wider logical transaction into two — one which rolled back and another one which is running, relational constraints might fail in the secondtransaction. even worse, if the relational constraint checks were not enforced, inconsistent data might be written permanently.

Solutions
• Throw exceptions in case of errors; let the caller decide what to do in case of error. Do noting in case the operation succeeded — let the caller decide if the entire wider transaction is correct or not.
• Add PRAGMA AUTONOMOUS_TRANSACTION; to the procedure header to make it run as an autonomous transaction

Exceptions
• Long-running worker procedures such as batch updates (may include suboperations and save-points to store partial results. should be marked as autonomous transaction).
• Auditing (should be done with autonomous transactions)

8) Using Sequence nextval without curval

Severity: Risk of data corruption

Symptoms
• Sequence currval method used in a procedure or trigger without calling nextval first — typically in a trigger that updates a log record, or a procedure that partially processes data
• sequence currval used to calculate the next value which will be read by nextval

Why is this bad?
• Calling the method will cause an exception if the sequence is not initialised — so the method/trigger
depends on the caller to initialise the sequence first
• Procedures relying on someone else to initialise the sequence must be called in a specific context, which limits their reuse
• Triggers may or may not work depending on the order of execution
• If procedure/trigger uses the current sequence value to update the relevant record, calling it in a different context/order of execution may update the wrong record
• Sequences are not transactional — they can be cached or changed in another session between calls to curval and nextval. currval+1 is not guaranteed to be the next value; using that to predict IDs is very dangerous, as it can lead to wrong records being deleted or updated.

Solutions
• Do not use currval to read contextual information. Pass IDs explicitly to procedures or use sys_context to store contextual information
• Use nextval instead of currval where appropriate (if you just need a unique number)

9) Using bound variables for constants

Severity: Reduced performance

Symptoms
• Bound variables used in queries for values that are never changing (often when client developers bind all variables in a query).
• Bound variables used for parameters where actual value can significantly effect the optimisation plan Why is this bad?
• Optimiser will not use the most efficient plan to execute the query
• If variable peeking is turned on, it might actually chose a completely wrong execution plan for subsequent runs

Solution
For values that are not changing in a query, or where statement caching does not make sense, use a literal and not a bound variable (this does not imply that other genuine parameters in the same query should not be bound).

10) Using derived column values for existence checks

Severity: Reduced performance

Symptom
count, sum or some other aggregate function (i.e. custom made csv) executed in a subquery, and then
compared to 0 or 1 or NULL in the WHERE clause of the outer query in order to check whether at least one (or exactly one) related element in the subsctructure exists; results of the aggregate function are then discarded (not passed on through the outer view).

The subquery was typically developed first, possibly as a full-fledged view, and then the outer query/view, which filters results of the first view; was written. Less frequently they are in the same view, first one used as a subquery.

CREATE OR REPLACE instrument_v AS
SELECT instr.idinstr, instr.name, count(opt.type) AS activeoptiontypes
FROM instrument instr, instrumentoption opt WHERE instr.ididinstr=opt.idinstr (+)
GROUP BY instr.idinstr, instr.name;
...
SELECT idinstr, name FROM instrument_v WHERE activeoptiontypes >0

Why is this bad?

Instead of just checking indexes, potentially a large number of records will have to be read and analysed.

Solution
Instead of comparing the results of aggregate function, create a different view that checks directly for existence of the related type — optimiser will be able to execute the plan using indexes and will not have to calculate aggregate functions for irrelevant rows


11) Using non-dedicated packages for continuous jobs

Severity: Makes system harder to use

Symptoms
• A continuous or frequent database job executes a long-running procedure from a pl/sql package. That same package is also used for client access or by other schemas.
• The body for a continuous or frequent database job is a procedure from such a package.

Why is this bad?
The package will be locked while the procedure is running. With a continuous or frequently running job, this may require interrupting the job or causing down-time even for the smallest changes like granting execution to other users/schemas to the package.

Solution
Extract procedures for job bodies into dedicated pl/sql packages or into standalone procedures.

12) Wrapping everything into stored procedures

Severity: Makes system harder to use

Symptoms

• Client access layer consists exclusively or mostly of stored procedures - both for writing and for reading. Ref Cursor output parameters of stored procedures used to read data.
This is typical for SQLServer programmers moving to Oracle, due to earlier SQL Server limitations in terms of access privileges and functionality. It is also often done under a false pretext that using stored procedures is more secure or brings better performance. In fact, the same security restrictions can be applied to views as to stored procedures in Oracle. If bound variables and statement caching are used, query access to views also brings pre-compilation benefits, so there is no performance gain.

• Stored procedures are used to fill in additional details when inserting or updating data (automatically calculated columns, timestamps and similar).

Why is this bad?

• Procedural access limits operations to a choice of parameters - i.e. deleting records with specific name. You end up by providing a stored procedure for every possible combination of parameters, or by providing “generic” stored procedures that are no better than allowing direct table access.

• Procedural access is typically done on row-level (procedures work with a single row), which has significant performance penalties over tabular access for operations that work on multiple rows.
• Output Ref Cursor parameters do not allow clients to apply further filters or conditions
• Output Ref Cursor cannot be easily joined with additional data
• Output Ref Cursors data cannot be easily paged on demand
• Packaging data-manipulation steps in a stored procedure does not prevent someone from using the
table differently and inserting or modifying data directly. Your rules will only be applied if the clients
are forced to use your stored procedures. As the code base grows, and different programmers join and
leave the team, this will be harder and harder to enforce.

Solutions

• Use views for reading data, do not use stored procedures; instead of passing filter parameters to stored procedures, expose those values as view columns and allow clients to filter using Where. Apply appropriate security restrictions to views.

• Use views for inserting and updating rows if the operation requires dynamic parameters.
• If all required columns in views are not updatable, consider using instead-of triggers to provide the
functionality
• Use triggers to perform action such as populating missing columns on data modification on tables. This ensures that the data will be populated regardless of how the table is used.
• Use stored procedures to encapsulate business rules and procedural processing logic; If front-end requires data that’s calculated from database column values, rather than stored in them, encapsulate that logic in functions and include functions in views. Procedures/functions should be used to extract common procedural steps from triggers and to simplify triggers and jobs.

Exceptions

• Output REF CURSOR objects should be used when filtering view columns for read-only access severely effects performance — for example when subqueries have aggregate functions and filtering outer view would cause query data to be executed on all data, and then filtering it. In this case, inserting specific values into the subquery could significantly reduce execution time.
• This technique can also be used to provide an uniform API for related reports, where query depends on parameter values or supplied parameters

13) Use of Truncate for Global Temporary Table(GTT).

Severity: Reduced performance

Symptom

Database some times dam slow or hung.

Why is this bad?

As a bad practice, developers used to write "delete" statement without any where cluase. It will reduce performance and so many other side effects. To over come this situation developer started writting "TRUNCATE". This is bettter use but more use of truncate in application will create bad impact on performance also. Based on scope it is better to define the table as GTT and use it. But still developers are used to write TRUNCATE for GTTs. GTTs are auto truncate when session will expire.

Solution

When you writting TRUNCATE for general table use below syntax:

TRUNCATE TABLE [schema_name.]table_name
  [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
  [ DROP STORAGE | REUSE STORAGE ] ;

e.g.,  TRUNCATE scott.emp reuse storage;


When you are using GTT, then dont use TRUNCATE.

Note :

DROP STORAGE Specify DROP STORAGE to deallocate all space from the deleted rows from the table or cluster except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default.

REUSE STORAGE Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table or cluster. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations. This clause leaves storage parameters at their current settings.

If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.

14) Using non-deterministic functions directly in conditions

Severity: Reduced performance

Symptom
Non-deterministic function that does not depend on current row values (or depends on a subset of row values) is used in Where clause. Examples might be functions that fetch data from a referential table depending on current database context, or perform some calculations on derived data; Functions are not enclosed in select from dual or a subquery.

Why is this bad?
Functions may be executed much more times than required. It might be sufficient to execute the function just once per query, but Oracle might execute the function for each row of the result (or even worse, for each row of the source).

Solutions
• Turn function into (select function() from dual) if it does not depend on any row values
• Move function into a similar subquery if it depends on referential data, and join the subquery in the
main query
• Mark function as deterministic if it is by nature deterministic (i.e. always returns same result for same parameter values)
• Use Oracle 11 result caching if possible

15) Catch-all error handling

Severity: Risk of data corruption

Symptom
A catch-all exception block (WHEN OTHERS THEN) used to process an error (or a group of errors) in a PL/SQL procedure. This is done typically either to ignore an expected error which should not affect a transaction (for example, when duplicate key in index should be disregarded, or if a problem with inserting should be quietly logged). Catch-all error handling might also be written when a single block of code is expected to throw several types of exceptions (no data found, duplicate value on index, validation…) and the developer wants to handle them all at once.

Another example is using WHEN OTHERS block to catch domain exceptions thrown by RAISE ERROR because they cannot be individually checked.

Why is this bad?
• Catch-all block will prevent other unexpected errors from propagating. For example, a mutating table error caused by a trigger may be hidden with a catch-all block, completing the transaction successfully when it did not really do everything it needed.
• There is often an assumption about the error that can occur, which may be incorrect when a different type of exception is thrown. This may lead to inconsistent data. For example, if a catch-all block is used to insert a record in case of a missing data error, then that record may be inserted on any other error as well.

Solutions
• Do not use WHEN OTHERS to check for a particular exception such as NO_DATA_FOUND. Check directly for a particular type of exception.
• To handle domain-specific errors, declare your domain exceptions so that you can check for a particular exception later in the code.
• Do not assume that you know all errors that may occur at a given place in the code. Storage problems, mutating tables and similar issues should surface to the client, and not be handled and discarded in an unrelated piece of the code.

Exception
Shielding parts of the system from errors in other parts

will continue...in next publish

Query Optimization - sql tunig

Optimize queries where column contains NULL values-
Can rewriting of query resolve issue for NULL value when used in where condition?

Query Performance Issue due to NVL(:b1,column_name) predicates in the WHERE clause. Due to these predicates, the Optimizer computed IncorrectCardinality and came out with a Sub-Optimal Plan.Issue can be solved by way of a workaround. Since, this query is a seeded query, the permanent fix (suggested in this blog) is expected by way of an Application Patch.

I will demonstrate this on my SCOTT Schema and a Query on EMP table. This will be easier for me to explain as well. Based on the EMP table, the requirement is to write a report that takes 2 Inputs. These are EMPNO and JOB. The users can run this report for any of the following conditions :

1.EMPNO and JOB are NOT NULL
2.EMPNO IS NULL and JOB IS NOT NULL
3.EMPNO IS NOT NULL AND JOB IS NULL


The way Original Query is written, I assumed the Developers had above 4 requirements in mind. However, at the production site, the customerconfirmed that only the 1st two conditions are applicable. Out of the total execution, 1st condition contributes to around 70% and 2ndcontributes to 30%.Back to our example on EMP table. With the 4 conditions in mind, any Developer would write a query as mentioned below.

select empno, ename, job, hiredate,deptno
from   emp
where  (empno=:b1 or :b1 is NULL)
and  (JOB=:b2 OR :b2 is null);

## Execution of this query for each of the combination
## Both are NOT NULL (For a JOB & for an Employee)

select empno, ename, job, hiredate,deptno
from   emp
where empno=1265 and JOB='MANAGER';


EMPNO ENAME      JOB       HIREDATE    DEPTNO
--------- ---------- --------- ----------- ------
1265 GOURANGA   MANAGER   07-Dec-2010     10

## EMPNO IS NULL (For a JOB and all Employees)
exec :JOB:=MANAGER; :empno:=null;

    EMPNO ENAME      JOB       HIREDATE    DEPTNO
--------- ---------- --------- ----------- ------
     1265 GOURANGA   MANAGER   07-Dec-2010     10
     7566 JONES      MANAGER   02-Apr-1981     20
     7698 BLAKE      MANAGER   01-May-1981     30
     7782 CLARK      MANAGER   09-Jun-1981


## JOB IS NULL (For an Employee)
exec :JOB:=null; :empno:=1265;

EMPNO ENAME      JOB       HIREDATE    DEPTNO
--------- ---------- --------- ----------- ------
1265 GOURANGA   MANAGER   07-Dec-2010     10

## Both are NULL (for all JOB and all Employees)
exec :JOB:=null; :empno:=null;

EMPNO ENAME      JOB       HIREDATE    DEPTNO
----- ---------- --------- ----------- ------
 1265 GOURANGA   MANAGER   07-Dec-2010     10
 7839 KING       PRESIDENT 17-Nov-1981     10
 7698 BLAKE      MANAGER   01-May-1981     30
 7782 CLARK      MANAGER   09-Jun-1981     10
 7566 JONES      MANAGER   02-Apr-1981     20
 7788 SCOTT      ANALYST   19-Apr-1987     20
 7902 FORD       ANALYST   03-Dec-1981     20
 7369 SMITH      CLERK     17-Dec-1980     20
 7499 ALLEN      SALESMAN  20-Feb-1981     30
 7521 WARD       SALESMAN  22-Feb-1981     30
 7654 MARTIN     SALESMAN  28-Sep-1981     30
 7844 TURNER     SALESMAN  08-Sep-1981     30
 7876 ADAMS      CLERK     23-May-1987     20
 7900 JAMES      CLERK     03-Dec-1981     30
 7934 MILLER     CLERK     23-Jan-1982     10

A single query meets the requirement for all the 4 combinations. The Developer, in this case, has done his job. However, they have not considered the fact that 2 out of 4 of the above combinations would end up doing a Full Table Scan of EMP table. In case of the customer case, since only the 1st two combinations are applicable and with 30% of the executions on combination 2, 30% of the time, the Optimizer would opt for a Full Table Scan. Before, we get into the Original case, let us check the runtime execution plan for the Query on EMP Table.


SQL> connect scott
Enter password:
Connected.
SQL> explain plan for
  2  select empno, ename, job, hiredate,deptno
from   emp
where  (empno=:b1 or :b1 is NULL)
and  (JOB=:b2 OR :b2 is null);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    29 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    29 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(("JOB"=:B2 OR :B2 IS NULL) AND (:B1 IS NULL OR
              "EMPNO"=TO_NUMBER(:B1)))

14 rows selected.

SQL>

From the predicate information, the Optimizer choice becomes very clear, which is “IF :EMPNO is NULL then FTS of EMP and IF :EMPNO is NOT NULL then Table Access is also going for FTS. This means, for optimizer there is no way to take any predicate as option to use index.

Then, I re-written the query like below and most of cases optimizer used index. Have a look on execution plan:

SQL> set lines 120;
SQL> explain plan for
  2  select empno, ename, job, hiredate,deptno
from   emp
where  empno=:b1 and  (JOB=:b2 OR :b2 is null)
union
select empno, ename, job, hiredate,deptno
from   emp
where  JOB=:b2
and  (empno=:b1 OR :b1 is null)
union
select empno, ename, job, hiredate,deptno
from   emp
where  JOB=:b2 and empno=:b1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 4286588931

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     3 |    87 |     7  (86)| 00:00:01 |
|   1 |  SORT UNIQUE                  |           |     3 |    87 |     7  (86)| 00:00:01 |
|   2 |   UNION-ALL                   |           |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    29 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | IDX_EMPNO |     1 |       |     0   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    29 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
|*  6 |     INDEX RANGE SCAN          | IDX_JOB   |    11 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    29 |     1   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN         | IDX_EMPNO |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("JOB"=:B2 OR :B2 IS NULL)
   4 - access("EMPNO"=TO_NUMBER(:B1))
   5 - filter(:B1 IS NULL OR "EMPNO"=TO_NUMBER(:B1))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
   6 - access("JOB"=:B2)
   7 - filter("JOB"=:B2)
   8 - access("EMPNO"=TO_NUMBER(:B1))

25 rows selected.

SQL>

In this case if user inputs :EMPNO, then optimzer use the index IDX_EMPNO. If user inputs :JOB, then optimizer use IDX_JOB. If both are not null then optimizer will cheaper plan and will use unique constraint based IDX_EMPNO index. Rather than these condition and rare cases optimize use FTS when both are NULL.

Here developer may think why more UNION, it may affect the performance or it may create other issue or side effect for the whole application.

Actually when we see the above execution plan ( Plan hash value: 4286588931), there is no issue with optimer and database. Query may looks bit complex but no other issues.

In real-time scenario, you may apply this and have patience while write big-big queries.

Another simpler way you can write in pl-sql sub-programs with implementing if-else structure.

Thanks.

Measure Index Selectivity: Create effective index - Tips and Tricks

Measure Index Selectivity: Create an effective index

About Index Selectivity:

In RDBMS databases, like Oracle, Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return a small portion of a table's rows.

Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries. Otherwise, they just take up space and add overhead when the indexed columns are updated. You should use the EXPLAIN PLAN feature to determine how the indexes are being used in your queries. Sometimes, if an index is not being used by default, you can use a query hint so that the index is used.

Basically, B*Tree Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows. This value may be higher in situations where all data can be retrieved from an index, or where the indexed columns can be used for joining to other tables.

The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index. The ideal selectivity is 1. Such a selectivity can be reached only by unique indexes on NOT NULL columns.

Example with good Selectivity :

A table having 100'000 records and one of its indexed column has 88000 distinct values, then the selectivity of this index is 88'000 / 10'0000 = 0.88.

Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints. These indexes are the most selective and the most effective in optimizing performance. The selectivity of an index is the percentage of rows in a table having the same value for the indexed column. An index's selectivity is good if few rows have the same value.

Example with bad Selectivity :

lf an index on a table of 100'000 records had only 500 distinct values, then the index's selectivity is 500 / 100'000 = 0.005 and in this case a query which uses the limitation of such an index will retum 100'000 / 500 = 200 records for each distinct value. It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.

How to Measure Index Selectivity ?

Manually measure index selectivity :

The ratio of the number of distinct values to the total number of rows is the selectivity of the columns. This method is useful to estimate the selectivity of an index before creating it.

SQL> select count (distinct job) “Distinct Values” from emp;

Distinct Values
---------------
              5

SQL> select count(*) “Total Number Rows” from emp;

Total Number Rows
-----------------
               14

Selectivity = Distinct Values / Total Number Rows
            = 5 / 14
            = 0.35

Automatically measure index selectivity  :

We can determine the selectivity of an index by dividing the number of distinct indexed values by the number of rows in the table.

SQL> create index idx_emp_job on emp(job);
SQL> analyze table emp compute statistics;

OR

SQL> exec dbms_stats.gather_table_stats('owner_name','table_name',cascade => TRUE);

SQL> select distinct_keys from user_indexes
where table_name = 'EMP' and index_name = 'IDX_EMP_JOB';

DISTINCT_KEYS
-------------
            5

SQL> select num_rows from user_tables where table_name = 'EMP';

NUM_ROWS
---------
       14

Selectivity = DISTINCT_KEYS / NUM_ROWS = 0.35

 Selectivity of each individual Column :

Assuming that the table has been analyzed it is also possible to query USER_TAB_COLUMNS to investigate the selectivity of each column individually.

SQL> select column_name, num_distinct from user_tab_columns where table_name = 'EMP';

COLUMN_NAME                     NUM_DISTINCT
------------------------------ ------------
EMPNO                                         14
ENAME                                         14
JOB                                                  5
MGR                                                2
HIREDATE                                    13
SAL                                                12
COMM                                             4
DEPTNO                                          3

 How to choose Composite Indexes ?

A composite index contains more than one key column. Composite indexes can provide additional advantages over single column indexes.

Better Selectivity	Sometimes two or more columns, each with poor selectivity, can be combined to form a composite index with good selectivity.
Adding Data Storage	If all the columns selected by the query are in the composite index, Oracle can return these values from the index without accessing the table. However in this case, it's better to use an IOT (Index Only Table).

An SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:

SQL> CREATE INDEX idx_composite ON my_table (x, y, z);

These combinations of columns are leading portions of the index: X, XY, and XYZ. These combinations of columns are not leading portions of the index: YZ and Z.

Guidelines for choosing columns for composite indexes : 

Consider creating a composite index on columns that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either column individually. Consider indexing columns that are used frequently to join tables in SQL statements. Here are basic guidelines:

1) Use as less as columns for composite key index

2) Use high cardinality column in the beginning and least cardinality column at end for the composite key index.

3) If composite key index is used very rarely for the queries, better to avoid in OLTP production databases and same can be created in replicated / logical standby / report databases.

I hope this document may help you to optimize your queries more effectively.

Related documents:( Click on the related topics)

1) Performance tuning for beginners

2) Top 10 best practices for performance tuning.

3) Interpreting Explain Plan in Oracle

4) User Oracle link to know best practices for Indexes

Troubleshoot ORA-06528 : Error executing PL/SQL profiler

Start DBMS_PROFILER - user level

Troubleshoot ORA-06528 : Error executing PL/SQL profiler

Issue Description :

I received “ORA-06528” error from HR user. Same procedure I have tested also and received above error. Same procedure I have created and tested in different schema without any issues. Then I guess there may be a issue with profiler tables for the issuing schema.

about dbms_profiler:

The dbms_profiler package is a built-in set of procedures to capture performance information from PL/SQL.   The dbms_profiler package has these procedures:

            dbms_profiler.start_profiler

      dbms_profiler.flush_data

      dbms_profiler.stop_profiler

The idea behind profiling with dbms_profiler is for the developer to understand where their code is spending the most time, so they can detect and optimize it.  The profiling utility allows Oracle to collect data in memory structures and then dumps it into tables as application code is executed.  dbms_profiler is to PL/SQL, what tkprof and Explain Plan are to SQL. 

Once you have run the profiler, Oracle will place the results inside the dbms_profiler tables. 

The dbms_profiler procedures are not a part of the base installation of Oracle.  Two tables need to be installed along with the Oracle supplied PL/SQL package.  In the $ORACLE_HOME/rdbms/admin directory, two files exist that create the environment needed for the profiler to execute. 

            proftab.sql        - Creates three tables and a sequence and must be executed before the profload.sql file.

            profload.sql      - Creates the package header and package body for DBMS_PROFILER.  This script                                              must be executed as the SYS user.

Starting a Profiling Session – Using sqlplus console window

The profiler does not begin capturing performance information until the call to start_profiler is executed.

SQL> exec dbms_profiler.start_profiler ('Test of raise procedure by Gouranga');

Flushing Data during a Profiling Session

The flush command enables the developer to dump statistics during program execution without stopping the profiling utility. The only other time Oracle saves data to the underlying tables is when the profiling session is stopped, as shown below:

SQL> exec dbms_profiler.flush_data();

PL/SQL procedure successfully completed.

OR
Data in the profiler tables must be cleaned up. This can be done by running.

delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;

Stopping a Profiling Session

Stopping a profiler execution using the Oracle dbms_profiler package is done after an adequate period of time of gathering performance benchmarks – determined by the developer. Once the developer stops the profiler, all the remaining (unflushed) data is loaded into the profiler tables.

SQL> exec dbms_profiler.stop_profiler();

PL/SQL procedure successfully completed.

Oracle dbms_profiler package also provides procedures that suspend and resume profiling (pause_profiler(), resume_profiler()).

Example:

[oracle@01HW155534 ~]$ sqlplus /nolog

SQL> connect hr@myDB

Enter password:

Connected.

SQL>

SQL> exec dbms_profiler.flush_data();

PL/SQL procedure successfully completed.

SQL> exec dbms_profiler.start_profiler ('Test of raise procedure by Gouranga');

PL/SQL procedure successfully completed.

SQL> exec p_test();

PL/SQL procedure successfully completed.

Query to find out statistics:

select runid, unit_number, line#, total_occur, total_time,   

       min_time, max_time

from plsql_profiler_data;

Sample Output ( console / html output)

Tables/Views associated with dbms_profiler:

      plsql_profiler_units

      plsql_profiler_runs

      plsql_profiler_data

 

Sample output – Using Plsql Developer tool:

Note : When you are using third-party tools for sql or plsql, it gives you extra benefits like executed text code, timespent marking, graphs etc.

Executed procedure body:

CREATE OR REPLACE PROCEDURE p_test AS

 cnt      NUMBER := 0;

BEGIN

 DBMS_PROFILER.START_PROFILER( 'mod' );

  FOR I IN 1..500000 LOOP

   cnt := cnt + 1;

   IF ( MOD(cnt,1000) = 0 ) THEN

     COMMIT;

   END IF;

  END LOOP;

 DBMS_PROFILER.STOP_PROFILER;

END;

/

Issues while executing dbms_profiler:

Most of cases ORA-06528 error will come while executing the profiler. Please see the below error which shows the details of the error.

Error snapshot:

  

Troubleshoot the error:

Step-1: Connect to database server by OS level and go to below location :

      $ORACLE_HOME/rdbms/admin

Step-2: Connect SQLPLUS:

      $ sqlplus /nolog

      SQL> connect hr@myDB

      Connected.

Step-3: Start the profiler package:

      SQL> @proftab.sql

Now you can test. No issues will come.

Thanks 

Please feel free to post a comment.