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@xxxxxxxxx> 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@xxxxxxxx> 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: @OracleSK
Ravi Teja Bellamkonda <raviteja.bellamkonda7@xxxxxxxxx> hat am 18.Juni 2017 um 18:57 geschrieben:
scenario in the page as this database is an AWS RDS (which I already hate
Hi All,
Thank you for inputs. Unfortunately I cannot go ahead with the test
for
obvious reasons) and I cannot access the x$ views.being impacted as the average wait caused by cursor pin : S is 14 ms while
Can someone help me provide an insight on how much the performance is
we
are in parallel trying to reduce the number of calls (App side).issue. Wait event is cursor pin:s. A particular sql query is executed
Any help is very appreciated.
Hi All,
We have a 11.2.0.4 database and recently we are facing a performance
that the no of executions are very high.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
--
Thanks & Regards,
Ravi Teja Bellamkonda
--
Thanks & Regards,
Ravi Teja Bellamkonda
Ph: (816)-905-7577 <(816)%20905-7577>.
