How to do Object level tunning in Oracle???

Object-level tuning

1. Last analyzed date of all objects (table as well as indexes).
Command to analyze:
Analyze table table_name compute statistics; /* will estimate with 100 % of total records as default size*/
Analyze table table_name estimate statistics; /*will estimate with 1024 bytes as default size*/
Analyze table table_name estimate statistics for table sample 20 PERCENT; /* will estimate with20 % of total records as default size*/

Else we may use DBMS_STATS.GATHER_TABLE_STATS package e.g.

EXEC DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME =>null,
TABNAME => 'DW_CSC_COLLN',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS’,
DEGREE=>8,
ESTIMATE_PERCENT=>100);

==> GATHER_INDEX_STATS Index statistics
==> GATHER_TABLE_STATS Table, column, and index statistics
==> GATHER_SCHEMA_STATS Statistics for all objects in a schema
==> GATHER_DATABASE_STATS Statistics for all objects in a database

Questions:
1. How to check the particular table has been analyzed with what percentage?
==> Sample_size column of dba_tables.

2. How to know when to analyze the object?
==> 10 % of DML occurrence is the thumb rule in particular time period( you may query user_tab_modifications to find out the occurrences of dmls since last analyzed statement executed on that particular object). To query from user_tab_modifications, monitoring should be on or execute alter table table_name monitoring on;
==> When 10 % of difference is there in num_rows column of dba_tables (information with data dictionary of total number of rows in one particular table) and count(*) output from “select count(*) from table_name;”

3. What does Analyzing of an object do?
By analyzing the objects, we can collect the latest object information (mentioned below) and stores/updates in Data Dictionary. It basically collects or updates statistics about specific objects in order to opt for best optimized execution plan.
==> Table Statistics
==> Number of rows
==> Number of blocks that have been used
==> Number of blocks never used
==> Average available free space
==> Number of chained rows
==> Average row length
==> Number of distinct values in a column
==> The low value and high value in a column
==> Index Statistics
==> Index level
==> Number of leaf blocks
==> Number of distinct keys

2. Check the blevel of indexes from dba_indexes
If Blevel of index is >=3 then
==> Rebuild the index with alter index index_name rebuild online;

3. Check the Type of Index
On the basis of cardinality we have to check whether the existing index on particular column is correct or not.
==> Less than 5% of difference in distinct_keys and count(*)/num_rows forces us to make bitmap indexes.
==> If the cardinality is less (<5%) then
Preferred index type is BITMAP
==> If the column is using a particular single row function (eg:UPPER,LOWER,SUBSTR etc) then
Preferred index type is FUNCTION BASED INDEX

4. Check the Status of Index
==> Status of indexes should be valid.

5. Check if the tables and indexes are in different tablespaces.

6. Check if there are any chained rows
How to check?
==> Chain_cnts column of dba_tables.
How to solve?
==> Reorganize the table by Exporting to different tablespace and import the same table back to original tablespace?
==> Or data move command. Alter table table_name data move;

7. Table fragmentation the tables.
How to check?
==> Compare blocks and empty_blocks columns of dba_tables, if difference is very high apply below solution. Below commands will work in same way as defragmentation works in windows.
How to solve?
Alter table shrink space cascade;
Before this we have to enable the row movement to allow shrinking.
Alter table table_name enable row movement;


Query To check all above things at one go…..
Query to obtain stats
select a.table_name "Table_name",
a.index_name "Index_name",
c.tablespace_name "Table's tablespace",
a.tablespace_name "Index Tablespace",
a.index_type,
a.blevel,
c.num_rows,
distinct_keys,
b.column_name,
a.status,
a.last_analyzed "index_lastanalyzed",
c.last_analyzed "table_lastanalyzed",
a.logging "index logging",
c.logging "table logging",
c.chain_cnt
from dba_indexes a, dba_ind_columns b, dba_tables c
where a.index_name=b.index_name
and a.table_name=c.table_name
and a.table_name =''

What is an Index Organized Table (IOT) and when should an IOT be used?

An IOT keeps its data stored according to the primary key column values for the table. IOTs store the data as if the entire table was stored in an index.A normal index only stores the indexed columns in the index; an IOT stores all of the table's columns in the index. IOTs are new in Oracle8.As the table's data is stored as an index, the rows of the table do not have physical RowIDs. Therefore, you cannot select the RowID pseudocolumn values from an IOT.

Oracle8i introduces a new feature for IOTs called logical RowIDs. Logical RowIDs allow the construction of secondary indexes and improve index performance allowing the IOT performance to match the performance of regular indexes.

Prior to Oracle8i you could not create additional indexes on an IOT. The only valid index being the primary key index. As of Oracle 8i you can create secondary indexes on IOTs. An IOT will require less space than a regular table since there are no RowIDs.

Suppose you create the following IOT:

CREATE TABLE iottest
(areacode CHAR(3) primary key,
city VARCHAR2 (50))
ORGANIZATION INDEX TABLESPACE users
PCTTHRESHOLD 10
OVERFLOW TABLESPACE users;

An IOT is appropriate if you will always be accessing the areacode information by the areacode column in the where clause of your queries. To limit the amount of internal management with the IOT you should only create an IOT if the data is very static. If the data in your table changes frequently you should use a regular table with indexes. In general, IOTs are most efffective when the primary key constitutes a large part of the tables columns.If the IOT contains many frequently accessed columns that are not part of the primary key, then the IOT will need to repeatedly access it's overflow area.

Use of the pctthreshold clause is optional. When specified, pctthreshold indicates a threshold limit that any row of data can occupy in an index-organized table block.In the example, the value set for this clause is 10, which means that any row in the table that takes up 10 percent or more of its data block for storage will have nonkey column data moved into an overflow tablespace defined using the overflow tablespace keywords. If overflow tablespace is specified without defining a pctthreshold, the default pctthreshold of 50 will be used.A row bigger than pctthreshold in an IOT might destroy the dense storage of the rows in the index. In cases where this condition might occur, an overflow tablespace clause should be used.

To retrieve Oracle8
IOT information from the Data Dictionary issue the following SQL:
-----------------------------------------------------------------
SELECT t.table_name AS ?IOT?, o.table_name AS ?Overflow?,
i.index_name AS ?Index?, o.tablespace_name AS ?Overflow TS?,
i.tablespace_name AS ?Index TX?, i.pct_threshold
FROM dba_tables t, dba_tables o, dba_indexes i
WHERE t.owner = o.owner
AND t.table_name = o.iot_name
AND t.owner = i.owner
AND t.table_name = i.table_name AND t.owner = ?SCOTT?;

To retrieve post Oracle8

IOT information from the Data Dictionary issue the following SQL:
-----------------------------------------------------------------

SQL> SELECT table_name, tablespace_name, iot_name, iot_type
FROM dba_tables
where table_name = 'IOTTEST'

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
IOT_NAME IOT_TYPE
------------------------------ ------------
IOTTEST
IOT
SQL> SELECT object_name, object_id
FROM dba_objects
WHERE object_name = 'IOTTEST';

OBJECT_NAME OBJECT_ID
------------------------------------- ---------
IOTTEST 74858

SQL> SELECT index_name, index_type, tablespace_name, table_name
FROM dba_indexes
WHERE table_name = 'IOTTEST';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_IOT_TOP_74858 IOT - TOP
USERS IOTTEST


Now using the object_id obtained from dba_objects

SQL> SELECT segment_name, tablespace_name, segment_type
FROM dba_segments
WHERE segment_name like '%74858%';

SEGMENT_NAME
--------------------------------------------------------------
TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IOT_OVER_74858
USERS TABLE

SYS_IOT_TOP_74858 USERS INDEX

IOT Restrictions:
-----------------
1. A primary key must be specified otherwise ORA-25175 is returned.
2. If pctthreshold is defined and an overflow segment is not defined,
rows exceeding the threshold are rejected with an ORA-1429 error.
3. Cannot create an IOT of object types.
4. An IOT can contain columns of LOB and nested table types, but only
if the table is not partitioned.
5. IOTs must be reorganized using the move clause of the alter table
command.

in Vs Exists???

The two are processed very very differently.

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.

As opposed to

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:

for x in ( select * from t1 ) loop
if ( exists ( select null from t2 where y = x.x ) then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).

So, when is where exists appropriate and in appropriate?

Lets say the result of the subquery
( select y from T2 )

is "huge" and takes a long time. But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very very fast (nice index on t2(y)). Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery we need to distinct on.

Lets say the result of the subquery is small -- then IN is typicaly more appropriate.

If both the subquery and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors.

Clustered table???

Clustered table
A clustered table provides an optional method for storing table data. A cluster is made up of a table or group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together.

Clusters have the following characteristics:

· Clusters have a cluster key, which is used to identify the rows that need to be stored together.

· The cluster key can consist of one or more columns.

· Tables in a cluster have columns that correspond to the cluster key.

· Clustering is a mechanism that is transparent to the applications using the tables. Data in a clustered table can be manipulated as though it were stored in a regular table.

· Updating one of the columns in the cluster key may migrate the row.

· The cluster key is independent of the primary key. The tables in a cluster can have a primary key, which may be the cluster key or a different set of columns.

· Clusters are usually created to improve performance. Random access to clustered data may be faster, but full table scans on clustered tables are generally slower.

· Clusters renormalize the physical storage of tables without affecting the logical structure.

When to Rebuild an Index???

You have to periodically check your indexes to see if they become skewed and,therefore, good candidates for rebuild.

A skewed index has many records clumped close together on the index tree due to their similar indexed values. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance. It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.

Here is a sample procedure on how to identify the skewed indexes:

1. Gather statistics on your indexes. For large indexes (over one hundred thousand records in the underlying table), use ESTIMATE instead of COMPUTE STATISTICS.

For example:

SQL> analyze index A1_PK compute statistics;

Index analyzed.

2. Run the script given below - index_check.sql - to find out how skewed each index is.

This query checks on all indexes that belong to user SCOTT:

SQL> select index_name, blevel,
decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',
2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
from dba_indexes
where owner='SCOTT';

INDEX_NAME BLEVEL OK
---------------------------------------- ------ ----
A1_PK BLEVEL HIGH
A1_UK BLEVEL HIGH
BUDVERPORT_BUDVERPORT2_UK 1 OK BLEVEL
BUDVERPORT_BV_FK_I 2 OK BLEVEL
BUDVERPORT_CHAR_CL_FK_I 1 OK BLEVEL
BUDVERPORT_DIRCTE_FK_I 3 OK BLEVEL
BUDVERPORT_FUND_TYPE_FK_I 1 OK BLEVEL
BUDVERPORT_OMB_SUBFCT_FK_I 1 OK BLEVEL
BUDVERPORT_ORG_FK_I 0 OK BLEVEL
BUDVERPORT_PL_TITLE_FK_I 1 OK BLEVEL
BUDVERPORT_RDC_FK_I 1 OK BLEVEL
S_INVENTORY_PRODID_WARID_PK BLEVEL HIGH
S_ITEM_ORDID_ITEMID_PK BLEVEL HIGH
S_ITEM_ORDID_PRODID_UK BLEVEL HIGH
S_LONGTEXT_ID_PK BLEVEL HIGH
S_ORD_ID_PK BLEVEL HIGH
S_PRODUCT_ID_PK BLEVEL HIGH
S_PRODUCT_NAME_UK BLEVEL HIGH
S_REGION_ID_PK BLEVEL HIGH
S_REGION_NAME_UK BLEVEL HIGH
S_TITLE_TITLE_PK BLEVEL HIGH
S_WAREHOUSE_ID_PK BLEVEL HIGH

3. The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL. Prior to 9i, if the BLEVEL is more than 4,it is recommended to rebuild the index. As database are getting bigger and bigger, BLEVEL may not be a good indicator of time to rebuild the index. BLEVEL > 4 may suggest an evaluation of whether the partitioning option could help you.

Note: If you do not analyze the index, the index_check.sql script will show "BLEVEL HIGH" for such an index.

4. Gather more index statistics using the VALIDATE STRUCTURE option
of the ANALYZE command to populate the INDEX_STATS virtual table. This table does not contain an OWNER column and assumes you are looking for statistics for indexes created by your active session only.

SQL> analyze index SCOTT.ORG_PK validate structure;

Index analyzed.

SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS)
PCT_DELETED, (LF_ROWS-DISTINCT_KEYS)*100/
decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
from index_stats
where NAME='&index_name';

Enter value for index_name: ORG_PK

PCT_DELETED DISTINCTIVENESS
----------- ---------------
0 0

The PCT_DELETED column shows what percent of leaf entries (index
entries) have been deleted and remain unfilled. The more deleted
entries exist on an index, the more unbalanced the index becomes.
If the PCT_DELETED is 20% or higher, the index is candidate for
rebuilding.If you can afford to rebuild indexes more frequently,
then do so if the value is higher than 10%. Leaving indexes with
high PCT_DELETED without rebuild might cause excessive redo
allocation on some systems.

The DISTINCTIVENESS column shows how often a value for the
column(s) of the index is repeated on average. For example, if a
table has 10000 records and 9000 distinct SSN values, the formula
would result in (10000-9000) x 100 / 10000 = 10. This shows a good
distribution of values.If, however, the table has 10000 records
and only 2 distinct SSN values, the formula would result in
(10000-2) x 100 /10000 = 99.98. This shows that there are very few
distinct values as a percentage of total records in the column.
Such columns are not candidates for a rebuild but good candidates
for bitmapped indexes.