oracle 10g采用 sql profile :两种模式
- 从SQL语句历史的执行计划,找到一个合理的,进行绑定
- 还有一种无法从历史的执行计划找到合理的,只能手工构造进行绑定
提供脚本
create_sql_profile
提供绑定shared pool中已有的执行计划中,找一个绑定或自己构造一个绑定
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
CREATE_SQL_PROFILE_AWR
绑定AWR中历史的计划中其他一个
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
sql_profile_hints
显示sql profile中的HINT信息
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
一、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.
|