— 查看配置文件使用情况
SELECT * FROM dba_sql_profile_attr
WHERE profile_name = ‘profile_name’;– 验证配置文件是否被使用
SELECT sql_id, plan_hash_value, executions, elapsed_time
FROM v$sql
WHERE sql_id = ‘sql_id_value’;– 查看配置文件的详细信息
SELECT * FROM table(DBMS_SQLTUNE.REPORT_SQL_PROFILE(
name => ‘profile_name’
));

— 创建测试表
CREATE TABLE fgsales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
amount NUMBER,
region VARCHAR2(50)
);– 插入测试数据
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_task_id VARCHAR2(30);l_sql_id VARCHAR2(13) := ‘sql_id_value’;BEGIN
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => l_sql_id,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘tune_sales_sql’,
description => ‘Tune fgsales summary query’
);DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_id);DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => l_task_id,
profile_name => ‘sales_summary_profile’,
replace => TRUE
);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
————- ————————————————–
ghi789 SELECT region, SUM(amount) AS total_sales FROM fgsales GROUP BY region

SQL> DECLARE
2 l_task_id VARCHAR2(30);3 l_sql_id VARCHAR2(13) := ‘ghi789’;4 BEGIN
5 l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
6 sql_id => l_sql_id,
7 scope => ‘COMPREHENSIVE’,
8 time_limit => 60,
9 task_name => ‘tune_sales_sql’,
10 description => ‘Tune fgsales summary query’
11 );12
13 DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_id);14
15 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
16 task_name => l_task_id,
17 profile_name => ‘sales_summary_profile’,
18 replace => TRUE
19 );20 END;21 /PL/SQL procedure successfully completed.

— 查看配置文件的效果
SELECT * FROM table(DBMS_SQLTUNE.REPORT_SQL_PROFILE(
name => ‘sales_summary_profile’
));– 禁用配置文件
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => ‘sales_summary_profile’,
attribute_name => ‘STATUS’,
attribute_value => ‘DISABLED’
);– 启用配置文件
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => ‘sales_summary_profile’,
attribute_name => ‘STATUS’,
attribute_value => ‘ENABLED’
);

— 检查配置文件状态
SELECT name, status, created
FROM dba_sql_profiles;– 检查配置文件是否被使用
SELECT sql_id, plan_hash_value, executions, elapsed_time
FROM v$sql
WHERE sql_id = ‘sql_id_value’;– 重新创建配置文件
DECLARE
l_task_id VARCHAR2(30);l_sql_id VARCHAR2(13) := ‘sql_id_value’;BEGIN
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => l_sql_id,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘retune_sql’,
description => ‘Retune SQL’
);DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_id);DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => l_task_id,
profile_name => ‘profile_name’,
replace => TRUE
);END;/– 删除配置文件
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => ‘profile_name’);