— 查看补丁使用情况
SELECT * FROM dba_sql_patch_attr
WHERE patch_name = ‘patch_name’;– 验证补丁是否被使用
SELECT sql_id, plan_hash_value, executions, elapsed_time
FROM v$sql
WHERE sql_id = ‘sql_id_value’;– 查看补丁的详细信息
SELECT * FROM table(DBMS_SQLDIAG.REPORT_SQL_PATCH(
patch_name => ‘patch_name’
));

— 创建测试表
CREATE TABLE fgsales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
amount NUMBER,
region VARCHAR2(50)
);– 创建索引
CREATE INDEX idx_sales_region ON fgsales(region);– 插入测试数据
INSERT INTO fgsales VALUES (1, SYSDATE – 30, 1000, ‘North’);INSERT INTO fgsales VALUES (2, SYSDATE – 20, 2000, ‘South’);INSERT INTO fgsales VALUES (3, SYSDATE – 10, 1500, ‘East’);INSERT INTO fgsales VALUES (4, SYSDATE, 3000, ‘West’);COMMIT;– 执行SQL语句
SELECT region, SUM(amount) AS total_sales
FROM fgsales
GROUP BY region;– 查看SQL ID
SELECT sql_id, sql_text FROM v$sql
WHERE sql_text LIKE ‘%region, SUM(amount)%’ AND rownum = 1;– 创建SQL补丁
DECLARE
l_patch_id NUMBER;BEGIN
l_patch_id := DBMS_SQLDIAG.CREATE_SQL_PATCH(
sql_id => ‘sql_id_value’,
patch_name => ‘sales_summary_patch’,
hint_text => ‘/*+ INDEX(fgsales idx_sales_region) */’,
description => ‘Patch for fgsales summary query’
);DBMS_OUTPUT.PUT_LINE(‘Patch ID: ‘ || l_patch_id);END;/

SQL> SELECT region, SUM(amount) AS total_sales
2 FROM fgsales
3 GROUP BY region;REGION TOTAL_SALES
——- ———–
North 1000
South 2000
East 1500
West 3000

SQL> SELECT sql_id, sql_text FROM v$sql
2 WHERE sql_text LIKE ‘%region, SUM(amount)%’ AND rownum = 1;SQL_ID SQL_TEXT
————- ————————————————–
jkl012 SELECT region, SUM(amount) AS total_sales FROM fgsales GROUP BY region

SQL> DECLARE
2 l_patch_id NUMBER;3 BEGIN
4 l_patch_id := DBMS_SQLDIAG.CREATE_SQL_PATCH(
5 sql_id => ‘jkl012’,
6 patch_name => ‘sales_summary_patch’,
7 hint_text => ‘/*+ INDEX(fgsales idx_sales_region) */’,
8 description => ‘Patch for fgsales summary query’
9 );10 DBMS_OUTPUT.PUT_LINE(‘Patch ID: ‘ || l_patch_id);11 END;12 /Patch ID: 1

— 查看补丁的效果
SELECT * FROM table(DBMS_SQLDIAG.REPORT_SQL_PATCH(
patch_name => ‘sales_summary_patch’
));– 禁用补丁
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH(
patch_name => ‘sales_summary_patch’,
attribute_name => ‘STATUS’,
attribute_value => ‘DISABLED’
);– 启用补丁
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH(
patch_name => ‘sales_summary_patch’,
attribute_name => ‘STATUS’,
attribute_value => ‘ENABLED’
);

— 检查补丁状态
SELECT name, status, created
FROM dba_sql_patches;– 检查补丁是否被使用
SELECT sql_id, plan_hash_value, executions, elapsed_time
FROM v$sql
WHERE sql_id = ‘sql_id_value’;– 重新创建补丁
DECLARE
l_patch_id NUMBER;BEGIN
l_patch_id := DBMS_SQLDIAG.CREATE_SQL_PATCH(
sql_id => ‘sql_id_value’,
patch_name => ‘patch_name’,
hint_text => ‘/*+ INDEX(fgsales idx_sales_region) */’,
description => ‘Updated patch for fgsales query’
);END;/– 删除补丁
EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(patch_name => ‘patch_name’);