性能分析过程中，经常会遇到生产库出现SQL的性能问题，但是，我们没有办法在生产库上做很多动作，需要将这个SQL的对应的表结构信息，统计信息导入到测试库进行测试（没有真实的测试数据，只有统计信息）
本次测试目的，客户环境导出了Test和product环境的相同SQL的不同执行计划的SQLT报告。
TEST环境是11.2.0.1版本
Product是11.2.0.2版本
我们会将两个SQLT报告分别导入到11.2.0.1和11.2.0.2的测试环境中，然后将11.2.0.1版本，好的执行计划，作为profile导出到11.2.0.2中。让11.2.0.2也根据11.2.0.1中的SQL一样，使用相同的执行计划。
~~~~~~~第一部分，导入testcase环境，只导入统计信息，和表的元数据。没有真实数据~~~~~~
1. 参考sqlt_s39917_readme.html（客户SQLT输出的文件中，就已经包含这个readme.html文件）
也可以参考文档How to Use SQLT (SQLTXPLAIN) to Create a Testcase Without Row Data (Doc ID 1470811.1)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
Implement SQLT Test
Case
(TC)
Custom mode
Create
test
case
user
and
schema
objects connecting
as
SYSDBA:
sqlplus /
as
sysdba
START sqlt_s39917_metadata.sql
Purge pre-existing s39917
from
local
SQLT repository connected
as
SYSDBA:
START sqlt_s39917_purge.sql
Import SQLT repository
for
s39917 (provide SQLTXPLAIN
password
):
HOS imp sqltxplain FILE=sqlt_s39917_exp.dmp LOG=sqlt_s39917_imp.log TABLES=sqlt%
IGNORE
=Y
Restore CBO
schema
statistics
for
test
case
user
connected
as
SYSDBA:
START sqlt_s39917_restore.sql
Restore CBO system
statistics
connected
as
SYSDBA:
START sqlt_s39917_system_stats.sql
Set
the CBO environment connecting
as
test
case
user
TC39917 (include optional test
case
user
suffix):
CONN TC39917/TC39917
START sqlt_s39917_set_cbo_env.sql
Execute
test
case
:
START tc.sql
|
2. 执行tc.sql
注意：每次sqlplus连接进入后，都需要执行最后两步
|
1
2
3
4
|
CONN TC39917/TC39917
START sqlt_s39917_set_cbo_env.sql
Execute
test
case
:
START tc.sql
----需要修改q.sql中对应表的用户名为TC39917
|
TC.sql执行结果如下
|
1
2
3
4
5
6
7
8
9
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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
SQL> start tc.sql
SQL> REM Executes SQL
on
TC
then
produces execution plan. Just
execute
"@tc.sql"
from
sqlplus.
SQL>
SET
APPI
OFF
SERVEROUT
OFF
;
SQL> @@q.sql
SQL>
SELECT
Distinct
fc_acc.blnctr_acc,
2 fc_acc.shortcut,
3 fc_acc.acc_name,
4 fc_subject.amount_money_sign,
5 fc_acc.subject_code,
6 fc_acc.corp_code,
7 fc_acc.net_code
8
FROM
TC39917.fc_acc, TC39917.fc_user_corp, TC39917.fc_subject
9
WHERE
((fc_acc.corp_code = fc_user_corp.corp_code)
OR
10 (acc_cussent
is
not
Null
or
acc_cussent <>
''
))
11
AND
(fc_acc.subject_code = fc_subject.subject_code)
12
and
((fc_acc.checker_code
is
Not
Null
))
13
AND
(fc_acc.acc_state <>
'R'
)
14
AND
(fc_acc.cur_code = 01)
15
and
(fc_acc.acc_state <>
'D'
)
16
AND
(fc_acc.net_code = 1000)
17
AND
(fc_acc.acc_type_code =
'02'
or
fc_subject.sys_subject_Code =
'1010'
or
18 (union_acc
is
not
Null
or
union_acc <>
''
)) ;
no
rows
selected
SQL> @@plan.sql
SQL> REM Displays plan
for
most recently executed SQL. Just
execute
"@plan.sql"
from
sqlplus.
SQL>
SET
PAGES 2000 LIN 180;
SQL> SPO plan.log;
SQL>
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
SQL>
SELECT
*
FROM
TABLE
(DBMS_XPLAN.DISPLAY_CURSOR(
NULL
,
NULL
,
'BASIC ROWS COST PREDICATE'
));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT
Distinct
fc_acc.blnctr_acc, fc_acc.shortcut,
fc_acc.acc_name,
fc_subject.amount_money_sign, fc_acc.subject_code,
fc_acc.corp_code, fc_acc.net_code
FROM
fc_acc, fc_user_corp, fc_subject
WHERE
((fc_acc.corp_code =
fc_user_corp.corp_code)
OR
(acc_cussent
is
not
Null
or
acc_cussent <>
''
))
AND
(fc_acc.subject_code =
fc_subject.subject_code)
and
((fc_acc.checker_code
is
Not
Null
))
AND
(fc_acc.acc_state <>
'R'
)
AND
(fc_acc.cur_code = 01)
and
(fc_acc.acc_state <>
'D'
)
AND
(fc_acc.net_code = 1000)
AND
(fc_acc.acc_type_code =
'02'
or
fc_subject.sys_subject_Code =
'1010'
or
(union_acc
is
not
Null
or
union_acc <>
''
))
Plan hash value: 31035937
------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Cost (%CPU)|
------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | 133 (100)|
| 1 | HASH
UNIQUE
| | 4573K| 133 (3)|
| 2 | CONCATENATION | | | |
| 3 | MERGE
JOIN
CARTESIAN | | 1 | 42 (0)|
| 4 | NESTED LOOPS | | | |
| 5 | NESTED LOOPS | | 1 | 36 (0)|
|* 6 |
TABLE
ACCESS
FULL
| FC_ACC | 1 | 35 (0)|
|* 7 |
INDEX
UNIQUE
SCAN | PK_FC_SUBJECT | 1 | 0 (0)|
|* 8 |
TABLE
ACCESS
BY
INDEX
ROWID| FC_SUBJECT | 1 | 1 (0)|
| 9 | BUFFER SORT | | 6210 | 41 (0)|
| 10 |
TABLE
ACCESS
FULL
| FC_USER_CORP | 6210 | 6 (0)|
| 11 | MERGE
JOIN
CARTESIAN | | 1 | 42 (0)|
| 12 | NESTED LOOPS | | | |
| 13 | NESTED LOOPS | | 1 | 36 (0)|
|* 14 |
TABLE
ACCESS
FULL
| FC_ACC | 1 | 35 (0)|
|* 15 |
INDEX
UNIQUE
SCAN | PK_FC_SUBJECT | 1 | 0 (0)|
|* 16 |
TABLE
ACCESS
BY
INDEX
ROWID| FC_SUBJECT | 1 | 1 (0)|
| 17 | BUFFER SORT | | 6210 | 41 (0)|
| 18 |
TABLE
ACCESS
FULL
| FC_USER_CORP | 6210 | 6 (0)|
|* 19 | HASH
JOIN
| | 122 | 47 (3)|
|* 20 | HASH
JOIN
| | 7 | 41 (3)|
|* 21 |
TABLE
ACCESS
FULL
| FC_ACC | 8 | 35 (0)|
| 22 |
TABLE
ACCESS
FULL
| FC_SUBJECT | 636 | 5 (0)|
| 23 |
TABLE
ACCESS
FULL
| FC_USER_CORP | 6210 | 6 (0)|
------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
6 - filter((TO_NUMBER(
"FC_ACC"
.
"CUR_CODE"
)=1
AND
"FC_ACC"
.
"ACC_STATE"
<>
'R'
AND
"FC_ACC"
.
"ACC_STATE"
<>
'D'
AND
"ACC_CUSSENT"
<>
''
AND
"FC_ACC"
.
"CHECKER_CODE"
IS
NOT
NULL
AND
TO_NUMBER(
"FC_ACC"
.
"NET_CODE"
)=1000))
7 - access(
"FC_ACC"
.
"SUBJECT_CODE"
=
"FC_SUBJECT"
.
"SUBJECT_CODE"
)
8 - filter((
"FC_ACC"
.
"ACC_TYPE_CODE"
=
'02'
OR
"FC_SUBJECT"
.
"SYS_SUBJECT_CODE"
=
'1010'
OR
"UNION_ACC"
IS
NOT
NULL
OR
"UNION_ACC"
<>
''
))
14 - filter((
"ACC_CUSSENT"
IS
NOT
NULL
AND
TO_NUMBER(
"FC_ACC"
.
"CUR_CODE"
)=1
AND
"FC_ACC"
.
"ACC_STATE"
<>
'R'
AND
"FC_ACC"
.
"ACC_STATE"
<>
'D'
AND
"FC_ACC"
.
"CHECKER_CODE"
IS
NOT
NULL
AND
TO_NUMBER(
"FC_ACC"
.
"NET_CODE"
)=1000
AND
LNNVL(
"ACC_CUSSENT"
<>
''
)))
15 - access(
"FC_ACC"
.
"SUBJECT_CODE"
=
"FC_SUBJECT"
.
"SUBJECT_CODE"
)
16 - filter((
"FC_ACC"
.
"ACC_TYPE_CODE"
=
'02'
OR
"FC_SUBJECT"
.
"SYS_SUBJECT_CODE"
=
'1010'
OR
"UNION_ACC"
IS
NOT
NULL
OR
"UNION_ACC"
<>
''
))
19 - access(
"FC_ACC"
.
"CORP_CODE"
=
"FC_USER_CORP"
.
"CORP_CODE"
)
20 - access(
"FC_ACC"
.
"SUBJECT_CODE"
=
"FC_SUBJECT"
.
"SUBJECT_CODE"
)
filter((
"FC_ACC"
.
"ACC_TYPE_CODE"
=
'02'
OR
"FC_SUBJECT"
.
"SYS_SUBJECT_CODE"
=
'1010'
OR
"UNION_ACC"
IS
NOT
NULL
OR
"UNION_ACC"
<>
''
))
21 - filter((TO_NUMBER(
"FC_ACC"
.
"CUR_CODE"
)=1
AND
"FC_ACC"
.
"ACC_STATE"
<>
'R'
AND
"FC_ACC"
.
"ACC_STATE"
<>
'D'
AND
"FC_ACC"
.
"CHECKER_CODE"
IS
NOT
NULL
AND
TO_NUMBER(
"FC_ACC"
.
"NET_CODE"
)=1000
AND
LNNVL(
"ACC_CUSSENT"
IS
NOT
NULL
)
AND
LNNVL(
"ACC_CUSSENT"
<>
''
)))
76
rows
selected.
SQL> SPO
OFF
;
SQL>
|
假设，上面的执行计划是我们期望的，性能优异的。我需要导出为profile，并导入到性能较差的数据库
~~~~~~~~第二部分，导出11.2.0.1的profile~~~~~~~~~~~~~~~~~~~~
3. 生成profile
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> @/home/oracle/Bright-
temp
/sqlt/utl/coe_xfr_sql_profile.sql cwju3syt2mx9a 31035937
SQL> SPO coe_xfr_sql_profile.log;
SQL>
SET
DEF
ON
TERM
OFF
ECHO
ON
FEED
OFF
VER
OFF
HEA
ON
LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS
ON
TI
OFF
TIMI
OFF
SERVEROUT
ON
SIZE
1000000 NUMF
""
SQLP SQL>;
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
31035937 .014
Parameter 2:
PLAN_HASH_VALUE (required)
Values
passed
to
coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID :
"cwju3syt2mx9a"
PLAN_HASH_VALUE:
"31035937"
|
检查生成的profile执行文件，需要修改force_match => TRUE ，并且在新的数据库执行前，还需要针对新的数据库的testcase用户名，修改其中对应的表的用户名。
vi coe_xfr_sql_profile_cwju3syt2mx9a_31035937.sql
~~~~~~~~第三部分，导入性能差的SQLT到11.2.0.2，并查看执行计划~~~~~~~~~~~~~~
4. 导入SQLT_product到11.2.0.2上，查看11.2.0.2上这个SQL的执行计划
|
1
2
3
4
5
6
7
8
9
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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
SQL> SPO
OFF
;
SQL> START tc.sql
SQL> REM Executes SQL
on
TC
then
produces execution plan. Just
execute
"@tc.sql"
from
sqlplus.
SQL>
SET
APPI
OFF
SERVEROUT
OFF
;
SQL> @@q.sql
SQL>
SELECT
Distinct
fc_acc.blnctr_acc,
2 fc_acc.shortcut,
3 fc_acc.acc_name,
4 fc_subject.amount_money_sign,
5 fc_acc.subject_code,
6 fc_acc.corp_code,
7 fc_acc.net_code
8
FROM
TC34646.fc_acc, TC34646.fc_user_corp, TC34646.fc_subject
9
WHERE
((fc_acc.corp_code = fc_user_corp.corp_code)
OR
10 (acc_cussent
is
not
Null
or
acc_cussent <>
''
))
11
AND
(fc_acc.subject_code = fc_subject.subject_code)
12
and
((fc_acc.checker_code
is
Not
Null
))
13
AND
(fc_acc.acc_state <>
'R'
)
14
AND
(fc_acc.cur_code = 01)
15
and
(fc_acc.acc_state <>
'D'
)
16
AND
(fc_acc.net_code = 1000)
17
AND
(fc_acc.acc_type_code =
'02'
or
fc_subject.sys_subject_Code =
'1010'
or
18 (union_acc
is
not
Null
or
union_acc <>
''
));
no
rows
selected
SQL> @@plan.sql
SQL> REM Displays plan
for
most recently executed SQL. Just
execute
"@plan.sql"
from
sqlplus.
SQL>
SET
PAGES 2000 LIN 180;
SQL> SPO plan.log;
SQL>
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
SQL>
SELECT
*
FROM
TABLE
(DBMS_XPLAN.DISPLAY_CURSOR(
NULL
,
NULL
,
'BASIC ROWS COST PREDICATE'
));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT
Distinct
fc_acc.blnctr_acc, fc_acc.shortcut,
fc_acc.acc_name,
fc_subject.amount_money_sign, fc_acc.subject_code,
fc_acc.corp_code, fc_acc.net_code
FROM
TC34646.fc_acc, TC34646.fc_user_corp, TC34646.fc_subject
WHERE
((fc_acc.corp_code = fc_user_corp.corp_code)
OR
(acc_cussent
is
not
Null
or
acc_cussent <>
''
))
AND
(fc_acc.subject_code =
fc_subject.subject_code)
and
((fc_acc.checker_code
is
Not
Null
))
AND
(fc_acc.acc_state <>
'R'
)
AND
(fc_acc.cur_code = 01)
and
(fc_acc.acc_state <>
'D'
)
AND
(fc_acc.net_code = 1000)
AND
(fc_acc.acc_type_code =
'02'
or
fc_subject.sys_subject_Code =
'1010'
or
(union_acc
is
not
Null
or
union_acc <>
''
))
Plan hash value: 561502789
--------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Cost (%CPU)|
--------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | 278K(100)|
| 1 | HASH
UNIQUE
| | 7469K| 278K (1)|
|* 2 | HASH
JOIN
| | 7469K| 3321 (6)|
|* 3 |
TABLE
ACCESS
FULL
| FC_ACC | 1156 | 38 (3)|
| 4 | MERGE
JOIN
CARTESIAN| | 4189K| 3197 (3)|
| 5 |
TABLE
ACCESS
FULL
| FC_SUBJECT | 636 | 5 (0)|
| 6 | BUFFER SORT | | 6587 | 3192 (3)|
| 7 |
TABLE
ACCESS
FULL
| FC_USER_CORP | 6587 | 5 (0)|
--------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - access(
"FC_ACC"
.
"SUBJECT_CODE"
=
"FC_SUBJECT"
.
"SUBJECT_CODE"
)
filter(((
"FC_ACC"
.
"CORP_CODE"
=
"FC_USER_CORP"
.
"CORP_CODE"
OR
"ACC_CUSSENT"
IS
NOT
NULL
OR
"ACC_CUSSENT"
<>
''
)
AND
(
"FC_ACC"
.
"ACC_TYPE_CODE"
=
'02'
OR
"FC_SUBJECT"
.
"SYS_SUBJECT_CODE"
=
'1010'
OR
"UNION_ACC"
IS
NOT
NULL
OR
"UNION_ACC"
<>
''
)))
3 - filter((TO_NUMBER(
"FC_ACC"
.
"CUR_CODE"
)=1
AND
"FC_ACC"
.
"ACC_STATE"
<>
'D'
AND
"FC_ACC"
.
"CHECKER_CODE"
IS
NOT
NULL
AND
"FC_ACC"
.
"ACC_STATE"
<>
'R'
AND
TO_NUMBER(
"FC_ACC"
.
"NET_CODE"
)=1000))
42
rows
selected.
SQL> SPO
OFF
;
|
5. 在将11.2.0.1的profile导入到11.2.0.2，然后查看profile是否生效
执行coe_xfr_sql_profile_cwju3syt2mx9a_31035937.sql前，选需要修改SQL文本为
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT
Distinct
fc_acc.blnctr_acc,
fc_acc.shortcut,
fc_acc.acc_name,
fc_subject.amount_money_sign,
fc_acc.subject_code,
fc_acc.corp_code,
fc_acc.net_code
FROM
TC34646.fc_acc, <<<<<<<<<<<<<<<<<<<<增加用户TC34646
TC34646.fc_user_corp,
TC34646.fc_subject
|
6. 执行coe脚本
|
1
2
|
sqlplus /
as
sysdba
SQL>@/home/oracle/Bright-
temp
/sqlt_s39917_xecute_TEST/coe_xfr_sql_profile_cwju3syt2mx9a_31035937.sql
|
7. 再次执行tc.sql
|
1
2
3
4
5
6
7
8
9
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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
SQL> CONN TC34646/TC34646
SQL> START sqlt_s34646_set_cbo_env.sql
SQL> START tc.sql
SQL> REM Executes SQL
on
TC
then
produces execution plan. Just
execute
"@tc.sql"
from
sqlplus.
SQL>
SET
APPI
OFF
SERVEROUT
OFF
;
SQL> @@q.sql
SQL>
SELECT
Distinct
fc_acc.blnctr_acc,
2 fc_acc.shortcut,
3 fc_acc.acc_name,
4 fc_subject.amount_money_sign,
5 fc_acc.subject_code,
6 fc_acc.corp_code,
7 fc_acc.net_code
8
FROM
TC34646.fc_acc, TC34646.fc_user_corp, TC34646.fc_subject
9
WHERE
((fc_acc.corp_code = fc_user_corp.corp_code)
OR
10 (acc_cussent
is
not
Null
or
acc_cussent <>
''
))
11
AND
(fc_acc.subject_code = fc_subject.subject_code)
12
and
((fc_acc.checker_code
is
Not
Null
))
13
AND
(fc_acc.acc_state <>
'R'
)
14
AND
(fc_acc.cur_code = 01)
15
and
(fc_acc.acc_state <>
'D'
)
16
AND
(fc_acc.net_code = 1000)
17
AND
(fc_acc.acc_type_code =
'02'
or
fc_subject.sys_subject_Code =
'1010'
or
18 (union_acc
is
not
Null
or
union_acc <>
''
));
no
rows
selected
SQL> @@plan.sql
SQL> REM Displays plan
for
most recently executed SQL. Just
execute
"@plan.sql"
from
sqlplus.
SQL>
SET
PAGES 2000 LIN 180;
SQL> SPO plan.log;
SQL>
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
SQL>
SELECT
*
FROM
TABLE
(DBMS_XPLAN.DISPLAY_CURSOR(
NULL
,
NULL
,
'BASIC ROWS COST PREDICATE'
));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT
Distinct
fc_acc.blnctr_acc, fc_acc.shortcut,
fc_acc.acc_name,
fc_subject.amount_money_sign, fc_acc.subject_code,
fc_acc.corp_code, fc_acc.net_code
FROM
TC34646.fc_acc, TC34646.fc_user_corp, TC34646.fc_subject
WHERE
((fc_acc.corp_code = fc_user_corp.corp_code)
OR
(acc_cussent
is
not
Null
or
acc_cussent <>
''
))
AND
(fc_acc.subject_code =
fc_subject.subject_code)
and
((fc_acc.checker_code
is
Not
Null
))
AND
(fc_acc.acc_state <>
'R'
)
AND
(fc_acc.cur_code = 01)
and
(fc_acc.acc_state <>
'D'
)
AND
(fc_acc.net_code = 1000)
AND
(fc_acc.acc_type_code =
'02'
or
fc_subject.sys_subject_Code =
'1010'
or
(union_acc
is
not
Null
or
union_acc <>
''
))
Plan hash value: 31035937
------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Cost (%CPU)|
------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | 8755 (100)|
| 1 | HASH
UNIQUE
| | 7469K| 8755 (1)|
| 2 | CONCATENATION | | | |
| 3 | MERGE
JOIN
CARTESIAN | | 226K| 245 (3)|
| 4 | NESTED LOOPS | | | |
| 5 | NESTED LOOPS | | 34 | 73 (2)|
|* 6 |
TABLE
ACCESS
FULL
| FC_ACC | 35 | 38 (3)|
|* 7 |
INDEX
UNIQUE
SCAN | PK_FC_SUBJECT | 1 | 0 (0)|
|* 8 |
TABLE
ACCESS
BY
INDEX
ROWID| FC_SUBJECT | 1 | 1 (0)|
| 9 | BUFFER SORT | | 6587 | 244 (3)|
| 10 |
TABLE
ACCESS
FULL
| FC_USER_CORP | 6587 | 5 (0)|
| 11 | MERGE
JOIN
CARTESIAN | | 2285 | 46 (3)|
| 12 | NESTED LOOPS | | | |
| 13 | NESTED LOOPS | | 1 | 39 (3)|
|* 14 |
TABLE
ACCESS
FULL
| FC_ACC | 1 | 38 (3)|
|* 15 |
INDEX
UNIQUE
SCAN | PK_FC_SUBJECT | 1 | 0 (0)|
|* 16 |
TABLE
ACCESS
BY
INDEX
ROWID| FC_SUBJECT | 1 | 1 (0)|
| 17 | BUFFER SORT | | 6587 | 45 (3)|
| 18 |
TABLE
ACCESS
FULL
| FC_USER_CORP | 6587 | 7 (0)|
|* 19 | HASH
JOIN
| | 193 | 51 (4)|
|* 20 | HASH
JOIN
| | 11 | 43 (3)|
|* 21 |
TABLE
ACCESS
FULL
| FC_ACC | 11 | 38 (3)|
| 22 |
TABLE
ACCESS
FULL
| FC_SUBJECT | 636 | 5 (0)|
| 23 |
TABLE
ACCESS
FULL
| FC_USER_CORP | 6587 | 7 (0)|
------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
6 - filter((TO_NUMBER(
"FC_ACC"
.
"CUR_CODE"
)=1
AND
"ACC_CUSSENT"
<>
''
AND
"FC_ACC"
.
"ACC_STATE"
<>
'D'
AND
"FC_ACC"
.
"CHECKER_CODE"
IS
NOT
NULL
AND
"FC_ACC"
.
"ACC_STATE"
<>
'R'
AND
TO_NUMBER(
"FC_ACC"
.
"NET_CODE"
)=1000))
7 - access(
"FC_ACC"
.
"SUBJECT_CODE"
=
"FC_SUBJECT"
.
"SUBJECT_CODE"
)
8 - filter((
"FC_ACC"
.
"ACC_TYPE_CODE"
=
'02'
OR
"FC_SUBJECT"
.
"SYS_SUBJECT_CODE"
=
'1010'
OR
"UNION_ACC"
IS
NOT
NULL
OR
"UNION_ACC"
<>
''
))
14 - filter((
"ACC_CUSSENT"
IS
NOT
NULL
AND
TO_NUMBER(
"FC_ACC"
.
"CUR_CODE"
)=1
AND
"FC_ACC"
.
"ACC_STATE"
<>
'D'
AND
"FC_ACC"
.
"CHECKER_CODE"
IS
NOT
NULL
AND
"FC_ACC"
.
"ACC_STATE"
<>
'R'
AND
TO_NUMBER(
"FC_ACC"
.
"NET_CODE"
)=1000
AND
LNNVL(
"ACC_CUSSENT"
<>
''
)))
15 - access(
"FC_ACC"
.
"SUBJECT_CODE"
=
"FC_SUBJECT"
.
"SUBJECT_CODE"
)
16 - filter((
"FC_ACC"
.
"ACC_TYPE_CODE"
=
'02'
OR
"FC_SUBJECT"
.
"SYS_SUBJECT_CODE"
=
'1010'
OR
"UNION_ACC"
IS
NOT
NULL
OR
"UNION_ACC"
<>
''
))
19 - access(
"FC_ACC"
.
"CORP_CODE"
=
"FC_USER_CORP"
.
"CORP_CODE"
)
20 - access(
"FC_ACC"
.
"SUBJECT_CODE"
=
"FC_SUBJECT"
.
"SUBJECT_CODE"
)
filter((
"FC_ACC"
.
"ACC_TYPE_CODE"
=
'02'
OR
"FC_SUBJECT"
.
"SYS_SUBJECT_CODE"
=
'1010'
OR
"UNION_ACC"
IS
NOT
NULL
OR
"UNION_ACC"
<>
''
))
21 - filter((TO_NUMBER(
"FC_ACC"
.
"CUR_CODE"
)=1
AND
"FC_ACC"
.
"ACC_STATE"
<>
'D'
AND
"FC_ACC"
.
"CHECKER_CODE"
IS
NOT
NULL
AND
"FC_ACC"
.
"ACC_STATE"
<>
'R'
AND
TO_NUMBER(
"FC_ACC"
.
"NET_CODE"
)=1000
AND
LNNVL(
"ACC_CUSSENT"
IS
NOT
NULL
)
AND
LNNVL(
"ACC_CUSSENT"
<>
''
)))
74
rows
selected.
SQL> SPO
OFF
;
SQL>
|
最终，可以看到，在11.2.0.2上，也按照11.2.0.1上的执行计划进行执行SQL了。
这里要强调一下SQLT真的是非常使用的SQL 调优工具。而且非常易用，很多步骤都在SQLT的HTML文件中，逐步指导。建议大家学习使用。
本文转自 hsbxxl 51CTO博客，原文链接：http://blog.51cto.com/hsbxxl/1906890，如需转载请自行联系原作者
