sqlprofile繫結執行計劃實驗測試

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

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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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/，如需轉載，請註明出處，否則將追究法律責任。

sqlprofile繫結執行計劃實驗測試

相關文章