1. 通过@?/rdbms/admin/sqltrpt.sql 查询SQL改进方案
Enter value for sqlid: af079a8aqu45r
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_504
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/14/2022 15:46:59
Completed at : 06/14/2022 15:47:33
-------------------------------------------------------------------------------
Schema Name: EASYTONG
SQL ID : af079a8aqu45r
SQL Text : select count(1)
from (select a.ACCNUM, a.ACCNAME, a.PERCODE, a.EPID, a.ACCDEPID,a.ACCTYPE, a.SEX, a.POSTCODE, a.PHONENO, a.EMAIL,
a.QQ, a.IDTYPE, a.IDNO, a.BIRTHDAY, a.COUNTRY,a.NATIVEPLACE, a.NATION, a.POLITICS, a.ACCSTATUS,
a.PLEDGE,a.QUERYPWD, a.PAYPWD, a.ISDEFAULTPWD,a.JOINDATE,a.OPENDATE, a.DISABLEDATE, a.CANCELLTIME,
a.ISINTERNAL,a.FREEZEPRIORSTATUS, a.ISEQUIPCARD,a.ISFREEZED,
a.FREEZEDATE, a.FREEZETIME, a.REMARK, a.OPENMONEY,a.EWALLETID,a.DURATION,a.EMAILPWD,
c.CAMPUSNAME as CAMPUSNAME,d.ACCDEPNAME as ACCDEPNAME,d.NAMEPATH as ACCDEPNAMEPATH,
f.ACCCLASSNAME as ACCCLASSNAME,k.DICTNAME as ACCTYPESTR,i.DICTNAME as SEXSTR,
b.DICTNAME as COUNTRYSTR, h.DICTNAME as IDTYPESTR,n.DICTNAME as NATIONSTR,
m.DICTNAME as POLITICSSTR,g.DICTNAME as ACCSTATUSSTR,sa.ACCDEPNAME as PARENTACCDEPNAME,
sa.SHORTNAME as PARENTSHORTNAME, e.ACCCLASSID as ACCCLASSNUM, e.CAMPUSID,
sduty.DICTNAME as DUTYSTR, s.DICTNAME as ACCCLASSTYPESTR, sep.EPNAME as EPNAME,
epg.GROUPNAME as EPGROUPNAME, e.MODIDATE, d.SHORTNAME, staff.WORKCODE, staff.SHORTOFFICETILTE,
ous.OKEY1 as USERID, aap.ACCNUM as imageFileUUid
from (select *
from AM_ACCOUNT a ORDER BY a.ACCNUM DESC) a
left join AM_ACCOUNT_ACCCLASS e on e.ACCNUM=a.ACCNUM and e.RECFLAG=:1 and e.EPID = :2
left join AM_STAFF staff on staff.ACCNUM = a.ACCNUM
left join SC_ENTERPRICE sep on a.epid = sep.epid
left join SC_ENTERPRICEGROUP epg on epg.groupnum = sep.groupnum
left join SC_DICT_EP sduty on sduty.TYPENUM = :3 and sduty.DICTNUM = staff.DUTY and sduty.EPID = e.EPID
left join SC_ACCCLASS f on f.ACCCLASSID=e.ACCCLASSID and f.EPID=e.EPID
left join SC_DICT_EP s on s.TYPENUM=:4 and s.DICTNUM=f.CLASSTYPE and s.EPID = e.EPID
left join SC_CAMPUS c on c.CAMPUSID=e.CAMPUSID and c.EPID = e.EPID
left join SC_ACCDEP d on d.ACCDEPID=a.ACCDEPID and d.EPID = a.EPID
left join SC_DICT_EP k on k.TYPENUM=:5 and k.DICTNUM=a.ACCTYPE and k.EPID = e.EPID
left join SC_DICT_EP i on i.TYPENUM=:6 and i.DICTNUM=a.SEX and i.EPID = e.EPID
left join SC_DICT_EP b on b.TYPENUM=:7 and b.DICTNUM=a.COUNTRY and b.EPID = e.EPID
left join SC_DICT_EP h on h.TYPENUM=:8 and h.DICTNUM=a.IDTYPE and h.EPID = e.EPID
left join SC_DICT_EP n on n.TYPENUM=:9 and n.DICTNUM=a.NATION and n.EPID = e.EPID
left join SC_DICT_EP m on m.TYPENUM=:10 and m.DICTNUM=a.POLITICS and m.EPID = e.EPID
left join SC_DICT_EP g on g.TYPENUM=:11 and g.DICTNUM=a.ACCSTATUS and g.EPID = e.EPID
left join SC_ACCDEP sa on sa.ACCDEPID=d.PARENTID and sa.EPID = e.EPID
left join SC_ENTERPRICE ep on ep.EPID=a.EPID
left join OM_USERCENTER_SYNC ous on ous.EPID = a.EPID and ous.SYNNCTYPE = 5 and ous.KEY1 = a.ACCNUM
left join AM_ACCOUNT_PHOTO aap on a.ACCNUM = aap.ACCNUM and aap.PHOTOTYPE = 1
where a.epid = :12
and a.ISINTERNAL = :13
and a.ACCSTATUS in ( :14 )
and not exists(select 1
from AM_ACCOUNTCARD j
where j.CARDSTATUS <> :15 and a.ACCNUM = j.ACCNUM)
ORDER BY a.ACCNUM DESC) sq
Bind Variables :
1 - (NUMBER):1
2 - (NUMBER):1
3 - (NUMBER):144
4 - (NUMBER):173
5 - (NUMBER):30
6 - (NUMBER):137
7 - (NUMBER):142
8 - (NUMBER):28
9 - (NUMBER):143
10 - (NUMBER):141
11 - (NUMBER):12
12 - (NUMBER):1
13 - (NUMBER):0
14 - (NUMBER):-1
15 - (NUMBER):0
-------------------------------------------------------------------------------
FINDINGS SECTION (11 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for table "EASYTONG"."AM_ACCOUNT_ACCCLASS" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'EASYTONG', tabname => 'AM_ACCOUNT_ACCCLASS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
2- Statistics Finding
---------------------
Optimizer statistics for table "EASYTONG"."AM_ACCOUNT" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'EASYTONG', tabname => 'AM_ACCOUNT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices in order to select a good execution plan.
3- Statistics Finding
---------------------
Optimizer statistics for table "EASYTONG"."SC_ACCCLASS" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'EASYTONG', tabname => 'SC_ACCCLASS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices in order to select a good execution plan.
4- Statistics Finding
---------------------
Optimizer statistics for table "EASYTONG"."SC_ACCDEP" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'EASYTONG', tabname => 'SC_ACCDEP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
5- Statistics Finding
---------------------
Optimizer statistics for index "EASYTONG"."PK_AM_ACCOUNT_PHOTO" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'EASYTONG', indname => 'PK_AM_ACCOUNT_PHOTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale
---------
The optimizer requires up-to-date statistics for the index in order to select a good execution plan.
6- Statistics Finding
---------------------
Optimizer statistics for table "EASYTONG"."AM_ACCOUNT_PHOTO" and its indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'EASYTONG', tabname => 'AM_ACCOUNT_PHOTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to select a good execution plan.
7- Statistics Finding
---------------------
Optimizer statistics for index "EASYTONG"."IDX_ACCOUNTCARD1" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'EASYTONG', indname => 'IDX_ACCOUNTCARD1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale
---------
The optimizer requires up-to-date statistics for the index in order to select a good execution plan.
8- Statistics Finding
---------------------
Optimizer statistics for index "EASYTONG"."FK_ACCOUNTCARD_ACCOUNT" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'EASYTONG', indname => 'FK_ACCOUNTCARD_ACCOUNT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale
---------
The optimizer requires up-to-date statistics for the index in order to select a good execution plan.
9- Statistics Finding
---------------------
Optimizer statistics for index "EASYTONG"."PK_AM_ACCOUNTCARD" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'EASYTONG', indname => 'PK_AM_ACCOUNTCARD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Rationale
---------
The optimizer requires up-to-date statistics for the index in order to select a good execution plan.
10- Statistics Finding
----------------------
Optimizer statistics for table "EASYTONG"."AM_ACCOUNTCARD" and its indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'EASYTONG', tabname => 'AM_ACCOUNTCARD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to select a good execution plan.
11- SQL Profile Finding (see explain plans section below)
---------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.87%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_504', task_owner => 'SYS', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: PARTIAL COMPLETE
Elapsed Time (s): 16.002239 .140409 99.12 %
CPU Time (s): 15.87113 .139279 99.12 %
User I/O Time (s): 0 0
Buffer Gets: 749517 913 99.87 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 0 1
Fetches: 0 1
Executions: 0 1
Notes
-----
1. Statistics for the original plan were averaged over 0 executions.
2. Statistics for the SQL profile plan were averaged over 8 executions.
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 6 of the execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1429787274
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 63 | 625M (1)|999:59:59 | | |
| 1 | SORT AGGREGATE | | 1 | 63 | | | | |
| 2 | NESTED LOOPS ANTI | | 24952 | 1535K| 625M (1)|999:59:59 | | |
| 3 | NESTED LOOPS OUTER | | 24954 | 1437K| 625M (1)|999:59:59 | | |
| 4 | NESTED LOOPS OUTER | | 24954 | 1267K| 625M (1)|999:59:59 | | |
| 5 | NESTED LOOPS OUTER | | 24954 | 1120K| 625M (1)|999:59:59 | | |
| 6 | VIEW | | 24954 | 999K| 625M (1)|999:59:59 | | |
| 7 | NESTED LOOPS OUTER | | 24954 | 5799K| 625M (1)|999:59:59 | | |
| 8 | NESTED LOOPS OUTER | | 24954 | 5531K| 625M (1)|999:59:59 | | |
| 9 | NESTED LOOPS OUTER | | 24954 | 5263K| 625M (1)|999:59:59 | | |
| 10 | NESTED LOOPS OUTER | | 24954 | 4995K| 625M (1)|999:59:59 | | |
| 11 | NESTED LOOPS OUTER | | 24954 | 4727K| 625M (1)|999:59:59 | | |
| 12 | NESTED LOOPS OUTER | | 24954 | 4459K| 625M (1)|999:59:59 | | |
| 13 | NESTED LOOPS OUTER | | 24954 | 4191K| 625M (1)|999:59:59 | | |
| 14 | NESTED LOOPS OUTER | | 24954 | 3923K| 625M (1)|999:59:59 | | |
| 15 | NESTED LOOPS OUTER | | 24954 | 3728K| 625M (1)|999:59:59 | | |
| 16 | NESTED LOOPS OUTER | | 24954 | 3582K| 625M (1)|999:59:59 | | |
| 17 | VIEW | | 24954 | 3314K| 625M (1)|999:59:59 | | |
| 18 | NESTED LOOPS OUTER | | 24954 | 4045K| 625M (1)|999:59:59 | | |
| 19 | NESTED LOOPS OUTER | | 24954 | 3899K| 625M (1)|999:59:59 | | |
| 20 | VIEW | | 24954 | 3631K| 625M (1)|999:59:59 | | |
| 21 | NESTED LOOPS OUTER | | 24954 | 2315K| 625M (1)|999:59:59 | | |
| 22 | MERGE JOIN OUTER | | 24954 | 1973K| 12532 (1)| 00:02:31 | | |
| 23 | NESTED LOOPS OUTER | | 24954 | 1827K| 12531 (1)| 00:02:31 | | |
|* 24 | TABLE ACCESS BY GLOBAL INDEX ROWID| AM_ACCOUNT | 24954 | 1194K| 12531 (1)| 00:02:31 | ROWID | ROWID |
|* 25 | INDEX RANGE SCAN | IDX_AM_ACCOUNT2 | 12478 | | 50 (0)| 00:00:01 | | |
| 26 | TABLE ACCESS BY INDEX ROWID | AM_STAFF | 1 | 26 | 0 (0)| 00:00:01 | | |
|* 27 | INDEX UNIQUE SCAN | PK_AM_STAFF | 1 | | 0 (0)| 00:00:01 | | |
| 28 | BUFFER SORT | | 1 | 6 | 12532 (1)| 00:02:31 | | |
|* 29 | INDEX UNIQUE SCAN | PK_SC_ENTERPRICE | 1 | 6 | 0 (0)| 00:00:01 | | |
|* 30 | TABLE ACCESS BY INDEX ROWID | AM_ACCOUNT_ACCCLASS | 1 | 14 | 25046 (1)| 00:05:01 | | |
|* 31 | INDEX RANGE SCAN | IDX_ACCOUNT_ACCCLASS1 | 24956 | | 85 (0)| 00:00:02 | | |
|* 32 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
| 33 | TABLE ACCESS BY INDEX ROWID | SC_ACCCLASS | 1 | 6 | 1 (0)| 00:00:01 | | |
|* 34 | INDEX UNIQUE SCAN | PK_SC_ACCCLASS | 1 | | 0 (0)| 00:00:01 | | |
|* 35 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 36 | INDEX UNIQUE SCAN | PK_SC_CAMPUS | 1 | 6 | 0 (0)| 00:00:01 | | |
| 37 | TABLE ACCESS BY INDEX ROWID | SC_ACCDEP | 1 | 8 | 4 (0)| 00:00:01 | | |
|* 38 | INDEX UNIQUE SCAN | PK_SC_ACCDEP | 1 | | 3 (0)| 00:00:01 | | |
|* 39 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 40 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 41 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 42 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 43 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 44 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 45 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 46 | INDEX UNIQUE SCAN | PK_SC_ACCDEP | 1 | 5 | 3 (0)| 00:00:01 | | |
|* 47 | INDEX RANGE SCAN | PK_AM_ACCOUNT_PHOTO | 1 | 6 | 1 (0)| 00:00:01 | | |
|* 48 | INDEX SKIP SCAN | PK_OM_USERCENTER_SYNC | 1 | 7 | 1 (0)| 00:00:01 | | |
|* 49 | TABLE ACCESS BY GLOBAL INDEX ROWID | AM_ACCOUNTCARD | 1 | 4 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 50 | INDEX RANGE SCAN | FK_ACCOUNTCARD_ACCOUNT | 1 | | 0 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
24 - filter("A"."ACCSTATUS"=:14 AND "A"."EPID"=:12)
25 - access(SYS_OP_DESCEND("ISINTERNAL")=SYS_OP_DESCEND(:13))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("ISINTERNAL"))=:13)
27 - access("STAFF"."ACCNUM"(+)="A"."ACCNUM")
29 - access("SEP"."EPID"(+)=:12)
30 - filter("E"."RECFLAG"(+)=:1 AND "E"."ACCNUM"(+)="A"."ACCNUM")
31 - access("E"."EPID"(+)=:2)
32 - access("SDUTY"."TYPENUM"(+)=:3 AND "SDUTY"."EPID"(+)="E"."EPID" AND "SDUTY"."DICTNUM"(+)="STAFF"."DUTY")
34 - access("F"."EPID"(+)="from$_subquery$_011"."QCSJ_C000000000500003" AND "F"."ACCCLASSID"(+)="from$_subquery$_011"."ACCCLASSID")
35 - access("G"."TYPENUM"(+)=:11 AND "G"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND "G"."DICTNUM"(+)=:14)
36 - access("C"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND "C"."CAMPUSID"(+)="from$_subquery$_015"."CAMPUSID")
38 - access("D"."EPID"(+)=:12 AND "D"."ACCDEPID"(+)="from$_subquery$_015"."ACCDEPID")
39 - access("H"."TYPENUM"(+)=:8 AND "H"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"H"."DICTNUM"(+)="from$_subquery$_015"."IDTYPE")
40 - access("M"."TYPENUM"(+)=:10 AND "M"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"M"."DICTNUM"(+)="from$_subquery$_015"."POLITICS")
41 - access("N"."TYPENUM"(+)=:9 AND "N"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"N"."DICTNUM"(+)="from$_subquery$_015"."NATION")
42 - access("B"."TYPENUM"(+)=:7 AND "B"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"B"."DICTNUM"(+)="from$_subquery$_015"."COUNTRY")
43 - access("K"."TYPENUM"(+)=:5 AND "K"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"K"."DICTNUM"(+)="from$_subquery$_015"."ACCTYPE")
44 - access("I"."TYPENUM"(+)=:6 AND "I"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"I"."DICTNUM"(+)="from$_subquery$_015"."SEX")
45 - access("S"."TYPENUM"(+)=:4 AND "S"."EPID"(+)="E"."EPID" AND "S"."DICTNUM"(+)="F"."CLASSTYPE")
46 - access("SA"."EPID"(+)="E"."EPID" AND "SA"."ACCDEPID"(+)="D"."PARENTID")
47 - access("from$_subquery$_035"."QCSJ_C000000000500000"="AAP"."ACCNUM"(+) AND "AAP"."PHOTOTYPE"(+)=1)
48 - access("OUS"."EPID"(+)=:12)
filter(TO_NUMBER("OUS"."SYNNCTYPE"(+))=5 AND "OUS"."EPID"(+)=:12 AND
"from$_subquery$_035"."QCSJ_C000000000500000"=TO_NUMBER("OUS"."KEY1"(+)))
49 - filter("J"."CARDSTATUS"<>:15)
50 - access("from$_subquery$_035"."QCSJ_C000000000500000"="J"."ACCNUM")
2- Using SQL Profile
--------------------
Plan hash value: 1006624635
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 63 | 195 (6)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 63 | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 24952 | 1535K| 195 (6)| 00:00:03 | | |
| 3 | INDEX FAST FULL SCAN | PK_SC_ACCDEP | 3447 | 17235 | 7 (0)| 00:00:01 | | |
|* 4 | HASH JOIN RIGHT OUTER | | 24952 | 1413K| 188 (6)| 00:00:03 | | |
|* 5 | INDEX FAST FULL SCAN | PK_AM_ACCOUNT_PHOTO | 1910 | 11460 | 5 (0)| 00:00:01 | | |
|* 6 | HASH JOIN RIGHT OUTER | | 24952 | 1267K| 183 (6)| 00:00:03 | | |
|* 7 | INDEX SKIP SCAN | PK_OM_USERCENTER_SYNC | 1 | 7 | 1 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS ANTI | | 24952 | 1096K| 182 (6)| 00:00:03 | | |
| 9 | VIEW | | 24954 | 999K| 179 (6)| 00:00:03 | | |
|* 10 | HASH JOIN RIGHT OUTER | | 24954 | 5799K| 179 (6)| 00:00:03 | | |
|* 11 | TABLE ACCESS FULL | SC_ACCDEP | 3447 | 27576 | 22 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS OUTER | | 24954 | 5604K| 157 (6)| 00:00:02 | | |
| 13 | NESTED LOOPS OUTER | | 24954 | 5336K| 157 (6)| 00:00:02 | | |
| 14 | NESTED LOOPS OUTER | | 24954 | 5068K| 157 (6)| 00:00:02 | | |
| 15 | NESTED LOOPS OUTER | | 24954 | 4800K| 157 (6)| 00:00:02 | | |
| 16 | NESTED LOOPS OUTER | | 24954 | 4532K| 157 (6)| 00:00:02 | | |
| 17 | NESTED LOOPS OUTER | | 24954 | 4264K| 156 (6)| 00:00:02 | | |
| 18 | NESTED LOOPS OUTER | | 24954 | 3996K| 154 (4)| 00:00:02 | | |
| 19 | NESTED LOOPS OUTER | | 24954 | 3728K| 153 (4)| 00:00:02 | | |
| 20 | NESTED LOOPS OUTER | | 24954 | 3460K| 152 (3)| 00:00:02 | | |
| 21 | VIEW | | 24954 | 3314K| 151 (2)| 00:00:02 | | |
|* 22 | HASH JOIN RIGHT OUTER | | 24954 | 4045K| 151 (2)| 00:00:02 | | |
| 23 | TABLE ACCESS FULL | SC_ACCCLASS | 17 | 102 | 3 (0)| 00:00:01 | | |
| 24 | NESTED LOOPS OUTER | | 24954 | 3899K| 148 (3)| 00:00:02 | | |
| 25 | VIEW | | 24954 | 3631K| 147 (2)| 00:00:02 | | |
|* 26 | HASH JOIN RIGHT OUTER | | 24954 | 2315K| 147 (2)| 00:00:02 | | |
|* 27 | TABLE ACCESS FULL | AM_ACCOUNT_ACCCLASS | 24956 | 341K| 68 (0)| 00:00:01 | | |
|* 28 | HASH JOIN RIGHT OUTER | | 24954 | 1973K| 78 (2)| 00:00:01 | | |
|* 29 | INDEX UNIQUE SCAN | PK_SC_ENTERPRICE | 1 | 6 | 0 (0)| 00:00:01 | | |
| 30 | NESTED LOOPS OUTER | | 24954 | 1827K| 78 (2)| 00:00:01 | | |
| 31 | PARTITION LIST SINGLE | | 24954 | 1194K| 77 (0)| 00:00:01 | KEY | KEY |
|* 32 | TABLE ACCESS FULL | AM_ACCOUNT | 24954 | 1194K| 77 (0)| 00:00:01 | KEY | KEY |
| 33 | TABLE ACCESS BY INDEX ROWID| AM_STAFF | 1 | 26 | 0 (0)| 00:00:01 | | |
|* 34 | INDEX UNIQUE SCAN | PK_AM_STAFF | 1 | | 0 (0)| 00:00:01 | | |
|* 35 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 36 | INDEX UNIQUE SCAN | PK_SC_CAMPUS | 1 | 6 | 0 (0)| 00:00:01 | | |
|* 37 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 38 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 39 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 40 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 41 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 42 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 43 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 44 | INDEX UNIQUE SCAN | PK_SC_DICT_EP | 1 | 11 | 0 (0)| 00:00:01 | | |
|* 45 | TABLE ACCESS BY GLOBAL INDEX ROWID | AM_ACCOUNTCARD | 1 | 4 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 46 | INDEX RANGE SCAN | FK_ACCOUNTCARD_ACCOUNT | 1 | | 0 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SA"."EPID"(+)="E"."EPID" AND "SA"."ACCDEPID"(+)="D"."PARENTID")
4 - access("from$_subquery$_035"."QCSJ_C000000000500000"="AAP"."ACCNUM"(+))
5 - filter("AAP"."PHOTOTYPE"(+)=1)
6 - access("from$_subquery$_035"."QCSJ_C000000000500000"=TO_NUMBER("OUS"."KEY1"(+)) AND
"OUS"."EPID"(+)="from$_subquery$_035"."QCSJ_C000000000500002")
7 - access("OUS"."EPID"(+)=:12)
filter(TO_NUMBER("OUS"."SYNNCTYPE"(+))=5 AND "OUS"."EPID"(+)=:12)
10 - access("D"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500002" AND "D"."ACCDEPID"(+)="from$_subquery$_015"."ACCDEPID")
11 - filter("D"."EPID"(+)=:12)
22 - access("F"."EPID"(+)="from$_subquery$_011"."QCSJ_C000000000500003" AND "F"."ACCCLASSID"(+)="from$_subquery$_011"."ACCCLASSID")
26 - access("E"."ACCNUM"(+)="A"."ACCNUM")
27 - filter("E"."EPID"(+)=:2 AND "E"."RECFLAG"(+)=:1)
28 - access("A"."EPID"="SEP"."EPID"(+))
29 - access("SEP"."EPID"(+)=:12)
32 - filter("A"."ISINTERNAL"=:13 AND "A"."ACCSTATUS"=:14 AND "A"."EPID"=:12)
34 - access("STAFF"."ACCNUM"(+)="A"."ACCNUM")
35 - access("SDUTY"."TYPENUM"(+)=:3 AND "SDUTY"."EPID"(+)="E"."EPID" AND "SDUTY"."DICTNUM"(+)="STAFF"."DUTY")
36 - access("C"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND "C"."CAMPUSID"(+)="from$_subquery$_015"."CAMPUSID")
37 - access("G"."TYPENUM"(+)=:11 AND "G"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND "G"."DICTNUM"(+)=:14)
38 - access("K"."TYPENUM"(+)=:5 AND "K"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"K"."DICTNUM"(+)="from$_subquery$_015"."ACCTYPE")
39 - access("I"."TYPENUM"(+)=:6 AND "I"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"I"."DICTNUM"(+)="from$_subquery$_015"."SEX")
40 - access("S"."TYPENUM"(+)=:4 AND "S"."EPID"(+)="E"."EPID" AND "S"."DICTNUM"(+)="F"."CLASSTYPE")
41 - access("H"."TYPENUM"(+)=:8 AND "H"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"H"."DICTNUM"(+)="from$_subquery$_015"."IDTYPE")
42 - access("M"."TYPENUM"(+)=:10 AND "M"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"M"."DICTNUM"(+)="from$_subquery$_015"."POLITICS")
43 - access("N"."TYPENUM"(+)=:9 AND "N"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"N"."DICTNUM"(+)="from$_subquery$_015"."NATION")
44 - access("B"."TYPENUM"(+)=:7 AND "B"."EPID"(+)="from$_subquery$_015"."QCSJ_C000000000500003" AND
"B"."DICTNUM"(+)="from$_subquery$_015"."COUNTRY")
45 - filter("J"."CARDSTATUS"<>:15)
46 - access("from$_subquery$_035"."QCSJ_C000000000500000"="J"."ACCNUM")
-------------------------------------------------------------------------------2. 发现大量：Statistics Finding
应该收集该用户的统计信息：
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'EASYTONG', -- oracle用户名，需要大写
cascade => true,
granularity=>'ALL' -- 收集分区表
);
END;
/3. 发现：SQL Profile Finding
接受SQLProfile
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_504', task_owner => 'SYS', replace => TRUE);4. 附录：DBMS_STATS重要参数详解
-
ownname
表示表的拥有者，不区分大小写。 -
tabname
表示表名字，不区分大小写。 -
granularity
表示收集统计信息的粒度，该选项只对分区表生效，默认为 AUTO，表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。对于该选项，我们一般采用AUTO 方式，也就是数据库默认方式，因此在后面的脚本中，省略该选项。| -
estimate_percent
表示采样率，范围是0.000 001～100。这个参数主要是用于CBO估算表的总行数，采样率越高，CBO估算的表行数越接近于真实值，执行计划越能走正确。
估算总行数=样本大小(DBA_TAB_STATISTICS.SAMPLE_SIZE）*100/采样率(estimate_percent)
这个参数可能对于很多新手来说都不知道怎么设置：
一般对小于 1GB 的表进行100%采样，因为表很小，即使100%采样速度也比较快。有时候小表有可能数据分布不均衡，如果没有100%采样，可能会导致统计信息不准。
因此建议对小表 100%采样。我们一般对表大小在1GB～5GB 的表采样50%，对大于5GB的表采样30%。如果表特别大，有几十甚至上百 GB，我们建议应该先对表进行分区，然后分别对每个分区收集统计信息。一般情况下，为了确保统计信息比较准确，我们建议采样率不要低于30%。
<1GB 建议采样比100%、1GB～5GB 建议采样比50%、>5GB 建议采样比30% -
method_opt
用于控制收集直方图策略。
直方图简单来说就是数据库了解表中某列的数据分布，从而更正确的走更优的执行计划method_opt => ‘for all columns size 1’ 表示所有列都不收集直方图 method_opt => ‘for all columns size skewonly’ 表示对表中所有列收集自动判断是否收集直方图。选择率非常高的列和null的列不会收集（谨慎使用） method_opt => ‘for all columns size auto’ 表示对出现在 where 条件中的列自动判断是否收集直方图。 method_opt => ‘for all columns size repeat’ 表示当前有哪些列收集了直方图，现在就对哪些列收集直方图。在实际工作中，当系统趋于稳定之后，使用 REPEAT 方式收集直方图。
-
no_invalidate
表示共享池中涉及到该表的游标是否立即失效，默认值为 DBMS_STATS.AUTO_INVALIDATE，表示让 Oracle 自己决定是否立即失效。
建议将 no_invalidate 参数设置为 FALSE，立即失效。因为发现有时候 SQL 执行缓慢是因为统计信息过期导致，重新收集了统计信息之后执行计划还是没有更改，原因就在于没有将这个参数设置为 false。 -
degree
表示收集统计信息的并行度，默认为 NULL。如果表没有设置 degree。如果表没有设置 degree，收集统计信息的时候后就不开并行；如果表设置了 degree，收集统计信息的时候就按照表的 degree 来开并行。
可以查询 DBA_TABLES.degree 来查看表的 degree，一般情况下，表的 degree 都为 1。我们建议可以根据当时系统的负载、系统中 CPU 的个数以及表大小来综合判断设置并行度。 -
cascade
表示在收集表的统计信息的时候，是否级联收集索引的统计信息，默认值为DBMS_STATS.AUTO_CASCADE，表示让 Oracle 自己判断是否级联收集索引的统计信息。