《Oracle PL/SQL实例精讲》学习笔记26——优化PL/SQL（第二部分——PL/SQL优化级别）

本章内容：

1. PL/SQL调优工具

2. PL/SQL优化级别

3. 子程序内联

 

代码如下：

1. 查看PROFTAB.sql内容

Rem
Rem $Header: plsql/admin/proftab.sql /main/4 2013/07/08 10:56:15 sylin Exp $
Rem
Rem proftab.sql
Rem
Rem Copyright (c) 1998, 2013, Oracle and/or its affiliates. 
Rem All rights reserved.
Rem
Rem    NAME
Rem      proftab.sql
Rem
Rem    DESCRIPTION
Rem      Create tables for the PL/SQL profiler
Rem
Rem    NOTES
Rem      The following tables are required to collect data:
Rem        plsql_profiler_runs  - information on profiler runs
Rem        plsql_profiler_units - information on each lu profiled
Rem        plsql_profiler_data  - profiler data for each lu profiled
Rem
Rem      The plsql_profiler_runnumber sequence is used for generating unique
Rem      run numbers.
Rem
Rem      The tables and sequence can be created in the schema for each user
Rem      who wants to gather profiler data. Alternately these tables can be
Rem      created in a central schema. In the latter case the user creating
Rem      these objects is responsible for granting appropriate privileges
Rem      (insert,update on the tables and select on the sequence) to all 
Rem      users who want to store data in the tables. Appropriate synonyms 
Rem      must also be created so the tables are visible from other user 
Rem      schemas.
Rem
Rem      The other tables are used for rolling up to line level; the views are
Rem      used to roll up across multiple runs. These are not required to 
Rem      collect data, but help with analysis of the gathered data. 
Rem
Rem      THIS SCRIPT DELETES ALL EXISTING DATA!
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sylin       06/12/13 - long identifier
Rem    jmuller     10/07/99 - Fix bug 708690: TAB -> blank
Rem    astocks     04/19/99 - Add owner,related_run field to runtab
Rem    astocks     10/21/98 - Add another spare field
Rem    ciyer       09/15/98 - Create tables for profiler
Rem    ciyer       09/15/98 - Created
Rem

drop table plsql_profiler_data cascade constraints;
drop table plsql_profiler_units cascade constraints;
drop table plsql_profiler_runs cascade constraints;

drop sequence plsql_profiler_runnumber;

create table plsql_profiler_runs
(
  runid           number primary key,  -- unique run identifier,
                                       -- from plsql_profiler_runnumber
  related_run     number,              -- runid of related run (for client/
                                       --     server correlation)
  run_owner       varchar2(32),        -- user who started run -- dbms_id
  run_date        date,                -- start time of run
  run_comment     varchar2(2047),      -- user provided comment for this run
  run_total_time  number,              -- elapsed time for this run
  run_system_info varchar2(2047),      -- currently unused
  run_comment1    varchar2(2047),      -- additional comment
  spare1          varchar2(256)        -- unused
);

comment on table plsql_profiler_runs is
        'Run-specific information for the PL/SQL profiler';

create table plsql_profiler_units
(
  runid              number references plsql_profiler_runs,
  unit_number        number,           -- internally generated library unit #
  unit_type          varchar2(32),     -- library unit type
  unit_owner         varchar2(32),     -- library unit owner name -- dbms_id
  unit_name          varchar2(32),     -- library unit name -- dbms_id
  -- timestamp on library unit, can be used to detect changes to
  -- unit between runs
  unit_timestamp     date,
  total_time         number DEFAULT 0 NOT NULL,
  spare1             number,           -- unused
  spare2             number,           -- unused
  --  
  primary key (runid, unit_number)
);

comment on table plsql_profiler_units is 
        'Information about each library unit in a run';

create table plsql_profiler_data
(
  runid           number,           -- unique (generated) run identifier
  unit_number     number,           -- internally generated library unit #
  line#           number not null,  -- line number in unit
  total_occur     number,           -- number of times line was executed
  total_time      number,           -- total time spent executing line
  min_time        number,           -- minimum execution time for this line
  max_time        number,           -- maximum execution time for this line
  spare1          number,           -- unused
  spare2          number,           -- unused
  spare3          number,           -- unused
  spare4          number,           -- unused
  --
  primary key (runid, unit_number, line#),
  foreign key (runid, unit_number) references plsql_profiler_units
);

comment on table plsql_profiler_data is 
        'Accumulated data from all profiler runs';

create sequence plsql_profiler_runnumber start with 1 nocache;

2. 在C##STUDENT模式下运行PROFTAB.sql脚本

SQL> show user
USER is "C##STUDENT"
SQL> @D:\app\pdh\product\12.1.0\dbhome_2\RDBMS\ADMIN\proftab.sql

Table dropped.


Table dropped.


Table dropped.


Sequence dropped.


Table created.


Comment created.


Table created.


Comment created.


Table created.


Comment created.


Sequence created.

3. 在SYS模式下运行PROFLOAD.sql脚本，并对C##STUDENT授权

SQL> @D:\app\pdh\product\12.1.0\dbhome_2\RDBMS\ADMIN\profload.sql

Session altered.


Package created.


Grant succeeded.


Synonym created.


Session altered.


Session altered.


Library created.


Package body created.


Session altered.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

SQL> grant execute on dbms_profiler to c##student
  2  ;

Grant succeeded.

4. 在c##student模式下测试1 （Optimizer level at 0）

SQL> show user
USER is "C##STUDENT"
SQL> SET TIMING ON;
SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

Session altered.

Elapsed: 00:00:00.00
SQL> /* Formatted on 2018/11/20 0:01:52 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     v_num1     NUMBER;
  3     v_num2     NUMBER;
  4     v_num3     NUMBER;
  5     v_run_id   BINARY_INTEGER; --    run    ID    generated    by    the    profiler
  6  BEGIN
  7     DBMS_PROFILER.START_PROFILER ('Optimizer    level    at    0');
  8
  9     FOR i IN 1 .. 1000000
 10     LOOP
 11        v_num1 := 1;
 12        v_num2 := i + i / 2 + SQRT (i);
 13        v_num3 := v_num1 + v_num2;
 14     END LOOP;
 15
 16     DBMS_PROFILER.STOP_PROFILER ();
 17
 18     SELECT min(runid)
 19       INTO v_run_id
 20       FROM plsql_profiler_runs
 21      WHERE run_comment = 'Optimizer    level    at    0';
 22
 23     DBMS_OUTPUT.PUT_LINE (
 24        'Optimizer    level    at    0,    run    ID    -    ' || v_run_id);
 25  END;
 26  /
Optimizer    level    at    0,    run    ID    -    1

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.00

5. 继续测试（Optimizer level at 1）

SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;

Session altered.

Elapsed: 00:00:00.00
SQL> /* Formatted on 2018/11/20 21:31:19 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     v_num1     NUMBER;
  3     v_num2     NUMBER;
  4     v_num3     NUMBER;
  5     v_run_id   BINARY_INTEGER; --    run    ID    generated    by    the    profiler
  6  BEGIN
  7     DBMS_PROFILER.START_PROFILER ('Optimizer    level    at    1');
  8
  9     FOR i IN 1 .. 1000000
 10     LOOP
 11        v_num1 := 1;
 12        v_num2 := i + i / 2 + SQRT (i);
 13        v_num3 := v_num1 + v_num2;
 14     END LOOP;
 15
 16     DBMS_PROFILER.STOP_PROFILER ();
 17
 18     SELECT min(runid)
 19       INTO v_run_id
 20       FROM plsql_profiler_runs
 21      WHERE run_comment = 'Optimizer    level    at    1';
 22
 23     DBMS_OUTPUT.PUT_LINE (
 24        'Optimizer    level    at    1,    run    ID    -    ' || v_run_id);
 25  END;
 26  /
Optimizer    level    at    1,    run    ID    -    2

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.98

6. 继续测试（Optimizer level at 2）

SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2     v_num1     NUMBER;
  3     v_num2     NUMBER;
  4     v_num3     NUMBER;
  5     v_run_id   BINARY_INTEGER; --    run    ID    generated    by    the    profiler
  6  BEGIN
  7     DBMS_PROFILER.START_PROFILER ('Optimizer    level    at    2');
  8
  9     FOR i IN 1 .. 1000000
 10     LOOP
 11        v_num1 := 1;
 12        v_num2 := i + i / 2 + SQRT (i);
 13        v_num3 := v_num1 + v_num2;
 14     END LOOP;
 15
 16     DBMS_PROFILER.STOP_PROFILER ();
 17
 18     SELECT min(runid)
 19       INTO v_run_id
 20       FROM plsql_profiler_runs
 21      WHERE run_comment = 'Optimizer    level    at    2';
 22
 23     DBMS_OUTPUT.PUT_LINE (
 24        'Optimizer    level    at    2,    run    ID    -    ' || v_run_id);
 25  END;
 26  /
Optimizer    level    at    2,    run    ID    -    3

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.29

7. 查看由PL/SQL剖析器生成的数据

SQL> col runid for 9
SQL> col run_comment for a30
SQL> col line# for 99
SQL> col total_occur 9999999
SQL> col total_occur for 9999999
SQL> col total_time for 9999999999
SQL>   SELECT R.RUNID,
  2           R.RUN_COMMENT,
  3           D.LINE#,
  4           D.TOTAL_OCCUR,
  5           D.TOTAL_TIME
  6      FROM PLSQL_PROFILER_RUNS R, PLSQL_PROFILER_DATA D, PLSQL_PROFILER_UNITS U
  7     WHERE     R.RUNID = D.RUNID
  8           AND D.RUNID = U.RUNID
  9           AND D.UNIT_NUMBER = U.UNIT_NUMBER
 10           AND D.TOTAL_OCCUR > 0
 11  ORDER BY D.RUNID, D.LINE#;

RUNID RUN_COMMENT                    LINE# TOTAL_OCCUR  TOTAL_TIME
----- ------------------------------ ----- ----------- -----------
    2 Optimizer    level    at    1      9     1000001   130145331
    2 Optimizer    level    at    1     11     1000000   137672334
    2 Optimizer    level    at    1     12     1000000  1464785069
    2 Optimizer    level    at    1     13     1000000   182513305
    2 Optimizer    level    at    1     14           1           0
    2 Optimizer    level    at    1     16           1        4000
    3 Optimizer    level    at    2      9     1000001   169299824
    3 Optimizer    level    at    2     11     1000000           0
    3 Optimizer    level    at    2     12     1000000  1216234601
    3 Optimizer    level    at    2     13     1000000   216410666
    3 Optimizer    level    at    2     16           1        5013
    4 Optimizer    level    at    0      9     1000001   170080040
    4 Optimizer    level    at    0     11     1000000   244403943
    4 Optimizer    level    at    0     12     1000000  1646097380
    4 Optimizer    level    at    0     13     1000000   242038640
    4 Optimizer    level    at    0     14           1           0
    4 Optimizer    level    at    0     16           1        5017

8. 继续测试（对于空操作，两个优化级别的性能提高很明显）

SQL> -- Create test table
SQL> CREATE TABLE TEST_TAB
  2     (col1 NUMBER);

Table created.

Elapsed: 00:00:00.00
SQL>
SQL> -- Populate newly created table with random data
SQL> INSERT INTO TEST_TAB
  2  SELECT ROUND(DBMS_RANDOM.VALUE (1, 99999999), 0)
  3    FROM dual
  4  CONNECT by level < 100001;

100000 rows created.

Elapsed: 00:00:01.06
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> -- Collect statistics
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (user, 'TEST_TAB');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL>
SQL> -- Run the same code sample with different optimization levels
SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> BEGIN
  2     FOR rec IN (SELECT col1 FROM test_tab)
  3     LOOP
  4        null; -- do nothing
  5     END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.07
SQL>
SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> BEGIN
  2     FOR REC IN (SELECT col1 FROM test_tab)
  3     LOOP
  4        NULL; -- do nothing
  5     END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

9. 继续测试（对于插入操作，两个优化级别的性能提高不明显）

SQL> -- Create test table
SQL> CREATE TABLE test_tab1 (col1 NUMBER);

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> -- Run the same code sample with different optimization levels
SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;

Session altered.

Elapsed: 00:00:00.01
SQL>
SQL> BEGIN
  2     FOR rec IN (SELECT col1 FROM test_tab)
  3     LOOP
  4        INSERT INTO TEST_TAB1 VALUES (rec.col1); -- populate newly created table
  5     END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.10
SQL>
SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> BEGIN
  2     FOR REC IN (SELECT col1 FROM test_tab)
  3     LOOP
  4        INSERT INTO TEST_TAB1 VALUES (rec.col1); -- populate newly created table
  5     END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.87