Guidelines for Data Objects Layer

This topic describes guidelines for configuring selected elements in the data objects layer for optimal performance. It contains the following information:

"Multilingual LOVs Query and Cache Performance"
"Managing Database Indexes in Sorting and Searching"
"Reusing Standard Columns"
"Limiting Extension Columns"
"Case Insensitivity and Performance"

Multilingual LOVs Query and Cache Performance

This topic is part of "Guidelines for Data Objects Layer".

Multilingual List of Values (MLOV) fields are implemented below the business component level. Fields that point to MLOVs with enabled target columns return display values that match the current language setting for the session.

For display, the underlying language-independent code is converted to its corresponding display value using a Siebel application lookup. For searching and sorting, however, a database join to the list of values table (S_LST_OF_VAL) is performed. Make sure that any configuration directly involving the S_LST_OF_VAL table is compatible with your Siebel application MLOV functionality.

When a view with MLOVs is displayed for the first time, a separate query on the S_LST_OF_VAL table is made for each field that has an MLOV. The query obtains all of the display values for that MLOV and writes the values to the LOV cache in memory. When the view is subsequently displayed during the same session, the values are obtained from the cache rather than by issuing another query.

Note:

Displaying multiple records in a list applet that contains one or more MLOV fields will cause memory consumption to increase, and can produce poor performance. The problem manifests particularly when multiple fetches are performed against a given logical result set; that is, you scroll through records. It can also manifest when client-side export is performed to automate this behavior, or anytime the NextRecord method is invoked repeatedly on the business component. It is generally recommended to use MLOV fields sparingly in list applets, or to disable client-side export from list applets containing MLOVs.

For more information about configuring MLOVs, see Configuring Siebel Business Applications and Siebel Global Deployment Guide.

Managing Database Indexes in Sorting and Searching

This topic is part of "Guidelines for Data Objects Layer".

A database index is a data structure in the RDBMS that is associated with a table. It provides references to all records in the table for quick lookup and filtering, and is sorted in a particular order for sorting in that order quickly. The Siebel database uses an index to efficiently retrieve and sort the result set of a query.

Indexes provided in the Siebel Data Model are tuned for optimal performance of standard Siebel applications. When you add new business components with custom sorting or filtering requirements, you need to make sure that a database index is present that supports the requirement and delivers the result set efficiently. You might need to add new indexes.

You add indexes using the Index and Index Column object types. The index is added in the database as a result of its being created in Siebel Tools and database extensions being applied.

Note:

The addition of custom indexes does not always improve performance and can reduce performance in some cases. The incremental value of an index depends in large part on the heterogeneity and distribution of the data.

When data is heterogeneous, all or most of the values are unique (such as with row ID values, which are unique). The less heterogeneous the data (that is, the more homogeneity or repeated instances of values), the less benefit the index offers relative to its costs.

For Boolean fields, indexes generally offer little value. Some performance benefit might be found when querying for the least commonly represented values. Little or no benefit is found when querying on more commonly represented values or values that are evenly distributed. Similar guidelines apply for other homogeneous data, such as fields that are constrained to a list of values.

Indexing generally improves performance of SELECT operations. However, it can significantly reduce performance for batch UPDATE and INSERT operations, such as are performed by Siebel EIM.

Discuss any custom index requirements with Oracle Advanced Customer Services. Contact your Oracle sales representative to request assistance from Oracle Advanced Customer Services.

Sort Specification

The Sort Specification property for a business component, picklist, or predefined query orders the records retrieved in a query, and serves as the basis for the ORDER BY clause in the resulting SQL issued. An index needs to be present that supports the order specified in the sort specification. Otherwise, the RDBMS engine physically sorts the entire result set in a temporary table.

The index needs to include the base columns for all of the fields, and to use them in the same order. There can be more columns specified in the index than are used in the sort specification, but the reverse is not true.

For example, the sort specification Last Name, First Name in the Contact business component is supported by at least one index on the S_CONTACT base table. One of these indexes is called S_CONTACT_U1, and it contains the LAST_NAME, FST_NAME, MID_NAME, PR_DEPT_OU_ID, OWNER_PER_ID, and CONFLICT_ID columns, in that order. If you want a sort specification that orders contacts in first-name order, then you would need to create a custom index.

Do not sort on joined columns, because indexes cannot be used.

Search Specification

The Search Specification property for a business component, applet, link, or picklist selectively retrieves rows from the underlying table that meet the criterion specified in the property. The search specification is the basis for the WHERE clause in the resulting SQL issued. An index needs to be present that supports the criterion. Otherwise, the RDBMS might scan through all rows in the table rather than only those to be returned by the query.

The index needs to contain all of the columns referenced by fields in the search specification.

In Sales Rep views such as My Accounts or where organization access control is implemented, if the user queries or sorts columns that are denormalized to the intersection table (for example, NAME and LOC in S_ORG_EXT), then performance is likely to be good. The Siebel application uses the intersection to determine visibility to records in the base table, and indexes can be used on the intersection table to improve performance.

For related information, see "Reusing Standard Columns".

Note:

If a query or sort includes columns that are not denormalized to the intersection table, then performance is likely to degrade, because indexes are not used.

Reusing Standard Columns

This topic is part of "Guidelines for Data Objects Layer".

The architecture and data model of your application has been tuned for best performance. This optimization is achieved by using proper indexes, data caching, and efficient SQL generation, and also by denormalizing columns on certain tables. These denormalized columns are indexed so that the application can improve the performance of complex SQL statements by using these columns for search or sort operations instead of the columns of the original tables.

Note:

Do not remap existing fields, especially those based on User Key columns, to other columns in the same table.

Caution:

Do not use custom denormalized columns without the assistance of Oracle Advanced Customer Services. Denormalized columns can improve performance by allowing indexes to be placed directly on an intersection table, rather than on its master or detail table. However, if this is configured improperly, then the data in the denormalized column can become out of sync with its source. This can result in several problems ranging from inconsistent sorting to corrupt data. Contact your Oracle sales representative to request assistance from Oracle Advanced Customer Services.

Example: Reusing NAME and LOC in S_ORG_EXT Table

The columns NAME and LOC of the S_ORG_EXT table are denormalized into ACCNT_NAME and ACCNT_LOC in the S_ACCNT_POSTN table.

When sorting accounts by name and location in views where the Visibility Applet Type property is set to Sales Rep, the Siebel application uses the denormalized columns ACCNT_NAME and ACCNT_LOC of the S_ACCNT_POSTN table. Doing so allows the use of an index.

If the account name and location were stored in extension columns (for example, X_NAME and X_LOC), then these columns would have to be used for sorting instead of NAME and LOC. Even if these extension columns were indexed, the application could not use an existing index to create the necessary joins and sort the data, because the index is on S_ORG_EXT and not on S_ACCNT_POSTN. Therefore, the result would be a significant decrease in performance.

Query Plan for My Accounts View

The first SQL statement is generated by the standard My Accounts view. The query plan shows that the database uses numerous indexes to execute the statement.

SELECT
   T1.LAST_UPD_BY,
   T1.ROW_ID,
   T1.CONFLICT_ID,
   .
   .
   .
   T10.PR_EMP_ID,
   T2.DUNS_NUM,
   T2.HIST_SLS_EXCH_DT,
   T2.ASGN_USR_EXCLD_FLG,
   T2.PTNTL_SLS_CURCY_CD,
   T2.PAR_OU_ID
FROM
   SIEBEL.S_PARTY T1
      INNER JOIN SIEBEL.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID
      INNER JOIN SIEBEL.S_ACCNT_POSTN T3 ON (T3.POSITION_ID = ?, 0.05)
   AND T2.ROW_ID = T3.OU_EXT_ID
      INNER JOIN SIEBEL.S_PARTY T4 ON (T4.ROW_ID = T3.POSITION_ID, 0.05)
      LEFT OUTER JOIN SIEBEL.S_PRI_LST T5 ON T2.CURR_PRI_LST_ID = T5.ROW_ID
      LEFT OUTER JOIN SIEBEL.S_INVLOC T6 ON T2.PR_FULFL_INVLOC_ID =
   T6.ROW_ID
      LEFT OUTER JOIN SIEBEL.S_ORG_EXT T7 ON T2.PAR_OU_ID = T7.PAR_ROW_ID
      LEFT OUTER JOIN SIEBEL.S_ORG_EXT_SS T8 ON T1.ROW_ID = T8.PAR_ROW_ID
      LEFT OUTER JOIN SIEBEL.S_INT_INSTANCE T9 ON T8.OWN_INST_ID =
   T9.ROW_ID
      LEFT OUTER JOIN SIEBEL.S_POSTN T10 ON T2.PR_POSTN_ID = T10.PAR_ROW_ID
      LEFT OUTER JOIN SIEBEL.S_USER T11 ON T10.PR_EMP_ID = T11.PAR_ROW_ID
      LEFT OUTER JOIN SIEBEL.S_ADDR_ORG T12 ON T2.PR_ADDR_ID = T12.ROW_ID
      LEFT OUTER JOIN SIEBEL.S_INDUST T13 ON T2.PR_INDUST_ID = T13.ROW_ID
      LEFT OUTER JOIN SIEBEL.S_ASGN_GRP T14 ON T2.PR_TERR_ID = T14.ROW_ID
      LEFT OUTER JOIN SIEBEL.S_POSTN T15 ON T3.POSITION_ID = T15.PAR_ROW_ID
      LEFT OUTER JOIN SIEBEL.S_USER T16 ON T15.PR_EMP_ID = T16.PAR_ROW_ID
      LEFT OUTER JOIN SIEBEL.S_ORG_SYN T17 ON T2.PR_SYN_ID = T17.ROW_ID
      LEFT OUTER JOIN SIEBEL.S_ORG_BU T18 ON T2.BU_ID = T18.BU_ID AND
   T2.ROW_ID = T18.ORG_ID
      LEFT OUTER JOIN SIEBEL.S_PARTY T19 ON T18.BU_ID = T19.ROW_ID
      LEFT OUTER JOIN SIEBEL.S_ORG_EXT T20 ON T18.BU_ID = T20.PAR_ROW_ID
WHERE
   ((T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG != 'N') AND (T3.ACCNT_NAME >= ?))
ORDER BY
   T3.POSITION_ID, T3.ACCNT_NAME
Query plan : T3(S_ACCNT_POSTN_M1),T2(S_ORG_EXT_P1),T1(S_PARTY_P1),T15(S_POSTN_U2),T10(S_POSTN_U2),T4(S_PARTY_P1),T12(S_ADDR_ORD_P1),T13(S_INDUST_P1),T7(S_ORG_EXT_U3),T16(S_USER_U2),T11(S_USER_U2),T17(S_ORG_SYN_P1),T6(S_INVLOC_P1),T5(S_PRI_LST_P1),T14(S_ASGN_GRP_P1),T18(S_ORG_BU_U1),T19(S_PARTY_P1),T20(S_ORG_EXT_U3),T8(S_ORG_EXT_SS_U1),T9(se)

Query Plan for My Accounts View (Different ORDER BY Clause)

The second SQL statement generated in My Accounts, below, has a different ORDER BY clause. Even though the columns NAME and LOC of S_ORG_EXT are indexed, the database cannot use this index. Performance decreases from the use of a temporary table. The same behavior occurs if the ORDER BY clause uses the columns X_NAME and X_LOC instead of NAME and LOC.

The following example shows a different ORDER BY clause than the previous example query plan.

WHERE
   ((T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG != 'N') AND)
   T3.ACCNT_NAME >= ?))
ORDER BY
   T3.ACCNT_NAME, T3.POSITION_ID
Query plan : TEMPORARY TABLE
T3(S_ACCNT_POSTN_M1),T2(S_ORG_EXT_P1),T1(S_PARTY_P1),T15(S_POSTN_U2),T10(S_POSTN_U2),T4(S_PARTY_P1),T12(S_ADDR_ORG_P1),T13(S_INDUST_P1),T7(S_ORG_EXT_U3),T16(S_USER_U2),T11(S_USER_U2),T17(S_ORG_SYN_P1),T6(S_INVLOC_P1),T5(S_PRI_LST_P1),T14(S_ASGN_GRP_P1),T18(S_ORG_BU_U1),T19(S_PARTY_P1),T20(S_ORG_EXT_U3),T8(S_ORG_EXT_SS_U1),T9(se)

Limiting Extension Columns

This topic is part of "Guidelines for Data Objects Layer".

Adding extension columns to base tables can also affect performance, depending on the data type and length, the joins and queries used, the number of columns, and other considerations. For example, it is recommended not to add more than three columns of the data type CLOB to a base table. See also "Reusing Standard Columns".

Case Insensitivity and Performance

This topic is part of "Guidelines for Data Objects Layer".

Case-sensitive queries perform better than case-insensitive queries, where queries include wildcards. However, you can support case-insensitive queries and reduce the performance impact through appropriate configuration. Siebel Business Applications are case-sensitive by default. You can enable case insensitivity for specified columns. End users can force case-sensitive or case-insensitive queries.

You can use the Case Insensitivity Wizard to configure Siebel database columns to support case-insensitive queries. For best performance, it is recommended to accept the recommendations of this wizard. For each specified column, the wizard creates a case-insensitive column populated with upper-case characters and creates a case-insensitive index. Before you run the wizard, verify that the columns that are to support case-insensitive queries are already indexed, to improve performance for case-insensitive queries (by avoiding table scans).The Case Insensitivity Wizard formats the search criteria for the applicable columns into upper case and appends a wildcard to the search string. The wildcard is used to match data in the specialized column populated with upper-case characters.

A trailing wildcard causes performance issues with Oracle Database and IBM DB2. However, you can resolve this problem by setting the user property Use Literals For Like. Instead of the host variable, a literal string like 'SMITH%' is sent to the database server, which determines that the wildcard is in the last position and uses the appropriate index.

If you enable case insensitivity for a user key column that is denormalized to related visibility tables, then also enable case insensitivity for the denormalized column. For example, if you enable case-insensitivity for Account.Name, then also enable it for the S_ACCNT_POSTN and S_ORG_BU denormalized columns for Account.Name. After you enable case insensitivity for user key fields like Last Name, the Contacts View is slower. For more information, see 536211.1 (Article ID) on My Oracle Support.

For more information about configuring case insensitivity and about different ways of using the Case Insensitivity Wizard, see Configuring Siebel Business Applications, Siebel Database Upgrade Guide, and Siebel Global Deployment Guide. For more information about performing case-insensitive queries, see Siebel Fundamentals and Siebel Applications Administration Guide.

Before you configure case insensitivity, a thorough review of business requirements and performance criteria is highly recommended. In addition, if the feature is enabled, then conduct a performance test with a full copy of the production database. It is also recommended that Oracle Advanced Customer Services be engaged to optimize the configuration and review requirements. Contact your Oracle sales representative to request assistance from Oracle Advanced Customer Services.

Related Books

Configuring Siebel Business Applications

Siebel Database Upgrade GuideSiebel Global Deployment GuideSiebel Fundamentals

Siebel Applications Administration Guide