What a hard parse...soft parse....latches

Hard parse - hard coded variables
soft parse - .....
latches - protect the data structures in the shared memory of Oracle from concurrent modifications by two sessions

command to clear shared pool
- alter system flush shared_pool;


tkyte@TKYTE816> set timing on
tkyte@TKYTE816> 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
10 ‘select object_name
11 from all_objects
12 where object_id = ‘ i;
13 fetch l_rc into l_dummy;
14 close l_rc;
15 end loop;
16 dbms_output.put_line
17 ( round( (dbms_utility.get_time-l_start)/100, 2 )
18 ‘ seconds...’ );
19 end;
20 /
14.86 seconds...
PL/SQL procedure successfully completed.


tkyte@TKYTE816> 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
10 ‘select object_name
11 from all_objects
12 where object_id = :x’
13 using i;
14 fetch l_rc into l_dummy;
15 close l_rc;
16 end loop;
17 dbms_output.put_line
18 ( round( (dbms_utility.get_time-l_start)/100, 2 )
19 ‘ seconds...’ );
20 end;
21 /
1.27 seconds...
PL/SQL procedure successfully completed.

Using bind variables

select * from emp where empno = :empno;


the query uses a bind variable, :empno, the value of which is supplied at query execution time.
This query is compiled once and then the query plan is stored in a shared pool (the library cache), from which it can be retrieved and reused.

Record should not be update while another program is running

select * from t for update

the for update - lock the record..until a commit or a rollback command

What is TKPROF

Differentiate between variables

create or replace package my_pkg
as
g_variable varchar2(10);
procedure p (p_variable in varchar2) is
l_variable varchar2(10);
begin
null;
end;
end;

the g_variable - can be viewed by the whole package
the p_variable - is a parameter variable
the l_variable - is the local variable

SQL plus enviroment

In order for DBMS_OUTPUT to work, the following command must be issued
SQL > set serveroutput on

The set serveroutput on size 1000000 ..... the set the default buffer size to be as large as possible...

Set trimspool on ensures that, when spooling text, lines will be blank-trimmed and not fixed width...

set long 5000 sets the default number of bytes displayed when selecting LONG and CLOB

set pagesize 9999 sets the pagesize, which controls how frequently sql*plus prints out headings, to big number (we get one set of heading per page).