The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 259 other followers

Dropping interval partitions – Revisited

Posted by Harald van Breederode on July 26, 2010

In a previous posting about dropping interval partitions I explained how the transition point in an interval partitioned table can be moved up to be able to drop partitions. I explained that by temporarily converting the table from interval to range partitioning, all interval partitions are converted into range partitions.

Last week I discovered, suggested by Heijne Makkreel, that this conversion is unnecessary and that just setting the interval granularity to what it is already set to, all interval partitions are converted to range partitions.

The query below shows the partitions of an interval partitioned table (the same table as used in my original posting) with one range partition and four adjacent interval partitions. The partitions are 1 month in size.

SQL> select partition_name,interval,high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
SYS_P359       NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P360       YES TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P361       YES TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P362       YES TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P363       YES TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

SQL> select interval from dba_part_tables where table_name = 'IPART';

INTERVAL
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(1,'MONTH')

By issuing an ALTER TABLE command that sets the interval to what it is already set to, all interval partitions are converted into range partitions as demonstrated below:

SQL> alter table ipart set interval (NUMTOYMINTERVAL(1,'month'));

Table altered.

SQL> select partition_name,interval,high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
SYS_P359       NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P360       NO  TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P361       NO  TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P362       NO  TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P363       NO  TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

The query output above shows that all partitions are now of type range, and we can simply drop them if we wish to do so.
-Harald

Posted in Oracle | 3 Comments »

A few words on Index Compression

Posted by Harald van Breederode on April 5, 2010

For the last couple of months I have been quite busy delivering Oracle11g New Features courses and one of the topics covered is table compression. To introduce this topic I ask my students in which version the first compression feature was introduced. Strangely enough almost no one seems to know the correct answer, which is Oracle8i where index compression was introduced. Because table compression is very much like index compression I explain to my students what index compression is all about and quite often they are completely shocked by the fact that such an important feature is missing in their skill set. So it is about time that I post something about index compression.

Index compression works by eliminating duplicated column values in the index leaf blocks thereby allowing the storage of more index entries per leaf block. To demonstrate this feature I will start by creating a non-compressed index on a few columns on the CUSTOMERS table in the SH sample schema:

SQL> connect sh/sh
Connected.
SQL> create index i1 on customers(cust_first_name,cust_last_name);

Index created.

SQL> select leaf_blocks from user_indexes where index_name = 'I1';

LEAF_BLOCKS
-----------
        193
 

As shown above the just created index consists of a total of 193 leaf blocks. To get an estimate about the percentage of leaf blocks that can be saved, by compressing the index, and the optimal compression factor we can use the information Oracle stores in the INDEX_STATS view after validating an index as shown below:

SQL> validate index i1;

Index analyzed.

SQL> select opt_cmpr_count,opt_cmpr_pctsave from index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             2               47

The above output reveals that the optimal compression factor for this index is 2 and that this will most likely reduce the number of leaf blocks by 47%. So, let’s rebuild the index with compression enabled and verify the given estimate:

SQL> alter index i1 rebuild compress 2;

Index altered.

SQL> select leaf_blocks from user_indexes where index_name = 'I1';

LEAF_BLOCKS
-----------
        101

The above shows that the number of leaf blocks in the index is now 101. We started with 193 leaf blocks and we were expecting a 47% reduction in the number of leaf blocks. 47% of 193 is 91 and 193 minus 91 is equal to 102. So Oracle estimated the reduction pretty good I would say ;-) Executing the validate index command again assures that 2 is indeed the optimal compression factor and no leaf blocks can be eliminated because the index is already compressed as shown below:

SQL> validate index i1;

Index analyzed.

SQL> select OPT_CMPR_COUNT,OPT_CMPR_PCTSAVE from index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             2                0

Hopefully this little post is enough to trigger you to dive into the world of compressed indexes! For a much more thorough discussion on index compression see Richard Foote’s fabulous Oracle blog. (part 1, part 2, part 3 And part 4).
-Harald

Posted in Oracle | 10 Comments »

Why does the size of my ORACLE_HOME increase?

Posted by Harald van Breederode on February 21, 2010

Recently I discovered that the size of an ORACLE_HOME for a given release varies from machine to machine although the machines are of the same architecture and run the same operating system. A small difference in size can be explained by the fact that one ORACLE_HOME was re-linked in the past while the other wasn’t, but this time the difference was more than 1 GByte. Digging a bit further revealed a sub-directory called .patch_storage which, as the name implies, is used to store patch related data.

I found NOTE:550522.1 on MyOracleSupport which describes what this directory is used for and why it suddenly so big. It turns out that recent versions of OPatch, installed by installing patch 6880880, not only store object files (several kilo byes in size) that are patched in $ORACLE_HOME/.patch_storage but also the library (several tens of mega bytes in size) in which the affected object file belongs. This is to make rolling back installed patches as quick as possible. Thus in effect this is a space-for-time issue. Because OPatch doesn’t clean the .patch_storage directory up whenever files are no longer needed because a more recent patch gets installed the size of an ORACLE_HOME increases without notice.

The good news is that recent versions of OPatch have an option to clean up the .patch_storage directory manually if one doesn’t like this space-for-time optimization.

Let’s have a look at the current disk usage before cleaning up my .patch_storage directory.

$ cd $ORACLE_HOME
$ du -hs .patch_storage
1.2G    .patch_storage

The above shows that I currently have 1.2 GByte in the .patch_storage directory. This is an Oracle 11g Release 1 (11.1.0.7.0) ORACLE_HOME with the latest PSU and a few interim patches applied.

The opatch util cleanup command is used to clean up the .patch_storage but before doing so let’s have a look at the OPatch help output for this command.

$ opatch util cleanup -help
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

UTIL session

DESCRIPTION
     This utility cleans up 'restore.sh,make.txt' files and 'rac,scratch,backup' 
     directories of the.patch_storage directory of Oracle Home.If -ps option is used, 
     then, it cleans the above specified areas only for that patch, else for all 
     patches under ORACLE_HOME/.patch_storage. You will be still able to
     rollback patches after this cleanup.

SYNTAX
opatch util cleanup  [-invPtrLoc <Path to oraInst.loc> ]
                     [-jre <LOC> ] [-oh <ORACLE_HOME> ]
                     [-silent] [-report]
                     [-ps <patch ID with time stamp>, this will
                       be located under ORACLE_HOME/.patch_storage/]

OPTIONS   
       -invPtrLoc
              Used to locate the oraInst.loc file. Needed when the
              installation used the -invPtrLoc flag. This should be
              the path to the oraInst.loc file.

       -jre
              This option tells OPatch to use JRE (java) from the
              specified location instead of the default location
              under Oracle Home. Both -jdk and -jre options cannot
              be specified together. OPatch will display error in
              that case.
            
       -oh
              The oracle home to work on. This takes precedence over
              the environment variable ORACLE_HOME.
         
       -ps
              This option is used to specify the Patch ID with timestamp.
              This Patch ID with timestamp should be the same as in
              .patch_storage directory.

              A directory by this name will be present under
              ORACLE_HOME/.patch_storage. If this directory is specified
              and is valid, then the contents specified in the description
              will be cleaned up only for this patch. Otherwise, all patch
              related directories will be acted upon by this utility.

      -silent 
              In silent mode, the cleanup always takes place.

      -report 
              Prints the operations without actually executing them.


OPatch succeeded.

With this knowledge we can proceed with the actual clean up as shown below:

$ opatch util cleanup
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

UTIL session

Oracle Home       : /u01/app/oracle/product/11.1.0
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.1.0.7.0
OUI location      : /u01/app/oracle/product/11.1.0/oui
Log file location : /u01/app/oracle/product/11.1.0/cfgtoollogs/opatch/opatch2010-02-20_15-27-15PM.log

Patch history file: /u01/app/oracle/product/11.1.0/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'rac,scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
y
User Responded with: Y
Size of directory "/u01/app/oracle/product/11.1.0/.patch_storage" before cleanup is 1256452888 bytes.
Size of directory "/u01/app/oracle/product/11.1.0/.patch_storage" after cleanup is 142265128 bytes.

UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.

OPatch succeeded.

OPatch started by displaying the amount of disk usage in the .patch_storage directory before asking for permission to perform the actual clean up. Once the clean up is finished OPatch reports the new amount of disk usage. We can easily verify that OPatch indeed freed up a fair amount of disk space by running the du –hs command.

$ du -hs .patch_storage
144M    .patch_storage
 

In short: Running opatch util cleanup after installing patches prevents your ORACLE_HOME from eating up too much disk space without losing the ability from rolling back those patches.
-Harald

Posted in Oracle | 6 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 259 other followers