一些有用的优化Oracle下SQL的语句

SELECT 
    sql_id,
    sql_text,
    executions,
    elapsed_time / 1000000 AS total_seconds,
    elapsed_time / 1000000 / DECODE(executions, 0, 1, executions) AS avg_seconds,
    buffer_gets,
    disk_reads,
    sharable_mem / 1024 AS mem_kb
FROM 
    v$sql
WHERE 
    elapsed_time / 1000000 / DECODE(executions, 0, 1, executions) > 1  -- 仅显示平均执行时间超过1秒的SQL
ORDER BY 
    avg_seconds DESC
FETCH FIRST 10 ROWS ONLY;

SELECT 
    sql_id,
    sql_text,
    executions,
    cpu_time / 1000000 AS total_cpu_seconds,
    cpu_time / 1000000 / DECODE(executions, 0, 1, executions) AS avg_cpu_seconds,
    elapsed_time / 1000000 AS total_elapsed_seconds,
    buffer_gets,
    disk_reads
FROM 
    v$sql
WHERE 
    cpu_time > 0  -- 排除未消耗CPU的SQL
    AND executions > 0  -- 排除未执行的SQL
ORDER BY 
    total_cpu_seconds DESC
FETCH FIRST 10 ROWS ONLY; 


SELECT sql_id, sql_text
FROM v$sql
WHERE sql_id = '10gywwmwk6xfy'


EXPLAIN PLAN FOR
你的SQL


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


CREATE INDEX idx_zjhm_length ON TB_CHSS_GRJKDA (LENGTH(ZJHM));

SELECT 
    s.sid, 
    s.serial#, 
    s.username, 
    s.osuser, 
    s.machine, 
    s.program, 
    l.type, 
    l.lmode, 
    l.request, 
    l.id1, 
    l.id2
FROM 
    v$session s 
JOIN 
    v$lock l ON s.sid = l.sid
WHERE 
    l.id1 IN (SELECT object_id FROM dba_objects WHERE object_name = 'TB_MZ_CFZB')
    AND s.status = 'ACTIVE';

ALTER SYSTEM KILL SESSION '1765,17204';


SELECT 
    index_name,
    table_name,
    leaf_blocks,
    distinct_keys,
    CASE 
        WHEN leaf_blocks = 0 THEN 0
        ELSE (leaf_blocks - distinct_keys) / leaf_blocks 
    END AS fragmentation_ratio,
    CASE 
        WHEN leaf_blocks = 0 THEN '空索引'
        WHEN (leaf_blocks - distinct_keys) / leaf_blocks > 0.2 THEN '高碎片率'
        ELSE '正常'
    END AS index_status
FROM 
    dba_indexes
WHERE 
    owner = 'dede'
ORDER BY 
    fragmentation_ratio DESC;