当前位置: 开发笔记 > 数据库 > 正文

Oracle固定执行计划之SQLPROFILE概要文件详解

作者：悟道山水_805 | 来源：互联网 | 2021-09-19 00:24

概要文件，就是一份描述如何使用系统的资源(主要是CPU资源)的配置文件，这篇文章主要介绍了Oracle固定执行计划之SQLPROFILE概要文件,需要的朋友可以参考下

1. 引子

Oracle系统为了合理分配和使用系统的资源提出了概要文件的概念。所谓概要文件，就是一份描述如何使用系统的资源(主要是CPU资源)的配置文件。将概要文件赋予某个数据库用户，在用户连接并访问数据库服务器时，系统就按照概要文件给他分配资源。

包括:

1、管理数据库系统资源。

利用Profile来分配资源限额，必须把初始化参数resource_limit设置为true默认是TRUE的。

2、管理数据库口令及验证方式。

默认给用户分配的是DEFAULT概要文件，将该文件赋予了每个创建的用户。但该文件对资源没有任何限制，因此管理员常常需要根据自己数据库系统的环境自行建立概要文件。

2. 概要文件限制

概要文件主要可以对数据库系统如下指标进行限制。

1）用户的最大并发会话数（SESSION_PER_USER)

2）每个会话的CPU时钟限制（CPU_PER_SESSION）

3）每次调用的CPU时钟限制，调用包含解析、执行命令和获取数据等等。（CPU_PER_CALL）

4）最长连接时间。一个会话的连接时间超过指定时间之后，Oracle会自动的断开连接（CONNECT_TIME）

5）最长空闲时间。如果一个会话处于空闲状态超过指定时间，Oracle会自动断开连接（IDLE_TIME）

6）每个会话可以读取的最大数据块数量（LOGICAL_READS_PER_SESSION）

7）每次调用可以读取的最大数据块数量（LOGICAL_READS_PER_CALL）

8）SGA私有区域的最大容量（PRIVATE_SGA）

概要文件对口令的定义和限制如下：

1）登录失败的最大尝试次数（FAILED_LOGIN_ATTEMPTS）

2）口令的最长有效期(PASSWORD_LIFE_TIME)

3）口令在可以重用之前必须修改的次数(PASSWORD_REUSE_MAX)

4）口令在可以重用之前必须经过的天数(PASSWORD_REUSE_TIME)

5）超过登录失败的最大允许尝试次数后，账户被锁定的天数

6）指定用于判断口令复杂度的函数名

在指定概要文件之后，DBA可以手工的将概要文件赋予每个用户。但是概要文件不是立即生效，而是要将初始化参数文件中的参数RESOURCE_LIMIT设置为TRUE之后，概要文件才会生效。

3. SQL PROFILE

SQL PROFILE在ORACLE10g中引入，主要目的侧重于SQL优化，弥补了存储概要的缺点.

DBA可以使用SQL调整顾问(STA)或SQL访问顾问(SAA)来识别可以得到更好性能的SQL语句，

这些语句可以保存在SQL调整集、一个AWR快照或保存在当前的库缓存中，一旦识别出调整候选者，这些顾问程序就开始分析捕获到的语句以期获得更好的性能，然后生成专用的语句扩展(就叫做SQL配置文件)并重写SQL语句以在执行期间获取更佳的性能。

与存储概要类似，一个SQL配置文件提供了使用更好的执行计划的能力(如果这个执行计

划是可用的)，SQL配置文件也可以象存储概要一样分阶段执行，或限制到对特定会话才能执行该SQL配置文件，但是大多数重要的改进超过了存储概要.

SQLProfile对于一下类型语句有效：

SELECT语句；

UPDATE语句；

INSERT语句（仅当使用SELECT子句时有效）；

DELETE语句；

CREATE语句（仅当使用SELECT子句时有效）；

MERGE语句（仅当作UPDATE和INSERT操作时有效）。

另外，使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。

4. 测试一

创建表

tpcc@TOADDB> create table t1 as selectobject_id,object_name from dba_objects where rownum<=50000; 
Table created.
tpcc@TOADDB> create table t2 as select * fromdba_objects; 
Table created.

创建索引：

tpcc@TOADDB> create index t2_idx on t2(object_id);
Index created.

收集统计信息：

tpcc@TOADDB> execdbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'forall columns size 1');
PL/SQL procedure successfully completed.
tpcc@TOADDB> execdbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'forall columns size 1');
PL/SQL procedure successfully completed.

执行无HINT的SQL

tpcc@TOADDB> set autotrace on
tpcc@TOADDB> select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id;
42 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation  | Name | Rows | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT |  | 2500 | 97K| 498 (1)| 00:00:01 |
|* 1| HASH JOIN   |   | 2500 | 97K| 498 (1)| 00:00:01 |
|* 2| TABLE ACCESS FULL| T1 | 2500 | 72500 | 68 (0)| 00:00:01 |
| 3| TABLE ACCESS FULL| T2 | 92021 | 988K| 430 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 2- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS
    NOT NULL)
Statistics
----------------------------------------------------------
   1 recursive calls
   0 dbblock gets
  1789 consistent gets
   0 physical reads
   0 redosize
  2350 bytes sent via SQL*Net toclient
   573 bytes received via SQL*Net from client
   4 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   42 rowsprocessed

执行带Hint的SQL

SQL>select /*+ use_nl(t1 t2) index(t2)*/ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
42 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)|Time  |
---------------------------------------------------------------------------------------
| 0| SELECT STATEMENT  |   | 2500 | 97K| 5069   (1)|00:00:01 |
| 1| NESTED LOOPS      |   | 2500| 97K| 5069   (1)| 00:00:01 |
| 2| NESTED LOOPS      |   | 2500 | 97K| 5069   (1)| 00:00:01 |
|* 3| TABLE ACCESS FULL   | T1  | 2500 | 72500 | 68 (0)|00:00:01 |
|* 4| INDEX RANGE SCAN   |T2_IDX |  1 |  | 1 (0)| 00:00:01 |
| 5| TABLE ACCESS BY INDEX ROWID| T2  | 1 | 11 |  2  (0)|00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 3- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS NOT
    NULL)
 4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
   1 recursive calls
   0 dbblock gets
   304 consistent gets
   24 physical reads
   0 redosize
  2350 bytes sent via SQL*Net toclient
   573 bytes received via SQL*Net from client
   4 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   42 rowsprocessed

使用SQL PROFILE

查找执行SQL的SQL_ID

tpcc@TOADDB> select sql_id,sql_text from v$sqlwhere sql_text like '%t1.object_name%';
SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
4zbqykx89yc8v
select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id
18bphz37dajq9
select /*+ use_nl(t1 t2) index(t2) */t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and
t1.object_id=t2.object_id

运行存储过程如下：

var tuning_task varchar2(100); 
DECLARE 
 l_sql_id v$session.prev_sql_id%TYPE; 
 l_tuning_task VARCHAR2(30); 
 BEGIN 
 l_sql_id:='4zbqykx89yc8v'; 
 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id =>l_sql_id); 
 :tuning_task:=l_tuning_task; 
 dbms_sqltune.execute_tuning_task(l_tuning_task); 
 dbms_output.put_line(l_tuning_task); 
 END;
/ 

TASK_114

PL/SQL procedure successfully completed.

查看task的名字

tpcc@TOADDB> print tuning_task;
TUNING_TASK
----------------------------------------------------------------------------------------------------
TASK_114

查看执行报告

set long 99999
col comments format a200
SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual; 
COMMENTS
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_114
Tuning Task Owner : TPCC
Workload Type : Single SQL Statement
Scope     : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at   : 03/06/2016 05:27:21
Completed at  : 03/06/2016 05:27:24
-------------------------------------------------------------------------------
Schema Name: TPCC
SQL ID   : 4zbqykx89yc8v
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
    and t1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain planssection below)
--------------------------------------------------------
 Apotentially better execution plan was found for this statement.
 Recommendation (estimated benefit: 83.08%)
 ------------------------------------------
 -Consider accepting the recommended SQL profile.
 executedbms_sqltune.accept_sql_profile(task_name => 'TASK_114',
    task_owner =>'TPCC', replace => TRUE);
 Validation results
 ------------------
 TheSQL profile was tested by executing both its plan and the original plan
 andmeasuring their respective execution statistics. A plan may have been
 only partially executed if the other could be run to completion in lesstime.
        Original Plan With SQL Profile % Improved
        ------------- ---------------- ----------
 Completion Status:    COMPLETE  COMPLETE
 Elapsed Time (s):   .012865 .004556  64.58 %
 CPUTime (s):     .0124  .0045  63.7%
 User I/O Time (s):      0     0
 Buffer Gets:        1787    302  83.1%
 Physical Read Requests:   0     0
 Physical Write Requests:   0     0
 Physical Read Bytes:     0     0
 Physical Write Bytes:    0     0
 Rows Processed:    42     42
 Fetches:        42     42
 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: 1838229974
---------------------------------------------------------------------------
| Id | Operation  | Name | Rows | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT |  | 42 | 1680 | 498 (1)| 00:00:01 |
|* 1| HASH JOIN   |   | 42 | 1680 | 498 (1)| 00:00:01 |
|* 2| TABLE ACCESS FULL| T1 | 42 | 1218 | 68 (0)| 00:00:01 |
| 3| TABLE ACCESS FULL| T2 | 92021 | 988K| 430 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 2- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS
    NOT NULL)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)|Time  |
---------------------------------------------------------------------------------------
| 0| SELECT STATEMENT  |   | 42 | 1680 | 152 (0)|00:00:01 |
| 1| NESTED LOOPS      |   | 42 | 1680 | 152 (0)| 00:00:01 |
| 2| NESTED LOOPS      |   | 42 | 1680 | 152 (0)| 00:00:01 |
|* 3| TABLE ACCESS FULL   | T1  | 42 | 1218 | 68 (0)| 00:00:01 |
|* 4| INDEX RANGE SCAN   |T2_IDX |  1 |  | 1 (0)| 00:00:01 |
| 5| TABLE ACCESS BY INDEX ROWID| T2  | 1 | 11 |  2  (0)|00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 3- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS NOT
    NULL)
 4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------

接受分析建议

报告中给出了执行方法，如上红色部分

接受报告的建议，验证一下如下：

tpcc@TOADDB> execute dbms_sqltune.accept_sql_profile(task_name=> 'TASK_114',task_owner => 'TPCC', replace => TRUE);
PL/SQL procedure successfully completed.

执行测试

再执行原先命令如下：

tpcc@TOADDB> select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id;
42 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)|Time  |
---------------------------------------------------------------------------------------
| 0| SELECT STATEMENT  |   | 42 | 1680 | 152 (0)|00:00:01 |
| 1| NESTED LOOPS      |   | 42| 1680 | 152 (0)|00:00:01 |
| 2| NESTED LOOPS      |   | 42 | 1680 | 152 (0)| 00:00:01 |
|* 3| TABLE ACCESS FULL   | T1  | 42 | 1218 | 68 (0)| 00:00:01 |
|* 4| INDEX RANGE SCAN   |T2_IDX |  1 |  | 1 (0)| 00:00:01 |
| 5| TABLE ACCESS BY INDEX ROWID| T2  | 1 | 11 |  2  (0)|00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 3- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS NOT
    NULL)
 4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
 - SQL profile"SYS_SQLPROF_01534b8309b90000" used for this statement
 - this is an adaptive plan
Statistics
----------------------------------------------------------
   35 recursive calls
   0 dbblock gets
   317 consistent gets
   1 physical reads
   0 redosize
  2350 bytes sent via SQL*Net toclient
   573 bytes received via SQL*Net from client
   4 SQL*Net roundtrips to/from client
   1 sorts (memory)
   0 sorts (disk)
   42 rowsprocessed

启用了PROFILE，PS：如果执行中多加几个空格，并不会影响PROFILE的生效的。

5. 维护操作

禁用命令
如下：

begin 
dbms_sqltune.alter_sql_profile( 
name   => 'SYS_SQLPROF_01534b8309b90000', 
attribute_name => 'status', 
value   => 'disabled'); 
end; 
/

启用命令
如下：

begin 
dbms_sqltune.alter_sql_profile( 
name   => 'SYS_SQLPROF_01534b8309b90000', 
attribute_name => 'status', 
value   => 'enabled'); 
end; 
/

查看使用的PROFILE

如下：

SQL>SELECT task_name,status FROMUSER_ADVISOR_TASKS ;

删除PROFILE

BEGIN 
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01534b8309b90000'); 
END;

总结

以上所述是小编给大家介绍的Oracle固定执行计划之SQL PROFILE概要文件，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

推荐阅读

数据库
优化Flask应用的并发处理：解决Mysql连接过多问题

本文探讨了在Flask应用中通过优化后端架构来应对高并发请求，特别是针对Mysql 'too many connections' 错误的解决方案。我们将介绍如何利用Redis缓存、Gunicorn多进程和Celery异步任务队列来提升系统的性能和稳定性。 ... [详细]

蜡笔小新 2024-12-21 09:21:49
数据库
PostgreSQL 最新动态 —— 2022年4月6日

了解 PostgreSQL 社区的最新进展和技术分享 ... [详细]

蜡笔小新 2024-12-21 14:54:10
数据库
使用JS、HTML5和C3创建自定义弹出窗口

本文介绍如何结合JavaScript、HTML5和C3.js来实现一个功能丰富的自定义弹出窗口。通过具体的代码示例，详细讲解了实现过程中的关键步骤和技术要点。 ... [详细]

蜡笔小新 2024-12-20 21:22:27
数据库
主调|大侠_重温C++

主调|大侠_重温C++ ... [详细]

蜡笔小新 2024-12-20 20:43:56
数据库
Java SpringMVC SSM 实现多模块集成：操作日志、文件管理、头像编辑、权限控制及缓存优化

本文介绍了一个基于 Java SpringMVC 和 SSM 框架的综合系统，涵盖了操作日志记录、文件管理、头像编辑、权限控制、以及多种技术集成如 Shiro、Redis 等，旨在提供一个高效且功能丰富的开发平台。 ... [详细]

蜡笔小新 2024-12-20 19:17:47
数据库
备战BAT面试：掌握这些MySQL核心问题

本文深入探讨了MySQL中常见的面试问题，包括事务隔离级别、存储引擎选择、索引结构及优化等关键知识点。通过详细解析，帮助读者在面对BAT等大厂面试时更加从容。 ... [详细]

蜡笔小新 2024-12-20 18:58:01
数据库
MySQL中Bin Log与Redo Log的区别及应用场景

本文详细介绍了MySQL数据库中的Bin Log和Redo Log，阐述了它们在日志记录机制、应用场景以及数据恢复方面的区别。通过对比分析，帮助读者更好地理解这两种日志文件的作用和特性。 ... [详细]

蜡笔小新 2024-12-21 13:50:51
数据库
SP框架数据操作指南

本章详细介绍SP框架中的数据操作方法，包括数据查找、记录查询、新增、删除、更新、计数及字段增减等核心功能。通过具体示例和详细解析，帮助开发者更好地理解和使用这些方法。 ... [详细]

蜡笔小新 2024-12-21 13:27:32
sql
SQL Server 登录验证模式的修改方法：混合验证模式详解

在安装 SQL Server 时，选择混合验证模式可以提供更高的灵活性和管理便利性。如果您已经安装了 SQL Server 并使用单一的 Windows 身份验证模式，可以通过以下步骤将其更改为混合验证模式。 ... [详细]

蜡笔小新 2024-12-21 11:32:52
sql
SQL Server 2012 客户端 SQL 格式化工具与方法

本文介绍了在 SQL Server 2012 客户端中格式化 SQL 查询语句的多种方法，包括内置功能和第三方工具，帮助用户提高代码可读性和维护性。 ... [详细]

蜡笔小新 2024-12-21 10:59:16
数据库
SQL Server 中 INSERT 语句无法插入数据的问题分析与解决

本文探讨了在 SQL Server 中使用 JDBC 插入数据时遇到的问题。通过详细分析代码和数据库配置，提供了解决方案并解释了潜在的原因。 ... [详细]

蜡笔小新 2024-12-21 09:52:27
数据库
优化SQL Server批量数据插入存储过程的实现

本文介绍了一种改进的SQL Server存储过程，用于生成批量插入语句。该方法不仅提高了性能，还支持单行和多行模式，适用于SQL Server 2005及以上版本。 ... [详细]

蜡笔小新 2024-12-21 06:43:52
sql
使用正则表达式去除字符串中单词间的空格

本文探讨了如何在Hive（基于Hadoop）环境中编写类似SQL的语句，以去除字段中的空格。特别是在处理邮政编码等数据时，去除特定位置的空格是常见的需求。 ... [详细]

蜡笔小新 2024-12-20 19:08:43
数据库
SQL Server 错误 18470：用户 'sa' 登录失败，原因：帐户被禁用的解决方案

本文详细介绍了如何解决 Microsoft SQL Server 中用户 'sa' 登录失败的问题。错误代码为 18470，提示该帐户已被禁用。我们将通过 Windows 身份验证方式登录，并启用 'sa' 帐户以恢复其访问权限。 ... [详细]

蜡笔小新 2024-12-20 18:41:13
数据库
配置PHPStudy环境并使用DVWA进行Web安全测试

本文详细介绍了如何在PHPStudy环境下配置DVWA（ Damn Vulnerable Web Application ），并利用该平台进行SQL注入和XSS攻击的练习。通过此过程，读者可以熟悉常见的Web漏洞及其利用方法。 ... [详细]

蜡笔小新 2024-12-20 18:22:20

悟道山水_805

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章