'포스트그레스 TOP SQL 추출'에 해당되는 글 1건

2019.04.11 PostgreSQL Top SQL 추출 스크립트

PostgreSQL Top SQL 추출 스크립트

1. IT Story/Scripts 2019. 4. 11. 21:41

PostgreSQL Top SQL 추출 스크립트

do $$
declare
   has_pg_stat_statements boolean;
begin
   select count(*)>0 from pg_extension where extname = 'pg_stat_statements' into has_pg_stat_statements;
   if has_pg_stat_statements = false then
        create extension pg_stat_statements;
        raise notice 'create extension pg_stat_statements';
   end if;
end
$$;

select
        queryid,
        sum( calls ) as calls,
        round(( sum( total_time )/ 1000 )::numeric, 2 ) as total_time,
        round(( min( min_time )/ 1000 )::numeric, 2 ) as min_time,
        round(( max( max_time )/ 1000 )::numeric, 2 ) as max_time,
        round((( sum( mean_time )/ 1000 )/ count(*))::numeric, 2 ) as mean_time,
        (
                sum( rows )/ count(*)
        )::int as rows,
        (
                sum( shared_blks_hit )/ count(*)
        )::int as shared_blks_hit,
        max( query ) as query
from
        pg_stat_statements ptt
where
        dbid in(
                select
                        oid
                from
                        pg_database
                where
                        datname = current_database()
        )
        and strpos(
                query,
                'create'
        )= 0
        and strpos(
                query,
                'drop'
        )= 0
        and strpos(
                query,
                'copy'
        )= 0
        and strpos(
                query,
                'pg_stat_user'
        )= 0
        and strpos(
                query,
                'vacuum'
        )= 0
group by
        queryid
order by
        total_time desc,
        mean_time desc limit 50;

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트 (0)	2019.04.17
Oracle 테스트 데이터 생성 스크립트 (0)	2019.04.16
Oracle SQL SysMetric 조회 스크립트 (0)	2019.04.13
Oracle Session UNDO, TEMP 사용 모니터링 (0)	2019.04.12
Oracle CPU Time, Waited Time 조회 스크립트 (0)	2019.04.10
PostgreSQL Autovacuum 대상 테이블 목록 스크립트 (0)	2019.04.08
PostgreSQL 통계(analyze) 상태 확인 스크립트 (0)	2019.04.07
Oracle 통계정보 백업, 복구(export/import) (0)	2019.04.06