Thank Mladen.
For sharing the cursor, I need find out how often these statements are executed
to get a sense of how long they would stay in the shared pool. If they are
accessed frequently then chances are that with adequately sized shared pool,
they would stay in the cache long enough to not impact the user response time.
But, if they are not executed that frequently then they might age out of the
shared pool frequently and require hard parsing.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mladen Gogala
Sent: Saturday, April 25, 2015 9:42 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Hard parse timing
On 04/24/2015 02:15 PM, Hameed, Amir wrote:
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.
Hi Amir,
The logic tells you that there must be a link between the number of tables
joined and the parse time. For each table optimizer has to estimate the amount
of rows retrieved and to determine the best access path. In addition to that,
it has to evaluate 3 methods for joining the table to the previous row source.
It also has to evaluate the best possible order of joining the tables. The more
tables in the join, the more work there is for optimizer to do. So, the answer
to the first question is definitely a "yes".
The answer to the second question is also yes. The mechanism to speed up
parsing is sharing cursors. You only need to parse the statement once. That is
what the shared pool is for. The first execution will pay the full price, the
others will reuse the plan.
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
