最近做优化，发现系统的行连接比较多，但是用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博客

分析行链接的简单方法

最新文章