Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 11, 2016 - 5:11 pm UTC

Last updated: July 20, 2016 - 1:02 pm UTC

Version: 11g2

Viewed 1000+ times

You Asked

How we define which query is run slower?
Means how we can say that query takes more time and require performance tuning?

and Connor said...

If you are licensed for it, an AWR report is an great tool to pick out SQL's that are impacting your system

If not, a good and simple place to start is:

select sql_text, buffer_gets, executions, sql_id, elapsed_time
from v$sqlstats
where buffer_gets > ...
or executions > ...
or elapsed_time > ...
order by 2 desc

with some thresholds suitable to your environment.

Rating

  (4 ratings)

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

Comments

A reader, July 19, 2016 - 11:31 am UTC

Here
buffer_gets and executions and elapsed_time value is byte , byte and micro second respectivily.

Chris Saxon
July 19, 2016 - 2:06 pm UTC

Executions isn't measured in bytes! It's just a count of how many times you executed it!

Chris

Hardik, July 20, 2016 - 6:47 am UTC

In organisation , plsql procedure written by other plsql developer. And DBA does not know what is inside the code and the what is a logic of the procedure.
My question is above case how Dba determine the plsql procedure is running slow?
Connor McDonald
July 20, 2016 - 9:32 am UTC

My question is above case how Dba determine the plsql procedure is running slow?

Slow is a relative term. If users are waiting for the results of the procedure, 1 minute is effectively forever. But if it's part of an overnight batch job, who cares?

It's your users that determine what "slow" is. Once you have this defined they can monitor runtime. PL/SQL calls appear in v$sqlstat too!

Hardik, July 20, 2016 - 12:02 pm UTC

Thank most useful me.

But when I set auto trace on.
1853418 consistent gets ( bytes )
89 recursive calls.
29779 bytes sent via sql net to client
1246 bytes received via sql net from client.
Connor McDonald
July 20, 2016 - 1:02 pm UTC

So... what are you hoping to learn from us?

Hardik, July 20, 2016 - 12:02 pm UTC

1001 rows processed
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 latest video and Chris's latest video 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.