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 =''