Tuning sub-optimal execution plan for Siebel SQL.

When talking about the tuning siebel sql transaction, you have limited options as a develper and dba. Siebel SQL transaction tuning is limited to implementation of indexes, use of stored outline and tweaking the access paths.

What does not work for Siebel sql transaction?

Oracle Hints, Restructuring the sql as siebel Object manager itself generate the sql code, Implementation of Bitmap indexes.

Siebel CRM application works on FIRST_ROWS optimization theory. Before any transaction, Siebel exeutes four parameters at session level mentioned below-

1- OPTIMIZER_MODE=FIRST_ROWS_10
2- _HASH_JOIN_ENABLED=FALSE in 10g

For Oracle 9i HASH_JOIN_ENABLED=FALSE
3- _OPTIMIZER_SORTMERGE_JOIN_ENABLED=FALSE
4- _OPTIMIZER_JOIN_SEL_SANITY_CHECK=TRUE

Siebel also requires throughput for other sql. To achieve this Siebel adds /+ALL_ROWS*/ hint in SELECT clause of sql. The injection of this hint in SQL depends upon the configuration and properties defined for the siebel business component.

How to tune siebel slq?

1- Ask Siebel developer to fix the issue at siebel layer by reviewing configuration and properties.

2- Check proper indexes are created on filter criteria.

3- Remove Sort specifications from the sql.

4- Use function based indexes for text search specification sql.

5- Reduce I/O by tuning access paths.

6- Check for the optimizer related bugs and fixes in metalink.

7- Create stored outline using for tuned sql with oracle hints.

What to do when Siebel application is using sub-optimal plan when better indexes and execution plan exists?

In this situation, analyst should implement the stored outline. Let see how to create outline for siebel sql.

1) Set Siebel session level parameters :

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;

2) Check Plan of original query for example

EXAPLIN PLAN FOR SELECT COUNT (*)
FROM A
WHERE A1 IN ( SELECT B1
FROM BELL B, CAT C
WHERE B.B2=C.C2 );

3) Create public stored outline for original query-

CREATE PUBLIC OUTLINE ORG_TEST ON SELECT COUNT (*)
FROM A
WHERE A1 IN ( SELECT B1
FROM BELL B, CAT C
WHERE B.B2=C.C2 );

4) Check Plan of Modified query With HINT -

EXAPLIN PLAN FOR SELECT COUNT (*)
FROM A
WHERE A1 IN ( SELECT /*+ORDERED*/ B1
FROM BELL B, CAT C
WHERE B.B2=C.C2 );

5) Create public stored outline for Modified query With HINT-

CREATE PUBLIC OUTLINE HINT_TEST ON SELECT COUNT (*)
FROM A
WHERE A1 IN ( SELECT /*+ORDERED*/ B1
FROM BELL B, CAT C
WHERE B.B2=C.C2 );

6) Check Outlines created from DBA_OUTLINES-

SELECT NAME, CATEGORY, USED, SQL_TEXT FROM DBA_OUTLINES;

7) Update OL$HINTS and swap Oulines -

UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'HINT_TEST','ORG_TEST','ORG_TEST','HINT_TEST')
WHERE OL_NAME IN ('ORG_TEST','HINT_TEST');
COMMIT;

8) Flush Shared pool to enable Hard Parse-

ALTER SYSTEM FLUSH SHARED_POOL;


9) Drop the temporary outline HINT_TEST-

DROP OUTLINE HINT_TEST;

10) Enble usage of stored outline at system level-

ALTER SYSTEM SET USE_STORED_OUTLINE=TRUE;

11) Create trigger on database to enable the use of stored outline whenever database start event it triggered.

create or replace trigger use_outlines_trig
after startup on database
begin
execute immediate('alter system set use_stored_outlines=true');
end;

Hope this will help you to tune your sql..