When creating the database session, the Siebel database connector makes the following session level changes
It is important to always remember these session hints when analyzing or tuning the performance of Siebel queries. For example, if you are executing a problematic SQL from SQLPlus, or reviewing an execution plan, and you did not set the above session hints, the plan that Oracle will use will most likely differ from the plan the optimizer uses for Siebel.
When analyzing Siebel-generated SQL outside of Siebel, it is therefore recommended to set the following session identifiers when opening the connection:
—it sets the optimization mode to First_ROWS_10, disables hash join, disables sort-merge, and, using two columns, enables a sanity check for the join operation.

alter session set optimizer_mode = first_rows_10
/
alter session set "_HASH_JOIN_ENABLED" = false
/
alter session set "_OPTIMIZER_SORTMERGE_JOIN_ENABLED" = FALSE
/
alter session set "_OPTIMIZER_JOIN_SEL_SANITY_CHECK" = TRUE

In most implementations, a high number of indexes already exist on some of the tables (such as, S_OPTY) and you must be careful not to add too many indexes. A new index can fix one query but may slow down another. Moreover, each new index adds some overhead to the system and slows down DML statements (such as, Insert, Delete, and Update). It is critical to pick the correct set of columns (both the columns and their order are important) so that the CBO can properly use the index, and the index can subsequently improve performance.
A recommended solution is to index the columns that improve the queries reported by DBA as top queries. For example, if a user is complaining about a slow response time in production, or if a LoadRunner transaction showed a higher average than the SLA, you can use sql tools/performance check tools to identify the activity for that user (or transaction) and identify the top SQL contributing to response time. There is no point in indexing a column that speeds up a query, even if the improvement is significant, if the query is executed only once a week. You should consider adding new indexes that improve queries that consume high database resources, as well as queries that cause end-users to wait a lengthy amount of time for a response.

PDQ refers to Pre Defined Queries, as the name speaks for itself these are the saved queries in Siebel application. Generally, users can click on Query button and enter query criteria and click go button, and save the query by navigating to  Query Menu -> “Save Query As” There are two types of PDQs

1. Private PDQ.
2. Public (Non – Private) PDQ.

Private PDQ: Private means that the query is only available for those users who created the query or for those who saved the query. This is normally done by application users and is available only to the user who saved it.

Non-Private/Public PDQ: Public PDQ’s are typically created by AppDev team / Admin team. These PDQ’s are available to all the users using the Siebel Application. And the Private flag is unchecked for these. Siebel Admin can make a private PDQ to Public by un-checking the private flag.

Understanding PDQ: PDQ’s are associated with the Business object, you can find the PDQ’s in Siebel applications at the upper right corner of the screen.

What can be done with PDQ?

PDQ has two functions

1. Search - Filter criteria to restrict the records
2. Sort – sort the records based on the field values

Understanding the PDQ Criteria:

As described earlier the PDQ Criteria will have 2 parts one search criteria, second sort criteria.

E.g. 1:

If you look at the below table it has 3 columns

• PDQ Name – Name of the PDQ
• Business Object Name – Name of the Business Object associated with PDQ
• Search Spec (including BC Names) - PDQ criteria showing search and Sort specifications.

Let’s break down the Search Spec text to understand what its doing – 'Register TC'.Search = "[Register Id] LIKE ""A*"" AND [Replacement Flag] = ""Y"""

• 'Register TC' – Business component Name
• 'Register TC'.Search - Search on the ‘Register TC’ Business component
• "[Register Id] LIKE ""A*"" AND [Replacement Flag] = ""Y""" - Search Criteria

Search criteria will have one are more filter conditions, lets understand the above search criteria. Field names are included in the [Field Name] (brackets).so the above search criterion is  “ show me all the registers where Register id is begins with A and replacement flag is Y.

E.g. 2: In Progress and Resolution Required

Eg2: has both search and sort criteria,

• Search criteria - show records from Compliance Infraction TC Business component where Resolution required field is checked and Status is InProgress
• Sort Criteria - sort the records in descending based on the creation date.

Operators in search Criteria–

ETL jobs run every day to pull data from Transactional OLTP database Servers and Load Analytical OLAP warehouse data bases takes more time than its expected, Follwing are tips that will help you improve the ETL performance.
Following are the daily running ETLs along with the current (before applying Improvement Tips) timings:-

Views Definition Optimization:- There were 3 main views used to get the data from the main source tables. The existing definition of the views was:-

• SELECT ACT.* FROM S_EVT_ACT ACT(NOLOCK)JOIN S_SRV_REQ SR(NOLOCK) ON ACT.SRA_SR_ID = SR.ROW_ID
   
• SELECT ACT.* FROM S_EVT_ACT ACT(NOLOCK)JOIN S_DOC_AGREE ITR(NOLOCK)ON ITR.ROW_ID = ACT.AGREEMENT_ID WHERE ITR.X_QUICK_TICKET_ROW_ID IS NOT NULL
   
• SELECT ACT.* FROM S_EVT_ACT ACT(NOLOCK)JOIN S_PROD_DEFECT IR(NOLOCK)ON ACT.SRA_DEFECT_ID = IR.ROW_ID

  Here the S_EVT_ACT, S_SRV_REQ, S_DOC_AGREE and S_PROD_DEFECT are main source Transactional database tables having huge data. While pulling from these views, the task was taking almost 1 Hr for each view. (This each view is used in 3 different ETLs).
  The reason for taking that much time was because of the join condition with main tables and for Optimizing those Views, Join condition between the main tables has been edited so that View should not query the S_SRV_REQ table for the View 1, S_DOC_AGREE for the view 2 and S_PROD_DEFECT for the view 3. So the views definition has been changed like following:-

  1. SELECT ACT.* FROM S_EVT_ACT ACT(NOLOCK)WHERE ACT.SRA_SR_ID IS NOT NULL
  2. SELECT ACT.* FROM S_EVT_ACT ACT(NOLOCK)WHERE ACT.AGREEMENT_ID IS NOT NULL
  3. SELECT ACT.* FROM S_EVT_ACT ACT(NOLOCK) WHERE ACT.SRA_DEFECT_ID IS NOT NUL

     above view definitions are doing look up in the same table rather then main table look up..

     Results: After changing the views definition as shown above, it really has improved the performance. Now the data pull task is taking just 2-3 minutes against the 1 hour earlier time. So save of almost 50 minutes for each ETL.

     1. SSIS Lookup Task (Null Column Handling):- There was one lookup task as a process of ETL processing through SSIS package that was taking almost 4 hrs each day means for each incremental day processing records it was taking almost 4 hrs that was very abnormal and was affecting the reporting very badly. So while analyzing the task following issues has been found:-
        1. There was one column in the Source-Destination mapping that is not mapped with the source. So there was no data in this column and this column name was Country for this example.
        2. Look up SQL was like following:-

      

     SELECT ROW_WID,PERSONNELNUMBER,COUNTRY FROM WC_PERBIGAREA_D(NOLOCK)

     As Country or say any columns that is being used in the Look UP SQL is having NULL values then the Look Up SQL will always return the same no of records for each incremental run means As the comparison with NULL always return False so the Look Up will always iterate for all the records in the Incremental pull against the Null Country Record in the existing table and returns the Incremental multiplied Null values records. That’s why it was taking almost same 4 Hrs for each incremental run.

     Solution:- Following changes has been for improving the Look Up task performance beasically the NULL values has been handled:

     1. Update Column(NULL):Updated the main WC_PERBIGAREA_D (NOLOCK) table for the COUNTRY column from NULL value to the relevant value by joining the tables to populate correct country value.
     2. Modified Lookup Mapping: Modified the LookUp target mapping to include COUNTRY (Missing Column) mapping also so that from this point onwards the Country values should not become NULL and Should not effect the performance.

      

     Results: After doing the above changes, the task is now completing in 10-15 minutes. Its again almost 4 hrs save in ETL execution time.

     1. Dead Lock Prevention: “It’s always a best solution to prevent Deadlock to occur at very first place rather then letting Deadlock occur and then recovering from Deadlock”. In one of the SSIS package task, there was truncation of main destination table and then loading fresh full data each time means each ETL run. After analyzing what we observed was that at the time of Truncation of this particular table, as truncation needs exclusive lock, if any user is querying that table then it is getting in to dead lock/Hanged state until unless that blocking is cleared manually. So due to this, ETL some times hanged for 2-3 hrs or even a day also. It was really impacting the daily ETL Performance.

      

     Solution:

     1. Created new temporary table having same schema definition as main destination table that is being truncated in each ETL run.
     2. In each incremental run, truncated only the temporary table and loaded data into the temporary table. So deadlock/blocking is prevented by not truncating the main table.
     3. And for the data insertion from temporary table to the Main table followed the UPDATE/INSERT strategy. This way there was no hanging of ETL because now table is not exclusively locked and no user queries are affected and the ETL start running faster as there is no hanging point now. And the performance is also consistent.

     Results:

     1. 1. ETL’s Performance improved because there are no blocking points.
        2. Users Queries are not affected.
        3. Consistent ETL Performance.

     Following are the daily running ETLs timing after applying Improvement Tips:

     ETL Name	Time Taken(Minutes) After Optimization	Solution Applied
     ETL 1	68(Avg. for 15 ETL runs)	Solution 1.
     ETL 2	151 (Avg. for 15 ETL run)	Solution 1 & 3.
     ETL 3	200 (Avg. for 15 ETL runs )	Solution 1 & 2.

      

      

     
     ETL 1 Performance Graph after Applying Solution 1

      

     

     ETL 2 Performance Graph after Applying Solution 1 & 3

     

     ETL 3 Performance Graph after Applying Solution 1 & 2
      

To constrain the list of values of a picklist field (Eg: ‘Status’) based on another picklist (Eg: ‘Type’) field value of business component (Eg: ‘Contact’) in multi organization environment.

• Consider there are two organizations ‘Org1’ and ‘Org2’.
• Org1 has no constrained on ‘Status’ field in Contact. It is normal pick list based on ‘Picklist Generic’ business component.
• Org2 needs to have constrained on ‘Status’ field based on values ‘Type1’ or ‘Type2’ of ‘Type’ field in Contact.
• Create new picklist based on ‘Picklist Hierarchical’ based on LOV Type, same as the existing pick list mapped to ‘Status’ and map it to ‘Status’ field.

Approach:
Normally, whenever a field value have to be constrained on another field, both the fields have to be mapped to hierarchical pick lists and pick maps will be configured so as to constrain the values. Following is alternate approach that can be followed using UpdOnlyIfNulls object definition of Pickmap
 Configuration Steps:

Business Component: Contact
As Mentioned below, create new calculated fields which will have value based on Organization and ‘Type’ field value

Map the new hierarchical picklist to ‘Status’ field as follows:
Field:

Pickmaps:
Create following pick map for ‘Status’ field

Pickmap UpdOnlyIfNulls:
Create following for pickmap ‘Type Calc’ created above

NOTE: ‘UpdOnlyIfNulls’ is to disable the constraint when the constraining field value is null, using a calculated field as the field for the constraint pick map.

The list of values in Data Administration à List of Values setup can be made as below:

In Siebel Workflow, there is no function for a carriage return line feed. So, if we want to populate a text field and format the contents so that they appear on separate lines, there is no simple way to do it.
One solution would be to write some eScript in a Business Service to return the CRLF escape sequences to the calling routine. The workflow can then call your business service to populate a process property with the CRLF characters and it can then use that process property as and when required.
Another solution exists which does not involve escript is to use siebel vanilla Business Service. There exists a “vanilla” Business Service which returns CRLF as a return argument.
 
Business Service details:
Business Service Name: SSSE Address Parser (eScript)
Business Service Method: GetCRLF  
 
Note : Newline characters can be added in oracle database level using the following ASCI functions CHR(10) – Line Feed and CHR(13) – Carriage Return

Scenario : Need to fill email/sms/fax template content substituted via scripting. Need to dynamically fetch Request ID and Timestamp value pertaining to the instance of the Outbound Communication ManagerWhat is available Out Of Box – you can use Send Email /SMS/Fax window to select the template to substitute the content. User has to manually copy the content Solution : We can use BS Outbound Communications Manager, method ExpandCommTemplate.Inputs: Recipid, RecipBusComp, CommTemplateName, SourceBusObj, SourceId. Outputs: ExpandedText, ExpandedSubject Note : The above method is a hidden one andcan be used for populating the template contents whenever required.   Benefits :

• Extensively used in interfacing with Fax/SMS/Email systems outside Siebel
• Method comes handy and can be used in business services as and when needed
• Once populated the message body can also be stored in desired entities.   

There would have been umpteen number of situations where you might have felt the need for an easier way of debugging profile attributes than logging the values in a script, haven't you?.If yes, then here is a much simpler way and you can do this on the fly.
When the web client is up, replace the URL with the following script:
javascript: alert(theApplication().GetProfileAttr("gNewRecord"));where 'gNewRecord' is a profile attribute.
 
1.      Place the below code in the address bar of the Siebel Application and hit Enter.
javascript:alert(theApplication().GetProfileAttr("Login Name")), results in SADMIN or CCHENG or valid login id.
 
 
2.      Similarly to set a particular Profile Attribute you can write,
javascript: alert(theApplication().SetProfileAttr("Test", "TestValue"));
 
 
3.      javascript: alert(var bo = theApplication().ActiveBusObject(););
This will return you the active bus object and assign it to the variable bo.
 
 
4.      javascript: alert(theApplication().ActiveBusObject().GetBusComp("Contact").GetFieldValue("Last Name"););
This will return you the Contact’s Last Name in that instance. Similarly SetFieldValue on the fly can also be achieved.