Amir,
You need to use ASH as Stefan suggests because something very odd is
happening.
You might also like to the following note:
http://jonathanlewis.wordpress.com/2014/12/09/parse-time-2/
It's not directly related to addressing your problem but shows a similar
effect - particularly a possible escalation due to concurrency.
The query at the start of the article is not appropriate for you because it
uses a known SQL_ID in the WHERE clause, you need to add sql_id to the
select, group by, and order by.
One oddity of your stats is that you must have at least 4(.4) sessions
concurrently parsing on average to get that much CPU used, but the "hard
parse" statistic is only 1, which suggests that whatever starts parsing
never finishes parsing (in that interval).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
To: "Stefan Koehler" <contact@xxxxxxxx>; "Ahmed Aangour"
<ahmed.aangour@xxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, August 18, 2016 6:48 PM
Subject: RE: Hard parse elapsed time in AWR
| Hi Stefan,
| Thank you. It is my understanding that the payload is large but I do not
know exactly how it is processed. Here is the processing flow:
|
| A large XML file is generated from the Oracle E-Business Suite
application through a concurrent job and is sent to a system for tax
calculations. That system is based on VERTEX software which uses Tomcat
application server. The VERTEX database is Oracle (11.2.0.3). When the call
is made from the Oracle E-Business Suite, the calling session calls a
VERTEX web service for tax calculations using the UTL_HTTP call and then
waits on the "TCP Socket (KGAS)" event. This call takes a long time to
return and we are trying to troubleshoot where the time is being spent. On
the VERTEX side, there are most likely only a very few sessions involved
with the tax calculation activity and I believe your observation is
correct.
| The VERTEX system is managed by a different group and on my ask, they
provided AWR from both RAC instances generated at 15-minute interval.
|
| I have discovered this statistics at two different intervals:
| 13:30 - 13:45
| 14:30 - 14:45
|
| -----Original Message-----
| From: Stefan Koehler [mailto:contact@xxxxxxxx]
| Sent: Thursday, August 18, 2016 12:14 PM
| To: Ahmed Aangour <ahmed.aangour@xxxxxxxxx>; Hameed, Amir
<Amir.Hameed@xxxxxxxxx>
| Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
| Subject: RE: Hard parse elapsed time in AWR
|
| Hi Amir,
| the metrics "parse time elapsed" and "parse time cpu" in "Instance
Activity Stats" really look strange (but it can be an accounting behavior
or bug in 11.2.0.3). However is it possible that only a few sessions (in
your case round about 4) are optimizing very long (or endlessly) and have
not finished at snap end time? You can cross-check this in ASH (column
IN_HARD_PARSE).
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7752 / Virus Database: 4647/12827 - Release Date: 08/17/16
--
//www.freelists.org/webpage/oracle-l
