Hard parse timing
From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 24 Apr 2015 18:15:39 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0E1B3B5D_at_USA7109MB012.na.xerox.net>
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:
Date: Fri, 24 Apr 2015 18:15:39 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0E1B3B5D_at_USA7109MB012.na.xerox.net>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 24 2015 - 20:15:39 CEST