Oracle11g 多列收集统计信息--直方图
发布时间：2020-12-12 16:36:49 所属栏目：百科 来源：网络整理
导读：最近发现有一条SQL要执行几百秒，是执行计划走错了，为什么走错呢？来看一下： SELECT * FROM (SELECT * FROM (SELECT INNER_TABLE.*,ROWNUM OUTER_TABLE_ROWNUM FROM (SELECT DISTINCT PRO.PROJECT_ID, PRO.PROJECT_NAME, PRO.PROJECT_CODE, PRO.PROJECT_CH
|
最近发现有一条SQL要执行几百秒，是执行计划走错了，为什么走错呢？来看一下： SELECT * FROM (SELECT *FROM (SELECT INNER_TABLE.*,ROWNUM OUTER_TABLE_ROWNUM FROM (SELECT DISTINCT PRO.PROJECT_ID, PRO.PROJECT_NAME, PRO.PROJECT_CODE, PRO.PROJECT_CHARGER, PRO.EXE_DEPT, PRO.EXE_DEPT_CODE, PRO.CONS_DEPT, PRO.CONS_DEPT_CODE, PRO.PROJECT_ACCESS_TYPE, PROJECT_AMOUNT, PRO.PROJECT_TYPE_CODE, VER.SANCTIFIED_AMOUNT, VER.BUDGET_AMOUNT, PRO.APPLY_YEAR, PRO.PROJECT_PROPERTY, PRO.BUILD_TYPE, PRO.PROJECT_TYPE, PRO.PLAN_START_DATE, PRO.TECH_PROJECT_CODE, PRO.PLAN_COMPLETE_DATE, PRO.LOCAL_CODE, PRO.DATA_AREA DATA_AREA, PRO.PROJECT_KIND, PRO.PROJECT_ATTRIBUTE, PRO.BELONGS_CATEGORY BELONGS_CATEGORY, PRO.FUND_SOURCE FUND_SOURCE, PRO.PROJECT_CHARGER_ID PROJECT_CHARGER_ID, V_BGG.TODO_TASK_ID AS TODO_TASK_ID, VER.FBS_VERSION_ID, NULL AS DONE_TASK_ID, VER.LEGAL_MARK, VER.AUDIT_STATUS, VER.PROCESS_INSTANCE_ID, V_BGG.READ_FLAG, VER.FBS_CLASS, V_BGG.BACK_FLAG AS BACK_FLAG FROM GG_PROJECT PRO, GG_FBS_VERSION VER, V_GG_BGG_TODOTASK V_BGG WHERE PRO.PROJECT_ID = VER.PROJECT_ID AND (VER.VERSION_ID = '1' OR VER.FBS_CLASS = '2') AND V_BGG.MAIN_PROCESS_INS_ID = VER.PROCESS_INSTANCE_ID AND V_BGG.VERSION = 8 AND VER.AUDIT_STATUS != '3' AND V_BGG.TRANS_ACTOR_ID = 'DFA179F838A14CAFB96D065948F46D86' AND V_BGG.CUR_NODE_ID = 'Task_5' AND VER.PROJECT_TYPE_CODE = PRO.PROJECT_TYPE_CODE AND PRO.PROJECT_STATUS < 40 AND PRO.PROJECT_TYPE_CODE = '4') INNER_TABLE) WHERE OUTER_TABLE_ROWNUM <= 10) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM > 0 ; --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1071 | 5101 | 00:01:02 | | * 1 | VIEW | | 1 | 1071 | 5101 | 00:01:02 | | 2 | COUNT | | | | | | | 3 | NESTED LOOPS | | 1 | 580 | 5101 | 00:01:02 | | 4 | NESTED LOOPS | | 256 | 580 | 5101 | 00:01:02 | | 5 | MERGE JOIN CARTESIAN | | 32 | 16704 | 5012 | 00:01:01 | | * 6 | TABLE ACCESS BY INDEX ROWID | GG_RU_TODO_TASK_GG | 1 | 121 | 5 | 00:00:01 | | * 7 | INDEX RANGE SCAN | TODO_TASK_GG_I_2 | 3 | | 3 | 00:00:01 | | 8 | BUFFER SORT | | 9223 | 3698423 | 5007 | 00:01:01 | | 9 | PARTITION LIST SINGLE | | 9223 | 3698423 | 5007 | 00:01:01 | | * 10 | TABLE ACCESS FULL | GG_PROJECT | 9223 | 3698423 | 5007 | 00:01:01 | | * 11 | INDEX RANGE SCAN | FBS_PROJECT_ID_TEMP1231 | 8 | | 2 | 00:00:01 | | * 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | GG_FBS_VERSION | 1 | 58 | 4 | 00:00:01 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter("from$_subquery$_002"."OUTER_TABLE_ROWNUM">0 AND "OUTER_TABLE_ROWNUM"<=10) * 6 - filter("BRTT"."VERSION"=8 AND "BRTT"."CUR_NODE_ID"='Task_5') * 7 - access("BRTT"."TRANS_ACTOR_ID"='F2D29C42D4904336B0CDEBEC6BC0B68A') * 10 - filter(TO_NUMBER("PRO"."PROJECT_STATUS")<40) * 11 - access("PRO"."PROJECT_ID"="PROJECT_ID") * 12 - filter("VER"."PROCESS_INSTANCE_ID" IS NOT NULL AND "VER"."AUDIT_STATUS"<>3 AND ("VER"."VERSION_ID"='1' OR "VER"."FBS_CLASS"=2) AND "VER"."PROJECT_TYPE_CODE"='4' AND "BRTT"."MAIN_PROCESS_INS_ID"="VER"."PROCESS_INSTANCE_ID")
exec dbms_stats.gather_table_stats(user,'GG_RU_TODO_TASK_GG',cascade => true,degree => 4,method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (TRANS_ACTOR_ID,CUR_NODE_ID,version)',no_invalidate=>FALSE); -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1071 | 6096 | 00:01:14 | | * 1 | VIEW | | 1 | 1071 | 6096 | 00:01:14 | | 2 | COUNT | | | | | | | 3 | NESTED LOOPS | | 1 | 580 | 6096 | 00:01:14 | | 4 | NESTED LOOPS | | 1 | 580 | 6096 | 00:01:14 | | * 5 | HASH JOIN | | 1 | 179 | 6094 | 00:01:14 | | * 6 | TABLE ACCESS BY INDEX ROWID | BGG_RU_TODO_TASK_GG | 3 | 363| 5 | 00:00:01 | | * 7 | INDEX RANGE SCAN | TODO_TASK_GG_I_2 | 3 | | 3 | 00:00:01 | | 8 | PARTITION LIST SINGLE | | 27586 | 1599988 | 6089 | 00:01:14 | | 9 | PARTITION LIST ALL | | 27586 | 1599988 | 6089 | 00:01:14 | | * 10 | TABLE ACCESS FULL | GG_FBS_VERSION | 27586 | 1599988 | 6089 | 00:01:14 | | * 11 | INDEX UNIQUE SCAN | PK_PROJECT_ID_T | 1 | | 1 | 00:00:01 | | * 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | GG_PROJECT | 1 | 401 | 2 | 00:00:01 | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter("from$_subquery$_002"."OUTER_TABLE_ROWNUM">0 AND "OUTER_TABLE_ROWNUM"<=10) * 5 - access("BRTT"."MAIN_PROCESS_INS_ID"="VER"."PROCESS_INSTANCE_ID") * 6 - filter("BRTT"."VERSION"=8 AND "BRTT"."CUR_NODE_ID"='Task_1') * 7 - access("BRTT"."TRANS_ACTOR_ID"='1F624F50BCAD4231B165BA246E582243') * 10 - filter("VER"."PROCESS_INSTANCE_ID" IS NOT NULL AND "VER"."AUDIT_STATUS"<>3 AND ("VER"."VERSION_ID"='1' OR "VER"."FBS_CLASS"=2)) * 11 - access("PRO"."PROJECT_ID"="VER"."PROJECT_ID") * 12 - filter(TO_NUMBER("PRO"."PROJECT_STATUS")<40 AND "PRO"."PROJECT_TYPE_CODE"='4') （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |