— 生成SQL健康检查报告
SET LINESIZE 100
SET PAGESIZE 100
SPOOL sql_health_check_report.txt

SELECT ‘SQL Health Check Report’ AS report_title FROM dual;SELECT ‘Generated on: ‘ || TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) AS generation_time FROM dual;– 高频执行的SQL语句
SELECT ‘High Frequency SQL Statements:’ AS section FROM dual;SELECT sql_id, sql_text, executions, elapsed_time/1000000 AS elapsed_seconds
FROM v$sql
WHERE executions > 100
ORDER BY executions DESC;– 高资源消耗的SQL语句
SELECT ‘High Resource Consumption SQL Statements:’ AS section FROM dual;SELECT sql_id, sql_text, elapsed_time/1000000 AS elapsed_seconds,
buffer_gets, disk_reads
FROM v$sql
WHERE elapsed_time > 1000000
ORDER BY elapsed_time DESC;– 全表扫描的SQL语句
SELECT ‘Full Table Scan SQL Statements:’ AS section FROM dual;SELECT sql_id, sql_text, executions, buffer_gets
FROM v$sql
WHERE sql_text LIKE ‘%FULL(%’ AND executions > 10
ORDER BY buffer_gets DESC;SPOOL OFF;

— 1. 检查高频执行的SQL语句
SELECT sql_id, sql_text, executions, elapsed_time/1000000 AS elapsed_seconds,
buffer_gets, disk_reads
FROM v$sql
WHERE executions > 100
ORDER BY executions DESC;– 2. 检查高资源消耗的SQL语句
SELECT sql_id, sql_text, elapsed_time/1000000 AS elapsed_seconds,
buffer_gets, disk_reads, rows_processed
FROM v$sql
WHERE elapsed_time > 1000000
ORDER BY elapsed_time DESC;– 3. 检查执行计划异常的SQL语句
SELECT sql_id, plan_hash_value, executions, elapsed_time/1000000 AS elapsed_seconds
FROM v$sql
WHERE plan_hash_value IN (
SELECT plan_hash_value
FROM v$sql
GROUP BY plan_hash_value
HAVING COUNT(*) > 1
)
ORDER BY executions DESC;– 4. 检查索引使用情况
SELECT table_name, index_name, index_type, status
FROM user_indexes
WHERE status != ‘VALID’;

SQL> SELECT sql_id, sql_text, executions, elapsed_time/1000000 AS elapsed_seconds
2 FROM v$sql
3 WHERE executions > 100
4 ORDER BY executions DESC;SQL_ID SQL_TEXT EXECUTIONS ELAPSED_SECONDS
————- ———————————————————— ———- —————
abc123 SELECT * FROM employees WHERE department_id = :1 500 2.5
def456 SELECT * FROM fgsales WHERE sale_date BETWEEN :1 AND :2 350 1.8
ghi789 SELECT region, SUM(amount) FROM fgsales GROUP BY region 200 0.9

SQL> SELECT sql_id, sql_text, elapsed_time/1000000 AS elapsed_seconds
2 FROM v$sql
3 WHERE elapsed_time > 1000000
4 ORDER BY elapsed_time DESC;SQL_ID SQL_TEXT ELAPSED_SECONDS
————- ———————————————————— —————
jkl012 SELECT * FROM fgsales WHERE amount > 1000 15.6
mno345 SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id 8.2
pqr678 SELECT region, SUM(amount) FROM fgsales GROUP BY region 5.1

— 1. 分析问题SQL的执行计划
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => ‘sql_id_value’,
format => ‘ALLSTATS LAST’
));– 2. 使用SQL调优顾问分析问题
DECLARE
l_task_id VARCHAR2(30);BEGIN
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => ‘sql_id_value’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘tune_problem_sql’,
description => ‘Tune problem SQL’
);DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_id);DBMS_OUTPUT.PUT_LINE(‘Task ID: ‘ || l_task_id);END;/– 3. 查看调优建议
SELECT * FROM table(DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name => ‘tune_problem_sql’
));– 4. 实施优化措施（如创建索引）
CREATE INDEX idx_sales_amount ON fgsales(amount);

— 1. 检查SQL语句的执行历史
SELECT * FROM dba_hist_sqlstat
WHERE sql_id = ‘sql_id_value’
ORDER BY snap_id DESC;– 2. 检查统计信息状态
SELECT table_name, last_analyzed
FROM user_tables
WHERE table_name IN (‘SALES’, ‘EMPLOYEES’);– 3. 检查索引状态
SELECT index_name, status, last_analyzed
FROM user_indexes
WHERE table_name = ‘SALES’;– 4. 检查系统资源使用情况
SELECT * FROM v$system_event
WHERE event LIKE ‘%wait%’
ORDER BY total_waits DESC;