Re: High Parse Call
- From: Hansraj Sao <hansrajsao@xxxxxxxxx>
- To: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- Date: Fri, 28 Mar 2014 12:45:51 -0400
Riyaj,
*Thanks for information, I will do some more debugging. I will try to find
out actual code or enable trace. that may give some more insight. *
*Thanks*
*Hansraj*
On Fri, Mar 28, 2014 at 12:09 PM, Riyaj Shamsudeen <
riyaj.shamsudeen@xxxxxxxxx> wrote:
> I followed up a few SQL statements in EBS many years ago, root cause
> usually boiled down to either programs executing dynamic SQL statements
> (either by dbms_sql or execute immediate) or the statements were not
> sharable due to context function calls in the execution plan.
>
> However, it isn't been a while, so YMMV.
>
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com - Specialists in Performance,
> RAC and EBS
> Blog: http://orainternals.wordpress.com/
> Oracle ACE Director and OakTable member <http://www.oaktable.com/>
>
> Co-author of the books: Expert Oracle
> Practices<http://tinyurl.com/book-expert-oracle-practices/>
> , Pro Oracle SQL,
> <http://tinyurl.com/ahpvms8><http://tinyurl.com/ahpvms8>Expert
> RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL
> practices <http://tinyurl.com/book-expert-plsql-practices>
>
> <http://tinyurl.com/book-expert-plsql-practices>
>
>
>
> On Fri, Mar 28, 2014 at 6:46 AM, hansie <hansrajsao@xxxxxxxxx> wrote:
>
>>
>> Hello Everyone,
>>
>> I came cross this sql in AWR report which parse every time it executed.
>> But when I checked v$sql_shared_cursor view there is no child cursor for
>> this sql_id. All the column in v$sql_shared_cursor table related to child
>> cursor having value 'N'. Version count is also 0 for this sql_id. I am not
>> able to understand the reason do parse in every call when there is no
>> child cursor. This is seeded oracle apps sql so if you happen to have r12
>> instance with 11.2.0.3/4 you can might able to see this sql_id in your
>> instance too.
>>
>>
>> Database: 11.2.0.4
>>
>> Parse section from AWR,
>>
>> Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
>> 7,008,164 7,008,162 20.65 875jz2d26yvd5 OEXAUGEN select count(*) from
>> oe_order_...
>> full sql.
>>
>> select count(*) from oe_order_lines_history hist where hist_type_code in
>> ('UPDATE', 'CANCELLATION', 'SPLIT') and hist_creation_date > :x and
>> header_id = :y and hist.line_id = :a and nvl(audit_flag, 'Y') = 'Y'
>>
>> Any idea?
>>
>> Thanks,
>> Hansraj
>>
>
>
--
Thanks,
Hansraj
Other related posts:
