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;