Oracle教程FG348-硬解析

本文档风哥主要介绍Oracle数据库硬解析（Hard Parse）相关知识，包括硬解析的概念、过程、影响、规划、配置、管理、监控、优化等内容，由风哥教程参考Oracle官方文档Performance内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 硬解析的概念

Oracle数据库硬解析（Hard Parse）是指当SQL语句首次执行或无法在共享池中找到匹配的游标时，数据库需要对该SQL语句进行完整的解析过程，包括语法分析、语义分析、优化器生成执行计划等步骤。硬解析会消耗大量的CPU和内存资源，影响数据库性能。更多视频教程www.fgedu.net.cn

硬解析的特点：

完整的SQL解析过程
包括语法分析、语义分析、优化器处理
生成新的执行计划
消耗大量CPU和内存资源
影响数据库性能

1.2 硬解析的过程

Oracle数据库硬解析的过程：

语法分析：检查SQL语句的语法是否正确
语义分析：检查SQL语句引用的对象是否存在、权限是否正确
优化器处理：优化器根据统计信息生成最优执行计划
游标生成：在共享池中生成新的游标
执行计划存储：将执行计划存储在共享池中

1.3 硬解析的影响

Oracle数据库硬解析的影响：

CPU使用率增加：硬解析消耗大量CPU资源
内存使用增加：硬解析消耗共享池内存
响应时间增加：SQL语句执行时间增加
系统性能下降：大量的硬解析会导致系统性能下降
库缓存争用：硬解析会导致库缓存锁和库缓存钉住争用

风哥提示：硬解析是Oracle数据库性能问题的常见原因之一，通过分析硬解析，可以识别数据库性能瓶颈，从而进行针对性的优化。

Part02-生产环境规划与建议

2.1 硬解析规划

Oracle数据库硬解析规划要点：

# 硬解析规划步骤
1. 分析系统架构
2. 评估SQL执行模式
3. 分析硬解析情况
4. 设计硬解析优化策略
5. 优化共享池配置
6. 配置相关参数
7. 测试和验证
8. 监控和优化

# 适用场景
– 高并发OLTP系统
– 大量硬解析系统
– 共享池争用严重系统
– 硬解析等待严重系统

# 不适用场景
– 只读系统
– 低并发系统
– 批处理系统

2.2 硬解析设计

Oracle数据库硬解析设计建议：

# 硬解析设计原则
– 基于系统规模设计
– 基于SQL执行模式设计
– 最小化硬解析
– 最大化SQL重用
– 合理配置参数

# 硬解析优化策略
– 使用绑定变量
– 启用游标共享
– 优化SQL语句
– 收集统计信息
– 使用SQL计划基线

# 设计步骤
1. 分析系统需求
2. 评估SQL执行模式
3. 设计硬解析优化策略
4. 优化共享池配置
5. 配置相关参数
6. 测试性能效果
7. 调整配置

2.3 硬解析最佳实践

Oracle数据库硬解析最佳实践：

使用绑定变量：使用绑定变量减少硬解析，提高SQL重用率
启用游标共享：启用游标共享功能，提高SQL重用率
优化SQL语句：提高SQL语句效率，减少硬解析
收集统计信息：定期收集统计信息，提高优化器准确性
使用SQL计划基线：使用SQL计划基线稳定执行计划
监控硬解析：定期监控硬解析情况，及时发现和处理问题

生产环境建议：硬解析规划应基于系统规模和SQL执行模式，使用绑定变量，启用游标共享，优化SQL语句，收集统计信息，使用SQL计划基线，定期监控硬解析情况，确保数据库运行高效。学习交流加群风哥微信: itpux-com

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

3.1 硬解析配置

3.1.1 配置共享池

# 查看当前共享池配置
SQL> SHOW PARAMETER shared_pool_size;

NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_size big integer 0

# 调整共享池大小
SQL> ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

System altered.

# 查看共享池统计信息
SQL> SELECT
pool,
name,
bytes/1024/1024 AS mb
FROM v$sgastat
WHERE pool = ‘shared pool’
AND name IN (‘free memory’, ‘library cache’, ‘sql area’);

POOL NAME MB
———— —————————— —
shared pool free memory 500
shared pool library cache 800
shared pool sql area 600

3.1.2 配置游标共享

# 查看当前游标共享配置
SQL> SHOW PARAMETER cursor_sharing;

NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string EXACT

# 调整游标共享配置
SQL> ALTER SYSTEM SET cursor_sharing = ‘FORCE’ SCOPE=BOTH;

System altered.

# 查看游标统计信息
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘parse time cpu’, ‘parse time elapsed’);

NAME VALUE
—————————— ———-
parse count (total) 100000
parse count (hard) 5000
parse time cpu 10000
parse time elapsed 15000

# 计算硬解析率
SQL> SELECT
(hard_parse / total_parse) * 100 AS hard_parse_ratio
FROM (
SELECT
value AS hard_parse
FROM v$sysstat
WHERE name = ‘parse count (hard)’
),
(
SELECT
value AS total_parse
FROM v$sysstat
WHERE name = ‘parse count (total)’
);

HARD_PARSE_RATIO
—————-
5

3.1.3 配置会话游标缓存

# 查看当前会话游标缓存配置
SQL> SHOW PARAMETER session_cached_cursors;

NAME TYPE VALUE
———————————— ———– ——————————
session_cached_cursors integer 50

# 调整会话游标缓存配置
SQL> ALTER SYSTEM SET session_cached_cursors = 100 SCOPE=BOTH;

System altered.

# 查看会话游标缓存统计信息
SQL> SELECT
statistic_name,
value
FROM v$sysstat
WHERE statistic_name LIKE ‘%session cursor%’;

STATISTIC_NAME VALUE
——————————— ———-
session cursor cache hits 50000
session cursor cache misses 5000

# 计算会话游标缓存命中率
SQL> SELECT
(cache_hits / (cache_hits + cache_misses)) * 100 AS cache_hit_ratio
FROM (
SELECT
value AS cache_hits
FROM v$sysstat
WHERE name = ‘session cursor cache hits’
),
(
SELECT
value AS cache_misses
FROM v$sysstat
WHERE name = ‘session cursor cache misses’
);

CACHE_HIT_RATIO
—————
90.9

3.2 硬解析管理

3.2.1 管理硬解析

# 查看硬解析统计信息
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘parse time cpu’, ‘parse time elapsed’);

NAME VALUE
—————————— ———-
parse count (total) 100000
parse count (hard) 5000
parse time cpu 10000
parse time elapsed 15000

# 查看硬解析率
SQL> SELECT
(hard_parse / total_parse) * 100 AS hard_parse_ratio
FROM (
SELECT
value AS hard_parse
FROM v$sysstat
WHERE name = ‘parse count (hard)’
),
(
SELECT
value AS total_parse
FROM v$sysstat
WHERE name = ‘parse count (total)’
);

HARD_PARSE_RATIO
—————-
5

# 查看硬解析等待
SQL> SELECT
event,
wait_class,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE event IN (‘library cache lock’, ‘library cache pin’);

EVENT WAIT_CLASS TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ——————– ———– ———– ————
library cache lock Concurrency 1000 5000 5.0
library cache pin Concurrency 500 2500 5.0

3.2.2 分析硬解析

# 查看硬解析的SQL语句
SQL> SELECT
sql_id,
sql_text,
parse_calls,
executions,
loads,
invalidations
FROM v$sql
WHERE loads > 1
ORDER BY loads DESC
FETCH FIRST 10 ROWS ONLY;

SQL_ID SQL_TEXT PARSE_CALLS EXECUTIONS LOADS INVALIDATIONS
————- ————————————————- ———– ———- —– ————-
abcd1234 SELECT * FROM employees WHERE employee_id = :1 100 1000 5 1
xyz7890 SELECT * FROM departments WHERE department_id = :1 50 500 3 1

# 查看硬解析的会话
SQL> SELECT
s.sid,
s.username,
s.status,
s.sql_id,
s.sql_child_number
FROM v$session s
WHERE s.status = ‘ACTIVE’
AND s.sql_id IS NOT NULL
ORDER BY s.sid;

SID USERNAME STATUS SQL_ID SQL_CHILD_NUMBER
— ————- ——– ————- —————-
123 SCOTT ACTIVE abcd1234 0
124 HR ACTIVE xyz7890 0
125 SYS ACTIVE def12345 0

# 查看硬解析的执行计划
SQL> SELECT
sql_id,
plan_hash_value,
operation,
options,
object_name
FROM v$sql_plan
WHERE sql_id = ‘abcd1234’
ORDER BY id;

SQL_ID PLAN_HASH_VALUE OPERATION OPTIONS OBJECT_NAME
————- —————- —————— ———— ————
abcd1234 1234567890 SELECT STATEMENT
abcd1234 1234567890 TABLE ACCESS BY INDEX ROWID EMPLOYEES
abcd1234 1234567890 INDEX RANGE SCAN EMP_EMP_ID_PK

3.3 硬解析监控

3.3.1 监控硬解析

# 实时监控硬解析
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘parse time cpu’, ‘parse time elapsed’);

NAME VALUE
—————————— ———-
parse count (total) 100000
parse count (hard) 5000
parse time cpu 10000
parse time elapsed 15000

# 使用AWR报告监控硬解析
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

# 输入报告类型：html
# 输入开始快照ID：100
# 输入结束快照ID：101
# 输入报告文件名：awrrpt.html

# 查看AWR报告中的硬解析部分
# Parse Statistics
~~~~~~~~~~~~~~~~~~~
Total Hard Parses: 5,000
Total Parses: 100,000
Hard Parse %: 5.0%

3.3.2 监控硬解析历史

# 查看硬解析历史
SQL> SELECT
h.snap_id,
SUM(h.value) AS total_hard_parse
FROM dba_hist_sysstat h
WHERE h.snap_id BETWEEN 100 AND 101
AND h.stat_name = ‘parse count (hard)’
GROUP BY h.snap_id
ORDER BY h.snap_id;

SNAP_ID TOTAL_HARD_PARSE
——- —————-
100 2500
101 2500

# 查看硬解析率历史
SQL> SELECT
h.snap_id,
ROUND(SUM(CASE WHEN h.stat_name = ‘parse count (hard)’ THEN h.value ELSE 0 END) /
SUM(CASE WHEN h.stat_name = ‘parse count (total)’ THEN h.value ELSE 0 END) * 100, 2) AS hard_parse_ratio
FROM dba_hist_sysstat h
WHERE h.snap_id BETWEEN 100 AND 101
AND h.stat_name IN (‘parse count (hard)’, ‘parse count (total)’)
GROUP BY h.snap_id
ORDER BY h.snap_id;

SNAP_ID HARD_PARSE_RATIO
——- —————-
100 5.0
101 5.0

风哥提示：定期监控硬解析，及时发现和处理问题，确保数据库运行高效。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 硬解析实施案例

在某企业的生产环境中，需要实施硬解析监控和优化，提高数据库性能。

# 案例背景
– 数据库版本：Oracle 19c
– 系统规模：中等规模，日交易量100万
– 问题：数据库性能下降，硬解析严重

# 实施方案
1. 监控硬解析
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘parse time cpu’, ‘parse time elapsed’);

NAME VALUE
—————————— ———-
parse count (total) 100000
parse count (hard) 20000
parse time cpu 40000
parse time elapsed 60000

2. 计算硬解析率
SQL> SELECT
(hard_parse / total_parse) * 100 AS hard_parse_ratio
FROM (
SELECT
value AS hard_parse
FROM v$sysstat
WHERE name = ‘parse count (hard)’
),
(
SELECT
value AS total_parse
FROM v$sysstat
WHERE name = ‘parse count (total)’
);

HARD_PARSE_RATIO
—————-
20

3. 查看硬解析的SQL语句
SQL> SELECT
sql_id,
sql_text,
parse_calls,
executions,
loads
FROM v$sql
WHERE loads > 1
ORDER BY loads DESC
FETCH FIRST 10 ROWS ONLY;

SQL_ID SQL_TEXT PARSE_CALLS EXECUTIONS LOADS
————- ————————————————- ———– ———- —–
abcd1234 SELECT * FROM employees WHERE employee_id = 100 100 1000 10
xyz7890 SELECT * FROM departments WHERE department_id = 10 50 500 5

4. 查看游标共享配置
SQL> SHOW PARAMETER cursor_sharing;

NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string EXACT

5. 优化措施
– 启用游标共享
SQL> ALTER SYSTEM SET cursor_sharing = ‘FORCE’ SCOPE=BOTH;

– 优化SQL语句，使用绑定变量
SQL> CREATE OR REPLACE PROCEDURE get_employee(p_emp_id IN NUMBER) IS
BEGIN
SELECT * FROM employees WHERE employee_id = p_emp_id;
END;
/

– 收集统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘EMPLOYEES’);

– 增加共享池大小
SQL> ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

6. 验证优化效果
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘parse time cpu’, ‘parse time elapsed’);

NAME VALUE
—————————— ———-
parse count (total) 100000
parse count (hard) 2000
parse time cpu 4000
parse time elapsed 6000

# 实施效果
– 硬解析减少90%
– 数据库性能提高50%
– SQL执行时间减少70%
– 系统稳定性提高

4.2 硬解析优化案例

在某金融机构的生产环境中，需要优化硬解析，提高数据库性能。

# 案例背景
– 数据库版本：Oracle 19c
– 系统规模：大规模，日交易量1000万
– 问题：硬解析严重，影响数据库性能

# 优化方案
1. 监控硬解析
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘parse time cpu’, ‘parse time elapsed’);

NAME VALUE
—————————— ———-
parse count (total) 500000
parse count (hard) 150000
parse time cpu 300000
parse time elapsed 450000

HARD_PARSE_RATIO
—————-
30

3. 查看硬解析的SQL语句
SQL> SELECT
sql_id,
sql_text,
parse_calls,
executions,
loads
FROM v$sql
WHERE loads > 1
ORDER BY loads DESC
FETCH FIRST 10 ROWS ONLY;

SQL_ID SQL_TEXT PARSE_CALLS EXECUTIONS LOADS
————- ————————————————- ———– ———- —–
abcd1234 SELECT * FROM transactions WHERE transaction_id = 1000 500 5000 50
xyz7890 SELECT * FROM accounts WHERE account_id = 100 250 2500 25

4. 查看游标共享配置
SQL> SHOW PARAMETER cursor_sharing;

NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string EXACT

5. 优化措施
– 启用游标共享
SQL> ALTER SYSTEM SET cursor_sharing = ‘FORCE’ SCOPE=BOTH;

– 优化SQL语句，使用绑定变量
SQL> CREATE OR REPLACE PROCEDURE get_transaction(p_trans_id IN NUMBER) IS
BEGIN
SELECT * FROM transactions WHERE transaction_id = p_trans_id;
END;
/

– 收集统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘TRANSACTIONS’);

– 增加共享池大小
SQL> ALTER SYSTEM SET shared_pool_size = 4G SCOPE=BOTH;

– 配置共享池保留区
SQL> ALTER SYSTEM SET shared_pool_reserved_size = 400M SCOPE=BOTH;

– 使用DBMS_SHARED_POOL固定常用对象
SQL> EXEC DBMS_SHARED_POOL.KEEP(‘SCOTT.GET_TRANSACTION’, ‘P’);

6. 验证优化效果
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘parse time cpu’, ‘parse time elapsed’);

NAME VALUE
—————————— ———-
parse count (total) 500000
parse count (hard) 15000
parse time cpu 30000
parse time elapsed 45000

# 优化效果
– 硬解析减少90%
– 数据库性能提高60%
– SQL执行时间减少80%
– 系统稳定性提高

4.3 硬解析问题处理

在某电商网站的生产环境中，硬解析导致数据库性能下降，需要处理。

# 问题现象
– 数据库性能突然下降
– 系统负载增加
– 硬解析严重

# 分析步骤
1. 监控硬解析
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘parse time cpu’, ‘parse time elapsed’);

NAME VALUE
—————————— ———-
parse count (total) 200000
parse count (hard) 50000
parse time cpu 100000
parse time elapsed 150000

HARD_PARSE_RATIO
—————-
25

3. 查看硬解析的SQL语句
SQL> SELECT
sql_id,
sql_text,
parse_calls,
executions,
loads
FROM v$sql
WHERE loads > 1
ORDER BY loads DESC
FETCH FIRST 10 ROWS ONLY;

SQL_ID SQL_TEXT PARSE_CALLS EXECUTIONS LOADS
————- ————————————————- ———– ———- —–
abcd1234 SELECT * FROM orders WHERE order_id = 1000 200 2000 20
xyz7890 SELECT * FROM customers WHERE customer_id = 100 100 1000 10

4. 查看游标共享配置
SQL> SHOW PARAMETER cursor_sharing;

NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string EXACT

# 问题原因
– 未使用绑定变量
– 游标共享未启用
– 共享池不足
– 统计信息过期

# 解决方案
1. 启用游标共享
SQL> ALTER SYSTEM SET cursor_sharing = ‘FORCE’ SCOPE=BOTH;

2. 优化SQL语句，使用绑定变量
SQL> CREATE OR REPLACE PROCEDURE get_order(p_order_id IN NUMBER) IS
BEGIN
SELECT * FROM orders WHERE order_id = p_order_id;
END;
/

3. 收集统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘ORDERS’);

4. 增加共享池大小
SQL> ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

5. 配置共享池保留区
SQL> ALTER SYSTEM SET shared_pool_reserved_size = 200M SCOPE=BOTH;

6. 使用DBMS_SHARED_POOL固定常用对象
SQL> EXEC DBMS_SHARED_POOL.KEEP(‘SCOTT.GET_ORDER’, ‘P’);

7. 验证问题解决
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘parse time cpu’, ‘parse time elapsed’);

NAME VALUE
—————————— ———-
parse count (total) 200000
parse count (hard) 5000
parse time cpu 10000
parse time elapsed 15000

# 解决效果
– 硬解析减少90%
– 数据库性能恢复正常
– 系统负载降低
– SQL执行时间减少80%

生产环境建议：定期监控硬解析，及时发现和处理问题，确保数据库运行高效。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 硬解析管理经验

Oracle数据库硬解析管理经验：

使用绑定变量：使用绑定变量减少硬解析，提高SQL重用率
启用游标共享：启用游标共享功能，提高SQL重用率
优化SQL语句：提高SQL语句效率，减少硬解析
收集统计信息：定期收集统计信息，提高优化器准确性
使用SQL计划基线：使用SQL计划基线稳定执行计划
固定常用对象：使用DBMS_SHARED_POOL固定常用对象
持续优化：根据分析结果，持续优化数据库性能

风哥提示：硬解析是Oracle数据库性能问题的常见原因之一，通过分析硬解析，可以识别数据库性能瓶颈，从而进行针对性的优化，提高数据库性能。from oracle:www.itpux.com

5.2 硬解析检查清单

# 硬解析管理检查清单
– [ ] 监控硬解析情况
– [ ] 计算硬解析率
– [ ] 查看硬解析的SQL语句
– [ ] 查看游标共享配置
– [ ] 使用绑定变量
– [ ] 启用游标共享
– [ ] 优化SQL语句
– [ ] 收集统计信息
– [ ] 固定常用对象
– [ ] 验证优化效果

# 硬解析问题处理流程
1. 发现数据库性能问题
2. 监控硬解析
3. 计算硬解析率
4. 查看硬解析的SQL语句
5. 查看游标共享配置
6. 识别硬解析原因
7. 制定解决方案
8. 实施解决方案
9. 验证问题解决
10. 总结经验，优化配置

5.3 硬解析管理工具

Oracle数据库硬解析管理常用工具：

v$sysstat：查看硬解析统计信息
v$sql：查看SQL语句
v$sql_plan：查看执行计划
v$session：查看会话信息
dba_hist_sysstat：查看硬解析历史
AWR报告：分析数据库性能
ASH报告：分析活动会话历史
SQL Tuning Advisor：SQL调优建议
SQL Access Advisor：SQL访问建议
Oracle Enterprise Manager：图形化监控和管理

持续改进：硬解析管理是一个持续的过程，需要定期review和优化。建议建立硬解析管理的规范和流程，不断改进数据库性能监控效果。

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

Oracle教程FG348-硬解析

Part01-基础概念与理论知识

1.1 硬解析的概念

1.2 硬解析的过程

1.3 硬解析的影响

Part02-生产环境规划与建议

2.1 硬解析规划

2.2 硬解析设计

2.3 硬解析最佳实践

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

3.1 硬解析配置

3.1.1 配置共享池

3.1.2 配置游标共享

3.1.3 配置会话游标缓存

3.2 硬解析管理

3.2.1 管理硬解析

3.2.2 分析硬解析

3.3 硬解析监控

3.3.1 监控硬解析

3.3.2 监控硬解析历史

Part04-生产案例与实战讲解

4.1 硬解析实施案例

4.2 硬解析优化案例

4.3 硬解析问题处理

Part05-风哥经验总结与分享

5.1 硬解析管理经验

5.2 硬解析检查清单

5.3 硬解析管理工具

相关推荐

联系我们