Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 30, 2020 - 7:13 pm UTC

Last updated: October 06, 2020 - 8:20 am UTC

Version: 11.2.0.2.0

Viewed 1000+ times

You Asked

Hi,
Please help me to clear couple of doubts.
1) If a procedure takes 0.76 seconds elapsed time per execution, is that bad?
2) we have around 15 waits for row lock contention. each taking more than 2 seconds. Is there any way to find out which table is being locked?
3) Is there any article available which gives an idea on how to use awr report properly?

and Connor said...

1) Maybe

Elapsed time isn't a factor you care about. What you really care about is *Required* Elapsed time. For example, If that procedure is "Customer Transaction", then 0.7 seconds might be slow if thats being done from a website . But if that procedure is "Monthly Report" then 0.7 seconds once per month is fantastically fast. It totally depends on the context and what the performance *requirements* of the procedure are.

2) Look at your ASH data both v$active_session_history and dba_hist_active_sess_history. The columns

CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
CURRENT_ROW#

show you what you were waiting for, and the BLOCKING prefixed columns show you who was blocking you.


3) Some great content here by the inventor of AWR and others

https://coug.us/wp/wp-content/uploads/2018/08/AWR_Analysis_coug.pdf
https://www.oracle.com/technetwork/database/manageability/awr-oow2013-2031463.pdf
https://www.oracle.com/technetwork/database/manageability/diag-techniques-presentation-ow07-128491.pdf

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.