2010/12/03

Advanced Compression in Oracle Database 11g

Oracle database release 11g introduced a new type of compression called ”Advanced Compression”. This option provides multiple features including OLTP table compression.


So, let’s test out this new feature.


First of all, we are going to create 3 new tables:


nocomp – table without compression

basiccomp – basic compression

oltpcomp – advanced compression


create table nocomp (a int, b char(30), c date);

create table basiccomp (a int, b char(30), c date) compress;

create table oltpcomp (a int, b char(30), c date) compress for oltp;


Now we check its creation.

select table_name,compression,compress_for from user_tables where table_name in ('NOCOMP','BASICCOMP', 'OLTPCOMP');


TABLE_NAME COMPRESS COMPRESS_FOR

NOCOMP DISABLED

BASICCOMP ENABLED BASIC

OLTPCOMP ENABLED OLTP


Next we add 500.000 rows with some kind of repetition so we can use the compression.


Previously to the inserts, we execute the followings commands so we can start checking the times for each operation plus some other execution information:


set timing on

set autotr on


Now the insertions:


insert into nocomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


insert into basiccomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


insert into oltpcomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


Result Times:

nocomp = 11.29 s

basiccomp = 13.57 s

oltpcomp = 22.36 s


As we can see, insertions with OLTP compression are slower.


Also, we can see that the table with OLTP compression uses a lot more redo than the other two.


Redo size = 28M8 / 28M7 / 95M1


But, let’s see how the pure data compression go.


select segment_name,round(bytes/1024/1024,2) Mb from user_segments where segment_name in (‘NOCOMP’,’BASICCOMP’,’OLTPCOMP’);


SEGMENT_NAME MB

----------------------------------

NOCOMP 27

BASICCOMP 24

OLTPCOMP 8


As we can see the OLTP compression almost compressed the data by 1/3.


OLTP Compression is slower and uses more redo but its compression is quite impressive.


Next we are going to do the same experiments but this time with direct inserts.


insert /*+ APPEND */ into nocomp select rownum , chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


insert /*+ APPEND */ into basiccomp select rownum, chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


insert /*+ APPEND */ into oltpcomp select rownum ,chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


select segment_name,blocks,round(bytes/1024/1024,2) Mb from user_segments where segment_name in (‘NOCOMP’,’BASICCOMP’,’OLTPCOMP’);


Results for Time and Space:

nocomp = 11.23 s / 24 MB

basiccomp = 11.40 s / 7 MB

oltpcomp = 11.32 s / 7 MB


With direct inserts, the times for the insertions are almost the same, but the two tables with some kind of compression use equally 1/3 of the space.



Finally, we are going to test how the different tables behave respect update and select operations.


First though, we increase the number of inserted rows from 500.000 to 2 milions.


(delete the tables and create new ones)


insert into nocomp select rownum , chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 2000000;


insert into basiccomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 2000000;


insert into oltpcomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 2000000;


Times for the insertions:

nocomp = 48 s

basiccomp = 51s

oltpcomp = 1 m 25 s


select segment_name,blocks,round(bytes/1024/1024,2) Mb from user_segments where segment_name in (‘NOCOMP’,’BASICCOMP’,’OLTPCOMP’);


Space used per table:

nocomp = 112 MB

basiccomp = 104 MB

oltpcomp = 31 MB


Once checek the time and space, we focus on the selects and updates:


select a from nocomp where b='A' and a>200000 and a<300000;

select a from basiccomp where b='A' and a>200000 and a<300000;

select a from oltpcomp where b='A' and a>200000 and a<300000;


Times:

nocomp = 22.28 s

basiccomp = 22.67 s

oltpcomp = 21.64 s


update nocomp set b='C' where b='B' and rownum <= 200000;

update basiccomp set b='C' where b='B' and rownum <= 200000;

update oltpcomp set b='C' where b='B' and rownum <= 200000;


Times:

nocomp = 5 s

basiccomp = 10 s

oltpcomp = 1 m 10 s


As we can see, both the inserts and especially the updates are slower with oltp compression but on the other hand, the selects take the same time and it uses much less space.


That's it. Thanks for reading.


(We might test LOBs compression in the near future.)

2010/11/26

UCM: Creating search and check-in profiles with custom metadata

To enhance the experience with Oracle Universal Content Management (UCM), users and clients create search and check-in profiles and, this way, they can adapt the Content Server to their business requirements.
To take full advantage of content management is essential to define what type of documents will be searched or checked-in on the system, defining and creating, in turn, new associated metadata.

Then, you can see a viewlet showing how to create new profiles and new metadata in Oracle UCM. The example shows how to add the new category "Training Documents" which has two new metadata fields, a simple type that contains the name of the instructor and other drop-down list with the type of training (presencial, online, self-study...).

Documentation:
Oracle's official documentation for UCM 11g

Downloads:
Oracle ECM suite 11g

Viewlet:
(Duration: 6 min.)

2010/11/12

WCM: Deploy new site with Site Studio for External Applications

In the latest version of Universal Content Management (UCM) 11g, Oracle improved its solution to web content management (WCM) using the new component Site Studio for External Applications (SSXA), which facilitates the development cost connecting it with IDE development tools such as JDeveloper 11g giving the possibility to use the JSP web format.
On the other hand, it continues keeping the tools Site Studio Designer and Site Studio Contributor of the 10g version.
In this post, to introduce the product a basic example of a website will be deployed on Weblogic Server 11g. Continue reading this blog for more posts about WCM and how to add contribution areas, navigation menu, breadcrumbs, etc.


Documentation:
Oracle's oficial documentation for ECM 11g
Developer's guide for SSXA


Downloads:
Oracle ECM 11g suite


Viewlet:
(Duration: 6 min.)


ODC: Install and configure Oracle Document Capture

Oracle offers in its Enterprise Content Management (ECM) suite solutions for the capture and document recognition. Oracle Document Capture (ODC) 10g is a desktop application designed to capture documents for, later, recognize some areas and metadata and index them against a target repository, which can be a file system, database, Content Server UCM, etc.
Oracle Document Capture incorporates other features such as indexing, recognition and import of documents in a customized and programmable batch processes. It also includes the Oracle Distributed Document Capture (ODDC) technology, an application based on a web interface to scan and index documents in a distributed environment.
The following summarizes the steps in a Viewlet to install and configure Oracle Document Capture. This example shows the installation in a single-user, which stores all the configuration information in a local database (Microsoft Access file). Once you followed these steps, the system should be ready to define the scanning and indexing profiles.



Documentation:
Oracle's oficial documentation for ECM 10g
Installation guide for ODC


Downloads:
Oracle ODC and ODDC


Viewlet:
(Duration: 3 min.)


UCM: Creating advanced folios

Through using advanced folios,Oracle Universal Content Management (UCM) is able to organize documents of the Content Server into more complex structures using a hierarchy of folders such as a filesystem.To create advanced folios, previously must have created a template or use a predefined template into the system. (See UCM: Creating templates for advanced folios)
Once the advanced folio is created and the documents are attached, you can apply different retention policies and lock the folio to prevent changes on it, you can continue adding more documents or download the folio in a specific format, as shown in the following tutorial.


Links:
UCM: Creating simple folios
UCM: Creating templates for advanced folios


Viewlet:
(Duration: 2.5 min.)


UCM: Creating templates for advanced folios

The folio feature that Oracle Universal Content Management (UCM) provides, allows you to group content under one structure. This structure can be a simple structure (a table) then you should use a simple folio or it may be a more complex structure (including a hierarchy tree) using an advanced folio.Before use an advanced folio, you must create a basic template which can be extended and modified. Here is a tutorial that shows how to create a template for advanced folios.

Links:
UCM: Creating simple folios
UCM: Creating advanced folios


Viewlet:
(Duration 2 min.)


UCM: Creating simple folios

One of the tools offered by Oracle Universal Content Management (UCM) to organize the documents in the Content Server are the folio features. This tool allows you to group multiple documents into more complex structures and view them as a single document.
This tutorial shows, through a viewlet, how simple folios work and which operation can be done on it.

In the case of simple folios, they are organized in a flat structure, such as a table so that all documents are in the same hierarchy level. It may be noted that once created, you can apply some operations like freezing the folio and its documents, extend to an advanced folio, etc.


Links:
UCM: Creating advanced folios
UCM: Creating templates for advanced folios


Viewlet:
(Duration: 2 min.)

HOWTO: Resize or create a new partition in a virtual machine

A bad forecast when partitioning a virtual machine can make it useless. To avoid to install again the operating system, you can use tools to increase the size of partitions or create new ones.

Prerequisites:
VMware Server or VMware Workstation
gParted liveCD or liveCD linux distribution with gParted


Downloads:
VMware downloads
gParted downloads


Step-by-step:
1) Edit the virtual machine in the configuration interface of VMware
2) In the hardware section, edit the Hard Disk configuration
3) Click on increase capacity and introduce the new size
4) In the hardware section, edit the CD/DVD drive configuration
5) Configure the drive to load the gParted LiveCD image (.iso)
6) Click on Configure VM to load the BIOS on the next start
7) Ensure DVD drive is the first boot option. If not, use "+" and "-" keys to change the boot order
8) Save and exit. gParted LiveCD is going to boot
9) Use the default configuration or change it if necessary
10) Once in the graphical mode, you must be able to change an existing partition or create a new. Click the Apply button to commit changes.


Viewlet:
(Duration: 2 min.)

UCM: Installation of UCM 11g

The effectiveness and benefits of UCM are shown daily in the interest of customers and partners who choose to implement records management in their information systems. Oracle understands this and for this reason continues promoting its product Oracle Universal Content Management (UCM) and the entire Enterprise Content Management (ECM) suite to provide solutions.
Since April 2010, the new version 11g has been released offering more potential than the previous version and has made improvements such as running on the upper layers of the WebLogic application server.

To introduce in the new version, you can see the installation tutorial of UCM 11g:

Downloads:
ECM Suite and RCU utility
Weblogic Server

Documentation:
Oracle's oficial documentation for ECM 11g
Quick installation guide for UCM 11g

Step-by-step:
Note: This guide has been installed in a Oracle Enterprise Linux operating system. It can change some step if you are installing on a Windows operating system.

Note: This tutorial assumes that there is an installed Database running on the system. It would be necessary of any supported database to continue with the tutorial.

1) Preparing the Database
1.1) Download and extract the Repository Creation Utility from the downloads section
1.2) Execute .../rcuHome/bin/rcu
1.3) An assistant guide will appear and will create the required schemas on the database depending of the selected installation product

2) Installation of the Weblogic Application Server
2.1) Once the shcema is created, download and extract the Weblogic Server installer
2.2) Launch the installer .../wls1033_linux32.bin
2.3) Follow the installation assistant

3) Installation of the Fusion Middleware ECM Suite
3.1) With the prepared database and the Weblogic Server installed, the next step is the installation of ECM Suite 11g where, in this case, this tutorial will install only Oracle UCM 11g. Download and extract the ECM Suite
3.2) Execute .../Disk1/runInstaller
3.3) Follow the installation assistant

4) Creating a Weblogic domain
4.1) Once all the software is ready, now you must create a Weblogic domain for UCM
4.2) Execute ./"ECM_ORACLE_HOME"/common/bin/config.sh
4.3) Follow the installer assistant
4.4) Optimize the JAVA configuration using the command:

export USER_MEM_ARGS="-Xms256m -Xmx1024m -XX:CompileThreshold=8000 -XX:PermSize=128m -XX:MaxPermSize=1024m"

5) Start UCM 11g
5.1) Finally UCM 11g is ready to run!

Viewlet:
(Duration: 2 min.)


URM: Adding the catalan language

From Oracle Barcelona we continue working to facilitate the implantation of our products to our clients and, given that a lot of them needs it, we offer them the translation to the catalan language as Oracle does not offers it by default. In this case, an package is attached with all the content translated for Oracle Universal Records Management (URM) and the necessary steps to enable this language:

Prerequisites:
Given that Oracle URM is based on the Oracle UCM technology, is also required to have it translated. You can follow the catalan installation guide for UCM 10g in this post:
http://hfolguera.blogspot.com/2010/05/ucm-traduccion-del-producto-al-catalan_26.html

Downloads:
Catalan translation pack for URM

Step-by-step:
1) Download the translation pack from the Downloads section. This package already has all the content translated, so no modifications are required
2) Make the ca folder in the directory:
"Installation Path"/custom/RM10gR3UiLanguagePack/lang and copy the content of RMProductStrings in the new folder
3) Restart the Content Server

UCM: Adding the catalan language

The philosophy and internationalization of Oracle makes that all of its products have, from the installation, the most extended languages in the world which is not included the catalan language.
You can read the internationalization capabilities (I18N) and localization (L10N) of UCM in the links below:
http://blogs.oracle.com/ecmalerts/2009/11/oracle_content_server_10gr3_lo.html
http://download.oracle.com/docs/cd/E10316_01/cs/cs_doc_10/documentation/integrator/internat_environments_10en.pdf

From Oracle Barcelona, we cover the Mediterranean zone where the user interface in catalan if an often petition, specially in the Public Administration sector.
This fact, makes that we think that is important to explain the way to add the catalan language to the Universal Content Management (UCM) or other languages:

Downloads:
Catalan translation pack for UCM

Step-by-step:
1) Download the package with the required files in the Downloads section. This pack already contains all the content translated, then no modification is required.
2) Modify the std_locale.htm file from the directory:
"Installation Path"/custom/CS10gR3/CoreUpdate/core/resources

Note: If the UCM installation has some update patch, you must modify the path for the new version, i.e.: CS10gR34CoreUpdate

Option 1: Overwrite the std_locale file with the attached in the translation pack.
Option 2: Add the new lines in the configuration tables (The attached std_locale.htm file can help you as a guide)

3) Copy the ca folder in the directory: "Installation Path"/custom/Localization/lang
4) Make the folder ca in the directory: "Installation Path"/weblayout/resources/lang
5) Restart the Content Server

Note: You can verify the process through the System Properties application, in Windows: Start > Programs > Oracle > Oracle Content Server > idc > utilities > System Properties
In the Localization tab should appear the new language as enabled.

UCM: How to create a new skin and layout

There is the possibility to change the look & feel in UCM and, this way, adapt the user interface to any organization that works with this product: changing the logo for its own, modify colors and CSS style, ...

Below, you can see a viewlet showing the necessary steps to create a new layout. In this example, it copies an existing template and it modifies some selected elements.

Note: This viewlet is based in UCM 10g version

Step-by-step:
1) Copy any existing layout of the system in "Installation Path"/weblayout/resources/layouts
2) Modify images, CSS style or layout
3) Copy the modified layout to the system in "Installation Path"/weblayout/resources/layouts
4) Log-in into the Content Server with an user with administration rights
5) Open the Admin Server (Administration > Admin Server)
6) Add the Additional Configuration Variables:
LegacyScanLayoutsDirForAdditionalLayouts=true
LegacyScanLayoutsDirForAdditionalSkins=true
7) Restart the Content Server
8) Publish the static layout (Administration > Actions > Publish static layout files)
9) Publish schema and configuration data (Administration > Actions > Publish schema and configuration data)
10) Done. Log-in with any user and configure the new look & fell in his profile.

Related Links:
http://blogs.oracle.com/kyle/2009/08/ucm_custom_skins_and_layouts_a.html
http://www.oracle.com/technology/products/content-management/ucm/samples/CreateLayout.zip
http://download.oracle.com/docs/cd/E10316_01/cs/cs_doc_10/sdk/modifying_interface/wwhelp/wwhimpl/js/html/wwhelp.htmEnlace

Documentation:
Oracle's oficial documentation for UCM 10g

Viewlet:
(Duration: 6min - Video comments in spanish)


UCM: Enabling full text search

To enable the full search in Oracle Universal Content Management (UCM) you must modify the configuration file. This is located in (under Windows or Linux):

"Installation Path"\config\config.cfg

Then, you must append this line: SearchIndexerEngineName=DATABASE.FULLTEXT in the Additional Variables section of the file.
Later, you must restart the Content Server and rebuild the index.

How to rebuild the index?
1) Log-in to the Content Server
2) Click on the Administration tab
3) Click on Admin Applets
4) Click on the Repository Manager button
5) Open the Indexer tab
6) In the Automatic Update Cycle, click Start
7) In the Collection Rebuild Cycle, click Start

Once the rebuild is finished, the Content Server will be able to perform full text search.

Documentation:
Oracle's oficial documentation for Oracle UCM 10g