Home -> Mailing Lists -> Oracle-L -> RE: Hard parse timing

RE: Hard parse timing

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 29 Apr 2015 19:34:23 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D92828ABFE_at_EXMBX01.thus.corp> 







There are some anomalies with counting misses. I've never followed them up too closely, but I have seen cases where a parse call on the execute increments the misses in library cache during parse as well as the misses in library cache during execute.  It may depend on what program supplied the SQL originally, whether it was holding cursor etc.



If I had a reason for worrying about it I'd do a detailed examination of the trace file to see if it gave me any clues.





Regards


Jonathan Lewis


http://jonathanlewis.wordpress.com


_at_jloracle




From: Hameed, Amir [Amir.Hameed_at_xerox.com]
Sent: 29 April 2015 20:26


To: Jonathan Lewis; 'Oracle L'


Subject: RE: Hard parse timing



The following appeared below the tabular output:



Misses in library cache during parse: 1


Misses in library cache during execute: 1



After the first execution, the subsequent executions of the statement are fast. I don’t know of hand for how long does the statement last in the shared pool before it is aged out and hard parsed again. So, based on your explanation, it seems that after the hard parse (Misses in library cache during parse: 1) but before the execution phase, the cursor had to be reparsed (Misses in library cache during execute: 1). If my understanding is correct then shouldn’t the first hard parse have taken the same amount of time as the parse at the time of execution?



Thanks


From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Wednesday, April 29, 2015 8:35 AM


To: 'Oracle L'


Subject: RE: Hard parse timing




What do the lines below the numbers show for "misses in library cache" ?




Since the numbers of query, current and disk blocks on that line are zero then I think we can discount any time spent actually executing (as might happen for queries involving aggregation and ordering, say). If the time disappears when you re-execute the query, then you're probably optimising on the execute call - which you can check in the "Misses in library cache during XXX:" lines just below the tabular output.  If you've run a query, and wait some time before running it again, it's possible that the child cursor plan will have been aged out of the library cache without the client layers of the code being aware of this - so a second call to the statement may result in Oracle going to the correct child cursor, then finding it has to "reload" the execution plan. See also this elderly post on the topic: https://jonathanlewis.wordpress.com/2007/07/19/library-cache-miss



Regards


Jonathan Lewis


http://jonathanlewis.wordpress.com


_at_jloracle




From: Hameed, Amir [Amir.Hameed_at_xerox.com]
Sent: 29 April 2015 13:26


To: Jonathan Lewis; 'Oracle L'


Subject: RE: Hard parse timing


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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] 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_at_xerox.com<mailto:Amir.Hameed_at_xerox.com>>
To: "'Oracle L'" <oracle-l_at_freelists.org<mailto: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:



    
  
  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


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




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


Received on Wed Apr 29 2015 - 21:34:23 CEST












Original text of this message