sqlprofile繫結執行計劃實驗測試

隨風映雪發表於2020-09-24

sqlprofile在10g中出現,是9ioutline的升級版,即便現在11g 19c的大環境下,依然有較高的使用率。常用的場景為不改寫sql的情況下,修改其執行計劃。相較於11g的sqlbaseline,使用上個人感覺sqlbaseline簡單點,因為可以指定sql_id,而sqlprofile只能指定sql_text。

難點部分其實只有一個,就是如何是的原sql走正確的執行計劃,比如怎麼去加hint,加索引等。

詳細測試步驟如下,可做參考:


1 構造實驗環境

2 測試執行sql語句

3 這邊我們手動優化下,讓他走索引nl,可以看到,邏輯讀少了很多。

4 獲取語句執行的sql_id

5 替換執行計劃

    5.1 步驟 1 獲取優化後的 outline

    5.2 步驟 2 帶入相關引數

   5.3 步驟3 檢視是否生效,已經生效了,可以看到執行計劃中有test1_troy_sql_profile這一條

6 查詢或者刪除sql profile

 

 

1 構造實驗環境

SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=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,'t1',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

 

2 測試執行 sql 語句

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 '%T1%' and t1.object_id=t2.object_id;

36 rows selected.

Execution Plan

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

Plan hash value: 1838229974

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

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

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

|   0 | SELECT STATEMENT   |      |  2500 |   100K|   402   (1)| 00:00:05 |

|*  1 |  HASH JOIN         |      |  2500 |   100K|   402   (1)| 00:00:05 |

|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 75000 |    66   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T2   | 86260 |   926K|   336   (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)

 

Statistics

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

         38  recursive calls

          0  db block gets

       1532  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

         10  sorts (memory)

          0  sorts (disk)

         36  rows processed

 

 

3 這邊我們手動優化下,讓他走索引nl,可以看到,邏輯讀少了很多。

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

 

 Execution Plan

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

Plan hash value: 1022743391

 

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

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

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

|   0 | SELECT STATEMENT             |        |  2500 |   100K|  5067   (1)| 00:01:01 |

|   1 |  NESTED LOOPS                |        |  2500 |   100K|  5067   (1)| 00:01:01 |

|   2 |   NESTED LOOPS               |        |  2500 |   100K|  5067   (1)| 00:01:01 |

|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 75000 |    66   (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")

 

 

Statistics

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

          0  recursive calls

          0  db block gets

        306  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

 

4 獲取語句執行的sql_id

select sql_id,sql_text from v$sql where sql_text like '% and t1.object_id=t2.object_id%'; (這一步驟沒啥必要)

 

4zbqykx89yc8v

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

7a3t45wbn1299

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

 

 

5 替換執行計劃

 

5.1 步驟 1 獲取優化後的 outline

explain plan for select /*+use_nl(t1,t2) index(t2)*/t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

select * from table(dbms_xplan.display(null,null,'outline'));

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1022743391

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

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

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

|   0 | SELECT STATEMENT             |        |  2500 |   100K|  5067   (1)| 00:01:01 |

|   1 |  NESTED LOOPS                |        |  2500 |   100K|  5067   (1)| 00:01:01 |

|   2 |   NESTED LOOPS               |        |  2500 |   100K|  5067   (1)| 00:01:01 |

|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 75000 |    66   (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 |

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

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")

      USE_NL(@"SEL$1" "T2"@"SEL$1")

      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")

      INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))

      FULL(@"SEL$1" "T1"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      DB_VERSION('11.2.0.4')

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      IGNORE_OPTIM_EMBEDDED_HINTS

      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")

37 rows selected.

 

5.2 步驟 2 帶入相關引數

-- 使用 sql profile, 我們選取必要的 hint OK 了,其他的可以不要 , 如下, 多行的話 逗號隔開

-- 注意單引號要變成雙引號,否則會提示格式錯誤

 

declare

  v_hints sys.sqlprof_attr;

begin

  v_hints := sys.sqlprof_attr(

  'USE_NL(@"SEL$1" "T2"@"SEL$1")',

  'LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")',

  'INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))'

  );

  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',

                                  v_hints,'test1_troy_sql_profile',

                                  force_match => true,replace=>true);

end;

/

 

成功會提示

PL/SQL procedure successfully completed.

 

 

5.3 步驟 3 檢視是否生效,已經生效了,可以看到執行計劃中有 test1_troy_sql_profile 這一條

 

Execution Plan

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

Plan hash value: 1022743391

 

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

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

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

|   0 | SELECT STATEMENT             |        |  2500 |   100K|  5067   (1)| 00:01:01 |

|   1 |  NESTED LOOPS                |        |  2500 |   100K|  5067   (1)| 00:01:01 |

|   2 |   NESTED LOOPS               |        |  2500 |   100K|  5067   (1)| 00:01:01 |

|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 75000 |    66   (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 "test1_troy_sql_profile" used for this statement

 

 

Statistics

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

          0  recursive calls

          0  db block gets

        306  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

 

  6 查詢或者刪除 sql profile

- 如果優化效果不理想,想要刪除繫結的 sql_profile, 執行計劃用的是未改變 slqprofile 之前的執行計劃

-- 檢視

select * from dba_sql_profiles;

 

SQL> BEGIN

  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'test1_troy_sql_profile');

END;


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31404823/viewspace-2723680/,如需轉載,請註明出處,否則將追究法律責任。

相關文章