Oracle数据倾斜优化案例

BEGIN   DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'PORTAL_HIS',                                 tabname          => 'YS_ZY_JZJL',                                 estimate_percent => 100,                                 method_opt       => 'for all columns size skewonly for columns(KSDM,CYBZ)',                                 no_invalidate    => FALSE,                                 cascade          => TRUE); END; / PORTAL_HIS.YS_ZY_JZJL alter index PORTAL_HISIDX_YS_ZY_JZJL_CYBZ usable; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> col table_owner for a15 SQL> set line 150 SQL> col table_name for a40 SQL> col column_name for a30 SQL> col index_name for a30 SQL> select table_owner, table_name, column_name, index_name  from dba_ind_columns where table_owner = 'PORTAL_HIS' and table_name = 'YS_ZY_JZJL';  TABLE_OWNER     TABLE_NAME                               COLUMN_NAME                    INDEX_NAME --------------- ---------------------------------------- ------------------------------ ------------------------------ PORTAL_HIS      YS_ZY_JZJL                               JZHM                           PK_YS_ZY_JZJL PORTAL_HIS      YS_ZY_JZJL                               KSDM                           IDX_YS_ZY_JZJL_KSDM PORTAL_HIS      YS_ZY_JZJL                               YSDM                           IDX_YS_ZY_JZJL_YSDM PORTAL_HIS      YS_ZY_JZJL                               RYRQ                           IDX_YS_ZY_JZJL_RYRQ PORTAL_HIS      YS_ZY_JZJL                               CYRQ                           IDX_YS_ZY_JZJL_CYRQ PORTAL_HIS      YS_ZY_JZJL                               CYBZ                           IDX_YS_ZY_JZJL_CYBZ PORTAL_HIS      YS_ZY_JZJL                               ZLXZ                           IDX_YS_ZY_JZJL_ZLXZ PORTAL_HIS      YS_ZY_JZJL                               JZXH                           IDX_YS_ZY_JZJL_JZXH 8 rows selected. SQL> select owner,table_name,object_type,stale_stats,num_rows,last_analyzed from  dba_tab_statistics where table_name='YS_ZY_JZJL'; OWNER          TABLE_NAME   OBJECT_TYPE  STALE_STA   NUM_ROWS LAST_ANALYZED -------------- ---------------------------------------- PORTAL_HIS     YS_ZY_JZJL  TABLE SQL> select count(*),count(distinct KSDM) from PORTAL_HIS.YS_ZY_JZJL;   COUNT(*) COUNT(DISTINCTKSDM) ---------- -------------------      14487                   6 SQL> select count(*),count(distinct KSDM),count(distinct CYBZ) from PORTAL_HIS.YS_ZY_JZJL;   COUNT(*) COUNT(DISTINCTKSDM) COUNT(DISTINCTCYBZ) ---------- ------------------- -------------------      14487                   6                   2 SQL> select KSDM,count(1) from PORTAL_HIS.YS_ZY_JZJL group by KSDM; KSDM                             COUNT(1) ------------------------------ ---------- 100                                   461 135                                   259 8                                   11278 7                                    2404 91                                     77 119                                     8 6 rows selected. SQL> select CYBZ,count(1) from PORTAL_HIS.YS_ZY_JZJL group by CYBZ;       CYBZ   COUNT(1) ---------- ----------          1      14410          0         77 SQL> select index_name,STATUS from dba_indexes where index_name='IDX_YS_ZY_JZJL_CYBZ'; INDEX_NAME                     STATUS ------------------------------ ------------------------ IDX_YS_ZY_JZJL_CYBZ            UNUSABLE SQL>  SQL>  SQL>  SQL>  SQL>  SQL>  SQL> BEGIN   2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'PORTAL_HIS',   3                                  tabname          => 'YS_ZY_JZJL',   4                                  estimate_percent => 100,   5                                  method_opt       => 'for all columns size skewonly for columns(KSDM,CYBZ)',   6                                  no_invalidate    => FALSE,   7                                  cascade          => TRUE);   8  END;   9  / PL/SQL procedure successfully completed. SQL>  SQL>  SQL>  SQL>  SQL>  SQL> set line 150 SQL>  SQL>  SQL> select * from table(dbms_xplan.DISPLAY_CURSOR('2wkpmjj7v2dw3', null, 'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  2wkpmjj7v2dw3, child number 0 ------------------------------------- Select V_EMR_YW_JZJL.JZHM,   V_EMR_YW_JZJL.BRBH,   V_EMR_YW_JZJL.BRXM,  V_EMR_YW_JZJL.BRXB,   V_EMR_YW_JZJL.CSRQ,   V_EMR_YW_JZJL.BRXZ, V_EMR_YW_JZJL.KSDM,   V_EMR_YW_JZJL.BQDM,   V_EMR_YW_JZJL.CWHM, V_EMR_YW_JZJL.YSDM,   V_EMR_YW_JZJL.ZYYS,   V_EMR_YW_JZJL.ZRYS, V_EMR_YW_JZJL.HLJB,   V_EMR_YW_JZJL.RYZD,   V_EMR_YW_JZJL.ZDMC, V_EMR_YW_JZJL.RYQK,   V_EMR_YW_JZJL.RYRQ,   V_EMR_YW_JZJL.CYRQ as outdata,   V_EMR_YW_JZJL.CYBZ as outflag,   V_EMR_YW_JZJL.ZDMC, V_EMR_YW_JZJL.RYNL AS BRNL,   V_EMR_YW_JZJL.ZKZT ,   V_EMR_YW_JZJL.ZGQK ,   V_EMR_YW_JZJL.ZLXZ as TeamId,   V_EMR_YW_JZJL.JLLX AS VisitType, '              '  AS OtherFlag,   '              '  AS ChargeTypeName,  0  AS XQBZ,   V_EMR_YW_JZJL.BRQK,   V_EMR_YW_JZJL.ZYCS, V_EMR_YW_JZJL.BAHM,   V_EMR_YW_JZJL.BRID,   0 AS JRRY,   0  AS QCBZ, 0 AS YGBZ,   0 AS SSQ,   0 AS SSH,   0 as QFBR,   0 AS LCLJ,   0 AS WTYZ,   0 AS YZYTS,   0 AS YZKBR,   0 AS PatientLevel,   '                     ' AS BRTX,   ' Plan hash value: 1733410705 PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- | Id  | Operation                       | Name                | E-Rows |  OMem |  1Mem | Used-Mem | |   0 | SELECT STATEMENT                |                     |        |       |       |          | |   1 |  VIEW                           | V_EMR_YW_JZJL       |     52 |       |       |          | |   2 |   SORT UNIQUE                   |                     |     52 | 15360 | 15360 |14336  (0)| |   3 |    UNION-ALL                    |                     |        |       |       |          | |*  4 |     TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL          |     35 |       |       |          | |*  5 |      INDEX RANGE SCAN           | IDX_YS_ZY_JZJL_KSDM |  11447 |       |       |          | |   6 |     NESTED LOOPS                |                     |     17 |       |       |          | |   7 |      NESTED LOOPS               |                     |     81M|       |       |          | |   8 |       TABLE ACCESS FULL         | BQ_XSEDJ            |   7082 |       |       |          | |*  9 |       INDEX RANGE SCAN          | IDX_YS_ZY_JZJL_KSDM |  11447 |       |       |          | |* 10 |      TABLE ACCESS BY INDEX ROWID| YS_ZY_JZJL          |      1 |       |       |          | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    4 - filter("YS_ZY_JZJL"."CYBZ"=0)    5 - access("YS_ZY_JZJL"."KSDM"='8')    9 - access("YS_ZY_JZJL"."KSDM"='8')   10 - filter(("YS_ZY_JZJL"."CYBZ"=0 AND "YS_ZY_JZJL"."JZHM"=CAST("MQZYH" AS VARCHAR(18)))) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Note -----    - dynamic sampling used for this statement (level=2)    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level 51 rows selected. SQL> / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  2wkpmjj7v2dw3, child number 0 ------------------------------------- Select V_EMR_YW_JZJL.JZHM,   V_EMR_YW_JZJL.BRBH,   V_EMR_YW_JZJL.BRXM,  V_EMR_YW_JZJL.BRXB,   V_EMR_YW_JZJL.CSRQ,   V_EMR_YW_JZJL.BRXZ, V_EMR_YW_JZJL.KSDM,   V_EMR_YW_JZJL.BQDM,   V_EMR_YW_JZJL.CWHM, V_EMR_YW_JZJL.YSDM,   V_EMR_YW_JZJL.ZYYS,   V_EMR_YW_JZJL.ZRYS, V_EMR_YW_JZJL.HLJB,   V_EMR_YW_JZJL.RYZD,   V_EMR_YW_JZJL.ZDMC, V_EMR_YW_JZJL.RYQK,   V_EMR_YW_JZJL.RYRQ,   V_EMR_YW_JZJL.CYRQ as outdata,   V_EMR_YW_JZJL.CYBZ as outflag,   V_EMR_YW_JZJL.ZDMC, V_EMR_YW_JZJL.RYNL AS BRNL,   V_EMR_YW_JZJL.ZKZT ,   V_EMR_YW_JZJL.ZGQK ,   V_EMR_YW_JZJL.ZLXZ as TeamId,   V_EMR_YW_JZJL.JLLX AS VisitType, PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ '              '  AS OtherFlag,   '              '  AS ChargeTypeName,  0  AS XQBZ,   V_EMR_YW_JZJL.BRQK,   V_EMR_YW_JZJL.ZYCS, V_EMR_YW_JZJL.BAHM,   V_EMR_YW_JZJL.BRID,   0 AS JRRY,   0  AS QCBZ, 0 AS YGBZ,   0 AS SSQ,   0 AS SSH,   0 as QFBR,   0 AS LCLJ,   0 AS WTYZ,   0 AS YZYTS,   0 AS YZKBR,   0 AS PatientLevel,   '                     ' AS BRTX,   ' Plan hash value: 1733410705 PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- | Id  | Operation                       | Name                | E-Rows |  OMem |  1Mem | Used-Mem | |   0 | SELECT STATEMENT                |                     |        |       |       |          | |   1 |  VIEW                           | V_EMR_YW_JZJL       |     52 |       |       |          | |   2 |   SORT UNIQUE                   |                     |     52 | 15360 | 15360 |14336  (0)| |   3 |    UNION-ALL                    |                     |        |       |       |          | |*  4 |     TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL          |     35 |       |       |          | |*  5 |      INDEX RANGE SCAN           | IDX_YS_ZY_JZJL_KSDM |  11447 |       |       |          | |   6 |     NESTED LOOPS                |                     |     17 |       |       |          | |   7 |      NESTED LOOPS               |                     |     81M|       |       |          | |   8 |       TABLE ACCESS FULL         | BQ_XSEDJ            |   7082 |       |       |          | |*  9 |       INDEX RANGE SCAN          | IDX_YS_ZY_JZJL_KSDM |  11447 |       |       |          | |* 10 |      TABLE ACCESS BY INDEX ROWID| YS_ZY_JZJL          |      1 |       |       |          | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    4 - filter("YS_ZY_JZJL"."CYBZ"=0)    5 - access("YS_ZY_JZJL"."KSDM"='8')    9 - access("YS_ZY_JZJL"."KSDM"='8')   10 - filter(("YS_ZY_JZJL"."CYBZ"=0 AND "YS_ZY_JZJL"."JZHM"=CAST("MQZYH" AS VARCHAR(18)))) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Note -----    - dynamic sampling used for this statement (level=2)    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level 51 rows selected. SQL> / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  2wkpmjj7v2dw3, child number 0 ------------------------------------- Select V_EMR_YW_JZJL.JZHM,   V_EMR_YW_JZJL.BRBH,   V_EMR_YW_JZJL.BRXM,  V_EMR_YW_JZJL.BRXB,   V_EMR_YW_JZJL.CSRQ,   V_EMR_YW_JZJL.BRXZ, V_EMR_YW_JZJL.KSDM,   V_EMR_YW_JZJL.BQDM,   V_EMR_YW_JZJL.CWHM, V_EMR_YW_JZJL.YSDM,   V_EMR_YW_JZJL.ZYYS,   V_EMR_YW_JZJL.ZRYS, V_EMR_YW_JZJL.HLJB,   V_EMR_YW_JZJL.RYZD,   V_EMR_YW_JZJL.ZDMC, V_EMR_YW_JZJL.RYQK,   V_EMR_YW_JZJL.RYRQ,   V_EMR_YW_JZJL.CYRQ as outdata,   V_EMR_YW_JZJL.CYBZ as outflag,   V_EMR_YW_JZJL.ZDMC, V_EMR_YW_JZJL.RYNL AS BRNL,   V_EMR_YW_JZJL.ZKZT ,   V_EMR_YW_JZJL.ZGQK ,   V_EMR_YW_JZJL.ZLXZ as TeamId,   V_EMR_YW_JZJL.JLLX AS VisitType, PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ '              '  AS OtherFlag,   '              '  AS ChargeTypeName,  0  AS XQBZ,   V_EMR_YW_JZJL.BRQK,   V_EMR_YW_JZJL.ZYCS, V_EMR_YW_JZJL.BAHM,   V_EMR_YW_JZJL.BRID,   0 AS JRRY,   0  AS QCBZ, 0 AS YGBZ,   0 AS SSQ,   0 AS SSH,   0 as QFBR,   0 AS LCLJ,   0 AS WTYZ,   0 AS YZYTS,   0 AS YZKBR,   0 AS PatientLevel,   '                     ' AS BRTX,   ' Plan hash value: 2971752397 PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- | Id  | Operation             | Name          | E-Rows |  OMem |  1Mem | Used-Mem | |   0 | SELECT STATEMENT      |               |        |       |       |          | |   1 |  VIEW                 | V_EMR_YW_JZJL |     64 |       |       |          | |   2 |   SORT UNIQUE         |               |     64 | 15360 | 15360 |14336  (0)| |   3 |    UNION-ALL          |               |        |       |       |          | |*  4 |     TABLE ACCESS FULL | YS_ZY_JZJL    |     43 |       |       |          | |*  5 |     HASH JOIN         |               |     21 |   883K|   883K| 1264K (0)| |*  6 |      TABLE ACCESS FULL| YS_ZY_JZJL    |     43 |       |       |          | |   7 |      TABLE ACCESS FULL| BQ_XSEDJ      |   7082 |       |       |          | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    4 - filter(("YS_ZY_JZJL"."CYBZ"=0 AND "YS_ZY_JZJL"."KSDM"='8'))    5 - access("YS_ZY_JZJL"."JZHM"=CAST("MQZYH" AS VARCHAR(18)))    6 - filter(("YS_ZY_JZJL"."CYBZ"=0 AND "YS_ZY_JZJL"."KSDM"='8')) Note -----    - dynamic sampling used for this statement (level=2)    - Warning: basic plan statistics not available. These are only collected when: PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level 47 rows selected. SQL> set pagesize 0