Thread: Q on SQL Performance tuning
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.
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
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 farsince there is no concept of shared pool unlike Oracle every execution demands a new hard parse. However wanted to checkwith 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
On Sun, 27 Jan 2019 at 06:29, legrand legrand <legrand_legrand@hotmail.com> 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