Expert One-ONE Oracle is the masterpiece of Oracle expert Thomas Kyte, a foreign view, a total of 1329 pages, which is quite expensive. Therefore, let this note, record the essence.
Oracle expert advanced programming learning notes
I. Preparation 1. Establish a basic debug environment Create Demo Schema (establish a demo mode) SQLPlus> Start $ Oracle_Home / Sqlplus / demo / demobld.sql 2. Create login.sql ------------- -------------------- Define_Editor = Viset Serveroutput On Size 1000000Set Trimspool OnSet Long 500Set Linesize 100Set PageSize 9999
Column Plan_plus_exp Format A80COLUMN GLOBAL_NAME New_VALUE GNAME
SELECT LOWER || '@' || Decode (Global_name, 'Webdb.Fanyamin.net', 'WebDB', Global_Name) Global_name from global_name
SET SQLPROMPT '& GNAME>' Set Termout on ----------------------------------------- . --------3 AutoTrace1 arranged in sqlplus) cd $ ORACLE_HOME / rdbms / adminlog into sqlplus as system (connect system / manager @ webdb) run @utlxplanrun create public synonym plan_table for plan_table; run grant all on plan_table to public; 2) cd $ ORACLE_HOME / sqlplus / adminlog into sqlplus as sys (connect sys / change_on_install @ webdb) run @plustrcerun grant plustrace to public;. two contents 1. develop successful Oracle applications with a simple recipe if (possible) Use a single SQL statement to complete the else if (possible) with a PLSQL program else if (possible) with a Java stored process else if (Possible) is used to consider whether it is necessary to do so
Two guidelines 1) Do not use the Binding Variables in the MTS Understand Use Binding Variable System @ WebDB> ALTER System Flush Shared_Pool; System @ WebDB> Set Timing ON to perform these two SQL files, a comparison, discovery use binding After the variable, the speed is more fast - Bindtest1.SQL, DID NOT USE BIND VARIABLE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------- Declare Type RC Is Ref cursor; l_rc rc; l_dummy all_objects.object_name% type; l_start number default dbms_utility.get_time; begin for i in 1..1000 loop open l_rc for '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 ((DBMS_UTILITY.GET_TIME-L_START) / 100, 2) || 'Seconds ...'); end; / - bindtest2.sql, use bind variable ----- -------------------------------------------------- ---- declare type rc is ref cursor; l_rc rc; l_dummy all_objects.object_name% type; l_start number default dbms_utility.get_time; begin for i in 1..1000 loop open l_rc for 'select object_name from all_objects where object_id =: x 'Using i; fetch l_rc INTO L_DUMMY; Close L_Rc; end loop; dbms_outp Ut.put_line ((DBMS_UTILITY.GET_TIME-L_START) / 100, 2) || 'Seconds ...'); end; / oracle blockade policy 1) Only when modified, Oracle locks data at the row level, don't Rising the lock to block or table level 2) Oracle never locks the data for reading, simple reading that cannot be set in the data line 3) The writer does not block the data reader. 4) Only When another data writer has locked a certain row data, it blocks other people to write to the row data. The data reader will never block the writeer of the data Oracle's "non-blocking" method A side effect, if you want to access a row data at a time, in the multi-user environment, you must often use similar techniques for multithreaded program design, select * from resource, name =: room_name for Update, SELECT * AROM_NAME for Update
Oracle's parallel control mechanism (multi-version unanimous reading) consistency query: consistent results in a query result Non-blocking query: Data writer never blocks the query (unreasonable)
