Chapter 1

1. Oracle Architecture Database Processing Chapter 1 The Oracle Architecture

2. Oracle Architecture Vocabulary/Terminology • Let’s review some terms and concepts from DB in general... • http://www.hp.isc.usouthal.edu/isc563/ISC563.htm Page 12

3. Oracle Architecture Review from 561 Customer Sales Rep • Transaction Processing Schema Product Document of Transaction Line Item Service

4. Service Oracle Architecture Southern Floors Customer Renter Sales Rep Clerk • Fill in the blanks... Product Sander Document of Transaction Rental Agreement Line Item Equipment List Service

5. Oracle Architecture Review from 561 Clerk ------------- ClerkID (PK) Other stuff Customer ------------- CustomerID (PK) Other stuff • Transaction Processing Schema Equipment ------------- EquipID (PK) Other stuff Recall, the ERD won’t necessarily map to the table structure as it does in this case. Rental Agreement ------------- Rental_Num (PK) CustomerID (FK) ClerkID (FK) DateTime Equipment List ------------- Rental_Num (PK)(FK) EquipID (PK)(FK) Other stuff

6. Oracle Architecture Time to look under the hood... hmm... PGA, SGA, PMON, SMON, LGWR, CKPT, DBWR, ARCH, DataFiles, Data Buffers image source: http://askanexpert.net/howto.html

7. Let’s start at the begining... Storage Hierarchy Oracle Architecture Database Tablespace Data file (Physical) (Logical) Segment Extent OracleBlock OS Block Page 12 Oracle Corporation

8. Why multiple files? Oracle Architecture Database (logical) (physical) Page 12

9. Storage Hierarchy Oracle Architecture A database is physically stored as data files. Database Tablespace Data file (Physical) (Logical) Segment Extent OracleBlock OS Block Page 12 Oracle Corporation

10. Name of database At a minimum there is a SYSTEM Tablespace Physical data files DBA_DATA_FILES is a data dictionary view Datafiles Oracle Architecture

11. Datafiles Oracle Architecture In this case, the tablespaces match a physical file 1:1, but a Tablespace can be distributed across multiple data files. Tablespace = logical Datafile = physical

12. Datafiles Oracle Architecture USER_DATA I can add a datafile to the USER_DATA tablespace. USER1ORCL.ORA USER1ORCL2.ORA SQL> ALTER TABLESPACE USER_DATA ADD DATAFILE ‘C:\ORACLE\ORA563\DATABASE\USER1ORCL2' SIZE 1M;

13. Datafiles Oracle Architecture Once you add a datafile, you can’t remove it from the tablespace.

14. Storage Hierarchy Oracle Architecture Data files are logically grouped by tablespaces. Database Tablespace Data file (Physical) (Logical) Segment Extent OracleBlock OS Block Page 12 Oracle Corporation

15. Tablespaces Oracle Architecture View tablespaces by querying the data dictionary.

16. Tablespaces Oracle Architecture • You can query across tablespaces • Why organize database datafiles into these logical storage units? • Control disk space allocation • Assign space quotas for users • Control availability of data: individual tablespaces can be brought offline and online separately • Partial backup and recovery • Tuning by allocating data across multiple storage devices • Manage I/O contention Page 12

17. Tablespaces Oracle Architecture • Tablespaces can contain: • Tables • Views • Sequences • Synonyms • Indexes • Clusters Page 12

18. Tablespaces Oracle Architecture  • Oracle vs. SQL Server Page 12 http://www.microsoft.com/sql/techinfo/deployment/2000/MigrateOracle.asp (109 pages)

19. Schemas Oracle Architecture • All objects in a Tablespace must have an owner. • Objects owned by the same user are members of that user’s schema • For example: SCOTT.DEPT indicates that the table DEPT is a member of the SCOTT schema • Therefore, ownership (by schema) is another means of logically organizing the objects in a tablespace. Page 12

20. Schemas Oracle Architecture The tables in the SCOTT schema Page 12

21. Tables Oracle Architecture Query the data dictionary for tables in the USER_DATA tablespace Page 12

22. Tables Oracle Architecture • No discussion on tables per se... • An administrative issue is when tables get very large (teraflation) • Very large database (VLDB) • What is large? • giga, tera, peta? • Numerical threshold or maintenance overhead? Recovery time? Page 12

23. Oracle Architecture Partitions • Oracle supports the partitioning of tables • Dividing a table into smaller sub-tables (partitions) based on a range value. • What types of tables should be considered for partitioning? • Historical data • Tables that are static except for regularly appended data. • Tables with a logical partition column

24. Oracle Architecture Partitions • Why not define separate tables? • Eg. the Sales table becomes: • SalesQ1y01 • SalesQ2y01 • SalesQ3y01 • SalesQ4O1 and so on...

25. Oracle Architecture Partitions A sample partitioning In this case, each partition is a separate tablespace. image source: Oracle documentation

26. Oracle Architecture Views • We briefly reviewed this. • Any questions? Query DBA_VIEWS

27. Oracle Architecture Sequences • Oracle doesn’t have an autonumber or identity option for the integer datatype • Instead, you define a sequence object to generate unique sequential numbers (e.g., for PKs)

28. Oracle Architecture Sequences • From an application design standpoint, what is a problem with autonumber and identity? • How do you enforce uniqueness across tables? • A recent example, Senior project: wanted to name jpg files same as PK of tables (Staff, Athletes, and Journalists). Problem, not unique across tables.

29. Oracle Architecture Sequences • Why not generate sequences at the application level? • processing bottleneck • Inefficient in large TPS, multi-user environment • A sequence object can be used to generate unique values for a table, across tables, or for an application. • What problem does concurrency cause? • What is the solution?

30. Oracle Architecture Sequences

31. Oracle Architecture Synonym • Essentially an alias to avoid having to spell out entire qualifier • Scope is either public or schema CREATE SYNONYM market FOR scott.market_research;

32. Oracle Architecture Indexes • What’s the larger issue? • Data access method • Reduces size of scan and therefore I/O • Does not impact formulation of SQL expressions • An index is an ordered list of all the values that reside in a group of one or more columns at a given time.

33. Oracle Architecture Indexes • The index contains two entries: • The key value (e.g., empno = 7369) • The ROWID • The ROWID is a unique address that specifies the row’s position in the datafile. • Object, Block, Row, Datafile

34. Oracle Architecture Indexes ROWID is a pseudocolumn Why not use ROWID as PK?

35. Storage Hierarchy Oracle Architecture Database Tablespace Data file (Physical) (Logical) Segment ROWID bridges this gap An object in a tablespace to a row in a datafile Extent OracleBlock OS Block Page 12 Oracle Corporation

36. Oracle Architecture Indexes • Oracle automatically creates an index on the PK of each table

37. Oracle Architecture Indexes • The most common index structure used by Oracle is a B-tree • A B-tree is an optimally height-balanced, multi-way search tree • What does it mean that Oracle maintains a shallow tree? • The maximum number of levels is 4

38. Oracle Architecture Indexes Root node What order? Branch node Leaf node Doubly linked Oracle corporation

39. Oracle Architecture Indexes • Why use a B-tree? • Relatively uniform access time to ROWID. Why? • Automatically stays balanced • Efficient for both equivalence and range searches. • Where emp = 7360 • Where emp > 700 and emp < 1000

40. Oracle Architecture Clusters • Another access method is clustering • I/O is reduced by storing data in clusters that are physically close to each other. • For example, Invoice and Invoice Details • Physical proximity enhances performance of SQL join statements

41. Oracle Architecture Clusters image source: Oracle documentation

42. Storage Hierarchy Oracle Architecture The space within a tablespace is allocated as segments. Database Tablespace Data file (Physical) (Logical) Segment Extent OracleBlock OS Block Page 12 Oracle Corporation

43. Oracle Architecture Segments • Tablespaces are large logical areas for the creation of objects (e.g., tables, views, indexes, etc... • The space for an object is allocated in terms of segments.

44. Oracle Architecture Segments

45. Oracle Architecture Extents • A segment is comprised of multiple extents

46. Oracle Architecture Extents • An extent is a set of contiguous data blocks used to store a particular type of information.

47. Oracle Architecture Extents • An initial extent is allocated. • When an extent becomes full, another extent from the freespace in the tablespace is allocated to the segment.

48. Oracle Architecture Data Blocks • Data blocks are the smallest unit of I/O for Oracle • When creating a database, you should define data blocks to be a multiple of the OS data blocks.

49. Oracle Architecture Database • So... that’s the basics of the Oracle architecture from a data storage perspective. • But in of itself, there’s not much you can do with the data. • Major distinction • Database versus Instance

50. Oracle Architecture Instance Why multiple instances? Page 12