ETL Tuning in ODI / BI Apps–The #ETL_ANALYZE_WORK_TABLE parameter

One of the first things I do when I run into performance issues with ETL loads is to look at the source and target table statistics. Have they been collected before the current select / insert statement was issued?

It turns out that in Oracle BI Apps the #ETL_ANALYZE_WORK_TABLE parameter is turned off by default when a load plan is generated. This can make doing a high level review of your load plan execution tricky since there will be steps that will seem to be gathering statistics, when in reality, the ODI code generator just puts a placeholder instead of the code for statistics. An example of this is shown below:

 

image

 

image

 

SOLUTION:

Once I realized what the issue was with statistics not being gathered for my work tables I was able to zoom into the ETL_ANALYZE_WORK_TABLE variable by looking at my generated load plan as depicted below, and change its default value to Y. The variable is defined globally so once you change the definition this new default value will apply to any newly generated load plans.

 

image  

 

image

How To: Download, install, configure and verify you have the latest version of Opatch

On this post we will discuss all the steps necessary to ensure you have the correct version of the OPatch patching utility for oracle software running on your system. If you find this post useful please or Share our site!

Environment Variables:

Set your ORACLE_INSTANCE path to a valid OBIEE 11g instance

Set your ORACLE_HOME path to the OBIEE 11g home (Oracle_BI1)

Validating Your Environment:

OPatch is installed by default, locate it go to the ORACLE_HOME path for your OBIEE installation:

\Oracle_BI1\OPatch

To verify your current session is correctly configured verify the installed version by running the following command:

opatch version

Minimum Required Versions:

OBIEE 11.1.1.5.0 -> OPatch version 11.1.0.8.3 or higher (do NOT use OPatch 12.x)

OBIEE 11.1.1.6.0 -> No OPatch packages available as of March 12, 2012

Downloading a Newer Version of OPatch:

To find and download the appropriate version of OPatch for yor system please go to ORacle Support and find the knoledge base article below:

Note 224346.1 – Opatch – Where Can I Find the Latest Version of Opatch?

Installing OPatch:

To install OPatch once you have downloaded the appropriate version follow the steps below:

  1. Rename your current OPatch directory (ORACLE_HOME\OPatch)
  2. Copy the zip file to your ORACLE_HOME
  3. Unzip the zip file
  4. Verify that the upgrade succeeded

    cd OPatch
    opatch version

File System Access:

OPatch will update the local Oracle Inventory so the user account running OPatch must have acces to the location of the OUI Inventory, to verify this you can run the following command:

opatch -lsinventory

Patch Directory Location (PATCH_TOP):

If you have a centralized location where you store code / releases it is a good idea to create a directory called PATCH_TOP to store patches as they are applied to each environment (DEV, QA, STAGE, PRD).

On this post we will discuss all the steps necessary to ensure you have the correct version of the OPatch patching utility for oracle software running on your system.

Configuring the windows host file to correctly use the loopback adapter for Oracle product installations

This procedure is required when installing Oracle products such at the Oracle Database or Oracle Business Intelligence Enterprise Edition on a host machine that uses DHCP.  Follow the steps to configure a loopback adapterand then:

  1. Open the host file under c:\windows\system32\drivers\etc using notepad
  2. Identify your computer’s host name by right clicking on My Computer, selecting properties and clicking on the computer name tab Identify your computers host name by right clicking on My Computer, selecting properties and clicking on the computer name tab
  3. On the first line of your host file add a link between the static IP address you assigned your loopback adapter and your computer’s host name, for example: # IP                 hostname.domain        hostname 10.10.10.35    virtualXP.acme.com virtualXP
  4. On the second line associate the same static IP with the localhost default alias, for example: 10.10.10.35 localhost.localdomain    localhost

Once you have completed these steps you can proceed with the next steps in your Oracle product installation. If you find this post useful please or Share our site! This procedure is required when installing Oracle server products such at the Oracle Database or Oracle Business Intelligence Enterprise Edition on a host machine that uses DHCP.  Follow the steps to configure a loopback adapter and then the steps outlined on this article…

Configuring a new loopback adapter in windows


 

  1. Start > Control Pannel> Add  Hardware
  2. Click next on the Welcome screen
    Click next on the Welcome screen
  3. When ask if you have connected your hardware, specify yes
    When ask if you have connected your hardware, specify yes
  4. In the next dialog select Add new hardware device
    In the next dialog select Add new hardware device
  5. On the following screen select Network Adapters
    On the following screen select Network Adapters
  6. Select Microsoft > Microsoft Loopback Adapter from the list
    Select Microsoft > Microsoft Loopback Adapter from the list
  7. Select next in the following screen to install your loopback adapter
    Select next in the following screen to install your loopback adapter
  8. Click finish to complete this section of the installation process
    Click finish to complete this section of the installation process
  9. Find your newly created adapter by going to Start > Control Panel > Network Connections
    Find your newly created adapter by going to Start > Control Panel > Network Connections
  10. Right click your new connection and select Properties
  11. Select the Internet Protocol on the second section of the screen and click properties
    Select the Internet Protocol on the second section of the screen and click properties
  12. In the IP properties dialog configure a static IP address following the example belowIP Address: 10.10.10.35 Subnet Mask: 255.255.255.0
    In the IP properties dialog configure a static IP address following the example belowIP Address: 10.10.10.35 Subnet Mask: 255.255.255.0

Once you have configured a static IP address you will have completed the configuration of your new loopback adapter.

For instructions on configuring your hosts file to correctly use your loopback adapter when installing Oracle products see this post.

If you find this post useful please or Share our site!

This short reference article will guide readers through the configuration process for a loopback adapter, this is a pre-requisite for most Oracle server software installs…