ASH

Franck Pachot's picture

Drilling down the pgSentinel Active Session History

By Franck Pachot

.
In pgSentinel: the sampling approach for PostgreSQL I mentioned that one of the advantages of the ASH approach is the ability to drill down from an overview of the database activity, down to the details where we can do some tuning. The idea is to always focus on the components which are relevant to our tuning goal:

Franck Pachot's picture

pgSentinel: the sampling approach for PostgreSQL

By Franck Pachot

.
Here is the first test I did with the beta of pgSentinel. This Active Session History sampling is a new approach to Postgres tuning. For people coming from Oracle, this is something that has made our life a lot easier to optimize database applications. Here is a quick example showing how it links together some information that are missing without this extension.

The installation of the extension is really easy (nore details on Daniel’s post):

cp pgsentinel.control /usr/pgsql-10/share/extension
cp pgsentinel--1.0.sql /usr/pgsql-10/share/extension
cp pgsentinel.so /usr/pgsql-10/lib

Franck Pachot's picture

PGIO, PG_STAT_ACTIVITY and PG_STAT_STATEMENTS

For an Oracle DBA, we are used to join active sessions (from V$SESSION where status=’ACTIVE) with active statements (from V$SQL where users_executing>0) on the SQL_ID. V$ACTIVE_SESSION_HISTORY also displays the TOP_LEVEL_SQL_ID to get the entrypoint of the usercall if we need it. With Postgres it is a bit more difficult because it seems that PG_STAT_ACTIVITY do not show the active statement but only the top-level one. But pg_stat_statement collects statistics for the final statements.

randolf.geist's picture

Posts by Alberto Dell'Era On ASH Wait Times And Materialized Views

This is just a short note that links to different articles on Alberto Dell'Era's blog. Alberto is a long time member of the OakTable and has published a number of posts I find very useful and therefore are linked here:

ASH wait times (including an ASH simulator!)

Posts on Materialized Views

randolf.geist's picture

New Version Of XPLAN_ASH Utility

A new version 4.23 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version comes only with minor changes, see the change log below.

Here are the notes from the change log:

- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" of the last 255 samples or so and updates them with the time waited, so these wait events are not "in-flight"

- Removed some of the clean up code added in 4.22 to the beginning of the script, because it doesn't really help much but spooled script output always contained these error messages about non-existent column definitions being cleared

alberto.dellera's picture

“ASH math” of time_waited explained with pictures and simulation

As explained by John Beresniewicz, Graham Wood and Uri Shaft in their excellent overview ASH architecture and advanced usage, avg( v$active_session_history.time_waited ) is not a correct estimate of the average latency (the "true average") esperienced by a wait event, the reason being that short events are less likely to be sampled. In order to correct this, the authors propose a formula that gives an unbiased estimate of the "true average".

tim.evdbt@gmail.com's picture

Presentation “Real-Time SQL Tuning” at Hotsos Sym2014 and UTOUG TD2014

This presentation was delivered at the Hotsos Symposium 2014 and at the Utah Oracle Users Group (UTOUG) Training Days 2014.

It describes a PL/SQL package that I put together called “ash_xplan.sql” which can be used to display real-time information about a SQL statement currently executing.  This package combines information from the Active Session History (V$ACTIVE_SESSION_HISTORY) or “ASH” view with information from the DBMS_XPLAN package.

As of Oracle12c v12.1, nothing in Oracle displays elapsed time while the SQL statement is still executing, not even SQL Monitor.

The source code for the “ash_xplan.sql” script is on the Scripts page of this website, along with sample spooled output.

tim.evdbt@gmail.com's picture

ash_xplan_output.txt

tim.evdbt@gmail.com's picture

ash_xplan.sql

dbakevlar's picture

Displaying CPU Graphs For Different Time Ranges in Enterprise Manager

This question was posted in Twitter from @matvarsh30, who asked, “How can I display CPU usage over different periods of time for databases in Enterprise Manager?”

To prevent automated spam submissions leave this field empty.
Syndicate content