{"id":1800,"date":"2022-06-14T16:39:47","date_gmt":"2022-06-14T08:39:47","guid":{"rendered":"http:\/\/dba.qishuo.xin\/?p=1800"},"modified":"2022-06-14T16:39:47","modified_gmt":"2022-06-14T08:39:47","slug":"oracle-sql-tune-report","status":"publish","type":"post","link":"http:\/\/dba.qishuo.xin\/?p=1800","title":{"rendered":"Oracle sql tune report"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_76 ez-toc-wrap-right counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<label for=\"ez-toc-cssicon-toggle-item-6a0201d9414ec\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a0201d9414ec\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"http:\/\/dba.qishuo.xin\/?p=1800\/#1_%E9%80%9A%E8%BF%87rdbmsadminsqltrptsql_%E6%9F%A5%E8%AF%A2SQL%E6%94%B9%E8%BF%9B%E6%96%B9%E6%A1%88\" >1. \u901a\u8fc7@?\/rdbms\/admin\/sqltrpt.sql \u67e5\u8be2SQL\u6539\u8fdb\u65b9\u6848<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"http:\/\/dba.qishuo.xin\/?p=1800\/#2_%E5%8F%91%E7%8E%B0%E5%A4%A7%E9%87%8F%EF%BC%9AStatistics_Finding\" >2. \u53d1\u73b0\u5927\u91cf\uff1aStatistics Finding<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"http:\/\/dba.qishuo.xin\/?p=1800\/#3_%E5%8F%91%E7%8E%B0%EF%BC%9ASQL_Profile_Finding\" >3. \u53d1\u73b0\uff1aSQL Profile Finding<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"http:\/\/dba.qishuo.xin\/?p=1800\/#4_%E9%99%84%E5%BD%95%EF%BC%9ADBMS_STATS%E9%87%8D%E8%A6%81%E5%8F%82%E6%95%B0%E8%AF%A6%E8%A7%A3\" >4. \u9644\u5f55\uff1aDBMS_STATS\u91cd\u8981\u53c2\u6570\u8be6\u89e3<\/a><\/li><\/ul><\/nav><\/div>\n<h4><span class=\"ez-toc-section\" id=\"1_%E9%80%9A%E8%BF%87rdbmsadminsqltrptsql_%E6%9F%A5%E8%AF%A2SQL%E6%94%B9%E8%BF%9B%E6%96%B9%E6%A1%88\"><\/span>1. \u901a\u8fc7<code>@?\/rdbms\/admin\/sqltrpt.sql<\/code> \u67e5\u8be2SQL\u6539\u8fdb\u65b9\u6848<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style='color:red'>Enter value for sqlid:  <\/span><strong>af079a8aqu45r<\/strong><\/p>\n<pre><code class=\"language-sql\">Tune the sql\n~~~~~~~~~~~~\n\nGENERAL INFORMATION SECTION\n-------------------------------------------------------------------------------\nTuning Task Name   : TASK_504\nTuning Task Owner  : SYS\nWorkload Type      : Single SQL Statement\nScope              : COMPREHENSIVE\nTime Limit(seconds): 1800\nCompletion Status  : COMPLETED\nStarted at         : 06\/14\/2022 15:46:59\nCompleted at       : 06\/14\/2022 15:47:33\n\n-------------------------------------------------------------------------------\nSchema Name: EASYTONG\nSQL ID     : af079a8aqu45r\nSQL Text   : select count(1)\n               from (select a.ACCNUM, a.ACCNAME, a.PERCODE, a.EPID, a.ACCDEPID,a.ACCTYPE, a.SEX, a.POSTCODE, a.PHONENO, a.EMAIL,\n                            a.QQ, a.IDTYPE, a.IDNO, a.BIRTHDAY, a.COUNTRY,a.NATIVEPLACE, a.NATION, a.POLITICS, a.ACCSTATUS,\n                            a.PLEDGE,a.QUERYPWD, a.PAYPWD, a.ISDEFAULTPWD,a.JOINDATE,a.OPENDATE, a.DISABLEDATE, a.CANCELLTIME,\n                            a.ISINTERNAL,a.FREEZEPRIORSTATUS, a.ISEQUIPCARD,a.ISFREEZED,\n                            a.FREEZEDATE, a.FREEZETIME, a.REMARK, a.OPENMONEY,a.EWALLETID,a.DURATION,a.EMAILPWD,\n                            c.CAMPUSNAME as CAMPUSNAME,d.ACCDEPNAME as ACCDEPNAME,d.NAMEPATH as ACCDEPNAMEPATH,\n                            f.ACCCLASSNAME as ACCCLASSNAME,k.DICTNAME as ACCTYPESTR,i.DICTNAME as SEXSTR,\n                            b.DICTNAME as COUNTRYSTR, h.DICTNAME as IDTYPESTR,n.DICTNAME as NATIONSTR,\n                            m.DICTNAME as POLITICSSTR,g.DICTNAME as ACCSTATUSSTR,sa.ACCDEPNAME as PARENTACCDEPNAME,\n                            sa.SHORTNAME as PARENTSHORTNAME, e.ACCCLASSID as ACCCLASSNUM, e.CAMPUSID,\n                            sduty.DICTNAME as DUTYSTR, s.DICTNAME as ACCCLASSTYPESTR, sep.EPNAME as EPNAME,\n                            epg.GROUPNAME as EPGROUPNAME, e.MODIDATE, d.SHORTNAME, staff.WORKCODE, staff.SHORTOFFICETILTE,\n                            ous.OKEY1 as USERID, aap.ACCNUM as imageFileUUid\n                      from (select *\n                              from AM_ACCOUNT a ORDER BY a.ACCNUM DESC) a\n                              left join AM_ACCOUNT_ACCCLASS e on e.ACCNUM=a.ACCNUM and e.RECFLAG=:1 and e.EPID = :2\n                              left join AM_STAFF staff on staff.ACCNUM = a.ACCNUM\n                              left join SC_ENTERPRICE sep on a.epid = sep.epid\n                              left join SC_ENTERPRICEGROUP epg on epg.groupnum = sep.groupnum\n                              left join SC_DICT_EP sduty on sduty.TYPENUM = :3  and sduty.DICTNUM = staff.DUTY and sduty.EPID = e.EPID\n                              left join SC_ACCCLASS f on f.ACCCLASSID=e.ACCCLASSID and f.EPID=e.EPID\n                              left join SC_DICT_EP s on s.TYPENUM=:4  and s.DICTNUM=f.CLASSTYPE and s.EPID = e.EPID\n                              left join SC_CAMPUS c on c.CAMPUSID=e.CAMPUSID and c.EPID = e.EPID\n                              left join SC_ACCDEP d on d.ACCDEPID=a.ACCDEPID and d.EPID = a.EPID\n                              left join SC_DICT_EP k on k.TYPENUM=:5  and k.DICTNUM=a.ACCTYPE and k.EPID = e.EPID\n                              left join SC_DICT_EP i on i.TYPENUM=:6  and i.DICTNUM=a.SEX  and i.EPID = e.EPID\n                              left join SC_DICT_EP b on b.TYPENUM=:7  and  b.DICTNUM=a.COUNTRY and b.EPID = e.EPID\n                              left join SC_DICT_EP h on h.TYPENUM=:8  and h.DICTNUM=a.IDTYPE and h.EPID = e.EPID\n                              left join SC_DICT_EP n on n.TYPENUM=:9  and n.DICTNUM=a.NATION and n.EPID = e.EPID\n                              left join SC_DICT_EP m on m.TYPENUM=:10  and  m.DICTNUM=a.POLITICS and m.EPID = e.EPID\n                              left join SC_DICT_EP g on g.TYPENUM=:11  and g.DICTNUM=a.ACCSTATUS and g.EPID = e.EPID\n                              left join SC_ACCDEP sa on sa.ACCDEPID=d.PARENTID and sa.EPID = e.EPID\n                              left join SC_ENTERPRICE ep on ep.EPID=a.EPID\n                              left join OM_USERCENTER_SYNC ous on ous.EPID = a.EPID and ous.SYNNCTYPE = 5 and ous.KEY1 = a.ACCNUM\n                              left join AM_ACCOUNT_PHOTO aap on a.ACCNUM = aap.ACCNUM and aap.PHOTOTYPE = 1\n                        where a.epid = :12\n                          and a.ISINTERNAL = :13\n                          and a.ACCSTATUS in ( :14 )\n                          and not exists(select 1\n                                           from AM_ACCOUNTCARD j\n                                          where j.CARDSTATUS &lt;&gt; :15  and a.ACCNUM = j.ACCNUM)\n               ORDER BY a.ACCNUM DESC) sq\nBind Variables :\n 1 -  (NUMBER):1\n 2 -  (NUMBER):1\n 3 -  (NUMBER):144\n 4 -  (NUMBER):173\n 5 -  (NUMBER):30\n 6 -  (NUMBER):137\n 7 -  (NUMBER):142\n 8 -  (NUMBER):28\n 9 -  (NUMBER):143\n 10 -  (NUMBER):141\n 11 -  (NUMBER):12\n 12 -  (NUMBER):1\n 13 -  (NUMBER):0\n 14 -  (NUMBER):-1\n 15 -  (NUMBER):0\n\n-------------------------------------------------------------------------------\nFINDINGS SECTION (11 findings)\n-------------------------------------------------------------------------------\n\n1- Statistics Finding\n---------------------\n  Optimizer statistics for table &quot;EASYTONG&quot;.&quot;AM_ACCOUNT_ACCCLASS&quot; are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this table and its indices.\n    execute dbms_stats.gather_table_stats(ownname =&gt; &#039;EASYTONG&#039;, tabname =&gt; &#039;AM_ACCOUNT_ACCCLASS&#039;, estimate_percent =&gt; DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =&gt; &#039;FOR ALL COLUMNS SIZE AUTO&#039;, cascade =&gt; TRUE);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the table and its indices\n    in order to select a good execution plan.\n\n2- Statistics Finding\n---------------------\n  Optimizer statistics for table &quot;EASYTONG&quot;.&quot;AM_ACCOUNT&quot; are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this table and its indices.\n    execute dbms_stats.gather_table_stats(ownname =&gt; &#039;EASYTONG&#039;, tabname =&gt; &#039;AM_ACCOUNT&#039;, estimate_percent =&gt; DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =&gt; &#039;FOR ALL COLUMNS SIZE AUTO&#039;, cascade =&gt; TRUE);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the table and its indices in order to select a good execution plan.\n\n3- Statistics Finding\n---------------------\n  Optimizer statistics for table &quot;EASYTONG&quot;.&quot;SC_ACCCLASS&quot; are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this table and its indices.\n    execute dbms_stats.gather_table_stats(ownname =&gt; &#039;EASYTONG&#039;, tabname =&gt; &#039;SC_ACCCLASS&#039;, estimate_percent =&gt; DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =&gt; &#039;FOR ALL COLUMNS SIZE AUTO&#039;, cascade =&gt; TRUE);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the table and its indices in order to select a good execution plan.\n\n4- Statistics Finding\n---------------------\n  Optimizer statistics for table &quot;EASYTONG&quot;.&quot;SC_ACCDEP&quot; are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this table and its indices.\n    execute dbms_stats.gather_table_stats(ownname =&gt; &#039;EASYTONG&#039;, tabname =&gt; &#039;SC_ACCDEP&#039;, estimate_percent =&gt; DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =&gt; &#039;FOR ALL COLUMNS SIZE AUTO&#039;, cascade =&gt; TRUE);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the table and its indices\n    in order to select a good execution plan.\n\n5- Statistics Finding\n---------------------\n  Optimizer statistics for index &quot;EASYTONG&quot;.&quot;PK_AM_ACCOUNT_PHOTO&quot; are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this index.\n    execute dbms_stats.gather_index_stats(ownname =&gt; &#039;EASYTONG&#039;, indname =&gt; &#039;PK_AM_ACCOUNT_PHOTO&#039;, estimate_percent =&gt; DBMS_STATS.AUTO_SAMPLE_SIZE);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the index in order to select a good execution plan.\n\n6- Statistics Finding\n---------------------\n  Optimizer statistics for table &quot;EASYTONG&quot;.&quot;AM_ACCOUNT_PHOTO&quot; and its indices are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this table.\n    execute dbms_stats.gather_table_stats(ownname =&gt; &#039;EASYTONG&#039;, tabname =&gt; &#039;AM_ACCOUNT_PHOTO&#039;, estimate_percent =&gt; DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =&gt; &#039;FOR ALL COLUMNS SIZE AUTO&#039;);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the table in order to select a good execution plan.\n\n7- Statistics Finding\n---------------------\n  Optimizer statistics for index &quot;EASYTONG&quot;.&quot;IDX_ACCOUNTCARD1&quot; are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this index.\n    execute dbms_stats.gather_index_stats(ownname =&gt; &#039;EASYTONG&#039;, indname =&gt; &#039;IDX_ACCOUNTCARD1&#039;, estimate_percent =&gt;  DBMS_STATS.AUTO_SAMPLE_SIZE);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the index in order to select a good execution plan.\n\n8- Statistics Finding\n---------------------\n  Optimizer statistics for index &quot;EASYTONG&quot;.&quot;FK_ACCOUNTCARD_ACCOUNT&quot; are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this index.\n    execute dbms_stats.gather_index_stats(ownname =&gt; &#039;EASYTONG&#039;, indname =&gt; &#039;FK_ACCOUNTCARD_ACCOUNT&#039;, estimate_percent =&gt; DBMS_STATS.AUTO_SAMPLE_SIZE);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the index in order to select a good execution plan.\n\n9- Statistics Finding\n---------------------\n  Optimizer statistics for index &quot;EASYTONG&quot;.&quot;PK_AM_ACCOUNTCARD&quot; are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this index.\n    execute dbms_stats.gather_index_stats(ownname =&gt; &#039;EASYTONG&#039;, indname =&gt; &#039;PK_AM_ACCOUNTCARD&#039;, estimate_percent =&gt;  DBMS_STATS.AUTO_SAMPLE_SIZE);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the index in order to select a good execution plan.\n\n10- Statistics Finding\n----------------------\n  Optimizer statistics for table &quot;EASYTONG&quot;.&quot;AM_ACCOUNTCARD&quot; and its indices are stale.\n\n  Recommendation\n  --------------\n  - Consider collecting optimizer statistics for this table.\n    execute dbms_stats.gather_table_stats(ownname =&gt; &#039;EASYTONG&#039;, tabname =&gt; &#039;AM_ACCOUNTCARD&#039;, estimate_percent =&gt; DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =&gt; &#039;FOR ALL COLUMNS SIZE AUTO&#039;);\n\n  Rationale\n  ---------\n    The optimizer requires up-to-date statistics for the table in order to  select a good execution plan.\n\n11- SQL Profile Finding (see explain plans section below)\n---------------------------------------------------------\n  A potentially better execution plan was found for this statement.\n\n  Recommendation (estimated benefit: 99.87%)\n  ------------------------------------------\n  - Consider accepting the recommended SQL profile.\n    execute dbms_sqltune.accept_sql_profile(task_name =&gt; &#039;TASK_504&#039;, task_owner =&gt; &#039;SYS&#039;, replace =&gt; TRUE);\n\n  Validation results\n  ------------------\n  The SQL profile was tested by executing both its plan and the original plan\n  and measuring their respective execution statistics. A plan may have been\n  only partially executed if the other could be run to completion in less time.\n\n                           Original Plan  With SQL Profile  % Improved\n                           -------------  ----------------  ----------\n  Completion Status:             PARTIAL          COMPLETE\n  Elapsed Time (s):           16.002239           .140409      99.12 %\n  CPU Time (s):                15.87113           .139279      99.12 %\n  User I\/O Time (s):                  0                 0\n  Buffer Gets:                   749517               913      99.87 %\n  Physical Read Requests:             0                 0\n  Physical Write Requests:            0                 0\n  Physical Read Bytes:                0                 0\n  Physical Write Bytes:               0                 0\n  Rows Processed:                     0                 1\n  Fetches:                            0                 1\n  Executions:                         0                 1\n\n  Notes\n  -----\n  1. Statistics for the original plan were averaged over 0 executions.\n  2. Statistics for the SQL profile plan were averaged over 8 executions.\n\n-------------------------------------------------------------------------------\nADDITIONAL INFORMATION SECTION\n-------------------------------------------------------------------------------\n- The optimizer could not merge the view at line ID 6 of the execution plan.\n\n-------------------------------------------------------------------------------\nEXPLAIN PLANS SECTION\n-------------------------------------------------------------------------------\n\n1- Original With Adjusted Cost\n------------------------------\nPlan hash value: 1429787274\n\n----------------------------------------------------------------------------------------------------------------------------------------------------\n| Id  | Operation                                                 | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |\n----------------------------------------------------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                                          |                        |     1 |    63 |   625M  (1)|999:59:59 |       |       |\n|   1 |  SORT AGGREGATE                                           |                        |     1 |    63 |            |          |       |       |\n|   2 |   NESTED LOOPS ANTI                                       |                        | 24952 |  1535K|   625M  (1)|999:59:59 |       |       |\n|   3 |    NESTED LOOPS OUTER                                     |                        | 24954 |  1437K|   625M  (1)|999:59:59 |       |       |\n|   4 |     NESTED LOOPS OUTER                                    |                        | 24954 |  1267K|   625M  (1)|999:59:59 |       |       |\n|   5 |      NESTED LOOPS OUTER                                   |                        | 24954 |  1120K|   625M  (1)|999:59:59 |       |       |\n|   6 |       VIEW                                                |                        | 24954 |   999K|   625M  (1)|999:59:59 |       |       |\n|   7 |        NESTED LOOPS OUTER                                 |                        | 24954 |  5799K|   625M  (1)|999:59:59 |       |       |\n|   8 |         NESTED LOOPS OUTER                                |                        | 24954 |  5531K|   625M  (1)|999:59:59 |       |       |\n|   9 |          NESTED LOOPS OUTER                               |                        | 24954 |  5263K|   625M  (1)|999:59:59 |       |       |\n|  10 |           NESTED LOOPS OUTER                              |                        | 24954 |  4995K|   625M  (1)|999:59:59 |       |       |\n|  11 |            NESTED LOOPS OUTER                             |                        | 24954 |  4727K|   625M  (1)|999:59:59 |       |       |\n|  12 |             NESTED LOOPS OUTER                            |                        | 24954 |  4459K|   625M  (1)|999:59:59 |       |       |\n|  13 |              NESTED LOOPS OUTER                           |                        | 24954 |  4191K|   625M  (1)|999:59:59 |       |       |\n|  14 |               NESTED LOOPS OUTER                          |                        | 24954 |  3923K|   625M  (1)|999:59:59 |       |       |\n|  15 |                NESTED LOOPS OUTER                         |                        | 24954 |  3728K|   625M  (1)|999:59:59 |       |       |\n|  16 |                 NESTED LOOPS OUTER                        |                        | 24954 |  3582K|   625M  (1)|999:59:59 |       |       |\n|  17 |                  VIEW                                     |                        | 24954 |  3314K|   625M  (1)|999:59:59 |       |       |\n|  18 |                   NESTED LOOPS OUTER                      |                        | 24954 |  4045K|   625M  (1)|999:59:59 |       |       |\n|  19 |                    NESTED LOOPS OUTER                     |                        | 24954 |  3899K|   625M  (1)|999:59:59 |       |       |\n|  20 |                     VIEW                                  |                        | 24954 |  3631K|   625M  (1)|999:59:59 |       |       |\n|  21 |                      NESTED LOOPS OUTER                   |                        | 24954 |  2315K|   625M  (1)|999:59:59 |       |       |\n|  22 |                       MERGE JOIN OUTER                    |                        | 24954 |  1973K| 12532   (1)| 00:02:31 |       |       |\n|  23 |                        NESTED LOOPS OUTER                 |                        | 24954 |  1827K| 12531   (1)| 00:02:31 |       |       |\n|* 24 |                         TABLE ACCESS BY GLOBAL INDEX ROWID| AM_ACCOUNT             | 24954 |  1194K| 12531   (1)| 00:02:31 | ROWID | ROWID |\n|* 25 |                          INDEX RANGE SCAN                 | IDX_AM_ACCOUNT2        | 12478 |       |    50   (0)| 00:00:01 |       |       |\n|  26 |                         TABLE ACCESS BY INDEX ROWID       | AM_STAFF               |     1 |    26 |     0   (0)| 00:00:01 |       |       |\n|* 27 |                          INDEX UNIQUE SCAN                | PK_AM_STAFF            |     1 |       |     0   (0)| 00:00:01 |       |       |\n|  28 |                        BUFFER SORT                        |                        |     1 |     6 | 12532   (1)| 00:02:31 |       |       |\n|* 29 |                         INDEX UNIQUE SCAN                 | PK_SC_ENTERPRICE       |     1 |     6 |     0   (0)| 00:00:01 |       |       |\n|* 30 |                       TABLE ACCESS BY INDEX ROWID         | AM_ACCOUNT_ACCCLASS    |     1 |    14 | 25046   (1)| 00:05:01 |       |       |\n|* 31 |                        INDEX RANGE SCAN                   | IDX_ACCOUNT_ACCCLASS1  | 24956 |       |    85   (0)| 00:00:02 |       |       |\n|* 32 |                     INDEX UNIQUE SCAN                     | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|  33 |                    TABLE ACCESS BY INDEX ROWID            | SC_ACCCLASS            |     1 |     6 |     1   (0)| 00:00:01 |       |       |\n|* 34 |                     INDEX UNIQUE SCAN                     | PK_SC_ACCCLASS         |     1 |       |     0   (0)| 00:00:01 |       |       |\n|* 35 |                  INDEX UNIQUE SCAN                        | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 36 |                 INDEX UNIQUE SCAN                         | PK_SC_CAMPUS           |     1 |     6 |     0   (0)| 00:00:01 |       |       |\n|  37 |                TABLE ACCESS BY INDEX ROWID                | SC_ACCDEP              |     1 |     8 |     4   (0)| 00:00:01 |       |       |\n|* 38 |                 INDEX UNIQUE SCAN                         | PK_SC_ACCDEP           |     1 |       |     3   (0)| 00:00:01 |       |       |\n|* 39 |               INDEX UNIQUE SCAN                           | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 40 |              INDEX UNIQUE SCAN                            | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 41 |             INDEX UNIQUE SCAN                             | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 42 |            INDEX UNIQUE SCAN                              | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 43 |           INDEX UNIQUE SCAN                               | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 44 |          INDEX UNIQUE SCAN                                | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 45 |         INDEX UNIQUE SCAN                                 | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 46 |       INDEX UNIQUE SCAN                                   | PK_SC_ACCDEP           |     1 |     5 |     3   (0)| 00:00:01 |       |       |\n|* 47 |      INDEX RANGE SCAN                                     | PK_AM_ACCOUNT_PHOTO    |     1 |     6 |     1   (0)| 00:00:01 |       |       |\n|* 48 |     INDEX SKIP SCAN                                       | PK_OM_USERCENTER_SYNC  |     1 |     7 |     1   (0)| 00:00:01 |       |       |\n|* 49 |    TABLE ACCESS BY GLOBAL INDEX ROWID                     | AM_ACCOUNTCARD         |     1 |     4 |     1   (0)| 00:00:01 | ROWID | ROWID |\n|* 50 |     INDEX RANGE SCAN                                      | FK_ACCOUNTCARD_ACCOUNT |     1 |       |     0   (0)| 00:00:01 |       |       |\n----------------------------------------------------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n  24 - filter(&quot;A&quot;.&quot;ACCSTATUS&quot;=:14 AND &quot;A&quot;.&quot;EPID&quot;=:12)\n  25 - access(SYS_OP_DESCEND(&quot;ISINTERNAL&quot;)=SYS_OP_DESCEND(:13))\n       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND(&quot;ISINTERNAL&quot;))=:13)\n  27 - access(&quot;STAFF&quot;.&quot;ACCNUM&quot;(+)=&quot;A&quot;.&quot;ACCNUM&quot;)\n  29 - access(&quot;SEP&quot;.&quot;EPID&quot;(+)=:12)\n  30 - filter(&quot;E&quot;.&quot;RECFLAG&quot;(+)=:1 AND &quot;E&quot;.&quot;ACCNUM&quot;(+)=&quot;A&quot;.&quot;ACCNUM&quot;)\n  31 - access(&quot;E&quot;.&quot;EPID&quot;(+)=:2)\n  32 - access(&quot;SDUTY&quot;.&quot;TYPENUM&quot;(+)=:3 AND &quot;SDUTY&quot;.&quot;EPID&quot;(+)=&quot;E&quot;.&quot;EPID&quot; AND &quot;SDUTY&quot;.&quot;DICTNUM&quot;(+)=&quot;STAFF&quot;.&quot;DUTY&quot;)\n  34 - access(&quot;F&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_011&quot;.&quot;QCSJ_C000000000500003&quot; AND &quot;F&quot;.&quot;ACCCLASSID&quot;(+)=&quot;from$_subquery$_011&quot;.&quot;ACCCLASSID&quot;)\n  35 - access(&quot;G&quot;.&quot;TYPENUM&quot;(+)=:11 AND &quot;G&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND &quot;G&quot;.&quot;DICTNUM&quot;(+)=:14)\n  36 - access(&quot;C&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND &quot;C&quot;.&quot;CAMPUSID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;CAMPUSID&quot;)\n  38 - access(&quot;D&quot;.&quot;EPID&quot;(+)=:12 AND &quot;D&quot;.&quot;ACCDEPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;ACCDEPID&quot;)\n  39 - access(&quot;H&quot;.&quot;TYPENUM&quot;(+)=:8 AND &quot;H&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;H&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;IDTYPE&quot;)\n  40 - access(&quot;M&quot;.&quot;TYPENUM&quot;(+)=:10 AND &quot;M&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;M&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;POLITICS&quot;)\n  41 - access(&quot;N&quot;.&quot;TYPENUM&quot;(+)=:9 AND &quot;N&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;N&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;NATION&quot;)\n  42 - access(&quot;B&quot;.&quot;TYPENUM&quot;(+)=:7 AND &quot;B&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;B&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;COUNTRY&quot;)\n  43 - access(&quot;K&quot;.&quot;TYPENUM&quot;(+)=:5 AND &quot;K&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;K&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;ACCTYPE&quot;)\n  44 - access(&quot;I&quot;.&quot;TYPENUM&quot;(+)=:6 AND &quot;I&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;I&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;SEX&quot;)\n  45 - access(&quot;S&quot;.&quot;TYPENUM&quot;(+)=:4 AND &quot;S&quot;.&quot;EPID&quot;(+)=&quot;E&quot;.&quot;EPID&quot; AND &quot;S&quot;.&quot;DICTNUM&quot;(+)=&quot;F&quot;.&quot;CLASSTYPE&quot;)\n  46 - access(&quot;SA&quot;.&quot;EPID&quot;(+)=&quot;E&quot;.&quot;EPID&quot; AND &quot;SA&quot;.&quot;ACCDEPID&quot;(+)=&quot;D&quot;.&quot;PARENTID&quot;)\n  47 - access(&quot;from$_subquery$_035&quot;.&quot;QCSJ_C000000000500000&quot;=&quot;AAP&quot;.&quot;ACCNUM&quot;(+) AND &quot;AAP&quot;.&quot;PHOTOTYPE&quot;(+)=1)\n  48 - access(&quot;OUS&quot;.&quot;EPID&quot;(+)=:12)\n       filter(TO_NUMBER(&quot;OUS&quot;.&quot;SYNNCTYPE&quot;(+))=5 AND &quot;OUS&quot;.&quot;EPID&quot;(+)=:12 AND\n              &quot;from$_subquery$_035&quot;.&quot;QCSJ_C000000000500000&quot;=TO_NUMBER(&quot;OUS&quot;.&quot;KEY1&quot;(+)))\n  49 - filter(&quot;J&quot;.&quot;CARDSTATUS&quot;&lt;&gt;:15)\n  50 - access(&quot;from$_subquery$_035&quot;.&quot;QCSJ_C000000000500000&quot;=&quot;J&quot;.&quot;ACCNUM&quot;)\n\n2- Using SQL Profile\n--------------------\nPlan hash value: 1006624635\n\n---------------------------------------------------------------------------------------------------------------------------------------------\n| Id  | Operation                                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |\n---------------------------------------------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                                   |                        |     1 |    63 |   195   (6)| 00:00:03 |       |       |\n|   1 |  SORT AGGREGATE                                    |                        |     1 |    63 |            |          |       |       |\n|*  2 |   HASH JOIN RIGHT OUTER                            |                        | 24952 |  1535K|   195   (6)| 00:00:03 |       |       |\n|   3 |    INDEX FAST FULL SCAN                            | PK_SC_ACCDEP           |  3447 | 17235 |     7   (0)| 00:00:01 |       |       |\n|*  4 |    HASH JOIN RIGHT OUTER                           |                        | 24952 |  1413K|   188   (6)| 00:00:03 |       |       |\n|*  5 |     INDEX FAST FULL SCAN                           | PK_AM_ACCOUNT_PHOTO    |  1910 | 11460 |     5   (0)| 00:00:01 |       |       |\n|*  6 |     HASH JOIN RIGHT OUTER                          |                        | 24952 |  1267K|   183   (6)| 00:00:03 |       |       |\n|*  7 |      INDEX SKIP SCAN                               | PK_OM_USERCENTER_SYNC  |     1 |     7 |     1   (0)| 00:00:01 |       |       |\n|   8 |      NESTED LOOPS ANTI                             |                        | 24952 |  1096K|   182   (6)| 00:00:03 |       |       |\n|   9 |       VIEW                                         |                        | 24954 |   999K|   179   (6)| 00:00:03 |       |       |\n|* 10 |        HASH JOIN RIGHT OUTER                       |                        | 24954 |  5799K|   179   (6)| 00:00:03 |       |       |\n|* 11 |         TABLE ACCESS FULL                          | SC_ACCDEP              |  3447 | 27576 |    22   (0)| 00:00:01 |       |       |\n|  12 |         NESTED LOOPS OUTER                         |                        | 24954 |  5604K|   157   (6)| 00:00:02 |       |       |\n|  13 |          NESTED LOOPS OUTER                        |                        | 24954 |  5336K|   157   (6)| 00:00:02 |       |       |\n|  14 |           NESTED LOOPS OUTER                       |                        | 24954 |  5068K|   157   (6)| 00:00:02 |       |       |\n|  15 |            NESTED LOOPS OUTER                      |                        | 24954 |  4800K|   157   (6)| 00:00:02 |       |       |\n|  16 |             NESTED LOOPS OUTER                     |                        | 24954 |  4532K|   157   (6)| 00:00:02 |       |       |\n|  17 |              NESTED LOOPS OUTER                    |                        | 24954 |  4264K|   156   (6)| 00:00:02 |       |       |\n|  18 |               NESTED LOOPS OUTER                   |                        | 24954 |  3996K|   154   (4)| 00:00:02 |       |       |\n|  19 |                NESTED LOOPS OUTER                  |                        | 24954 |  3728K|   153   (4)| 00:00:02 |       |       |\n|  20 |                 NESTED LOOPS OUTER                 |                        | 24954 |  3460K|   152   (3)| 00:00:02 |       |       |\n|  21 |                  VIEW                              |                        | 24954 |  3314K|   151   (2)| 00:00:02 |       |       |\n|* 22 |                   HASH JOIN RIGHT OUTER            |                        | 24954 |  4045K|   151   (2)| 00:00:02 |       |       |\n|  23 |                    TABLE ACCESS FULL               | SC_ACCCLASS            |    17 |   102 |     3   (0)| 00:00:01 |       |       |\n|  24 |                    NESTED LOOPS OUTER              |                        | 24954 |  3899K|   148   (3)| 00:00:02 |       |       |\n|  25 |                     VIEW                           |                        | 24954 |  3631K|   147   (2)| 00:00:02 |       |       |\n|* 26 |                      HASH JOIN RIGHT OUTER         |                        | 24954 |  2315K|   147   (2)| 00:00:02 |       |       |\n|* 27 |                       TABLE ACCESS FULL            | AM_ACCOUNT_ACCCLASS    | 24956 |   341K|    68   (0)| 00:00:01 |       |       |\n|* 28 |                       HASH JOIN RIGHT OUTER        |                        | 24954 |  1973K|    78   (2)| 00:00:01 |       |       |\n|* 29 |                        INDEX UNIQUE SCAN           | PK_SC_ENTERPRICE       |     1 |     6 |     0   (0)| 00:00:01 |       |       |\n|  30 |                        NESTED LOOPS OUTER          |                        | 24954 |  1827K|    78   (2)| 00:00:01 |       |       |\n|  31 |                         PARTITION LIST SINGLE      |                        | 24954 |  1194K|    77   (0)| 00:00:01 |   KEY |   KEY |\n|* 32 |                          TABLE ACCESS FULL         | AM_ACCOUNT             | 24954 |  1194K|    77   (0)| 00:00:01 |   KEY |   KEY |\n|  33 |                         TABLE ACCESS BY INDEX ROWID| AM_STAFF               |     1 |    26 |     0   (0)| 00:00:01 |       |       |\n|* 34 |                          INDEX UNIQUE SCAN         | PK_AM_STAFF            |     1 |       |     0   (0)| 00:00:01 |       |       |\n|* 35 |                     INDEX UNIQUE SCAN              | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 36 |                  INDEX UNIQUE SCAN                 | PK_SC_CAMPUS           |     1 |     6 |     0   (0)| 00:00:01 |       |       |\n|* 37 |                 INDEX UNIQUE SCAN                  | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 38 |                INDEX UNIQUE SCAN                   | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 39 |               INDEX UNIQUE SCAN                    | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 40 |              INDEX UNIQUE SCAN                     | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 41 |             INDEX UNIQUE SCAN                      | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 42 |            INDEX UNIQUE SCAN                       | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 43 |           INDEX UNIQUE SCAN                        | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 44 |          INDEX UNIQUE SCAN                         | PK_SC_DICT_EP          |     1 |    11 |     0   (0)| 00:00:01 |       |       |\n|* 45 |       TABLE ACCESS BY GLOBAL INDEX ROWID           | AM_ACCOUNTCARD         |     1 |     4 |     1   (0)| 00:00:01 | ROWID | ROWID |\n|* 46 |        INDEX RANGE SCAN                            | FK_ACCOUNTCARD_ACCOUNT |     1 |       |     0   (0)| 00:00:01 |       |       |\n---------------------------------------------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - access(&quot;SA&quot;.&quot;EPID&quot;(+)=&quot;E&quot;.&quot;EPID&quot; AND &quot;SA&quot;.&quot;ACCDEPID&quot;(+)=&quot;D&quot;.&quot;PARENTID&quot;)\n   4 - access(&quot;from$_subquery$_035&quot;.&quot;QCSJ_C000000000500000&quot;=&quot;AAP&quot;.&quot;ACCNUM&quot;(+))\n   5 - filter(&quot;AAP&quot;.&quot;PHOTOTYPE&quot;(+)=1)\n   6 - access(&quot;from$_subquery$_035&quot;.&quot;QCSJ_C000000000500000&quot;=TO_NUMBER(&quot;OUS&quot;.&quot;KEY1&quot;(+)) AND\n              &quot;OUS&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_035&quot;.&quot;QCSJ_C000000000500002&quot;)\n   7 - access(&quot;OUS&quot;.&quot;EPID&quot;(+)=:12)\n       filter(TO_NUMBER(&quot;OUS&quot;.&quot;SYNNCTYPE&quot;(+))=5 AND &quot;OUS&quot;.&quot;EPID&quot;(+)=:12)\n  10 - access(&quot;D&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500002&quot; AND &quot;D&quot;.&quot;ACCDEPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;ACCDEPID&quot;)\n  11 - filter(&quot;D&quot;.&quot;EPID&quot;(+)=:12)\n  22 - access(&quot;F&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_011&quot;.&quot;QCSJ_C000000000500003&quot; AND &quot;F&quot;.&quot;ACCCLASSID&quot;(+)=&quot;from$_subquery$_011&quot;.&quot;ACCCLASSID&quot;)\n  26 - access(&quot;E&quot;.&quot;ACCNUM&quot;(+)=&quot;A&quot;.&quot;ACCNUM&quot;)\n  27 - filter(&quot;E&quot;.&quot;EPID&quot;(+)=:2 AND &quot;E&quot;.&quot;RECFLAG&quot;(+)=:1)\n  28 - access(&quot;A&quot;.&quot;EPID&quot;=&quot;SEP&quot;.&quot;EPID&quot;(+))\n  29 - access(&quot;SEP&quot;.&quot;EPID&quot;(+)=:12)\n  32 - filter(&quot;A&quot;.&quot;ISINTERNAL&quot;=:13 AND &quot;A&quot;.&quot;ACCSTATUS&quot;=:14 AND &quot;A&quot;.&quot;EPID&quot;=:12)\n  34 - access(&quot;STAFF&quot;.&quot;ACCNUM&quot;(+)=&quot;A&quot;.&quot;ACCNUM&quot;)\n  35 - access(&quot;SDUTY&quot;.&quot;TYPENUM&quot;(+)=:3 AND &quot;SDUTY&quot;.&quot;EPID&quot;(+)=&quot;E&quot;.&quot;EPID&quot; AND &quot;SDUTY&quot;.&quot;DICTNUM&quot;(+)=&quot;STAFF&quot;.&quot;DUTY&quot;)\n  36 - access(&quot;C&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND &quot;C&quot;.&quot;CAMPUSID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;CAMPUSID&quot;)\n  37 - access(&quot;G&quot;.&quot;TYPENUM&quot;(+)=:11 AND &quot;G&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND &quot;G&quot;.&quot;DICTNUM&quot;(+)=:14)\n  38 - access(&quot;K&quot;.&quot;TYPENUM&quot;(+)=:5 AND &quot;K&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;K&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;ACCTYPE&quot;)\n  39 - access(&quot;I&quot;.&quot;TYPENUM&quot;(+)=:6 AND &quot;I&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;I&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;SEX&quot;)\n  40 - access(&quot;S&quot;.&quot;TYPENUM&quot;(+)=:4 AND &quot;S&quot;.&quot;EPID&quot;(+)=&quot;E&quot;.&quot;EPID&quot; AND &quot;S&quot;.&quot;DICTNUM&quot;(+)=&quot;F&quot;.&quot;CLASSTYPE&quot;)\n  41 - access(&quot;H&quot;.&quot;TYPENUM&quot;(+)=:8 AND &quot;H&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;H&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;IDTYPE&quot;)\n  42 - access(&quot;M&quot;.&quot;TYPENUM&quot;(+)=:10 AND &quot;M&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;M&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;POLITICS&quot;)\n  43 - access(&quot;N&quot;.&quot;TYPENUM&quot;(+)=:9 AND &quot;N&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;N&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;NATION&quot;)\n  44 - access(&quot;B&quot;.&quot;TYPENUM&quot;(+)=:7 AND &quot;B&quot;.&quot;EPID&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;QCSJ_C000000000500003&quot; AND\n              &quot;B&quot;.&quot;DICTNUM&quot;(+)=&quot;from$_subquery$_015&quot;.&quot;COUNTRY&quot;)\n  45 - filter(&quot;J&quot;.&quot;CARDSTATUS&quot;&lt;&gt;:15)\n  46 - access(&quot;from$_subquery$_035&quot;.&quot;QCSJ_C000000000500000&quot;=&quot;J&quot;.&quot;ACCNUM&quot;)\n\n-------------------------------------------------------------------------------<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"2_%E5%8F%91%E7%8E%B0%E5%A4%A7%E9%87%8F%EF%BC%9AStatistics_Finding\"><\/span>2. \u53d1\u73b0\u5927\u91cf\uff1aStatistics Finding<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style='color:red'>\u5e94\u8be5\u6536\u96c6\u8be5\u7528\u6237\u7684\u7edf\u8ba1\u4fe1\u606f\uff1a<\/span><\/p>\n<pre><code class=\"language-sql\">BEGIN\n  DBMS_STATS.GATHER_SCHEMA_STATS(ownname =&gt; &#039;EASYTONG&#039;,  -- oracle\u7528\u6237\u540d\uff0c\u9700\u8981\u5927\u5199\n                                cascade =&gt; true,\n                                granularity=&gt;&#039;ALL&#039;     -- \u6536\u96c6\u5206\u533a\u8868\n                                );\nEND;\n\/<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"3_%E5%8F%91%E7%8E%B0%EF%BC%9ASQL_Profile_Finding\"><\/span>3. \u53d1\u73b0\uff1aSQL Profile Finding<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style='color:red'>\u63a5\u53d7SQLProfile<\/span><\/p>\n<pre><code class=\"language-sql\"> execute dbms_sqltune.accept_sql_profile(task_name =&gt; &#039;TASK_504&#039;, task_owner =&gt; &#039;SYS&#039;, replace =&gt; TRUE);<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"4_%E9%99%84%E5%BD%95%EF%BC%9ADBMS_STATS%E9%87%8D%E8%A6%81%E5%8F%82%E6%95%B0%E8%AF%A6%E8%A7%A3\"><\/span>4. \u9644\u5f55\uff1aDBMS_STATS\u91cd\u8981\u53c2\u6570\u8be6\u89e3<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<ul>\n<li>\n<p>ownname<br \/>\n\u8868\u793a\u8868\u7684\u62e5\u6709\u8005\uff0c\u4e0d\u533a\u5206\u5927\u5c0f\u5199\u3002<\/p>\n<\/li>\n<li>\n<p>tabname<br \/>\n\u8868\u793a\u8868\u540d\u5b57\uff0c\u4e0d\u533a\u5206\u5927\u5c0f\u5199\u3002<\/p>\n<\/li>\n<li>\n<p>granularity<br \/>\n\u8868\u793a\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u7684\u7c92\u5ea6\uff0c\u8be5\u9009\u9879\u53ea\u5bf9\u5206\u533a\u8868\u751f\u6548\uff0c\u9ed8\u8ba4\u4e3a AUTO\uff0c\u8868\u793a\u8ba9Oracle\u6839\u636e\u8868\u7684\u5206\u533a\u7c7b\u578b\u81ea\u5df1\u5224\u65ad\u5982\u4f55\u6536\u96c6\u5206\u533a\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u3002\u5bf9\u4e8e\u8be5\u9009\u9879\uff0c\u6211\u4eec\u4e00\u822c\u91c7\u7528AUTO \u65b9\u5f0f\uff0c\u4e5f\u5c31\u662f\u6570\u636e\u5e93\u9ed8\u8ba4\u65b9\u5f0f\uff0c\u56e0\u6b64\u5728\u540e\u9762\u7684\u811a\u672c\u4e2d\uff0c\u7701\u7565\u8be5\u9009\u9879\u3002|<\/p>\n<\/li>\n<li>\n<p>estimate_percent<br \/>\n\u8868\u793a\u91c7\u6837\u7387\uff0c\u8303\u56f4\u662f0.000 001\uff5e100\u3002\u8fd9\u4e2a\u53c2\u6570\u4e3b\u8981\u662f\u7528\u4e8eCBO\u4f30\u7b97\u8868\u7684\u603b\u884c\u6570\uff0c\u91c7\u6837\u7387\u8d8a\u9ad8\uff0cCBO\u4f30\u7b97\u7684\u8868\u884c\u6570\u8d8a\u63a5\u8fd1\u4e8e\u771f\u5b9e\u503c\uff0c\u6267\u884c\u8ba1\u5212\u8d8a\u80fd\u8d70\u6b63\u786e\u3002<br \/>\n\u4f30\u7b97\u603b\u884c\u6570=\u6837\u672c\u5927\u5c0f(DBA_TAB_STATISTICS.SAMPLE_SIZE\uff09*100\/\u91c7\u6837\u7387(estimate_percent)<br \/>\n\u8fd9\u4e2a\u53c2\u6570\u53ef\u80fd\u5bf9\u4e8e\u5f88\u591a\u65b0\u624b\u6765\u8bf4\u90fd\u4e0d\u77e5\u9053\u600e\u4e48\u8bbe\u7f6e\uff1a<br \/>\n\u4e00\u822c\u5bf9\u5c0f\u4e8e 1GB \u7684\u8868\u8fdb\u884c100%\u91c7\u6837\uff0c\u56e0\u4e3a\u8868\u5f88\u5c0f\uff0c\u5373\u4f7f100%\u91c7\u6837\u901f\u5ea6\u4e5f\u6bd4\u8f83\u5feb\u3002\u6709\u65f6\u5019\u5c0f\u8868\u6709\u53ef\u80fd\u6570\u636e\u5206\u5e03\u4e0d\u5747\u8861\uff0c\u5982\u679c\u6ca1\u6709100%\u91c7\u6837\uff0c\u53ef\u80fd\u4f1a\u5bfc\u81f4\u7edf\u8ba1\u4fe1\u606f\u4e0d\u51c6\u3002<br \/>\n\u56e0\u6b64\u5efa\u8bae\u5bf9\u5c0f\u8868 100%\u91c7\u6837\u3002\u6211\u4eec\u4e00\u822c\u5bf9\u8868\u5927\u5c0f\u57281GB\uff5e5GB \u7684\u8868\u91c7\u683750%\uff0c\u5bf9\u5927\u4e8e5GB\u7684\u8868\u91c7\u683730%\u3002\u5982\u679c\u8868\u7279\u522b\u5927\uff0c\u6709\u51e0\u5341\u751a\u81f3\u4e0a\u767e GB\uff0c\u6211\u4eec\u5efa\u8bae\u5e94\u8be5\u5148\u5bf9\u8868\u8fdb\u884c\u5206\u533a\uff0c\u7136\u540e\u5206\u522b\u5bf9\u6bcf\u4e2a\u5206\u533a\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u3002\u4e00\u822c\u60c5\u51b5\u4e0b\uff0c\u4e3a\u4e86\u786e\u4fdd\u7edf\u8ba1\u4fe1\u606f\u6bd4\u8f83\u51c6\u786e\uff0c\u6211\u4eec\u5efa\u8bae\u91c7\u6837\u7387\u4e0d\u8981\u4f4e\u4e8e30%\u3002<br \/>\n<code>&lt;1GB \u5efa\u8bae\u91c7\u6837\u6bd4100%<\/code>\u3001<code>1GB\uff5e5GB \u5efa\u8bae\u91c7\u6837\u6bd450%<\/code>\u3001<code>&gt;5GB \u5efa\u8bae\u91c7\u6837\u6bd430%<\/code><\/p>\n<\/li>\n<li>\n<p>method_opt<br \/>\n\u7528\u4e8e\u63a7\u5236\u6536\u96c6\u76f4\u65b9\u56fe\u7b56\u7565\u3002<br \/>\n\u76f4\u65b9\u56fe\u7b80\u5355\u6765\u8bf4\u5c31\u662f\u6570\u636e\u5e93\u4e86\u89e3\u8868\u4e2d\u67d0\u5217\u7684\u6570\u636e\u5206\u5e03\uff0c\u4ece\u800c\u66f4\u6b63\u786e\u7684\u8d70\u66f4\u4f18\u7684\u6267\u884c\u8ba1\u5212<\/p>\n<pre><code>method_opt =&gt; \u2018for all columns size 1\u2019 \u8868\u793a\u6240\u6709\u5217\u90fd\u4e0d\u6536\u96c6\u76f4\u65b9\u56fe\nmethod_opt =&gt; \u2018for all columns size skewonly\u2019 \u8868\u793a\u5bf9\u8868\u4e2d\u6240\u6709\u5217\u6536\u96c6\u81ea\u52a8\u5224\u65ad\u662f\u5426\u6536\u96c6\u76f4\u65b9\u56fe\u3002\u9009\u62e9\u7387\u975e\u5e38\u9ad8\u7684\u5217\u548cnull\u7684\u5217\u4e0d\u4f1a\u6536\u96c6\uff08\u8c28\u614e\u4f7f\u7528\uff09\nmethod_opt =&gt; \u2018for all columns size auto\u2019 \u8868\u793a\u5bf9\u51fa\u73b0\u5728 where \u6761\u4ef6\u4e2d\u7684\u5217\u81ea\u52a8\u5224\u65ad\u662f\u5426\u6536\u96c6\u76f4\u65b9\u56fe\u3002\nmethod_opt =&gt; \u2018for all columns size repeat\u2019 \u8868\u793a\u5f53\u524d\u6709\u54ea\u4e9b\u5217\u6536\u96c6\u4e86\u76f4\u65b9\u56fe\uff0c\u73b0\u5728\u5c31\u5bf9\u54ea\u4e9b\u5217\u6536\u96c6\u76f4\u65b9\u56fe\u3002<\/code><\/pre>\n<p>\u5728\u5b9e\u9645\u5de5\u4f5c\u4e2d\uff0c\u5f53\u7cfb\u7edf\u8d8b\u4e8e\u7a33\u5b9a\u4e4b\u540e\uff0c\u4f7f\u7528 REPEAT \u65b9\u5f0f\u6536\u96c6\u76f4\u65b9\u56fe\u3002<\/p>\n<\/li>\n<li>\n<p>no_invalidate<br \/>\n\u8868\u793a\u5171\u4eab\u6c60\u4e2d\u6d89\u53ca\u5230\u8be5\u8868\u7684\u6e38\u6807\u662f\u5426\u7acb\u5373\u5931\u6548\uff0c\u9ed8\u8ba4\u503c\u4e3a DBMS_STATS.AUTO_INVALIDATE\uff0c\u8868\u793a\u8ba9 Oracle \u81ea\u5df1\u51b3\u5b9a\u662f\u5426\u7acb\u5373\u5931\u6548\u3002<br \/>\n\u5efa\u8bae\u5c06 no_invalidate \u53c2\u6570\u8bbe\u7f6e\u4e3a FALSE\uff0c\u7acb\u5373\u5931\u6548\u3002\u56e0\u4e3a\u53d1\u73b0\u6709\u65f6\u5019 SQL \u6267\u884c\u7f13\u6162\u662f\u56e0\u4e3a\u7edf\u8ba1\u4fe1\u606f\u8fc7\u671f\u5bfc\u81f4\uff0c\u91cd\u65b0\u6536\u96c6\u4e86\u7edf\u8ba1\u4fe1\u606f\u4e4b\u540e\u6267\u884c\u8ba1\u5212\u8fd8\u662f\u6ca1\u6709\u66f4\u6539\uff0c\u539f\u56e0\u5c31\u5728\u4e8e\u6ca1\u6709\u5c06\u8fd9\u4e2a\u53c2\u6570\u8bbe\u7f6e\u4e3a false\u3002<\/p>\n<\/li>\n<li>\n<p>degree<br \/>\n\u8868\u793a\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u7684\u5e76\u884c\u5ea6\uff0c\u9ed8\u8ba4\u4e3a NULL\u3002\u5982\u679c\u8868\u6ca1\u6709\u8bbe\u7f6e degree\u3002\u5982\u679c\u8868\u6ca1\u6709\u8bbe\u7f6e degree\uff0c\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u7684\u65f6\u5019\u540e\u5c31\u4e0d\u5f00\u5e76\u884c\uff1b\u5982\u679c\u8868\u8bbe\u7f6e\u4e86 degree\uff0c\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u7684\u65f6\u5019\u5c31\u6309\u7167\u8868\u7684 degree \u6765\u5f00\u5e76\u884c\u3002<br \/>\n\u53ef\u4ee5\u67e5\u8be2 DBA_TABLES.degree \u6765\u67e5\u770b\u8868\u7684 degree\uff0c\u4e00\u822c\u60c5\u51b5\u4e0b\uff0c\u8868\u7684 degree \u90fd\u4e3a 1\u3002\u6211\u4eec\u5efa\u8bae\u53ef\u4ee5\u6839\u636e\u5f53\u65f6\u7cfb\u7edf\u7684\u8d1f\u8f7d\u3001\u7cfb\u7edf\u4e2d CPU \u7684\u4e2a\u6570\u4ee5\u53ca\u8868\u5927\u5c0f\u6765\u7efc\u5408\u5224\u65ad\u8bbe\u7f6e\u5e76\u884c\u5ea6\u3002<\/p>\n<\/li>\n<li>\n<p>cascade<br \/>\n\u8868\u793a\u5728\u6536\u96c6\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u7684\u65f6\u5019\uff0c\u662f\u5426\u7ea7\u8054\u6536\u96c6\u7d22\u5f15\u7684\u7edf\u8ba1\u4fe1\u606f\uff0c\u9ed8\u8ba4\u503c\u4e3aDBMS_STATS.AUTO_CASCADE\uff0c\u8868\u793a\u8ba9 Oracle \u81ea\u5df1\u5224\u65ad\u662f\u5426\u7ea7\u8054\u6536\u96c6\u7d22\u5f15\u7684\u7edf\u8ba1\u4fe1\u606f\u3002<\/p>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>1. \u901a\u8fc7@?\/rdbms\/admin\/sqltr&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,21],"tags":[16,219],"class_list":{"0":"post-1800","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"hentry","6":"category-oracle","8":"tag-oracle","9":"tag-sql-tune"},"_links":{"self":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/1800","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1800"}],"version-history":[{"count":1,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/1800\/revisions"}],"predecessor-version":[{"id":1801,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/1800\/revisions\/1801"}],"wp:attachment":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1800"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1800"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1800"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}