Re: High Parse Call

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Fri, 28 Mar 2014 09:09:59 -0700
Message-ID: <CAA2DszwL0boKHWu5HOqXx8+QyN=23YpbqMjVBw6mUskpuokrYw_at_mail.gmail.com>

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_at_gmail.com> 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
>

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

Received on Fri Mar 28 2014 - 17:09:59 CET