Show Parameter Modes

show parameter

SQL> show parameter user_dump_Dest
NAME TYPE VALUE
--------------------- -------------------------------- -------
user_dump_dest string f:\app\stelios\diag\rdbms\snc1\snc1\trace
SQL> host dir f:\app\stelios\diag\rdbms\snc1\snc1\trace\*DIAG*.trc
Volume in drive F is My Passport
Volume Serial Number is 1E65-69CC
Directory of f:\app\stelios\diag\rdbms\snc1\snc1\trace

09/15/2012 12:41 PM 1,096 snc1_diag_2548.trc
10/05/2012 12:54 PM 68,133 snc1_ora_4980_DIAG.trc
2 File(s) 69,229 bytes
0 Dir(s) 788,285,857,792 bytes free
SQL> host notepad f:\app\stelios\diag\rdbms\snc1\snc1\trace\snc1_ora_4980_DIAG.trc

SQL> host type area.sql
create or replace package getcircarea as
function getcircarea(radius number)
return number;
end getcircarea;
/
create or replace package body getcircarea as
function getcircarea (radius number) return number
is area number(8,2);
begin
select 3.142*radius*radius into area from dual;
return area;
end;
end getcircarea;
/
set serveroutput on size 100000;
declare
area number(8,2);
begin
area:= getcircarea.getcircarea(10);
dbms_output.put_line('Area is '||area);
end;
/

SQL> select sql_text, sql_id from v$sqlarea where sql_text like '%3.142%';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
SELECT 3.142*:B1 *:B1 FROM DUAL
9rjmrhbjuasav
select sql_text, sql_id from v$sqlarea where sql_text like '%3.142%'
ggux6y542z8mr

alter session set tracefile_identifier='PLSQL';
alter session set events 'trace[rdbms.SQL_Optimizer.*][sql:9rjmrhbjuasav]';
SQL> @area
Package created.
Package body created.
Area is 314.2
PL/SQL procedure successfully completed.
Session altered.

Understanding the Rule-Based Optimizer