Re: Hard parse timing
Date: Wed, 29 Apr 2015 12:05:21 +0100
Message-ID: <4E3D3DF9F2C540CFAB3E48DA2157A2E7_at_Primary>
(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_at_xerox.com> To: "'Oracle L'" <oracle-l_at_freelists.org> 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:
- Is there a direct correlation between the number of joins in a statement and the time spent on hard parsing?
- 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
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 29 2015 - 13:05:21 CEST