配置环境 - 定制脚本

最新推荐文章于 2024-01-14 15:26:32 发布

ZhouZhiqiangFreesky

最新推荐文章于 2024-01-14 15:26:32 发布

阅读量608

点赞数

分类专栏： Expert Oracle Database - Architecture 9i and 10g Programming Techniques and Solutions 文章标签： insert table oracle delete cache windows

本文链接：https://blog.csdn.net/ZhouZhiqiangFreesky/article/details/1788468

版权

Expert Oracle Database - Architecture 9i and 10g Programming Techniques and Solutions 专栏收录该内容

29 篇文章 0 订阅

订阅专栏

1.RunStats - 对做同一件事的两个不同方法进行比较，得出孰优孰劣的结果。

RunStats只是测量3个要素：

墙上时钟（wall clock）或耗用时间（elapsed time）
系统统计结果
闩定（latching）

Step1：创建Stats视图

CREATE OR REPLACE VIEW Stats
AS
  SELECT 'STAT...' || sn.NAME NAME,
         ms.VALUE
    FROM v$StatName sn,
         v$MyStat ms
   WHERE sn.Statistic# = ms.Statistic#
  UNION ALL
  SELECT 'LATCH.' || l.NAME,
         l.Gets
    FROM v$Latch l;

Step2：创建一个表来收集统计结果

CREATE GLOBAL TEMPORARY TABLE Run_Stats
(
  RunID VARCHAR2(15),
  Name  VARCHAR2(80),
  Value INT
)
ON COMMIT PRESERVE ROWS;

Step3：创建RunStats_pkg包的说明

CREATE OR REPLACE PACKAGE RunStats_pkg
AS
  PROCEDURE rs_Start;
  PROCEDURE rs_Middle;
  PROCEDURE rs_Stop(p_Difference_Threshold IN NUMBER DEFAULT 0);
END;

Step4：创建RunStats包体

CREATE OR REPLACE PACKAGE BODY RunStats_pkg
AS
  g_Start NUMBER;
  g_Run1  NUMBER;
  g_Run2  NUMBER;

  PROCEDURE rs_Start
  IS
  BEGIN
    DELETE
      FROM Run_Stats;

    INSERT INTO Run_Stats
    SELECT 'Before',
           s.*
      FROM Stats s;

    g_Start := DBMS_UTILITY.GET_TIME;
  END rs_Start;

  PROCEDURE rs_Middle
  IS
  BEGIN
    g_Run1 := DBMS_UTILITY.GET_TIME - g_Start;

    INSERT INTO Run_Stats
    SELECT 'After 1',
           s.*
      FROM Stats s;

    g_Start := DBMS_UTILITY.GET_TIME;
  END rs_Middle;

  PROCEDURE rs_Stop
  (
    p_Difference_Threshold IN NUMBER DEFAULT 0
  )
  IS
  BEGIN
    g_Run2 := DBMS_UTILITY.GET_TIME - g_Start;

    DBMS_OUTPUT.PUT_LINE('Run1 ran in ' || g_Run1 || ' hsecs');
    DBMS_OUTPUT.PUT_LINE('Run2 ran in ' || g_Run2 || ' hsecs');
    DBMS_OUTPUT.PUT_LINE('Run1 ran in ' || ROUND(g_Run1 / g_Run2 * 100, 2) || '% of the time');

    DBMS_OUTPUT.PUT_LINE(CHR(9));

    INSERT INTO Run_Stats
    SELECT 'After 2',
           s.*
      FROM Stats s;

    DBMS_OUTPUT.PUT_LINE(RPAD('Name', 30) || LPAD('Run1', 10) || LPAD('Run2', 10) || LPAD('Diff', 10));

    FOR x IN (SELECT RPAD(a.Name, 30) ||
                     TO_CHAR(b.Value - a.Value, '999,999,999') ||
                     TO_CHAR(c.Value - b.Value, '999,999,999') ||
                     TO_CHAR(((c.Value - b.Value) - (b.Value - a.Value)), '999,999,999') Data
                FROM Run_Stats a,
                     Run_Stats b,
                     Run_Stats c
               WHERE a.Name = b.Name
                 AND b.Name = c.Name
                 AND a.RunID = 'Before'
                 AND b.RunID = 'After 1'
                 AND c.RunID = 'After 2'
                 AND (c.Value - a.Value) > 0
                 AND ABS((c.Value - b.Value) - (b.Value - a.Value)) > p_Difference_Threshold
               ORDER BY ABS((c.Value - b.Value) - (b.Value - a.Value)))
    LOOP
      DBMS_OUTPUT.PUT_LINE(x.Data);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(CHR(9));

    DBMS_OUTPUT.PUT_LINE('Run1 lathces total versus runs -- difference and pct');
    DBMS_OUTPUT.PUT_LINE(LPAD('Run1', 10) || LPAD('Run2', 10) || LPAD('Diff', 10) || LPAD('Pct', 8));

    FOR x IN (SELECT TO_CHAR(Run1, '999,999,999') ||
                     TO_CHAR(Run2, '999,999,999') ||
                     TO_CHAR(Diff, '999,999,999') ||
                     TO_CHAR(ROUND(Run1 / Run2 * 100, 2), '99,999.99') || '%' Data
                FROM (SELECT SUM(b.Value - a.Value) Run1,
                             SUM(c.Value - b.Value) Run2,
                             SUM((c.Value - b.Value) - (b.Value - a.Value)) Diff
                        FROM Run_Stats a,
                             Run_Stats b,
                             Run_Stats c
                       WHERE a.Name = b.Name
                         AND b.Name = c.Name
                         AND a.RunID = 'Before'
                         AND b.RunID = 'After 1'
                         AND c.RunID = 'After 2'
                         AND a.Name LIKE 'LATCH%'))
    LOOP
      DBMS_OUTPUT.PUT_LINE(x.Data);
    END LOOP;
  END rs_Stop;
END;

eg.下面的例子对批量插入（INSERT）和逐行处理进行比较，看看哪种方法效率更高

MUZIYU@MYDB> CREATE TABLE t1
  2  AS
  3    SELECT *
  4      FROM Big_Table
  5     WHERE 1 = 0;

表已创建。

MUZIYU@MYDB> CREATE TABLE t2
  2  AS
  3    SELECT *
  4      FROM Big_Table
  5     WHERE 1 = 0;

表已创建。

MUZIYU@MYDB> EXEC RunStats_Pkg.Rs_Start;

PL/SQL 过程已成功完成。

MUZIYU@MYDB> INSERT INTO t1
  2  SELECT *
  3    FROM Big_Table;

已创建1000000行。

MUZIYU@MYDB> COMMIT;

提交完成。

MUZIYU@MYDB> EXEC RunStats_Pkg.Rs_Middle;

PL/SQL 过程已成功完成。

MUZIYU@MYDB> BEGIN
  2    FOR x IN (SELECT * FROM Big_Table)
  3    LOOP
  4      INSERT INTO t2 VALUES x;
  5    END LOOP;
  6    COMMIT;
  7  END;
  8  /

PL/SQL 过程已成功完成。

MUZIYU@MYDB> EXEC RunStats_Pkg.Rs_Stop(1000000);
Run1 ran in 7502 hsecs
Run2 ran in 14030 hsecs
Run1 ran in 53.47% of the time
        
Name                           Run1        Run2        Diff
STAT...execute count                   215   1,000,316   1,000,101
LATCH.shared pool                    2,293   1,003,035   1,000,742
STAT...session logical reads       184,145   2,139,195   1,955,050
STAT...db block changes            108,638   2,084,264   1,975,626
STAT...recursive calls               2,283   2,002,931   2,000,648
LATCH.library cache pin              2,356   2,003,292   2,000,936
LATCH.library cache                  3,881   2,005,397   2,001,516
STAT...session pga memory           82,208   4,196,448   4,114,240
STAT...session pga memory max       66,072   4,393,056   4,326,984
LATCH.cache buffers chains         590,988   7,499,318   6,908,330
STAT...redo size               115,567,468 356,828,232 241,260,764
        
Run1 lathces total versus runs -- difference and pct
Run1     Run2        Diff        Pct
856,657  13,952,561  13,095,904  6.14%

PL/SQL 过程已成功完成。

2.MyStat - 用于展示完成某操作之前和之后的某些Oracle“统计结果”的变化情况。

MyStat.SQL：获得结果的开始值

SET ECHO OFF
SET VERIFY OFF
COLUMN Value New_Val V
DEFINE S = "&1"

SET AUTOTRACE OFF
SELECT sn.Name,
       ms.Value
  FROM v$StatName sn,
       v$MyStat ms
 WHERE sn.Statistic# = ms.Statistic#
   AND LOWER(sn.Name) LIKE '%' || LOWER('&S') || '%'
/

SET ECHO ON

MyStat2.SQL：报告统计结果的变化情况（差值）

SET ECHO OFF
SET VERIFY OFF

SELECT sn.Name,
       ms.Value V,
       TO_CHAR(ms.Value - &V, '999999999999') Diff
  FROM v$StatName sn,
       v$MyStat ms
 WHERE sn.Statistic# = ms.Statistic#
   AND LOWER(sn.Name) LIKE '%' || LOWER('&S') || '%'
/

SET ECHO ON

eg.删除表Dept数据，将会生成1940字节的Redo

MUZIYU@MYDB> @D:/SQL/MyStat.SQL "redo size"

NAME                                                             VALUE
---------------------------------------------------------------- ----------
redo size                                                        556

MUZIYU@MYDB> DELETE FROM DEPT;

已删除4行。

MUZIYU@MYDB> @D:/SQL/MyStat2.SQL
MUZIYU@MYDB> SET ECHO OFF

NAME                                                             V          DIFF
---------------------------------------------------------------- ---------- -------------
redo size                                                        2496       1940

软件环境：Windows XP SP3+Oracle 9i R2，[ORACLE_HOME]=C:/Oracle/Ora92