The note says only relevant for queries that .could run parallel.
Given the work done and result set in this case it doesn't look as if that
applies in this case - plus, we don't see any disk I/Os in the parse line).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
To: <exriscer@xxxxxxxxx>; "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
Cc: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, April 24, 2015 10:20 PM
Subject: RE: Hard parse timing
| optimizer_dynamic_sampling = 1 does not guarantee that dynamic sampling
would not fire.
| As per MOS note 1102413.1 , Oracle (version 11.2 and up) can use higher
DS level than specified in OPTIMIZER_DYNAMIC_SAMPLING.
|
|
| Iordan Iotzov | Lead Database Administrator, Information Services | News
America Marketing
| 20 Westport Road, 1st floor, Wilton CT 06897 | P 203.563.6472 | C
203.423.9269
| iiotzov@xxxxxxxxxxxxxxx<mailto:iiotzov@xxxxxxxxxxxxxxx>|
newsamerica.com<http://www.newsamerica.com/> |
smartsource.com<http://www.smartsource.com/>
|
| [cid:image001.gif@01D07EB3.01644A60]
|
| From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ls Cheng
| Sent: Friday, April 24, 2015 4:34 PM
| To: Hameed, Amir
| Cc: Oracle L
| Subject: Re: Hard parse timing
|
| 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<mailto: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
|
|
| ________________________________
| This message and its attachments may contain legally privileged or
confidential information. It is intended solely for the named addressee. If
you are not the addressee indicated in this message (or responsible for
delivery of the message to the addressee), you may not copy or deliver this
message or its attachments to anyone. Rather, you should permanently delete
this message and its attachments and kindly notify the sender by reply
e-mail. Any content of this message and its attachments that does not
relate to the official business of News America Incorporated or its
subsidiaries must be taken not to have been sent or endorsed by any of
them. No warranty is made that the e-mail or attachment(s) are free from
computer virus or other defect.
|
|
| -----
| No virus found in this message.
| Checked by AVG - www.avg.com
| Version: 2015.0.5863 / Virus Database: 4331/9567 - Release Date: 04/18/15
|
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5863 / Virus Database: 4339/9655 - Release Date: 04/29/15
--
//www.freelists.org/webpage/oracle-l
