Home > Database, Oracle > Gather Schema and Index Statistics in Oracle

Gather Schema and Index Statistics in Oracle

January 28th, 2010

In this post I would like to draw your attention to the Optimizer in Oracle 10g.
The best place to start is the DBMS_STATS package reference page.

The DBMS_STATS package contains all the procedures required to maintain the optimizer statistics of any schema.
Once you get familiar with the documentation of the package, you will actually know which approach is best for your database and schemas. In my case I have to gather schema statistics once per week with a specific estimate_percent value and I have to gather schema index statistics every night. For this purpose I have created the following simple PL/SQL. I recommend if you create jobs and schedule them as appropriate.

-- Gathering Schema Statistics
execute dbms_stats.gather_schema_stats('SCHEMA1', estimate_percent=>39, cascade=>TRUE);
execute dbms_stats.gather_schema_stats('SCHEMA2', estimate_percent=>45, cascade=>TRUE);
-- Gathering Index Statistics
set serveroutput on
begin
for ind in
(select object_name
from user_objects
where object_type='INDEX'
and object_name like 'YOUR_INDEX_PATTERN_HERE'
)
loop
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||'.....');
dbms_stats.gather_index_stats('SHEMA1', ind.object_name , estimate_percent=>100);
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||' is Complete!');
end loop;
end;
/

The above code I run as the owner of the schemas in question. This is of course an example but the idea is to start making a difference between gathering schema statistics, object statistics.

Remember that the best information on the Oracle Optimizer is out there (mainly in the oracle documentation).

Hope this helps you improve the performance of your database.

Regards,

–St0ma

  1. May 2nd, 2013 at 21:12 | #1

    Very good blog! Do you have any hints for aspiring
    writers? I’m hoping to start my own blog soon but I’m a little lost on
    everything. Would you advise starting with a free platform
    like WordPress or go for a paid option? There are so many choices out there that I’m completely overwhelmed .. Any ideas? Kudos!

  2. June 13th, 2013 at 02:56 | #2

    🙂

Comments are closed.