Thanks Jonathan for the explanation. I have looked at the SPFILE that was sent
to me by the DBA and it did show "_optimizer_max_permutations=100". I will try
to get a hold of the alert log file to validate it as well. The Siebel team has
also set optimizer_dynamic_sampling=0 against the Siebel advise which suggests
to set it to "1".
Can you please also help me understand how to interpret the Execute statistic
under the cpu column. Is this the time spent on the CPU while the optimizer was
evaluating different execution plans and is therefore part of hard-parsing?
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 53.83 53.83 0 0 0 0
Fetch 4 0.19 0.19 0 2833 0 44
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 54.05 54.05 0 2833 0 44
Thank you,
Amir
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Wednesday, April 29, 2015 7:05 AM
To: 'Oracle L'
Subject: Re: Hard parse timing
(1) On top of Mladen's comment about number of tables and examination of 3
joins; there's also an escalating cost relating to the number of indexes on
each table given the options for range scans, full scans, fast full scan, skip
scans and index combines and index joins; often the number of legal indexing
options makes a big difference that is more significant than simply the number
of tables. The problem is exacerbated because Siebel dictates
optimizer_index_cost_adj = 1, so all indexes look very cheap.
(And, yes, Siebel often has lots of indexes per table).
As per Stefan's comment - have you got the right "max permutations"
parameter set. You can always check the alert log to see if the right one is
set and the wrong one has been reported as obsolete. If the right one has NOT
been set then Oracle has a costing cut-off which can keep searching until the
cost falls below a value that is less than a value derived from the number of
tables in the query block.
Also, as per Stefan's comment, the limit is per query block, and if you have
SQL which allows for variations in transformations then the number of times you
work through re-optimizing long join orders without being able to re-use
previously calculated results, e.g. if you have a query with two subqueries you
may find that Oracle examines
Query as is
query with 1st subquery unnested
query with 2nd subquery unnnested
query with both subqueries unnested
(2) You cannot avoid optimization, you can only limit the number of options
that the optimizer tries to use (which is why Siebel dictates the limit on max
permutations). If you have this type of timing problem on a straight N-table
join then your only options are: reduce the number of indexes, or stop the
optimizer from trying subtle uses of indexes (set parameters to
(e.g.) disable skip scans, index joins)
If you have this problem with queries involving aggregation you could check to
see if the optimizer is spending time trying lots of variations of "placing
group bys" and disable those features.
If you have this problem with queries involving subqueries then start disabling
subquery manipulation transformations.
In a real crunch, consider setting (with confirmation from Oracle and
Siebel): "_optimizer_cost_based_transformation" = off
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>>
To: "'Oracle L'" <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Sent: Friday, April 24, 2015 7:15 PM
Subject: Hard parse timing
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
-----
No virus found in this message.
Checked by AVG - www.avg.com<http://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<http://www.avg.com>
Version: 2015.0.5863 / Virus Database: 4339/9655 - Release Date: 04/29/15
--
//www.freelists.org/webpage/oracle-l
