RE: Hard parse timing

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Mon, 27 Apr 2015 03:24:03 +0000
Message-ID: <2FE2AA1C5F8DEC478F58DF8DD32BA637033FA4_at_HKWPIPXMB03C.zone1.scb.net>

See Oracle Support Doc 564848.1 and 781927.1 and the PDF file and notes attached to it.

Hemant K Chitale

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir Sent: Saturday, April 25, 2015 2:16 AM
To: 'Oracle L'
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

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html

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

Received on Mon Apr 27 2015 - 05:24:03 CEST