RE: Hard parse timing

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 24 Apr 2015 19:06:00 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0E1B3BDB_at_USA7109MB012.na.xerox.net>

I learned during the investigation that Dynamic Sampling was turned off by the Siebel DBAs for unknown reasons.

From: Iotzov, Iordan [mailto:IIotzov_at_newsamerica.com] Sent: Friday, April 24, 2015 3:02 PM
To: Hameed, Amir; 'Oracle L'
Subject: RE: Hard parse timing

Was dynamic sampling (DS) invoked for this query?

Iordan Iotzov | Lead Database Administrator, Information Services | News America Marketing

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir Sent: Friday, April 24, 2015 2:16 PM
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 message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.

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

Received on Fri Apr 24 2015 - 21:06:00 CEST