在ORACLE中ASH是非常有用的性能问题诊断工具，在Mogdb中是否有对象的功能呢？答案是有的，叫做ASP(ACTIVE SESSION PROFILE).虽然指标还比较粗，但总算是有了。
下面以ORACLE DBA的视角是探索一下ASP。
有哪些参数?
[omm2@og01 ~]$ gsql -c "show all"|grep asp
asp_flush_mode | table | Sets the active session profile flush mode:file/table/all.
asp_flush_rate | 10 | every Nth sample to disk, MOD(sample_id, N) = 0 will flush to dist
asp_log_directory | asp_data/test | Sets the destination directory for asp log files.
asp_log_filename | asp-%Y-%m-%d_%H%M%S.log | Sets the file name pattern for asp data files.
asp_retention_days | 2 | set max retention days for pg_asp
asp_sample_interval | 1s | Sets the active session profile max sample nums in buff
asp_sample_num | 100000 | Sets the active session profile max sample nums in buff
enable_asp | on | Enable active session profile
有哪些视图?
MogDB=# show search_path;
search_path
--------------------------
"$user", publi, dbe_perf
(1 row)
MogDB=# \dv *sess*
List of relations
Schema | Name | Type | Owner | Storage
------------+------------------------------+------+-------+---------
dbe_perf | global_session_memory | view | omm2 |
dbe_perf | global_session_memory_detail | view | omm2 |
dbe_perf | global_session_stat | view | omm2 |
dbe_perf | global_session_stat_activity | view | omm2 |<<<<<<current active session
dbe_perf | global_session_time | view | omm2 |
dbe_perf | global_stat_session_cu | view | omm2 |
dbe_perf | local_active_session | view | omm2 |<<<<<<active session history(sample interval 1s)
dbe_perf | session_cpu_runtime | view | omm2 |
dbe_perf | session_memory | view | omm2 |
dbe_perf | session_memory_detail | view | omm2 |
dbe_perf | session_memory_runtime | view | omm2 |
dbe_perf | session_stat | view | omm2 |
dbe_perf | session_stat_activity | view | omm2 |
dbe_perf | session_time | view | omm2 |
pg_catalog | gs_session_cpu_statistics | view | omm2 |
pg_catalog | gs_session_memory | view | omm2 |
pg_catalog | gs_session_memory_context | view | omm2 |
pg_catalog | gs_session_memory_detail | view | omm2 |
pg_catalog | gs_session_memory_statistics | view | omm2 |
pg_catalog | gs_session_stat | view | omm2 |
pg_catalog | gs_session_time | view | omm2 |
pg_catalog | gs_stat_session_cu | view | omm2 |
pg_catalog | gs_wlm_session_history | view | omm2 |
pg_catalog | gs_wlm_session_info | view | omm2 |
pg_catalog | gs_wlm_session_info_all | view | omm2 |
pg_catalog | gs_wlm_session_statistics | view | omm2 |
pg_catalog | pg_session_iostat | view | omm2 |
pg_catalog | pg_session_wlmstat | view | omm2 |
(28 rows)
MogDB=# \d global_session_stat_activity
View "dbe_perf.global_session_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
coorname | text |
datid | oid |
datname | text |
pid | bigint |
usesysid | oid |
usename | text |
application_name | text |
client_addr | inet |
client_hostname | text |
client_port | integer |
backend_start | timestamp with time zone |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
state_change | timestamp with time zone |
waiting | boolean |
enqueue | text |
state | text |
resource_pool | name |
query_id | bigint |
query | text |
unique_sql_id | bigint |
trace_id | text |
MogDB=# \d local_active_session
View "dbe_perf.local_active_session"
Column | Type | Modifiers
-----------------------+--------------------------+-----------
sampleid | bigint |
sample_time | timestamp with time zone |
need_flush_sample | boolean |
databaseid | oid |
thread_id | bigint |
sessionid | bigint |
start_time | timestamp with time zone |
event | text |
lwtid | integer |
psessionid | bigint |
tlevel | integer |
smpid | integer |
userid | oid |
application_name | text |
client_addr | inet |
client_hostname | text |
client_port | integer |
query_id | bigint |
unique_query_id | bigint |
user_id | oid |
cn_id | integer |
unique_query | text |
locktag | text |
lockmode | text |
block_sessionid | bigint |
final_block_sessionid | bigint |
wait_status | text |
global_sessionid | text |
plan_node_id | integer |
MogDB=# select max(sample_time),now() from local_active_session;
max | now
-------------------------------+-------------------------------
2022-05-18 15:26:11.706498+08 | 2022-05-18 15:26:12.440904+08
(1 row)
与等待事件相关的视图
MogDB=# \d os_threads
View "dbe_perf.os_threads"
Column | Type | Modifiers
---------------+--------------------------+-----------
node_name | text |
pid | bigint |
lwpid | integer |
thread_name | text |
creation_time | timestamp with time zone |
MogDB=# \d thread_wait_status
View "dbe_perf.thread_wait_status"
Column | Type | Modifiers
------------------+---------+-----------
node_name | text |
db_name | text |
thread_name | text |
query_id | bigint |
tid | bigint |
sessionid | bigint |
lwtid | integer |
psessionid | bigint |
tlevel | integer |
smpid | integer |
wait_status | text |
wait_event | text |
locktag | text |
lockmode | text |
block_sessionid | bigint |
global_sessionid | text |
移植ORACLE中ASH分析角本
with t as (select to_char(sample_time,'hh24:mi')etime,unique_query_id,round(count(*),2) cnt,
round(1.0*count(*)/NULLIF(SUM(count(*)) OVER(partition by to_char(sample_time,'hh24:mi')),1)*100,0)||'%' pct,
row_number()over(partition by to_char(sample_time,'hh24:mi')order by count(*) desc) rn
from local_active_session
where sample_time>now()-'10 mi'::interval
group by to_char(sample_time,'hh24:mi'),unique_query_id)
select * from t where rn<=10 order by etime,cnt desc;
etime | unique_query_id | cnt | pct | rn
-------+-----------------+--------+------+----
15:56 | | 224.00 | 100% | 1
15:57 | | 420.00 | 100% | 1
15:58 | | 398.00 | 100% | 1
15:59 | | 354.00 | 100% | 1
16:00 | | 361.00 | 100% | 1
16:01 | | 361.00 | 100% | 1
16:02 | | 362.00 | 100% | 1
16:03 | | 479.00 | 69% | 1
16:03 | 1323875317 | 42.00 | 6% | 2
16:03 | 3867546897 | 28.00 | 4% | 3
16:03 | 855126023 | 21.00 | 3% | 4
16:03 | 617922769 | 20.00 | 3% | 5
16:03 | 3765514031 | 14.00 | 2% | 6
16:03 | 541675883 | 14.00 | 2% | 7
16:03 | 2486663307 | 11.00 | 2% | 8
16:03 | 1151796260 | 10.00 | 1% | 9
16:03 | 3396063170 | 6.00 | 1% | 10
16:04 | | 515.00 | 66% | 1
16:04 | 1323875317 | 44.00 | 6% | 2
16:04 | 855126023 | 37.00 | 5% | 3
16:04 | 617922769 | 29.00 | 4% | 4
16:04 | 3867546897 | 27.00 | 3% | 5
16:04 | 3396063170 | 26.00 | 3% | 6
16:04 | 541675883 | 19.00 | 2% | 7
16:04 | 2525665588 | 9.00 | 1% | 8
16:04 | 3878359364 | 9.00 | 1% | 9
16:04 | 4290726527 | 9.00 | 1% | 10
16:05 | | 496.00 | 64% | 1
16:05 | 1323875317 | 55.00 | 7% | 2
16:05 | 3867546897 | 38.00 | 5% | 3
16:05 | 855126023 | 30.00 | 4% | 4
16:05 | 617922769 | 28.00 | 4% | 5
16:05 | 3396063170 | 23.00 | 3% | 6
16:05 | 541675883 | 21.00 | 3% | 7
16:05 | 3878359364 | 13.00 | 2% | 8
16:05 | 1151796260 | 11.00 | 1% | 9
16:05 | 1284732268 | 9.00 | 1% | 10
16:06 | | 250.00 | 69% | 1
16:06 | 1323875317 | 18.00 | 5% | 2
16:06 | 3867546897 | 16.00 | 4% | 3
16:06 | 855126023 | 14.00 | 4% | 4
16:06 | 541675883 | 10.00 | 3% | 5
16:06 | 3878359364 | 8.00 | 2% | 6
16:06 | 3396063170 | 7.00 | 2% | 7
16:06 | 3287280357 | 6.00 | 2% | 8
16:06 | 617922769 | 5.00 | 1% | 9
16:06 | 1487146832 | 4.00 | 1% | 10
(47 rows)
with t as (select to_char(sample_time,'hh24:mi')etime,event,round(count(*)/60,2) cnt,
round(1.0*count(*)/NULLIF(SUM(count(*)) OVER(partition by to_char(sample_time,'hh24:mi')),1)*100,0)||'%' pct,
row_number()over(partition by to_char(sample_time,'hh24:mi')order by count(*) desc) rn
from local_active_session
where sample_time>now()-'10 mi'::interval
group by to_char(sample_time,'hh24:mi') ,event)
select * from t where rn<=10 order by etime,cnt desc;
etime | event | cnt | pct | rn
-------+------------------------+------+------+----
15:58 | none | 4.52 | 100% | 1
15:58 | analyze | .02 | 0% | 2
15:59 | none | 5.90 | 100% | 1
16:00 | none | 6.02 | 100% | 1
16:01 | none | 6.02 | 100% | 1
16:02 | none | 5.90 | 98% | 1
16:02 | vacuum | .13 | 2% | 2
16:03 | none | 8.87 | 77% | 1
16:03 | vacuum | 1.00 | 9% | 2
16:03 | wait cmd | .85 | 7% | 3
16:03 | Sort - fetch tuple | .32 | 3% | 4
16:03 | DataFileRead | .10 | 1% | 5
16:03 | transactionid | .10 | 1% | 6
16:03 | WALFlushWait | .08 | 1% | 7
16:03 | HashAgg - build hash | .08 | 1% | 8
16:03 | BufHashTableSearch | .03 | 0% | 9
16:03 | Sort | .02 | 0% | 10
16:04 | none | 9.53 | 73% | 1
16:04 | wait cmd | 1.35 | 10% | 2
16:04 | vacuum | .98 | 8% | 3
16:04 | Sort - fetch tuple | .47 | 4% | 4
16:04 | transactionid | .25 | 2% | 5
16:04 | WALFlushWait | .13 | 1% | 6
16:04 | DWSingleFlushFirstLock | .10 | 1% | 7
16:04 | flush data | .05 | 0% | 8
16:04 | DataFileRead | .03 | 0% | 9
16:04 | HashAgg - build hash | .03 | 0% | 10
16:05 | none | 9.62 | 75% | 1
16:05 | wait cmd | 1.15 | 9% | 2
16:05 | vacuum | 1.00 | 8% | 3
16:05 | Sort - fetch tuple | .45 | 3% | 4
16:05 | transactionid | .33 | 3% | 5
16:05 | WALFlushWait | .13 | 1% | 6
16:05 | DataFileRead | .07 | 1% | 7
16:05 | HashAgg - build hash | .05 | 0% | 8
16:05 | WALWrite | .03 | 0% | 9
16:05 | flush data | .03 | 0% | 10
16:06 | none | 9.37 | 68% | 1
16:06 | wait cmd | 1.50 | 11% | 2
16:06 | vacuum | .98 | 7% | 3
16:06 | analyze | .80 | 6% | 4
16:06 | transactionid | .37 | 3% | 5
16:06 | Sort - fetch tuple | .35 | 3% | 6
16:06 | DWSingleFlushFirstLock | .12 | 1% | 7
16:06 | HashAgg - build hash | .05 | 0% | 8
16:06 | flush data | .05 | 0% | 9
16:06 | extend | .03 | 0% | 10
16:07 | none | 9.35 | 68% | 1
16:07 | wait cmd | 1.32 | 10% | 2
16:07 | analyze | .98 | 7% | 3
16:07 | vacuum | .98 | 7% | 4
16:07 | Sort - fetch tuple | .45 | 3% | 5
16:07 | transactionid | .22 | 2% | 6
16:07 | HashAgg - build hash | .07 | 0% | 7
16:07 | BufferContentLock | .07 | 0% | 8
16:07 | WALFlushWait | .05 | 0% | 9
16:07 | DWSingleFlushFirstLock | .05 | 0% | 10
16:08 | none | 2.95 | 73% | 1
16:08 | vacuum | .32 | 8% | 2
16:08 | analyze | .32 | 8% | 3
16:08 | wait cmd | .22 | 5% | 4
16:08 | Sort - fetch tuple | .08 | 2% | 5
16:08 | transactionid | .07 | 2% | 6
16:08 | HashAgg - build hash | .02 | 0% | 7
16:08 | DataFileWrite | .02 | 0% | 8
16:08 | WALFlushWait | .02 | 0% | 9
16:08 | DataFilePrefetch | .02 | 0% | 10
(67 rows)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
