Archive for January, 2010

Gather Schema and Index Statistics in Oracle

January 28th, 2010 2 comments

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
for ind in
(select object_name
from user_objects
where object_type='INDEX'
and object_name like 'YOUR_INDEX_PATTERN_HERE'
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;

Read more…

Compile Invalid SYNONYM Oracle

January 11th, 2010 4 comments

Happy New Year!
Wish you all the great stuff you have been dreaming about to come true in 2010!

I haven’t posted for a long time but my excuse is the busy season at work. At the end of the day I am finding a minute to share a simple but useful hint on generating the COMPILE SQL for invalid PUBLIC SYNONYM objects.

Every time you drop and recreate an object such as a SEQUENCE, the PUBLIC SYNONYM for that objects invalidates (if such exists). In order to generate the COMPILE commands you could use SQL similar to the following two examples:

Read more…

Categories: Database, Oracle Tags: , , , ,