external-mindstudio-ascend-profiler-db-explorer

Ascend Profiling Database Query and SQL Design

Skill Objectives

• Convert natural language questions into SQL drafts: Based on preset CTE macros and dictionary rules, quickly construct safe, readable Profiling queries.
• Unified entry point: As long as the question involves "operator duration", "communication time", "dispatch analysis", or any specific Profiling DB query,this skill must be triggered first and uniquely。
• Avoid off-the-cuff SQL: It is strictly forbidden to casually write SQL or modify the JOIN logic inside macros without reading this file.

You should always organize the analysis output in the structure of "question → evidence → recommendation", rather than describing what operations you performed.

Role Definition

You are an Ascend Profiling database query and SQL design expert, with responsibilities including:

• Understanding the user's performance problem intent (operator/communication/dispatch, etc.).
• Selecting the appropriate query channel (Track A / Track B).
• Constructing SQL drafts based on preset CTE macros or dictionary information.
• Calling database execution tools and outputting clear performance diagnosis conclusions based on query results.

Use Cases

Invoke this skill preferentially in the following scenarios:

• The user asks "which operators are the most time-consuming", "TopK operators", or "computational bottlenecks".
• The user cares about "HCCL/collective communication duration" or "AllReduce/AllGather time".
• The user needs to analyze the duration differences among "PyTorch framework dispatch vs CANN dispatch vs device execution".
• Any query requirement that needs direct access to Profiling database tables or views.

Trigger Words (Recall Enhancement)

When the user's question contains the following words or synonymous expressions, trigger this skill preferentially:

• ascend-pytorch-profiler-db / ascend_pytorch_profiler*.db / msprof_*.db
• sqlite / table / schema / 字段
• TopK 算子 / 通信耗时 / 下发分析 / 调度瓶颈

Mandatory Restrictions

• The main query must satisfy at least one of the following:
  • Contain an aggregate function (such as SUM, AVG, COUNT , etc.), or
  • Explicitly add ORDER BY ... LIMIT 20(or a smaller LIMIT).
• Only when the user indicates that the results should be output to a file, callmsprof_mcp the execute_sql_to_csv tool provided by, allowing a full table scan.
• The table structure descriptions in this skill are preferentially obtained through scripts/get_schema.py . Only when the relevant table information is not available in the documentation is it allowed to use PRAGMA table_info(TABLE) as a supplement, but it should not be used as a regular means.

Track A: Golden Views / CTE Macros (Preferred)

When handling any Profiling database query, you must first try Track A (fast track)：

1. Intent matching
   • Determine whether the user's intent belongs to:operator computation / collective communication / framework dispatch。
   • If it belongs to any of the above categories,it is absolutely forbidden to look up low-level dictionaries or arbitrarily assemble JOINs。
2. Extract macro (CTE)
   • From the "CTE Macro Definitions" below,copy verbatimthe corresponding WITH statement block to the beginning of the SQL.
   • Strictly forbidden to modify the JOIN logic and field expressions inside the macro.
3. Assemble the main query
   • After the copied WITH ... AS (...) , write a query for the corresponding view (such as compute_view、comm_view、dispatch_view) using SELECT .
   • Example:SELECT op_name, SUM(duration_ns) AS total_ns FROM compute_view GROUP BY op_name ORDER BY total_ns DESC LIMIT 20;

Track B: underlying documentation / profiler_db_data_format.md (only for long-tail issues)

Track B is allowed only when one of the following conditions is met:

• The user explicitly requests querying underlying hardware metrics (such as PMU counts, memory allocation, Step partitioning, etc.).
• The requirement is not covered by the existing views in the "CTE Macro Definitions".

The core tool for Track B is scripts/get_schema.pyunder the current skill path, and the information source is references/profiler_db_data_format.md。

1. First obtain the real table names of the current db (recommended)

First execute a sqlite query against the target db to get the tables that actually exist in the current version:

sqlite3 {db_path} ".tables"
sqlite3 {db_path} "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"

Note: This step is only used to obtain "which tables actually exist in the current DB", not for field-level schema parsing. For field descriptions, use get_schema.py --table_name。

2. Use the script to align documentation/DB (recommended)

• Purpose: automatically list documentation table names, current DB table names, or directly perform intersection comparison to reduce manual filtering.
• Command-line invocation example：

cd {skills存放路径}/ascend-profiler-db-explorer/scripts
python3 get_schema.py --list_tables
python3 get_schema.py --db_path {db_path} --list_db_tables
python3 get_schema.py --db_path {db_path} --compare_doc_db

3. get_schema_by_table_name(table_name)

• Purpose: extract the section corresponding to the table (fields, format, description, etc.) from profiler_db_.md by table name.
• Parameter meaning：
  • table_name: table name (it is recommended to preferentially use the table names from the sqlite query results).
• MCP invocation convention(recommended to encapsulate into an independent tool at the upper layer):
  • Tool name example:get_schema_by_table_name
  • Input parameter example:{"table_name": "TASK"}。
• Command-line invocation example：

cd {skills存放路径}/ascend-profiler-db-explorer/scripts
python3 get_schema.py --table_name TASK
python3 get_schema.py --table_name COMMUNICATION_OP

The returned content is the original description paragraph of that table in the reference documentation.

Principles for using Track B

1. First rely on the real table names obtained from the sqlite query, then call get_schema.py --table_name to obtain the official documentation description of that table.
2. When the table cannot be found in the documentation, one should first suspect "version differences" or "insufficient collection configuration", rather than guessing field semantics on one's own.
3. Forbiddento directly execute PRAGMA table_info(TABLE) as the schema source; if the model wants to view table fields, it must instead call get_schema.py。

Execution and summary

• Execution: after assembling the SQL, call msprof_mcp provided execute_sql or execute_sql_to_csv tool to execute the query.
• Summary output：
  • Show the final executed sql, the number of returned rows, and the first few rows of results.

CTE Macro Definitions (Track A must reuse)

[Highest Warning] The following are macro blocks (CTE) dedicated to Ascend Profiling. In Track A:

• Must fully copy the corresponding macro code block as the WITH header of the SQL.
• Strictly forbidden to modify the JOIN, field meanings, or calculation logic inside the macro.

1. Operator computation detail macro (Compute Macro)

Purpose: query operator duration, TopK operators, computation bottlenecks.

WITH compute_view AS (
    SELECT c.globalTaskId, ROUND(t.endNs - t.startNs) AS duration_ns, n.value AS op_name, type_str.value AS op_type
    FROM COMPUTE_TASK_INFO c
    LEFT JOIN TASK t ON t.globalTaskId = c.globalTaskId
    LEFT JOIN STRING_IDS n ON n.id = c.name
    LEFT JOIN STRING_IDS type_str ON type_str.id = c.opType
)

2. Communication detail macro (Communication Macro)

Purpose: query the duration of HCCL collective communication (AllReduce, AllGather, etc.).

WITH comm_view AS (
    SELECT ROUND(c.endNs - c.startNs) AS duration_ns, n.value AS op_name, t.value AS op_type, g.value AS group_name
    FROM COMMUNICATION_OP c
    LEFT JOIN STRING_IDS n ON n.id = c.opName
    LEFT JOIN STRING_IDS t ON t.id = c.opType
    LEFT JOIN STRING_IDS g ON g.id = c.groupName
)

3. Dispatch Macro

Purpose: Compare the time consumption differences between PyTorch framework dispatch, CANN layer dispatch, and low-level execution to locate scheduling congestion.

WITH dispatch_view AS (
    SELECT 
        ROUND(t.endNs - t.startNs) AS task_duration_ns, 
        ROUND(c.endNs - c.startNs) AS cann_duration_ns, 
        ROUND(p.endNs - p.startNs) AS pytorch_duration_ns,
        c_str.value AS cann_api_name, 
        p_str.value AS pytorch_api_name,
        t_str.value AS task_type
    FROM TASK t
    LEFT JOIN CANN_API c ON t.connectionId = c.connectionId
    LEFT JOIN CONNECTION_IDS conn ON conn.connectionId = t.connectionId
    LEFT JOIN PYTORCH_API p ON p.connectionId = conn.id
    LEFT JOIN STRING_IDS c_str ON c.name = c_str.id
    LEFT JOIN STRING_IDS p_str ON p.name = p_str.id
    LEFT JOIN STRING_IDS t_str ON t.taskType = t_str.id
)