Re: Hard parse timing

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Fri, 24 Apr 2015 23:08:51 +0300
Message-ID: <CAOVevU4mgy4egaGr-buZHcCZB+R8iO2hwOxq1RgPrcAAkeUCSA_at_mail.gmail.com>



Hi Amir,

Could you show:
1. params from v$sql_optimizer_env/v$ses_optimizer_env where isdefault='NO'? 2. select * from v$system_fix_control c where is_default!=1

and it would be even better if you can show full 10053 trace.

On Fri, Apr 24, 2015 at 10:55 PM, Stefan Koehler <contact_at_soocs.de> wrote:

> Hi Amir,
>
> 1) "Parsing cpu" is nearly "Parsing elapsed" - so nearly no waiting states
> (by parsing)
> 2) I hope the parameter "optimizer_max_permutations" was not set as it is
> deprecated. The (new hidden) parameter itself is called
> "_optimizer_max_permutations". Please check the PDF from MOS ID #781927.1
> for more details.
> 3) The amount of child cursors should be no issue in your scenario as you
> flushed the shared pool before (e.g.
>
> http://antognini.ch/2012/10/does-the-parse-time-increase-linearly-with-the-number-of-child-cursors/
> ).
> 4) Are there only joins or also sub-queries and inline views? The
> permutation limit is per query block (afaik) and you can have a lot of
> (different)
> query blocks with sub-queries or inline views for example.
> 5) Do you have the CBO trace? If yes how many different join orders do you
> have? How many indexes do you have per table? This can also scale up badly.
>
> Maybe this is a good starting point for you, but if only the parameter
> "optimizer_max_permutations" is set, you may already got the root cause (in
> case of Siebel).
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > "Hameed, Amir" <Amir.Hameed_at_xerox.com> hat am 24. April 2015 um 20:15
> geschrieben:
> >
> >
> > 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
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle ACE Associate
Lead performance tuning engineer
PSBank
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 24 2015 - 22:08:51 CEST

Original text of this message