Tuning Oracle for Siebel – SQL template

The time has come to write down some of the most relevant discoveries I've made so far while being part of a team that is tuning a huge Siebel installation for a leading Italian company ("huge" especially because of the user base dimension and secondarily because of the hardware deployed, a three-node RAC on pretty powerful SMP machines).

This blog entry is about the structure of the Siebel queries and the effect of the settings of some CBO-related parameters - settings made by the Siebel clients by altering the session at connect time, or required as mandatory in the Siebel installation notes. Other postings may follow in case I discover something worth writing about.

But first of all, let me thank for their support all the fellow members of the OakTable network (especially Tim Gorman that has exchanged long emails with me) and Andy Cowling (introduced to me by Doug Burns) that kindly provided me with a lot of detailed information coming from their vast hands-on experience with Siebel on Oracle.

For the record, the environment is Siebel 8.0, using a three-node 10.2.0.4 RAC cluster on identical SMP machines with 16 CPUs each.

Most of the Siebel queries follow this template:

select ...
  from base, inner1, ..., innerN
 where base.j1 = inner1.j1(+)
   and base.j2 = inner2.j2(+)
   ...
   and base.jN = innerN.jN(+)
   and "filter conditions on base"
  order by base.order1 [asc|desc], base.order2 [asc|desc], ..., base.orderK [asc|desc];

which must be discussed keeping in mind another critical and subtle information: the Siebel clients read only a subset of the returning rows (probably the ones that fit on the screen, sometimes only 2 or 3 on average) - an intention that the Siebel client wisely communicates to the CBO by altering the session and setting optimizer_mode = first_rows_10.

Side note: there are variants to this template; sometimes there are two or three base tables that are joined and filtered together, and more frequently, some of the outer join relations are based on one of the innerN tables, not on the base tables (eg. innerM.jN = innerN.jN), but that is inessential for what we are going to discuss here.

The Siebel client's intention is clear: get some rows from the base table, order them by the columns order1, order2, ..., orderK, get only the first rows, and then get additional information (columns) by following the outer join relations. Note the order of the operations.

Visiting the outer-joined tables almost always comes out as the most expensive part. The reason is two-fold; first, the sheer number of outer-joined tables (ten tables is the norm, up to about 50, since the Siebel relational model is highly normalized and hence the information is highly dispersed), and second and most importantly, because of the join method the CBO is forced to follow.

In fact, Siebel blocks HASH JOINS (_hash_join_enabled=false) and SORT MERGE JOINS (_optimizer_sortmerge_join_enabled=false), which leaves NESTED LOOPS as the only surviving join method. When nested looping, Oracle must obviously start from the outer table listed in the SQL statement (base) and then visit the SQL inner tables (inner1 ... innerN) using the join conditions, something that can be efficiently done only by visiting an index that has innerN.jN as the leading column of its definition. Each of these visits requires blevel+1 consistent gets for the index and some additional consistent gets for every row that is found - usually one or two, sometimes zero, at least in the scenarios I've seen.

So it is clear that every row that is fetched from the base table may easily produce tens and tens of consistent gets coming from the outer joins - and that can easily lead to a disaster if the number of rows from the base table is not very very small. In one of our queries, for example, about one million rows came out from the base table, and since the CBO chose to outer join first and then order, each execution caused a whopping 15,000,000 consistent gets - only to have the Siebel client select the first 3 rows or so. Needless to say, such a huge number of gets is completely unacceptable; the impact on the response time and CPU consumption is obvious, but scalability suffers as well (gets cause latches or mutexes acquisitions that are the most frequent cause of contention) - that means that even a slight increase on the workload may cause severe degradation of response time (especially on RAC).

The solution is to have Oracle order first, and then join - to very quickly feed the first 3 or 4 rows to the client thus sparing the effort to outer join the remaing ones, that will never (or rarely) be fetched. That usually means that you must prepare an ad-hoc index for the query that has the order1,order2, orderK column as the last columns; the first_rows_10 setting will strongly encourage the CBO into choosing the index. For example, say that the last "filter conditions on base" and the order clause are

...
and base.col1 = :1 and upper(base.col2) = :2
order by base.order1 desc, base.order2 asc;

a suitable index might be (col1, upper(col2), order1 desc, order2 asc).
Oracle will hopefully reach the start of the index range (or "slice") corresponding to the filter condition, a range that has the rows sorted exactly as Siebel wants them, and then scan the index range rows in succession; for each of them, it will outer join and then return the result to Siebel, and hence stop after a few rows read from the index and a few outer joins performed.

This is the main "tuning" technique in Siebel; of course there are others (using covering indexes for the outer join tables for example) that can provide some additional advantage to our beloved Oracle instances, that we are going to investigate and that I will blog about if they turn out as being useful. But I doubt that they can be as effective as this one.


  1. James

    Wednesday, July 1, 2009

    Very illuminating, thank you. A question on the solution:

    If the indexes are ad hoc, will they be created (and afterwards dropped) for each query? Is this manual or automated? If automated, how?

    Or will you create a standard set for your most common queries?

    Thanks

  2. Alberto Dell'Era

    Wednesday, July 1, 2009

    @James

    The indexes are created manually, following the usual procedures – they are “ad hoc” because they are usually useful for one query only, or a bunch of strongly related queries. But they are just regular indexes, and you must make good use of your indexing skills to define them – this post is just an head start, as it summarizes the main information about the peculiarities of the Siebel environment.

  3. Blogroll Report 26/06/2009 – 03/07/2006 « Coskan’s Approach to Oracle

    Friday, July 3, 2009

    [...] Alberto Dell’Era -Tuning Oracle for Siebel – SQL template [...]

  4. @lex

    Thursday, July 23, 2009

    Hi Alberto,

    great post and very concise!

    However, I just want to throw in that it is a very common practice of the DBA to create “ad hoc” indexes in the Siebel database.

    When the Siebel schema is upgraded (say to version 8.1), the upgrade process drops all indexes and recreates only those which are registered in the Siebel Repository (using Siebel Tools).

    I strongly encourage that the people creating ad-hoc indexes talk to the Siebel developers so that they can add the index definition to the Siebel Repository. Or even create the definition first and then use the Siebel ddlsync utility to “apply” the metadata-resident definitions to the physical schema.

    For indexes where this is not possible, you should at least document the index definition (create statement) in a way that it is present during the upgrade process.

    have a nice day

    @lex

  5. Alberto Dell'Era

    Thursday, July 23, 2009

    @Alex

    Thank you for your contribution – that is definitely something that must be always remembered when “tuning” Siebel. Actually in our case, the database schema is so heavily customized (manually partitioned tables, a lot of indexes using options such as “compress” etc) that everything is pretty much done by hand … Siebel is synchronized somehow, but I do not know exactly how since I’m not the one in charge of this :)

  6. Samir

    Thursday, February 4, 2010

    Hello,

    Nice post – i just saw this the first time. However i think there are issues in my environment which are related to your post somewhat, but different. The vanilla queries coming out of siebel are as you mentioned, from what i can see. However the customizations that are often done to them are causing issues, I believe. Many screens in our environment seem to have multiple base tables, not just one. For example, a query on accounts may select for the business unit from S_ORG_BU (of which there are only 1 or 2, so this will match alot of records), but then the developers cater this query for a team, to search by a national account number, using a custom column from the accounts table. But the ORDER BY stayed on the business unit, so oracle really loves that index on the S_ORG_BU table, so it can fetch the first 10 rows quickly as you said. But the problem is, as it is fetching the 10 rows they then are matched against the other predicate on the other ACCOUNTS table S_ORG_EXT. If the row doesn’t satisfy that 2nd predicate, it must scan the next row on S_ORG_BU and try again. This can keep happening until it may end up scanning hundreds of thousands of rows until it finds 10 which match the 2nd predicate, and return them to the user. Response time can be horrible because of this. See below for an example. Even though an index exists on the X_NATIONAL_ACCOUNT_NUMBER table, the CBO doesn’t pick it up as the column is not very selective. Each national account matches a few hundred rows in that table, but this is FAR better than using the S_ORG_BU index, which matches hundreds of thousands of rows. But the CBO likes the latter, because it is thinking FIRST_ROWS_10, that it only will fetch 10 rows from each step. Seems the CBO doesn’t realize that it will scan many more rows than 10, to satisfy the 2nd predicate on S_ORG_EXT table…

    SELECT …
    FROM SIEBEL.S_ORG_BU T1,

    SIEBEL.S_ORG_EXT T27
    WHERE ….
    AND T1.BU_ID = :1
    AND T27.ROW_ID = T1.ORG_ID
    AND T1.BU_ID = T8.ROW_ID
    AND T1.BU_ID = T6.PAR_ROW_ID (+)
    AND (T1.ORG_NAME >= :2)
    AND (T27.X_NATIONAL_ACCOUNT_NUMBER = :3)
    ORDER BY T1.BU_ID,
    T1.ORG_NAME,
    T1.ORG_LOC

  7. Alberto Dell'Era

    Wednesday, February 10, 2010

    @Samir

    sorry, I have seen your comment only today, probably I have an issue with email notifications …

    The main issue with Siebel is that some “supported settings” are just there to kill the CBO. For example, setting OPTIMIZER_INDEX_COST_ADJ to 1 makes the cost of index access so tiny, that it really makes them so unrealistically cheap that the CBO basically miscalculates the cost completely … if memory serves right you have, in recent version, the opportunity to set it to the default (100) hence allowing the CBO to make its work much better. You could try it and see whether that makes any difference.

    The second is that very often, if the predicates on the N tables are not selective enough, there’s no way that a nested loop could get them back efficiently – whatever table you choose as the driving table(s), it makes for a lot of index accesses on the inner tables(s). An hash join (or sort merge) would be optimal, but Siebel blocks them …

    Of course to remedy the situation, you might be forced to drop the index on the ordered-by columns … possibly killing other queries. At the end, in Siebel, poor database performance has to be accepted as a fact of life almost always :(

    PS: FIRST_ROWS_10 means asking the CBO to get back the first 10 rows of the statement as fast as it can, not the first 10 rows of each step.

  8. Samir

    Wednesday, February 10, 2010

    Alberto,

    Thanks – yes i agree with you about FIRST_ROWS_10, however, when the CBO calculates cost to get back the first 10 rows of the statement as fast as it can, it can grossly underestimate the # of rows for some key steps. Example, for the 1st predicate it uses an index range scan for that step, the CBO estimates only 10 records need to be scanned. But when running the query it goes through thousands of records as that is what it ends up taking get 10 rows which satisfy the 2nd predicate as well. I did find a potential solution for this though – even though i am mostly working at the query and database level, i discovered that in the siebel application, you can set something at the business-component level which will hint queries in that BC with an ALL_ROWS hint. That can fix some queries with this sort issue. I’m trying to get my siebel developers to look into this, as we are experiencing this issue all over the place.

Leave a Comment

Please enclose SQL code inside the tag pair [sql] ... [/sql]

Subscribe without commenting

Links (alphabetical order)

Blogroll (alphabetical order)

Blog Aggregators (alphabetical order)


Switch to our mobile site