Oracle 9i
.?.?.? DB.? Language.?.?.? (DW,OLAP,MINING,OLTP ) DB.?.?
Technology Evolution High Availability Scalability Manageability Development Platform Business Intelligence
Technology Evolution
Technology Evolution /
Technology Evolution Distributed Computing. Oracle 7 90 OLTP/DW /APP GUI Tool
Technology Evolution Internet Computing. Oracle8i 90 /00 Web Server 3-Tier JAVA B-to-C/B-to-B ERP/CRM E-business RDBMS JVM (EJB,CORBA) Fine-grained access control Materialized View
e-business Era. Oracle9i Clustered e-business (High Availability) (High Scalability) (High Quality Of Services) (Extend Market Globalization) DBA On-Line Management Business intelligence E-business
Oracle9i Database - Downtime... & Real Application Clusters Crash Data Guard Data Corruption Guard Zero Data, Loss, FlashBack Query Dynamic Reconfiguration & O/S HW Online Redefition
High Availability Scalability
Choosing A Deployment Platform Single SMP CPU Failover Clustering Fault tolerant systems;
Real Application Clusters Interconnect Hub Switch Fabric Storage Area Network Cache Fusion Shared Cache
Real Application Cluster Instance Oracle Parallel Database Distributed Lock Manager SGA Instance 1 SGA Instance 1 SGA Instance 1 Dictionary Cache Log Buffer Buffer Cache Dictionary Cache Log Buffer Buffer Cache Dictionary Cache Log Buffer Buffer Cache Library Cache Distributed Lock Area Library Cache Distributed Lock Area Library Cache Distributed Lock Area LCK LGWR DBWR LCK LGWR DBWR LCK LGWR DBWR SMON PMON SMON PMON SMON PMON 1 2 N
e-business Era. Oracle9i RAC No Single point of Failure C a c h e F u s i o n
(Cache Fusion) Oracle9i Interconnect Disk Disk I/O A B Database
(Cache Fusion) Instance B instance A contention Lock (DLM) Request for Block Cache A Read Read Write Write Lock Status Block in Cache B Read Write Read Write
clustered database (DW OLTP ) (Data Warehouse) Parallel-enabled OLTP Lock DLM configuration INIT.ORA lock. SMP SAN
Oracle9iDB Cluster Performance Oracle E-Business Suite 11i Scalability 89% Scalability # Users 4,000 3,500 3,000 2,500 2,000 1,500 1,000 500 0 1,026 1,900 3,648 Single Node 2 Nodes 4 Nodes Running on HP Computers
Clustering Types Shared disk Runs real applications DB2 Shard Cache Fusion Oracle 9i RAC Shared nothing Only runs benchmarks Microsoft SQLServer DB2 UNIX & windows No SAP, Siebel, Peoplesoft A-Z A-F A-E G-L F-K M-R L-P S-Z Q-S T-Z
Shard Nothing Disk 8 1. 2. 3. 4. 5. 6. 7. 8.
Oracle9i Data Guard Primary System Oracle9i Production Database Standby System Oracle9i Standby Database Broker Redo Logs Log Ship ZERO DATA LOSS Log Receive Standby Logs Log Apply Optional Delay Broker Broker Management Interface
Oracle9i Data Guard protects... System Failure Data Corruption Disasters Human Error Routine Operations Routine Maintenance Data Guard Standby Failover Primary Primary Standby Offload Standby Switchover
Oracle9i Flashback Query SQL Create table old_emp as select * from emp AS OF yesterday;.
Online Redefinition INDEX Index Organized Table,,, analyze validate Updates & queries continue uninterrupted
Oracle9i High Availability Matrix Fast Restart Recovery Manager Dynamic Reconfiguration Online Redefinition Flashback Query Log Miner Data Partitioning Data Guard cold Failover Real Application Clusters Partial Partial
Oracle9i DB2 UDB SS 2000 Fast Start recovery No No Multiplexed log files No Data Guard No No Transparent Application Failover No No Sub-minute System Failover No No Flashback Query No No Partitioning Mechanisms Limited No
Manageability
Manageability / Enterprise Manager Keep the number of DBA s constant as we scale to Internet Use
(Undo) Undo - undo block contention, consistent read, space utilization (Managed Files) Oracle9i Self-Tuning of private memory allocation Buffer cache, Shared pool Buffer Cache Shared Pool Private Memory
Dynamic Reconfiguration Oracle9i SMP CPU Storage Capacity on demand
Persistent Parameter File INIT.ORA Resumable Space Allocation Cached Execution Plan SQL Execution Plan Default Temporary Tablespace
MTTR( ) Block DB File Block / / / (Test Recovery)
Increase Oracle DBA productivity by 40 % DBA Days Per Week 5 4 3 2 1 0 Total Configuring Monitoring Managing Tuning Training Oracle8i Database Oracle9i Database
Development Platform
Development Platform Globalization NCHAR Unicode 3.0/UTF16 Timezone/DST Security Virtual Private Databases label security Type Inheritance Type evolution Native XMLType XMLType SQL XML XPath SQL PL/SQL TimeZone DST SQL CASE Table Function UltraSearch Intranet Portal
Globalization ebusiness Multi language via Unicode 3.0 UTF16 Multilingual, Linguistic and User Definable Collation DST
Globalization 57,88 200?????????? ñ????? ó ö?????? DB - NCHAR
Business Intelligence
Oracle 8i Architecture for E-Business Intelligence Operational Data Warehouse Web Builder Data External Data Oracle8i Express Darwin CWM Metadata Application Server Reports Discoverer Portal Express
Oracle 9i Foundation for E-Business Intelligence Operational Data E-Business Intelligence Suite Oracle9i Reports Discoverer Web Data Warehouse Builder ETL Infrastructure and OLAP Services and Data Mining 9i Application Server BI Beans External Data CWM Metadata Portal
Data Warehousing OLAP Data Mining ETL Data Warehousing List Partitioning Bitmap Join Indexes OLAP Services - Analytic Business Intelligence Beans MOLAP/ROLAP Data Mining Oracle Personalization Recommendation Engine Oracle9i ETL Extract,Storage,Transform, Load, Store Data Staging External Table OS
Oracle9i Partitioning Partition by... Range List Hash Composite
List Partitioning,. CREATE TABLE (...) PARTITION BY LIST ( ) ( PARTITION VALUES (,, ), PARTITION VALUES (,, ), PARTITION VALUES (,, ), PARTITION VALUES (,,, )); 1200 2000 0400 GMT
Bitmap Join Indexes Bitmap join index Materialized views Star Schema Dimension Sales Customer Example Query SELECT SUM(Sales.dollar_amount) FROM Sales, Customer WHERE Sales.cust_id = Customer.cust_id AND Customer.state = Oregon ;
Oracle9i Scalability Data Partitioning Range Hash Composite List Oracle8i No Oracle9i DB2 S/390 Unix, NT AS400 No No SS 2000 No No No No
OLAP Services SQL Aggregation Enhancements Grouping Sets Concatenated Grouping Sets Aggregate Pruning Concatenated Grouping Sets (new): SELECT,,,,SUM(Sales) FROM,, WHERE. ID =. ID AND. ID =. ID GROUP BY GROUPING SETS (.,. ), GROUPING SETS (.,. );
Personalization Services How Does It Work? BooksRus.com BooksRus.com s Book Store - Netscape BooksRus.com BooksRus.com The Brethren by John Grisham BooksRus. BooksRus.com Auctions. BooksRus.co BooksRus.com Auctions. 100 Hot m Books 100 Hot Books Html request Web Server Html Code Generation <static sections> Customer IDBooksRus.com Profession Field BooksRus.com Score Classic Rap Action Thriller Poetry 23567 Computer Consulting 15 1 600 1 0 Html code <dynamic sections created using JSP> www. booksrus.com Browser (Client) The session data is cleaned to create DB a Oracle 9i browsing profile to be Personalization used with customer. data Services for scoring
ETL Process Business Intelligence, The Old Way Extract Stage Transform Load Store Mine Analyze Use
ETL Process- Oracle 9i Business Intelligence Use Scaleable Data Flow High-Performance Transformation Engine Easy to build and manage
Business Intelligence Beans Rapid Development Presentation Beans (Crosstab, Table,Graph) Analysis Beans (Query Builder, Calc Builder) Internet Deployment Java client Server side / HTML only client Leverage 9i OLAP JDeveloper integration Table Connection Cross Tab Oracle9i OLAP Services Query Graph Oracle9i Query Builder Meta Data Manager Calculation Builder Persistence Beans Repository
Oracle9i Unique New Features Scalability Real Application Clusters Availability DataGuard On-Line Reorganization Flash-Back Query Security Label Security Fine Grained Auditing Management Feedback-Directed Memory Management Resumable Statements Hosting Enhanced Virtual Private Database Business Intelligence Integrated OLAP Advanced Analytics ETL Bitmap-Join Indexes Content Management Files UltraSearch Web Services Dynamic Services XML Datatype
Oracle 9i