Oracle固定执行计划之SQL PROFILE概要文件详解-木庄网络博客

本文整理自网络，侵删。

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

阅读剩余部分