作者
digoal
日期
2021-09-04
标签
PostgreSQL , Oracle , sql_trace
《PostgreSQL 兼容Oracle sql_trace 10046 10053 方法 - SQL诊断跟踪》
其他:
awr (perf insight, awr)
《PostgreSQL pg_stat_statements AWR 插件 pg_stat_monitor , 过去任何时间段性能分析 [推荐、收藏]》
历史会话 (perf insight)
《PostgreSQL 活跃会话历史记录插件 - pgsentinel 类似performance insight \ Oracle ASH Active Session History》
《PostgreSQL 13 preview - wait event sample - 等待事件统计(插件,hook) - ASH - performance insight》
《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级》
- PARSER STATISTICS
- PARSE ANALYSIS STATISTICS
- parse tree
- REWRITER STATISTICS
- rewritten parse tree
- PLANNER STATISTICS
- plan
- sort STATISTICS
- EXECUTOR STATISTICS
- QUERY PLAN
- actual time
- loops
- mem usage
- Buffers: shared hit
- blocks
- io timing
- 锁
- WAL
- JIT
- 死锁等
详见:
《2019-PostgreSQL 2天体系化培训 - 视频每周更新》
打印到当前会话前台, 同时打印到日志
\set VERBOSITY verbose
set log_min_messages = debug5;
set log_min_error_statement = debug5;
-- set trace_recovery_messages = DEBUG5; # 只能启动前在参数文件中配置
-- set track_commit_timestamp = on; # 只能启动前在参数文件中配置
-- set log_checkpoints = on; # 只能启动前在参数文件中配置
set log_error_verbosity = verbose;
set log_lock_waits = on;
set log_replication_commands = off;
set log_temp_files = 0;
set track_activities = on;
set track_counts = on;
set track_io_timing = on;
set track_wal_io_timing = on;
set track_functions = 'all';
set trace_sort = on;
-- set trace_locks = on; # 要求 LOCK_DEBUG macro 定义
-- set trace_lwlocks = on; # 要求 LOCK_DEBUG macro 定义
-- set trace_userlocks = on; # 要求 LOCK_DEBUG macro 定义
-- set trace_lock_oidmin # 要求 LOCK_DEBUG macro 定义
-- set trace_lock_table # 要求 LOCK_DEBUG macro 定义
-- set debug_deadlocks = on; # 要求 LOCK_DEBUG macro 定义
-- set log_btree_build_stats = on; # 要求 BTREE_BUILD_STATS macro 定义
-- set wal_debug = on; # 要求 WAL_DEBUG macro 定义
-- set jit_debugging_support = on; # 只能启动前在参数文件中配置
set jit_dump_bitcode = on;
set jit_expressions = on;
-- set jit_profiling_support = on; # 只能启动前在参数文件中配置
set jit_tuple_deforming = on;
-- set log_statement_stats = on; # 与log_statement_stats互斥, 可挑选使用
set log_parser_stats = on; -- 与log_statement_stats互斥, 可挑选使用
set log_planner_stats = on; -- 与log_statement_stats互斥, 可挑选使用
set log_executor_stats = on; -- 与log_statement_stats互斥, 可挑选使用
-- set log_autovacuum_min_duration = 0; # 只能启动前在参数文件中配置
set deadlock_timeout = '1s';
set debug_print_parse = on;
set debug_print_rewritten = on;
set debug_print_plan = on;
set debug_pretty_print = on;
set lock_timeout = '1s';
-- set log_recovery_conflict_waits = on; # 只能启动前在参数文件中配置
set client_min_messages = log; -- 可选debug5,...debug1, log, notice, warning, error
例子
postgres=# explain (analyze,verbose,timing,costs,buffers,WAL,SUMMARY) select count(*),relkind from pg_class group by relkind order by count(*) desc limit 1;
LOG: 00000: PARSER STATISTICS
DETAIL: ! system usage stats:
! 0.000101 s user, 0.000069 s system, 0.000168 s elapsed
! [0.005759 s user, 0.003854 s system total]
! 4120 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/36 [0/1095] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [31/77] messages rcvd/sent
! 0/0 [1/33] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: PARSE ANALYSIS STATISTICS
DETAIL: ! system usage stats:
! 0.000597 s user, 0.000211 s system, 0.000809 s elapsed
! [0.006405 s user, 0.004079 s system total]
! 4824 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/179 [0/1274] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [31/78] messages rcvd/sent
! 0/0 [1/33] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: parse tree:
DETAIL: {QUERY
:commandType 5
:querySource 0
:canSetTag true
:utilityStmt ?
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:isReturn false
:cteList <>
:rtable <>
:jointree <>
:targetList <>
:override 0
:onConflict <>
:returningList <>
:groupClause <>
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 144
}
LOCATION: elog_node_display, print.c:85
LOG: 00000: REWRITER STATISTICS
DETAIL: ! system usage stats:
! 0.000000 s user, 0.000000 s system, 0.000001 s elapsed
! [0.006462 s user, 0.004101 s system total]
! 4828 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [0/1275] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [31/80] messages rcvd/sent
! 0/0 [1/33] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: rewritten parse tree:
DETAIL: (
{QUERY
:commandType 5
:querySource 0
:canSetTag true
:utilityStmt ?
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:isReturn false
:cteList <>
:rtable <>
:jointree <>
:targetList <>
:override 0
:onConflict <>
:returningList <>
:groupClause <>
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 144
}
)
LOCATION: elog_node_display, print.c:85
LOG: 00000: PLANNER STATISTICS
DETAIL: ! system usage stats:
! 0.000549 s user, 0.000530 s system, 0.001121 s elapsed
! [0.007130 s user, 0.004662 s system total]
! 5668 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/191 [0/1486] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [31/82] messages rcvd/sent
! 0/2 [1/35] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: plan:
DETAIL: {PLANNEDSTMT
:commandType 1
:queryId 0
:hasReturning false
:hasModifyingCTE false
:canSetTag true
:transientPlan false
:dependsOnRole false
:parallelModeNeeded false
:jitFlags 0
:planTree
{LIMIT
:startup_cost 34.31
:total_cost 34.32
:plan_rows 1
:plan_width 9
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 0
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 0
:varattnosyn 0
:location -1
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location -1
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:qual <>
:lefttree
{SORT
:startup_cost 34.31
:total_cost 34.32
:plan_rows 4
:plan_width 9
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 1
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 0
:varattnosyn 0
:location -1
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location -1
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:qual <>
:lefttree
{AGG
:startup_cost 34.25
:total_cost 34.29
:plan_rows 4
:plan_width 9
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 2
:targetlist (
{TARGETENTRY
:expr
{AGGREF
:aggfnoid 2803
:aggtype 20
:aggcollid 0
:inputcollid 0
:aggtranstype 20
:aggargtypes <>
:aggdirectargs <>
:args <>
:aggorder <>
:aggdistinct <>
:aggfilter <>
:aggstar true
:aggvariadic false
:aggkind n
:agglevelsup 0
:aggsplit 0
:aggno 0
:aggtransno 0
:location 66
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 17
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 0
:varattnosyn 0
:location -1
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:qual <>
:lefttree
{SEQSCAN
:startup_cost 0.00
:total_cost 32.17
:plan_rows 417
:plan_width 1
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 3
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 1
:location -1
}
:resno 1
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 19
:vartypmod -1
:varcollid 950
:varlevelsup 0
:varnosyn 1
:varattnosyn 2
:location -1
}
:resno 2
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 3
:location -1
}
:resno 3
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 4
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 4
:location -1
}
:resno 4
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 5
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 5
:location -1
}
:resno 5
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 6
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 6
:location -1
}
:resno 6
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 7
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 7
:location -1
}
:resno 7
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 8
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 8
:location -1
}
:resno 8
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 9
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 9
:location -1
}
:resno 9
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 10
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 10
:location -1
}
:resno 10
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 11
:vartype 700
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 11
:location -1
}
:resno 11
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 12
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 12
:location -1
}
:resno 12
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 13
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 13
:location -1
}
:resno 13
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 14
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 14
:location -1
}
:resno 14
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 15
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 15
:location -1
}
:resno 15
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 16
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 16
:location -1
}
:resno 16
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 17
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location -1
}
:resno 17
:resname <>
:ressortgroupref 2
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 18
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 18
:location -1
}
:resno 18
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 19
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 19
:location -1
}
:resno 19
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 20
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 20
:location -1
}
:resno 20
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 21
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 21
:location -1
}
:resno 21
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 22
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 22
:location -1
}
:resno 22
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 23
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 23
:location -1
}
:resno 23
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 24
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 24
:location -1
}
:resno 24
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 25
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 25
:location -1
}
:resno 25
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 26
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 26
:location -1
}
:resno 26
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 27
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 27
:location -1
}
:resno 27
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 28
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 28
:location -1
}
:resno 28
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 29
:vartype 28
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 29
:location -1
}
:resno 29
:resname <>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
