Understanding parallel processing and the Siebel upgrade

 

Introduction

The Siebel upgrade process supports two types of parallel processing. Both can help decrease the time required to run your production upgrade. In order to avoid confusion lets start by clarifying the two types of parallel processing.

* Use of the Oracle keywords Parallel Nologging on index builds by ddlimp. This type of parallel processing is enabled by selecting the parallel option when prompted by the Siebel upgrade wizard. This option is only available with Oracle and does not apply to SQL Server or DB2.
* Running multiple copies of ddlimp to create different sets of indexes at the same time. This type of parallel processing is available for all databases. This option is enabled by using the Siebel upgrade tuner to create multiple ddlimp streams that will run at the same time. We will refer to this type of parallel processing as index build swim lanes in order to avoid confusion.

A key point to keep in mind with all Oracle index builds regardless of the parallel option is the amount of memory available for index builds is very important. Index builds do two main things in order to build an index: reads and sorts. If you don’t allocate enough memory then your sorts will require temp file usage and these index builds will be slower. The amount of memory available for sort operations is controlled by the parameter pga_aggregate_target if you are living in the modern world or sort_area_size if you are still doing things the old-fashioned way.

Oracle Parallel Index Creation

If you specify “Y” when prompted by the upgrade wizard to use the Oracle parallel option, ddlimp will add the keywords “Parallel Nologging” to all of your index builds. This is actually a mixed blessing since you really only want this option for the large indexes.

The majority of indexes that are built during upgrep are indexes on EIM tables. One of the first things upgrep does is to drop all EIM tables and when these tables get dropped so are their indexes. Specifying Parallel Nologging for these empty EIM tables will make the index builds take longer. When the Oracle parallel option was first introduced, a number of my colleagues at Siebel reported their index builds taking much longer than expected. How much longer you ask? The worst case we have seen was EIM index creation that normally would take around 30 minutes took 8 hours on a 72 processor Sun E10K server. For this reason, we normally never use this option on development upgrades.

The key to fast index creation on Siebel upgrades is to specify Oracle parallel for the really big tables but not specify it for small tables and empty tables like the EIM tables. We will cover this technique later when we discuss multiple ddlimp swim lanes.

So how do you know if you should use the parallel option on your Siebel index builds? We do test index creates before running the upgrade. To do this we, check our development upgrade logs and find an index created on your largest table. Then simply create the index with and without the Parallel Nologging option and compare the difference. It is a good idea to repeat the test several times to ensure the first index build was not biased by not having data loaded into the buffer pool.

If you decide to go with the parallel index option, you need to make sure certain key init.ora parameters are set correctly. The parameters of concern are:

* cpu_count – number of cpu’s. For dual core or hyperthreaded processes this number will normally be the number of cores available.
* parallel_max_servers – maximum number of parallel processes that can run in the Oracle instance.
* parallel_threads_per_cpu – Number of threads per cpu. Normally defaults to 2.

For Siebel upgrades these parameters are important for a number of reasons. First, when the keyword Parallel is given without a parallel degree (e.g. parallel (16)) then Oracle uses cpu_count * parallel_threads_per_cpu = n parallel threads. For example, if cpu_count = 16 and parallel_threads_per_cpu = 2 then 16 * 2 = 32 threads will be used. Not exactly. If you monitor v$px_process or just use TOAD you will see 64 threads launched (numbered 0 – 63). Why? 32 threads will scan the table while another 32 threads will sort the results from the scanners.

If parallel_threads_per_cpu is not large enough, then problems can occur. On one upgrade with both Oracle parallel and multiple ddlimp swim lanes performance was horrible until we realized parallel_threads_per_cpu was not set high enough. In this case, only one swim lane at a time would run while the other swim lanes waited for enough parallel treads to become available.

Given all of this the correct math to determine the minimum value for parallel_max_servers on your Siebel upgrade is:

(((cpu_count * parallel_threads_per_cpu) * 2 ) * swim_lane_count) + safety_factor

where swim_lane_count is the number of swim lanes that are using the Oracle parallel option and saftey_factor is some number, say 10, so that DB monitoring tools such as Quest won’t alert you that you are close to hitting your max.

Multiple ddlimp Index Create Swim Lanes

The Siebel upgrade includes a utility called the Upgrade Tuner. In addition to eliminating zero-row SQL statements this utility also allows you to move certain tables and their corresponding indexes into different ddlimp instances such that will all run at the same time. As an example we might have four ddlimp swim lanes such as

* Huge table all by itself
* Three large tables
* Ten fairly large tables
* Everything else including EIM tables and all small tables

The number of ddlimp swim lanes and tables in each depends on how long particular index builds take. We normally use the standard project management techniques to determine the critical path and move things around so that we minimize the total duration for index builds. It is important to note that there will be a limit on how many swim lanes you will want to create since these swim lanes will be competing for cpu, memory and disk resources and eventually you will reach a point of diminishing returns with too many swim lanes actually taking longer.

Unfortunately, Siebel does not provide an option to turn the parallel option on or off for a given swim lane. You either get Oracle Parallel Nologging for all swim lanes or no swim lanes. In the example above we would turn the parallel option off for swim lane number 4. Please contact us if you would like to know how you selectively turn parallel on or off for a given swim lane.

Note: The content published on this post belongs to Mr. Robert Ponder <founder of “Ponder Pro Serve”>. All credit goes to him.

Old Siebel eScript T (typeless) Engine vs. New ECMA Version 4 ST (Strong Type) Engine

Introduction

Oracle has recently introduced a new eScript engine known as the Strong Type or ST engine. This engine is available in versions 7.7, 7.8 and 8.0. The ST engine is the default engine in Siebel 8.0 and customers are encouraged not to disable it.

Oracle cites these benefits for the new ST engine.

+ Improved performance
+ Improved scalability
+ New features only available with the ST engine

In order to better advise our customers we undertook an independent benchmark comparing the T engine with the new ST engine. Our benchmark included these tests

+ T engine
+ ST engine
+ ST engine with deduce types enabled/checked
+ ST engine with all variables given types

We ran a series of two tests. The second test was added after we did not see the results we expected and the results Oracle cites in their material from Oracle Open World 2006. The tests were:

+ A transaction mix that included math, string and Siebel objects.
+ A transaction mix that included just math and string operations but excluded Siebel objects

Results Findings

Three major discoveries were found on this benchmark. The first was that for the first mix of transactions we did not see very much response time improvement. While CPU on the object manager improved with the ST engine the overall transaction time did not improve very much due to this transaction mix spending the majority of time doing SQL I/O.

The implication from this is that end users may not see much improvement in response time from the new ST engine although their CPU certainly will improve.

The second and third discoveries were identified on the second mix of transactions where we removed the Siebel object scripts and all I/O from the transaction mix. The second finding was when it comes to CPU intensive tasks like mathematical operations and string operations that the new ST engine is about twice as fast as the old T engine.

The third and very much unexpected finding was that using the ST engine with strongly typed variables resulted in horrible performance! With strongly typed variables the performance of the ST engine was twice as slow as the T engine. After opening a service request with Siebel Technical Support it was determined that two change requests exist for this issue and it is considered a product defect.

It was later determined that variables of type String, Number and Boolean are slower when typed on var statements. Other data types such as PropertySet, BusObject, BusComp and Date were as fast or even faster when typed on var statements.

Adding types to var statements presents other problems too. Here are examples of var statements with and without types.

var myString;

var myString : String;

One very undesirable aspect of typing variables is that object compares for String, Boolean, Date and Numeric data types no longer works the way it used to with the old T engine. If you type your var statements you must change your object compares to use the valueOf() operator.

For example if you type your strings this won’t work:

var myString : String;
var yourString: String;

if (myString == yourString) //This won’t work anymore

And instead you must code:

if (myString.valueOf() == yourString.valueOf())

Changing most of your if statements is painful even with our PPS Tools Helper product.

In addition to the above issue with adding types to your var statements you will find a service request that tells you not to type Arrays.

Update – 4/4/2007

After conferring with Oracle Product Marketing we investigated the use of the corresponding primitive data types of chars, float and bool instead of the object types of String, Number and Boolean.

We were delighted with the results. Performance was as good or better than non typed yet will still had the advantages of typing our variables. Plus with the primitive data types we did not have to use the quirky valueOf() function on comparisons.

Conclusion

We love the new ST engine and recommend for it’s features alone.

Given the finding above with strongly typing your var statements our current recommendation about the ST engine is:

Use the new ST engine but be sure to use primitive data types of chars, float and bool instead of String, Number and Boolean. Don’t type Arrays until the stability issue that currently exists is fixed.

Customers with existing eScript code bases need not necessarily add types to their var statements. These customers should still see a big performance improvement.

Also be sure to carefully test your application with the new ST engine before deploying to production! An important product defect relating to passing function parameters by reference has only just been fixed with 7.8.2.5 and 8.0.

URL to another blog which hosts similar content…

Contact Denormalization

Navigate to Site Map -> Assignment Admin -> Assignment Policy Screen
Query for following Policy Records by Name field and backdate the expiration date for these records
ASGN: Contact
Contact Denormalization
Login to Server Manager
srvrmgr /g <Gtvy Srvr Name> /e <Ent Srvr Name> /s <Siebel Srvr Name> /u <User Id> /p <User Pwd>
Start Batch Task for doing the job
Start task for comp asgnbatch with asgnmode=’Denorm’, AsgnObjName=’Account’, ObjWhereClause=<where clause of a query that filters the account records to be considered for the operation>
Exit from Server manager
exit
Observe that all associated contact records have upadted there contact list position data with the associated Account record

CursorMode for Query

The CursorMode is not defined correctly for the BC.ExecuteQuery method. If you do not specify a CursorMode when calling the ExecuteQuery BC method, the method will use the default CursorMode, ForwardBackward. In the case where you traverse the record set using an initial ‘FirstRecord’ followed by repeated calls to ‘NextRecord’, use the CursorMode ForwardOnly.   When using the ForwardOnly CursorMode, the system does not create a buffer/cache maintaining the entire record set, resulting in improved performance. If requirements dictate that script must step backwards through a record set with BusComp.PreviousRecord or by calling BusComp.FirstRecord after moving off the first record, then the scripter must use the ForwardBackward CursorMode. When manipulating the current UI context data set, it is necessary to set the query mode to ForwardBackward because the user will want to traverse the data in both directions for the applet displaying the data.
Consequence: Where a ForwardOnly cursor mode is used in a UI context or in one where reverse navigation is performed, a runtime error will occur when attempting to scroll backwards through the result set. Using a ForwardBackward cursor where one is not necessary will result in sub-optimal performance when fetching and navigating through record sets.  In most cases, a ForwardOnly record set is preferable

“This” Object Reference

Instead of using TheApplication().ActiveXXX() method, it is recommended that you use the “this” object reference. This is essentially eScript shorthand for the current object of the event handler, rather than the active object. For example, in the BC event handlers, this refers to the BC of the event that the code is placed in. If it is absolutely necessary to use the Activexxx reference at run-time, you must consider the possibility that the script may be executed outside of the UI (e.g. through COM, CORBA or JDB).
Some acceptable uses of ActiveBusObject() include:
• Use in business services to retrieve the current UI context.
• Use on applet level code, although this.BusObject() works just as well.

Picklist – Long List Property

A number of pick lists that are not based on the List of Values do not have the Long List property defined. Where the property is set to FALSE, Siebel will attempt to position the cursor on the matching row in the pick applet. In the case of Standard Interactive applications, a query will be issued against the picklist BC if the record is editable. This query is performed when the record is displayed.
Consequence: Where the list is long, Siebel will search through the result set to position the cursor resulting in potential performance problems. This is also true where the picklist field does not possess a value.
Recommendation: Set the Long List property of the picklists to TRUE if they will return a large result set (> 30). If this is done, the picklist can no longer be a static picklist and needs to be exposed using a pick applet. Generally only picklists based on LOVs should have the Long List property set to FALSE.

Accessing Objects in eScript

The following table shows how to access the BC or BO objects depending on the object that the event handler or function is in:
Object: Applet/Control
Target Object            Syntax
Current Applet           this.
Current BusComp      this.BusComp( )
Current BusObject     this.BusObject( )

Object: BusComp
Target Object            Syntax
Current BusComp      this.
Current BusObject     this.BusObject( )
PickList BusComp      this.PickListBusComp(…)
Associate BusComp          this.AssociateBusComp(…)
Parent BusComp        this.ParentBusComp( )
MVG BusComp           this.MVGBusComp(…)

eScript Best Practices

I came across a blog posted by Mr. Sridhar on eScript Best Practices. Its quite informative.

You can access the link here.

Thanks Sridhar for collating all the information on your blog.

Giving Access to another User for Single Object Locking

Problem Description: When Siebel ships its products, the user who has the access to modify the “Allow Object Locking” flag is hard coded in the Business Component and hence the User SADMIN has access to modify this flag. However when we test on Siebel Tools pointing to a Db2 390 where the primary responsibility is with the user QADMIN, this feature is not available or exposed to this user and hence we can’t carry out the operation of modifying the “Allow Object Locking” (AOL) Flag.

Solution: With the help of the following configuration, we can provide access to any user other than the default SADMIN. E.g., please follow the below mentioned steps to give access to QADMIN.

Navigate to Business Component “Repository Project”. Expand it to expose its fields. Query for “OL RO” field. Set the Calculated value for this field to

IIF(LoginName() = “QADMIN”,”N”,”Y”)

Then copy the Siebel.srf file in the tools\Objects\enu folder and compile the changes against the copied file.
Launch Tools connected to the compiled srf. The user should be able to modify the AOL flag now by logging to the Siebel Tools as QADMIN.

Make Field Read-Only based on View

Step1) Create a calculated field which will be used in BusComp UserProperty (Field Read Only Field: FieldName
Name: ReadOnlyFlag
Calculated: TRUE
Value: GetProfileAttr(“ReadOnly”)

Step 2) Create Field Read Only Field User Property in the BusComp User Proeprty
Name: Field Read Only Field:<FieldName>
Value:ReadOnlyFlag

Step3) Write a script at Application_PreNavigate event where the attribute vaue is set

TheApplication().SetProfileAttr(“ReadOnly”,”N”);

if(ViewName == “<Name of the view for which for which the field has to be made read only>”)
{
TheApplication().SetProfileAttr(“ReadOnly”,”Y”);
}

return(ContinueOperation)