最近做优化,发现系统的行连接比较多,但是用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;
/
于是从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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8242091/viewspace-720512/