Oracle expert Advanced Programming Study Notes (1)

Expert one-on-one Oracle is a masterpiece of Oracle expert Thomas Kyte. It has a total of 1329 pages, so it takes a lot of effort to take this note and record the essence.

Oracle expert advanced programming learning notes

I. Preparations
1. Create a basic debugging environment
Create demo Schema (create Demo mode)
Sqlplus> Start $ ORACLE_HOME/sqlplus/demo/demobld. SQL
 
2. Create login. SQL
---------------------------------
Define_editor = vi
Set serveroutput on size 1000000
Set trimspool on
Sets long 500
Set linesize 100
Set pagesize 9999

Column plan_plus_exp format a80
Column global_name new_value gname

Select lower (User) | '@' | decode (global_name, 'webdb. fanyamin. net', 'webdb', global_name) global_name from global_name;

Set sqlprompt '& gname>'
Set termout on
-------------------------------------------------
3. Configure autotrace in sqlplus
1) CD $ ORACLE_HOME/rdbms/admin
Log into sqlplus as system (connect system/manager @ webdb)
Run @ utlxplan
Run create public synonym plan_table for plan_table;
Run grant all on plan_table to public;
2)
CD $ ORACLE_HOME/sqlplus/admin
Log into sqlplus as sys (connect sys/change_on_install @ webdb)
Run @ plustrce
Run grant plustrace to public;

Ii. Content
1. Developed Oracle Applications
A simple secret
If (possible) is completed with a single SQL statement.
Else if (possible) with PLSQL Program
Else if (possible) uses Java stored procedure
Else if (possible) uses C external process
Else considers whether it is really necessary to do so.

Two guidelines
1) do not run long transactions under MTS <45 s
2) bind a variable
System @ webdb> alter system flush shared_pool;
System @ webdb> set timing on
Execute these two SQL files. After a comparison, it is found that the variable binding speed is much faster.
-- Bindtest1. SQL, did not use Bind Variable
-----------------------------------------------------------------------------
Declare
Type RC is ref cursor;
Rochelle RC;
Rochelle dummy all_objects.object_name % type;
Rochelle start number default dbms_utility.get_time;
Begin
For I in 1000
Loop
Open l_rc
'Select object_name from all_objects where object_id = '| I;
Fetch l_rc into l_dummy;
Close l_rc;
End loop;
Dbms_output.put_line (round (dbms_utility.get_time-l_start)/, 2) | 'Seconds ...');
End;
/
-- Bindtest2. SQL, use Bind Variable
-----------------------------------------------------------
Declare
Type RC is ref cursor;
Rochelle RC;
Rochelle dummy all_objects.object_name % type;
Rochelle start number default dbms_utility.get_time;
Begin
For I in 1000
Loop
Open l_rc
'Select object_name from all_objects where object_id =: x'
Using I;
Fetch l_rc into l_dummy;
Close l_rc;
End loop;
Dbms_output.put_line (round (dbms_utility.get_time-l_start)/, 2) | 'Seconds ...');
End;
/
Oracle Blocking Policy
1) Oracle locks data at the row level only when the data is modified. Do not upgrade the lock to the block level or table level.
2) Oracle will never lock data for reading. For simple reading, you cannot set a lock on the Data row.
3) The data writer does not block the data reader.
4) only when the other data writer has locked a row of data will the others be blocked from writing the row of data. The data reader will never block the data writer.

The "no blocking" method of Oracle uses one side effect. If you want only one user to access a row of data at a time, you must frequently use the similar techniques used in multi-threaded programming in a multi-user environment,
Select * from resources where resource-name =: room_name for update

Parallel Control Mechanism of Oracle (Multi-version consistent read)
Consistent query: the query produces consistent results at a specific time point.
Non-blocking query: The data writer never blocks the query.
(To be continued)