分析行連結的簡單方法

westzq1984發表於2012-04-06
最近做優化，發現系統的行連線比較多，但是用analyze分析太慢，影響也比較大

於是從SESSION統計方面入手，寫了個簡單的分析行連線的指令碼。效果相當不錯

--------------------------------------------------------------------------------
--
-- File name:   chains_stats.sql
-- Author:      zhangqiao
-- Copyright:   zhangqiaoc@olm.com.cn
--
--------------------------------------------------------------------------------

DECLARE
  LN_ROW_CNT_1       NUMBER;
  LN_ROW_CNT_2       NUMBER;
  LN_ROW_CONTINUED_1 NUMBER;
  LN_ROW_CONTINUED_2 NUMBER;
  LN_CNT             NUMBER;
  LVC_SQL            VARCHAR2(4000);
  LVC_STATS_SQL      VARCHAR2(4000) := '
      SELECT SUM(DECODE(NAME, ''table scan rows gotten'', VALUE)) ROW_CNT,
           SUM(DECODE(NAME, ''table fetch continued row'', VALUE)) ROW_CONTINUED
      FROM V$MYSTAT A, V$STATNAME B
     WHERE A.STATISTIC# = B.STATISTIC#
       AND NAME IN (''table scan rows gotten'', ''table fetch continued row'')';
  CURSOR C1 IS
    SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE
      FROM DBA_SEGMENTS
     WHERE SEGMENT_TYPE LIKE 'TABLE%'
       AND WNER = '&1';
BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'create table zhangqiaoc_analyze_chains(
                         owner VARCHAR2(30),
                         table_name VARCHAR2(30),
                         partition_name varchar2(30),
                         segment_type VARCHAR2(30),
                         row_cnt NUMBER,
                         row_continued NUMBER,
                         ANALYZED_DATE DATE)';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  FOR R1 IN C1 LOOP
    EXECUTE IMMEDIATE LVC_STATS_SQL
      INTO LN_ROW_CNT_1, LN_ROW_CONTINUED_1;
    IF R1.SEGMENT_TYPE = 'TABLE' THEN
      LVC_SQL := 'select /*+parallel(a,8)*/ count(*) from ' || R1.OWNER || '.' ||
                 R1.SEGMENT_NAME || ' a';
    ELSIF R1.SEGMENT_TYPE = 'TABLE PARTITION' THEN
      LVC_SQL := 'select /*+parallel(a,8)*/ count(*) from ' || R1.OWNER || '.' ||
                 R1.SEGMENT_NAME || ' partition(' || R1.PARTITION_NAME || ') a';
    ELSIF R1.SEGMENT_TYPE = 'TABLE SUBPARTITION' THEN
      LVC_SQL := 'select /*+parallel(a,8)*/ count(*) from ' || R1.OWNER || '.' ||
                 R1.SEGMENT_NAME || ' subpartition(' || R1.PARTITION_NAME || ') a';
    END IF;
    EXECUTE IMMEDIATE LVC_SQL
      INTO LN_CNT;
    EXECUTE IMMEDIATE LVC_STATS_SQL
      INTO LN_ROW_CNT_2, LN_ROW_CONTINUED_2;
    INSERT INTO ZHANGQIAOC_ANALYZE_CHAINS
      SELECT R1.OWNER,
             R1.SEGMENT_NAME,
             R1.PARTITION_NAME,
             R1.SEGMENT_TYPE,
             LN_ROW_CNT_2 - LN_ROW_CNT_1,
             LN_ROW_CONTINUED_2 - LN_ROW_CONTINUED_1,
             SYSDATE
        FROM DUAL;
    COMMIT;
  END LOOP;
END;
/


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-720512/，如需轉載，請註明出處，否則將追究法律責任。

相關文章