Q on SQL Performance tuning

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Q on SQL Performance tuning

Bhupathi, Kaushik (CORP)

Hi Team, I’ve few Questions on SQL perf tuning.

 

1)      Is there any SQL monitoring report that’s available in Oracle. Highlight of the report is it tells the  % of time spent on CPU & IO. And which step took how much % in overall execution.

2)      Is there anyway to know the historical execution plan details of a particular SQL ? Per my understanding so far since there is no concept of shared pool unlike Oracle every execution demands a new hard parse. However wanted to check with experts to know if any extension available on this?

 

 

Thanks!
-Kaushik


This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, notify the sender immediately by return email and delete the message and any attachments from your system.
Reply | Threaded
Open this post in threaded view
|

Re: Q on SQL Performance tuning

legrand legrand
Hi,

There are many tools:
- (core) extension pg_stat_statements will give you informations of SQL
executions,
- extension pgsentinel https://github.com/pgsentinel/pgsentinel
  gives the same results as Oracle ASH view
- java front end PASH viewer https://github.com/dbacvetkov/PASH-Viewer 
  gives a nice view of CPU IO per query
- extension pg_stat_sql_plans (alpha) gives all of pg_stat_statements and
much more
  (parsing time, planid, plan text, ...)

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Reply | Threaded
Open this post in threaded view
|

Re: Q on SQL Performance tuning

Justin Pryzby
In reply to this post by Bhupathi, Kaushik (CORP)
On Sun, Jan 27, 2019 at 08:43:15AM +0000, Bhupathi, Kaushik (CORP) wrote:
> 2)      Is there anyway to know the historical execution plan details of a particular SQL ? Per my understanding so far since there is no concept of shared pool unlike Oracle every execution demands a new hard parse. However wanted to check with experts to know if any extension available on this?

There's also autoexplain, althought I think that's typically configured to only
output plans for queries which longer than a minimum duration.

Justin

Reply | Threaded
Open this post in threaded view
|

Re: Q on SQL Performance tuning

Greg Stark
In reply to this post by legrand legrand
On Sun, 27 Jan 2019 at 06:29, legrand legrand
<[hidden email]> wrote:
>
> Hi,
>
> There are many tools:
> - (core) extension pg_stat_statements will give you informations of SQL
> executions,

I've had enormous success using pg_stat_statements and gathering the
data over time in Prometheus. That let me build a dashboard in Grafana
that can dive into specific queries and see when their executions rate
suddenly spiked or the resource usage for the query suddenly changed.

> - extension pg_stat_sql_plans (alpha) gives all of pg_stat_statements and
much more

Extending pg_stat_statements to track statistics per-plan would be a
huge advance. And being able to link the metrics with data dumped in
the log from things like log_min_duration and pg_auto_explain would
make them both more useful.

--
greg