扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Manual类型的SQL Profile
  • 116

0
分享到

Manual类型的SQL Profile

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

实验目的：Manual类型稳定执行计划，相对AUTOMATIC类型更稳定 关键词： dbms_stats.gather_table_stats、 DBMS_sqlTUNE.DROP

实验目的：Manual类型稳定执行计划，相对AUTOMATIC类型更稳定

关键词： dbms_stats.gather_table_stats、 DBMS_sqlTUNE.DROP_SQL_PROFILE、 coe_xfr_sql_profile.sql  

Manual类型的SQL Profile

一、创建表，收集表统计信息

SQL> create  table t1( n number);

表已创建。

SQL> declare

  2     begin

  3      for  i in 1 .. 10000

  4      loop

  5      insert  into t1 values(i);

  6      commit;

  7      end loop;

  8      end;

  9      /

PL/SQL 过程已成功完成。

 SQL>   select  count(*)  from  t1;

  COUNT(*)

----------

     10000

SQL> create index  idx_t1  on  t1(n);

索引已创建。  

 

SQL> exec dbms_stats.gather_table_stats( ownname =>'TEST' , tabname =>'T1' , method_opt =>'for all columns size 1', CASCADE => TRUE);

PL/SQL 过程已成功完成。

二是查找到SQL_PROFILE,并将相应的SQL_PROFILE删除

SQL> SET LONG 9000

SQL> SET LONGCHUNKSIZE  1000

SQL> SET LINESIZE  2000

SQL> SELECT NAME ,SQL_TEXT, TYPE,STATUS,FORCE_MATCHING FROM  DBA_SQL_PROFILES  WHERE  SQL_TEXT LIKE 'SELECT    *   FROM  T1 WHERE%';

NAME                                                         SQL_TEXT

                                                                                              TYPE       STATUS       FORCE_

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

SYS_SQLPROF_0162663bdb700000                                 SELECT    *   FROM  T1 WHERE N=1

                                                                                                      MANUAL         ENABLED          NO

SYS_SQLPROF_01626643a6130001                                 SELECT    *   FROM  T1 WHERE N=1

                                                                                                      MANUAL         ENABLED          YES

SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_01626643a6130001');

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_0162663bdb700000');

PL/SQL 过程已成功完成。

三是调用coe_xfr_sql_profile.sql，产生Manual类型的SQL PROFILE脚本

SQL> SELECT    *   FROM  T1 WHERE N=1 ;

         N

----------

         1

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID  gn8zuq00kd86g, child number 0

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

SELECT    *   FROM  T1 WHERE N=1

Plan hash value: 3617692013

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

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

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

|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          |

|*  1 |  TABLE ACCESS FULL| T1   |     1 |     4 |     7   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

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

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / T1@SEL$1

Outline Data

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

  

Predicate InfORMation (identified by operation id):

PLAN_TABLE_OUTPUT

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

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

   1 - filter("N"=1)

Column Projection Information (identified by operation id):

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

   1 - "N"[NUMBER,22]

已选择42行。

SQL> SELECT * FROM T1 WHERE N=3;

         Nse

----------

         3

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID  866w0nx37z5kg, child number 0

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

SELECT * FROM T1 WHERE N=3

Plan hash value: 1369807930

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

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

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

|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |

|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

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

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / T1@SEL$1

Outline Data

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

  

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

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

   1 - access("N"=3)

Column Projection Information (identified by operation id):

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

   1 - "N"[NUMBER,22]

已选择42行。

SQL> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%N=3%';

SQL_TEXT

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

SQL_ID                     VERSION_COUNT

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

SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%N=3%'

9bvng6dz8ct9z                          1

SELECT * FROM T1 WHERE N=3

866w0nx37z5kg                          1

SQL> SELECT PLAN_HASH_VALUE FROM V$SQL WHERE SQL_ID='866w0nx37z5kg';

PLAN_HASH_VALUE

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

     1369807930

SQL> @F:\oracle\脚本\coe_xfr_sql_profile.sql  

Parameter 1:

SQL_ID (required)

输入 1 的值:   866w0nx37z5kg    --条件为 N=3 的SQL_ID

PLAN_HASH_VALUE AVG_ET_SECS

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

     1369807930        .001

Parameter 2:

PLAN_HASH_VALUE (required)

输入 2 的值:   1369807930

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID         : "866w0nx37z5kg"

PLAN_HASH_VALUE: "1369807930"

SQL>BEGIN

  2    IF :sql_text IS NULL THEN

  3      RaiSE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

  4    END IF;

  5  END;

  6  /

SQL>SET TERM OFF;

SQL>BEGIN

  2    IF :other_xml IS NULL THEN

  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');

  4    END IF;

  5  END;

  6  /

SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_866w0nx37z5kg_1369807930.sql

on TARGET system in order to create a custom SQL Profile

with plan 1369807930 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.  

Manual类型的SQL Profile

SQL> @F:\oracle\脚本\coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

输入 1 的值:   gn8zuq00kd86g   --条件为 N=1的SQL_ID

PLAN_HASH_VALUE AVG_ET_SECS

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

     3617692013        .002

Parameter 2:

PLAN_HASH_VALUE (required)

输入 2 的值:   3617692013

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID         : "gn8zuq00kd86g"

PLAN_HASH_VALUE: "3617692013"

SQL>BEGIN

  2    IF :sql_text IS NULL THEN

  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

  4    END IF;

  5  END;

  6  /

SQL>SET TERM OFF;

SQL>BEGIN

  2    IF :other_xml IS NULL THEN

  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');

  4    END IF;

  5  END;

  6  /

SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql

on TARGET system in order to create a custom SQL Profile

with plan 3617692013 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

Manual类型的SQL Profile

四、将coe_xfr_sql_profile_866w0nx37z5kg_1369807930.sql中HINT组合

h := SYS.SQLPROF_ATTR(

q'[BEGIN_OUTLINE_DATA]',

q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

q'[DB_VERSION('11.2.0.4')]',

q'[ALL_ROWS]',

q'[OUTLINE_LEAF(@"SEL$1")]',

q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]',

q'[END_OUTLINE_DATA]');

替换 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql

h := SYS.SQLPROF_ATTR(

q'[BEGIN_OUTLINE_DATA]',

q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

q'[DB_VERSION('11.2.0.4')]',

q'[ALL_ROWS]',

q'[OUTLINE_LEAF(@"SEL$1")]',

q'[FULL(@"SEL$1" "T1"@"SEL$1")]',

q'[END_OUTLINE_DATA]');

:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);   

并将 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql中的参数 FORCE_MATCH 的值由 FALSE 替换 TRUE Manual类型的SQL Profile

五、执行脚本，调整执行计划

SQL> @C:\Users\YX\coe_xfr_sql_profile_gn8zuq00kd86g_3617692013（修改后）.sql

SQL>REM

SQL>REM $Header: 215187.1 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql 11.4.3.5 2018/03/28 carlos.sierra $

SQL>REM

SQL>REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.

SQL>REM

SQL>REM AUTHOR

SQL>REM    carlos.sierra@oracle.com

SQL>REM

SQL>REM SCRIPT

SQL>REM   coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql

SQL>REM

SQL>REM DESCRIPTION

SQL>REM   This script is generated by coe_xfr_sql_profile.sql

SQL>REM   It contains the SQL*Plus commands to create a custom

SQL>REM   SQL Profile for SQL_ID gn8zuq00kd86g based on plan hash

SQL>REM   value 3617692013.

SQL>REM   The custom SQL Profile to be created by this script

SQL>REM   will affect plans for SQL commands with signature

SQL>REM   matching the one for SQL Text below.

SQL>REM   Review SQL Text and adjust accordingly.

SQL>REM

SQL>REM PARAMETERS

SQL>REM   None.

SQL>REM

SQL>REM EXAMPLE

SQL>REM   SQL> START coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql;

SQL>REM

SQL>REM NOTES

SQL>REM   1. Should be run as SYSTEM or SYSDBA.

SQL>REM   2. User must have CREATE ANY SQL PROFILE privilege.

SQL>REM   3. SOURCE and TARGET systems can be the same or similar.

SQL>REM   4. To drop this custom SQL Profile after it has been created:

SQL>REM  EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_gn8zuq00kd86g_3617692013');

SQL>REM   5. Be aware that using DBMS_SQLTUNE requires a license

SQL>REM  for the Oracle Tuning Pack.

SQL>REM

SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;

SQL>REM

SQL>VAR signature NUMBER;

SQL>REM

SQL>DECLARE

  2  sql_txt CLOB;

  3  h       SYS.SQLPROF_ATTR;

  4  BEGIN

  5  sql_txt := q'[

  6  SELECT       *      FROM  T1 WHERE N=1

  8  ]';

  9  h := SYS.SQLPROF_ATTR(

10   q'[BEGIN_OUTLINE_DATA]',

11  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

12  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

13  q'[DB_VERSION('11.2.0.4')]',

14  q'[ALL_ROWS]',

15  q'[OUTLINE_LEAF(@"SEL$1")]',

16  q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]',

17  q'[END_OUTLINE_DATA]');

18  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

19  sql_text    => sql_txt,

20  profile     => h,

21  name        => 'coe_gn8zuq00kd86g_3617692013',

22  description => 'coe gn8zuq00kd86g 3617692013 '||:signature||'',

23  cateGory    => 'DEFAULT',

24  validate    => TRUE,

25  replace     => TRUE,

26  force_match => TRUE );

27  END;

28  /

PL/SQL 过程已成功完成。

SQL>WHENEVER SQLERROR CONTIN UE

SQL>SET ECHO OFF;

            SIGNATURE

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

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_gn8zuq00kd86g_3617692013 completed

六、查看执行计划

SQL> SELECT    *   FROM  T1 WHERE N=1;

         N

----------

         1

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID  gn8zuq00kd86g, child number 0

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

SELECT    *   FROM  T1 WHERE N=1

Plan hash value: 1369807930

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

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

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

|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |

|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

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

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / T1@SEL$1

Outline Data

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

  

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

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

   1 - access("N"=1)

Column Projection Information (identified by operation id):

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

   1 - "N"[NUMBER,22]

Note

-----

PLAN_TABLE_OUTPUT

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

   - SQL profile coe_gn8zuq00kd86g_3617692013 used for this statement

已选择46行。


您可能感兴趣的文档:

--结束END--

本文标题: Manual类型的SQL Profile

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

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

猜你喜欢
  • Manual类型的SQL Profile
    实验目的：Manual类型稳定执行计划，相对AUTOMATIC类型更稳定 关键词： dbms_stats.gather_table_stats、 DBMS_SQLTUNE.DROP...
    99+
    2024-04-02
  • SQL列的数据类型分类
    SQL列的数据类型分类：     Unicode数据： 1.nchar 2.nvarchar 3.ntext 说明： Unicode支持的字符范围更大。存储 Unicode 字符所需要的空间更大。 傻瓜式教程...
    99+
    2017-03-27
    SQL列的数据类型分类
  • Sql日期类型
    declare @datetime datetime = Current_Timestamp ,@date date = Current_Timestamp ,@tim...
    99+
    2024-04-02
  • Oracle固定SQL的执行计划(一)---SQL Profile
    我们都希望对于所有在Oracle数据库中执行的SQL，CBO都能产生出正确的执行计划，但实际情况却并非如此，由于各种各样的原因(比如目标SQL所涉及的对象的统计信息的不准确，或者CBO内部一些成本计算公式的...
    99+
    2024-04-02
  • SQL的CAST()——转换数据类型
    目录 1.CAST()的语法及用法 （1）语法：CAST(Expression AS Data_type) 即CAST(表达式 AS 数据类型) （2）用法：将需要转换的表达式转为目标类型，可以是获取目标类型的字段，也可以与wher...
    99+
    2023-09-17
    其他 经验分享 sql mysql
  • SQL盲注的类型有哪些
    SQL盲注是一种利用应用程序对SQL语句的处理不当而进行的攻击。根据攻击者能否获取到数据库返回的具体错误信息，SQL盲注可以分为两个...
    99+
    2023-08-23
    SQL
  • SQL数据类型详解
    一、数据类型简介 数据表由多列字段构成，每一个字段指定了不同的数据类型，指定了数据类型之后，也就决定了向字段插入的数据内容; 不同的数据类型也决定了 MySQL 在存储它们的时候使用的方式，以及在使用它...
    99+
    2024-04-02
  • MS SQL Server - 类型转换
    当我们在MS SQL Server中处理数据时，我们经常需要根据数据类型执行计算或过滤结果。正确转换数据类型可确保我们的计算准确并且查询返回所需的结果。在本文中，我们将讨论 MS SQL Server 中的各种类型转换。 在MS SQL S...
    99+
    2023-10-22
  • sql server属于哪个类型的数据模型
    这篇文章给大家分享的是有关sql server属于哪个类型的数据模型的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。sql server属于什么类型的数据模型？SQL Server...
    99+
    2024-04-02
  • SQL SERVER数据类型是怎样的
    本篇文章给大家分享的是有关SQL SERVER数据类型是怎样的，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 1.SQL SERVER的数据...
    99+
    2024-04-02
  • SQL SERVER的数据类型有哪些
    本篇内容介绍了“SQL SERVER的数据类型有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！1.SQ...
    99+
    2024-04-02
  • SQL Server中的数据类型详解
    目录哪些对象需要数据类型一、 整数数据类型1、bit2、tinyint3、smallint4、int （integer）5、bigint二、 浮点数据类型1、real： 近似数值型2...
    99+
    2024-04-02
  • SQL decimal类型的用法是什么
    SQL中的decimal类型用于存储精确的十进制数值。它可以指定数字的总位数和小数位数，以便在存储和计算过程中保持精确性。decim...
    99+
    2023-10-18
    SQL
  • sql float类型的特点有哪些
    Float类型用于存储浮点数，可以表示小数值。 Float类型的精度可以在一定范围内自由设定，通常为单精度（4字节）或双精度（8字...
    99+
    2024-04-09
    sql
  • sql的注入类型有哪几种
    SQL注入类型主要有以下几种： 基于错误的注入：通过输入恶意的SQL语句使得程序返回错误信息或者异常，从而获取数据库结构或数据。 ...
    99+
    2024-04-09
    sql
  • SQL Server数据类型介绍
    简单整理几个常用的SQL server 数据类型的描述，以便查阅。 ...
    99+
    2024-04-02
  • sql中date类型怎么写
    date 数据类型是 sql 中用于存储日期值的类型，不含时间信息，格式为 "yyyy-mm-dd"。特殊情况下，闰年 2 月有 29 天，没有年份时使用当前年份，没有年份或月份时使用当...
    99+
    2024-05-30
  • sql数据类型怎么改
    sql 中更改数据类型的步骤：1. 确定需要更改类型的列；2. 使用 alter table 语句更改类型，指定新数据类型；3. 处理数据转换；4. 处理外键关系；5. 提交更改。 如...
    99+
    2024-05-30
  • SQL Server可以锁定的资源类型
    SQL Server可以锁定的资源类型SQL Server可以锁定不同类型的资源。这些可以被锁定的资源类型包括：RIDs或键（keys）（行级别），页（pages），对象（objects）（例如，表），数据...
    99+
    2024-04-02
  • mysqli_query($conn, $sql)函数的返回值类型
    本篇文章给大家分享的是有关mysqli_query($conn, $sql)函数的返回值类型，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。一、...
    99+
    2024-04-02
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作