Reorganizing Oracle Database

To provide the optimizer with accurate information for determining an optimal access plan, current statistics are essential. Therefore, it is highly recommended to use automatic statistics gathering.

Procedure

Ensure that the automatic statistics gathering job is scheduled, by using the following SQL.
```
SELECT  ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';
```
Output should be 'TRUE'.
Note: GATHER_STATS_PROG is the program that runs the internal procedure GATHER_DATABASE_STATS_JOB_PROC).
Delete any existing statistics, by using the following command from SQLPLUS. Replace WPCUSER with the user who is specified on the db_user variable found in the $TOP/etc/default/common.properties file.
```
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('WPCUSER');
```
Change the default ESTIMATE_PERCENT to 100% for DBMS_STATS. Use the following procedure from SQLPLUS to change the default. Run this using the Oracle SYSDBA user ID:
```
EXEC DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','100');
```

Run GATHER_DATABASE_STATS using the following command from SQLPLUS. Run this command by using the Oracle SYSDBA user ID.

EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 2, CASCADE => TRUE);

Shut down and start the Oracle Database.

Note: This step flushes any dynamic SQL plans that are still using old access paths that did not use the current updated statistics.

Modify the default maintenance window for the automatic statistics gathering job.

Oracle provides utilities to change the default schedule window of automatic statistics gathering.

This example changes the default maintenance schedule to be 1:00 AM. to 3:00 AM. everyday. You must run these commands as the sysdba user.

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'WEEKNIGHT_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=1;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'WEEKNIGHT_WINDOW', attribute => 'duration', value => interval '120' minute);
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'WEEKEND_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT, SUN;byhour=1;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'WEEKEND_WINDOW', attribute => 'duration', value => interval '120' minute);

What to do next

Occasionally, it is required to reorganize the database for optimal performance. To check for table fragmentation you can use the oracle_table_fragmentation_report.sh script.

Create the file oracle_table_fragmentation_report.sh file on Product Master server side, for example at $TOP/bin/db and paste the script content as described as follows.

Add an execute permission to the chmod u+x oracle_table_fragmentation_report.sh file.

Start fragmentation report by running the following command:

./oracle_table_fragmentation_report.sh <db_user> <db_password> <dbname>

The generated fragmentation report is created in the local directory with name table_fragmentation_report.out. The report looks similar to the following:

Table name : <25% full Blocks : 25-50% full Blocks : 50-75% full Blocks : >75%
    full Blocks : Full Blocks
    ...
    TCTG_CAD_CATEGORY_DETAIL : 7 : 1117 : 239 : 977 : 80177
    TCTG_CAT_CATEGORY : 5 : 121 : 64 : 521 : 13466
    TCTG_CAX_CATEGORY_CONTENT : 1 : 3 : 1 : 542 : 92918
    TCTG_CCE_CTLG_CTLG_EXPORT : 0 : 0 : 0 : 0 : 0
    TCTG_CCM_CATEGORY_CATEGORY_MAP : 0 : 1 : 2 : 0 : 2
    ...
    TCTG_ICM_ITEM_CATEGORY_MAP : 65 : 14389 : 6020 : 3746 : 31777
    TCTG_ITA_ITEM_ATTRIBUTES : 39 : 46833 : 32414 : 138070 : 878941
    TCTG_ITD_ITEM_DETAIL : 54 : 38286 : 22507 : 53205 : 270075
    TCTG_ITM_ITEM : 642 : 5630 : 2307 : 1518 : 17340
    TCTG_ITX_ITEM_CONTENT : 26 : 18 : 21 : 1328 : 953764
    ...

Tables, which show a high portion of blocks with low usage (<50% full) should be considered for reorganization.

oracle_table_fragmentation_report.sh
#!/bin/bash
/* _______________________________________________________ {COPYRIGHT-TOP} _____
* IBM Confidential
* OCO Source Materials
*
* 5725-E59
*
* (C) Copyright IBM Corp. 2007, 2014  All Rights Reserved.
*
* The source code for this program is not published or otherwise
* divested of its trade secrets, irrespective of what has been
* deposited with the U.S. Copyright Office.
* ________________________________________________________ {COPYRIGHT-END} _____*/

if [ $# -lt 3 ]
then
  echo "Usage " $0 "db_user db_password db_name"
  echo "Example: " $0 "pimuser pass4pim pimdb"
  exit 0
fi
userid=$1
passwd=$2
dbname=$3

CONNECTION="$userid/$passwd@$dbname"

#echo 'CONNECTION: ' $CONNECTION

echo '== Generating Table fragmentation report for ' ${userid} ' =='

sqlplus -s ${CONNECTION} <<EOF 2>&1 >table_fragmentation_report.out
SET term off ver off trims on serveroutput on size 1000000 feed off;
declare
  v_unformatted_blocks number;
  v_unformatted_bytes number;
  v_fs1_blocks number;
  v_fs1_bytes number;
  v_fs2_blocks number;
  v_fs2_bytes number;
  v_fs3_blocks number;
  v_fs3_bytes number;
  v_fs4_blocks number;
  v_fs4_bytes number;
  v_full_blocks number;
  v_full_bytes number;
  cursor table_list is select table_name from user_tables where partitioned = 'NO' order by table_name;
  cursor table_part_list is select table_name, partition_name from user_tab_partitions where high_value is not null order by table_name;
begin
  dbms_output.put_line('Table name : <25% full Blocks : 25-50% full Blocks : 50-75% full Blocks : >75% full Blocks : Full Blocks');
  for table_list_rec in table_list loop
    dbms_space.space_usage (USER,table_list_rec.table_name,'TABLE', v_unformatted_blocks,
       v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
       v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
    dbms_output.put_line(table_list_rec.table_name || ' : ' || v_fs1_blocks || ' : ' || v_fs2_blocks || ' : ' || v_fs3_blocks|| ' : ' || v_fs4_blocks || ' : ' || v_full_blocks);
  end loop;
  for table_part_list_rec in table_part_list loop
    dbms_space.space_usage (USER,table_part_list_rec.table_name,'TABLE PARTITION', v_unformatted_blocks,
       v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
       v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes,table_part_list_rec.partition_name);
    dbms_output.put_line(table_part_list_rec.table_name || ' : ' || v_fs1_blocks || ' : ' || v_fs2_blocks || ' : ' || v_fs3_blocks|| ' : ' || v_fs4_blocks || ' : ' || v_full_blocks);
  end loop;
end;
/
EOF

echo '== Check Table fragmentation report at table_fragmentation_report.out =='
exit 0