Re: Concurrency - Cursor Pin:S

Hi Stefan and Lewis,

Thank you for responding. You were right about the cloud :P and I heard cloud makes administration easy-lol.

Back to the question

The elapsed time is for the particular SQL Query. It took 2033.64 s for 31,173,028
executions. These executions are captured for a duration of around 30 - 35 mins.

I will look at the other possibilities involving dbms_shared_pool.markhot.

On Sun, Jun 18, 2017 at 10:24 AM, Stefan Koehler <contact_at_soocs.de> wrote:

> Hello Ravi,
> welcome to the new world - the cloud - where everything is easy and shiny
> ;-P
>
> OK enough sarcasm!
>
> 1) Unfortunately your provided data is not enough - is the mentioned
> "Total Elapsed Time" related to the SQL or to the time between two AWR or
> STATSPACK snapshots? If it is the first - we don't know the frequency. If
> the latter - then yes 15.333 executions per second are high.
>
> 2) How should we know the impact if we don't know how much time the whole
> SQL or business process takes? 14 ms for 20 ms in sum (in avg) is much but
> 14 ms for 10 seconds in sum (in avg) is nothing.
>
>
> However you can also clone the hot library cache object with "_kgl_debug"
> & "_kgl_hot_object_copies" or dbms_shared_pool.markhot but i don't know if
> this is possible with the cloud (AWS RDS) - dbms_shared_pool should be
> possible according to the AWS forum
> (https://forums.aws.amazon.com/thread.jspa?threadID=155426).
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Website: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > Ravi Teja Bellamkonda <raviteja.bellamkonda7_at_gmail.com> hat am 18. Juni
> 2017 um 18:57 geschrieben:
> >
> > Hi All,
> >
> > Thank you for inputs. Unfortunately I cannot go ahead with the test
> scenario in the page as this database is an AWS RDS (which I already hate
> for
> > obvious reasons) and I cannot access the x$ views.
> >
> > Can someone help me provide an insight on how much the performance is
> being impacted as the average wait caused by cursor pin : S is 14 ms while
> we
> > are in parallel trying to reduce the number of calls (App side).
> >
> > Any help is very appreciated.
> >
> > > Hi All,
> >
> > > We have a 11.2.0.4 database and recently we are facing a performance
> issue. Wait event is cursor pin:s. A particular sql query is executed
> > > multiple times
> > >
> > > Executions - 31,173,028
> > > Total Elapsed Time - 2033.64 s
> > >
> > > Can some provide some input a way to diagnose the root issue. I see
> that the no of executions are very high.
> > > --
> > > Thanks & Regards,
> > > Ravi Teja Bellamkonda
>

-- 
Thanks & Regards,
Ravi Teja Bellamkonda
Ph: (816)-905-7577.

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