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
Independent Oracle performance consultant and researcher
Ravi Teja Bellamkonda <raviteja.bellamkonda7@xxxxxxxxx> hat am 18. Juni 2017--
um 18:57 geschrieben:
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.
We have a 22.214.171.124 database and recently we are facing a performance issue.
Wait event is cursor pin:s. A particular sql query is executed
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