Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajesh.

Asked: June 02, 2017 - 8:19 am UTC

Last updated: June 06, 2017 - 1:47 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Database has 22 schema which are similar.

Some of the queries in PL.SQL code remains active and in AWR we Observe that there is a very very high Parse to execution ratio for these queries (Example :Parse calls 92,899,371, Executions 3,875 in a period of 8 hours).

Also the PGA, CPU utilization and "enq: TX - row lock contention" for these queries is very high

What is leading to this high parsing ratio.

and Connor said...

Nothing in the database parses just for the sake of parsing...

Things will cause it are:

a) bad application code, or intermediate layer (ORMs etc)
b) invalidations (excessive DDL or gathering of stats)
c) insufficient resources (small SGA so cursors getting pushed out)

row lock contention is unrelated to parsing, but of course, is still an issue - look for poor code, missing FK indexes

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

High Parsing

Rajesh pai, June 05, 2017 - 11:53 am UTC

To track the issue. Restarted the database to track the issue from the start. In one schema 3 sessions remained active. Tracked the session and their PGA and found increasing for the same session over time. In AWR during the same period for these queries, the Parsing to execution ratio was very very high(Parses 14,322,245 execution 121).
Indicates parsing is happening within the session.
Connor McDonald
June 06, 2017 - 1:47 am UTC

Indeed it does :-)

Take a look at V$SQL for high PARSE_CALLS
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.