Oracle教程FG086-游标共享机制

Part02-生产环境规划与建议

2.1 游标共享配置

游标共享的配置参数：

CURSOR_SHARING：

EXACT：默认值，要求SQL语句完全相同才能共享游标
SIMILAR：允许结构相同但字面量值不同的SQL语句共享游标
FORCE：强制共享游标，即使绑定变量类型不同

OPEN_CURSORS：

默认值：50
建议值：根据应用程序的需求设置，一般为1000-3000

SESSION_CACHED_CURSORS：

默认值：20
建议值：根据应用程序的需求设置，一般为100-500

风哥提示：在生产环境中，应根据应用程序的特点和性能需求选择合适的游标共享配置。

2.2 游标共享策略

游标共享的策略：

使用绑定变量：使用绑定变量可以提高游标共享的机会
统一SQL格式：确保SQL语句的格式一致，包括大小写、空格和注释
合理设置CURSOR_SHARING：根据应用程序的特点选择合适的CURSOR_SHARING值
监控游标使用：定期监控游标使用情况，及时发现和解决问题
优化SQL语句：优化SQL语句，减少游标数量
合理设置游标缓存：根据应用程序的需求设置合适的游标缓存大小

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

2.3 游标共享注意事项

游标共享的注意事项：

绑定变量类型：确保绑定变量的类型与列的类型匹配
SQL语句格式：保持SQL语句的格式一致，避免因格式不同导致游标无法共享
CURSOR_SHARING设置：谨慎使用SIMILAR和FORCE模式，可能会影响执行计划的选择
游标泄漏：避免游标泄漏，及时关闭不再使用的游标
共享池大小：确保共享池大小足够，避免因共享池不足导致游标被换出
执行计划质量：注意游标共享对执行计划质量的影响，特别是在使用SIMILAR和FORCE模式时

from oracle:www.itpux.com

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

在生产环境中实施游标共享的实施方案：

评估现状：评估当前的游标使用情况，包括游标数量、硬解析次数等。
分析问题：分析游标使用中存在的问题，如硬解析过多、游标泄漏等。
制定策略：根据评估结果，制定游标共享策略，包括绑定变量使用、CURSOR_SHARING设置等。
测试验证：在测试环境中测试游标共享策略的效果。
部署实施：在生产环境中部署游标共享策略。
监控维护：定期监控游标使用情况，及时发现和解决问题。
优化调整：根据监控结果，调整游标共享策略，优化性能。

Part04-生产案例与实战讲解

4.1 游标共享参数设置

示例：游标共享参数设置

— 查看当前的游标共享参数
SHOW PARAMETER cursor_sharing;SHOW PARAMETER open_cursors;SHOW PARAMETER session_cached_cursors;– 修改游标共享参数（需要重启数据库）
ALTER SYSTEM SET cursor_sharing = ‘EXACT’ SCOPE=SPFILE;ALTER SYSTEM SET open_cursors = 1000 SCOPE=SPFILE;ALTER SYSTEM SET session_cached_cursors = 200 SCOPE=SPFILE;– 立即修改游标共享参数（不需要重启数据库）
ALTER SYSTEM SET cursor_sharing = ‘EXACT’ SCOPE=BOTH;ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;ALTER SYSTEM SET session_cached_cursors = 200 SCOPE=BOTH;– 在会话级别修改游标共享参数
ALTER SESSION SET cursor_sharing = ‘EXACT’;

— 查看当前的游标共享参数
NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string EXACT
open_cursors integer 3000
session_cached_cursors integer 500

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

4.2 游标共享监控

示例：游标共享监控

— 查看游标使用情况
SELECT
sid,
username,
sql_id,
sql_text,
cursor_type,
status
FROM v$open_cursor
WHERE username = ‘SCOTT’;– 查看共享池中的游标
SELECT
sql_id,
sql_text,
version_count,
executions,
parse_calls,
hard_parse_count
FROM v$sql
WHERE sql_text LIKE ‘%SELECT%FROM%EMPLOYEES%’;– 查看游标共享统计信息
SELECT
name,
value
FROM v$sysstat
WHERE name LIKE ‘%cursor%’;– 查看硬解析统计信息
SELECT
name,
value
FROM v$sysstat
WHERE name LIKE ‘%parse%’;– 查看游标缓存统计信息
SELECT
name,
value
FROM v$sysstat
WHERE name LIKE ‘%cursor cache%’;

— 查看游标使用情况
SID USERNAME SQL_ID SQL_TEXT CURSOR_TYPE STATUS
———- —————————— ————- ———————————– ——————– ———-
123 SCOTT a1b2c3d4e5f6 SELECT * FROM employees WHERE emp OPEN PARSED

— 查看共享池中的游标
SQL_ID SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HARD_PARSE_COUNT
————- ———————————– ————- ———- ———– —————-
a1b2c3d4e5f6 SELECT * FROM employees WHERE emp 1 100 100 1

— 查看游标共享统计信息
NAME VALUE
—————————————————————- ———-
opened cursors current 123
session cursor cache hits 10000
session cursor cache count 500

— 查看硬解析统计信息
NAME VALUE
—————————————————————- ———-
parse count (total) 1000
parse count (hard) 100
parse count (failures) 0

— 查看游标缓存统计信息
NAME VALUE
—————————————————————- ———-
session cursor cache hits 10000
session cursor cache count 500

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

4.3 游标共享问题排查

示例：游标共享问题排查

— 查找硬解析过多的SQL语句
SELECT
sql_id,
sql_text,
parse_calls,
hard_parse_count,
executions,
round((hard_parse_count/executions)*100, 2) hard_parse_percent
FROM v$sql
WHERE executions > 10
ORDER BY hard_parse_percent DESC;– 查找版本计数过高的SQL语句
SELECT
sql_id,
sql_text,
version_count,
executions
FROM v$sql
WHERE version_count > 10
ORDER BY version_count DESC;– 查找未使用绑定变量的SQL语句
SELECT
sql_id,
sql_text,
executions,
hard_parse_count
FROM v$sql
WHERE sql_text LIKE ‘%WHERE%=%’
AND sql_text NOT LIKE ‘%:=%’
AND executions > 10
ORDER BY hard_parse_count DESC;– 查看游标泄漏情况
SELECT
username,
count(*) open_cursors
FROM v$open_cursor
GROUP BY username
ORDER BY open_cursors DESC;– 查看共享池使用情况
SELECT
pool,
name,
bytes/1024/1024 MB
FROM v$sgastat
WHERE pool = ‘shared pool’
ORDER BY bytes DESC;

— 查找硬解析过多的SQL语句
SQL_ID SQL_TEXT PARSE_CALLS HARD_PARSE_COUNT EXECUTIONS HARD_PARSE_PERCENT
————- ———————————– ———– —————- ———- ——————
b1c2d3e4f5g6 SELECT * FROM employees WHERE emp 1000 500 1000 50.00

— 查找版本计数过高的SQL语句
SQL_ID SQL_TEXT VERSION_COUNT EXECUTIONS
————- ———————————– ————- ———-
c1d2e3f4g5h6 SELECT * FROM employees WHERE emp 20 500

— 查找未使用绑定变量的SQL语句
SQL_ID SQL_TEXT EXECUTIONS HARD_PARSE_COUNT
————- ———————————– ———- —————-
d1e2f3g4h5i6 SELECT * FROM employees WHERE emp 1000 999

— 查看游标泄漏情况
USERNAME OPEN_CURSORS
—————————— ————
SCOTT 500
HR 200

— 查看共享池使用情况
POOL NAME MB
———— ————————– ———-
shared pool free memory 100.5
shared pool library cache 80.2
shared pool dictionary cache 20.1

学习交流加群风哥QQ113257174

4.4 游标共享性能测试

示例：游标共享性能测试

— 创建测试表
CREATE TABLE test_cursor_sharing (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
value NUMBER
);– 插入测试数据
BEGIN
FOR i IN 1 .. 10000 LOOP
INSERT INTO test_cursor_sharing VALUES (i, ‘Name ‘ || i, i);END LOOP;COMMIT;END;/– 测试1：不使用绑定变量
SET TIMING ON

DECLARE
v_id NUMBER;v_name VARCHAR2(100);v_value NUMBER;BEGIN
FOR i IN 1 .. 1000 LOOP
EXECUTE IMMEDIATE ‘SELECT id, name, value FROM test_cursor_sharing WHERE id = ‘ || i
INTO v_id, v_name, v_value;END LOOP;END;/– 测试2：使用绑定变量
DECLARE
v_id NUMBER;v_name VARCHAR2(100);v_value NUMBER;BEGIN
FOR i IN 1 .. 1000 LOOP
EXECUTE IMMEDIATE ‘SELECT id, name, value FROM test_cursor_sharing WHERE id = :id’
INTO v_id, v_name, v_value
USING i;END LOOP;END;/– 测试3：使用CURSOR_SHARING=FORCE
ALTER SYSTEM SET cursor_sharing = ‘FORCE’ SCOPE=BOTH;DECLARE
v_id NUMBER;v_name VARCHAR2(100);v_value NUMBER;BEGIN
FOR i IN 1 .. 1000 LOOP
EXECUTE IMMEDIATE ‘SELECT id, name, value FROM test_cursor_sharing WHERE id = ‘ || i
INTO v_id, v_name, v_value;END LOOP;END;/ALTER SYSTEM SET cursor_sharing = ‘EXACT’ SCOPE=BOTH;SET TIMING OFF

— 测试1：不使用绑定变量
PL/SQL procedure successfully completed.

Elapsed: 00:00:01.23

— 测试2：使用绑定变量
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.12

— 测试3：使用CURSOR_SHARING=FORCE
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

学习交流加群风哥QQ113257174

Part05-风哥经验总结与分享

风哥提示：在使用游标共享机制时，应注意以下几点：

使用绑定变量：尽可能使用绑定变量，提高游标共享的机会。
统一SQL格式：保持SQL语句的格式一致，包括大小写、空格和注释。
合理设置CURSOR_SHARING：根据应用程序的特点选择合适的CURSOR_SHARING值，一般建议使用EXACT模式。
监控游标使用：定期监控游标使用情况，及时发现和解决问题。
避免游标泄漏：及时关闭不再使用的游标，避免游标泄漏。
优化共享池：确保共享池大小足够，避免因共享池不足导致游标被换出。
测试性能：定期进行性能测试，验证游标共享的效果。
注意执行计划质量：在使用SIMILAR和FORCE模式时，注意对执行计划质量的影响。
合理设置游标缓存：根据应用程序的需求设置合适的游标缓存大小。
结合其他优化手段：游标共享应与其他优化手段结合使用，如索引优化、SQL重写等。

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

from:风哥.QQ113257174.WX:itpux-com,web: http://www.fgedu.net.cn

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

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

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

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