sql_profile的使用(一)

作者：我并没有你们想象P的坚强 | 来源：互联网 | 2023-06-29 13:53

今天看了老熊关于sql_profile的讲解，受益匪浅，自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。准备的数据如下

今天看了老熊关于sql_profile的讲解&＃xff0c;受益匪浅&＃xff0c;自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。
准备的数据如下&＃xff0c;创建两个表&＃xff0c;一个大&＃xff0c;一个小&＃xff0c;然后做表分析
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<&＃61;50000;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create index t2_idx on t2(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,&＃39;t1&＃39;,cascade&＃61;>true,method_opt&＃61;>&＃39;for all columns size 1&＃39;);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,&＃39;t2&＃39;,cascade&＃61;>true,method_opt&＃61;>&＃39;for all columns size 1&＃39;);
PL/SQL procedure successfully completed.

不加任何hint,查看执行计划,可以看到两个表都走了全表扫描。
SQL> set autot trace exp stat
SQL> set linesize 200
SQL> set pages 100
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like &＃39;%T1%&＃39; and t1.object_id&＃61;t2.object_id;
26 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 112K| 122 |
|* 1 | HASH JOIN | | 2500 | 112K| 122 |
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 77500 | 8 |
| 3 | TABLE ACCESS FULL| T2 | 269K| 3952K| 107 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"&＃61;"T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE &＃39;%T1%&＃39; AND "T1"."OBJECT_NAME" IS
NOT NULL)
Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4477 consistent gets
0 physical reads
0 redo size
1669 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed

如果手动调优&＃xff0c;加入Hint&＃xff0c;可以参考如下的形式&＃xff0c;可以看到性能有了成倍的提升。

SQL> select /*&＃43; use_nl(t1 t2) index(t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like &＃39;%T1%&＃39;
and t1.object_id&＃61;t2.object_id; 4
26 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 112K| 258 |
| 1 | NESTED LOOPS | | | | |
| 2 | NESTED LOOPS | | 2500 | 112K| 258 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 77500 | 8 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE &＃39;%T1%&＃39; AND "T1"."OBJECT_NAME" IS
NOT NULL)
4 - access("T1"."OBJECT_ID"&＃61;"T2"."OBJECT_ID")

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
323 consistent gets
0 physical reads
0 redo size
1669 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed

下面来根据sql_id来进行调优&＃xff0c;试试sql_profile给出的见解。先从缓存中查出刚才执行的sql语句。

SQL> select sql_id,sql_text from v$sql where sql_text like &＃39;%t1.object_name%&＃39;
2 /
SQL_ID
。。。。
4zbqykx89yc8v
select t1.*,t2.owner from t1,t2 where t1.object_name like &＃39;%T1%&＃39; and t1.object_id&＃61;t2.object_id
2pxr40u2zm0ja
select sql_id,sql_text from v$sql where sql_text like &＃39;%t1.object_name%&＃39;

然后运行下面的存储过程&＃xff0c;执行sqltune task.
SQL> var tuning_task varchar2(100);
SQL> DECLARE
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task VARCHAR2(30);
BEGIN
l_sql_id:&＃61;&＃39;4zbqykx89yc8v&＃39;;
l_tuning_task :&＃61; dbms_sqltune.create_tuning_task(sql_id &＃61;> l_sql_id);
:tuning_task:&＃61;l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
END;
/

PL/SQL procedure successfully completed.

查看task的name
SQL> print tuning_task;
TUNING_TASK
--------------------------------------------------------------------------------------------------------------------------------
TASK_12352

如果sql语句本身不复杂&＃xff0c;涉及的表不大的话&＃xff0c;执行是很快的。如下查看报告。
SQL> set long 99999
SQL> col comments format a200
SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual;
COMMENTS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_12352
Tuning Task Owner : N1
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 07/10/2014 15:04:18
Completed at : 07/10/2014 15:04:20

Schema Name: N1
SQL ID : 4zbqykx89yc8v
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like &＃39;%T1%&＃39;
and t1.object_id&＃61;t2.object_id

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 92.9%)
-----------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name &＃61;> &＃39;TASK_12352&＃39;,task_owner &＃61;> &＃39;N1&＃39;, replace &＃61;> 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): .042304 .006329 85.03 %
CPU Time (s): .042293 .006399 84.86 %
User I/O Time (s): 0 0
Buffer Gets: 4475 317 92.91 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 26 26
Fetches: 26 26
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 | | 26 | 1196 | 796 (1)| 00:00:10 |
|* 1 | HASH JOIN | | 26 | 1196 | 796 (1)| 00:00:10 |
|* 2 | TABLE ACCESS FULL| T1 | 26 | 806 | 47 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 269K| 3952K| 748 (1)| 00:00:09 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"&＃61;"T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE &＃39;%T1%&＃39; 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 | | 26 | 1196 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 26 | 1196 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 26 | 806 | 47 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE &＃39;%T1%&＃39; AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"&＃61;"T2"."OBJECT_ID")

-------------------------------------------------------------------------------
可以从报告看出&＃xff0c;改进确实是很客观的&＃xff0c;提升了90%以上。

来简单验证一下&＃xff0c;先得accept 一下。
SQL> execute dbms_sqltune.accept_sql_profile(task_name &＃61;> &＃39;TASK_12352&＃39;,task_owner &＃61;> &＃39;N1&＃39;, replace &＃61;> TRUE);
PL/SQL procedure successfully completed.

再来查询一下&＃xff0c;看看是否启用了profile
SQL> set autot trace exp stat
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like &＃39;%T1%&＃39; and t1.object_id&＃61;t2.object_id
/
26 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 26 | 1196 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 26 | 806 | 47 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE &＃39;%T1%&＃39; AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"&＃61;"T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_01471f52938e0000" used for this statement

Statistics
----------------------------------------------------------
34 recursive calls
1 db block gets
338 consistent gets
3 physical reads
196 redo size
1669 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
26 rows processed

再来看看如果改动了sql语句&＃xff0c;多加了些空格&＃xff0c;看看profile还能不能正常启用。
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like &＃39;%T1%&＃39; and t1.object_id&＃61; t2.object_id
2 /
26 rows selected.
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 26 | 1196 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 26 | 806 | 47 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE &＃39;%T1%&＃39; AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"&＃61;"T2"."OBJECT_ID")

Note
-----
- SQL profile "SYS_SQLPROF_01471f52938e0000" used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
323 consistent gets
0 physical reads
0 redo size
1669 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed

可以看到&＃xff0c;还是正常启用了。另外&＃xff0c;库里的cursor_sharing参数如下。
SQL> show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory&＃43;disk
cursor_sharing string EXACT

所以在使用中&＃xff0c;对于sql调优来说还是可以尝试使用sql_profile的&＃xff0c;确实提供了不少的知识集。

来自 “ ITPUB博客 ” &＃xff0c;链接&＃xff1a;http://blog.itpub.net/23718752/viewspace-1215257/&＃xff0c;如需转载&＃xff0c;请注明出处&＃xff0c;否则将追究法律责任。

转:http://blog.itpub.net/23718752/viewspace-1215257/

推荐阅读

format
mysql join 算法_【MySQL】之join算法详解

在阿里巴巴的java开发手册有这么一条强制规定：超过三个表禁止join，须要join的字段，数据类型保持绝对一致，多表关联查 ... [详细]

蜡笔小新 2024-09-27 13:57:57
list
Java如何快速定位无效字符,mybatis的报错…ORA00911: 无效字符,该怎么解决

mybatis的报错……ORA-00911:无效字符xml里的配置resultTypejava.lang.Stringselectt.sfzhfromt_ldrktandt. ... [详细]

蜡笔小新 2024-09-30 14:45:30
select
SQLSERVER数据处理刷新组织层级

–声明变量DECLAREBuCodeASvarchar(max)–声明游标DECLAREC_EmployeesCURSORFAST_FORWARDFORSELECTBUCodeFR ... [详细]

蜡笔小新 2024-09-30 10:08:21
heap
外层|条件下_MySQL还能这样玩第五篇之视图应该这样玩

篇首语：本文由编程笔记#小编为大家整理，主要介绍了MySQL还能这样玩---第五篇之视图应该这样玩相关的知识，希望对你有一定的参考价值。 ... [详细]

蜡笔小新 2024-09-29 16:30:33
select
故障申报系统php源码,运维不再专业救火不会PHP照样找出代码性能问题

作者:凉白开网站：www.ttlsa.com身处互联网的SA(运维)们总感叹自己职业的苦逼，Why？我来告诉你：APP奔溃、 ... [详细]

蜡笔小新 2024-09-28 23:22:26
python
Python多进程遇到的问题

多进程共享对象我有一个IpConnectionPool对象需要多个进程共享创建BaseManager注册Ip ... [详细]

蜡笔小新 2024-09-28 08:53:08
list
TLB 缓存延迟刷新漏洞 CVE201818281 解析

TLB 缓存延迟刷新漏洞 CVE201818281 解析 ... [详细]

蜡笔小新 2024-09-27 17:53:48
select
ORACLE常用SQL技巧

2019独角兽企业重金招聘Python工程师标准ORACLE常用SQL技巧1.SELECT子句中避免使用“*”当你想在SELECT子句中列出所有的COLUMN时,使用动态SQ ... [详细]

蜡笔小新 2024-09-26 16:53:56
byte
从分布式数据库选型的第一件事谈起

本文很长，谨慎阅读现在在我们的面前摆着太多的分布式数据库可以让我们选择，那么如果我想先让 ... [详细]

蜡笔小新 2024-09-26 15:36:10
get
CentOS8.4 安装PHP7.4.25

1、下载官网：https:www.php.netdownloads#cdmydata#wgethttps:www.php.netdistributionsphp ... [详细]

蜡笔小新 2024-09-30 19:32:33
数组
重学数据结构之链表篇

本文是重学数据结构系列文章的第二篇，本文和大家一起探讨链表的相关知识。重学数据结构之数组篇文章目录链表是怎么样的数据结构链表的特点常见的链表结构单链表双向链表循环链表链表or数组链 ... [详细]

蜡笔小新 2024-09-30 13:26:42
select
STM32F时钟设置

HSE8MHz。配置前将所有RCC重置为初始值RCC_DeInit();*这里选择外部晶振（HSE）作为时钟源，因此首先打开外部晶振*RC ... [详细]

蜡笔小新 2024-09-30 12:55:37
bit
GPS 校验和代码_Linux recovery 移除签名校验

原创作者：王锐，多年Linux系统、龙芯平台移植与优化研发经验，LinuxContributor、Mozillian。背景某个设备配套的刷 ... [详细]

蜡笔小新 2024-09-29 12:43:54
list
结对编程地铁最短路径张波朱新远

结对编程地铁最短路径一、任务：实现一个帮助进行地铁出行路线规划的命令行程序。PSP2.1PersonalSoftwareProcessStagesTimePlanni ... [详细]

蜡笔小新 2024-09-27 17:32:27
object
SciKitLearn标签编码器导致错误“参数必须是字符串或数字”

我有点困惑-在这里创建ML模型。我正在尝试从“大”数据框（180列）中获 ... [详细]

蜡笔小新 2024-09-26 18:39:21

我并没有你们想象P的坚强

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

Tags | 热门标签

RankList | 热门文章