Manual类型的SQL Profile

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

关键词：

dbms_stats.gather_table_stats、

DBMS_SQLTUNE.DROP_SQL_PROFILE、

coe_xfr_sql_profile.sql

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

SQL>

createtable t1( n number);

表已创建。

SQL>

declare

2begin

3fori in 1 .. 10000

4loop

5insertinto t1 values(i);

6commit;

7end loop;

8end;

9/

PL/SQL 过程已成功完成。

SQL>

selectcount(*)fromt1;

COUNT(*)

———-

10000

SQL>

create indexidx_t1ont1(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 LONGCHUNKSIZE1000

SQL>

SET LINESIZE 2000

SQL>

SELECT NAME ,SQL_TEXT, TYPE,STATUS,FORCE_MATCHING FROMDBA_SQL_PROFILESWHERESQL_TEXT LIKE ‘SELECT/*+NO_INDEX(T1 IDX_T1) */*FROMT1 WHERE%’;

NAMESQL_TEXT

TYPESTATUSFORCE_

———————————————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————- ————– —————- ——

SYS_SQLPROF_0162663bdb700000SELECT/*+NO_INDEX(T1 IDX_T1) */*FROMT1 WHERE N=1

MANUALENABLEDNO

SYS_SQLPROF_01626643a6130001SELECT/*+NO_INDEX(T1 IDX_T1) */*FROMT1 WHERE N=1

MANUALENABLEDYES

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/*+NO_INDEX(T1 IDX_T1) */*FROMT1 WHERE N=1 ;

N

———-

1

SQL>

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,’advanced’));

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_IDgn8zuq00kd86g, child number 0

————————————-

SELECT/*+NO_INDEX(T1 IDX_T1) */*FROMT1 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

————-

/*+

PLAN_TABLE_OUTPUT

——————————————————————————–

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4’)

DB_VERSION(‘11.2.0.4’)

ALL_ROWS

OUTLINE_LEAF(@”SEL$1″)

FULL(@”SEL$1″ “T1″@”SEL$1”)

END_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 /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3;

Nse

———-

3

SQL>

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,’advanced’));

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_ID866w0nx37z5kg, child number 0

————————————-

SELECT /*+ INDEX(T1 IDX_T1) */* 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

————-

/*+

PLAN_TABLE_OUTPUT

——————————————————————————–

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4’)

DB_VERSION(‘11.2.0.4’)

ALL_ROWS

OUTLINE_LEAF(@”SEL$1″)

INDEX(@”SEL$1″ “T1″@”SEL$1” (“T1″.”N”))

END_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_IDVERSION_COUNT

————————– ————-

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

9bvng6dz8ct9z1

SELECT /*+ INDEX(T1 IDX_T1) */*

FROM T1 WHERE N=3

866w0nx37z5kg1

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

2IF :sql_text IS NULL THEN

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

4END IF;

5END;

6/

SQL>SET TERM OFF;

SQL>BEGIN

2IF :other_xml IS NULL THEN

3RAISE_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).’);

4END IF;

5END;

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.

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

2IF :sql_text IS NULL THEN

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

4END IF;

5END;

6/

SQL>SET TERM OFF;

SQL>BEGIN

2IF :other_xml IS NULL THEN

3RAISE_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).’);

4END IF;

5END;

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.

四、将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

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

SQL>


@C:UsersYXcoe_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>REMcoe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql

SQL>REM

SQL>REM DESCRIPTION

SQL>REMThis script is generated by coe_xfr_sql_profile.sql

SQL>REMIt contains the SQL*Plus commands to create a custom

SQL>REMSQL Profile for SQL_ID gn8zuq00kd86g based on plan hash

SQL>REMvalue 3617692013.

SQL>REMThe custom SQL Profile to be created by this script

SQL>REMwill affect plans for SQL commands with signature

SQL>REMmatching the one for SQL Text below.

SQL>REMReview SQL Text and adjust accordingly.

SQL>REM

SQL>REM PARAMETERS

SQL>REMNone.

SQL>REM

SQL>REM EXAMPLE

SQL>REMSQL> START coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql;

SQL>REM

SQL>REM NOTES

SQL>REM1. Should be run as SYSTEM or SYSDBA.

SQL>REM2. User must have CREATE ANY SQL PROFILE privilege.

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

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

SQL>REMEXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(‘coe_gn8zuq00kd86g_3617692013’);

SQL>REM5. Be aware that using DBMS_SQLTUNE requires a license

SQL>REMfor the Oracle Tuning Pack.

SQL>REM

SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;

SQL>REM

SQL>VAR signature NUMBER;

SQL>REM

SQL>DECLARE

2sql_txt CLOB;

3hSYS.SQLPROF_ATTR;

4BEGIN

5sql_txt := q'[

6SELECT/*+NO_INDEX(T1 IDX_T1)

7*/*FROMT1 WHERE N=1

8]’;

9h := SYS.SQLPROF_ATTR(

10


q'[BEGIN_OUTLINE_DATA]’,

11q'[IGNORE_OPTIM_EMBEDDED_HINTS]’,

12q'[OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4′)]’,

13q'[DB_VERSION(‘11.2.0.4′)]’,

14q'[ALL_ROWS]’,

15q'[OUTLINE_LEAF(@”SEL$1″)]’,

16q'[INDEX(@”SEL$1″ “T1″@”SEL$1” (“T1″.”N”))]’,

17q'[END_OUTLINE_DATA]’);

18DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

19sql_text=> sql_txt,

20profile=> h,

21name=> ‘coe_gn8zuq00kd86g_3617692013’,

22description => ‘coe gn8zuq00kd86g 3617692013 ‘||:signature||”,

23category=> ‘DEFAULT’,

24validate=> TRUE,

25replace=> TRUE,

26force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

27END;

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/*+NO_INDEX(T1 IDX_T1) */*FROMT1 WHERE N=1;

N

———-

1

SQL>



SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,’advanced’));

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_IDgn8zuq00kd86g, child number 0

————————————-

SELECT/*+NO_INDEX(T1 IDX_T1) */*FROMT1 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

————-

/*+

PLAN_TABLE_OUTPUT

——————————————————————————–

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE(‘11.2.0.4’)

DB_VERSION(‘11.2.0.4’)

ALL_ROWS

OUTLINE_LEAF(@”SEL$1″)

INDEX(@”SEL$1″ “T1″@”SEL$1” (“T1″.”N”))

END_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行。

相关推荐: redis与mongodb有什么区别

redis与mongodb有什么区别？这个问题可能是我们日常学习或工作经常见到的。希望通过这个问题能让你收获颇深。下面是小编给大家带来的参考内容，让我们一起来看看吧！MongoDB更类似MySQL，支持字段索引、游标操作，其优势在于查询功能比较强大，擅长查询J…