INTRODUZIONE AL TUNING Tipi di tuning tuning dellarchitettura

INTRODUZIONE AL TUNING Tipi di tuning: • tuning dell’architettura fisica • tuning dell’istanza • tuning dell’architettura logica • tuning applicativo Metodi di tuning: il tuning prevede sempre una configurazione di base, successivamente il tuning viene iterato sulla base di risultati ottenuti attraverso monitoraggio sistematico delle performance, dei conflitti e delle contese sulle risorse.

SGA Buffer Chache PROCESSES SQLArea PMON SMON DBWR LGWR LCKn SNPn RECO Log Buffer Snnn DATABASE ARCH Redo Log Files Archived Redo Log Data Files CKPT Control Files

SGA Buffer Chache PROCESSES PMON SMON DBWR LGWR LCKn SNPn SQLArea RECO Log Buffer Snnn DATABASE ARCH Redo Log Files Data Files CKPT Control Files

SGA Buffer Chache PROCESSES PMON SMON DBWR LGWR LCKn SQLArea SNPn RECO Log Buffer Snnn DATABASE ARCH Redo Log Files Data Files CKPT Control Files

SGA Buffer Chache PROCESSES PMON SMON DBWR LGWR LCKn SNPn SQLArea RECO Log Buffer Snnn DATABASE ARCH Redo Log Files Data Files CKPT Control Files

SGA Buffer Chache PROCESSES PMON SMON DBWR LGWR LCKn SQLArea SNPn RECO Log Buffer Snnn DATABASE ARCH Redo Log Files Archived Redo Log Data Files CKPT Control Files

SGA Buffer Chache PROCESSES PMON SMON DBWR LGWR LCKn SQLArea SNPn RECO Log Buffer Snnn DATABASE ARCH Redo Log Files Archived Redo Log Data Files CKPT Control Files

Blocco oracle HEADER Pctfree Data space Pctused

PARAMETRI DI SISTEMA

Esempi di configurazione(Hard. Ware) DA-392 GG-AB AS 8400 4 GB Unix - 9 CPU ev. 67 da 700 MHz KZPBA-CA sigle channe PCI to Ultra. SCSI BN 38 C-02 cable 6 x. RZ 1 ED-VW 18. 2 GB HD 10000 RP TLZ 10 -VA 4 mm DAT DWLPB-BA second 12 -slot PCI exp KZPSA-BB storgae PCI bus adapt BN 21 K-05 cable TL 891 -NT 350/700 GB TZ 89 drive TL 892 -UA add-on TZ 89 drive BN 21 W-0 B cable H 879 -AA SCSI-3 term BN 38 C-10 cable SWXRA-HA RA 7000 subsystem QB-5 SBAB-SA HSZ 70 SW BA 35 X-HH power supply BA 35 X-He power distr Dischi: Symmetrix fibre channel 16 Gb cache performance: da 5 -6 M I/O/sec a 10 -11 M I/O/sec con bloccaggio da 16 Kb o 32 Kb da 5 -6 M I/O a 9/10 M I/O con bloccaggio > di 16 K

Esempi di configurazione (caratteristiche db) OLTP: dimensione db: 350 Gb circa numero utenti: circa 3000 accesso: tramite applicazione client/server DWH: dimensione db: 1 thera circa tabelle da qualche centinaio di Gb caricamento tramite pro*c e manipolazione dati tramite pl/sql

Esempi di configurazione (init. ora di un OLTP) db_name = oltp db_files = 250 control_files =(/u 01/oradata/oltp/control 01. ctl, /u 02/oradata/oltp/control 02. ctl, /u 03/oradata/oltp/control 03. ctl) user_dump_dest = /u 1/app/oracle/admin/oltp/udump background_dump_dest = /u 1/ app/oracle/admin/oltp/bdump core_dump_dest = /u 1/app/oracle/admin/oltp/cdump log_archive_dest = (/u 03/orarch/oltp) # log_archive_format = arc%T_%S. arc log_archive_start = TRUE shared_pool_size = 524288000 # 500 Mb sort_area_size = 524288 sort_area_retained_size = 524288 db_block_buffers = 80000 db_block_size = 8192 # 80000*8192=655360000 -> 650 Mb # QUESTO SETTAGGIO HA IL 45% DI REDO WASTAGE: LO ABBASSO DEL 30% log_buffer = 524288 log_buffer = 368640 log_checkpoint_interval = 1000000 transactions_per_rollback_segment = 26 processes = 1030 optimizer_mode = RULE timed_statistics = TRUE

Esempi di configurazione (init. ora di un DWH) db_name = dwh db_files = 800 control_files = (/u 3/oradata/dwh/controlraw 01. ctl, /u 3/oradata/dwh/controlraw 02. ctl, /u 3/oradata/dwh/controlraw 03. ctl) user_dump_dest = /u 1/app/oracle/admin/dwh/udump background_dump_dest = /u 1/ app/oracle/admin/dwh/bdump core_dump_dest = /u 1/app/oracle/admin/dwh/cdump # log_archive_dest = (/u 2/oraarch/dwh) # log_archive_format = arc%T_%S. arc log_archive_start = FALSE shared_pool_size = 150000000 sort_area_size = 2000000 sort_area_retained_size = 2000000 # 150 Mb # 20 M db_block_buffers = 120000 db_block_size = 32768 # 120000*32768=3932160000 -> 4 Gb db_file_multiblock_read_count = 8 # controllare log_buffer = 819200 log_checkpoint_interval = 1000000 parallel_max_servers = 20 parallel_min_servers = 0 processes = 50 rollback_segments = (batch 01, batch 02, rbs 04, rbs 05, batch 03, batch 04) # optimizer_mode = CHOOSE timed_statistics = TRUE # controllare

LAYOUT FISICO DI UN DATABASE Control Files ? ? Redo Log Files ? SYSTEM ? TEMP ? RBS DATA IDX ?

LAYOUT FISICO DI UN DATABASE Control Files Redo Log Files SYSTEM TEMP RBS DATA IDX

LAYOUT FISICO DI UN DATABASE Control Files Redo Log Files SYSTEM TEMP RBS DATA IDX

INTRODUZIONE AL BACKUP & RECOVERY Tipi di backup: • backup logico complete • backup logico cumulative • backup logico incremental • backup fisico a freddo, offline (cold backup) • backup fisico a caldo, online (hot backup) Analogie tra: complete vs. cold backup e triade complete, cumulative, incremental vs. hot backup

SYSTEM CHANGE NUMBER & CKPT DATABASE SCN Data Files Gr. 1 SCN SYSTEM SCN TEMP SCN SCN RBS DATA IDX SCN Gr. 3 ……. SCN Gr. n SCN Redo Log Files SCN SCN Control Files

Analogie backup logico e fisico Complete Cumulative Incremental t logico 1 complete + 3 cumulative + 2 incemental = quasi ok t fisico 1 restore + applicazione archived redo log = ok

Backup fisico online (hot backup): Control Files ARCH /u 02/oraarch/orcl Redo Log Files SYSTEM TEMP RBS Cp rbs 01. dbf /backupfs/orcl/rbs 01. bck DATA IDX Tablespace is frozen

Backup fisico online (hot backup): #!/bin/ksh ORACLE_SID=orcl; export ORACLE_SID $ORACLE_HOME/bin/svrmgrl << EOF! Connect internal ALTER TABLESPACE RBS BEGIN BACKUP; exit EOF! cp /u 02/oradata/orcl/rbs 01. dbf /backupfs/orcl/rbs 01. bck …. . cp /u 02/oradata/orcl/rbs 0 n. dbf /backupfs/orcl/rbs 0 n. bck $ORACLE_HOME/bin/svrmgrl << EOF! Connect internal ALTER TABLESPACE RBS END BACKUP; ALTER TABLESPACE SYSTEM BEGIN BACKUP; exit EOF! …. . $ORACLE_HOME/bin/svrmgrl << EOF! Connect internal ALTER TABLESPACE XXX END BACKUP; exit EOF! cp /u 02/oradata/orcl/controlfile*. ctl /backupfs/orcl/ cp /u 02/oraarch/orcl/* /backupfs/orcl/ echo backup complete

Dettaglio delle modalità di startup… Startup nomount Startup mount Alter database open …e di shutdown • Shutdown normal • shutdown transactional • shutdown immediate • shutdown abort Startup open

Tipi e caratteristiche di recovery:

Tipi di media recovery: * vengono detti metodi di recovery incompleti. Si sta effettuando un Point in Time Recover (PTTR)

Casi di studio

Riferimenti: • Generale: www. databtech. com www. jlcomp. demon. co. uk • Tuning www. oraperf. com www. ixora. com • Backup&Recovery Oracle Backup & Recovery Handbook Rama Velpuri, Anad Adkolli; 1998, Oracle Press. alberto. gambella@databtech. com

ROLLBACK SEGMENT T 1 b 2 T 2 b 3 b 4 b 5 b 6