服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Oracle - Oracle固定执行计划之SQL PROFILE概要文件详解

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

2020-04-03 17:54数据架构师 Oracle

概要文件,就是一份描述如何使用系统的资源(主要是CPU资源)的配置文件,这篇文章主要介绍了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.  测试一

创建表

?
1
2
3
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.

创建索引:

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

收集统计信息:

?
1
2
3
4
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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

?
1
2
3
4
5
6
7
8
9
10
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

运行存储过程如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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的名字

?
1
2
3
4
tpcc@TOADDB> print tuning_task;
TUNING_TASK
----------------------------------------------------------------------------------------------------
TASK_114

查看执行报告

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
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")
-------------------------------------------------------------------------------

接受分析建议

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

接受报告的建议,验证一下如下:

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

执行测试

再执行原先命令如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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.  维护操作

禁用命令
如下:

?
1
2
3
4
5
6
7
begin
dbms_sqltune.alter_sql_profile(
name   => 'SYS_SQLPROF_01534b8309b90000',
attribute_name => 'status',
value   => 'disabled');
end;
/

启用命令
如下:

?
1
2
3
4
5
6
7
begin
dbms_sqltune.alter_sql_profile(
name   => 'SYS_SQLPROF_01534b8309b90000',
attribute_name => 'status',
value   => 'enabled');
end;
/

查看使用的PROFILE

如下:

?
1
SQL>SELECT task_name,status FROMUSER_ADVISOR_TASKS ;

删除PROFILE

?
1
2
3
BEGIN
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01534b8309b90000');
END;

总结

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

原文链接:https://www.cnblogs.com/DataArt/archive/2018/10/31/9881852.html

延伸 · 阅读

精彩推荐