打造YashanDB的ora工具-yat 是我们一直在打磨的一款 运维工具 。随着版本迭代，目前已经集成了10个实用功能，覆盖了日常 DBA 工作中最常见的数据收集、诊断与分析场景。接下来，我们逐个看看这些命令都能做什么。
1. 收集表的统计信息
yat table <table_name> 可以快速查看表的基本信息、列信息、索引信息，并自动触发统计信息收集。当存在多个同名表（不同 schema）时，工具会给出列表让你选择。
[yashan@yashandb1 yashango]$ ./yat table t1
============================================================
YashanDB Table Info: T1
============================================================
Multiple owners found, please select:
[1] BENCHMARK
[2] SYS
Enter selection (default 1):
Target: BENCHMARK.T1
Table Basic Info:
TABLE_NAME TABLESPACE_NAME TOTAL_MB NUM_ROWS BLOCKS LAST_ANALYZED
------------------------- --------------- ----------- --------------------- --------------------- --------------------
T1 USERS .0625 1 1 2026-04-30 17:13:00
Table Columns info:
COLUMN_NAME NDV NUL NUM_NULLS DATA_TYPE LAST_ANALYZED HISTOGRAM
------------------------- ---------- --- ---------- ------------------ -------------------- ----------
ID 1 N 0 INTEGER 2026-04-30 17:13:00 FREQUENCE
NAME 1 Y 0 VARCHAR(20) 2026-04-30 17:13:00 FREQUENCE
Table Index info:
INDEX_NAME INDEX_TYPE COLUMNS
------------------------------ --------------- ------------------------------------------------------------
SYS_C_49 PRIMARY ID
Analysis Finished.
2. 一键生成 AWR 报告
yat awr 自动获取最近 7 天的快照列表，交互式选择起止快照 ID，几秒钟即可生成 HTML 格式的 AWR 报告。
[yashan@yashandb1 yashango]$ ./yat awr
============================================================
YashanDB AWR Report Generator
============================================================
[1/3] Fetching snapshots from last 7 days...
SNAP_ID BEGIN_TIME END_TIME DB TIME (MINS)
----------- -------------------- -------------------- --------------
509 2026-04-23 18:12:01 2026-04-23 18:42:02
510 2026-04-23 18:42:02 2026-04-23 19:12:03 .6
... (省略中间大量快照) ...
795 2026-04-30 16:27:10 2026-04-30 16:57:11 .44
796 2026-04-30 16:57:11 2026-04-30 17:27:12 .44
Enter BEGIN Snapshot ID: 795
Enter END Snapshot ID: 796
[2/3] Generating AWR report...
[3/3] Success! Report created: awr_1044692841_1_795_796.html
3. 查询阻塞锁
yat block 会直接展示当前会话阻塞树，根节点是锁持有者，子节点是被阻塞的会话，一条链清晰直观。
[yashan@yashandb1 yashango]$ ./yat block
================================================================================
YashanDB Session Blocking Tree
================================================================================
INST_ID SID LEVEL PATH
----------- -------- --------------------- ------------------------------
1 41 1 ->41
1 42 2 ->41->42
Total sessions in blocking chain: 2
Note: LEVEL=1 is the root blocker (holder), LEVEL>1 are blocked sessions.
PATH shows the blocking chain, e.g. ->123->456 means SID 123 blocks SID 456.
4. 查询等待事件
yat event 汇总当前所有活跃会话的等待事件，并给出产生该等待的顶级 SQL 和用户，帮助快速定位性能瓶颈。
[yashan@yashandb1 yashango]$ ./yat event
================================================================================
YashanDB Active Session Wait Events
================================================================================
INST_ID WAIT_EVENT MAX_SQL_ID MAX_USER_NAME SUM_ELAP_S SESSION_CNT
----------- ----------------------------------- -------------- -------------------- ----------- ---------------------
1 row xact wait 30ntpmyapvr37 SYS 6522 1
Total wait event types: 1
Note: SESSION_CNT shows the number of active sessions per wait event.
SUM_ELAP_S shows total elapsed seconds across all sessions in that event.
5. 杀前台会话
yat kill 列出所有活跃会话，输入 SID 即可自动识别 SERIAL# 并执行 ALTER SYSTEM KILL SESSION。操作后会再次验证会话状态。
[yashan@yashandb1 yashango]$ ./yat kill
================================================================================
YashanDB Kill Session
================================================================================
Current Active Sessions (v$session):
SID SERIAL# USERNAME STATUS WAIT_EVENT PROGRAM
-------- ------------ -------------------- ---------- ------------------------------ ------------------------------
41 430 SYS INACTIVE SQL*Net message from client /u01/yashan/app/23.4.7.103/bin
42 854 SYS ACTIVE row xact wait /u01/yashan/app/23.4.7.103/bin
43 160 SYS INACTIVE SQL*Net message from client /u01/yashan/app/23.4.7.103/bin
44 1595 SYS INACTIVE SQL*Net message from client C:\Program Files\YashanDB\Yash
45 307 SYS INACTIVE SQL*Net message from client C:\Program Files\YashanDB\Yash
47 114 SYS INACTIVE SQL*Net message from client /u01/yashan/app/23.4.7.103/bin
Enter SID: 45
Session info for SID=45:
SID SERIAL# USERNAME STATUS WAIT_EVENT PROGRAM
-------- ------------ -------------------- ---------- ------------------------------ ------------------------------
45 307 SYS INACTIVE SQL*Net message from client C:\Program Files\YashanDB\Yash
Auto-detected SERIAL#=307 for SID=45
Are you sure to kill session SID=45, SERIAL#=307? [y/N]: y
Executing: ALTER SYSTEM KILL SESSION '45,307';
Succeed.
Verifying session status...
SID SERIAL# STATUS USERNAME
-------- ------------ --------- ----------------------------------------------------------------
45 307 KILLED SYS
[Info] Session may still exist (marked KILLED, will be cleaned up shortly).
6. 分析 SQL 性能
yat sql <sql_id> 会从历史性能仓库中提取该 SQL 的执行统计、历史执行计划、当前游标计划以及涉及的表信息。它是诊断单条 SQL 问题的利器。
[yashan@yashandb1 yashango]$ ./yat sql 30ntpmyapvr37
================================================================================
YashanDB SQL Performance Report: 30ntpmyapvr37
================================================================================
DBA_HIST_SQLSTAT detail(recent 15 days):
SNAP_ID SNAP_DATE INST PHV CPU_S EXECS BUF_PE ROW_PE MS_PE DISK_R IO_S
----------- ---------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
796 20260430 16:57 1 605450030 0 0 0 0 1.8 0 0
795 20260430 16:27 1 605450030 0 0 0 0 1.8 0 0
794 20260430 15:57 1 605450030 0 0 9 0 1.4 1 0
Historical Plans Summary(from WRH$_SQLSTAT):
PHV AVG_ETIME_S AVG_CPU_S AVG_BUFFERS EXECS FIRST_SNAP LAST_SNAP
----------- ----------- ----------- ----------- ----------- ----------- -----------
605450030 .002 .002 3 0 794 796
SQL Text (from V$SQLAREA):
update t1 set name='a' where id=1
Current SQL plans in Cursor (V$SQL_PLAN):
ID PLAN_TABLE_OUTPUT OBJECT_NAME COST ROWS
------------ ---------------------------------------------------------------- -------------------- --------------------- ---------------------
0 UPDATE STATEMENT
1 UPDATE T1
2 TABLE ACCESS FULL T1 1
Tables involved (Stats from DBA_TABLES):
TABLE_OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED SIZE_M
-------------------- ------------------------------ --------------------- --------------------- -----------
SYS T1 2 2026-04-30 17:20:13 0
Analysis Finished.
7. 查看表信息
与第 1 个功能“收集表统计信息”使用的是同一个核心逻辑，它完整呈现表结构、列统计、索引等元数据。
[yashan@yashandb1 yashango]$ ./yat table t1
============================================================
YashanDB Table Info: T1
============================================================
Multiple owners found, please select:
[1] BENCHMARK
[2] SYS
Enter selection (default 1):
Target: BENCHMARK.T1
Table Basic Info:
TABLE_NAME TABLESPACE_NAME TOTAL_MB NUM_ROWS BLOCKS LAST_ANALYZED
------------------------- --------------- ----------- --------------------- --------------------- --------------------
T1 USERS .0625 1 1 2026-04-30 17:13:00
Table Columns info:
COLUMN_NAME NDV NUL NUM_NULLS DATA_TYPE LAST_ANALYZED HISTOGRAM
------------------------- ---------- --- ---------- ------------------ -------------------- ----------
ID 1 N 0 INTEGER 2026-04-30 17:13:00 FREQUENCE
NAME 1 Y 0 VARCHAR(20) 2026-04-30 17:13:00 FREQUENCE
Table Index info:
INDEX_NAME INDEX_TYPE COLUMNS
------------------------------ --------------- ------------------------------------------------------------
SYS_C_49 PRIMARY ID
Analysis Finished.
8. 查看表空间
yat tbs 一次性列出所有表空间的使用情况，并给出百分比和最大可分配空间，方便容量规划。
[yashan@yashandb1 yashango]$ ./yat tbs
================================================================================
YashanDB Tablespace Usage
================================================================================
TBSNAME USED_MB USED_PERCENT TOTAL_MB MAX_ALLOCATE_MB REAL_FREE
------------------------- ----------- ------------ ----------- --------------- -----------
USERS 3281.13 .63 3328 524288 521006.87
UNDO 114.15 .17 768 65536 65421.85
SYSAUX 76.31 .01 128 524288 524211.69
SYSTEM 59.38 .01 64 524288 524228.62
TEMP 5 0 64 524288 524283
SWAP 1 0 1280 524288 524287
Summary:
--------
Total tablespaces: 6
Note: USED_PERCENT shows actual usage vs maximum allocatable space
9. 查看数据库用户信息
yat user 展示所有用户的默认表空间、账户状态、创建时间等信息。
[yashan@yashandb1 yashango]$ ./yat user
====================================================================================================
YashanDB User Information
====================================================================================================
USERNAME DEFAULT_TABLESPACE ACCOUNT_STATUS TEMPORARY_TABLESPACE CREATED PROFILE
------------------------- -------------------- -------------------- -------------------- -------------------- --------------------
AUDITOR USERS EXPIRED TEMP 2026-04-08 DEFAULT
BENCHMARK USERS OPEN TEMP 2026-04-08 DEFAULT
MDSYS USERS LOCKED TEMP 2026-04-08 DEFAULT
SECURITOR USERS EXPIRED TEMP 2026-04-08 DEFAULT
SYS SYSTEM OPEN TEMP 2026-04-08 DEFAULT
XA_SYS USERS LOCKED TEMP 2026-04-08 DEFAULT
YASOM USERS OPEN TEMP 2026-04-08 DEFAULT
Summary:
--------
Total users: 7
10. 查看版本信息
简单的 yat version 可输出当前工具版本及适配的 YashanDB 版本。
[yashan@yashandb1 yashango]$ ./yat version
YashanDB Tool (yat) v1.0 for YashanDB v23.4
除此之外，yat 还提供了一个统一的入口，直接运行 ./yat 就会列出所有可用命令及其简短说明：
[yashan@yashandb1 yashango]$ ./yat
Usage:
yat ana <table_name> [degree] Analyze table statistics
yat awr Generate AWR report
yat block Show session blocking tree
yat event Show session wait events
yat kill <sid> <serial#> Kill a session
yat sql <sql_id> Show SQL performance and plan history
yat table <table_name> Show table details
yat tbs Show tablespace usage
yat user Show user information
yat version Show version info
Analyze options:
degree - Degree of parallelism (default: 4, max: 32)
这套工具大幅简化了 YashanDB 的日常运维操作，我们将继续完善功能。有需要的同学可以关注后续更新。
发表于 4 小时前
|
查看: 4|
回复: 0
