# 1. 分析SQL执行计划
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM fgedu.orders WHERE customer_id = 123;

Explained.

# 2. 查看执行计划
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
—————————————-
Plan hash value: 1234567890

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 10 | 100 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ORDERS | 10 | 100 | 5 (0)| 00:00:01 |
| 2 | INDEX RANGE SCAN | IDX_ORDERS_CUSTOMER_ID| 10 | | 2 (0)| 00:00:01 |
——————————————————————————-

# 3. 分析执行计划的关键点
– 操作类型：查看执行计划中的操作类型，如TABLE ACCESS、INDEX SCAN等
– 访问路径：查看数据的访问路径，如全表扫描、索引扫描等
– 成本：查看执行计划的成本，评估SQL语句的性能
– 行数：查看预计返回的行数，评估SQL语句的效率
– 时间：查看预计执行时间，评估SQL语句的性能

# 4. 优化执行计划
– 添加索引：为频繁查询的列添加索引
– 重写SQL：重写性能差的SQL语句
– 调整参数：调整数据库参数，优化执行计划
– 使用提示：使用SQL提示，指导优化器选择正确的执行计划

# 1. 创建SQL调优任务
SQL> DECLARE
2 l_task_name VARCHAR2(30);
3 BEGIN
4 l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
5 sql_text => ‘SELECT * FROM fgedu.orders WHERE customer_id = 123’,
6 user_name => ‘FGEDU’,
7 scope => ‘COMPREHENSIVE’,
8 time_limit => 60,
9 task_name => ‘tune_order_query’,
10 description => ‘Tune query for orders table’);
11 DBMS_OUTPUT.PUT_LINE(‘Task created: ‘ || l_task_name);
12 END;
13 /

Task created: tune_order_query

# 2. 执行SQL调优任务
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘tune_order_query’);

PL/SQL procedure successfully completed.

# 3. 查看调优建议
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘tune_order_query’) FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNE_ORDER_QUERY’)
——————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : tune_order_query
Tuning Task Owner : FGEDU
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 2026-04-01 10:00:00
Completed at : 2026-04-01 10:00:10

——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-

1- Index Finding (see plan 1 in explain plans section)
————————————————–
The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 95.0%)
—————————————-
– Consider running the Access Advisor to improve the physical schema design
– Create the recommended indices:
CREATE INDEX FGEDU.IDX_ORDERS_CUSTOMER_ID ON FGEDU.ORDERS(CUSTOMER_ID);

Rationale
———
The statement is using a full table scan for this query. Creating the
recommended index would use an index range scan which would improve
performance.

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original
———–
Plan hash value: 1234567890

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 10 | 100 | 500 (5)| 00:00:01 |
| 1 | TABLE ACCESS FULL| ORDERS | 10 | 100 | 500 (5)| 00:00:01 |
——————————————————————————-

2- Using Recommended Index
————————–
Plan hash value: 9876543210

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 10 | 100 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ORDERS | 10 | 100 | 5 (0)| 00:00:01 |
| 2 | INDEX RANGE SCAN | IDX_ORDERS_CUSTOMER_ID| 10 | | 2 (0)| 00:00:01 |
——————————————————————————-

# 4. 实施调优建议
SQL> CREATE INDEX idx_orders_customer_id ON fgedu.orders(customer_id);

Index created.

# 5. 验证调优结果
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.orders WHERE customer_id = 123;

Elapsed: 00:00:00.01

# 对比优化前后的性能
# 优化前：Elapsed: 00:00:00.50
# 优化后：Elapsed: 00:00:00.01

# 1. 生成AWR报告
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DBID DB_NAME INST_NUM INST_NAME
——— ——— ———- ———-
12345678 FGEDUDB 1 fgdb1

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for num_days: 1

Listing the last day’s Completed Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Snap Id Snap Time Sessions Curs/Sess
——– ——————- ——– ———
100 2026-04-01 00:00:00 10 5
101 2026-04-01 01:00:00 12 6
102 2026-04-01 02:00:00 15 7
103 2026-04-01 03:00:00 18 8
104 2026-04-01 04:00:00 20 9
105 2026-04-01 05:00:00 25 10
106 2026-04-01 06:00:00 30 12
107 2026-04-01 07:00:00 35 15
108 2026-04-01 08:00:00 40 20
109 2026-04-01 09:00:00 45 25
110 2026-04-01 10:00:00 50 30

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 100
Enter value for end_snap: 110

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_100_110.html. To use this name, press to continue, otherwise enter an alternative.
Enter value for report_name: /tmp/awr_report.html

# 2. 分析AWR报告
– 数据库负载：查看数据库的负载情况，如CPU使用率、I/O等待等
– 性能指标：查看关键性能指标，如响应时间、吞吐量等
– 等待事件：查看系统的等待事件，找出性能瓶颈
– SQL统计：查看SQL语句的执行情况，找出性能差的SQL
– 资源使用：查看系统资源的使用情况，如内存、I/O等

# 3. 基于AWR报告进行优化
– 优化SQL语句：根据AWR报告中的SQL统计，优化性能差的SQL语句
– 调整参数：根据AWR报告中的性能指标，调整数据库参数
– 优化索引：根据AWR报告中的SQL执行情况，优化索引
– 调整资源：根据AWR报告中的资源使用情况，调整系统资源

# 1. 查看系统状态
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%CPU%’;

# 2. 查看会话状态
SQL> SELECT * FROM v$session WHERE status = ‘ACTIVE’;

# 3. 查看等待事件
SQL> SELECT * FROM v$session_wait WHERE event NOT LIKE ‘SQL*Net%’;

# 4. 查看SQL执行情况
SQL> SELECT * FROM v$sql WHERE executions > 100 ORDER BY elapsed_time DESC;

# 5. 查看索引使用情况
SQL> SELECT * FROM v$object_usage WHERE index_name IS NOT NULL;

# 6. 查看表空间使用情况
SQL> SELECT * FROM dba_tablespaces;

# 7. 查看数据文件使用情况
SQL> SELECT * FROM dba_data_files;

# 8. 查看内存使用情况
SQL> SELECT * FROM v$sga;
SQL> SELECT * FROM v$pga_target_advice;

# 9. 查看I/O使用情况
SQL> SELECT * FROM v$iostat_file;

# 10. 查看网络使用情况
SQL> SELECT * FROM v$net_service_stats;

# 调优工具监控脚本

# 输出文件
output_file=”/tmp/tuning_tool_monitor_$(date +%Y%m%d_%H%M%S).log”

echo “开始监控调优工具” > ${output_file}
echo “监控时间: $(date)” >> ${output_file}
echo “” >> ${output_file}

# 查看系统状态
echo “===== 系统状态 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM v\$sysstat WHERE name LIKE ‘%CPU%’;
EXIT;
EOF
echo “” >> ${output_file}

# 查看会话状态
echo “===== 会话状态 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM v\$session WHERE status = ‘ACTIVE’;
EXIT;
EOF
echo “” >> ${output_file}

# 查看等待事件
echo “===== 等待事件 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM v\$session_wait WHERE event NOT LIKE ‘SQL*Net%’;
EXIT;
EOF
echo “” >> ${output_file}

# 查看SQL执行情况
echo “===== SQL执行情况 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM v\$sql WHERE executions > 100 ORDER BY elapsed_time DESC;
EXIT;
EOF
echo “” >> ${output_file}

# 查看索引使用情况
echo “===== 索引使用情况 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM v\$object_usage WHERE index_name IS NOT NULL;
EXIT;
EOF
echo “” >> ${output_file}

# 查看表空间使用情况
echo “===== 表空间使用情况 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM dba_tablespaces;
EXIT;
EOF
echo “” >> ${output_file}

# 查看内存使用情况
echo “===== 内存使用情况 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM v\$sga;
EXIT;
EOF
echo “” >> ${output_file}

# 查看I/O使用情况
echo “===== I/O使用情况 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM v\$iostat_file;
EXIT;
EOF
echo “” >> ${output_file}

echo “调优工具监控完成，结果保存至：${output_file}” >> ${output_file}
echo “调优工具监控完成，结果保存至：${output_file}”

# 检查性能异常
slow_sql_count=$(sqlplus -s / as sysdba << EOF SET HEAD OFF SET FEEDBACK OFF SELECT COUNT(*) FROM v\$sql WHERE elapsed_time > 1000000000 AND executions > 10;
EXIT;
EOF
)

if [ $slow_sql_count -gt 0 ]; then
echo “发现慢SQL！数量: $slow_sql_count”
# 发送告警
echo “SQL告警：发现 $slow_sql_count 个慢SQL” | mail -s “YashanDB调优工具告警” admin@fgedu.net.cn
fi

# 1. 发现问题
– 通过监控系统发现调优工具问题
– 通过告警信息发现调优工具问题
– 通过用户反馈发现调优工具问题

# 2. 分析问题
– 查看调优工具的日志
– 检查调优工具的配置
– 测试调优工具的功能
– 分析调优工具的输出

# 3. 确定原因
– 配置问题：调优工具的配置不正确
– 权限问题：调优工具的权限不足
– 资源问题：调优工具的资源不足
– 兼容性问题：调优工具与数据库版本不兼容
– 网络问题：调优工具的网络连接问题

# 4. 采取处理措施
– 调整配置：调整调优工具的配置
– 授予权限：授予调优工具所需的权限
– 增加资源：增加调优工具的资源
– 升级版本：升级调优工具的版本
– 修复网络：修复调优工具的网络连接

# 5. 验证处理结果
– 测试调优工具的功能
– 检查调优工具的输出
– 确认问题是否解决

# 6. 预防措施
– 定期维护：定期维护调优工具
– 备份配置：备份调优工具的配置
– 监控状态：监控调优工具的状态
– 及时更新：及时更新调优工具的版本

# 1. 日志分析工具
– alert.log：数据库告警日志
– trace files：跟踪文件
– listener.log：监听器日志
– audit logs：审计日志

# 2. 诊断工具
– DBMS_SYSTEM：系统诊断
– DBMS_TRACE：跟踪诊断
– DBMS_MONITOR：监控诊断
– Enterprise Manager：企业管理器

# 3. 操作系统工具
– top：查看系统资源使用情况
– iostat：查看I/O使用情况
– vmstat：查看虚拟内存使用情况
– netstat：查看网络使用情况
– ps：查看进程状态

# 4. 第三方工具
– Toad：数据库管理工具
– SQL Developer：SQL开发工具
– Navicat：数据库管理工具
– Prometheus + Grafana：监控和可视化工具