SELECT name, value
FROM v$parameter
WHERE name ='plsql_code_type' ;
drop procedure p1;
create or replace procedure p1
is
begin
dbms_output.put_line('P1');
end;
select * from user_plsql_object_settings
where name ='P1';
ALTER SESSION SET plsql_code_type=native; --you can do alter system, but this for DBA
--still the P1 =INTERPRETED
select * from user_plsql_object_settings
where name ='P1'
--so we should compile again
create or replace procedure p1
is
begin
dbms_output.put_line('P1');
end;
select * from user_plsql_object_settings
where name ='P1'
---------------------------------------------------------------
--now let us see the perf.
ALTER SESSION SET plsql_code_type=INTERPRETED;
create or replace procedure p1
is
n number:=0;
begin
for i in 1..500000000
loop
n:=n+1;
end loop;
end;
exec p1
alter procedure p1 compile plsql_code_type=native;
exec p1
---------------------------------------------------------------------------------------------------------------
SELECT name, value
FROM v$parameter
WHERE name ='plsql_optimize_level' ;
alter session set PLSQL_OPTIMIZE_LEVEL = 0;
SELECT name, value
FROM v$parameter
WHERE name ='plsql_optimize_level' ;
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'hh:mi:ss'));
FOR I IN 1..100000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'hh:mi:ss'));
END;
----------------
alter session set PLSQL_OPTIMIZE_LEVEL = 2;
SELECT name, value
FROM v$parameter
WHERE name ='plsql_optimize_level' ;
DECLARE
A NUMBER;
B NUMBER;
C NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'hh:mi:ss'));
FOR I IN 1..100000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'hh:mi:ss'));
END;
----------------------------------------------------------------------------------------------------------
--to see the curr value of plsql_warnings
--1 v$parameter
select name,value
from v$parameter
where name='plsql_warnings'
--2 dbms_warning.get_warning_setting_string()
declare
s varchar2(100);
begin
s:= dbms_warning.get_warning_setting_string();
dbms_output.put_line(s);
end;
--now the status is plsql_warnings DISABLE:ALL
--let us do this
--it will compile without any warning
drop PROCEDURE P11;
CREATE OR REPLACE PROCEDURE P11
(p OUT VARCHAR2)
IS
BEGIN
p := 'test';
END;
select * from user_plsql_object_settings
where name ='P11'
--now see the warning
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE P11
(p OUT VARCHAR2)
IS
BEGIN
p := 'test';
END;
select * from user_errors
where name='P11'
select * from user_plsql_object_settings
where name ='P11'
alter PROCEDURE P11 compile PLSQL_WARNINGS='DISABLE:ALL';
select * from user_plsql_object_settings
where name ='P11'
--------------------------------------------------------------------------------------------------------------
select name,value
from v$parameter
where name='plsql_warnings';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
create or replace function test_f
return number
is
v number:=0;
begin
return v;
v:=v+1; --THIS CODE WILL NEVER BE EXECUTED
end;
--now when you do this, this mean SEVERE&PERFORMANCE also will be disabled
--because oracle understand that you need to do new setting, and disable is the default
--so SEVERE&PERFORMANCE also will be disabled
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL';
select name,value
from v$parameter
where name='plsql_warnings';
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL, ENABLE:SEVERE,ENABLE:PERFORMANCE';
select name,value
from v$parameter
where name='plsql_warnings';
create or replace function test_f
return number
is
v number:=0;
begin
return v;
v:=v+1;
end;
------
--Warning(1,1): PLW-05018
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL,
ENABLE:SEVERE,ENABLE:PERFORMANCE,
ERROR:05018';
select name,value
from v$parameter
where name='plsql_warnings';
create or replace function test_f
return number
is
v number:=0;
begin
return v;
v:=v+1;
end;
--------------------------------------------------------------------------------------------------------------------------
select name,value
from v$parameter
where name='plsql_warnings'
--1--
--Modify the current session's warning settings
/*
dbms_warning.add_warning_setting_cat(
warning_category IN VARCHAR2,
warning_value IN VARCHAR2,
scope IN VARCHAR2)
*/
--now we can modify the setting
begin
dbms_warning.add_warning_setting_cat('SEVERE', 'ENABLE', 'SESSION');
end;
--we can check the new value by
select name,value
from v$parameter
where name='plsql_warnings'
--or
SELECT dbms_warning.get_warning_setting_string FROM dual;
--------------------
--2
/*
dbms_warning.add_warning_setting_num(
warning_number IN PLS_INTEGER,
warning_value IN VARCHAR2,
scope IN VARCHAR2);
*/
EXEC dbms_warning.add_warning_setting_num(6002, 'DISABLE', 'SESSION');
SELECT dbms_warning.get_warning_setting_num(6002)
FROM dual;
EXEC dbms_warning.add_warning_setting_num(6002, 'ENABLE', 'SESSION');
SELECT dbms_warning.get_warning_setting_num(6002)
FROM dual;
-----------------------------
--3
/*
dbms_warning.set_warning_setting_string(
value IN VARCHAR2,
scope IN VARCHAR2);
*/
exec dbms_warning.set_warning_setting_string('ENABLE:ALL', 'SESSION');
SELECT dbms_warning.get_warning_setting_string
FROM dual;
--------------------
--4
/*
dbms_warning.get_category(warning_number IN PLS_INTEGER)
RETURN VARCHAR2
*/
-- severe
SELECT dbms_warning.get_category(5000)
FROM dual;
-- informational
SELECT dbms_warning.get_category(6002)
FROM dual;
-- performance
SELECT dbms_warning.get_category(7203)
FROM dual;
------------------------------------------
--5
/*
dbms_warning.get_warning_setting_cat(warning_category IN VARCHAR2)
RETURN VARCHAR2
*/
SELECT dbms_warning.get_warning_setting_cat('SEVERE')
FROM dual;
SELECT dbms_warning.get_warning_setting_cat('INFORMATIONAL')
FROM dual;
SELECT dbms_warning.get_warning_setting_cat('PERFORMANCE')
FROM dual;
------------
--6
--dbms_warning.get_warning_setting_num(warning_number IN PLS_INTEGER)
--RETURN VARCHAR2
SELECT dbms_warning.get_warning_setting_num(5000)
FROM dual;
SELECT dbms_warning.get_warning_setting_num(6002)
FROM dual;
SELECT dbms_warning.get_warning_setting_num(7203)
FROM dual;
No comments:
Post a Comment