Siebel Performance Optimization using custom denormalized columns
21. 9. 2017
Siebel uses denormalized columns to improve the performance of query operation in view where data visibility is based on Siebel View Mode like Personal, Position or Organization. If View Mode is used, Siebel composes Query upon Relationship (Intersection) Table, e.g. S_ACT_EMP for Personal View Mode on Activities or S_OPTY_POSTN for Personal View on Opportunities, etc. Denormalized columns are often created for columns most frequently used for queries to restrict records on intersection table as most as possible before join with base table and applying clause on base table columns.
For example in View My Opportunities, there would be a Query on S_OPTY_POSTN Table restricted by clause on column POSITION_ID. When user queries in the view for particular Opportunities, without the denormalized columns, SQL generated would have WHERE clause for Opportunity base table S_OPTY as well as S_OPTY_POSTN table. One can understand the more denormalized columns, the higher probability, that the user query WHERE clause will run on intersection table.
Improving performance is usually task that is common for the system running several years. As the time goes, records in the most common tables grow and the queries become less and less performing. This performance degradation phenomenon is obvious also for View Mode based Views, where the WHERE clause for Employee Id, Postion Id, Organization still returns ten thousands records that must be joined to base table and filtered or sorted based on the column on base table.
At that moment, one can consider introducing custom denormalized columns as solution for performance improvement of Views based on View Mode queries.
Siebel Tools out-of-box does not allow creating custom denormalized columns. The solution is to create columns of type “Extension”, and rewrite the type in Siebel Database using:
UPDATE SIEBEL.S_COLUMN SET TYPE=’Denormalized’ WHERE ROW_ID=:1;
For the customer we had to improve the performance of Opportunities and Activities since customer was complaining about performance of various My Opportunities and My Activities Views. After vast analysis of Spool, Queries and Explain plans we have found out, that Customer uses a few custom columns and most often uses query on those columns. As those columns were custom, Sales Rep and Personal based view modes queries were not performing as they combine WHERE condition on base and intersection table.
On S_OPTY_TABLE we have introduced three the most often in conditions used columns.
SQL queries before denormalization:
FROM
…..
SIEBEL.S_OPTY_POSTN T38,
SIEBEL.S_USER T39,
SIEBEL.S_ORG_EXT T40,
SIEBEL.S_OPTY_TNTX T41,
SIEBEL.S_SALES_METHOD T42,
SIEBEL.S_PARTY T43,
SIEBEL.S_OPTY T44
WHERE
…..
((T44.X_STATUS IN ( :4 )) AND
(T44.X_STATUS NOT IN ( :5 ))) AND
(T44.X_OPTY_NAME IN ( :6 ) AND T18.NAME = :7 AND T44.TEMPLATE_FLG = 'N')
ORDER BY
T38.POSITION_ID DESC, T38.SUM_EFFECTIVE_DT DESC
SQL query after denormalization:
…..
FROM
…..
SIEBEL.S_OPTY_POSTN T38,
SIEBEL.S_USER T39,
SIEBEL.S_ORG_EXT T40,
SIEBEL.S_OPTY_TNTX T41,
SIEBEL.S_SALES_METHOD T42,
SIEBEL.S_PARTY T43,
SIEBEL.S_OPTY T44
WHERE
…..
((T38.X_STATUS IN ( :4 )) AND
(T38.X_STATUS NOT IN ( :5 ))) AND
(T38.X_OPTY_NAME IN ( :6 ) AND T18.NAME = :7 AND T44.TEMPLATE_FLG = 'N')
ORDER BY
T38.POSITION_ID DESC, T38.SUM_EFFECTIVE_DT DESC
As seen, we can benefit from introducing of denormalized columns, by having the denormalized column; the SQL generated would have WHERE clause mostly on Single table and performance of the query will be faster.
Afterwards, we have created Table Index in Table S_OPTY_POSTN based on columns: POSITION_ID, X_STATUS, X_OPTY_NAME, which have boosted the performance.
Please note, for denormalized column, Siebel ensure that if you are updating the data using any standard Siebel method (UI, EIM, EAI) the value in denormalized columns are automatically kept in synchronization.
One important note for EIM, please be aware of that, if EIM is used for migration of columns which have been denormalized, appropriate EIM Table must be used. For instance if EIM_FN_OPTY1 is used for Opportunities migration to update denormalized column, e.g. X_OPTY_NAME in our case, this table does not have mapping to S_OPTY_POSTN Table and therefore it would not update the corresponding intersection records. To ensure that intersection table denormalized columns will be updated correctly, one must use EIM Table having mapping to Table S_OPTY_POSTN.
Reference to Oracle Document: Doc ID 1614374.1
Späť na Blog