SQLT导入测试数据，及SQL Profile的使用
SQL> CONN TC34646/TC34646SQL> 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:
------------------------
SELECTDistinct 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_subjectWHERE
((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>
页:
[1]