DBST 651 DBST 651 Session 13 part 2 of 2 McGraw Hill Irwin Copyright 2007 by The McGraw Hill Companies Inc All rights reserved Chapter 17 Client Server Processing Parallel Database Processing and Distributed Databases McGraw Hill Irwin Copyright 2007 by The McGraw Hill Companies Inc All rights reserved Parallel DBMS Uses a collection of resources processors disks and memory to perform work in parallel Divide work among resources to achieve desired performance scaleup and speedup and availability Uses high speed network operating system and storage system Purchase decision involves more than parallel DBMS 17 3 Basic Architectures a SE b SD c SN N P P P M N P P P P P P M M M M M M Legend P processor M memory N high speed network SE shared everything SD shared disk SN shared nothing 17 4 Clustering Architectures a Clustered disk CD b Clustered nothing CN N N P P P P P P P P P P P P M M M M M M M M M M M M 17 5 Design Issues Load balancing CN architecture most sensitive Cache coherence CD architecture problem Interprocessor communication CN architecture most sensitive Application transparency no knowledge about parallelism 17 6 Oracle Real Application Clusters 17 7 Oracle RAC Features Cache fusion to synchronize cache access Query optimizer intelligence Connection load balancing Automatic failover Comprehensive administration interface 17 8 IBM DB2 SPF Coordinator P P P P P Partition 1 P M M P Partition 2 P P M Partition n 17 9 IBM SPF Features Automatic or DBA determined partitioning Query optimizer intelligence High scalability Partitioned log parallelism 17 10 Distributed Database Architectures DBMSs need fundamental extensions Underlying the extensions are a different component architecture and a different schema architecture Component Architecture manages distributed database requests Schema Architecture provides additional layers of data description 17 11 Global Requests Product data Customer order data Product data Customer order data 17 12 Component Architecture GD GD DDM GD Site 2 DDM Site 1 LDM DB DDM Site 3 LDM DB 17 13 DBST 651 If you were tasked with defining the functions of the DDM what would you say 17 14 DBST 651 If you were tasked with defining the functions of the LDM what would you say 17 15 Schema Architecture I External schema 1 External schema 2 External schema n Conceptual schema Fragmentation schema Allocation schema Internal schema 1 Internal schema 2 m Sites Internal schema m 17 16 Schema Architecture II Global external schema 1 Global external schema 2 Global external schema n Global conceptual schema m Sites Site 1 local mapping schema Site 2 local mapping schema Site 1 local schemas conceptual internal external Site 2 local schemas conceptual internal external Site m local mapping schema Site m local schemas conceptual internal external 17 17 Distributed Database Transparency Transparency is related to data independence With transparency users can write queries with no knowledge of the distribution and distribution changes will not cause changes to existing queries and transactions Without transparency users must reference some distribution details in queries and distribution changes can lead to changes in existing queries 17 18 Motivating Example Customer CustNo CustName CustCity CustState CustZip CustRegion Product ProdNo 1 1 ProdName ProdColor ProdPrice 8 1 ProdNo OrdNo Inventory OrdCity StockNo OrdDate OrdAmt CustNo 1 8 OrdNo 8 OrderLine 8 Order QOH WarehouseNo ProdNo 17 19 Fragments Based on the CustRegion Column CREATE FRAGMENT Western Customers AS SELECT FROM Customer WHERE CustRegion West CREATE FRAGMENT Western Orders AS SELECT Order FROM Order Customer WHERE Order CustNo Customer CustNo AND CustRegion West CREATE FRAGMENT Western OrderLines AS SELECT OrderLine FROM Customer OrderLine Order WHERE OrderLine OrdNo Order OrdNo AND Order CustNo Customer CustNo AND CustRegion West CREATE FRAGMENT Eastern Customers AS SELECT FROM Customer WHERE CustRegion East CREATE FRAGMENT Eastern Orders AS SELECT Order FROM Order Customer WHERE Order CustNo Customer CustNo AND CustRegion East CREATE FRAGMENT Eastern OrderLines AS SELECT OrderLine FROM Customer OrderLine Order WHERE OrderLine OrdNo Order OrdNo AND Order CustNo Customer CustNo AND CustRegion East 17 20 Fragments Based on the WareHouseNo Column CREATE FRAGMENT Denver Inventory AS SELECT FROM Inventory WHERE WareHouseNo 1 CREATE FRAGMENT Seattle Inventory AS SELECT FROM Inventory WHERE WareHouseNo 2 17 21 Fragmentation Transparency Fragmentation transparency provides the highest level of data independence Users formulate queries and transactions without knowledge of fragments locations or local formats If fragments change queries and transactions are not affected 17 22 Location Transparency Location transparency provides a lesser level of data independence than fragmentation transparency Users need to reference fragments in formulating queries and transactions However knowledge of locations and local formats is not necessary 17 23 Local Mapping Transparency Local mapping transparency provides a lesser level of data independence than location transparency Users need to reference fragments at sites in formulating queries and transactions However knowledge of local formats is not necessary 17 24 Oracle Distributed Databases Homogeneous and heterogeneous distributed databases Emphasis on site autonomy Provides local mapping transparency Each site is a separately managed database 17 25 Oracle Links One way link from local to remote Support remote access to other users objects Necessary to have knowledge of remote database objects Use synonyms and views with links to reduce remote database knowledge 17 26 Distributed Database Processing Distributed data adds considerable complexity to query processing and transaction processing Distributed database processing involves movement of data remote processing and site coordination Performance implications sometimes cannot be hidden 17 27 Distributed Query Processing Involves both local intra site and global inter site optimization Multiple optimization objectives The weighting of communication costs versus local processing costs depends on network characteristics There are many more possible access plans for a distributed query 17 28 Distributed Transaction Processing Distributed DBMS provides concurrency and recovery transparency Independently operating sites must be coordinated New kinds of failures exist because of the communication network New protocols are