Sql profiles-->手工创建sql profile

水铭晨

2023-12-01

一. 手工创建sql profile的方法

因为sql tuning的sql profile不能锁定执行计划，只是提供更准确的信息以生成更准确的执行计划，当统计信息改变时，sql的执行计划也会变化。所以就需要手工创建sql profile的方法来锁定指定计划。

目的：

a. 锁定或者说是稳定sql执行计划

b. 再不能修改应用的sql的情况下，来改变或者说是强制使sql使用我们指定的sql的执行计划，即使原始的sql包含了Hints.

方法：DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程，可以像outlines一样，稳定sql的执行计划。

二. 手工创建sql profile测试

1. 删除之前生成的sql profile，同时恢复T1表的统计信息中的表行数

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

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.set_table_stats('TEST','T1',numrows=>49953);  

PL/SQL procedure successfully completed.

2. 手工创建sql profile

SQL>  declare  
  2      v_hints sys.sqlprof_attr;  
  3    begin  
  4      v_hints:=sys.sqlprof_attr('USE_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)');  
  5      dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',  
  6                  v_hints,'SQLPROFILE_NAME1',force_match=>true);  
  7    end;  
  8    /  

PL/SQL procedure successfully completed.

3. 执行相应的sql

SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
  2  ;

36 rows selected.


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

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |  2498 |   100K|  5067   (1)| 00:01:01 |
|   1 |  NESTED LOOPS                |        |  2498 |   100K|  5067   (1)| 00:01:01 |
|   2 |   NESTED LOOPS               |        |  2498 |   100K|  5067   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |  2498 | 74940 |    70   (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 by operation 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 "SQLPROFILE_NAME1" used for this statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        310  consistent gets
          1  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

可以看到，sql的执行计划已经采用了之前创建的sql profile

4. 修改表的统计信息，查看sql的执行计划

SQL> exec dbms_stats.set_table_stats('TEST','T1',numrows=>5000000);

PL/SQL procedure successfully completed.

SQL> select  t1.*,t2.owner  
  2         from t1,t2  
  3         where t1.object_name like '%T1%'  
  4         and t1.object_id=t2.object_id;  

36 rows selected.


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

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |   250K|     9M|   500K  (1)| 01:40:03 |
|   1 |  NESTED LOOPS                |        |   250K|     9M|   500K  (1)| 01:40:03 |
|   2 |   NESTED LOOPS               |        |   250K|     9M|   500K  (1)| 01:40:03 |
|*  3 |    TABLE ACCESS FULL         | T1     |   250K|  7324K|   107  (35)| 00:00:02 |
|*  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 by operation 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 "SQLPROFILE_NAME1" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        305  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

可以看到，oracle优化器评估表T1经过like条件过滤后返回的行数虽然很大，但是这里执行计划依然与之前一致，采用range scan+nested loop join。

三. 对现有的sql稳定其执行计划

1. 通过v$sql_plan找出稳定执行计划的hints

对于复杂的sql，很难手工构造hints，同时手工构造的hints不一定能够保证sql的执行计划能够稳定。10g之后，v$sql_plan中包括了sql语句的outline数据，也就是稳定执行计划的hints.

SQL> select  t1.*,t2.owner  
  2         from t1,t2  
  3         where t1.object_name like '%T1%'  
  4         and t1.object_id=t2.object_id;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline')); 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2fvukamj6u7b7, child number 0
-------------------------------------
select  t1.*,t2.owner        from t1,t2        where t1.object_name
like '%T1%'        and t1.object_id=t2.object_id

Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |       |       |   500K(100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |        |   250K|     9M|   500K  (1)| 01:40:03 |
|   2 |   NESTED LOOPS               |        |   250K|     9M|   500K  (1)| 01:40:03 |
|*  3 |    TABLE ACCESS FULL         | T1     |   250K|  7324K|   107  (35)| 00:00:02 |
|*  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 |
---------------------------------------------------------------------------------------

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")
      INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      USE_NL(@"SEL$1" "T2"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL))
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - SQL profile SQLPROFILE_NAME1 used for this statement


47 rows selected.

上面所显示的"Outline Data"即是我们稳定SQL执行计划需要的hints(甚至可以将这些hints直接写到sql中)。对需要稳定执行计划的SQL，就可以将Hints与SQL文本一起创建一个SQL profile。就要用到coe_xfr_sql_profile.sql脚本，这个脚本可以从 shared pool、awr中提取执行SQL ID的out line data并创建SQL profile。脚本来自于"SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]"

SQL> select /*+ proftest1 */ t1.*,t2.owner  
  2         from t1,t2  
  3         where t1.object_name like '%T1%'  
  4         and t1.object_id=t2.object_id;

从v$sql里面可以找出刚才执行的sql_id为3wymxrtstfstk。

2. 测试coe_xfr_sql_profile.sql

SQL> @coe_xfr_sql_profile.sql 

Parameter 1:
SQL_ID (required)

Enter value for 1: 5czwj3q9sp5v1 --输入sql_id


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2959412835        .584

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2959412835 --输入正确的，需要稳定的执行计划的hash value

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "5czwj3q9sp5v1"
PLAN_HASH_VALUE: "2959412835"

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_5czwj3q9sp5v1_2959412835.sql
on TARGET system in order to create a custom SQL Profile
with plan 2959412835 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

执行完之后会生成另一个脚本，运行这个脚本就会创建出稳定执行计划所需要的sql profile，sql profile的名字为coe+sql_id+plan_hash_value。这个脚本里面force match为false，可以手工修改脚本将其改为true，同时也可以根据医院来修改脚本的其他内容。

SQL>host
[oracle@prod1 scripts]$ ls
coe_xfr_sql_profile_5czwj3q9sp5v1_2959412835.sql  coe_xfr_sql_profile.log  coe_xfr_sql_profile.sql

执行生成的脚本即可绑定指定的执行计划

SQL>@coe_xfr_sql_profile_5czwj3q9sp5v1_2959412835.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_5czwj3q9sp5v1_2959412835.sql 11.4.4.4 2017/03/31 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, 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_5czwj3q9sp5v1_2959412835.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 5czwj3q9sp5v1 based on plan hash
SQL>REM   value 2959412835.
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_5czwj3q9sp5v1_2959412835.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_5czwj3q9sp5v1_2959412835');
SQL>REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM  for the Oracle Tuning Pack.
SQL>REM   6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM  Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM  By doing so you can create a custom SQL Profile for the original
SQL>REM  SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  PROCEDURE wa (p_line IN VARCHAR2) IS
  5  BEGIN
  6  DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
  7  END wa;
  8  BEGIN
  9  DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
 10  DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
 11  -- SQL Text pieces below do not have to be of same length.
 12  -- So if you edit SQL Text (i.e. removing temporary Hints),
 13  -- there is no need to edit or re-align unmodified pieces.
 14  wa(q'[select /*+ proftest1 */ t1.*,t2.owner
 15           from t1,t2
 16        ]');
 17  wa(q'[      where t1.object_name like '%T1%'
 18           and t1.object_id=t2]');
 19  wa(q'[.object_id]');
 20  DBMS_LOB.CLOSE(sql_txt);
 21  h := SYS.SQLPROF_ATTR(
 22  q'[BEGIN_OUTLINE_DATA]',
 23  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 24  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
 25  q'[DB_VERSION('11.2.0.4')]',
 26  q'[ALL_ROWS]',
 27  q'[OUTLINE_LEAF(@"SEL$1")]',
 28  q'[FULL(@"SEL$1" "T2"@"SEL$1")]',
 29  q'[FULL(@"SEL$1" "T1"@"SEL$1")]',
 30  q'[LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")]',
 31  q'[USE_HASH(@"SEL$1" "T1"@"SEL$1")]',
 32  q'[END_OUTLINE_DATA]');
 33  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 34  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
 35  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 36  sql_text    => sql_txt,
 37  profile     => h,
 38  name        => 'coe_5czwj3q9sp5v1_2959412835',
 39  description => 'coe 5czwj3q9sp5v1 2959412835 '||:signature||' '||:signaturef||'',
 40  category    => 'DEFAULT',
 41  validate    => TRUE,
 42  replace     => TRUE,
 43  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 44  DBMS_LOB.FREETEMPORARY(sql_txt);
 45  END;
 46  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
---------------------
  6058051510930011685


           SIGNATUREF
---------------------
  2283478425026279467


... manual custom SQL Profile has been created  --sql profile创建成功


COE_XFR_SQL_PROFILE_5czwj3q9sp5v1_2959412835 completed

---

四. 再不能修改sql的情况下改变并固定sql的执行计划，即是原始sql使用了hints

在sql语句及其执行计划有问题，或者sql使用了错误的hints(比如/*+rule*/)导致sql性能差，但是应用不能修改或者暂时无法修改sql的情况下，可以使用sql profile来稳定sql的执行计划。一般情况下不建议调整统计信息，因为比较复杂，不稳定可靠，影响面比较广(可能会为影响其他访问此对象的sql)

实现方法：

step1：取得原始sql的文本(sql_id)

step2：构造一个与原始sql在逻辑，结构上完全相同的sql。这里强制逻辑上和结构上相同，sql解析的用户名、sql中引用对象的用户名设置是一些predicate条件都可以不同。当然与原始sql一样最省事。

step3：执行构造的sql,取得构造的sql的outline data

step4：使用原始sql的文本和构造的sql的outline data创建sql profile

1. 执行原始sql，取得sql_id（2u5zxb296223v）

SQL>    select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner     
  2           from t1,t2     
  3           where t1.object_name like '%T1%'     
  4           and t1.object_id=t2.object_id;   

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   250K|     9M|       |  1325   (3)| 00:00:16 |
|*  1 |  HASH JOIN         |      |   250K|     9M|  1952K|  1325   (3)| 00:00:16 |
|   2 |   TABLE ACCESS FULL| T2   | 86645 |   930K|       |   345   (1)| 00:00:05 |
|*  3 |   TABLE ACCESS FULL| T1   |   250K|  7324K|       |   107  (35)| 00:00:02 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
       1482  consistent gets
       1722  physical reads
          0  redo size
       2205  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

2. 执行修改后的sql（dums8csjskpxn），使其按照我们想要的执行计划执行。

SQL>select /*+ modify_sql index(t1) use_nl(t1 t2) */ t1.*,t2.owner  
  2         from t1,t2  
  3         where t1.object_name like '%T1%'  
  4         and t1.object_id=t2.object_id;  

36 rows selected.


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

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |   250K|     9M|   500K  (1)| 01:40:03 |
|   1 |  NESTED LOOPS                |        |   250K|     9M|   500K  (1)| 01:40:03 |
|   2 |   NESTED LOOPS               |        |   250K|     9M|   500K  (1)| 01:40:03 |
|*  3 |    TABLE ACCESS FULL         | T1     |   250K|  7324K|   107  (35)| 00:00:02 |
|*  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 by operation 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  db block gets
        303  consistent gets
        260  physical reads
          0  redo size
       2163  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

3. 使用coe_xfr_sql_profile.sql脚本来提取构造的sql的outline data

SQL> @coe_xfr_sql_profile.sql

Enter value for 1: dums8csjskpxn

PLAN_HASH_VALUE: "1022743391"

取得生成的创建sql profile的脚本，其中的outline data为：

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'[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',
q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "T2"@"SEL$1")]',
q'[NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

4. 针对原始sql使用coe_xfr_sql_profile.sql，手工修改生成的sql脚本，将其中的outline data替换为构造sql得到的那一段outline，同时将force_match从false改为true

SQL> @coe_xfr_sql_profile.sql

Enter value for 1: 2u5zxb296223v

Enter value for 2: 2959412835

修改生成的sql脚本coe_xfr_sql_profile_2u5zxb296223v_2959412835.sql并执行

SQL> @coe_xfr_sql_profile_2u5zxb296223v_2959412835.sql

PL/SQL procedure successfully completed.

... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_2u5zxb296223v_2959412835 completed

5. 执行原始sql，查看执行情况

SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner  
  2         from t1,t2  
  3         where t1.object_name like '%T2%'  
  4         and t1.object_id=t2.object_id; 

57 rows selected.


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

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

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
e     |

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

|   0 | SELECT STATEMENT             |        |   250K|     9M|   500K  (1)| 01:
40:03 |

|   1 |  NESTED LOOPS                |        |   250K|     9M|   500K  (1)| 01:
40:03 |

|   2 |   NESTED LOOPS               |        |   250K|     9M|   500K  (1)| 01:
40:03 |

|*  3 |    TABLE ACCESS FULL         | T1     |   250K|  7324K|   107  (35)| 00:
00:02 |

|*  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 by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T2%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "coe_2u5zxb296223v_2959412835" used for this statement --可以看到执行计划已经采用了创建的sql profile


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        306  consistent gets
        261  physical reads
          0  redo size
       2976  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         57  rows processed

Sql profiles-->手工创建sql profile

相关阅读

相关文章

相关问答

相关文档