Re: Concurrency - Cursor Pin:S

Hi,

Thank you for response. Application Team is trying to reduce the calls itself and after the fix we are still observing similar no of calls. Will update the rest

On Sun, Jun 18, 2017 at 10:56 AM, Mauro Pagano <mauro.pagano_at_gmail.com> wrote:

> Ravi,
>
> Any chance you can take a step back and understand WHY your application
> needs to execute a SQL 31M times over 30 mins?
> It's very unlikely this is a (directly) user generated load and smells
> more application-multithreaded row-by-row.
>
> Also you mentioned 2033.64 secs of total elapsed time, what's the amount
> of time spent on cursor pin S out of those 2k secs? I'm asking because 2k
> secs for 31M execs is 0.06ms / exec so even without waits not sure how much
> you can trim from that.
>
> Bottom line (just my 2cents), you might be looking too close at something
> for which a better solution can be implemented looking at the big picture
>
> Cheers,
> Mauro
>
> On Sun, Jun 18, 2017 at 1:35 PM, Ravi Teja Bellamkonda <
> raviteja.bellamkonda7_at_gmail.com> wrote:
>
>> 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.mar
>> khot.
>>
>>
>> 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 <(816)%20905-7577>.
>>
>
>

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

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