Re: savepoint related performance issues

just to follow up: method-r was extremely responsive to my ticket related to processing this trace file and I now have the complete output. the response time all makes sense now. 3,455,108 savepoints created which consumed 11 hours of CPU and over 4 1/2 hours of network latency. Pretty insane. I never would have been able to zero in on this so quickly w/out 10046 and method-r software. ASH and snapper weren't much help in this case (possibly because I didn't know how to dig deeper in the case of stats output from snapper).

this has motivated me to comb through Master Oracle Trace Data 3rd edition to fill in the many gaps i have in knowledge related to interpreting 10046 database.

chris

On Wed, May 15, 2019 at 11:56 AM Chris Stephens <cstephens16_at_gmail.com> wrote:

> We have a SQLAlchemy/python based application that is experiencing
> performance issues.
>
> The process in question runs for about 18hours and has keeps a single CPU
> very busy for a majority of that time.
>
> I traced the last execution but the resulting trace file is ~9GB and
> methodr's mrprof is having some trouble processing it (which may be due to
> the fact that I've only just begun to use it).
>
> I split the file into 2GB pieces and processed the first piece.
>
> 82% of the response time is due to "CPU:EXEC dbcalls" subroutine
> (2,130,813 calls). Of that 99.3% is due to "SAVEPOINT sa_savepoint_10" w/
> 897,601 EXEC calls and the same number of corresponding PARSE calls.
>
> Active session history isn't much help digging into the cause for the
> excessive CPU consumption. I would think 800,000 exec calls would take
> nowhere near the time it does.
>
> nothing really stuck out to me when i ran snapper (ash,stats) on the
> session in question.
>
> Does anyone have any suggestions on how to troubleshoot further?
>
> Ultimately, i think this will require a change to the application code and
> how it's making use of savepoints.
>
> Thanks for any insight/help!
>
>

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