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; 

/  

---------------------

作者：badman250

来源：CSDN

原文：https://blog.csdn.net/notbaron/article/details/50830910

版权声明：本文为博主原创文章，转载请附上博文链接！