Hello All,

In this topic I will share with you a set of scripts I am using to collect ORACLE optimizer statistics.

Today I will start with script 1.

Collect statistics for partition in schema with partitioned INDEX ORGANISED TABLES.


Note that the statistics are sometimes incorrect when the index contains 0 mb for the partition and this is the reason we are using the below script to make sure our stats are with the correct values.


Please feel free to edit it based on your needs and test it before execute on LIVE systems.

Code:
set serveroutput on

DECLARE
  V_SQL varchar2(32000);
BEGIN
   FOR c1 IN (select  x.owner, x.table_name, x.partname,
case when x.size01 between 0 and 1 then 100         -- size
...