目录

OceanBase 4.2 SQL性能优化指南 - CPU高耗时SQL诊断与优化

一、CPU高耗时SQL识别

1.1 通过GV$OB_SQL_AUDIT视图查找高CPU消耗SQL

-- 查找CPU消耗TOP 20的SQL
SELECT /*+ QUERY_TIMEOUT(30000000) */
    SQL_ID,
    PLAN_ID,
    QUERY_SQL,
    ROUND(AVG(ELAPSED_TIME)/1000, 2) AS avg_elapsed_ms,
    ROUND(AVG(CPU_TIME)/1000, 2) AS avg_cpu_ms,
    ROUND(AVG(CPU_TIME)/AVG(ELAPSED_TIME)*100, 2) AS cpu_ratio,
    SUM(EXECUTIONS) AS total_exec,
    ROUND(SUM(CPU_TIME)/1000000, 2) AS total_cpu_sec,
    ROUND(AVG(ROW_PROCESSED)) AS avg_rows,
    ROUND(AVG(LOGICAL_READS)) AS avg_logical_reads,
    ROUND(AVG(PHYSICAL_READS)) AS avg_physical_reads,
    MAX(REQUEST_TIME) AS last_exec_time
FROM 
    oceanbase.GV$OB_SQL_AUDIT
WHERE 
    REQUEST_TIME > DATE_SUB(NOW(), INTERVAL 1 HOUR)
    AND IS_INNER_SQL = 0
    AND EXECUTIONS > 0
GROUP BY 
    SQL_ID, PLAN_ID, QUERY_SQL
HAVING 
    AVG(CPU_TIME) > 10000  -- CPU时间超过10ms
ORDER BY 
    total_cpu_sec DESC
LIMIT 20;

1.2 实时监控CPU密集型会话

-- 查看当前正在执行的高CPU消耗SQL
SELECT 
    sid,
    sql_id,
    plan_id,
    trace_id,
    query_sql,
    ROUND((SYSDATE - query_start) * 24 * 60 * 60) AS exec_seconds,
    ROUND(cpu_time/1000, 2) AS cpu_ms,
    state,
    event,
    tenant_id
FROM 
    oceanbase.GV$OB_PROCESSLIST
WHERE 
    state = 'ACTIVE'
    AND cpu_time > 100000  -- CPU时间超过100ms
ORDER BY 
    cpu_time DESC;

1.3 按Plan Hash聚合分析

-- 按执行计划聚合统计
SELECT 
    PLAN_ID,
    COUNT(DISTINCT SQL_ID) AS sql_count,
    SUM(EXECUTIONS) AS total_exec,
    ROUND(AVG(CPU_TIME)/1000, 2) AS avg_cpu_ms,
    ROUND(SUM(CPU_TIME)/1000000, 2) AS total_cpu_sec,
    ROUND(AVG(ELAPSED_TIME)/1000, 2) AS avg_elapsed_ms,
    ROUND(AVG(LOGICAL_READS)) AS avg_logical_reads,
    MIN(QUERY_SQL) AS sample_sql
FROM 
    oceanbase.GV$OB_SQL_AUDIT
WHERE 
    REQUEST_TIME > DATE_SUB(NOW(), INTERVAL 1 HOUR)
    AND IS_INNER_SQL = 0
GROUP BY 
    PLAN_ID
HAVING 
    SUM(CPU_TIME) > 1000000000  -- 总CPU时间超过1000秒
ORDER BY 
    total_cpu_sec DESC
LIMIT 20;

二、基于Plan Hash的执行计划详情分析

2.1 获取执行计划详情

-- 方法1：通过PLAN_ID获取执行计划
SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT 
WHERE PLAN_ID = 'your_plan_id';

-- 方法2：通过SQL_ID和PLAN_ID获取详细执行计划
SELECT 
    OPERATOR,
    NAME,
    ROWS,
    COST,
    PROPERTY
FROM 
    oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
WHERE 
    TENANT_ID = 1001  -- 替换为实际租户ID
    AND PLAN_ID = 'your_plan_id'
ORDER BY 
    OPERATOR_ID;

-- 方法3：使用EXPLAIN EXTENDED查看执行计划
EXPLAIN EXTENDED 
SELECT /*+ USE_PLAN_CACHE(NONE) */ 
    * 
FROM your_table 
WHERE your_conditions;

2.2 执行计划关键指标解读

-- 分析执行计划的关键性能指标
SELECT 
    p.PLAN_ID,
    p.SQL_ID,
    p.TYPE,
    p.IS_BIND_SENSITIVE,
    p.IS_BIND_AWARE,
    s.AVG_EXE_TIME,
    s.SLOWEST_EXE_TIME,
    s.SLOW_COUNT,
    s.HIT_COUNT,
    s.PLAN_SIZE,
    s.EXECUTIONS,
    s.DISK_READS,
    s.BUFFER_GETS,
    s.CPU_TIME,
    s.ELAPSED_TIME,
    s.TABLE_SCAN,
    s.INSERT_COUNT,
    s.UPDATE_COUNT,
    s.DELETE_COUNT,
    s.SELECT_COUNT
FROM 
    oceanbase.GV$OB_PLAN_CACHE_PLAN p
    JOIN oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT s 
        ON p.PLAN_ID = s.PLAN_ID
WHERE 
    p.PLAN_ID = 'your_plan_id';

2.3 执行计划算子分析

-- 详细分析每个算子的执行情况
WITH plan_detail AS (
    SELECT 
        PLAN_ID,
        OPERATOR_ID,
        OPERATOR,
        NAME,
        ROWS,
        COST,
        CARDINALITY,
        PROPERTY,
        EXECUTION_TIME,
        CPU_TIME,
        IO_TIME
    FROM 
        oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
    WHERE 
        PLAN_ID = 'your_plan_id'
)
SELECT 
    OPERATOR_ID,
    OPERATOR,
    NAME,
    ROWS AS estimated_rows,
    COST AS estimated_cost,
    EXECUTION_TIME AS actual_time,
    CPU_TIME,
    IO_TIME,
    CASE 
        WHEN ROWS > 0 THEN ROUND(CARDINALITY/ROWS, 2)
        ELSE NULL 
    END AS cardinality_accuracy,
    PROPERTY
FROM 
    plan_detail
ORDER BY 
    OPERATOR_ID;

三、CPU密集型SQL常见问题模式

3.1 问题模式识别

模式1：全表扫描（TABLE SCAN）
-- 识别全表扫描
SELECT 
    SQL_ID,
    PLAN_ID,
    QUERY_SQL,
    TABLE_SCAN,
    LOGICAL_READS,
    CPU_TIME/1000 AS cpu_ms
FROM 
    oceanbase.GV$OB_SQL_AUDIT
WHERE 
    TABLE_SCAN = 1
    AND LOGICAL_READS > 10000
    AND REQUEST_TIME > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY 
    CPU_TIME DESC
LIMIT 10;
模式2：嵌套循环连接（NESTED LOOP JOIN）大数据集
-- 识别低效的嵌套循环
EXPLAIN FORMAT=JSON
SELECT /*+ USE_NL(t1 t2) */ *
FROM large_table t1
JOIN another_large_table t2 ON t1.id = t2.ref_id;

-- 查看实际执行统计
SELECT 
    OPERATOR,
    ROWS,
    COST,
    OUTPUT_ROWS,
    EXECUTION_TIME
FROM 
    oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
WHERE 
    PLAN_ID = 'your_plan_id'
    AND OPERATOR LIKE '%NESTED%LOOP%';
模式3：排序操作（SORT）消耗大量CPU
-- 识别大量排序操作
SELECT 
    SQL_ID,
    PLAN_ID,
    QUERY_SQL,
    SORT_MERGE_PASSES,
    SORTS,
    CPU_TIME/1000 AS cpu_ms
FROM 
    oceanbase.GV$OB_SQL_AUDIT
WHERE 
    SORTS > 0
    AND CPU_TIME > 100000
    AND REQUEST_TIME > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY 
    CPU_TIME DESC;

3.2 典型问题案例分析

案例1：缺失索引导致的全表扫描
-- 原始SQL（问题）
SELECT * FROM orders 
WHERE order_date = '2024-01-01' 
  AND status = 'COMPLETED';

-- 执行计划分析
EXPLAIN EXTENDED SELECT * FROM orders 
WHERE order_date = '2024-01-01' 
  AND status = 'COMPLETED';

-- 输出示例：
-- | TABLE SCAN | orders | 1000000 rows | cost=50000 |

-- 优化方案：创建复合索引
CREATE INDEX idx_orders_date_status 
ON orders(order_date, status);

-- 优化后执行计划
-- | INDEX RANGE SCAN | idx_orders_date_status | 100 rows | cost=50 |
案例2：不当的JOIN顺序
-- 问题SQL：小表驱动大表
SELECT /*+ LEADING(large_table) USE_NL(small_table) */
    l.*, s.*
FROM 
    large_table l
    JOIN small_table s ON l.id = s.ref_id
WHERE 
    s.type = 'A';

-- 优化：调整JOIN顺序，小表驱动大表
SELECT /*+ LEADING(small_table) USE_NL(large_table) */
    l.*, s.*
FROM 
    small_table s
    JOIN large_table l ON s.ref_id = l.id
WHERE 
    s.type = 'A';

-- 或使用HASH JOIN
SELECT /*+ USE_HASH(l s) */
    l.*, s.*
FROM 
    large_table l
    JOIN small_table s ON l.id = s.ref_id
WHERE 
    s.type = 'A';

四、优化策略与方法

4.1 索引优化

-- 1. 分析表的索引使用情况
SELECT 
    table_name,
    index_name,
    column_name,
    column_position,
    index_type
FROM 
    oceanbase.DBA_IND_COLUMNS
WHERE 
    table_name = 'YOUR_TABLE'
ORDER BY 
    index_name, column_position;

-- 2. 查看索引使用统计
SELECT 
    INDEX_NAME,
    TABLE_NAME,
    BLEVEL,
    LEAF_BLOCKS,
    DISTINCT_KEYS,
    AVG_LEAF_BLOCKS_PER_KEY,
    AVG_DATA_BLOCKS_PER_KEY,
    CLUSTERING_FACTOR
FROM 
    oceanbase.DBA_INDEXES
WHERE 
    TABLE_NAME = 'YOUR_TABLE';

-- 3. 创建覆盖索引减少回表
CREATE INDEX idx_covering 
ON orders(customer_id, order_date, status, amount)
GLOBAL PARTITION BY HASH(customer_id) PARTITIONS 16;

4.2 SQL改写优化

4.2.1 避免函数操作导致索引失效
-- 问题SQL
SELECT * FROM orders 
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2024-01';

-- 优化后
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2024-02-01';
4.2.2 使用EXISTS替代IN
-- 问题SQL
SELECT * FROM orders o
WHERE o.customer_id IN (
    SELECT c.id FROM customers c
    WHERE c.vip_level > 3
);

-- 优化后
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c
    WHERE c.id = o.customer_id
      AND c.vip_level > 3
);
4.2.3 分页查询优化
-- 问题SQL：深分页
SELECT * FROM orders 
ORDER BY id 
LIMIT 1000000, 20;

-- 优化方案1：使用延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    ORDER BY id
    LIMIT 1000000, 20
) t ON o.id = t.id;

-- 优化方案2：使用游标方式
SELECT * FROM orders
WHERE id > last_seen_id
ORDER BY id
LIMIT 20;

4.3 使用Hint优化

-- 常用Hint示例
SELECT /*+ 
    USE_PLAN_CACHE(NONE)           -- 不使用计划缓存
    QUERY_TIMEOUT(10000000)         -- 设置查询超时
    READ_CONSISTENCY(WEAK)         -- 弱一致性读
    USE_HASH(t1 t2)               -- 使用Hash Join
    USE_NL(t1 t2)                  -- 使用Nested Loop Join
    USE_MERGE(t1 t2)               -- 使用Merge Join
    LEADING(t1 t2 t3)              -- 指定JOIN顺序
    INDEX(t1 idx_name)             -- 指定使用的索引
    FULL(t1)                       -- 强制全表扫描
    PARALLEL(4)                    -- 并行度
*/
    t1.*, t2.*
FROM 
    table1 t1
    JOIN table2 t2 ON t1.id = t2.ref_id;

4.4 并行执行优化

-- 1. 设置会话级并行度
SET SESSION parallel_servers_target = 64;
SET SESSION parallel_degree_policy = AUTO;

-- 2. 查询级别指定并行度
SELECT /*+ PARALLEL(8) */ 
    COUNT(*), 
    SUM(amount)
FROM large_orders
WHERE order_date >= '2024-01-01';

-- 3. 表级别设置并行度
ALTER TABLE large_orders PARALLEL 8;

-- 4. 监控并行执行
SELECT 
    SQL_ID,
    PLAN_ID,
    PX_SERVERS_REQUESTED,
    PX_SERVERS_ALLOCATED,
    ELAPSED_TIME,
    CPU_TIME
FROM 
    oceanbase.GV$OB_SQL_AUDIT
WHERE 
    PX_SERVERS_ALLOCATED > 0
ORDER BY 
    REQUEST_TIME DESC;

五、执行计划缓存管理

5.1 计划缓存分析

-- 查看计划缓存使用情况
SELECT 
    TENANT_ID,
    SVR_IP,
    SQL_NUM,
    MEM_USED,
    MEM_HOLD,
    ACCESS_COUNT,
    HIT_COUNT,
    HIT_RATE,
    PLAN_NUM,
    PLAN_MEM_USED
FROM 
    oceanbase.GV$OB_PLAN_CACHE_STAT;

-- 查看特定SQL的计划缓存
SELECT 
    SQL_ID,
    PLAN_ID,
    SCHEMA_VERSION,
    MERGED_VERSION,
    PLAN_TYPE,
    PLAN_SIZE,
    FIRST_LOAD_TIME,
    LAST_ACTIVE_TIME,
    AVG_EXE_TIME,
    SLOWEST_EXE_TIME,
    HIT_COUNT
FROM 
    oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
WHERE 
    SQL_ID = 'your_sql_id';

5.2 计划缓存清理

-- 清理特定SQL的执行计划
ALTER SYSTEM FLUSH PLAN CACHE GLOBAL SQL_ID = 'your_sql_id';

-- 清理特定租户的所有计划缓存
ALTER SYSTEM FLUSH PLAN CACHE TENANT = tenant_name;

-- 全局清理计划缓存
ALTER SYSTEM FLUSH PLAN CACHE GLOBAL;

六、自动化诊断脚本

6.1 CPU热点SQL自动诊断

DELIMITER //
CREATE PROCEDURE diagnose_cpu_intensive_sql()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_sql_id VARCHAR(64);
    DECLARE v_plan_id BIGINT;
    DECLARE v_cpu_time BIGINT;
    
    DECLARE cur CURSOR FOR
        SELECT SQL_ID, PLAN_ID, AVG(CPU_TIME) as avg_cpu
        FROM oceanbase.GV$OB_SQL_AUDIT
        WHERE REQUEST_TIME > DATE_SUB(NOW(), INTERVAL 1 HOUR)
        GROUP BY SQL_ID, PLAN_ID
        HAVING AVG(CPU_TIME) > 100000
        ORDER BY avg_cpu DESC
        LIMIT 10;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时结果表
    CREATE TEMPORARY TABLE IF NOT EXISTS cpu_diagnosis_result (
        sql_id VARCHAR(64),
        plan_id BIGINT,
        issue_type VARCHAR(100),
        recommendation TEXT,
        priority INT
    );
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_sql_id, v_plan_id, v_cpu_time;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 检查是否存在全表扫描
        IF EXISTS (
            SELECT 1 FROM oceanbase.GV$OB_SQL_AUDIT
            WHERE SQL_ID = v_sql_id AND TABLE_SCAN = 1
        ) THEN
            INSERT INTO cpu_diagnosis_result VALUES
            (v_sql_id, v_plan_id, 'FULL_TABLE_SCAN', 
             'Consider adding appropriate index', 1);
        END IF;
        
        -- 检查是否存在大量排序
        IF EXISTS (
            SELECT 1 FROM oceanbase.GV$OB_SQL_AUDIT
            WHERE SQL_ID = v_sql_id AND SORTS > 1000
        ) THEN
            INSERT INTO cpu_diagnosis_result VALUES
            (v_sql_id, v_plan_id, 'EXCESSIVE_SORTING', 
             'Consider adding index on ORDER BY columns', 2);
        END IF;
        
    END LOOP;
    
    CLOSE cur;
    
    -- 输出诊断结果
    SELECT * FROM cpu_diagnosis_result ORDER BY priority;
    
    DROP TEMPORARY TABLE cpu_diagnosis_result;
END//
DELIMITER ;

6.2 执行计划对比分析

-- 对比同一SQL不同执行计划的性能
WITH plan_comparison AS (
    SELECT 
        SQL_ID,
        PLAN_ID,
        COUNT(*) AS exec_count,
        AVG(ELAPSED_TIME) AS avg_elapsed,
        AVG(CPU_TIME) AS avg_cpu,
        AVG(LOGICAL_READS) AS avg_reads,
        AVG(ROW_PROCESSED) AS avg_rows
    FROM 
        oceanbase.GV$OB_SQL_AUDIT
    WHERE 
        SQL_ID = 'your_sql_id'
        AND REQUEST_TIME > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    GROUP BY 
        SQL_ID, PLAN_ID
)
SELECT 
    p1.PLAN_ID AS plan_id,
    p1.exec_count,
    ROUND(p1.avg_elapsed/1000, 2) AS avg_elapsed_ms,
    ROUND(p1.avg_cpu/1000, 2) AS avg_cpu_ms,
    p1.avg_reads,
    p1.avg_rows,
    CASE 
        WHEN p1.avg_cpu < (SELECT MIN(avg_cpu) FROM plan_comparison) * 1.1 
        THEN 'OPTIMAL'
        WHEN p1.avg_cpu < (SELECT AVG(avg_cpu) FROM plan_comparison)
        THEN 'GOOD'
        ELSE 'POOR'
    END AS performance_rating
FROM 
    plan_comparison p1
ORDER BY 
    p1.avg_cpu;

七、性能调优最佳实践

7.1 建立性能基线

-- 创建性能基线表
CREATE TABLE sql_performance_baseline (
    sql_id VARCHAR(64),
    plan_id BIGINT,
    baseline_date DATE,
    avg_cpu_ms DECIMAL(10,2),
    avg_elapsed_ms DECIMAL(10,2),
    avg_logical_reads BIGINT,
    p95_elapsed_ms DECIMAL(10,2),
    p99_elapsed_ms DECIMAL(10,2),
    daily_executions BIGINT,
    PRIMARY KEY (sql_id, plan_id, baseline_date)
);

-- 定期收集基线数据
INSERT INTO sql_performance_baseline
SELECT 
    SQL_ID,
    PLAN_ID,
    CURDATE(),
    AVG(CPU_TIME)/1000,
    AVG(ELAPSED_TIME)/1000,
    AVG(LOGICAL_READS),
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ELAPSED_TIME)/1000,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY ELAPSED_TIME)/1000,
    COUNT(*)
FROM 
    oceanbase.GV$OB_SQL_AUDIT
WHERE 
    REQUEST_TIME >= CURDATE()
    AND REQUEST_TIME < CURDATE() + INTERVAL 1 DAY
GROUP BY 
    SQL_ID, PLAN_ID;

7.2 性能监控告警

-- 检测性能退化
SELECT 
    current.sql_id,
    current.plan_id,
    baseline.avg_cpu_ms AS baseline_cpu,
    current.avg_cpu_ms AS current_cpu,
    ROUND((current.avg_cpu_ms - baseline.avg_cpu_ms) / baseline.avg_cpu_ms * 100, 2) AS degradation_pct
FROM (
    SELECT 
        SQL_ID,
        PLAN_ID,
        AVG(CPU_TIME)/1000 AS avg_cpu_ms
    FROM 
        oceanbase.GV$OB_SQL_AUDIT
    WHERE 
        REQUEST_TIME > DATE_SUB(NOW(), INTERVAL 1 HOUR)
    GROUP BY 
        SQL_ID, PLAN_ID
) current
JOIN sql_performance_baseline baseline
    ON current.sql_id = baseline.sql_id
    AND current.plan_id = baseline.plan_id
    AND baseline.baseline_date = CURDATE() - INTERVAL 1 DAY
WHERE 
    current.avg_cpu_ms > baseline.avg_cpu_ms * 1.5  -- 性能退化超过50%
ORDER BY 
    degradation_pct DESC;

八、参考资源

九、总结

  1. 监控先行：建立完善的SQL性能监控体系
  2. 基线管理：定期收集性能基线，及时发现退化
  3. 计划分析：深入理解执行计划，找出性能瓶颈
  4. 索引优化：合理创建和维护索引
  5. SQL改写：掌握常见的SQL优化模式
  6. 并行执行：充分利用OceanBase的并行处理能力
  7. 持续优化：建立持续的性能优化机制
# oceanbase # sql # 性能优化
Logo
深开鸿 技术专区

更多推荐

cover

智慧隧道进化史

avatar 深开鸿 技术专区

OpenHarmony+6G查询助手创新实践

针对“开源鸿蒙社区月度优秀评选｜多方向有奖评优、高校开发者速冲！赛道一：系统与技术创新赛道腾讯元器信息技术6G查询助手”这一主题，核心是理解并参与该评选活动，特别是“系统与技术创新赛道”中与“腾讯元器信息技术6G查询助手”相关的项目。以下是如何基于开源鸿蒙（OpenHarmony）进行系统与技术创新的关键路径和示例。

avatar 深开鸿 技术专区
cover

开源鸿蒙跨平台直播｜ 快手KRN鸿蒙适配与性能优化

avatar 深开鸿 技术专区