Oracle PL/SQL/System Packages/dbms utility
Содержание
- 1 Assign dbms_utility.get_time to integer variable
- 2 Call dbms_utility.get_time twice to time a statement in PL SQL
- 3 DBMS_UTILITY.analyze_schema
- 4 dbms_utility.format_error_stack
- 5 dbms_utility.get_parameter_value
- 6 demonstrates the use of DBMS_UTILITY.TABLE_TO_COMMA and DBMS_UTILITY.COMMA_TO_TABLE.
- 7 Performace difference between simple parameter and collection parameter
- 8 round dbms_utility.get_time
- 9 This script demonstrates DBMS_UTILITY.NAME_TOKENIZE.
- 10 time to commit
- 11 Timing Package function call
- 12 Timing Per Thousand Records Processed (in secs)
- 13 use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger.
- 14 Use dbms_utility.get_time to do performace check
- 15 Use dbms_utility to time
Assign dbms_utility.get_time to integer variable
SQL>
SQL> create or replace procedure num_test_as_integer is
2 x integer;
3 t number := dbms_utility.get_time;
4 begin
5 for i in 1 .. 1000 loop
6 x := i;
7 end loop;
8 dbms_output.put_line((dbms_utility.get_time-t)||"cs");
9 end;
10 /
Procedure created.
SQL>
Call dbms_utility.get_time twice to time a statement in PL SQL
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 l_start number default dbms_utility.get_time;
3 begin
4 for i in 1 .. 1000
5 loop
6 insert into t values ( 1 );
7 end loop;
8 commit;
9 dbms_output.put_line( dbms_utility.get_time-l_start || " hsecs" );
10 end;
11 /
21 hsecs
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL> --
DBMS_UTILITY.analyze_schema
SQL>
SQL> CREATE OR REPLACE PROCEDURE analyze_oem
2 IS
3 CURSOR curs_get_hour IS SELECT TO_CHAR (SYSDATE, "HH24") FROM dual;
4 v_current_hour NUMBER (2);
5 v_method_opt VARCHAR2 (15);
6 BEGIN
7 OPEN curs_get_hour;
8 FETCH curs_get_hour INTO v_current_hour;
9 CLOSE curs_get_hour;
10
11 IF v_current_hour IN (1, 2)
12 THEN
13 v_method_opt := NULL;
14 ELSE
15 v_method_opt := "FOR ALL INDEXES";
16 END IF;
17
18 DBMS_UTILITY.analyze_schema (
19 schema => "OEM",
20 method => "COMPUTE",
21 method_opt => v_method_opt
22 );
23 END analyze_oem;
24 /
Procedure created.
dbms_utility.format_error_stack
SQL> DECLARE
2 myException EXCEPTION;
3 FUNCTION innerFunction
4 RETURN BOOLEAN IS
5 retval BOOLEAN := FALSE;
6 BEGIN
7 RAISE myException;
8 RETURN retval;
9 END;
10 BEGIN
11 IF innerFunction THEN
12 dbms_output.put_line("No raised exception");
13 END IF;
14 EXCEPTION
15 WHEN others THEN
16 dbms_output.put_line("DBMS_UTILITY.FORMAT_ERROR_STACK");
17 dbms_output.put_line(dbms_utility.format_error_stack);
18
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
dbms_utility.get_parameter_value
SQL>
SQL> declare
2 l_intval number;
3 l_strval varchar2(2000);
4 l_type number;
5 begin
6 l_type := dbms_utility.get_parameter_value("user_dump_dest", l_intval, l_strval);
7 dbms_output.put_line(l_strval );
8 end;
9 /
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP
PL/SQL procedure successfully completed.
SQL>
SQL>
demonstrates the use of DBMS_UTILITY.TABLE_TO_COMMA and DBMS_UTILITY.COMMA_TO_TABLE.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_InitialString VARCHAR2(100) := "Alpha, Bravo, Charlie, Delta, Epsilon, Frank, George";
3 v_Table DBMS_UTILITY.UNCL_ARRAY;
4 v_FinalString VARCHAR2(100);
5 v_TabLen BINARY_INTEGER;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE("Initial string: " || v_InitialString);
8 DBMS_UTILITY.ruMA_TO_TABLE(v_InitialString, v_TabLen, v_Table);
9 DBMS_OUTPUT.PUT_LINE("Table (length = " || v_TabLen || "):");
10 FOR v_Count IN 1..v_Table.COUNT LOOP
11 DBMS_OUTPUT.PUT_LINE(v_Table(v_Count));
12 END LOOP;
13 DBMS_UTILITY.TABLE_TO_COMMA(v_Table, v_TabLen, v_FinalString);
14 DBMS_OUTPUT.PUT_LINE("Final string (length = " || v_TabLen || "):");
15 DBMS_OUTPUT.PUT_LINE(v_FinalString);
16 END;
17 /
Initial string: Alpha, Bravo, Charlie, Delta, Epsilon, Frank, George
Table (length = 7):
Alpha
Bravo
Charlie
Delta
Epsilon
Frank
George
Final string (length = 7):
Alpha, Bravo, Charlie, Delta, Epsilon, Frank, George
PL/SQL procedure successfully completed.
SQL>
Performace difference between simple parameter and collection parameter
SQL> create or replace procedure SIMPLE_PARM(p number) is
2 x number;
3 begin
4 null;
5 end;
6 /
Procedure created.
SQL>
SQL> create or replace type rec is object
2 ( a number,
3 b number,
4 c varchar2(30));
5 /
SQL>
SQL> create or replace type rec_list is table of rec;
2 /
Type created.
SQL>
SQL> create or replace procedure big_parm(p Rec_list) is
2 x number;
3 begin
4 null;
5 end;
6 /
Procedure created.
SQL>
SQL>
SQL>
SQL> declare
2 x rec_list := rec_list();
3 t1 number;
4 t2 number;
5 begin
6 x.extend(50000);
7 for i in 1 .. 50000 loop
8 x(i) := rec(i,i,rpad(i,30));
9 end loop;
10 t1 := dbms_utility.get_time;
11 for i in 1 .. 500000 loop
12 simple_parm(i);
13 end loop;
14 t2 := dbms_utility.get_time;
15 dbms_output.put_line("Simple: "||(t2-t1));
16 for i in 1 .. 500000 loop
17 big_parm(x);
18 end loop;
19 t1 := dbms_utility.get_time;
20 dbms_output.put_line("Collection:"||(t1-t2));
21 end;
22 /
Simple: 19
Collection:15
PL/SQL procedure successfully completed.
round dbms_utility.get_time
SQL>
SQL> declare l_tmp char(1);
2 l_start number := dbms_utility.get_time;
3 begin
4 select * into l_tmp from dual;
5 dbms_output.put_line( round( (dbms_utility.get_time-l_start)/100, 2 )||" seconds" );
6 end;
7 /
0 seconds
PL/SQL procedure successfully completed.
SQL>
SQL>
This script demonstrates DBMS_UTILITY.NAME_TOKENIZE.
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE Tokenize(name IN VARCHAR2) AS
2 v_A VARCHAR2(256);
3 v_B VARCHAR2(256);
4 v_C VARCHAR2(256);
5 v_DBLink VARCHAR2(256);
6 v_NextPos BINARY_INTEGER;
7 BEGIN
8 DBMS_UTILITY.NAME_TOKENIZE(name, v_A, v_B, v_C, v_DBLink,
9 v_NextPos);
10 DBMS_OUTPUT.PUT_LINE("a: " || v_A);
11 DBMS_OUTPUT.PUT_LINE("b: " || v_B);
12 DBMS_OUTPUT.PUT_LINE("c: " || v_C);
13 DBMS_OUTPUT.PUT_LINE("db link: " || v_DBLink);
14 DBMS_OUTPUT.PUT_LINE("next pos: " || v_NextPos);
15 END Tokenize;
16 /
Procedure created.
SQL>
SQL> exec Tokenize("example.lecturer");
a: EXAMPLE
b: LECTURER
c:
db link:
next pos: 16
PL/SQL procedure successfully completed.
time to commit
SQL>
SQL> set echo on
SQL>
SQL> create table t
2 as
3 select * from all_objects;
Table created.
SQL>
SQL> insert into t select * from t;
12210 rows created.
SQL> insert into t select * from t;
24420 rows created.
SQL> insert into t select * from t where rownum < 12000;
11999 rows created.
SQL> insert into t select * from t;
60839 rows created.
SQL> insert into t select * from t;
121678 rows created.
SQL> insert into t select * from t;
243356 rows created.
SQL> insert into t select * from t;
486712 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create or replace procedure do_commit( p_rows in number )
2 as
3 l_start number;
4 l_after_redo number;
5 l_before_redo number;
6 begin
7 l_start := dbms_utility.get_time;
8 insert into t select * from t where rownum < p_rows;
9 dbms_output.put_line( sql%rowcount || " rows created" );
10 dbms_output.put_line ( "Time to INSERT: " ||to_char( round( (dbms_utility.get_time-l_start)/100, 5 ), "999.99") || " seconds" );
11
12 l_start := dbms_utility.get_time;
13 commit;
14 dbms_output.put_line( "Time to COMMIT: " ||to_char( round( (dbms_utility.get_time-l_start)/100, 5 ), "999.99") || " seconds" );
15
16 dbms_output.put_line( "Generated " || to_char(l_after_redo-l_before_redo,"999,999,999,999") || " bytes of redo" );
17 dbms_output.new_line;
18 end;
19 /
Procedure created.
SQL> show error
No errors.
SQL>
SQL> set serveroutput on format wrapped
SQL> begin
2 for i in 1 .. 5
3 loop
4 do_commit( power(10,i) );
5 end loop;
6 end;
7 /
9 rows created
Time to INSERT: .06 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
99 rows created
Time to INSERT: .00 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
999 rows created
Time to INSERT: .02 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
9999 rows created
Time to INSERT: .31 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
99999 rows created
Time to INSERT: .80 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
Timing Package function call
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE CopyFast AS
2 TYPE StudentArray IS
3 TABLE OF lecturer%ROWTYPE;
4
5 PROCEDURE Passlecturer1(p_Parameter IN StudentArray);
6 PROCEDURE Passlecturer2(p_Parameter IN OUT StudentArray);
7 PROCEDURE Passlecturer3(p_Parameter IN OUT NOCOPY StudentArray);
8
9 PROCEDURE Go;
10 END CopyFast;
11 /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY CopyFast AS
2 PROCEDURE Passlecturer1(p_Parameter IN StudentArray) IS
3 BEGIN
4 NULL;
5 END Passlecturer1;
6
7 PROCEDURE Passlecturer2(p_Parameter IN OUT StudentArray) IS
8 BEGIN
9 NULL;
10 END Passlecturer2;
11
12 PROCEDURE Passlecturer3(p_Parameter IN OUT NOCOPY StudentArray) IS
13 BEGIN
14 NULL;
15 END Passlecturer3;
16
17 PROCEDURE Go IS
18 myLecturerArray StudentArray := StudentArray(NULL);
19 myLecturerRec lecturer%ROWTYPE;
20 v_Time1 NUMBER;
21 v_Time2 NUMBER;
22 v_Time3 NUMBER;
23 v_Time4 NUMBER;
24 BEGIN
25 SELECT *
26 INTO myLecturerArray(1)
27 FROM lecturer
28 WHERE ID = 10007;
29 myLecturerArray.EXTEND(50000, 1);
30
31 v_Time1 := DBMS_UTILITY.GET_TIME;
32 Passlecturer1(myLecturerArray);
33 v_Time2 := DBMS_UTILITY.GET_TIME;
34 Passlecturer2(myLecturerArray);
35 v_Time3 := DBMS_UTILITY.GET_TIME;
36 Passlecturer3(myLecturerArray);
37 v_Time4 := DBMS_UTILITY.GET_TIME;
38
39 DBMS_OUTPUT.PUT_LINE("Time to pass IN: " || TO_CHAR((v_Time2 - v_Time1) / 100));
40 DBMS_OUTPUT.PUT_LINE("Time to pass IN OUT: " || TO_CHAR((v_Time3 - v_Time2) / 100));
41 DBMS_OUTPUT.PUT_LINE("Time to pass IN OUT NOCOPY: " || TO_CHAR((v_Time4 - v_Time3) / 100));
42 END Go;
43 END CopyFast;
44 /
Package body created.
SQL> show errors
No errors.
SQL>
SQL> BEGIN
2 CopyFast.Go;
3 END;
4 /
Time to pass IN: 0
Time to pass IN OUT: .19
Time to pass IN OUT NOCOPY: 0
PL/SQL procedure successfully completed.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
Timing Per Thousand Records Processed (in secs)
SQL>
SQL>
SQL> DECLARE
2 lv_counter_num PLS_INTEGER := 0;
3 lv_timer_start_num NUMBER;
4 lv_timer_previous_num NUMBER;
5 lv_timer_current_num NUMBER;
6 BEGIN
7 lv_timer_start_num := DBMS_UTILITY.GET_TIME;
8 lv_timer_previous_num := lv_timer_start_num;
9 LOOP
10 lv_counter_num := lv_counter_num + 1;
11 IF MOD(lv_counter_num, 1000) = 0 THEN
12 lv_timer_current_num := DBMS_UTILITY.GET_TIME;
13 DBMS_OUTPUT.PUT_LINE("Time Elapsed-Total: " ||
14 (lv_timer_current_num - lv_timer_start_num)/100 ||
15 CHR(9) || " This Set: " ||
16 (lv_timer_current_num - lv_timer_previous_num)/100 ||
17 CHR(9) || " Records Processed: " ||
18 lv_counter_num);
19 lv_timer_previous_num := lv_timer_current_num;
20 END IF;
21 EXIT WHEN lv_counter_num = 5540;
22 END LOOP;
23 lv_timer_current_num := DBMS_UTILITY.GET_TIME;
24 DBMS_OUTPUT.PUT_LINE("Time Elapsed-Total: ");
25 DBMS_OUTPUT.PUT_LINE(lv_timer_current_num - lv_timer_start_num );
26 DBMS_OUTPUT.PUT_LINE(" This Set: " );
27 DBMS_OUTPUT.PUT_LINE(lv_timer_current_num - lv_timer_previous_num);
28 DBMS_OUTPUT.PUT_LINE(" Records Processed: " ||lv_counter_num);
29 END;
30 /
Time Elapsed-Total: 0 This Set: 0 Records Processed: 1000
Time Elapsed-Total: 0 This Set: 0 Records Processed: 2000
Time Elapsed-Total: 0 This Set: 0 Records Processed: 3000
Time Elapsed-Total: .01 This Set: .01 Records Processed: 4000
Time Elapsed-Total: .01 This Set: 0 Records Processed: 5000
Time Elapsed-Total:
1
This Set:
0
Records Processed: 5540
PL/SQL procedure successfully completed.
SQL>
use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger.
SQL>
SQL>
SQL> CREATE TABLE error_log (
2 timestamp DATE,
3 username VARCHAR2(30),
4 instance NUMBER,
5 database_name VARCHAR2(50),
6 error_stack VARCHAR2(2000)
7 );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER LogErrors
2 AFTER SERVERERROR ON DATABASE
3 BEGIN
4 INSERT INTO error_log
5 VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.
6 DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
7 END LogErrors;
8 /
Trigger created.
SQL>
SQL> SELECT * FROM non_existent_table;
SELECT * FROM non_existent_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> BEGIN
2 INSERT INTO non_existent_table VALUES ("Hello!");
3 END;
4 /
INSERT INTO non_existent_table VALUES ("Hello!");
*
ERROR at line 2:
ORA-06550: line 2, column 15:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
SQL> COLUMN error_stack FORMAT a61 WRAPPED
SQL> SET LINE 80
SQL>
SQL> SELECT * FROM error_log;
TIMESTAMP USERNAME INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
18-JUN-08 sqle 1
XE
ORA-00942: table or view does not exist
18-JUN-08 sqle 1
XE
ORA-06550: line 2, column 15:
TIMESTAMP USERNAME INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
SQL>
SQL> DROP TABLE error_log;
Table dropped.
SQL>
SQL> drop trigger LOGERRORS;
Trigger dropped.
SQL>
Use dbms_utility.get_time to do performace check
SQL>
SQL>
SQL>
SQL> declare
2 type charListType is table of number index by varchar2(100);
3 vc charListType;
4 type numberListType is table of number index by binary_integer;
5 n numberListType;
6 t number;
7 begin
8 t := dbms_utility.get_time;
9 for i in 1 .. 1000 loop
10 n(i) := i;
11 end loop;
12 dbms_output.put_line("Index by Number : "|| (dbms_utility.get_time-t));
13 t := dbms_utility.get_time;
14 for i in 1 .. 1000 loop
15 vc(i) := i;
16 end loop;
17 dbms_output.put_line("Index by Varchar2: "|| (dbms_utility.get_time-t));
18 end;
19 /
Index by Number : 0
Index by Varchar2: 0
PL/SQL procedure successfully completed.
SQL>
Use dbms_utility to time
SQL> set echo on
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> set timing on
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for "select object_name from all_objects where object_id = " || i;
10 fetch l_rc into l_dummy;
11 close l_rc;
12 end loop;
13 dbms_output.put_line( round( (dbms_utility.get_time-l_start)/100, 2 ) || " seconds..." );
14 end;
15 /
37.76 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:38.10
SQL> set timing off
SQL>
SQL> --