Oracle教程FG347-游标互斥

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

Part01-基础概念与理论知识

1.1 游标互斥的概念

Oracle数据库游标互斥（Cursor Mutex）是一种轻量级的同步机制，用于保护共享池中的游标对象免受并发访问的影响。游标互斥比传统的库缓存锁更轻量级，能够提供更好的并发性能。更多视频教程www.fgedu.net.cn

游标互斥的特点：

轻量级同步机制
用于保护游标对象的并发访问
比传统库缓存锁更高效
在高并发环境下性能更好
减少锁等待时间

1.2 游标互斥的原因

Oracle数据库游标互斥的原因：

高并发游标访问：多个会话同时访问相同的游标
游标解析：游标解析时需要获取互斥
游标执行：游标执行时需要获取互斥
游标关闭：游标关闭时需要获取互斥
游标失效：游标失效时需要获取互斥

1.3 游标互斥的影响

Oracle数据库游标互斥的影响：

会话等待时间增加：会话需要等待获取游标互斥
系统性能下降：大量的游标互斥会导致系统性能下降
响应时间增加：SQL语句执行时间增加
CPU使用率增加：互斥等待和上下文切换增加CPU使用率
并发性能降低：影响系统的并发处理能力

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

Part02-生产环境规划与建议

2.1 游标互斥规划

Oracle数据库游标互斥规划要点：

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

# 适用场景
– 高并发OLTP系统
– 大量游标使用系统
– 共享池争用严重系统
– 游标互斥等待严重系统

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

2.2 游标互斥设计

Oracle数据库游标互斥设计建议：

# 游标互斥设计原则
– 基于系统规模设计
– 基于游标使用模式设计
– 最小化游标互斥争用
– 最大化系统性能
– 合理配置参数

# 游标优化策略
– 使用绑定变量
– 避免硬解析
– 使用游标共享
– 优化SQL语句

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

2.3 游标互斥最佳实践

Oracle数据库游标互斥最佳实践：

使用绑定变量：使用绑定变量减少硬解析，降低游标互斥争用
优化共享池：根据系统需求调整共享池大小
使用游标共享：启用游标共享功能，提高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)’, ‘cursor authentications’);

NAME VALUE
—————————— ———-
parse count (total) 100000
parse count (hard) 5000
cursor authentications 1000

# 计算软解析率
SQL> SELECT
(1 – (hard_parse / total_parse)) * 100 AS soft_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)’
);

SOFT_PARSE_RATIO
—————-
95

3.1.3 配置游标

# 查看游标统计信息
SQL> SELECT
statistic_name,
value
FROM v$sysstat
WHERE statistic_name LIKE ‘%cursor%’
AND value > 0
ORDER BY value DESC
FETCH FIRST 10 ROWS ONLY;

STATISTIC_NAME VALUE
—————————————- ———-
opened cursors cumulative 100000
cursor authentications 10000
parse count (total) 100000
session cursor cache hits 50000
session cursor cache count 1000
opened cursors current 500

# 查看游标使用情况
SQL> SELECT
s.sid,
s.username,
s.status,
s.sql_id,
s.cursor_type,
s.sql_child_number
FROM v$session s
WHERE s.status = ‘ACTIVE’
AND s.cursor_type IS NOT NULL
ORDER BY s.sid;

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

# 查看游标缓存
SQL> SELECT
parameter_value,
sessions_cached,
sessions_current,
hit_ratio
FROM v$session_cache;

PARAMETER_VALUE SESSIONS_CACHED SESSIONS_CURRENT HIT_RATIO
————— —————- —————- ———-
100 50 50 100

3.2 游标互斥管理

3.2.1 管理游标互斥

# 查看游标互斥等待情况
SQL> SELECT
event,
wait_class,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE event LIKE ‘%cursor%’;

EVENT WAIT_CLASS TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ——————– ———– ———– ————
cursor: pin S wait on X Concurrency 1000 5000 5.0
cursor: pin S wait on X Concurrency 500 2500 5.0

# 查看会话级别的游标互斥等待
SQL> SELECT
s.sid,
s.username,
sw.event,
sw.wait_class,
sw.seconds_in_wait
FROM v$session s,
v$session_wait sw
WHERE s.sid = sw.sid
AND s.status = ‘ACTIVE’
AND sw.event LIKE ‘%cursor%’;

SID USERNAME EVENT WAIT_CLASS SECONDS_IN_WAIT
— ————- —————————— ——————– —————
123 SCOTT cursor: pin S wait on X Concurrency 10
124 HR cursor: pin S wait on X Concurrency 5

# 查看游标互斥的详细信息
SQL> SELECT
p1 AS mutex_type,
p2 AS id,
p3 AS value
FROM v$session_wait
WHERE event LIKE ‘%cursor%’;

MUTEX_TYPE ID VALUE
——————– ———- ———-
cursor: pin S wait 0 1
cursor: pin S wait 1 2

3.2.2 分析游标互斥

# 分析游标互斥的SQL语句
SQL> SELECT
s.sid,
s.username,
s.sql_id,
sw.event,
sw.seconds_in_wait
FROM v$session s,
v$session_wait sw
WHERE s.sid = sw.sid
AND s.status = ‘ACTIVE’
AND sw.event LIKE ‘%cursor%’;

SID USERNAME SQL_ID EVENT SECONDS_IN_WAIT
— ————- ————- —————————— —————
123 SCOTT abcd1234 cursor: pin S wait on X 10
124 HR xyz7890 cursor: pin S wait on X 5

# 查看SQL语句
SQL> SELECT
sql_id,
sql_text,
parse_calls,
executions
FROM v$sql
WHERE sql_id IN (‘abcd1234’, ‘xyz7890’);

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

# 查看游标统计信息
SQL> SELECT
statistic_name,
value
FROM v$sysstat
WHERE statistic_name IN (‘cursor authentications’, ‘session cursor cache hits’, ‘session cursor cache misses’);

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

3.3 游标互斥监控

3.3.1 监控游标互斥

# 实时监控游标互斥
SQL> SELECT
event,
wait_class,
COUNT(*)
FROM v$session_wait
WHERE event LIKE ‘%cursor%’
GROUP BY event, wait_class;

EVENT WAIT_CLASS COUNT(*)
—————————— ——————– ——–
cursor: pin S wait on X Concurrency 5
cursor: pin S wait on X Concurrency 3

# 使用AWR报告监控游标互斥
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

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

# 查看AWR报告中的游标互斥部分
# Top 5 Wait Events
~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
———————————— ———– ———– —— —— ———-
cursor: pin S wait on X 1000 5000 500 40.0 Concurrency
cursor: pin S wait on X 500 2500 500 20.0 Concurrency

3.3.2 监控游标互斥历史

# 查看游标互斥历史
SQL> SELECT
h.snap_id,
h.event_name,
SUM(h.total_waits) AS total_waits,
SUM(h.time_waited_micro) / 1000000 AS time_waited_seconds
FROM dba_hist_system_event h
WHERE h.snap_id BETWEEN 100 AND 101
AND h.event_name LIKE ‘%cursor%’
GROUP BY h.snap_id, h.event_name
ORDER BY h.snap_id;

SNAP_ID EVENT_NAME TOTAL_WAITS TIME_WAITED_SECONDS
——- —————————— ———– ——————
100 cursor: pin S wait on X 500 25
100 cursor: pin S wait on X 250 12
101 cursor: pin S wait on X 500 25
101 cursor: pin S wait on X 250 12

# 查看会话游标互斥历史
SQL> SELECT
h.session_id,
h.event_name,
COUNT(*) AS wait_count,
SUM(h.wait_time + h.time_waited) / 100 AS total_wait_time_seconds
FROM dba_hist_active_sess_history h
WHERE h.sample_time BETWEEN SYSDATE – 1/24 AND SYSDATE
AND h.event_name LIKE ‘%cursor%’
GROUP BY h.session_id, h.event_name
ORDER BY total_wait_time_seconds DESC
FETCH FIRST 10 ROWS ONLY;

SESSION_ID EVENT_NAME WAIT_COUNT TOTAL_WAIT_TIME_SECONDS
———- —————————— ———– ———————-
123 cursor: pin S wait on X 100 500
124 cursor: pin S wait on X 50 250

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

Part04-生产案例与实战讲解

4.1 游标互斥实施案例

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

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

# 实施方案
1. 监控游标互斥
SQL> SELECT
event,
wait_class,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE event LIKE ‘%cursor%’;

2. 分析游标使用情况
SQL> SELECT
statistic_name,
value
FROM v$sysstat
WHERE statistic_name IN (‘parse count (total)’, ‘parse count (hard)’, ‘cursor authentications’);

NAME VALUE
—————————— ———-
parse count (total) 100000
parse count (hard) 20000
cursor authentications 10000

3. 计算软解析率
SQL> SELECT
(1 – (hard_parse / total_parse)) * 100 AS soft_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)’
);

SOFT_PARSE_RATIO
—————-
80

4. 查看游标缓存
SQL> SELECT
parameter_value,
sessions_cached,
sessions_current,
hit_ratio
FROM v$session_cache;

PARAMETER_VALUE SESSIONS_CACHED SESSIONS_CURRENT HIT_RATIO
————— —————- —————- ———-
100 50 50 100

5. 优化措施
– 增加共享池大小
SQL> ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

– 启用游标共享
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’);

6. 验证优化效果
SQL> SELECT
event,
wait_class,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE event LIKE ‘%cursor%’;

EVENT WAIT_CLASS TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ——————– ———– ———— ————
cursor: pin S wait on X Concurrency 100 500 5.0
cursor: pin S wait on X Concurrency 50 250 5.0

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

4.2 游标互斥优化案例

在某金融机构的生产环境中，需要优化游标互斥，提高数据库性能。

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

# 优化方案
1. 监控游标互斥
SQL> SELECT
event,
wait_class,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE event LIKE ‘%cursor%’;

EVENT WAIT_CLASS TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ——————– ———– ———— ————
cursor: pin S wait on X Concurrency 5000 25000 5.0
cursor: pin S wait on X Concurrency 2500 12500 5.0

2. 分析游标使用情况
SQL> SELECT
statistic_name,
value
FROM v$sysstat
WHERE statistic_name IN (‘parse count (total)’, ‘parse count (hard)’, ‘cursor authentications’);

NAME VALUE
—————————— ———-
parse count (total) 500000
parse count (hard) 150000
cursor authentications 50000

SOFT_PARSE_RATIO
—————-
70

4. 查看游标缓存
SQL> SELECT
parameter_value,
sessions_cached,
sessions_current,
hit_ratio
FROM v$session_cache;

PARAMETER_VALUE SESSIONS_CACHED SESSIONS_CURRENT HIT_RATIO
————— —————- —————- ———-
200 100 100 100

5. 优化措施
– 增加共享池大小
SQL> ALTER SYSTEM SET shared_pool_size = 4G SCOPE=BOTH;

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

– 启用游标共享
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;
/

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

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

6. 验证优化效果
SQL> SELECT
event,
wait_class,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE event LIKE ‘%cursor%’;

EVENT WAIT_CLASS TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ——————– ———– ———— ————
cursor: pin S wait on X Concurrency 500 2500 5.0
cursor: pin S wait on X Concurrency 250 1250 5.0

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

4.3 游标互斥问题处理

在某电商网站的生产环境中，游标互斥导致数据库性能下降，需要处理。

# 问题现象
– 数据库性能突然下降
– 系统负载增加
– 游标互斥严重

# 分析步骤
1. 监控游标互斥
SQL> SELECT
event,
wait_class,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE event LIKE ‘%cursor%’;

EVENT WAIT_CLASS TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ——————– ———– ———— ————
cursor: pin S wait on X Concurrency 2000 10000 5.0
cursor: pin S wait on X Concurrency 1000 5000 5.0

2. 分析游标使用情况
SQL> SELECT
statistic_name,
value
FROM v$sysstat
WHERE statistic_name IN (‘parse count (total)’, ‘parse count (hard)’, ‘cursor authentications’);

NAME VALUE
—————————— ———-
parse count (total) 200000
parse count (hard) 50000
cursor authentications 20000

SOFT_PARSE_RATIO
—————-
75

4. 查看游标缓存
SQL> SELECT
parameter_value,
sessions_cached,
sessions_current,
hit_ratio
FROM v$session_cache;

PARAMETER_VALUE SESSIONS_CACHED SESSIONS_CURRENT HIT_RATIO
————— —————- —————- ———-
100 50 50 100

# 问题原因
– 共享池不足
– 硬解析过多
– 未使用绑定变量
– 游标缓存不足

# 解决方案
1. 增加共享池大小
SQL> ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

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

3. 启用游标共享
SQL> ALTER SYSTEM SET cursor_sharing = ‘FORCE’ SCOPE=BOTH;

4. 优化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;
/

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

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

7. 验证问题解决
SQL> SELECT
event,
wait_class,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE event LIKE ‘%cursor%’;

EVENT WAIT_CLASS TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ——————– ———– ———— ————
cursor: pin S wait on X Concurrency 200 1000 5.0
cursor: pin S wait on X Concurrency 100 500 5.0

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

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

Part05-风哥经验总结与分享

5.1 游标互斥管理经验

Oracle数据库游标互斥管理经验：

使用绑定变量：使用绑定变量减少硬解析，降低游标互斥争用
优化共享池：根据系统需求调整共享池大小
使用游标共享：启用游标共享功能，提高SQL重用率
优化SQL语句：提高SQL语句效率，减少游标使用
正确关闭游标：确保应用程序正确关闭游标
固定常用对象：使用DBMS_SHARED_POOL固定常用对象
持续优化：根据分析结果，持续优化数据库性能

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

5.2 游标互斥检查清单

# 游标互斥管理检查清单
– [ ] 监控游标互斥情况
– [ ] 分析游标使用情况
– [ ] 计算软解析率
– [ ] 查看游标缓存
– [ ] 使用绑定变量
– [ ] 优化共享池
– [ ] 使用游标共享
– [ ] 优化SQL语句
– [ ] 固定常用对象
– [ ] 验证优化效果

# 游标互斥问题处理流程
1. 发现数据库性能问题
2. 监控游标互斥
3. 分析游标使用情况
4. 计算软解析率
5. 查看游标缓存
6. 识别游标互斥原因
7. 制定解决方案
8. 实施解决方案
9. 验证问题解决
10. 总结经验，优化配置

5.3 游标互斥管理工具

Oracle数据库游标互斥管理常用工具：

v$system_event：查看系统级别的游标互斥
v$session_wait：查看会话级别的游标互斥
v$sysstat：查看游标统计信息
v$session_cache：查看游标缓存
v$sql：查看SQL语句
dba_hist_system_event：查看游标互斥历史
dba_hist_active_sess_history：查看会话游标互斥历史
AWR报告：分析数据库性能
ASH报告：分析活动会话历史
Oracle Enterprise Manager：图形化监控和管理

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

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

Oracle教程FG347-游标互斥

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 游标互斥管理工具

相关推荐

联系我们