整套解决方案:熊猫智能采集监测软件
优采云 发布时间: 2022-11-22 17:23整套解决方案:熊猫智能采集监测软件
优财云采集器
软件是新一代采集软件，全程鼠标操作。用户无需关心网页源代码，编写采集规则，使用正则表达式技术。全程智能辅助，是采集软件行业的更新换代产品。
基本介绍
" />
如果您不能通过熊猫软件解决您的采集
需求，最可能的原因是您还不熟悉熊猫的功能和操作。优财云采集器
软件是新一代采集软件，全程鼠标操作。用户无需关心网页源代码，编写采集规则，使用正则表达式技术。全程智能辅助，是采集软件行业的更新换代产品。同时它也是一款通用的采集软件，可应用于各行业，满足各种采集需求（包括站群系统）。是复杂采集需求的必备之选，也是新手使用采集软件的首选。
软件特色
" />
优财云采集器
软件与同类软件最大的区别在于它功能强大，同时操作简单，类似于从DOS操作系统切换到windows操作系统。前者需要专业技术人员才能有效操作，而Panda则是面向大众的可视化操作平台。
本产品为免费安全软件，欢迎有需要的朋友到188soft软件园下载使用。
解决方案:oracle sql_profile文件基本管理操作及调用sql调优助手
1、什么是SQL配置文件？
SQL 配置文件是存储在数据字典中的信息集合，它使查询优化器能够为 SQL 语句创建最佳执行计划。SQL 配置文件收录
对自动 SQL 调优期间发现的优化器估计不佳的更正。这些信息可以提高优化器的基数和选择性估计，进而指导优化器选择更好的计划。
SQL 配置文件不收录
有关各个执行计划的信息。相反，优化器在选择计划时具有以下信息来源：
重要的是要注意 SQL Profile 不会像存储的配置文件那样冻结 SQL 语句的执行计划。随着表的增长或索引的创建或删除，可以使用相同的 SQL 配置文件更改执行计划。即使相应语句的数据分布或访问路径发生变化，其中存储的信息仍然具有相关性。但是，时间长了，其内容可能会过时，必须重新生成。这可以通过在同一语句上再次运行 Auto SQL Tuning 重新生成 SQL 配置文件来完成。
二、如何控制SQL Profile的范围？
SQL 配置文件的范围可以由 CATEGORY 配置文件属性控制。此属性确定哪些用户会话可以应用配置文件。SQL Profile 的 CATEGORY 属性可以在 DBA_SQL_PROFILES 视图的 CATEGORY 列中查看。
SQL> select * from DBA_SQL_PROFILES;
no rows selected
默认情况下，所有配置文件都创建在 DEFAULT 类别中。这意味着所有将 SQLTUNE_CATEGORY 初始化参数设置为 DEFAULT 的用户会话都可以使用该配置文件。
通过更改 SQL 配置文件的类别，您可以确定哪些会话受到配置文件创建的影响。例如，通过将 SQL Profile 的类别设置为 DEV，只有那些 SQLTUNE_CATEGORY 初始化参数设置为 DEV 的用户会话才能使用该配置文件。所有其他会话无权访问 SQL profile，SQL 语句的执行计划不受 SQL profile 的影响。此技术使您能够在受限环境中测试 SQL 配置文件，然后再将它们提供给其他用户会话。
3、SQL Profile适用于哪些语句？
选择语句
更新语句
INSERT 语句（仅带有 SELECT 子句）
删除语句
CREATE TABLE 语句（仅限 AS SELECT 子句）
MERGE 语句（更新或插入操作）
四、sql配置文件管理 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. 接受资料
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. 修改资料
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
<p>
" />
------------------------------ -------------------------------------------------- ----------
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
</p>
4.5、删除*敏*感*词*
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
5、调用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')
<p>
" />
----------------------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------
</p>
6、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. 解压配置文件临时表
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)
