Oracle: Analyze Table or Index

 Analyze command 

We use "analyze" command to store fresh statistics of table or index . We can do the same DBMS_UTILITY package as well. Statistics created by "analyze" command or by DBMS_UTILITY package are used by CBO (Cost based Optimizer).

Some time same query taking huge time for execution, at that time we have to check that table or index used in query have latest statistics in data dictionary table or not, if not then we have to analyze table and index and re-execute the query .   

Below are commands syntax for same

Syntax:
For Table

analyze table <table_Name> {compute|estimate|delete) statistics;

For Index

analyze index <index_Name> {compute|estimate|delete) statistics ;

For Cluster

analyze cluster <cluster_Name> {compute|estimate|delete) statistics ;

Examples:-

DBMS_UTILITY package - If we want schema level or database level then we use this utility while we can use this utility for single table and single index as well

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');

exec DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE'); 

PLSQL Performance Tuning DBMS_PROFILER

DBMS_PROFILER

DBMS_PROFILER - DBMS_PROFILER package is Oracle supplied package to tune PLSQL code.

Steps :- 1) Environment Setup (One Time Setup)
              2) Start DBMS_PROFILER then Execute PLSQL Package (Which need to Tune) then Stop the DBMS_PROFILER
              3) Analyze Profiler data
              4) Optimize PLSQL code then again repeat step 2)

1) Environment Setup :- a) Check DBMS_PROFILER Package available in SYS schema.

select object_name,object_type from all_objects where object_name like '%PROF%';

b) check that you are able to execute package DBMS_PROFILER from your user. if you are not able to excute then grant execute priviledge
on package DBMS_PROFILER from sys user to your user.

grant execute on DBMS_PROFILER to scott;

c) Create rquired table through 'proftab.sql'

@?/RDBMS/ADMIN/proftab.sql

d) check profile tables creates

select * from user_tables where table_name like '%PROF%';

PLSQL_PROFILER_RUNS
PLSQL_PROFILER_UNITS
PLSQL_PROFILER_DATA


2) Start DBMS_PROFILER then Execute PLSQL Package (Which need to Tune) then Stop the DBMS_PROFILER :-

exec DBMS_PROFILER.START_PROFILER('TEST');
exec procedure;
exec DBMS_PROFILER.STOP_PROFILER();


3) Analyze Profiler data :-

select * from PLSQL_PROFILER_RUNS;
select * from PLSQL_PROFILER_UNITS;
select * from PLSQL_PROFILER_DATA;


SELECT u.runid,
       u.unit_number,
       u.unit_type,
       u.unit_owner,
       u.unit_name,
       d.line#,
       d.total_occur,
       d.total_time,
       d.min_time,
       d.max_time
FROM   plsql_profiler_units u
       JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE  u.runid = 1
ORDER BY u.unit_number, d.line#;


-- Top 10 slow statements

SELECT * FROM (
select trim(decode(unit_type,'PACKAGE SPEC','PACKAGE',unit_type)||
 ' '||trim(pu.unit_owner)||'.'||trim(pu.unit_name))||
' (line '|| pd.line#||')' object_name
, pd.total_occur
, pd.total_time
, pd.min_time
, pd.max_time
, src.text
, rownum sequencenr
from plsql_profiler_units pu
,    plsql_profiler_data pd
,    all_source src
where pu.unit_owner = user
and pu.runid = &1
and pu.runid=pd.runid
and pu.unit_number = pd.unit_number
and src.owner = pu.unit_owner
and src.type = pu.unit_type
and src.name = pu.unit_name
and src.line = pd.line#
) where sequencenr <=10;



4) Optimize PLSQL code :-

PLSQL code need to modified with help of following  :-

Bulk Collection
Global Temporary Table
Inbuilt Functions
Nocopy Compiler Hint



Truncate sequence for Profiler tables data :-

truncate table PLSQL_PROFILER_DATA;
truncate table PLSQL_PROFILER_UNITS;
truncate table PLSQL_PROFILER_RUNS;