扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >如何手工创建SQL Profile
  • 945

0
分享到

如何手工创建SQL Profile

2024-04-02 19:04:59 945人浏览 八月长安
摘要

本篇内容介绍了“如何手工创建sql Profile”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！手工创建S

本篇内容介绍了“如何手工创建sql Profile”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

手工创建SQL profile

oracle 10G版本，可以通过查看sys.sqlprof$、sys.sqlprof$attr来获得SQL Profile使用的hint，但是11G后这两个数据字典基表不再有效，需要通过查看sys.sqlobj$data、sys.sqlobj$来查看SQL Profile使用的hint。

我们继续接着上面一节，看看通过SQL Tuning Advisor创建的SQL Profile使用到的hint。（11G版本）

SQL>SELECT extractValue(value(h),'.') AS hint                                  

  2  FROM sys.sqlobj$data od, sys.sqlobj$ so,                                   

  3  table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h  

  4  WHERE so.name = 'SYS_SQLPROF_01479094feeb0003'                             

  5  AND so.signature = od.signature                                            

  6  AND so.cateGory = od.category                                              

  7  AND so.obj_type = od.obj_type                                              

  8  AND so.plan_id = od.plan_id;                                               

 

hint

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

OPT_ESTIMATE(@"SEL$1", TABLE, "TEST"@"SEL$1", SCALE_ROWS=0.004)

OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "TEST"@"SEL$1", "T_IND", SCALE_ROWS=0.004)

OPTIMIZER_FEATURES_ENABLE(default)

这些hint都不是我们日常所用的hint，大部分是以OPT_ESTIMATE打头的，例如OPT_ESTIMATE(@"SEL$1", TABLE, "TEST"@"SEL$1", SCALE_ROWS=0.004)代表的是把表test经过谓词过滤后返回的基数修正为原始评估的基数乘以0.004，也就是缩小了250倍：基数从25000缩小为100。按照OPT_ESTIMATE提示缩小后的基数非常的准确，由于OPT_ESTIMATE告诉了优化器非常准确的基数信息，因此优化器再次评估执行计划的时候选择了索引扫描。

就如我们看到的SQL Profile并没有明确的告诉优化器使用索引扫描，只是告诉它应该如何纠正优化器的原始评估，以得到更好的基数信息。但是随着时间的推移，这些提示信息可能会变得过时，最终变得不再有效，因此使用了SQL Profile的SQL也可能会遭遇执行计划发生变化，没起到锁定执行计划的作用。本章后面会介绍如何让SQL Profile起到锁定执行计划的作用。

n Note：SQL Profile里可能会包含哪些hint？这里对SQL Profile里一些常出现的hint做出解释。

1) OPT_ESTIMATE(@SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10)

返回10倍于预估的表的基数

2) OPT_ESTIMATE(@SEL$1, INDEX_SCAN, TEST@SEL$1, TEST_IDX, SCALE_ROWS=.1)

返回十分之一的预估的索引的基数

3) OPT_ESTIMATE(@SEL$1, JOIN, (TEST1@SEL$1,TEST2@SEL$1),SCALE_ROWS=4.2)

当test1,test2做join时，返回4.2倍与预估的基数

4) TABLE_STATS(“HR”, “EMPLOYEES”, scale, blocks=10, rows=107)

为表提供统计信息：如行数、块数

5) COLUMN_STATS(“HR”, “EMPLOYEES”, “EMPLOYEE_ID”, scale,length=3 DISTINCT=107 nulls=0 min=100 max=207)

为表上的列提供统计信息：如空值、最大值、最小值等

6) INDEX_STATS(“HR”, “EMPLOYEES”, “EMP_IDX”, scale, blocks=5, rows=107)

为索引提供统计信息：如索引块数、索引条目数

7) ALL_ROWS

设置优化器的模式为ALL_ROWS

8) IGNORE_OPTIM_EMBEDDED_hintS

忽略嵌入在SQL里的hint

虽然ORACLE官方只提供了通过SQL Tuning Advisor来创建SQL Profile，但是一些ORACLE的爱好者慢慢的发现了SQL Tuning Advisor底层的运作机制，发现SQL Tuning Advisor其实是通过调用dbms_sqltune包的import_sql_profile来创建的SQL Profile。通过import_sql_profile过程，可以为任何的SQL创建想要的SQL Profile。我们来看看import_sql_profile如何使用。

PROCEDURE IMPORT_SQL_PROFILE

 Argument Name                  Type                    In/Out Default?

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

 SQL_TEXT                       CLOB                    IN

 PROFILE                        SQLPROF_ATTR            IN

 NAME                           VARCHAR2                IN     DEFAULT

 DESCRIPTioN                    VARCHAR2                IN     DEFAULT

 CATEGORY                       VARCHAR2                IN     DEFAULT

 VALIDATE                       BOOLEAN                 IN     DEFAULT

 REPLACE                        BOOLEAN                 IN     DEFAULT

 FORCE_MATCH                    BOOLEAN                 IN     DEFAULT

使用IMPORT_SQL_PROFILE来创建SQL Profile需要提供一些参数，SQL_TEXT指SQL语句的文本，我们可以从v$sqlarea的sql_fulltext中获得SQL语句的完整文本信息，PROFILE指的是需要为这个SQL文本绑定的hint集合，name为SQL Profile的名称，DESCRIPTION为对SQL Profile的描述信息，CATEGORY为SQL Profile所属的类信息，默认为default，VALIDATE代表创建的SQL Profile是否有效，默认为true, REPLACE代表是否取代之前存在的SQL Profile，FORCE_MATCH代表采用何种文本标准化方式产生签名，默认为false。关于FORCE_MATCH的意义，在本章文本标准化与signature一节有详细解释。我们来手工创建一个SQL Profile看看：                     

SQL>exec dbms_sqltune.drop_sql_profile('profile_c37q7z5qjnwwf_dwrose');

 

PL/SQL procedure successfully completed.

 

SQL>declare                                                                   

  2     l_profile_name varchar2(30);                                           

  3     cl_sql_text    clob;                                                   

  4   begin                                                                    

  5     select sql_fulltext                                                    

  6       into cl_sql_text                                                     

  7       from v$sqlarea                                                       

  8      where sql_id = 'c37q7z5qjnwwf';                                       

  9                                                                            

 10     select 'profile_' || 'c37q7z5qjnwwf' || '_dwrose'                      

 11       into l_profile_name                                                  

 12       from dual;                                                           

 13     dbms_sqltune.import_sql_profile(sql_text    => cl_sql_text,            

 14                                     profile     =>                         

 15     sqlprof_attr('INDEX_RS_ASC(TEST T_IND)'),                               

 16                                     category    => '',                     

 17                                     name        => l_profile_name,         

 18                                     force_match => FALSE);                 

 19   end;                                                                     

 20   /                                                                        

 

 

PL/SQL procedure successfully completed.

 

我们先通过dbms_sqltune包的drop_sql_profile过程删除了通过SQL Tuning Advisor创建的SQL Profile，然后通过import_sql_profile手工创建了一个SQL Profile，而且我们使用了我们常见的hint INDEX_RS_ASC(TEST T_IND)，而不是SQL Profile默认的以OPT_ESTIMATE打头的hint，上面的代码已经成功的创建了一个SQL Profile，我们看看使用常规的hint会不会起作用。

SQL>select count(name) from test where status='Inactive';

 

 

COUNT(NAME)

-----------

        100

 

1 row selected.

 

SQL>select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

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

SQL_ID  c37q7z5qjnwwf, child number 1

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

select count(name) from test where status='Inactive'

 

Plan hash value: 1950795681

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |       |       |    51 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    21 |            |          |

|*  2 |   TABLE ACCESS FULL| TEST | 25000 |   512K|    51   (2)| 00:00:01 |

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

 

Note

-----

   - SQL Profile profile_c37q7z5qjnwwf_dwrose used for this statement

 

SQL>SELECT extractValue(value(h),'.') AS hint                                                

  2    FROM sys.sqlobj$data od, sys.sqlobj$ so,                                               

  3   table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h              

  4    WHERE so.name = 'profile_c37q7z5qjnwwf_dwrose'                                         

  5    AND so.signature = od.signature                                                        

  6    AND so.category = od.category                                                          

  7    AND so.obj_type = od.obj_type                                                          

  8    AND so.plan_id = od.plan_id;                                                           

 

hint

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

INDEX_RS_ASC(TEST T_IND)

 

虽然执行计划的输出Note部分显示已经使用到了SQL Profile，但是执行计划并没有如我们预期一样被改变，依然是全表扫描，查看存储hint的基表也显示索引扫描的hint已经被绑定到了这个SQL上，那么问题出哪了？

这是由于SQL Profile对于hint是非常挑剔的，SQL Profile里接受的hint需要提供Query Block Name(初始化参数类的hint不需要提供Query Block Name），否则优化器会忽略掉这些hint，我们重新设置SQL Profile的Hints，在Hints中加上Query Block Name看看。（Query Block Name相关知识参考本章Query Block Name一节）

SQL>declare                                                                  

  2     l_profile_name varchar2(30);                                          

  3     cl_sql_text    clob;                                                  

  4   begin                                                                   

  5     select sql_fulltext                                                   

  6       into cl_sql_text                                                    

  7       from v$sqlarea                                                      

  8      where sql_id = 'c37q7z5qjnwwf';                                      

  9                                                                           

 10     select 'profile_' || 'c37q7z5qjnwwf' || '_dwrose'                     

 11       into l_profile_name                                                 

 12       from dual;                                                          

 13     dbms_sqltune.import_sql_profile(sql_text    => cl_sql_text,           

 14                                     profile     =>                        

 15     sqlprof_attr('INDEX_RS_ASC(@SEL$1 TEST@SEL$1 T_IND))'),               

 16                                     category    => '',                    

 17                                     name        => l_profile_name,        

 18                                     force_match => FALSE);                

 19   end;                                                                    

 20   /                                                                       

 

 

PL/SQL procedure successfully completed.

 

SQL>select count(name) from test where status='Inactive';

 

COUNT(NAME)

-----------

        100

 

1 row selected.

 

SQL>select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

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

SQL_ID  c37q7z5qjnwwf, child number 1

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

select count(name) from test where status='Inactive'

 

Plan hash value: 4130896540

 

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

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |       |       |       |   218 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |    21 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  | 25000 |   512K|   218   (1)| 00:00:03 |

|*  3 |    INDEX RANGE SCAN          | T_IND | 25000 |       |    63   (0)| 00:00:01 |

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

 

Note

-----

   - SQL Profile profile_c37q7z5qjnwwf_dwrose used for this statement

这一次hint起作用了，执行计划输出的Note部分可以知道创建的SQL Profile已经起作用了， 执行计划已经走了索引扫描，看来SQL Profile可以接受常规的hint ,只不过这些hint要包含Query Block Name，如果SQL Profile发现指定的hint无效，会简单的忽略掉这些hint，不会报任何的错误，也不会做任何的校验。既然常规的hint可以对SQL Profile起作用，那么我们也可以用SQL Profile来锁定执行计划了。从上面的执行计划输出也可以看到由于我们使用了常规的hint，因此执行计划的基数信息并没有得到纠正，仅仅是通过index_rs_asc这种暴力的hint把执行计划强制修正为索引扫描了。

“如何手工创建SQL Profile”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注编程网网站，小编将为大家输出更多高质量的实用文章！

您可能感兴趣的文档:

--结束END--

本文标题: 如何手工创建SQL Profile

本文链接: https://lsjlt.com/news/64633.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • 如何手工创建SQL Profile
    本篇内容介绍了“如何手工创建SQL Profile”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！手工创建S...
    99+
    2024-04-02
  • 如何手工创建数据库
    这篇文章给大家分享的是有关如何手工创建数据库的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。Refer Doc:Administrator’s Guide->2 Creati...
    99+
    2024-04-02
  • Oracle如何手工创建数据库
    这篇文章给大家分享的是有关Oracle如何手工创建数据库的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。手工创建数据库步骤1. 创建instance步骤①准备参数文件pf...
    99+
    2024-04-02
  • SQL视图如何创建
    创建SQL视图的步骤如下：1. 选择一个数据库管理工具，例如MySQL Workbench或phpMyAdmin，并登录到数据库服务...
    99+
    2023-10-11
    SQL
  • sql如何创建视图
    视图是 sql 中的虚拟表，从基础表中派生数据。创建视图的步骤包括：使用 create view 语句指定视图名称和从基础表中选择的列。可选地指定基础表之间的连接和应用过滤器。视图简化了...
    99+
    2024-06-06
    敏感数据
  • Oracle 12c如何实现手工建库而非CDB及CDB创建
    这篇文章给大家分享的是有关Oracle 12c如何实现手工建库而非CDB及CDB创建的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。相信大家应该都知道，对于Oracle数据库的创建...
    99+
    2024-04-02
  • sql数据库如何创建
    今天就跟大家聊聊有关sql数据库如何创建，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。  sql新建数据库步骤：登录然后选择数据库后右击新...
    99+
    2024-04-02
  • sql如何创建触发器
    要创建触发器，您可以使用 SQL 的 CREATE TRIGGER 语句。以下是创建触发器的基本语法： CREATE TRIGGER...
    99+
    2023-10-26
    sql
  • ASP.NET如何创建工程库
    这篇文章主要讲解了“ASP.NET如何创建工程库”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“ASP.NET如何创建工程库”吧！学习创建工程库、在自己的项目中应用ASP.NET组件设计学习之...
    99+
    2023-06-18
  • 手工创建Oracle 11g数据库
    配置Oracle环境变量 1、编辑.bash_profile [oracle@FDB ~]$ vi ~/.bash_profile 2、使配置文件生效 [oracle@FDB ~]$ . .bas...
    99+
    2024-04-02
  • SQL Server如何创建数据库
    这篇文章给大家分享的是有关SQL Server如何创建数据库的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。SQL Server 怎么创建数据库？首先启动“SQL Server M...
    99+
    2024-04-02
  • sql数据库如何创建表
    要创建表，你需要使用CREATE TABLE语句。以下是创建表的基本语法：CREATE TABLE table_name (colu...
    99+
    2023-10-08
    sql数据库
  • sql联合主键如何创建
    在创建表时，可以通过使用PRIMARY KEY关键字来指定联合主键。以下是一个示例： CREATE TABLE 表名 ( 列...
    99+
    2023-10-24
    sql
  • sql如何查看创建的表
    在SQL中，可以使用以下命令来查看已创建的表： 使用`SHOW TABLES`命令来查看数据库中的所有表。示例： SHOW TA...
    99+
    2023-10-24
    sql
  • SQL Server如何创建数据库
    本篇内容主要讲解“SQL Server如何创建数据库”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“SQL Server如何创建数据库”吧!方...
    99+
    2023-03-01
    sql server 数据库
  • sql中如何创建临时表
    在 sql 中创建临时表可使用 create temp table 语句，特点包括：会话范围、无持久性、性能优化、命名约定（## 或 # 前缀）；适用于存储中间结果、复杂查询、数据分析和...
    99+
    2024-05-10
  • 如何创建Python工程目录
    如何创建一个简单但是比较规范的python工程目录，本文是学习了Learn Python the Hard Way相关内容后做的一些笔记。 1. pip from http://pypi.python.org/pypi/pip   ...
    99+
    2023-01-31
    目录 工程 Python
  • Mysql如何使用profile分析sql开销
    小编给大家分享一下Mysql如何使用profile分析sql开销，希望大家阅读完这篇文章后大所收获，下面让我们一起去探讨吧！Mysql使用profile分析sql开销1.使用之前先查看当前数据库的版本信息,...
    99+
    2024-04-02
  • 如何使用sql语句创建表
    小编给大家分享一下如何使用sql语句创建表，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！sql语句中创建表的语句是“CREATE...
    99+
    2024-04-02
  • SQL中如何创建存储过程
    今天就跟大家聊聊有关SQL中如何创建存储过程，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。创建存储过程 表名和比较字段可以做参数的存储过程 ...
    99+
    2024-04-02
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作