I think it's probably usual in Siebel. In Siebel the basic queries joins
around 16 to 20 tables, I have seen join of over 60 tables.
Check Siebel version and check MOS note, there is a note which tells what
optimizer parameter you should be using depending on Siebel version. Last
time I checked these were the recommneded changes:
_always_semi_join OFF
_b_tree_bitmap_plans FALSE
_gc_defer_time 0
_no_or_expansion FALSE
_optimizer_max_permutations 100
_partition_view_enabled FALSE
_like_with_bind_as_equality * TRUE -> this is not always recommeneded
but for some siebel users it's was a relieve
optimizer_dynamic_sampling 1
optimizer_index_cost_adj 1
star_transformation_enabled FALSE
query_rewrite_enabled FALSE
optimizer_dynamic_sampling should be set to 1 in Siebel
Regards
On Fri, Apr 24, 2015 at 8:15 PM, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote:
During UAT testing of new deployments in an existing Siebel application,
end users of the Siebel applications were complaining of slow response time
upon first execution of their activity from the application. I do not have
much knowledge of the Siebel application, but the way it was explained to
me was that from within the Siebel application, users can select options
and based on those options Siebel generates statements on-the-fly. I asked
the support DBAs to capture one of the SQL and run a 10046 trace on it. The
shared pool was also flushed prior to running the statement. The output of
the trace file is shown below:
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 13.28 13.30 0 247
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 1 0.00 0.00 0 2
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 13.28 13.30 0 249
0 0
Misses in library cache during parse: 1
The above output shows that all time was spent during the parsing (hard)
period. This behavior seems to be common to all those statement that have a
lot of table joins in the WHERE clause. This particular statement has 40+
joins. The database version was 11.2.0.4. The OPTIMIZER_MAX_PERMUTATIONS
was set to 100 per Siebel’s recommendation. I have the following questions:
1. Is there a direct correlation between the number of joins in a
statement and the time spent on hard parsing?
2. If the answer to the above question is yes then is there a way
to optimize parsing time? There are a lot of Seibel statements, I have been
told, that have 50+ joins in the WHERE clause. Since these statements are
generated on-the-fly by the application based on a user’s selection, it is
not possible to pre-hint these statements.
Thanks,
Amir
