Re: Hard parse timing

From: Ls Cheng <exriscer_at_gmail.com>
Date: Fri, 24 Apr 2015 22:34:29 +0200
Message-ID: <CAJ2-Qb_6jMLWi9KJBxrANdVD4SX2r1OtoEUx7wpQgLAEHw1yMw_at_mail.gmail.com>

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_at_xerox.com> 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
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 24 2015 - 22:34:29 CEST