Oracle +9 i & 10g art of programming: in-depth database architecture, reading notes / summary

2010-12-27  来源:本站原创  分类:Database  人气:129 

Abstract:
Successful development of Chapter 1 of Oracle Applications

Bitmap index is only applicable to low base (low-cardinality) value, but the frequently updated list does not apply.
(So-called low cardinality column refers to this column can be only a few values.)
Reasons: the use of bitmap index, a key point to multi-line, may be hundreds or even more, if you update a bitmap index key, then the key point to the hundreds of records will be updated with what you actually are effectively the same as with locked.

If PROCESSED_FLAG out only two values: Y and N, for the record inserted into the table, the column value is N (for untreated). Other processes to read and process the record, they will put the updated column value from N Y. If someone inserts a new record (PROCESSED_FLAG column is N). At this point, you want to read this table and record the process of dealing with N records can not be modified Y recorder. The reason is that in order to update this column from N to Y, to lock the bitmap with a key. In fact, want to insert a new record in this table the other sessions will be blocked, because they also want the bitmap index key lock. Simply put, developers realized that a group structure, which allows only one person up to insert or update!

Example: Use two sessions to demonstrate the occurrence of blocking

SQL> create table t (processed_flag varchar2 (2));
Tbale created.
SQL> create bitmap index t_idx on t (processed_flag);
Index created.
SQL> insert into t value ('N');
1 row created.

In another sql * plus session, execute the following command:
SQL> insert into t values ​​('N');

This statement will "hang" until the first session to issue a commit block location.

=====================================

1.3 database application development right (and wrong) way

1.3.1 Understanding Oracle architecture
SQL Server there is a very common practice that you want to execute each concurrent statement opens a database connection. If you want to Run 5 queries, SQL Server may be seen in the five connections. If Windows is not for multi-threaded multi-process design of the same. In Oracle, whether you want to run 5 or 500 Query query, want to open up a connection. Oracle is the basis of this concept design. Therefore, SQL Server in the normal practice is not advocated in Oracle: you may not want to maintain multiple database connections.

1.3.2 Use bind variables
Oracle will be parsed, compiled SQL connectivity other content stored in the shared pool (shared pool), this is the system global area (System Global Area, SGA) is very important in a shared memory structure. This structure can complete the "smooth" operation, provided that the developer will use to bind the majority of cases. If you really want Oracle to run slowly, or even near-standstill, as long as you do not use bind variables can be done.

Bind variable (bind variable) is a placeholder in the query. For example:

select * from emp where empno = 123;
Or, you can also bind variables: empno is set to 123, and execute the following query:
select * from emp where empno =: empno;

If the amount used in the query directly (constant), then each query is a whole new area of ​​inquiry, seems to have never seen in the database, the query must be parsed, qualified (2 analysis), security check, optimization . Briefly put, is that you perform each different statement compiled at execution time.
The second query uses a bind variable: empno, to provide a variable in the query execution. This query is only compiled once, then it will query plan stored in a shared pool (library cache) for later access and reuse the query plan.

1.3.2 understand the concurrency control
1 to achieve lock
Oracle only when the data plus the modified row-level locking.

(2) to prevent lost updates
select * from resources where resource_name =: room_name FOR UPDATE;
Before the deployment of resources to lock resources (in this room), in other words, the query in the resources table of the resource prior to the scheduled lock resources. By locking the resources to be scheduled, the developer can guarantee that others will not also modify the scheduling of resources. Others must wait until he presented things up. If someone else has locked this line, we will block and wait.

Example:
(1) into sql * plus (Session 1)
SQL> select * from TMP_DEST where DEST_SYS_ID = 'IC' FOR UPDATE;

DEST_SYS_ID SYS_NAME IP_ADDR PORT USR_NAME PASSWD SUBS_MODE TRAN_MODE PUBLISH_MODE UPD_FLAG
----------- ------------ ------------- ---- -------- - -------------- --------- --------------------------- ---------- ------------ --------
IC reporting platform 3 New Line 21.123.22.146 23 dcdsftp 56864857DC705A33 0 com.bocsoft.dcds.dataproces.FTPUpload 1 A

(2), the newly opened window into another sql * plus session (session 2):
SQL> update TMP_DEST set port = '24 'where DEST_SYS_ID =' IC ';

Blinking cursor after the execution, in the waiting (blocked) state.

(3), in the sql * plus session 1 to commit:
SQL> commit;

Commit complete.

(4), 2 sql * plus session screen:

1 row updated.

Chapter 2 Architecture Overview

2.1 Definitions and examples of database

Database (database): the physical operating system files or disk (disk) of the collection.
Instance (instance): a set of Oracle background processes / threads and a shared memory area, which computer memory to run by the same thread / process sharing.

Example is a set of operating system processes (or a multi-threaded process) and some memory. These processes can operate the database; the database is a collection of files (including data files, temporary files, redo logs and control files). At any time, only one instance of a group of related files (associated with a database). Most cases, the opposite is true: only one instance of a database on its operation. However, Oracle Real Application Clusters (Real application clusters, RAC) is an exception, this is an option provided by Oracle, allows multiple cluster environment in computer operation, so that you can load multiple instances of the same time and open a database (in a shared physical disk).

Chapter 6 Lock

Update the table in Oracle will not block on this table check; row-level locks are no overhead.

6.2.2 Pessimistic Pessimistic locking (pessimistic locking) only for a state (stateful) or link (connected) environment, that is, applied to the database you have a continuous connection, but at least things in life which only you a person using this connection.

6.2.3 Optimistic locking Optimistic locking (optimistic locking), which are delayed until all the locks before the update was about to do. Namely, that data is not modified by other users. In this way, the update of the user "failure" will increase the likelihood. This user wants to update his data row that the data has been modified, it must start from scratch.

In the application while retaining the old and new values, and then update the data using the following update statement, which is a popular implementation of optimistic locking:
Update table
set column1 =: new_column1, column2 =: new_column2, ......
where primary_key =: primary_key
and column1 = ld_column1 and column2 = ld_column2

If you update the zero line, indicating that the update failed.

1 Use optimistic locking version of the column to protect the table for each increase of one. This column is NUMBER or generally DATA / TIMESTAMP columns, usually through a line of flip-flop on the table to maintain. Each time you modify, this trigger is responsible for increasing the value of NUMBER columns, or update the DATA / TIMESTAMP / column.
If the application to implement optimistic concurrency control, but just save the value of the additional columns, other columns need to save all the "before" image. Just update the application authentication request that moment, the value of this column in the database and read out the value of the first match. If the two values ​​are equal, it means the line has not been updated.

create table dept
(Deptno number (2),
dname varchar2 (14),
loc varchar2 (13),
last_mod timestamp with time zone
default systimestamp
not null,
constraint dept_pk primary key (deptno)
)

Can not always rely on each application to maintain this field. Suggestions into the stored procedure. Stored procedures can take more than updated to use bind variables as input, perform the same update. 0 rows updated is detected, the stored procedure returns an exception to the customers, let customers know that update failed.
Does not recommend the use of triggers, will introduce a lot of overhead, but such a simple task did not need to use them.

2 uses a checksum of the optimistic locking

3. ORA_ROWSCN optimistic locking using

6.2.5 If a session is holding a blocking lock resources, and another session at the request of the resource, there will be blocking (blocking). In this way, the requested session will be blocked, it will "hang", but to give up holding the session lock resources.
There are five common database DML statements may be blocked: INSERT, UPDATE, DELETE, MERGE, and SELECT FOR UPDATE. For a block of SELECT FOR UPDATE, simply increase the NOWAIT clause, it will not be blocked.

6.2.6 deadlock if there are two sessions, each session of the resources you want to hold another session, this time there will be deadlock (deadlock).

6.3 Lock Types
There are three types of Oracle in the lock:
DML locks: DML data manipulation language on behalf of (Data Manipulation Language). In general, said the SELECT, INSERT, UPDATE, MERGEE and DELETE statements. DML lock mechanism allows concurrent execution of data modifications. For example, DML locks may lock a particular row of data, or lock all rows in the table table-level locks.
DDL locks: DDL data definition language on behalf of (Data Definition Language), such as CREATE and ALTER statements, etc. DDL locks protect the definition of object structure.
Internal locks and latches: ...

Chapter 7 with multi-version concurrency
7.1 What is concurrency control concurrency control (concurrency control) is a database provides a collection of functions that allow multiple people to simultaneously access and modify data.
Oracle Management shared lock is concurrent access to database resources and to prevent concurrent database transactions between the "mutual interference" one of the core mechanisms

7.2 transaction isolation level
ANSI (American National Standards Institute American National Standards Institute) / ISO (International Standardization Organization ISO) SQL standard defines four isolation levels, for the same transaction, using different isolation levels have different results. In other words, even if the same input, but in the same way to accomplish the same work, it may be completely different answers, depending on transaction isolation level in the. These isolation levels are based on three "phenomena" to the definition, what is given isolation level that may allow or not allow the 3 phenomena:

Dirty read (dirty read): read uncommitted data is dirty read. As long as people are reading and writing to open a 0S file, you can achieve the effect of dirty read.
Non-repeatable reads (nonrepeatable read): If you have time to read a row in T1, the T2 re-read this line, this line may have been modified. Perhaps it has disappeared, may be updated, and so on.
Phantom read (phantom read): This shows that if you execute a query in the T1 time, but in the T2 time to execute the query, this time may have added another to the data line, which will affect your results. And the difference is non-repeatable read: In phantom read, has read the data will not change, but compared with the previous, more data will satisfy your query.

ANSI isolation level

Non-repeatable read isolation level dirty phantom read
READ UNCOMMITTED (read uncommitted) Allowed Allowed Allowed
READ COMMITTED (read committed) to allow allow
REPEATABLE READ (repeatable read) to allow
SERIALIZABLE (serializable)

相关文章
  • Oracle +9 i & 10g art of programming: in-depth database architecture, reading notes / summary 2010-12-27

    Abstract: Successful development of Chapter 1 of Oracle Applications Bitmap index is only applicable to low base (low-cardinality) value, but the frequently updated list does not apply. (So-called low cardinality column refers to this column can be o

  • Oracle +9 i & 10g art of programming: in-depth study notes database architecture / Abstract 2010-12-27

    Abstract: Chapter 1 of Oracle applications developed Bitmap index is only applicable to low base (low-cardinality) value, but frequently updated column does not apply. (So-called low-cardinality column refers to the value of the column can be only a

  • Oracle9i & 10g art of programming: in-depth database architecture. Pdf 2010-11-22

    Directory Chapter 1 Successful development of the Oracle Applications 1.1 My method 1.2 Black box method 1.3 The correct development of database applications ( And incorrect ) Methods 1.4 Summary Chapter 2 Architecture Overview 2.1 Define database an

  • C + + Programming Principles and Practice of Reading Notes Hello World 2010-08-29

    #include "std_lib_facilities.h" int main() { cout<<"Hellom World!\n"; return 0; } I use to compile vc6.0, the results of error ompiling ... hello.cpp d: \ c + + \ ws \ cpphello \ hello.cpp (1): fatal error C1083: Cannot open incl

  • Oracle Database 9i/10g/11g art of programming: in-depth database architecture: 2 2011-01-12

    Interactive Network in China New china-pub: Oracle Database 9i/10g/11g art of programming: in-depth database architecture : 2 Oracle prestigious classical masterpiece of the world's top experts, Thomas Kyte Ask Tom! Solve all your Oracle incurable di

  • Oracle Database 9i/10g/11g art of programming: in-depth database Architecture: 2nd Edition 2011-01-12

    China Interactive Network china-pub New: Oracle Database 9i/10g/11g art of programming: in-depth database architecture : 2nd Edition Prestigious of the world's top Oracle experts, Thomas Kyte classic masterpiece Ask Tom! Solve all your Oracle incurab

  • Oracle 9i10g art of programming 2010-12-08

    Oracle 9i10g art of programming

  • Book: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions 2011-09-17

    Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions

  • (Strongly recommended) Oracle Concepts Chinese (10g R2 Bilingual version) 2010-12-07

    Part I What Is Oracle? What is the first part of Oracle? Chapter 1, Introduction to the Oracle Database Chapter Chapter 1, Oracle Database Introduction Part II Oracle Database Architecture The second part of the Oracle database architecture Chapter 2

  • javascript DOM programming arts reading notes 2010-09-06

    javascript DOM programming arts reading notes The first part of the directory Chapter 1 Origin of JavaScript 1.1 JavaScript 1.2 A Brief History of the browser war 1.3 standard 1.4 Summary Chapter 2, JavaScript 2.2 Grammar 2.1 Grammar 2.3 statement pr

  • J2ME programming knowledge if they had to switch summary screen 2010-12-24

    J2ME programming knowledge if they had to switch summary screen Java Studio Chen Yuefeng m From: http://blog.csdn.net/mailbomb Note: Please indicate the source reproduced As mobile phones become increasingly rich features and support if they had to s

  • Extreme Programming reading notes 2 - Extreme Programming principles 2010-02-18

    Extreme Programming principle - I think most of them are universal principles of software development Today twelfth lunar month 28, the family was busy cooking, he is not much help. Books, and enrich my knowledge is also a good choice. Today, learn a

  • Understanding of Oracle database architecture 2010-09-06

    --------- -------------------------------------- Oracle architecture --------------- 1. what Object-relational database management system (ORDBMS) 2. why Supports multiple users, large transaction volumes, transaction security and data integrity cont

  • [Transfer] oracle database architecture (overview) 2011-05-08

    [Switch] oracle database architecture (overview) Original Address: http://blog.tianya.cn/blogger/post_show.asp?BlogID=1271361&PostID=14905875 As shown above, ORACLE database there are different types of processes, memory structures and file types. A

  • Oracle 8i.9i.10g.11g between the various versions of the new features of relatively 2010-07-01

    Oracle 9i 8i more than what new features? In brief: 9i easier to manage. Detail: Concurrent cluster, 8i OPS upgraded to 9i RAC, 8i between nodes to exchange information with the hard drive, 9i node network cable between the high-speed cache fusion (C

  • Oracle 8i.9i.10g.11g compare different versions of the new features 2010-02-20

    Oracle 9i 8i more than what new features? In brief: 9i easier to manage. Detail: Concurrent cluster, 8i OPS upgraded to 9i RAC, 8i between nodes to exchange information with the hard drive, 9i node network cable between the high-speed cache fusion (C

  • oracle regular expressions (10G can) 2011-06-01

    oracle regular expression (regular expression) briefly present, the regular expression has a lot of software is widely used, including * nix (Linux, Unix, etc.), HP and other operating systems, PHP, C #, Java and other development environment. Oracle

  • oracle sum of ten ---PL/SQL Programming 2011-04-18

    PL / SQL Programming PL / SQL is the oracle of the special language, its standard SQL language extension. SQL statements can be nested in a PL / SQL language, and combined treatment statement. PL / SQL program using the block structure of the structu

  • oracle 9i and 10g coexistence 2011-06-24

    Today whim, in the original computer installed oracle 9I installed directly on the 10G, the time of installation and smooth all the way, did not encounter any problems. After a successful installation using the pl / sql to connect some of the problem

  • 如何升级Oracle 9i到10g 2013-12-22

    从9i升级到10g有两个方案可供选择: 1.利用oracle提供的一个升级实用程序dbua(database upgrade assistant)直接将9i的数据库升级到10g. 2.新建一个10g的数据库,利用oracle的导入/导出(imp/exp)实用程序迁移数据 1.首先来讲第一种方案(用升级程序:dbua): 适合: 这种方案适宜于数据量比较大,用导入导出工具来迁移可能要花费很长时间的数据库. 方法: 用这种方案的话我们要在原来的数据库的机器上安装10g的软件,不安装10g的数据库.安