Oracle教程FG078-SQL调优基础

Part02-生产环境规划与建议

2.1 SQL调优准备工作

SQL调优的准备工作：

收集统计信息：确保表和索引的统计信息是最新的
分析执行计划：了解SQL语句的执行计划
监控性能指标：监控SQL语句的执行时间、资源消耗等指标
识别问题SQL：识别执行时间长、资源消耗大的SQL语句
了解业务逻辑：了解SQL语句的业务逻辑，以便更好地优化

风哥提示：在生产环境中，SQL调优前应做好充分的准备工作，确保调优的有效性。

2.2 SQL调优工具

常用的SQL调优工具：

EXPLAIN PLAN：查看SQL语句的执行计划
SQL*Plus AUTOTRACE：查看SQL语句的执行计划和统计信息
SQL Tuning Advisor：自动分析和优化SQL语句
SQL Access Advisor：分析和优化索引设计
AWR报告：分析数据库的整体性能
ASH报告：分析数据库的活动会话历史

更多学习教程公众号风哥教程itpux_com

2.3 SQL调优最佳实践

SQL调优的最佳实践：

使用绑定变量：减少硬解析，提高游标共享率
优化WHERE子句：将最选择性的条件放在前面
避免使用SELECT *：只选择需要的列
使用适当的索引：为经常查询的列创建索引
避免在WHERE子句中使用函数：可能导致索引失效
优化JOIN操作：选择合适的JOIN类型和顺序
避免使用ORDER BY和GROUP BY：如果不需要
使用并行执行：对于大型查询

from oracle:www.itpux.com

Part03-生产环境项目实施方案

在生产环境中进行SQL调优的实施方案：

需求分析：确定SQL调优的目标和范围。
问题识别：识别执行时间长、资源消耗大的SQL语句。
执行计划分析：分析SQL语句的执行计划，找出性能瓶颈。
调优方案设计：设计SQL调优方案，如修改SQL语句、添加索引等。
方案实施：实施调优方案，如修改SQL语句、创建索引等。
性能测试：测试调优后的性能，验证调优效果。
部署上线：将调优后的SQL语句部署到生产环境。
监控维护：监控SQL语句的执行情况，及时调整。

Part04-生产案例与实战讲解

4.1 基本SQL调优实战

示例：基本SQL调优

— 创建示例表
CREATE TABLE fgedu_employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
emp_dept VARCHAR2(50),
emp_salary NUMBER,
hire_date DATE
);/– 插入示例数据
INSERT INTO fgedu_employees VALUES (1001, ‘风哥1号’, ‘技术部’, 5000, SYSDATE – 365);INSERT INTO fgedu_employees VALUES (1002, ‘风哥2号’, ‘市场部’, 6000, SYSDATE – 180);INSERT INTO fgedu_employees VALUES (1003, ‘风哥3号’, ‘技术部’, 5500, SYSDATE – 90);INSERT INTO fgedu_employees VALUES (1004, ‘赵六’, ‘市场部’, 6500, SYSDATE – 60);INSERT INTO fgedu_employees VALUES (1005, ‘孙七’, ‘财务部’, 5800, SYSDATE – 30);– 插入更多数据
BEGIN
FOR i IN 1006 .. 10000 LOOP
INSERT INTO fgedu_employees VALUES (
i,
‘员工’ || TO_CHAR(i),
CASE MOD(i, 3)
WHEN 0 THEN ‘技术部’
WHEN 1 THEN ‘市场部’
ELSE ‘财务部’
END,
5000 + MOD(i, 2000),
SYSDATE – MOD(i, 365)
);END LOOP;COMMIT;END;/– 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYS’, ‘FGEDU_EMPLOYEES’);– 原始SQL语句（性能较差）
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 优化后的SQL语句
SELECT emp_id, emp_name, emp_salary FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 查看执行计划
EXPLAIN PLAN FOR SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 查看优化后SQL的执行计划
EXPLAIN PLAN FOR SELECT emp_id, emp_name, emp_salary FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND
emp_salary > 5000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

— 原始SQL语句执行计划
Plan hash value: 3883868984

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 3333 | 232K| 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FGEDU_EMPLOYEES | 3333 | 232K| 15 (0)| 00:00:01 |
——————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMP_DEPT”=’技术部’ AND “EMP_SALARY”>5000)

— 优化后SQL语句执行计划
Plan hash value: 3883868984

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 3333 | 73326 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FGEDU_EMPLOYEES | 3333 | 73326 | 15 (0)| 00:00:01 |
——————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMP_DEPT”=’技术部’ AND “EMP_SALARY”>5000)

更多视频教程www.fgedu.net.cn

4.2 索引优化实战

示例：索引优化

— 创建索引前的SQL执行
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 查看执行计划
EXPLAIN PLAN FOR SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 创建索引
CREATE INDEX idx_fgedu_emp_dept_salary ON fgedu_employees(emp_dept, emp_salary);– 创建索引后的SQL执行
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 查看执行计划
EXPLAIN PLAN FOR SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 测试不同条件的SQL
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’;EXPLAIN PLAN FOR SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);SELECT * FROM fgedu_employees WHERE emp_salary > 5000;EXPLAIN PLAN FOR SELECT * FROM fgedu_employees WHERE emp_salary > 5000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

— 创建索引前的执行计划
Plan hash value: 3883868984

— 创建索引后的执行计划
Plan hash value: 3711770936

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 3333 | 232K| 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_EMPLOYEES | 3333 | 232K| 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_EMP_DEPT_SALARY | 3333 | | 2 (0)| 00:00:01 |
—————————————————————————————–

— 只使用emp_dept条件的执行计划
Plan hash value: 3711770936

— 只使用emp_salary条件的执行计划
Plan hash value: 3883868984

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 5000 | 351K| 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FGEDU_EMPLOYEES | 5000 | 351K| 15 (0)| 00:00:01 |
——————————————————————————-

学习交流加群风哥微信: itpux-com

4.3 执行计划分析实战

示例：执行计划分析

— 创建另一个表
CREATE TABLE fgedu_departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50),
dept_location VARCHAR2(100)
);/– 插入数据
INSERT INTO fgedu_departments VALUES (1, ‘技术部’, ‘北京’);INSERT INTO fgedu_departments VALUES (2, ‘市场部’, ‘上海’);INSERT INTO fgedu_departments VALUES (3, ‘财务部’, ‘广州’);COMMIT;/– 创建索引
CREATE INDEX idx_fgedu_emp_dept ON fgedu_employees(emp_dept);– 执行JOIN查询
SELECT e.emp_id, e.emp_name, e.emp_salary, d.dept_name, d.dept_location
FROM fgedu_employees e
JOIN fgedu_departments d ON e.emp_dept = d.dept_name
WHERE e.emp_salary > 6000;– 查看执行计划
EXPLAIN PLAN FOR
SELECT e.emp_id, e.emp_name, e.emp_salary, d.dept_name, d.dept_location
FROM fgedu_employees e
JOIN fgedu_departments d ON e.emp_dept = d.dept_name
WHERE e.emp_salary > 6000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 优化JOIN查询
SELECT /*+ USE_HASH(e d) */ e.emp_id, e.emp_name, e.emp_salary, d.dept_name, d.dept_location
FROM fgedu_employees e
JOIN fgedu_departments d ON e.emp_dept = d.dept_name
WHERE e.emp_salary > 6000;– 查看优化后的执行计划
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(e d) */ e.emp_id, e.emp_name, e.emp_salary, d.dept_name, d.dept_location
FROM fgedu_employees e
JOIN fgedu_departments d ON e.emp_dept = d.dept_name
WHERE e.emp_salary > 6000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

— 原始执行计划
Plan hash value: 3393241698

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 1667 | 76682 | 17 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1667 | 76682 | 17 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | FGEDU_EMPLOYEES | 1667 | 53344 | 15 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| FGEDU_DEPARTMENTS | 1 | 14 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0014002 | 1 | | 0 (0)| 00:00:01 |
——————————————————————————————–

— 优化后的执行计划
Plan hash value: 1595739390

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 1667 | 76682 | 17 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1667 | 76682 | 17 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | FGEDU_DEPARTMENTS | 3 | 42 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | FGEDU_EMPLOYEES | 1667 | 53344 | 15 (0)| 00:00:01 |
——————————————————————————————–

学习交流加群风哥QQ113257174

Part05-风哥经验总结与分享

风哥提示：在进行SQL调优时，应注意以下几点：

收集统计信息：确保表和索引的统计信息是最新的，这对于CBO优化器选择最优执行计划至关重要。
分析执行计划：了解SQL语句的执行计划，找出性能瓶颈。
使用绑定变量：减少硬解析，提高游标共享率，降低CPU消耗。
优化WHERE子句：将最选择性的条件放在前面，提高查询效率。
使用适当的索引：为经常查询的列创建索引，但不要创建过多索引，以免影响DML操作的性能。
避免在WHERE子句中使用函数：这可能导致索引失效，从而进行全表扫描。
优化JOIN操作：选择合适的JOIN类型和顺序，对于小表使用NESTED LOOPS，对于大表使用HASH JOIN。
避免使用SELECT *：只选择需要的列，减少数据传输和内存消耗。
使用并行执行：对于大型查询，使用并行执行可以提高性能。
测试充分：在生产环境中实施调优方案前，应进行充分的测试，确保调优效果。

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

风哥提示：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。学习交流加群风哥QQ113257174

生产环境建议：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。

风哥提示：更多学习教程公众号风哥教程itpux_com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html