oracle 10g采用 sql profile ：两种模式
- 从SQL语句历史的执行计划，找到一个合理的，进行绑定
- 还有一种无法从历史的执行计划找到合理的，只能手工构造进行绑定
提供脚本
create_sql_profile
提供绑定shared pool中已有的执行计划中，找一个绑定或自己构造一个绑定
create_sql_profile
CREATE_SQL_PROFILE_AWR
绑定AWR中历史的计划中其他一个
create_sql_profile_awr
sql_profile_hints
显示sql profile中的HINT信息
sql_profile_hints
一、SQL 绑定现有执行计划
一个SQL存在多个执行计划，选择其中一个固定
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
select
*
from
scott.emp
where
deptno=30
select
*
from
table
(dbms_xplan.display_cursor(
'4hpk08j31nm7y'
,
null
))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select
*
from
scott.emp
where
deptno=30
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 3 (100)| |
|* 1 |
TABLE
ACCESS
FULL
| EMP | 6 | 228 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
1 - filter(
"DEPTNO"
=30)
SQL_ID 4hpk08j31nm7y, child number 2
-------------------------------------
select
*
from
scott.emp
where
deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 2 (100)| |
| 1 |
TABLE
ACCESS
BY
INDEX
ROWID| EMP | 6 | 228 | 2 (0)| 00:00:01 |
|* 2 |
INDEX
RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - access(
"DEPTNO"
=30)
|
语句绑定第一个子游标为固定的执行计划：全表扫描
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
sys@GULL> @create_sql_profile
Enter value
for
sql_id: 4hpk08j31nm7y
Enter value
for
child_no (0): 0
Enter value
for
new_sql_id: 4hpk08j31nm7y
Enter value
for
profile_name (PROF_sqlid_planhash):
Enter value
for
category (
DEFAULT
):
Enter value
for
force_matching (
TRUE
):
原值 19: sql_id =
'&&sql_id'
新值 19: sql_id =
'4hpk08j31nm7y'
原值 20:
and
child_number = &&child_no
新值 20:
and
child_number = 0
原值 27: decode(
'&&profile_name'
,
'X0X0X0X0'
,
'PROF_&&sql_id'
||
'_'
||plan_hash_value,
'&&profile_name'
)
新值 27: decode(
'X0X0X0X0'
,
'X0X0X0X0'
,
'PROF_4hpk08j31nm7y'
||
'_'
||plan_hash_value,
'X0X0X0X0'
)
原值 33: sql_id =
'&&new_sql_id'
;
新值 33: sql_id =
'4hpk08j31nm7y'
;
原值 38: category =>
'&&category'
,
新值 38: category =>
'DEFAULT'
,
原值 40: force_match => &&force_matching
新值 40: force_match =>
TRUE
原值 51: dbms_output.put_line(
'ERROR: sql_id: '
||
'&&sql_id'
||
' Child: '
||
'&&child_no'
||
' not found in v$sql.'
);
新值 51: dbms_output.put_line(
'ERROR: sql_id: '
||
'4hpk08j31nm7y'
||
' Child: '
||
'0'
||
' not found in v$sql.'
);
原值 52: dbms_output.put_line(
'ERROR: sql_id: '
||
'&&new_sql_id'
||
' not found in v$sqlarea.'
);
新值 52: dbms_output.put_line(
'ERROR: sql_id: '
||
'4hpk08j31nm7y'
||
' not found in v$sqlarea.'
);
SQL Profile PROF_4hpk08j31nm7y_1404472509 created.
|
执行相同的SQL语句运行 http://item.taobao.com/item.htm?id=41222768202
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
select
*
from
scott.emp
where
deptno=30
select
*
from
table
(dbms_xplan.display_cursor(
'4hpk08j31nm7y'
,
null
))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select
*
from
scott.emp
where
deptno=30
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 3 (100)| |
|* 1 |
TABLE
ACCESS
FULL
| EMP | 6 | 228 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
1 - filter(
"DEPTNO"
=30)
Note
-----
- SQL profile PROF_4hpk08j31nm7y_1404472509 used
for
this statement
|
在Note信息中可以看到sql profile的信息， - http://item.taobao.com/item.htm?id=41222768202，说明已经强制使用了手工绑定的执行计划，之后这个语句就一直采用全表扫描了，不会再走索引的访问方式
二、SQL绑定AWR中的执行计划
shared pool中之前没有合适的执行计划，你可以在awr（DBMS_XPLAN.DISPLAY_AWR）中查找历史的执行计划，查询到了，采用create_sql_profile_awr这个过程来绑定
构造一个SQL语句两个执行计划，保存到AWR中
|
01
02
03
04
05
06
07
08
09
10
11
|
select
*
from
scott.emp
where
deptno=30
select
*
from
table
(dbms_xplan.display_cursor(
'4hpk08j31nm7y'
,
null
))
alter
session
set
optimizer_index_cost_adj=500
select
*
from
scott.emp
where
deptno=30
select
*
from
table
(dbms_xplan.display_cursor(
'4hpk08j31nm7y'
,
null
))
execute
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
|
查看AWR中的执行计划
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
select
*
from
table
(dbms_xplan.display_cursor(
'4hpk08j31nm7y'
,
null
))
SQL_ID 4hpk08j31nm7y
--------------------
select
*
from
scott.emp
where
deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 2 (100)| |
| 1 |
TABLE
ACCESS
BY
INDEX
ROWID| EMP | 6 | 522 | 2 (0)| 00:00:01 |
| 2 |
INDEX
RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Note
-----
-
dynamic
sampling used
for
this statement (
level
=2)
SQL_ID 4hpk08j31nm7y
--------------------
select
*
from
scott.emp
where
deptno=30
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 3 (100)| |
| 1 |
TABLE
ACCESS
FULL
| EMP | 6 | 522 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
-
dynamic
sampling used
for
this statement (
level
=2)
|
使SQL语句固定走索引的处理模式
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set
serveroutput
on
SQL> @create_sql_profile_awr.sql
Enter value
for
sql_id: 4hpk08j31nm7y
Enter value
for
plan_hash_value: 1404472509
Enter value
for
profile_name (PROF_sqlid_planhash):
Enter value
for
category (
DEFAULT
):
Enter value
for
force_matching (
FALSE
):
TRUE
原值 19: sql_id =
'&&sql_id'
新值 19: sql_id =
'4hpk08j31nm7y'
原值 20:
and
plan_hash_value = &&plan_hash_value
新值 20:
and
plan_hash_value = 1404472509
原值 27: decode(
'&&profile_name'
,
'X0X0X0X0'
,
'PROF_&&sql_id'
||
'_'
||
'&&plan_hash_value'
,
'&&profile_name'
)
新值 27: decode(
'X0X0X0X0'
,
'X0X0X0X0'
,
'PROF_4hpk08j31nm7y'
||
'_'
||
'1404472509'
,
'X0X0X0X0'
)
原值 33: sql_id =
'&&sql_id'
;
新值 33: sql_id =
'4hpk08j31nm7y'
;
原值 38: category =>
'&&category'
,
新值 38: category =>
'DEFAULT'
,
原值 40: force_match => &&force_matching
新值 40: force_match =>
TRUE
原值 51: dbms_output.put_line(
'ERROR: sql_id: '
||
'&&sql_id'
||
' Plan: '
||
'&&plan_hash_value'
||
' not found in AWR.'
);
新值 51: dbms_output.put_line(
'ERROR: sql_id: '
||
'4hpk08j31nm7y'
||
' Plan: '
||
'1404472509'
||
' not found in AWR.'
);
SQL Profile PROF_4hpk08j31nm7y_1404472509
create
|
重新执行SQL语句并查看执行计划 http://item.taobao.com/item.htm?id=41222768202
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
select
*
from
scott.emp
where
deptno=30
select
*
from
table
(dbms_xplan.display_cursor(
null
,
null
))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select
*
from
scott.emp
where
deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 2 (100)| |
| 1 |
TABLE
ACCESS
BY
INDEX
ROWID| EMP | 4 | 348 | 2 (0)| 00:00:01 |
|* 2 |
INDEX
RANGE SCAN | INDEX_EMP_DEPTNO | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - access(
"DEPTNO"
=30)
Note
-----
- SQL profile PROF_4hpk08j31nm7y_1404472509 used
for
this statement
|
note 信息中已经使用了sql profile，而且语句也是走索引
三、SQL绑定构造的执行计划
shared pool和awr中没有一个合适的，需要自己构造这个sql语句的执行计划，进行偷梁换柱
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
|
select
*
from
scott.emp
where
deptno=30
select
*
from
table
(dbms_xplan.display_cursor(
'4hpk08j31nm7y'
,
null
))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select
*
from
scott.emp
where
deptno=30
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 3 (100)| |
|* 1 |
TABLE
ACCESS
FULL
| EMP | 6 | 228 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
1 - filter(
"DEPTNO"
=30)
|
可以构造一个走deptno索引的，在走索引的执行计划去替换全表
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
select
/*+index(emp index_emp_deptno)*/
*
from
scott.emp
where
deptno=30
select
*
from
table
(dbms_xplan.display_cursor(
null
,
null
))
SQL_ID 2hdyvqk9b09va, child number 0
-------------------------------------
select
/*+index(emp index_emp_deptno)*/
*
from
scott.emp
where
deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 10 (100)| |
| 1 |
TABLE
ACCESS
BY
INDEX
ROWID| EMP | 6 | 228 | 10 (0)| 00:00:01 |
|* 2 |
INDEX
RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - access(
"DEPTNO"
=30)
|
可以使用SQL_ID 2hdyvqk9b09va, child number 0，来替换之前SQL_ID 4hpk08j31nm7y, child number 0的执行计划
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
sys@GULL> @create_sql_profile
Enter value
for
sql_id: 2hdyvqk9b09va
Enter value
for
child_no (0): 0
Enter value
for
new_sql_id: 4hpk08j31nm7y
Enter value
for
profile_name (PROF_sqlid_planhash):
Enter value
for
category (
DEFAULT
):
Enter value
for
force_matching (
TRUE
):
原值 19: sql_id =
'&&sql_id'
新值 19: sql_id =
'2hdyvqk9b09va'
原值 20:
and
child_number = &&child_no
新值 20:
and
child_number = 0
原值 27: decode(
'&&profile_name'
,
'X0X0X0X0'
,
'PROF_&&sql_id'
||
'_'
||plan_hash_value,
'&&profile_name'
)
新值 27: decode(
'X0X0X0X0'
,
'X0X0X0X0'
,
'PROF_2hdyvqk9b09va'
||
'_'
||plan_hash_value,
'X0X0X0X0'
)
原值 33: sql_id =
'&&new_sql_id'
;
新值 33: sql_id =
'4hpk08j31nm7y'
;
原值 38: category =>
'&&category'
,
新值 38: category =>
'DEFAULT'
,
原值 40: force_match => &&force_matching
新值 40: force_match =>
TRUE
原值 51: dbms_output.put_line(
'ERROR: sql_id: '
||
'&&sql_id'
||
' Child: '
||
'&&child_no'
||
' not found in v$sql.'
);
新值 51: dbms_output.put_line(
'ERROR: sql_id: '
||
'2hdyvqk9b09va'
||
' Child: '
||
'0'
||
' not found in v$sql.'
);
原值 52: dbms_output.put_line(
'ERROR: sql_id: '
||
'&&new_sql_id'
||
' not found in v$sqlarea.'
);
新值 52: dbms_output.put_line(
'ERROR: sql_id: '
||
'4hpk08j31nm7y'
||
' not found in v$sqlarea.'
);
SQL Profile PROF_2hdyvqk9b09va_3956160932 created.
|
再次查看原始语句的执行计划 http://item.taobao.com/item.htm?id=41222768202
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
select
*
from
scott.emp
where
deptno=30
select
*
from
table
(dbms_xplan.display_cursor(
'4hpk08j31nm7y'
,
null
))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select
*
from
scott.emp
where
deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 10 (100)| |
| 1 |
TABLE
ACCESS
BY
INDEX
ROWID| EMP | 6 | 228 | 10 (0)| 00:00:01 |
|* 2 |
INDEX
RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - access(
"DEPTNO"
=30)
Note
-----
- SQL profile PROF_2hdyvqk9b09va_3956160932 used
for
this statement
|
偷梁换柱完成，操作起来也是很方便。
四、查看sql profile hint信息
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
|
SQL> @sql_profile_hints.sql
Enter value
for
profile_name: PROF_4hpk08j31nm7y_1404472509
原值 19:
'and name like ('
'&&profile_name'
') '
||
新值 19:
'and name like ('
'PROF_4hpk08j31nm7y_1404472509'
') '
||
原值 38:
'and p.name like ('
'&&profile_name'
')) '
||
新值 38:
'and p.name like ('
'PROF_4hpk08j31nm7y_1404472509'
')) '
||
HINT
--------------------------------------------------------------------------------
----------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(
'11.2.0.3'
)
DB_VERSION(
'11.2.0.3'
)
ALL_ROWS
OUTLINE_LEAF(@
"SEL$1"
)
INDEX_RS_ASC(@
"SEL$1"
"EMP"
@
"SEL$1"
(
"EMP"
.
"DEPTNO"
))
6
rows
selected.
|
7890
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
