1 / 27

Tipi di tuning: tuning dell’architettura fisica tuning dell’istanza

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

vail
Download Presentation

Tipi di tuning: tuning dell’architettura fisica tuning dell’istanza

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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.

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

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

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

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

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

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

  8. Blocco oracle HEADER Pctfree Data space Pctused

  9. PARAMETRI DI SISTEMA

  10. Esempi di configurazione(HardWare) DA-392GG-AB AS8400 4GB Unix - 9 CPU ev.67 da 700MHz KZPBA-CA sigle channe PCI to UltraSCSI BN38C-02 cable 6xRZ1ED-VW 18.2GB HD 10000RP TLZ10-VA 4mm DAT DWLPB-BA second 12-slot PCI exp KZPSA-BB storgae PCI bus adapt BN21K-05 cable TL891-NT 350/700GB TZ89 drive TL892-UA add-on TZ89 drive BN21W-0B cable H879-AA SCSI-3 term BN38C-10 cable SWXRA-HA RA7000 subsystem QB-5SBAB-SA HSZ70 SW BA35X-HH power supply BA35X-He power distr Dischi: Symmetrix fibre channel 16 Gb cache performance: da 5-6M I/O/sec a 10-11M I/O/sec con bloccaggio da16Kb o 32Kb da 5-6M I/O a 9/10M I/O con bloccaggio > di 16K

  11. Esempi di configurazione (caratteristiche db) OLTP: dimensione db: 350Gb 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

  12. Esempi di configurazione (init.ora di un OLTP) db_name = oltp db_files = 250 control_files =(/u01/oradata/oltp/control01.ctl, /u02/oradata/oltp/control02.ctl, /u03/oradata/oltp/control03.ctl) user_dump_dest = /u1/app/oracle/admin/oltp/udump background_dump_dest = /u1/app/oracle/admin/oltp/bdump core_dump_dest = /u1/app/oracle/admin/oltp/cdump log_archive_dest = (/u03/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 -> 650Mb # 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

  13. Esempi di configurazione (init.ora di un DWH) db_name = dwh db_files = 800 control_files = (/u3/oradata/dwh/controlraw01.ctl, /u3/oradata/dwh/controlraw02.ctl, /u3/oradata/dwh/controlraw03.ctl) user_dump_dest = /u1/app/oracle/admin/dwh/udump background_dump_dest = /u1/app/oracle/admin/dwh/bdump core_dump_dest = /u1/app/oracle/admin/dwh/cdump # log_archive_dest = (/u2/oraarch/dwh) # log_archive_format = arc%T_%S.arc log_archive_start = FALSE shared_pool_size = 150000000 # 150Mb sort_area_size = 2000000 # 20M sort_area_retained_size = 2000000 db_block_buffers = 120000 db_block_size = 32768 # 120000*32768=3932160000 -> 4Gb 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 = (batch01,batch02,rbs04,rbs05,batch03,batch04) # optimizer_mode = CHOOSE # controllare timed_statistics = TRUE

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

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

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

  17. 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

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

  19. 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

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

  21. 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 /u02/oradata/orcl/rbs01.dbf /backupfs/orcl/rbs01.bck ….. cp /u02/oradata/orcl/rbs0n.dbf /backupfs/orcl/rbs0n.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 /u02/oradata/orcl/controlfile*.ctl /backupfs/orcl/ cp /u02/oraarch/orcl/* /backupfs/orcl/ echo backup complete

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

  23. Tipi e caratteristiche di recovery:

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

  25. Casi di studio

  26. 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

  27. ROLLBACK SEGMENT T1 b1 b2 T2 b3 b4 b5 b6

More Related