Siebel Architecture Specialists

When Siebel Installations going bad - troubleshoot

2010-03-07T11:54:00.002+05:30

I have recently installed the Siebel environment for a customer and have faced all the horrors (errors). Though troubleshooting took time to get rid of all the errors. Here are few major errors which I have came across.

Few errors below:

SRBroker log:

DBCLog DBCLogError 1 0000a2c64b90707e:0 2010-03-05 18:02:59 [DataDirect][ODBC lib] Specified driver could not be loaded

GenericLog GenericError 1 0000a2c64b90707e:0 2010-03-05 18:02:59 (srbthrd.cpp (4022) err=2097168 sys=0) SBL-SRM-00016: Unable to initialize the Database environment -- Unable to connect to DB (data ops)

GenericLog GenericError 1 0000a2c64b90707e:0 2010-03-05 18:02:59 (srbmtsrv.cpp (86) err=2097168 sys=0) SBL-SRM-00016: Unable to initialize the Database environment -- Unable to connect to DB (data ops)

Enterprise log:

GenericLog GenericError 1 0000a2cf4b90707e:0 2010-03-05 18:03:04 (scirkey.cpp (1142) err=1310772 sys=0) SBL-SVR-00052: Internal: Invalid proc handle

ServerLog ProcessExit 1 0000a2cf4b90707e:0 2010-03-05 18:03:04 SRBroker 798942 TERMINATED Process 798942 was terminated

GenericLog GenericError 1 00002ca64b91902a:0 2010-03-05 18:07:59 (schedule.cpp (2221) err=1376409 sys=0) SBL-SMI-00153: SRBroker NOT READY Component is not ready.

GenericLog GenericError 1 00002ca64b91902a:0 2010-03-05 18:07:59 (schedule.cpp (2162) err=1376408 sys=0) SBL-SMI-00152: TIMEOUT Component initialization for priority level 2 timed out after 300 s.

GenericLog GenericFatal 0 00002ca64b91902a:0 2010-03-05 18:07:59 (schedule.cpp (2173) err=1376407 sys=0) SBL-SMI-00151: System component initialization timed out. System will shut down.

Usually, when these errors are seen its confirmed that you missed some steps while installing the environment. Only way is to review your checklist again and checkout what could have been missed. It could be running setting db.sh, dbenv.sh, siebenv.sh or odbcsql connectivity.

I would try to add more as I recall any.

HTH

Understanding Deployment Architecture and Web-SSO Process Flow and Setup

2010-01-28T13:28:00.003+05:30

Sharing this information as it seems useful to many working on Web-SSO authentication using SUN One Identity Server 6.0

Pre-requisites to Integration

Before you begin the integration, make sure that:

• Siebel and all of its required components, including the web server and the database server, are installed and running in the environment. For detailed information on how to do this, please refer to the Siebel Bookshelf.

• Sun ONE Identity Server, including Sun ONE Directory Server, is installed and running. For information on how to install these products, please refer to the Sun ONE Identity Server product documentation.

Integration Steps

Once you have the products mentioned above installed and running, follow the steps listed below to enable Web SSO between Siebel 7.5 (check out for your specific application version on SUNs site) and Sun ONE Identity Server.

1. Set up Sun ONE Directory Server 5.1 so that the database accounts and the user’s Siebel uid can be retrieved.

Users in the Siebel database must correspond to users in Sun ONE Directory Server.

Users in Sun ONE Directory Server must have attributes containing values for Siebel uid, password and database account. Attribute names must correspond to what is configured in the web server plug-in configuration file, eapps.cfg, and the Call Center configuration file, uagent.cfg. The web server plug-in configuration file is located in $WEB_PLUGIN_HOME/bin directory on the web server machine. The Call Center configuration file is located in $SIEBEL_HOME/siebsrvr/bin directory on the Siebel server machine. If you are using a Siebel application other than Call Center, make sure you look at that particular application’s configuration file rather than the Call Center configuration file.

2. Edit the parameters in the web server plug-in configuration file, eapps.cfg, residing on the web server machine to have the following values:

Code Example 1

[defaults]
AnonUserName = sadmin
AnonPassword = sadmin
AnonUserPool = 120
StatsPage = _stats.swe
HTTPPort = 8080
HTTPSPort = 443
EnableFQDN = FALSE
FQDN = CHANGE_ME
AnonSessionTimeout = 900
GuestSessionTimeout = 300
SessionTimeout = 900
DoCompression = TRUE
SingleSignOn = TRUE
TrustToken = siebel2sun
UserSpec = REMOTE_USER
UserSpecSource = Header

3. Edit the parameters in the Call Center configuration file, uagent.cfg, as described below. If you are using another Siebel application, make sure to edit the appropriate configuration file in a similar manner (see Appendix C).

Code Example 2

[LDAP]
DllName = libsscfldap.so
ServerName = e450b.sunmde.com
Port = 389
BaseDN = "ou=People,o=siebel.com"
SharedCredentialsDN = "uid=sadmin,ou=People,o=siebel.com"
UsernameAttributeType = uid
PasswordAttributeType = userPassword
CredentialsAttributeType = dbaccount
;RolesAttributeType = siebelrole
;SslDatabase =
ApplicationUser = "uid=amAdmin,ou=People,o=siebel.com"
ApplicationPassword = netscape1
;EncryptApplicationPassword = FALSE
;EncryptCredentialsPassword = FALSE
SingleSignOn = TRUE
TrustToken = siebel2sun
;UseAdapterUsername = FALSE
;SiebelUsernameAttributeType =
;UseRemoteConfig =

4. Install Sun One Identity Server Policy Agent, version 2.0 on the web server hosting the Siebel application. For information on how to install this agent, please refer to the policy agents documentation at http://docs.sun.com/db/coll/S1_IdServ_60.

5. Modify the file amAgent.properties to allow the policy agent to set values in the HTTP header. The amAgent.properties file is the configuration file for the policy agent. It is typically located in /etc/opt/SUNWam/agents/es6/config/_opt_SUNWam_servers_/AMAgent.properties. In the file, modify the following properties as listed below:

com.sun.am.policy.am.fetchHeaders=true

com.sun.am.policy.am.headerAttributes=uid|uid

6. Create policies in Sun ONE Identity Server 6.0 to allow/deny access to your Siebel application. The steps for creating policies are documented in the Sun ONE Identity Server Administration Guide, which is located at http://docs.sun.com/source/816-6686-10/index.html.

Policies can be set on users, roles, or organizations. For this integration, policies have been set on the organization.

7. Stop the Web Server, Web Server Admin, Siebel Server, and then the Gateway Server. Restart them in the reverse order.

To verify if the integration is successful, access the Call Center application URL. You will redirected to Sun ONE Identity Server login page for authentication and upon successful authentication, you will be able to access the Call Center application.

Note: This informations has very well been documented in http://docs.sun.com. For more information refer to SUNs site.

OCI 10g/11g Interface error with OBIEE deployment on HP-UX IA 64 bits

2010-01-25T14:29:00.002+05:30

Most of you might have faced this issue OCI 10g/11g interface error while implementing OBIEE 10.1.3.4.x on HP-UX Itanium 64 bits platform (or any other platform). Most of you might have spend tirelessly resolving this issue. Well, ruling any more pain, this is an Oracle bug documented in Bug# 7675849 and Doc Id: 975071.1

Solution

From RDBMS prospective, there are two things that Oracle had suggested:

1. The customer was advised to apply the fix/workaround of bug7675849. "relink client shared library after adding option '-lrt' to sysliblist"
Add option '-lrt' to sysliblist file and rebuild the libraries.

You might have to recreate shared libraries if using Oracle 11g as /oracle11/app/product/11.1.0/db_1/rdbms/lib >make -f ins_rdbms.mk client_sharedlib

2. Set "diag_adr_enabled=off" via sqlnet.ora
"diag_adr_enabled=off" in $TNS_ADMIN/sqlnet.ora.

Note that above steps need to be performed only after bringing down Oracle instance. Hope this helps.

How to Create Gateway Windows Service On Other Systems in the Cluster?

2009-12-08T18:31:00.002+05:30

When the Gateway Server is installed, one system in the cluster is selected on which to perform the installation. To enable the Gateway Server to run on other systems in the cluster, the Gateway Server installation process does not have to be re-executed on the other systems in the cluster. Instead, to enable the Gateway Server to run on other systems in the cluster, a Gateway Server Windows service needs to be created on the other systems. This requirement and configuration process is fully documented by the chapter Clustering Your Siebel Deployment for Failover in the document titled Siebel Server Installation Guide for Microsoft Windows. Below is a summary of the steps to create a Windows service for the Gateway Server on the remaining systems in the cluster.

1. Switch the Gateway Server service group to the next system in the cluster on which the Gateway Server will be allowed to run.

2. Set to online the disk group, mount, NIC, and IP resources that support the Gateway Server. But, do not start the Gateway Server service. The files in Siebel root directory (SiebelRoot) should now be accessible to this system.

3. Open a DOS Command Prompt window, change to the directory SiebelRoot\ADMIN, and execute the following batch program:

cluster_gtwy.bat <password>

4. Using Windows Services manager, verify that the service is created.

5. Configure the Windows service for the Gateway Server to log on as the account specified by attribute User. Be sure to re-input the account's log-on password in the service's properties dialog window (this is important).

6. Verify successful configuration by starting the Gateway Server service on this system.

7. Repeat steps 1 through 6 on each remaining system in the cluster that may run the Gateway Server.

Seggregating one application to differnt application

2009-11-27T15:04:00.000+05:30

Hello Guys,

Today I am going to discuss regarding segregating a single application to different applications on the basis of user type, functionalities etc. Its not an easy task and require lot of planning and development efforts for any customer having an already live application to segregate their appln in two or three differnt application. Now you may ask WHY? Whats the need of doing this?

Here lies the answer with example. Say, your application has mix type of users - Sales, Service, Distributors with specific functionalities. In the past you observeed, with you old architecture, some process were hangigng and it was not identifiable why these processess were getting bad and who is responsible for this - sales, service or distributor?

With new architecture you have seperate URLs for Sales, Service and Distributors logging and hitting their own object managers on to their respective hardwares. This is an approach I have collected from a live experience sometime back.

Lots of tasks were involved doing so. Few are discussed below very briefly:

1) Development Efforts: Seggregaion is not an easy task and involve lot of developmet efforts on the basis of identifying the specific functionalities required by specific user group (or new appln). I would say for a live productionsetuop it would range from 1 to 3 months depenmding upon the kind of customization and development efforts.

2) New Architecture capacity planning: Again, you have to treat this altogether as a new project. Each appln has specific set of users and has their own concurrent annd active user load. Differnt sizings need to be performed for hardwares for each newly created application. Require some 5-10 days of rigrous planning based on concurrent user load and monitoring patterns and differnt parameters that need to be planned.

3) Tuning Application: Once you are live in production you application may perform as you expected, but may differ and further require tuning. This is an ongoing activity and should be done after each major functionality Go-Live.

4) Issues resolution: Segregation may help isolate the originating point of any issue in this kind of environment where there are differnt category of users accessing these applications. Further analysis could be done based on logs available and other techniques.

Understanding Bulk Order functionality and Implementation

2009-11-12T15:53:00.002+05:30

'Bulk Order' module provides the ability to mass-generate orders for a group of assets that are based on a set of common actions. For e.g. A telco is launching new-services, say pre-paid cards. Bulk Order functionality could be used to create bulk pre-paid numbers which could directly be connected/activated to/for the direct user once sold (this example is just an imagination for understanding). Please note that this is an optional module that requires additional licensing and installation files.

Siebel Industry Applications - Bulk Orders Installation Toolkit could be downloaded from Oracle's eDelivery site (http://edelivery.oracle.com/).

The implementation instructions:

Lock the Symbolic String Project and add the ENU and FRA records as necessary.

The following file was imported:

Table_BulkRequest.sif

The database was then extended for the following tables:

S_BR_ACTION
S_BR_ACTN_SET
S_BR_EXCPTN
S_BR_INST
S_BULK_REQ
S_ORDER_ACTNSET

The following files were then imported in order:

SIS_OM_Base_Architecture.sif
Order_Entry.sif
CUT_Siebel_Communications.sif
ABO_Bulk_Request.sif
ABO_Bulk_Request_UI.sif
Product Selection UI.sif

Compile SRF.

Add the following new views in Administration - Application > Views:

ABO Bulk Request Action Set Orders View
ABO Bulk Request Exceptions List View
ABO Bulk Request List View
ABO Bulk Request Users Actions View
ABO Order Item Drill Down View
SIS OM Warning Message View (ABO Bulk Request)

Assign the views to the appropriate responsibility.

Log into Siebel eCommunication application using a login including the views added as above (e.g. SADMIN).

Helpful reference could be found at: 8.1.1 Bookshelf > Siebel Order Management Guide Addendum for Communications > Employee Asset-Based Ordering

Understanding Siebel Upgrade Process

2009-11-04T12:11:00.001+05:30

Before pursuing an upgrade, it is imperative that you fully understand the upgrade process, potential upgrade paths, and most importantly, your criteria for considering an upgrade of your Siebel CRM solution.

Upgrade project is similiar to an implementation project which begin with an Upgrade Assessment and project definition, and continue through testing, deployment and training activities.

Following steps are well documented in 'Best practices for Siebel Upgrade' which could be downloaded from 'My Oracle Support':

1. Perform Upgrade Assessment and Planning

This phase includes a granular assessment of the technical complexity of your Siebel CRM upgrade, and an estimate of resources required for the upgrade.

The Upgrade Assessment should achieve the following objectives:

a) Assess the current Siebel CRM environment

b) Analyze new product functionality

c) Evaluate the complexity of the upgrade

d) Estimate the level of effort to upgrade

e) Assess business processes, functional redesign, and current requirements versus obsolete requirements that may be out of scope

f) Recommend upgrade team roles

g) Review interface migration tasks

h) Plan for upgrade tuning

i) Identify data migration tasks

j) Provide logical end-user training recommendations
2. Upgrade the Infrastructure

The first phase of the actual project is to upgrade your hardware and software to meet system and implementation requirements, which includes upgrading the Siebel Enterprise to the new release. This action upgrades the Siebel servers and provides the programs, scripts, input files, and other files required to merge the repository and upgrade the Siebel database.

3. Perform Pre-Upgrade Tasks

This upgrade project phase prepares the Siebel database for upgrade and includes such tasks as closing database connections, clearing pending workflow tasks, and disabling customized triggers

4. Perform Upgrade Tasks

This phase of the upgrade cycle merges your customizations into the new release. You must always plan upgrading you development environment before upgrading test and production environment. Steps like Repository 'Repository Merge' and 'Post-Merge Activities' must be considered while upgrading development environemnt. The ouputs from these steps could directly be used as inputs for test and production upgrade process. This portion of the effort also upgrades the development environment database and includes these tasks:

4a. Prepare application data. These tasks prepare test data for migration. 4b. Involves running the Database Server Configuration Utility in "upgrep" mode. This utility performs a basic upgrade of the Siebel database schema and loads repositories to prepare for the repository merge.Upgrade Siebel database schema (upgrep).

4c Merge repository (Development Environment only). Leverage Siebel Tools to merge your current repository with the repository in the new release. Postmerge utilities upgrade form applets and verify that applets and views are configured correctly.

Run postmerge utilities (Development Environment only) Leverage Siebel Tools to run a set of utilities that examine the merged repository. The utilities analyze any customizations and apply changes to them as needed to conform to the user interface in the new release.

4e Upgrade Siebel custom database schema (upgphys). Involves applying the Database Server Configuration Utility in "upgphys" mode. This step further upgrades the Siebel database with changes resulting from the repository merge and completes the database upgrade. The Database Server Configuration Utility also generates the "customer repository" definition file and logical schema definition file that are used as input to the production test environment and production upgrades.

5. Perform Post-Upgrade Tasks

During this upgrade phase, the environments are set up, applications are configured, and the system is tested as follows:

5a. Set up the environment. These tasks set up the post-upgrade environment, which includes extracting the developer's databases and running database statistics.

5b. Application administration. These tasks set up applications and include such things as setting up user access and visibility of views and screens.

5c. Application configuration. These tasks prepare applications for testing, including data migration for specific applications.

5d. Test the system. For development environment upgrades, you perform basic unit tests to verify application function. For production test environment upgrades, you should perform a full suite of regression and stress tests to verify the system is ready for production.

6. Upgrade the Test Environment
Your test environment should exactly be a copy of production environment. It is extremely important that the upgraded release be tested to validate its function and performance before deploying it to users. In addition, the upgrade process must be tuned to minimize the time required to perform the production upgrade and optimize the environment for improved performance at deployment
7.Upgrade the Production Environment

The production environment is your live, deployed business instance running on the new Siebel CRM release, where your internal and external users interact with the Siebel CRM applications and generate actual business data

Hope this helps....

Siebel Upgrade Roadmap

2009-11-04T00:14:00.002+05:30

Most of the users has questions regarding Road Map for Siebel Upgrade. Here is your answer

Upgrade from 6.x to 8.1 -> Its a two step process with stop over at 7.5

Upgrade from 7.x(HOR) to 8.1(SIA) -> Its a two step process and there are two ways for doing this.

- First, upgrade from 7.x HOR to 8.1 HOR, then from 8.1 HOR to 8.1 SIA.

- Second, upgrade from 7.x HOR to 7.x SIA and then from 7.7 SIA to 8.1SIA

Please note: There are some changes around data model between SEA and SIA. Hence reconfiguration of such business components is required while upgrading from SEA to SIA.

Consult the Siebel Database Upgrade Guide provided by oracle.

S_SRM_REQUEST and S_ESCL_REQ getting queued issue?

2009-10-20T11:53:00.002+05:30

You might have faced Assignments getting queued and S_ESCL_REQ and S_SRM_REQUEST tables growing. Here are few checks I woul like you to ensure -

- Check if there is any record in s_escl_req table

select count(*) from s_escl_req;

If the o/p is more than zero and keeps growing or remains constant, then WorkMonTask comp might be stuck and not processing the records.

Check the task status of WorkMonTask (list tasks for comp WorkMonTask) and see if the row_id or rule_id displayed as part of task_status keeps changing.

It is possible that, S_ESCL_REQ table keeps growing and WorkMonTask is still processing if multple imports gets kicked in and AsgnSrvr keeps inserting records into S_ESCL_REQ table.

So, we need to use the combination of WorkMonTask comp task status, Record count on S_ESCL_REQ and S_ESCL_LOG (for every record gets processed from S_ESCL_REQ, a new record gets added into S_ESCL_LOG)

We need to check AsgnSrvr,SrProc,WfProcMgr and WorkMonTask for any errors/issues.

- If you find records in in S_SRM_REQUEST with status Active, Queued or Errored (pls note that S_ESCL_REQ table may have Zero records), means assignments are stuck for end user. Actual assignments happens only if the records in S_SRM_REQUEST gets processed. You might need to check logs for WfProcMgr and SRProc for any conclusion.

- Check if your environment is meeting all the SRSP listed requirements for softwares and hardwares used?

- Ensure that there is enough free drive space.

- Ensure to check with Oracle Support if there is any Fix available for particular issue.

Hope this helps....

Are 'Siebel Best Practices' getting conflicted?

2009-09-18T13:24:00.001+05:30

Most of you might have noticed the minimum oracle client requirement for Siebel Version 8.1.1 is 11g while Oracle Database Server could be 10.2.0.2 or above. Do you guys think that Oracle is conflicting its own terminology of using

same Oracle versions for Server and Client?

What other things you find conflicting with Oracle provided 'Best Practices'? Your comments would be apprecated!

Domain Change: http://siebelarchitect.com

2009-09-16T00:04:00.000+05:30

Hi All,
Thanks for overwhelming resposes for my siebel technical blogs. I hope it covers the topics you really want to know or if you are in Siebel space, would come up at some point with the topics I have discussed and would be discussing.

Please note down my new web domain for future posts: http://siebelarchitect.com

Regards,
Arun

How To Migrate a Siebel Release To Another Environment

2009-09-15T17:53:00.000+05:30

This article describes the process taken to migrate a Siebel Release to another Siebel environment. For example migrating from the Development environment to the Test / QA environment.

Firstly, it is a good idea to version controls your Siebel configuration items in a source control tools such as Visual Source Safe (VSS). At my current project we use VSS to source control our items and label these items with a particular release version to baseline items for that version. When it comes time to migrate the release to another environment we run a custom extract script which will extract all items from VSS with that particular release label into a particular directory structure to be used for the deployment.

Prior to deployment you need to:

a) Perform a full compile to get an SRF file to be deployed. If you dont know how to do this then look it up in the Siebel Bookshelf.
b) Perform a repository export to get a repository dump file. This can be done using the repimexp tool. Here is an example of how to export the repository using repimexp:

repimexp -a e -c siebsrvr_siebel -u sadmin -p sadmin -d siebel -f c:\Siebel_Repository.dat -l c:\Siebel_Repository_Export.log

Where:

-a - this is e for export
-c - is the odbc source
-u and -p - these are the credentials for the sadmin user
-f - this is the siebel repository export file
-l - this is the log file

The deployment process is outlined as such:

1. Stop the Siebel Servers.
2. In Siebel Tools, rename the current Siebel Repository to another name. In the Object Explorer go to Repository and rename the current Siebel Repository to something like: Siebel Repository V1.0. The current repository needs to be renamed so that you can import the new repository with the name: 'Siebel Repository'
3. Import the repository using the repimexp tool. Here is an example of how to import the repository using repimexp:

repimexp -a i -g ALL -c siebsrvr_siebelsystest -u sadmin -p sadmin -d siebel -r "Siebel Repository" -H 2000 -f c:\Siebel_Repository.dat -l c:\Siebel_Repository_Import.log

4. Perform a DDLSYNC to apply database level DDL operations to be in sync with the new repository. To do this run the following commands in order:

ddldict -u sadmin -p sadmin -c siebsrvr_siebelsystest -d siebel -f ../schema.ddl -e y -a y -l ../DDLDICT.log -n "Siebel Repository" -T DCIR

ddlimp -u siebel -p siebel -c siebsrvr_siebelsystest -g SSE_ROLE -f ../schema.ddl -e n -B SIEB_TBLSPC -X SIEB_INDX -R Y -s Y -l ../ddlsync1.log

ddlimp -u siebel -p siebel -c siebsrvr_siebelsystest -g SSE_ROLE -f ../schema.ddl -e y -B SIEB_TBLSPC -X SIEB_INDX -R Y -s Y -l ../ddlsync2.log

5. Deploy files to the Siebel Servers. These files include the SRF, web templates, image files, configuration files, etc.

6. Generate browser scripts using genbscript. Something like this if you navigate to the bin directory on the siebel servers:

genbscript ./siebel.cfg ../webmaster

6. Start up the Siebel Servers.

7. Activate any new or updated workflows in the Administration - Business Process > Workflow Deployment view.

8. Compile any new or updated PLSQL procedures or packages.

9. Deploy master data changes. This would include any changes required to LOVS, runtime events, state models, assessment templates, etc. All master data changes.

10. Restart the Siebel Servers.

11. Execute any data updates through EIM and/or business service. If these data updates are quite large involving changes to a lot of data then you would most likely want to regenerate the database statistics through consultation with a DBA.

Oracle Hidden Parameters

2009-09-15T16:01:00.000+05:30

Mentioned below is the list of Oracle hidden parameters. Some of these parameters are 'UNDOCUMENTED' and it is necessary to ask Oracle Support before making any changes to these. Any change without proper consultation could be harmful.

Parameter Name                            Description

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

_always_anti_join                         always use this method for anti-join when possible

_always_semi_join                         always use this method for semi-join when possible

_always_star_transformation               always favor use of star transformation

_cost_equality_semi_join                  enables costing of equality semi-join

_cpu_count                                current number of cpu's for this instance

_cpu_to_io                                divisor for converting CPU cost to I/O cost

_cr_server_log_flush                      if TRUE, flush redo log before serving a CR buffer (DFS)

_cursor_db_buffers_pinned                 additional number of buffers a cursor can pin at once

_cursor_plan_enabled                      enable collection and display of cursor plans

_index_join_enabled                       enable the use of index joins

_index_prefetch_factor                    index prefetching factor

_left_nested_loops_random                 enable random distribution method for left of nestedloops

_optim_adjust_for_part_skews              adjust stats for skews across partitions

_optim_enhance_nnull_detection            TRUE to enable index [fast] full scan more often

_optim_new_default_join_sel               improves the way default equijoin selectivity are computed

_optim_peek_user_binds                    enable peeking of user binds

_optimizer_adjust_for_nulls               adjust selectivity for null values

_optimizer_choose_permutation             force the optimizer to use the specified permutation

_optimizer_cost_model                     optimizer cost model

_optimizer_degree                         force the optimizer to use the same degree of parallelism

_optimizer_dyn_smp_blks                   number of blocks for optimizer dynamic sampling

_optimizer_mode_force                     force setting of optimizer mode for user recursive SQL also

_optimizer_new_join_card_computation      compute join cardinality using non-rounded input values

_optimizer_percent_parallel               optimizer percent parallel

_optimizer_search_limit                   optimizer search limit

_optimizer_system_stats_usage             system statistics usage

_optimizer_undo_changes                   undo changes to query optimizer

_optimizer_undo_cost_change               optimizer undo cost change

_or_expand_nvl_predicate                  enable OR expanded plan for NVL/DECODE predicate

_query_cost_rewrite                       perform the cost based rewrite with materialized views

_query_rewrite_1                          perform query rewrite before&after or only before view merging

_query_rewrite_2                          perform query rewrite before&after or only after view merging

_query_rewrite_drj                        mv rewrite and drop redundant joins

_query_rewrite_expression                 rewrite with cannonical form for expressions

_query_rewrite_fpc                        mv rewrite fresh partition containment

_query_rewrite_fudge                      cost based query rewrite with MVs fudge factor

_query_rewrite_jgmigrate                  mv rewrite with jg migration

_query_rewrite_maxdisjunct                query rewrite max disjuncts

_query_rewrite_or_error                   allow query rewrite, if referenced tables are not dataless

_query_rewrite_vop_cleanup                prune frocol chain before rewrite after view-merging

_ordered_nested_loop                      enable ordered nested loop costing

_ordered_semijoin                         enable ordered semi-join subquery

_union_rewrite_for_gs                     expand queries with GSets into UNIONs for rewrite

_unnest_notexists_sq                      unnest NOT EXISTS subquery with one or more tables if possible

_unnest_subquery                          enables unnesting of correlated subqueries

_nested_loop_fudge                        nested loop fudge

_nested_mav_fast_oncommit_enabled         nested MAV refresh fast on commit allowed

_new_initial_join_orders                  enable initial join orders based on new ordering heuristics

_new_sort_cost_estimate                   enables the use of new cost estimate for sort

_multi_join_key_table_lookup              TRUE iff multi-join-key table lookup prefetch is enabled

_like_with_bind_as_equality               treat LIKE predicate with bind as an equality predicate

_push_join_predicate                      enable pushing join predicate inside a view

_push_join_union_view                     enable pushing join predicate inside a union view

_partial_pwise_join_enabled               enable partial partition-wise join when TRUE

_small_table_threshold                    threshold level of table size for direct reads

Few obscure undocumented optimizer parameters in Oracle 10g:

_add_stale_mv_to_dependency_list

_always_anti_join

_always_semi_join

_always_star_transformation

_b_tree_bitmap_plans

_bloom_filter_enabled

_bt_mmv_query_rewrite_enabled

_complex_view_merging

_convert_set_to_join

_cost_equality_semi_join

_cpu_to_io

_db_file_optimizer_read_count

_default_non_equality_sel_check

_dimension_skip_null

_disable_datalayer_sampling

_disable_function_based_index

_distinct_view_unnesting

_dm_max_shared_pool_pct

_dml_monitoring_enabled

_eliminate_common_subexpr

_enable_dml_lock_escalation

_enable_type_dep_selectivity

_extended_pruning_enabled

_fast_full_scan_enabled

_fic_area_size

_force_datefold_trunc

_force_rewrite_enable

_force_temptables_for_gsets

_full_pwise_join_enabled

_gby_

_generalized_pruning_enabled

_gs_anti_semi_join_allowed

_hash_join_enabled

_hash_multiblock_io_count

_improved_outerjoin_card

_improved_row_length_enabled

_index_join_enabled

_left_nested_loops_random

_like_with_bind_as_equality

_local_communication_costing_enabled

_local_communication_ratio

_minimal_stats_aggregation

_mmv_query_rewrite_enabled

_nested_loop_fudge

_new_initial_join_orders

_new_sort_cost_estimate

_no_or_expansion

_oneside_colstat_for_equijoins

_optim_adjust_for_part_skews

_optim_enhance_nnull_detection

_optim_new_default_join_sel

_optim_peek_user_binds

_optimizer_adjust_for_nulls

_optimizer_better_inlist_costing

_optimizer_block_size

_optimizer_cache_stats

_optimizer_cartesian_enabled

_optimizer_cbqt_factor

_optimizer_cbqt_no_size_restriction

_optimizer_complex_pred_selectivity

_optimizer_compute_index_stats

_optimizer_correct_sq_selectivity

_optimizer_cost_based_transformation

_optimizer_cost_filter_pred

_optimizer_cost_hjsmj_multimatch

_optimizer_cost_model

_optimizer_degree

_optimizer_dim_subq_join_sel

_optimizer_disable_strans_sanity_checks

_optimizer_enhanced_filter_push

_optimizer_extended_cursor_sharing

_optimizer_filter_pred_pullup

_optimizer_ignore_hints

_optimizer_join_elimination_enabled

_optimizer_join_order_control

_optimizer_join_sel_sanity_check

_optimizer_max_permutations

_optimizer_min_cache_blocks

_optimizer_mjc_enabled

_optimizer_mode_force

_optimizer_new_join_card_computation

_optimizer_or_expansion

_optimizer_order_by_elimination_enabled

_optimizer_outer_to_anti_enabled

_optimizer_percent_parallel

_optimizer_push_down_distinct

_optimizer_push_pred_cost_based

_optimizer_random_plan

_optimizer_rownum_pred_based_fkr

_optimizer_search_limit

_optimizer_self_induced_cache_cost

_optimizer_skip_scan_enabled

_optimizer_sortmerge_join_enabled

_optimizer_squ_bottomup

_optimizer_star_tran_in_with_clause

_optimizer_starplan_enabled

_optimizer_system_stats_usage

_optimizer_transitivity_retain

_optimizer_undo_changes

_optimizer_undo_cost_change

_or_expand_nvl_predicate

_ordered_nested_loop

_parallel_broadcast_enabled

_partial_pwise_join_enabled

_partition_view_enabled

_pga_max_size

_pre_rewrite_push_pred

_pred_move_around

_predicate_elimination_enabled

_project_view_columns

_push_join_predicate

_push_join_union_view

_push_join_union_view2

_px_broadcast_fudge_factor

_px_pwg_enabled

_query_cost_rewrite

_query_rewrite_1

_query_rewrite_2

_query_rewrite_drj

_query_rewrite_expression

_query_rewrite_fpc

_query_rewrite_fudge

_query_rewrite_jgmigrate

_query_rewrite_maxdisjunct

_query_rewrite_or_error

_query_rewrite_setopgrw_enable

_query_rewrite_vop_cleanup

_remove_aggr_subquery

_right_outer_hash_enable

_rowsrc_trace_level

_selfjoin_mv_duplicates

_simple_view_merging

_slave_mapping_enabled

_smm_auto_cost_enabled

_smm_auto_max_io_size

_smm_auto_min_io_size

_smm_max_size

_smm_min_size

_smm_px_max_size

_sort_elimination_cost_ratio

_sort_multiblock_read_count

_spr_push_pred_refspr

_sql_model_unfold_forloops

_subquery_pruning_enabled

_subquery_pruning_mv_enabled

_system_index_caching

_table_scan_cost_plus_one

_union_rewrite_for_gs

_unnest_subquery

_update_bji_ipdml_enabled

_use_column_stats_for_function

Few undocumented optimizer parameters in Oracle 10g:

 _always_star_transformation

_b_tree_bitmap_plans

_complex_view_merging

_default_non_equality_sel_check

_enable_type_dep_selectivity

_fast_full_scan_enabled

_improved_outerjoin_card

_improved_row_length_enabled

_index_join_enabled

_like_with_bind_as_equality

_nested_loop_fudge

_new_initial_join_orders

_no_or_expansion

_oneside_colstat_for_equijoins

_optim_enhance_nnull_detection

_optimizer_adjust_for_nulls

_optimizer_choose_permutation

_optimizer_mode_force

_optimizer_undo_changes

_or_expand_nvl_predicate

_ordered_nested_loop

_push_join_predicate

_push_join_union_view

_query_cost_rewrite

_sort_elimination_cost_ratio

_sortmerge_inequality_join_off

_subquery_pruning_cost_factor

_subquery_pruning_enabled

_subquery_pruning_reduction_factor

_table_scan_cost_plus_one

_unnest_subquery

_use_column_stats_for_function

_use_nosegment_indexes

Tuning the EIM Import Process and How to Improve EIM Performance During Initial Loads

2009-08-05T21:04:00.000+05:30

This information is very well documentedin the bookshelf:

Tuning the EIM Import Process

Review and tune your EIM process for each set of business data you load, for example, Opportunities, Contacts, or Products.

To tune each EIM import process

Load a single batch with the following flags set in your configuration file:
- Error flag = 1
- SQL flag = 8
- Trace flag = 3
  Setting these flags produces a log file containing SQL statements and information about the length of time each statement took to execute.
Identify SQL statements that might be taking too long to execute.
Using the DB2 EXPLAIN utility, populate the explain table with information on how the DB2 Optimizer is executing each of these SQL statements.
Carefully review any changes in the default optimization level in the EIM log.
Based on the Access Plan and other information from the EXPLAIN output, determine the required indexes for EIM and base tables.
Run EIM with parameters to reach each partition and repeat single process tuning.
Perform .ifb file tuning for each process, and remove unnecessary foreign keys.
Execute the REORG utility on base tables to build a compression dictionary.
Perform parallel process tuning.
Perform buffer pool tuning.
Execute STOSPACE on base tables. This collects storage information on DB2 objects.
Perform RUNSTATS on base and EIM tables.
Perform EXPLAINs to verify access path (required indexes).
Modify statistics if required.
Drop unused indexes on base and EIM tables.
Check space and resize table spaces if necessary.

How to Improve EIM Performance During Initial Loads

There are certain considerations relating to improving EIM performance that are only applicable during an initial load of data into your Siebel application. These are described below.

Unused, Nonunique Indexes

The initial load of data is typically a database-intensive process. Each row that is inserted into the base table requires modifications, not only to the table itself, but to all its affected indexes. However, most of these indexes are never used during an initial load process.

You can usually improve performance by determining which base table indexes are unused by EIM during the import process and dropping them prior to the initial load. You must later recreate these indexes.

Unused Triggers

Dropping unused triggers can improve import performance by reducing the system overhead used to verify trigger constraints. You can use the Generate Triggers (GenTrig) component from Siebel Server Tasks to drop the triggers and to re-create them later on. This component must be in the "Enabled" state. For details on how to work with Generate Triggers, see the Siebel Business Process Designer Administration Guide.

NOTE: If you are using partitioning, do not drop triggers that are used for partitioning purposes. If you do, your EIM process fails, or it inserts all the data in a single partition.

The Free Space Parameters

If you use your Siebel application primarily for queries, updates, and deletions, alter your table spaces and indexes to provide optimal insert and update performance.

To improve the EIM import and update SQL performance, set PCTFREE for table spaces and indexes to a value of 20 or 30 prior to the EIM initial load, and maintain these settings at 20 or 30 for subsequent loads.

Insert Performance on Base Tables

You can improve the insert performance on base tables during an initial load of data into your Siebel application by following these guidelines:

Set PCTFREE to zero for data and sequential index.
Set FREEPAGE to zero for data and index.
Use Page Lock if you are inserting many rows or pages sequentially.
For a data sharing environment, set the member cluster option to reduce space map and data page P-Lock contention.

Alter base table spaces to PCTFREE and FREEPAGE values of 20 or 30 for subsequent import processes. You should also load or reorganize any altered items before the new values take effect. The following example demonstrates how to set these values for your table spaces and indexes. Substitute your own table space and index names for your implementation.

ALTER TABLESPACE SIDB0401.H0401000 PART 1 PCTFREE 20;
ALTER TABLESPACE SIDB0401.H0401000 PART 2 PCTFREE 20;

ALTER INDEX SIDB0401.S_ADDR_ORG_M6 PCTFREE 20;
ALTER INDEX SIDB0401.S_ADDR_ORG_P1 PCTFREE 20;
ALTER INDEX SIDB0401.S_ADDR_ORG_P99 PART 1 PCTFREE 20;
ALTER INDEX SIDB0401.S_ADDR_ORG_P99 PART 2 PCTFREE 20;

Transaction Logging

Siebel transaction logging is unnecessary during an initial EIM load, and should be disabled by setting LOG TRANSACTIONS = FALSE in your .ifb file. For more information on transaction logging in EIM, see the Siebel Enterprise Integration Manager Administration Guide

Actuate to BI Publisher conversion in Siebel 8.1.1

2009-08-05T11:36:00.000+05:30

Lot of users/customers still has issues and questions understanding whats going on with Actuate Reporting? Well, Actuate will not going to be supported with and 8.1.1 as per my understanding and for the customer who are planning to upgrade to Siebel 8.1.1 should also plan well in advance to move their reporting system from Actuate 8.0 to BI Publisher. BIP is being provided OOB with Siebel 8.1.1 and most of the OOB actuate reports are already been converted to BI Publisher reports in this version.
Note: With not supported I mean oracle might not provide support for this reporting tool anymore. Licensee who purchased from Siebel/Oracle can continue to run on Actuate as long as they want as it's a perpetual license. Actuate maintenance for the Actuate software will need to be purchased through Actuate directly though after December 31^st, 2009.

Once an SR has been raised the Technical Support Engineer needs to contact Siebel Product Management with the information below to obtain a unique/temporary password that can be given to the customer to download from Metalink. The password is valid for 7 days.

The Siebel Reports Adapter is available on AIX, Windows, Solaris, Linux and HP-UX (Note hybrid architecture, HP-Itanium Siebel Server and any other platform for Reports Server since Actuate is not supported on HP-Itanium).

Siebel Reports Adapter

The Siebel Reports Adapter is available for upgrading customers only and enables Actuate reports to run with Siebel 8.1.1. The Siebel Reports Adapter is available from Oracle Siebel Technical Support

Functional Overview

In the Siebel 8.1.1 release there is a new reporting tool, known as Siebel BI Publisher (or Siebel BIP). Siebel BIP is to be documented in the Reports Admin guide, and is the replacement for Actuate.

To create reports it uses EAI technology to extract the data, then Oracle BI Publisher to produce the reports based on a predefined report template and the extracted siebel data. To generate the report data we use Oracle Business Intelligence Publisher Server (via Webservice call)

Here is a brief summary of how reports are defined with Siebel BI Publisher:

Data definition:
The definition of the data to be available for the report is in Integration Objects.
All BIP Integration Objects need to be prefixed "BIP". A number of OOTB Integration Objects are supplied.

If customers have custom buscomps/views etc, some of these OOTB Integration Objects may not be suitable, so new Integration Objects would need to be created in Siebel Tools.

There is a view, which enables users to generate a sample data/XML file based on the Integration Object.

Report Definition:
Oracle XML Publisher plugin for Microsoft Word is used to create the report template files. It uses the XML file, generated from the sample view as a source.

Report Registration
Reports are registered and associated with a BIP Integration Object and report template.

View Association
A view is asscoiated with one or more of the registered Bip reports.

Actuate Report Conversion Assistance

There is a .zip file that could be downloaded from metalink3. This utility only works on windows client. The utility internally has a 'run' command that need to be executed to convert Actuate outputs (.bas) to .rtf format.

Actuate Conversion Summary

The Actuate Conversion project was designed to provide out of the box report templates for the BI Publisher integration in Whistler. This was designed as a tool and consists of two main functionalities:

1. The Layout Creator - This is the Java program, which takes the Actuate (.BAS) file, parses all the controls, functions and creates an RTF template with the data place holders. This tool also extends to convert key functions, simple controls (Charts, Tables) to the equivalent BI publisher report artifact

2. The IO Creator - This is the Java program, which uses the Siebel Report definition and creates equivalent Integration Object, Component and Fields

Following diagram depicts the Actuate Conversion process:

What makes a PERFECT Siebel Architecture?

2009-08-04T16:21:00.001+05:30

Well Guys, my question might be easy and small to answer but hard and costly to implement. How do you define a or what makes a PERFECT Siebel Architecture?

Is it something:

- Which has high availablity and load balancing concepts? (Just think of: How many customer have you seen which has a implemeneted 'Maximum Availability Architecture'?)

- Cost savy architecture with just required functionalites to support failovers?

- Which have all sort of data authentication features and firwall blockages?

- Which has a parallel replica?

- With zero downtime and maximum availability to the users?

- Which is fully safe to failovers, 100% data recoverable, 100% clustered, 100% flash backed?

Share your thoughts!!

Marking indexes as "unusable" to speed up inserts (10g/Siebel 8.0)??

2009-07-02T12:30:00.000+05:30

Well, this question was around my mind for quiet some time. Basically, I still haven't verified with any Production environment yet. Can we be sure that no I/O at all occurs for "unusable" indexes?

My answer would be - why not try and test if I/O occures for 'unusable' indexes or not(in test environement). The simplest way to do a 10046 trace on the insert/select to get a "good feel" for what's going on. If you also install the trace analyzer you will get an IO summary report with level 12 tracing that should point to specific issues with the select/insert. For this purpose you can stick some SESSION SQL into your ifb file 'alter session set events '10046 trace name context forever, level 12'; then interpret the results later using the trace analyzer utility

Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

Is sharing hardwares among two Siebel environment good?

2009-06-09T16:59:00.000+05:30

Well, I came across this question from one of the Technical Account Manager while sizing for one of the biggest Telco.

What I would say is, yes, its fine but care and considerations regarding hardware resources should be taken. We surely, don't want to get an existing enviroment affected by sharing its hardwares with a new application.

Although we typically recommend to run Siebel on dedicated hardware it might make good sense to use the hardware for other purposes, especially web server and gateway as resources on these servers
will typically be under-utilized. Most of the processing is done on the Siebel application servers and database which I recommend not to use for other applications.

I have seen one customer using a load balanced farm of web servers for other purposes (web server for other applications). It all depends on the resource requirements. If web servers are heavily used, don't do it. If not, go ahead but test first!

Same goes for gateway!

Navigation in Siebel 8.x is sometime slow

2009-06-09T15:50:00.000+05:30

I noticed that in many log files time taken at the MessageFlow category is longer than expected. Log/Error:
MessageFlow MsgFlowDetail 4 000600a54a2c1496:0 2009-06-08 13:18:02 248: Returned from SessionHandleMsg(7700) for task 22020455, pErrStack = 0x2b5c5238
MessageFlow MsgFlowDetail 4 000601344a2c1496:0 2009-06-08 13:18:14 248: ProcessMessage() worker will process 0x20c867b0, pErrStack = 0x2b5c5238

Explanation:
The time difference above implicitly denotes the "think time" on the client.
"ProcessMessage() worker " & "Calling SessionHandleMsg" denotes the time when the request came from SWSE/client to the Object Manager.
"Returned from SessionHandleMsg" denotes the time when the request went back from OM to the SWSE/client.

In order to trace the time taken by the OM for a message, look for the time consumed between "ProcessMessage()/Calling SessionHandleMsg" and "Returned from SessionHandleMsg"

If there is a considerable differnce between the two, tuning might be required.

How can file attachments be imported via EIM?

2009-06-09T15:46:00.000+05:30

In Siebel versions 5.x ,6.x and 7.x, users can run Enterprise Integration Manager (EIM) to import file attachments. For example, there are already some Service Request records in S_SRV_REQ table. Please follow several steps to import file attachments to Service Requests:
In version 6.x and 7.x:
1. Put the file to be attached in the Siebel_Server\input directory (i.e. ABC.doc)
2. In the EIM_SR_DTL table, populate:
SR_SR_NUM
SR_BU
ATT_FILE_NAME (’ABC’)
ATT_FILE_EXT (’DOC’)
ATT_FILE_SRC_TYPE (’FILE’)
3. Run EIM to import file attachment to S_SR_ATT table.
4. A new .saf file will be generated in the file system after EIM loading.
In version 5.x:
1. Put the file to be attached in the Siebel_Server\input directory (i.e. ABC.doc)
2. In the S_SR_DTLIF table, populate:
SR_NUM
SR_FILE_NAME (’ABC’)
SR_FILE_EXT (’DOC’)
SR_FILE_SRC_TYPE (’FILE’)
3. Run EIM to import file attachment to S_SR_ATT table.
4. A new .saf file will be generated in the file system after EIM loading.

Tuning EIM batches for improving performance

2009-06-09T15:44:00.000+05:30

I went through this post on another blog and I thought of sharing it with you.

Steps to tune EIM batches to improve performance !!!!
Here are some options that can be tried to improve Enterprise Integration Manager (EIM) performance:
Check to make sure all the standard indices are in existence for the tables involved.
Limit tables and columns to be processed using ONLY BASE TABLES/COLUMNS configuration parameters to minimize the work that EIM has to perform.
Consider switching off Siebel transaction logging during the EIM run. This will definitely improve performance; however, it must be balanced with the need for mobile users to re-extract afterwards.
Try different batch sizes. Often, large batch sizes are not the most efficient. Siebel Systems recommends that customers use a batch size no more than 5000 rows, preferably less than 2000 rows in a single batch.
Try using batch ranges (BATCH = x-y). This allows users to run with smaller batch sizes and yet avoid the startup overhead on each batch.
Perform regular table maintenance on interface tables. Frequent insert or delete operations on interface tables can cause fragmentation in the table. The database administrator will know how to detect and correct fragmentation in the EIM interface tables.
Delete batches from interface tables on completion. Leaving old batches in the EIM interface table will waste space and could adversely affect performance.
Run independent EIM jobs in parallel. EIM jobs that have no interface or base tables in common can be run in parallel.
Set the USING SYNONYMS parameter to false in the .IFB file to indicate to EIM that account synonyms do not need to be checked.
Avoid using the UPDATE PRIMARY KEYS parameter in the IFB file.
ORACLE INSERT APPEND MODE. The default value is FALSE, which means EIM does not use use append mode for insert at EIM process step 9, avoiding a deadlock when running parallel EIM processes. If you need to improve step 9 performance, you can set the value to TRUE. *
Delete and Merge performance is improved if you create some specific temporary indices first. Please refer to Siebel Enterprise Integration Manager Administration Guide, Version 7.5, Creating Temporary Indexes for more details *
If using Oracle database, ensure that Oracle rule-based optimizer is used. Siebel Systems has highly recommended the Oracle rule-based optimizer because it provides the consistent behavior needed for effective performance tuning.
If all else fails, set “Trace Flags”=1 and “SQL Trace Flags”=8 and rerun the batch. The resulting task log will identify slow running steps and queries.
Note:
For Siebel eBusiness Application Version 7, this is documented in the Bookshelf: Siebel Enterprise Integration Manager Administration Guide > Frequently Asked Questions > Performance Questions.
For Siebel eBusiness Application Version 6, this is documented in the Bookshelf: Siebel Enterprise Integration Manager Administration Guide > Advanced Usage > Questions and Answers > Q. How can I tune my EIM batches to improve performance?
* Valid only for version 7.5.x

Siebel And Its Integrations

2009-05-15T12:31:00.001+05:30

How many 'Products' or 'Applications' (Internal or 3rd Party), you know which could be integrated with Siebel Application. I Hope to see your comments. It would be great if you share some knowledge on -

1. How the specific Product or Application you might be suggesting could be integrated (Out-Of-Box, Inbuilt API's, 3rd parth integration techniques etc)?

2. What all 'Applications' or 'Products' you wish see, integrated with forecomming releases of Siebel?

3. Why you want the 'Applications' or 'Products' mentioned in Point# 2 should be integrated with Siebel?

How CTI work?

2009-05-12T10:46:00.000+05:30

Well, most of us might have heard of this three alphabet word "CTI". What does it means and how is it intergrated with Siebel application? Lets share our thoughts.

: CTI stands for "Customer Telephoney Integration". Basically, most of the companies which carters services to their customers use this product integrated with siebel call center application to make/recieve calls.

CTI+Siebel involves a mix of infrastructure and archichecture planning requirments.

The general architecture is as shown below:

Communication Server - CTI flow:

Why not move to BI Publisher?

2009-05-11T13:50:00.000+05:30

Its very much clear that oracle already expressed its intention of moving all its report from Actuate to BI Publisher.

Siebel 8.1.1 has already inhoused BI publisher solution which you could get integrated with this package.

Few things that comes to my mind:

1. What will happen to the customers who are on old versions of Siebel (say 7.5, 7.7, 7.8 etc) and not planning to upgrade?

<Arun>: I haven't heard of BIP integrating with 7.x right now (may be I don't have that information,keep finding and post here), I think they can continue with the existing version of Actuate (but Siebel is not going to provide any support).

2. What will happen to existing Actuate report for the customer who are planing to upgrade siebel to 8.x?

<Arun>: If customer upgrading to 8.x, there is a converion strategy devloped by oracle for moving all Actuate report to BIP. So, no worries. get your TAM involved for further discussions.

3. Don't know much about Siebel BIP. Where should I get information from?

<Arun>: Hey, METALINK is there. Brushup your Grey Cells

Let me know if you have any questions. I could be reached at arunhudiya@in.com

Oracle CBO and Siebel Business Applications

2009-05-11T13:23:00.000+05:30

Summary

A query optimizer determines the best Execution Path for performing each query. The Oracle Rule Based Optimization (RBO) method chooses its plans based on a set of fixed rules. While the Cost Based Optimization method leverages a costing model based on statistics, which evaluates all of the different access and join methods for the specific query and uses the most efficient one.

RBO enumerates a deterministic, ordered (fixed rank) set of plans based on a pre-defined set of rules and always picks the first applicable plan in the order. For example, if you have a query of the form:

SELECT * from EMP where EMPNO > 50

and if there is an index on column EMPNO, then one of the RBO's rules specifies that this query will always be executed using the index. The behavior of the RBO is entirely fixed; the execution plan for the sample query remains the same regardless of whether the EMP table has 10 rows or 10 million rows, and regardless of whether the "EMPNO > 50" predicate will return 2 rows or 2 million rows.

On the other hand, the cost-based optimization strategy generates multiple execution plans for a given query, with each having an estimated cost computed for each plan. The query optimizer chooses the plan with the most efficient cost (for example, the highest performance and lowest cost). The CBO requires accurate statistics in order to deliver good query performance. Hence, it is crucial that the statistics the CBO uses are available and accurately maintained.

Preparing and Administrating the Siebel Database for CBO

When using Oracle's Cost Based Optimization with the Siebel database, there are some requirements that should be followed regarding the database parameters configuration and the Optimizer statistics generation on tables, columns and indexes, as specified below:

Database Parameters

Oracle Parameter	9i Default Value	10g Default Value
HASH_JOIN_ENABLED	True	** Obsolete
OPTIMIZER_FEATURES_ENABLE	9.2.0	10.1.0 *
OPTIMIZER_INDEX_CACHING	0	0
OPTIMIZER_MODE	CHOOSE	ALL_ROWS
PARTITION_VIEW_ENABLED	False	** Obsolete
QUERY_REWRITE_INTEGRITY	Enforced	Enforced
STAR_TRANSFORMATION_ENABLED	False	False

In addition, for the Siebel application to work effectively and efficiently, the following parameters need to be set to the recommended values other than default values.

Oracle Parameter	Description	9i Default Value	10g Default Value	Recommended Value
OPTIMIZER_DYNAMIC_SAMPLING	This parameter controls the level of dynamic sampling performed by the optimizer.	1	2	1
OPTIMIZER_MAX_PERMUTATIONS	This parameter restricts the number of permutations of the tables the optimizer will consider in queries with joins. A higher value result in higher parse time, a lower value carries the risk of overlooking a good plan. NOTE: Starting with Oracle Database 10g, this parameter has been made obsolete. Refer to the Oracle documentation for further information.	2000	** Obsolete	100
OPTIMIZER_INDEX_COST_ADJ	Controls the access path selection to be more or less index friendly.	100	100	1
QUERY_REWRITE_ENABLED	Enable or disable query rewriting globally for the database	False	True	False

Remember - More details could be found on Oracle Metalink.