新托福阅读的词汇与背景知识同样重程序讲述.ppt

? Dennis Shasha, Philippe Bonnet 2001 Locking in Oracle 8i Data page row Interested transaction list (fixed array - INITRANS – MAXTRANS) T1 T1 lock Enqueue resource structure (fixed array – default 4 entries per transaction) T1 Enqueued locks array T2 lock Enqueue wait (time out ~ 3sec) Process T3 lock Deadlock detection H ? Dennis Shasha, Philippe Bonnet 2001 Locking Overhead -- data Settings: accounts( number, branchnum, balance); create clustered index c on accounts(number); 100000 rows Cold buffer SQL Server 7, DB2 v7.1 and Oracle 8i on Windows 2000 No lock escalation on Oracle; Parameter set so that there is no lock escalation on DB2; no control on SQL Server. Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000. ? Dennis Shasha, Philippe Bonnet 2001 Locking Overhead -- transactions No Concurrent Transactions: Update [10 000 updates]update accounts set balance = Val; Insert [10 000 inserts], e.g. typical one:insert into accounts values(664366,72255,2296.12); 2 - Tuning the Guts ? Dennis Shasha, Philippe Bonnet 2001 * Locking Overhead Row locking is barely more expensive than table locking because recovery overhead is higher than locking overhead Exception is updates on DB2 where table locking is distinctly less expensive than row locking. (DB2 use logical logging, which logs the operation that caused change instead of image of changed data) Data definition language (DDL) statementsare considered harmful DDL is the language used to access and manipulate catalog or metadata The catalog can easily become a hot spot and therefore a bottleneck The lesson is: avoid updates to the system catalog during heavy system activity, especially if you are using dynamic SQL ? Dennis Shasha, Philippe Bonnet 2001 Think about partitioning Problem: insert a collection records into history file or security file, or log file Time dependent insertion Last page of the file may become a concurrency bott