Why do we need Gather Stats?

Many times we have heard about gather stat during our project implementation and maintenance. So before understanding Oracle gather stats we will go through below items first:

• Optimizer
• Explain Plan
• dba_tables

1. Optimizer :- Optimizer is one which determines the most efficient way of execution of the sql query and fast retrieval of result set.

2. Explain Plan :- Explain Plan displays the plan used by Oracle Optimizer to execution the sql query.

3. dba_tables :- Oracle dba_tables stores the information regarding all oracle tables. This table contains the information regarding number of rows and last analyzed.

Now we will understand about gather stats. Oracle gather stats helps optimizer to choose most efficient path by updating the stats of the table in the dba_tables. Optimizer uses the information available in dba_table and creates the explain plan to execute a query in a most efficient way.

When the stats are not properly updated then optimizer may execute wrong explain plan for the fast retrieval of result set.

So Oracle gather stats helps optimizer to take correct explain plan to execution the sql query in an efficient way and fast retrieval of result set.

For generating stats Oracle has stored procedure. 

We need to use:

DBMS_STATS.GATHER_SCHEMA_STATS to gather schema objects stats and DBMS_STATS.GATHER_table_STATS to gather table stats.

Siebel OpenUI Physical Renderer Life Cycle

Whenever application started loading ,OpenUI framework calls the Physical Renderer file (PR) and these PR files can also be extend to create a custom PR file to perform the user defined task.

Below is the PR life cycle execution flow:

base_pr_lifecycle

1. Renderer method creates the renderer to render the UI.

2. Init method allows you to override any method that resides in a derived presentation model(PM) by calling the Init method in the predefined PM before it calls the Init method in the derived PM and vice versa for setup method.

3. ShowUI method ,controls the display of physical control that related to an applet control. It also renders the container for the metadata, data, and physical event bindings. For example, when Siebel Open UI renders a list applet as a grid, ShowUI renders the third-party grid control uses for that applet.

4. BindEvents method ,Setting up the user interface binding of physical events to the physical user interface, represented as HTML elements. It captures the user actions, and then translates these actions to logical events in the physical renderer before Siebel Open UI sends them to the presentation model for processing.

5. BindData method ,downloads metadata and data from the Siebel Server to the client proxy, and then binds this data to the user interface. The list columns that a list applet uses is an example of metadata, and the record set that list applet uses is an example of data.

6. AttachPMBinding method attaches handlers to notifications that occur during the life cycle.

we can use GetPM to Calls a method available in PM like,

ExecuteMethod

OnControlEvent

GetSetProperty

7. EndLife Method ,execute at last by ending the life of physical renderer and complete the cycle.

EIM - Best Practice

1. ONLY BASE COLUMNS or IGNORE BASE COLUMNS parameters
List only those columns that are relevant for a particular EIM task.

2. ONLY BASE TABLES or IGNORE BASE TABLES parameters
List only those tables that are relevant for a particular EIM task.

3. USE INDEX HINTS Parameters usage
If FALSE, EIM does not generates hints during processing
EIM processing should be tested with both settings (TRUE and FALSE) to determine which provides better performance.

4. Additional Indexes on EIM tables
Sometimes it is recommended to create additional indexes on EIM tables to improve performance of time-consuming SQL statements.

5. Separate Insert and Update Statements
Always better to separate the Inserts and Updates in different batches
EIM has to perform additional processing to determine whether to insert or update

7. Controlling size of batches
Siebel recommends to use a batch size no more than 5000 rows.
Using batch ranges (x-y) allows us to run with smaller batch size and avoid the startup overhead on each batch.

8. USING SYNONYMS parameter usage
When set to FALSE, it saves processing time because queries that look up synonyms are not used (for Account load) Should not be set to FALSE if multiple addresses are used for Accounts.

9. Transaction logging
When set to FALSE during initial load, reduces transaction activity to the Siebel docking tables.

10. Parallel execution of EIM jobs
Run mutually exclusive EIM processes concurrently.

11. Optimizing SQL
Running EIM job with the following flag settings:
Error Flags: 1
SQL Trace Flags: 8
Trace Flags: 3

12. UPDATE PRIMARY KEYS and PRIMARY KEYS ONLY parameter usage
UPDATE PRIMARY KEYS and PRIMARY KEYS ONLY parameters should be avoided in IFB file.

13. Regular Table maintenance (GATHER TABLE STATS)
Running gather stats would help faster processing of batches.

14. Clear EIM tables
Delete old batch records before running fresh EIM task and also Truncate if possible.

EIM - Optimization techniques

i) Trace Flag Settings, using Synonyms, primary key only Parameters

Limiting the Number of Records and Rows for Merge Processes.

ii) Limit base tables and columns to be processed by using : only base Tables, ignore base Tables, only base Columns and ignore base Columns.

iii) Run independent EIM jobs in parallel. Two or more EIM processes can be started simultaneously by using the Siebel Server Manager.

iv) Always delete batches from EIM tables upon completion. Leaving old batches in the EIM table wastes space and can adversely affect performance.