def get(self, database, query):
bs = block_size.c.block_size
stmt = powa_getstatdata_detailed_db()
stmt = stmt.where(
(column("datname") == bindparam("database")) &
(column("queryid") == bindparam("query")))
stmt = stmt.alias()
from_clause = outerjoin(powa_statements, stmt,
and_(powa_statements.c.queryid == stmt.c.queryid, powa_statements.c.dbid == stmt.c.dbid))
c = stmt.c
rblk = mulblock(sum(c.shared_blks_read).label("shared_blks_read"))
wblk = mulblock(sum(c.shared_blks_hit).label("shared_blks_hit"))
stmt = (select([
column("query"),
sum(c.calls).label("calls"),
sum(c.runtime).label("runtime"),
rblk,
wblk,
(rblk + wblk).label("total_blks")])
.select_from(from_clause)
.where(powa_statements.c.queryid == bindparam("query"))
.group_by(column("query"), bs))
value = self.execute(stmt, params={
"query": query,
"database": database,
"from": self.get_argument("from"),
"to": self.get_argument("to")
})
if value.rowcount < 1:
self.render("xhr.html", content="No data")
return
self.render("database/query/detail.html", stats=value.first())
def query(self):
# Working from the statdata detailed_db base query
inner_query = powa_getstatdata_detailed_db(bindparam("server"))
inner_query = inner_query.alias()
c = inner_query.c
ps = powa_statements
# Multiply each measure by the size of one block.
columns = [
c.srvid, c.queryid, ps.c.query,
sum(c.calls).label("calls"),
sum(c.runtime).label("runtime"),
sum(mulblock(c.shared_blks_read)).label("shared_blks_read"),
sum(mulblock(c.shared_blks_hit)).label("shared_blks_hit"),
sum(mulblock(c.shared_blks_dirtied)).label("shared_blks_dirtied"),
sum(mulblock(c.shared_blks_written)).label("shared_blks_written"),
sum(mulblock(c.temp_blks_read)).label("temp_blks_read"),
sum(mulblock(c.temp_blks_written)).label("temp_blks_written"),
(sum(c.runtime) / greatest(sum(c.calls), 1)).label("avg_runtime"),
sum(c.blk_read_time).label("blks_read_time"),
sum(c.blk_write_time).label("blks_write_time")
]
from_clause = inner_query.join(ps, (ps.c.queryid == c.queryid) &
(ps.c.userid == c.userid) &
(ps.c.dbid == c.dbid))
return (select(columns).select_from(from_clause).where(
c.datname == bindparam("database")).group_by(
c.srvid, c.queryid,
ps.c.query).order_by(sum(c.runtime).desc()))
def query(self):
# Working from the statdata detailed_db base query
inner_query = powa_getstatdata_detailed_db()
inner_query = inner_query.alias()
c = inner_query.c
ps = powa_statements
# Multiply each measure by the size of one block.
columns = [c.queryid,
ps.c.query,
sum(c.calls).label("calls"),
sum(c.runtime).label("runtime"),
sum(mulblock(c.shared_blks_read)).label("shared_blks_read"),
sum(mulblock(c.shared_blks_hit)).label("shared_blks_hit"),
sum(mulblock(c.shared_blks_dirtied)).label("shared_blks_dirtied"),
sum(mulblock(c.shared_blks_written)).label("shared_blks_written"),
sum(mulblock(c.temp_blks_read)).label("temp_blks_read"),
sum(mulblock(c.temp_blks_written)).label("temp_blks_written"),
(sum(c.runtime) / greatest(sum(c.calls), 1)).label("avg_runtime"),
sum(c.blk_read_time).label("blks_read_time"),
sum(c.blk_write_time).label("blks_write_time")]
from_clause = inner_query.join(ps,
(ps.c.queryid == c.queryid) &
(ps.c.userid == c.userid) &
(ps.c.dbid == c.dbid))
return (select(columns)
.select_from(from_clause)
.where(c.datname == bindparam("database"))
.group_by(c.queryid, ps.c.query)
.order_by(sum(c.runtime).desc()))
