Oracle V2.3 to 10g a journey

Oracle V2.3 : The first Oracle to run on a Digital PDP-11 Macro-11 with two task architecture as 16 bit system allowed only 32K process size.Rule Based Optimizer with table order and Equality or partially qualified keys for indexes.

Oracle V3 for Portability The code went in for a Complete rewrite;Used VAX/VMS shared memory capabilities for secure single task architecture and Supported transactions.Oracle V4 for Portability Architecture MS-DOS in 640K with Read consistency BI files, copies of before image of block-views .

Oracle V5 for Distributed Client server Model SQL*Net-Client/server-Distributed (v5.1) Birth of SQL*Plus SQL*Forms.

Oracle V6 for ScalabilityCompletely New architectureDBWR for database file writesLGWR for log writesRollback segments for undo and CR-Row level lockingHot backupPL/SQL anonymous blocksTable spacesOPS New 'Dynamic Performance Tables'v$sysstatv$lockv$waitstattimed_statistics parameter, off by default
SQLtrace,tkprof and Explain plan capability

Oracle7: for ProgrammableProcedure, triggers (stored PL/SQL), Constraints came into existence. Introduction of Cost Based Optimizer and shared pool for addresses parsing/memory issues and Parallel Query in 7.1. V$SQL to see what is in shared pool–which SQL was doing most buffer gets, disk reads and Wait Events.

Oracle 8 & 8i for Objects & Java Introduction to Partitioning, First 'cache fusion' features and 8i STATSPACK as part of product-High level summary. Wait times prominent-Drilldowns-Time based method of analysis.

Oracle 9i Unbreakable RAC with complete cache fusion Statistics changes-Times in microsecond V$SQL_PLAN-Times in V$SQLno longer need proxies of buffer gets, disk reads and object ids in waiting sessionsAdvisories for buffer cache, shared pool and PGA Statspack changes-CPU time into 'Top Wait Events' this would be a linkSQL rep Report usage of SQL between two snaps including all plans that had been used (See the Oracle Tutorial section for details) this would be a link Oracle 10g for ManageabilityAutotuning of SGA Buffer cache, shared pool, large pool and java pool Automatic Work Load Repository Statspack++Installs as part of database-Built into database so more efficient Automatic Database Diagnostic Monitor Time as common currency-Time based tuning recommendations Tuning AdvisorMakes tuning recommendations for tuning an individual statement

ConclusionsWith time Oracle has become more scalable More instrumented more diagnosable and more complicated (but also more automated)