Oracle Database - What and How to learn

Oracle Database Expert Learning

Session 1: User creation, Database user, OS authenticated user, SYS user, PDB users, Global users

Session 2: tablespace allocation – TEMP, default tablespace, Tablespace quota, database_properties, basic permissions

Session 3: Tables: What is a table, create table, columns, INSERT, DELETE and UPDATE, DROP table, Add Column

Session 4: What is an index, how to create, drop, rebuild

Session 5: Tablespace creation, drop, add data files, alter data file size, extent management – LOCAL, extent size,

Session 6: What is a parameter, parameter file, spfile, SCOPE, alter parameter, V$PARAMETER, Dynamic parameters, underscore parameters, basic parameters, INSTANCE_MODIFIABLE, V$SYSTEM_PARAMETR

Session 7: Introduction to database and Instance – SGA, processes, files and memory

Session 8 : introduction SGA components – Shared pool, buffer cache, redo log buffer, Large pool, streams pool, Java Pool

Session 9 : PGA and UGA – Stack space, Hash Area, SQL Work area, Private work area, Session memory

Session 10 : Introduction to background process – PMON, PMAN,SMON, DBWRn, CKPT, MMON, MMNL, RECO, LGWR, ARCn, CJQ0, RVWR, FBDA, SMCO, Dnnn, Snnn

Session 11 : Introduction to files – parameter files, datafiles, log file, controlfile, recovery files, trace and log files, archive log files, RMAN backup files

Session 12 : Oracle network configuration - TNSNAMES.ORA files, SQL net

Session 13 : Connection life cycle – dedicated, shared, server process, communication

Session 14 : Memory configuration - SGA_TARGET, SGA_MAX_SIZE, MEMORY_TARGET, PGA_AGGREGATE_TARGET, PGA_AGGREGATE_LIMIT, configuring other sub pools like buffer cache, log buffer, large pool.

Session 15 : Database buffer cache, Default buffer, Keep buffer, Recycle buffer, 4K, 8K,16K, 32K custom pools, Buffer management – Hot and cold region, check point

Session 16 : What is redo – redo concepts, redo file configuration, redo buffer, Log writer process and management, archive files, archive and noarchive.

Session 17 : Database block – block structure, Pctfree, pctused, inittrans, maxtrans, Segment Space management, Block size

Session 18 : Block and buffer cache – CR reads, physical reads, Current gets, Block management in buffer cache, Why recycle pool, impact of Full Table Scans

Session 19 : Database Writer - concept, configuration, checkpoint

Session 20 : Undo management – concepts, UNDO_MANAGEMENT, Undo files, undo segments, UNDO_RETENTION, V$UNDOSTAT

Session 21 : Introduction to Indexes - what is index, Binary indexes, function based indexes, reverse key indexes, Bit map indexes, Text indexes, partitioned indexes, local indexes, invisible indexes

Session 22 : Why indexes and how many – a developer thinking.  Pros and cons of indexes, Index selection, indexing in OLTP and DWH

Session 23 : Full Table scans – What is full table scan, impact, good or bad, When to use and not use, forcing index hints

Session 24 : Index blocks – index fragmentation, BLEVELS, index rebuild - why, Index COALESCE, init trans, maxtrans

Session 25 : Index clustering factor and full tables scans – a developer thinking

Session 26 : Introduction to Cost based optimizer – how it works, basic needs, configuration – OPTIMIZER_MODE

Session 27 : Introduction to Oracle optimizer statistics – what is statistics, DBMS_STATS, where it is stored

Session 28 : Statistics collection – DBMS_STATS, tables stats, schema stats, method_opt, sample size, GATHER_STATS_JOB, GATHER_TABLE_STATS   

Session 29 : Stats collection – introduction to histograms, Height based, frequency based, Hybrid, top frequency, auto collection, manual collection, METHOD_OPT,

Session 30 : Introduction to partitioned tables – List partition, Range Partition, Hash partition, composite partitions, sub partitions,

Session 31 : Stats collection – What a developer should know. Know the data, expected plans, data distribution, OLTP or DWH, execution time, execution plans, type of DB server - Cloud, onperm or Exadata. 

Session 32 : Table joins - Sort and Merge join, Hash join, Nexted loops

Session 33 : Sort and Merge Join – what is a sort and merge join, one pass, multipass, temporary tablespace usage, IO impact, PGA / sort memory usage

Session 34 : Hash join - concepts, Table selection, PGA hash size, Hash tables, Look up table and execution path

Session 35 : Nested Loop join – concepts, driving table, inner table, nested look batching

Session 36 : CBO Optimizations steps – Parse, Execute and fetch.

Session 37 : CBO - Hard Parse, Soft parse and Soft softer parse, Cursor sharing, Session cached cursor, Child cursor, CBO re-parsing scenarios

Session 38 : Sorting explain – single pass, multi-pass, PGA usage, V$SQL_WORKAREA_HISTOGRAM, V$WORKAREA_ACTIVE , V$SQL_WORKAREA, Maximum sort usage

Session 39 : Over view of V$SYSSTAT and V$SESSTAT – peep into various session run time statistics and figuring out bottlenecks

Session 40 : Direct Path read – concepts, various cases, FTS, Parallel Query, Backup, pros and cons, direct path read TEMP, DB_FILE_DIRECT_IO_COUNT, _SERIAL_DIRECT_READ, events 10357 trace 

Session 41 : Introduction to Oracle wait interface – Events, wait classes, P1,P2,P3, what is a wait

Session 42 : Concurrent wait classes – Latches and mutexes, CPU consumption, _SPIN_COUNT

Session 43 : Managing CPU issues in Oracle database environment – iostat, TOP and other unix tools. Idnetifying SQL from ASH and AWR, Various latches – CBC, Library latches. Identifying and troubleshooting

Session 44 : Introduction to AWR and ASH tools – understanding AWR report, ASH report

Session 45: Analyzing ASH and AWR tables directly – identifying SQLs causing issues directly from ASH and AWR tables

Session 46 : Database Services – Creating services – DBMS_SERVICES (Single instance), SRVCTL, SERVICE_NAMES(parameter), preferred and AVAILABLE services, FAN registration, TAF (Transparent Application Failover), Global Service goals – LONG and SHORT.

Session 47 : CBC latches and CPU issues – What is a Latch and Mutex, Immediate and willing wait latches, _SPIN_COUNT, concurrent FTS, Identifying the issues from AWR data.

Session 48 : Library Cache Latches and CPU issues – Hard parsing Vs Soft parsing Vs Soft Softer parse, Child cursor - VSQL, Session _cached_cursor,   CURSOR_SHARING, V$SQL_CS_SELECTIVITY,   V$SQL_CS_STATISTICS, V$SQL_CS_HISTOGRAM

Session 49 : INITRANS / MAXTRANS and ITL waits.  

Session 50 : ROW migration – PCTFREE, detection and prevention

Session 51 : Influencing Automatic statistics collection : DBMS_STATS.SET_*_PREFS, AUTOSTAT_TARGET, CASCADE, DEGREE, ESTIMATE_PERCENT, METHOD_OPT, NO_INVALIDATE, GRANULARITY, PUBLISH, INCREMENTAL, STALE_PERCENT

Session 52 : Introduction to flashback : query, table, database, drop, very query, flashback archive