Re: Concurrency - Cursor Pin:S

From: Mauro Pagano <mauro.pagano_at_gmail.com>
Date: Sun, 18 Jun 2017 13:56:20 -0400
Message-ID: <CAAnDMS2iu=tFK3PoA0cqUqXZP7Cev6MGo6LdhBf-oFo8UmPMLA_at_mail.gmail.com>



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.
> 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 <(816)%20905-7577>.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jun 18 2017 - 19:56:20 CEST

Original text of this message