---------------------------------------------------------------------------------------
Reference Link :
http://www.oracle-base.com/articles/misc/efficient-sql-statements.php
--------------------------------------------------------------------------------------
1) Check Your Stats
-------------------
The Cost Based Optimizer (CBO) uses statistics to decide which execution plan to use.
If these statistics are incorrect the decision made by the CBO may be incorrect.
For this reason it is important to make sure that these statistics are refreshed regularly.
2) Why Indexes Aren't Used
------------------------
* The presence of an index on a column does not guarantee it will be used.
The following is a small list of factors that will prevent an index from being used.
* The optimizer decides it would be more efficient not to use the index.
If your query is returning the majority of the data in a table,
then a full table scan is probably going to be the most efficient way to access the table.
* You perform a function on the indexed column i.e. WHERE UPPER(name) = 'JONES'.
The solution to this is to use a Function-Based Index.
* You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001
* You concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
* You do not include the first column of a concatenated index in the WHERE clause of your statement.
For the index to be used in a partial match, the first column (leading-edge) must be used.
create table test_index ( name varchar2(10), name1 varchar2(10), name3 varchar2(10))
create index ix_test_index on test_index(name, name1)
create table test_index1 ( name4 varchar2(10), name5 varchar2(10), name6 varchar2(10))
create index ix_test_index1 on test_index1(name4, name5)
select * from test_index where name1='arul' --table access full
select * from test_index where name1='arul' and name='arul' --ix_test_index
create index ix_test_index2 on test_index(name1)
select * from test_index where name1='arul' --ix_test_index2
select * from test_index where name1='arul' and name='arul' --ix_test_index
Index Skip Scanning in Oracle 9i and above allow indexes to be used even when the leading edge is not referenced.
* The use of 'OR' statements confuses older versions of the Cost Based Optimizer (CBO).
It will rarely choose to use an index on column referenced using an OR statement. The only way of guaranteeing the use of indexes in these situations is to use an INDEX hint.
3) EXISTS vs. IN
------------------------
The EXISTS function searches for the presence of a single row meeting the stated criteria as opposed to the IN statement which looks for all occurrences.
TABLE1 - 1000 rows
TABLE2 - 1000 rows
(A)
SELECT t1.id
FROM table1 t1
WHERE t1.code IN (SELECT t2.code
FROM table2 t2);
(B)
SELECT t1.id
FROM table1 t1
WHERE EXISTS (SELECT '1'
FROM table2 t2
WHERE t2.code = t1.code)
For query A, all rows in TABLE2 will be read for every row in TABLE1.
The effect will be 1,000,000 rows read from items. In the case of query B,
a maximum of 1 row from TABLE2 will be read for each row of TABLE1, thus reducing the processing overhead of the statement.
Rule of thumb:
If the majority of the filtering criteria are in the subquery then the IN variation may be more performant.
If the majority of the filtering criteria are in the top query then the EXISTS variation may be more performant.
I would suggest they you should try both variants and see which works the best.
Note. In later versions of Oracle there is little difference between EXISTS and IN operations.
4) Inequalities
----------------
If a query uses inequalities (item_no > 100) the optimizer must estimate the number of rows returned before
it can decide the best way to retrieve the data. This estimation is prone to errors.
If you are aware of the data and it's distribution you can use optimizer hints to encourage or discourage
full table scans to improve performance.
If an index is being used for a range scan on the column in question, the performance can be improved by substituting >= for >. In this case, item_no > 100 becomes item_no >= 101. In the first case, a full scan of the index will occur. In the second case, Oracle jumps straight to the first index entry with an item_no of 101 and range scans from this point. For large indexes this may significantly reduce the number of blocks read.
FK's :
--------
Try adding new indexes to the system to reduce excessive full table scans. Typically, foreign key columns should be indexed as these are regularly used in join conditions. On occasion it may be necessary to add composite (concatenated) indexes that will only aid individual queries. Remember, excessive indexing can reduce INSERT, UPDATE and DELETE performance.
Table Order
-------------
Driving Tables (RBO Only)
The structure of the FROM and WHERE clauses of DML statements can be tailored to improve the performance of the statement. The rules vary depending on whether the database engine is using the Rule or Cost based optimizer. The situation is further complicated by the fact that the engine may perform a Merge Join or a Nested Loop join to retrieve the data. Despite this, there are a few rules you can use to improve the performance of your SQL.
Oracle processes result sets a table at a time. It starts by retrieving all the data for the first (driving) table. Once this data is retrieved it is used to limit the number of rows processed for subsequent (driven) tables. In the case of multiple table joins, the driving table limits the rows processed for the first driven table. Once processed, this combined set of data is the driving set for the second driven table etc. Roughly translated into English, this means that it is best to process tables that will retrieve a small number of rows first. The optimizer will do this to the best of it's ability regardless of the structure of the DML, but the following factors may help.
Caching Tables
--------------
Queries will execute much faster if the data they reference is already cached. For small frequently used tables performance may be improved by caching tables. Normally, when full table scans occur, the cached data is placed on the Least Recently Used (LRU) end of the buffer cache. This means that it is the first data to be paged out when more buffer space is required. If the table is cached (ALTER TABLE employees CACHE;) the data is placed on the Most Recently Used (MRU) end of the buffer, and so is less likely to be paged out before it is re-queried. Caching tables may alter the CBO's path through the data and should not be used without careful consideration.
Improving Parse Speed by using alias
------------------------------------
Execution plans for SELECT statements are cached by the server, but unless the exact same statement is repeated the stored execution plan details will not be reused. Even differing spaces in the statement will cause this lookup to fail. Use of bind variables allows you to repeatedly use the same statements whilst changing the WHERE clause criteria. Assuming the statement does not have a cached execution plan it must be parsed before execution. The parse phase for statements can be decreased by efficient use of aliasing. If an alias is not present, the engine must resolve which tables own the specified columns. The following is an example.
Bad Statement Good Statement
SELECT first_name,
last_name,
country
FROM employee,
countries
WHERE country_id = id
AND lastname = 'HALL';
SELECT e.first_name,
e.last_name,
c.country
FROM employee e,
countries c
WHERE e.country_id = c.id
AND e.last_name = 'HALL';
6) Consider adding columns for index only access
------------------------------------
Sometimes it can be advantageous to include additional columns in an index to increase the chances of index-only access. With index-only access all of the data needed to satisfy the query can be found in the index alone — without having to read data from the table space.
For example, suppose that there is an index on the DEPTNO column of the DEPT table. The following query may use this index:
SELECT DEPTNAME
FROM DEPT
WHERE DEPTNO = 'D01';
The index could be used to access only those columns with a DEPTNO greater than D00, but then the DBMS would need to access the data in the table space to return the DEPTNAME. If you added DEPTNAME to the index, that is, create the index on (DEPTNO, DEPTNAME) then all of the data needed for this query exists in the index and additional I/O to the table space would not need be needed. This technique is sometimes referred to as index overloading.
Of course, this is not always a good idea. You have to take into account whether other queries use the index and how it might negatively impact their performance.
---------------------------------------------------------------------------------------
How to find the redundent index which can be dropped.
reference Link :
http://www.dba-oracle.com/t_detecting_duplicate_indexes.htm
--------------------------------------------------------------------------------------
This script will locate all Oracle indexes with duplicate columns.
select /*+ rule */
a.table_owner,
a.table_name,
a.index_owner,
a.index_name,
column_name_list,
column_name_list_dup,
dup duplicate_indexes,
i.uniqueness,
i.partitioned,
i.leaf_blocks,
i.distinct_keys,
i.num_rows,
i.clustering_factor
from
(
select
table_owner,
table_name,
index_owner,
index_name,
column_name_list_dup,
dup,
max(dup) OVER
(partition by table_owner, table_name, index_name) dup_mx
from
(
select
table_owner,
table_name,
index_owner,
index_name,
substr(SYS_CONNECT_BY_PATH(column_name, ','),2)
column_name_list_dup,
dup
from
(
select
index_owner,
index_name,
table_owner,
table_name,
column_name,
count(1) OVER
(partition by
index_owner,
index_name) cnt,
ROW_NUMBER () OVER
(partition by
index_owner,
index_name
order by column_position) as seq,
count(1) OVER
(partition by
table_owner,
table_name,
column_name,
column_position) as dup
from
sys.dba_ind_columns
where
index_owner in ('&OWNER'))
where
dup!=1
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
)) a,
(
select
table_owner,
table_name,
index_owner,
index_name,
substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
(
select index_owner, index_name, table_owner, table_name, column_name,
count(1) OVER ( partition by index_owner, index_name) cnt,
ROW_NUMBER () OVER ( partition by index_owner, index_name order by column_position) as seq
from sys.dba_ind_columns
where index_owner in ('&OWNER' ))
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
) b, dba_indexes i
where
a.dup=a.dup_mx
and a.index_owner=b.index_owner
and a.index_name=b.index_name
and a.index_owner=i.owner
and a.index_name=i.index_name
order by
a.table_owner, a.table_name, column_name_list_dup;
---------------------------
Another query :
reference :
http://www.orafaq.com/node/926
---------------------------
SELECT
/*+ RULE */
tab_owner.name owner, t.name table_name,
o1.name || '(' || DECODE(bitand(i1.property, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name ,
o2.name || '(' || DECODE(bitand(i2.property, 1), 0, 'N', 1, 'U', '*') || ')' including_index_name
FROM sys.USER$ tab_owner, sys.OBJ$ t, sys.IND$ i1, sys.OBJ$ o1, sys.IND$ i2, sys.OBJ$ o2
WHERE i1.bo# = i2.bo# AND i1.obj# <> i2.obj# AND i2.cols >= i1.cols AND i1.cols > 0 AND
i1.cols = ( SELECT /*+ ORDERED */ COUNT(1) FROM sys.ICOL$ cc1, sys.icol$ cc2
WHERE cc2.obj# = i2.obj# AND cc1.obj# = i1.obj# AND
cc2.pos# = cc1.pos# AND cc2.COL# = cc1.COL#) AND
i1.obj# = o1.obj# AND i2.obj# = o2.obj# AND t.obj# = i1.bo# AND
t.owner# = tab_owner.USER# AND tab_owner.name LIKE '%'
ORDER BY 1, 2
Rule to drop the index
-----------------------
If 2 indexes ( I1 and I2 ) exist for a table and
the number of columns in Index I1 is less or equal to the number of column in index I2 and
index I1 has the same columns in the same order as leading columns of index I2
Then
If index I1 is UNIQUE then
If index I2 is used to support Foregh Key or for Index Overload then
Do Nothing
Else
Index I2 can be DROPPED
End If
Else
Index I1 can be DROPPED
End If
End If
----------------------------------------
Displays a list of non-indexes FKs
----------------------------------------
SELECT t.table_name,
c.constraint_name,
c.table_name table2,
acc.column_name
FROM user_constraints t,
user_constraints c,
user_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name = acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS (SELECT '1'
FROM user_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name
)
ORDER BY c.table_name;
---------------------------------------------------------------------------------------
How to monitor the index usability
--------------------------------------------------------------------------------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql
SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" MONITORING USAGE;'
FROM dba_indexes i
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));
SPOOL OFF
SET PAGESIZE 18
SET FEEDBACK ON
@temp.sql
status :
----------
SET VERIFY OFF
SELECT table_name,
index_name,
monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'))
usgae
-------
SET VERIFY OFF
SET LINESIZE 200
SELECT table_name,
index_name,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL'
Monitor off;
-----------------
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql
SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" NOMONITORING USAGE;'
FROM dba_indexes i
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));
SPOOL OFF
SET PAGESIZE 18
SET FEEDBACK ON
@temp.sql
