一、 什么是 SQL 配置文件？
SQL 配置文件是存储在数据字典中的信息集合，它使查询优化器能够为 SQL 语句创建最佳执行计划。SQL 配置文件包含对自动 SQL 调优期间发现的优化器估计不佳的更正。此信息可以改进优化器基数和选择性估计，进而引导优化器选择更好的计划。
SQL 配置文件不包含有关单个执行计划的信息。相反，优化器在选择计划时具有以下信息来源：
- 环境，包含数据库配置、绑定变量值、优化器统计信息、数据集等
- SQL 配置文件中的补充统计信息
重要的是要注意 SQL Profile 不会像存储大纲那样冻结 SQL 语句的执行计划。随着表的增长或索引的创建或删除，执行计划可以使用相同的 SQL 配置文件进行更改。即使相应语句的数据分布或访问路径发生变化，存储在其中的信息仍然具有相关性。但是，在很长一段时间内，其内容可能会过时，必须重新生成。这可以通过在同一语句上再次运行自动 SQL 调整来重新生成 SQL 配置文件来完成。
二、如何控制 SQL Profile 的范围？
SQL 概要文件的范围可以由 CATEGORY 概要文件属性控制。此属性确定哪些用户会话可以应用配置文件。可以在 DBA_SQL_PROFILES 视图的 CATEGORY 列中查看 SQL Profile 的 CATEGORY 属性。
SQL> select * from DBA_SQL_PROFILES; no rows selected复制
默认情况下，所有配置文件都在 DEFAULT 类别中创建。这意味着 SQLTUNE_CATEGORY 初始化参数设置为 DEFAULT 的所有用户会话都可以使用该配置文件。
通过更改 SQL 配置文件的类别，您可以确定哪些会话受到配置文件创建的影响。例如，通过将 SQL Profile 的类别设置为 DEV，只有那些 SQLTUNE_CATEGORY 初始化参数设置为 DEV 的用户会话才能使用该配置文件。所有其他会话都无权访问 SQL 配置文件，并且 SQL 语句的执行计划不受 SQL 配置文件的影响。此技术使您能够在受限环境中测试 SQL 配置文件，然后再将其提供给其他用户会话。
三、SQL Profile 可以应用于哪些语句？
SELECT 语句
UPDATE 语句
INSERT 语句（仅带有 SELECT 子句）
DELETE 语句
CREATE TABLE 语句（仅带有 AS SELECT 子句）
MERGE 语句（更新或插入操作）
四、sql profile管理
4.1、创建测试数据
SQL> select * from dept where deptno='20'; Execution Plan ---------------------------------------------------------- Plan hash value: 2852011669 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPTNO"=20) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 572 bytes sent via SQL*Net to client 387 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select /*+ no_index(dept,pk_dept) */ * from dept where deptno='20'; Execution Plan ---------------------------------------------------------- Plan hash value: 3383998547 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 30 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO"=20) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 712 bytes sent via SQL*Net to client 428 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed复制
4.2、创建调优任务
SQL> declare 2 my_task_name VARCHAR2(30); 3 my_sqltext CLOB; 4 begin 5 my_sqltext := 'select /*+ no_index(dept,pk_dept) */ * from dept where deptno=20'; 6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 sql_text => my_sqltext, 8 user_name => 'SCOTT', 9 scope => 'COMPREHENSIVE', 10 time_limit => 60, 11 task_name => 'my_sql_tuning_task_2', 12 description => 'Task to tune a query on a specified table'); 13 end; 14 / PL/SQL procedure successfully completed. SQL> begin 2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2'); 3 end; 4 / PL/SQL procedure successfully completed.复制
SQL> set long 10000 SQL> set longchunksize 1000 SQL> set linesize 100 SQL> set heading off SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL; set heading on GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : my_sql_tuning_task_2 Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 60 Completion Status : COMPLETED Started at : 10/18/2022 19:46:07 Completed at : 10/18/2022 19:46:08 ------------------------------------------------------------------------------- Schema Name: SCOTT SQL ID : 2zh9xq5rc29zp SQL Text : select /*+ no_index(dept,pk_dept) */ * from dept where deptno=20 ------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) ------------------------------------------------------------------------------- 1- Statistics Finding --------------------- Table "SCOTT"."DEPT" and its indices were not analyzed. Recommendation -------------- - Consider collecting optimizer statistics for this table and its indices. execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'DEPT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); Rationale --------- The optimizer requires up-to-date statistics for the table and its indices in order to select a good execution plan. 2- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 71.42%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE); Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .000027 .000017 37.03 % CPU Time (s): .000021 .000008 61.9 % User I/O Time (s): 0 0 Buffer Gets: 7 2 71.42 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 3383998547 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 18 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO"=20) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 3 (U - Unused (3)) --------------------------------------------------------------------------- 0 - STATEMENT U - IGNORE_OPTIM_EMBEDDED_HINTS / hint overridden by another in parent query block U - OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block 1 - SEL$1 / DEPT@SEL$1 U - no_index(dept,pk_dept) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS 2- Using SQL Profile -------------------- Plan hash value: 2852011669 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 18 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPTNO"=20) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 U - no_index(dept,pk_dept) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS -------------------------------------------------------------------------------复制
4.3、接受profile
SQL> DECLARE 2 my_sqlprofile_name VARCHAR2(30); 3 begin 4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 5 task_name => 'my_sql_tuning_task_2', 6 name => 'my_sql_profile'); 7 end; 8 / PL/SQL procedure successfully completed.复制
SQL> select /*+ no_index(dept,pk_dept) */ * from dept where deptno=20; Execution Plan ---------------------------------------------------------- Plan hash value: 2852011669 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 18 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPTNO"=20) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 U - no_index(dept,pk_dept) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS Note ----- - SQL profile "my_sql_profile" used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 572 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed复制
4.4、修改profile
SQL> col name for a30 SQL> col sql_text for a50 SQL> col STATUS for a10 SQL> select name,sql_text,status from dba_sql_profiles; NAME SQL_TEXT STATUS ------------------------------ -------------------------------------------------- ---------- my_sql_profile select /*+ no_index(dept,pk_dept) */ * from dept w ENABLED here deptno=20 SQL> BEGIN 2 DBMS_SQLTUNE.ALTER_SQL_PROFILE( 3 name => 'my_sql_profile', 4 attribute_name => 'STATUS', 5 value => 'DISABLED'); 6 END; 7 / PL/SQL procedure successfully completed. SQL> select name,sql_text,status from dba_sql_profiles; NAME SQL_TEXT STATUS ------------------------------ -------------------------------------------------- ---------- my_sql_profile select /*+ no_index(dept,pk_dept) */ * from dept w DISABLED here deptno=20 SQL> select /*+ no_index(dept,pk_dept) */ * from dept where deptno=20; Execution Plan ---------------------------------------------------------- Plan hash value: 3383998547 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO"=20) Statistics ---------------------------------------------------------- 102 recursive calls 0 db block gets 117 consistent gets 0 physical reads 0 redo size 712 bytes sent via SQL*Net to client 426 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 1 rows processed复制
4.5、删除profile
SQL> begin 2 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select name,sql_text,status from dba_sql_profiles; no rows selected复制
五、调用sql优化助手
5.1、创建调优任务
SQL> DECLARE 2 my_task_name VARCHAR2(30); 3 sqltext CLOB; 4 BEGIN 5 sqltext := 'select /*+ no_index(dept,pk_dept) */ * from dept where deptno=20'; 6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 sql_text => sqltext, 8 user_name => 'SCOTT', 9 scope => 'COMPREHENSIVE', 10 time_limit => 60, 11 task_name => 'task1', 12 description => 'tuning a sql'); 13 END; 14 / PL/SQL procedure successfully completed. --对于已知sql_id,可以使用如下 SQL> SET SERVEROUTPUT ON SQL> declare 2 my_task VARCHAR2(40); 3 begin 4 my_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '8xfufkq3nnuwa'); 5 DBMS_OUTPUT.put_line('task_id: ' || my_task ); 6 end; 7 / task_id: TASK_168 PL/SQL procedure successfully completed. ##### 5.2、检查任务 SQL> select owner,task_id,task_name,status,status_message from dba_advisor_log; OWNER TASK_ID TASK_NAME STATUS STATUS_MESSAGE -------------------- ---------- ------------------------------ -------------------- ------------------------------ SCOTT 168 TASK_168 INITIAL SYS 167 task1 INITIAL复制
5.3、运行任务
SQL> begin 2 dbms_sqltune.Execute_tuning_task(task_name=>'TASK_168'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> begin 2 dbms_sqltune.Execute_tuning_task(task_name=>'task1'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select owner,task_id,task_name,status,status_message from dba_advisor_log; SCOTT 168 TASK_168 COMPLETED SYS 167 task1 COMPLETED复制
5.4、检查建议
SQL> set long 65536 SQL> set longchunksize 65536 SQL> set linesize 100 SQL> select dbms_sqltune.report_tuning_task('task1') from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : task1 Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 60 Completion Status : COMPLETED Started at : 10/20/2022 04:47:25 Completed at : 10/20/2022 04:47:25 DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Schema Name: SCOTT SQL ID : 2zh9xq5rc29zp SQL Text : select /*+ no_index(dept,pk_dept) */ * from dept where deptno=20 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 66.64%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'task1', task_owner => 'SYS', replace => TRUE); Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .000028 .000022 21.42 % CPU Time (s): .000029 .000022 24.13 % User I/O Time (s): 0 0 Buffer Gets: 6 2 66.66 % Physical Read Requests: 0 0 DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions. DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 3383998547 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO"=20) Hint Report (identified by operation id / Query Block Name / Object Alias): DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- Total hints for statement: 3 (U - Unused (3)) --------------------------------------------------------------------------- 0 - STATEMENT U - IGNORE_OPTIM_EMBEDDED_HINTS / hint overridden by another in parent query block U - OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block 1 - SEL$1 / DEPT@SEL$1 U - no_index(dept,pk_dept) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS 2- Using SQL Profile DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- -------------------- Plan hash value: 2852011669 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPTNO"=20) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 U - no_index(dept,pk_dept) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK1') ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------复制
SQL> set long 65536 SQL> set longchunksize 65536 SQL> set linesize 100 SQL> select dbms_sqltune.report_tuning_task('TASK_168') from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : TASK_168 Tuning Task Owner : SCOTT Workload Type : Single SQL Statement Execution Count : 2 Current Execution : EXEC_184 Execution Type : TUNE SQL Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- Started at : 10/20/2022 04:47:14 Completed at : 10/20/2022 04:47:14 ------------------------------------------------------------------------------- Schema Name: SCOTT SQL ID : 8xfufkq3nnuwa SQL Text : select /*+ no_index(emp,PK_EMP) */ count(*) from emp ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 83.31%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_168', task_owner => 'SCOTT', replace => TRUE); DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .000026 .000018 30.76 % CPU Time (s): .000024 .000018 25 % DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- User I/O Time (s): 0 0 Buffer Gets: 6 1 83.33 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- ----- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 2083865914 DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 | ------------------------------------------------------------------- Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 3 (U - Unused (3)) DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------- 0 - STATEMENT U - IGNORE_OPTIM_EMBEDDED_HINTS / hint overridden by another in parent query block U - OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block 2 - SEL$1 / EMP@SEL$1 U - no_index(emp,PK_EMP) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS 2- Using SQL Profile -------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- Plan hash value: 2937609675 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 | ------------------------------------------------------------------- Hint Report (identified by operation id / Query Block Name / Object Alias): DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_168') ---------------------------------------------------------------------------------------------------- Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 2 - SEL$1 / EMP@SEL$1 U - no_index(emp,PK_EMP) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS -------------------------------------------------------------------------------复制
六、sql_profile的导入导出
6.1、检查sql_profile
SQL> col name for a30 SQL> col sql_text for a50 SQL> col STATUS for a10 SQL> select name,sql_text,status from dba_sql_profiles; NAME SQL_TEXT STATUS ------------------------------ -------------------------------------------------- ---------- SYS_SQLPROF_0183f21bc4c50000 select /*+ no_index(dept,pk_dept) */ * from dept w ENABLED here deptno=20 1 row selected. SQL> select /*+ no_index(dept,pk_dept) */ * from dept where deptno=20; Execution Plan ---------------------------------------------------------- Plan hash value: 2852011669 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPTNO"=20) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 U - no_index(dept,pk_dept) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS Note ----- - SQL profile "SYS_SQLPROF_0183f21bc4c50000" used for this statement Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 5 consistent gets 1 physical reads 0 redo size 572 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed复制
6.2、存储SQL概要文件
SQL> begin 2 DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'tmp_profile',schema_name=>'SCOTT'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> begin 2 DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name=>'tmp_profile',profile_name=>'SYS_SQLPROF_0183f21bc4c50000'); 3 end; 4 / PL/SQL procedure successfully completed. --staging_table_name = >存储SQL概要文件的表的名称。 --profile_name = >要打包的SQL概要文件的名称。 --table_name = >存储SQL概要文件的表的名称。 --schema_name = >要在其中创建表的模式的名称。 --table_name和schema_name区分大小写。复制
6.3、目标库创建数据
SQL> create directory dir1 as '/tmp'; Directory created. SQL> create public database link orcl connect to scott identified by oracle using 'ORCL'; Database link created. SQL> select sysdate from dual@orcl; SYSDATE --------- 20-OCT-22 [oracle@19c01 ~]$ impdp scott/tiger@pdbprod1 directory=dir1 schemas=scott table_exists_action=replace network_link=orcl Import: Release 19.0.0.0.0 - Production on Thu Oct 20 05:38:42 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SCOTT"."SYS_IMPORT_SCHEMA_01": scott/********@pdbprod1 directory=dir1 schemas=scott table_exists_action=replace network_link=orcl Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 36.81 MB Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"SCOTT" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "SCOTT"."T" 72511 rows . . imported "SCOTT"."T2" 72602 rows . . imported "SCOTT"."T3" 72603 rows . . imported "SCOTT"."TMP_PROFILE" 3 rows . . imported "SCOTT"."TEST" 10000 rows . . imported "SCOTT"."DEPT" 4 rows . . imported "SCOTT"."EMP" 14 rows . . imported "SCOTT"."SALGRADE" 5 rows . . imported "SCOTT"."BONUS" 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Oct 20 05:39:42 2022 elapsed 0 00:00:58复制
这一步同时也导入了sql_profile的临时表
6.4、目标库测试sql
SQL> select /*+ no_index(dept,pk_dept) */ * from dept where deptno=20; Execution Plan ---------------------------------------------------------- Plan hash value: 3383998547 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO"=20) Statistics ---------------------------------------------------------- 14 recursive calls 18 db block gets 11 consistent gets 0 physical reads 3140 redo size 712 bytes sent via SQL*Net to client 426 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --全表扫描，未使用sql_profile复制
6.5、解包profile临时表
SQL> begin 2 DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TMP_PROFILE',staging_schema_owner => 'SCOTT'); 3 end; 4 / PL/SQL procedure successfully completed.复制
6.6、验证
SQL> select /*+ no_index(dept,pk_dept) */ * from dept where deptno=20; Execution Plan ---------------------------------------------------------- Plan hash value: 2852011669 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPTNO"=20) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 U - no_index(dept,pk_dept) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS Note ----- - SQL profile "SYS_SQLPROF_0183f21bc4c50000" used for this statement Statistics ---------------------------------------------------------- 37 recursive calls 0 db block gets 10 consistent gets 1 physical reads 0 redo size 572 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed --已经使用sql_profile复制
参考文档
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/managing-sql-profiles.html#GUID-C7FE0936-63B8-46EF-A03E-7E59F704606E Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1) Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor (Doc ID 262687.1) How to Move SQL Profiles from One Database to Another (Including to Higher Versions) (Doc ID 457531.1)复制
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
789次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
664次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
592次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
548次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
533次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
510次阅读
2025-04-22 00:20:37
一页概览：Oracle GoldenGate
甲骨文云技术
497次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
471次阅读
2025-04-17 09:30:30
OR+DBLINK的关联SQL优化思路
布衣
383次阅读
2025-05-05 19:28:36
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
382次阅读
2025-04-15 14:48:05
