1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
|
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as mni
SQL>
SQL> drop table mytable;
Table dropped
SQL> drop table mytable_1;
Table dropped
SQL> CREATE TABLE mytable(c1 number);
Table created
SQL> ALTER TABLE mytable ADD (constraint nn_mytable CHECK (c1 IS NOT NULL) novalidate);
Table altered
SQL> CREATE TABLE mytable_1(c1 number NOT NULL);
Table created
SQL> CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;
2 /
Type created
SQL> CREATE or replace FUNCTION row_generator (
2 rows_in IN PLS_INTEGER
3 ) RETURN number_ntt PIPELINED IS
4 BEGIN
5 FOR i IN 1 .. rows_in LOOP
6 PIPE ROW (i);
7 END LOOP;
8 RETURN;
9 END;
10 /
Function created
SQL> desc mytable
Name Type Nullable Default Comments
---- ------ -------- ------- --------
C1 NUMBER Y
SQL> desc mytable_1
Name Type Nullable Default Comments
---- ------ -------- ------- --------
C1 NUMBER
SQL> set serveroutput on
SQL> begin
2 runstats_pkg.rs_start;
3 INSERT INTO mytable_1 SELECT column_value FROM TABLE(row_generator(100000));
4 runstats_pkg.rs_middle;
5 INSERT INTO mytable SELECT column_value FROM TABLE(row_generator(100000));
6 runstats_pkg.rs_stop(1000);
7 end;
8 /
Run1 ran in 81 hsecs
Run2 ran in 96 hsecs
run 1 ran in 84,38% of the time
Name Run1 Run2 Diff
LATCH.row cache objects 929 2,396 1,467
STAT...consistent gets from ca 929 2,618 1,689
STAT...consistent gets 929 2,618 1,689
LATCH.library cache lock 402 2,353 1,951
STAT...session logical reads 3,551 5,640 2,089
LATCH.library cache pin 637 2,960 2,323
STAT...recursive calls 1,322 4,721 3,399
LATCH.library cache 1,065 5,388 4,323
LATCH.cache buffers chains 10,935 15,610 4,675
LATCH.session allocation 2,603 9,400 6,797
STAT...undo change vector size 253,208 277,864 24,656
STAT...session pga memory max 262,144 196,608 -65,536
STAT...redo size 1,625,728 1,695,580 69,852
STAT...physical read bytes 16,384 114,688 98,304
STAT...physical read total byt 16,384 114,688 98,304
STAT...session uga memory max 254,380 130,928 -123,452
STAT...session uga memory 130,928 0 -130,928
STAT...session pga memory 196,608 0 -196,608
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
21,726 46,090 24,364 47.14%
PL/SQL procedure successfully completed
SQL> |
Partager