50 %
50 %
Information about internal

Published on January 15, 2009

Author: aSGuest10685


DBMSInternal Management : DBMSInternal Management Pamela Quick Main topics of this presentation : Main topics of this presentation DBMS File management File organisations ORACLE internal management Query Optimisation Oracle database tuning role of the DBA Internal Level : Internal Level in Ansi-Sparc 3-level architecture - internal (storage view) maps the logical structures onto physical structures vary according to DBMS and host system same general principles. File Management and DBMS : File Management and DBMS DBMS interacts with the operating system, OS to retrieve and store data The components of the OS system relevant are : File Manger Disk Manager DBMS /Host system intercommunication : DBMS /Host system intercommunication DBMS Requests stored record File Manager Request stored Page Disk Manager Disk I/O operation Stored record returned Stored Page returned Data read from disk File Manager : File Manager Component of OS Communicates with the DBMS Maps DBMS request into page set request Each page has a unique page id Page set, unique page set id - collection pages Passes request to Disk Manager Many OS do not provide File Manager facility - DBMS communicates with Disk Manager Disk Manager : Disk Manager Component of OS Responsible for basic I/O services Maps logical page set to physical disk address Advantage - device specific code isolation New allocation - free space page set File Organisations : File Organisations File Organisations : The physical arrangement of data in a file into records and pages on secondary storage Page/physical record/ block is the unit of transfer from secondary storage The order the records are accessed is dependent on the file organisation Main types of file organisation: Heap/unordered Sorted Hash Clusters File Organisations - Heap : File Organisations - Heap Simplest , records held in the same order as inserted. Insertion easy Linear search required to access a record Deletion requires finding record, space not reused, reorganisation required to keep performance Used for : bulk loading data into tables ie populating after recent creation (usually reorganise after creation) if relation is only a few pages if every tuple is to be retrieved File Organisation - Sorted : File Organisation - Sorted records held in order of one or more ordering fields search time can be reduced with a binary search Insertion problematic - leads to overflow Deletion problematic - reorganisation required to recover space Ordered files are rarely used for database storage unless a Primary Index is added to the file File Organisation - Hash : File Organisation - Hash hash function e.g. folding, division-remainder, used to calculate the address of the page. Problems in identifying hash function, to achieve equal distribution and with clashes/synonyms which causes overlow problems Good for when tuples are retrieved based on an exact match of hash field value No good for : pattern match of partial hash field, range of values, tuples retrieved on other field than hash Clusters : Clusters Technique to store data physically close Importance performance factor Intra file clustering - key used to determine sub-sets or clusters e.g. students often retrieved by course Inter file clustering - records from more than one logical database file on same or adjacent physical page e.g. parts and orders tables often joined Indexes : Indexes Index : A data structure that allows the DBMS to locate particular records in a file more quickly, and thereby improve response to queries The file consists of Index file and data file. Index file contains ordered key values and pointer to the data record Indexes can be sparse : has an index for only some of the keys dense: index entry for all records Indexed Files : Indexed Files Indexed sequential - sorted data file with a primary index, data file same sequence. Records accessed sequentially or randomly e.g. IBM ISAM, VSAM. Sparse index can be used - compounded problem for volatile files of both index and data file ordering Secondary Indexes - ordered file similar to primary index but may contain duplicates, data file not usually in same sequence. Requires use of a dense index. Can inprove query speed but needs to be balanced against overhead of index maintenance Multi-level index - large index split into hierarchy of indices to improve search time . Most DBMS use B+ -Trees a special version of a multi-level index B+ - Tree : B+ - Tree A special version of the B-tree ( balanced tree) The degree or order of a tree is the max no of children per parent B+ - tree rules: a tree of order n, each node (except root and leaf) must have between n/2 and n pointers and children for a tree of order n, the number of key values in a leaf node must be between (n-1)/2 and (n-1) values the number of key values is less than the number of pointers the tree must be balanced - every path from root to node must be same length leaf nodes are linked in order of key values B+ - Tree of order 3 Index : B+ - Tree of order 3 Index Two types of pages higher levels - index-set indexes to other index pages lowest level - leaf ‘sequence set’ - pointers to data blocks SL21 SG5 B+ - Tree : B+ - Tree A B+ -Tree takes approx same time to access any data record Being a dense index there is no requirement for data file to be sorted Supports retievals on exact key match, pattern matching, range of values and part-key specification Balancing the tree index on update can be costly on time Oracle Internal Management : Oracle Internal Management User tables table space 1 table space 2 User tables mapped to data blocks within a tablespace ORACLE data blocks Tablespace mapped to physical files file1 file2 file3 Oracle Internal management : Oracle Internal management Tables are associated together in a tablespace Tablespace, a logical structure consisting of Oracle data blocks A number of data blocks assigned to a Create table Inserted data stored in 1st available block When full next data block used More blocks may be assigned ORACLE/Host system intercommunication : ORACLE/Host system intercommunication User/application requests rows ORACLE DBMS maps row/table request to ORACLE data block request ORACLE DBMS maps data block request to host system file page request File Manager Logical page request Disk Manager Physical page access DB ORACLE ROWID : ORACLE ROWID Oracle allocates a unique six byte identifier to each tuple held as a ‘pseudo’ column block no, row no, file no eg 0039.0001.0003 can be accessed by DML SELECT ROWID FROM EMPLOYEE; Query Processing : Query Processing The processing of a query may have more than one way of producing result Query optimization determines ‘best way’ to service query ‘Best way’ usually involves least amount of I/O Changing the order of operations can save I/O Two main approaches to evaluating alternatives: (Syntax-based) Heuristic approach uses Transformation Rules - take a series of operations and demonstrates they can be achieved by a different sequence of operations (Statistics-based) Cost estimation uses data dictionary and DBMS compiled statistics to evaluate Steps in Query Processing : Steps in Query Processing SQL Query Parser Query Optimiser Query Code Generator Run-time Database Parsed Query Execution Plan Executable Code result Query processing steps : Query processing steps Parse and validate - check syntax, validate against data dictionary ‘Parsed query ‘ an intermediate form , represents the semantics in the form of a tree Query optimiser formulates a query plan - efficient execution method query plan is translated into executable code by code generator code execution Parsed Query : Parsed Query The parsed query takes the form of a Relational Algebra Tree where: A leaf node is created for each base relation in the query A non-leaf node is created for each internmediate relation produced by a relation algebra operation The root of the tree is represents the result of the query The sequence of operations is directed from the leaves to the root Parsed Query example : Parsed Query example SELECT * FROM STUDENT S , COURSE C WHERE s.cno= c.cno AND coursename = ‘information Systems’ AND year_enrolled =98; Root Intermediate operations Leaves Student Course year_enrolled = 98 coursename = ‘information systems’ |X| s.cno=c.cno Heuristical Approach to Query Optimization : Heuristical Approach to Query Optimization Some good heuristics that could be applied: Perform selection operations as early as possible Combine the Cartesian Product with a subsequent selection opearation into a join operation Use associativity of binary operations to rearrange leaf nodes so that the leaf nodes with the most restrictive selection are executed first Perform projections as early as possible Compute common expressions once and store for reuse Cost Estimation Approach to Query Optimization : Cost Estimation Approach to Query Optimization To select the option with the lowest cost - some typical statistical information that could be used: cardinality of relations number of pages requirted to store a relation number of distinct values for each attribute selection cardinality of each attribute number of levels in multi-level index Will usually use more machine resources tha syntax-based optimiser and only as good as statistics compiled ORACLE - EXPLAIN PLAN : ORACLE - EXPLAIN PLAN Utility to give details of of access paths used by a specific query steps in use: create table for results PLAN_TABLE run utility naming query retreive results from table PLAN_TABLE details access methods (index, Full scan ROWID) and sequence chosen by the optimiser ORACLE SQL TRACE : ORACLE SQL TRACE Utility gives performance statistics on individula SQL statements Steps in use: Set trace ON Run statement to be tuned Set trace off Convert trace file into readable format using TKPROF utility Details include: CPU and elapsed time number of logical and physical reads Database Administrator DBA Responsibilities - Prior to implementation : Database Administrator DBA Responsibilities - Prior to implementation spread and ‘sell’ concept to users Justify cost/benefits Coordinate and resolve user groups incompatibility DBA Responsibilities - Management of Data Activity : DBA Responsibilities - Management of Data Activity provide db standards establish data ownership, retrieval and modification rights establish integrity constraints create and disseminate recovery procedures inform and train users enforce data activity policy achieve and maintain a satisfactory level of performance achieve and maintain a satisfactory level of security publish and maintain documentation DBA Responsibilities - Management of Database Structure : DBA Responsibilities - Management of Database Structure design schema coordinate data sharing, locking maintain configuration control of DBA change requests user change requests implement schema changes maintain user documentation maintain DBA documentation DBA Responsibilities -Management of Database System : DBA Responsibilities -Management of Database System generate database performance reports investigate user performance complaints analyse reports and complaints tune the database tune communication software evaluate and implement new features DBA - oranisational structure : DBA - oranisational structure Essential role for successful database use Functions - managerial to technical Often broken into following roles: DBA - overall control documentation and standards manager User representative Operations representative performance monitor

Add a comment

Related presentations

Related pages Wörterbuch :: internal :: Deutsch-Englisch-Übersetzung

Englisch-Deutsch-Übersetzung für internal im Online-Wörterbuch (Deutschwörterbuch).
Read more

Kontrollüberzeugung – Wikipedia

Internale und externale Kontrolle. Eine internale Kontrollüberzeugung liegt dann vor, wenn ein Individuum ein positives oder negatives Ereignis als ...
Read more

internal – Wiktionary

Letzte Änderung dieser Seite: 21. Mai 2016 um 22:31; Abrufstatistik Der Text ist unter der Lizenz ''Creative-Commons''-Lizenz „Namensnennung ...
Read more | internal | Wörterbuch Englisch-Deutsch

Übersetzung für internal im Englisch-Deutsch-Wörterbuch
Read more - Sign In

Sign in with your Microsoft domain account. Remember my user name and password. Sign in using smart card Insert your smart card and click the arrow to ...
Read more

internal (C#-Referenz) -

Das internal-Schlüsselwort ist Zugriffsmodifizierer für Typen und Typmember. Auf interne Typen oder Member kann nur in Dateien derselben Assembly ...
Read more

Internal | Define Internal at

Internal definition, situated or existing in the interior of something; interior. See more.
Read more

internal (C# Reference) - MSDN - Microsoft

The internal keyword is an access modifier for types and type members. Internal types or members are accessible only within files in the same assembly, as ...
Read more

Internal | Definition of Internal by Merriam-Webster

1: being within something : inner 2: occurring or located within the body Read more

Internal - definition of internal by The Free Dictionary

While these internal revolutions were going on, her external life had been as busy and uneventful as usual, and if she sometimes looked serious or a little ...
Read more