1 / 58

Toni Navarrete Enginyeria del Software II – UPF 200 7

Aspectes d’administració de l’emmagatzematge d’Oracle (parcialement basat en “Oracle 8: Database Administration” d’Oracle Education). Toni Navarrete Enginyeria del Software II – UPF 200 7. Estructura de l’emmagatzematge a Oracle. Database. Tablespace. Data file. Logical. Physical.

noah-cervantes
Download Presentation

Toni Navarrete Enginyeria del Software II – UPF 200 7

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. Aspectes d’administració de l’emmagatzematge d’Oracle(parcialement basat en “Oracle 8: Database Administration” d’Oracle Education) Toni Navarrete Enginyeria del Software II – UPF 2007

  2. Estructura de l’emmagatzematge a Oracle Database Tablespace Data file Logical Physical Segment Extent Oracle block O/S Block

  3. Què és un espai de taules (tablespace)? • És el nom que es dóna a un grup d’un o més arxius de bases de dades • Quan es creen “objectes” es pot especificar en quin espai de taules • Control sobre on i quin espai d’emmagatzematge s’utilitza • No s’especifica sobre quin fitxer, sinó sobre quin espai de taules • N’hi ha un espai de taules anomenat SYSTEM

  4. SYSTEM Tablespacecontains: Data dictionary information SYSTEM rollback segment Tablespaces SYSTEM i no SYSTEM • Non-SYSTEMTablespace contains: • Rollback segments • Temporary segments • Application data • Application indexes

  5. Com es crea un tablespace? CREATE TABLESPACE nom DATAFILE filespec [clàusula_autoextend] [, filespec [clàusula_autoextend]]... [MINIMUM EXTENT integer[K|M]] [DEFAULT clàusula_storage] [PERMANENT|TEMPORARY] [ONLINE|OFFLINE] Exemple: CREATE TABLESPACE app_data DATAFILE ‘/...../app01.dbf’ SIZE 100M, ‘/...../app02.dbf’ SIZE 100M MINIMUM EXTENT 500K DEFAULT STORAGE (INITIAL 500K NEXT 500K MAXEXTENTS 500 PCTINCREASE 0);

  6. Storage • L’espai està estructurat en blocs • El tamany d’aquests blocs està definit quan es munta la BD, normalment és el mateix tamany que els blocs de SO (per exemple en Linux 4K) • Quan es crea un segment (una taula, per exemple), se li assigna un extent (extensió). • El seu tamany ve determinat per INITIAL • El valor per defecte és 5*tamany del bloc

  7. Storage • En aquest primer extent es posen dades de control del segment i l’espai restant s’usa per insercions de files • Quan és ple, se’n crea un segon • El seu tamany ve determinar per NEXT • Per defecte també 5*tamany de bloc • I així successivament, es pot configurar que cada extent nou sigui en un % més gran que l’anterior • Clàusula PCTINCREASE • Valor per defecte: 50 (arrodonit a l’alça a múltiples de 5) • La creació de nous extents és un procés bastant costòs • Es pot limitar el nombre màxim d’extents • MAXEXTENTS

  8. Exemple Storage • 1er extent: 5 blocs = 20K • 2on extent: 5 blocs = 20K • 3er extent: 10 blocs (5*1,5=7,5 arrodonits) = 40K • 4rt extent: 15 blocs (7,5*1,5=11,25) = 60K • 5è extent: 20 blocs (11,25*1,5=16,875) = 80K

  9. Com obtenim informació dels tablespaces existents? • Taula del diccionari de dades dba_tablespaces (o user_tablespaces) SELECT tablespace_name, initial_extent, next_extent,max_extents, pct_increase, min_extlen FROM dba_tablespaces; • La majoria d’usuaris només tenen permís per veure els tablespaces definits pel propi usuari (no poden accedir a dba_tablespaces) • user_tablespaces

  10. Recomanació • A la taula del diccionari de dades dba_extents (user_extents) tenim els extents • Si un segment (per exemple, taula) té més de 10 extents és convenient refer-lo • Abans de crear (o refer) una taula, convé calcular l’espai de les dades que ja té per assignar-ho al primer extent

  11. Cluster Tipus de segments (1/3) Table Table partition Index

  12. Index-organizedtable Tipus de segments (2/3) Index partition Rollbacksegment Temporarysegment

  13. Tipus de segments (3/3) LOBsegment LOB index Nested table Bootstrapsegment

  14. Jerarquia de les clàusules d’emmagatzematge • Un segment pot tenir les seves pròpies clàusules de storage • Nivells de prioritat: • Especificat al nivell del segment (per exemple en fer un Create Table) • Especificat al nivell de l’espai de taules (en fer un Create Tablespace) • Valors per defecte d’Oracle Oracle default Tablespace Segment

  15. Unificant extents lliures colindants (evitant fragmentacions) • Això passa, per exemple, si s’esborra una taula amb varis extents • Per determinar els extents fragmentats: • Vista DBA_FREE_SPACE_COALESCED • Nota: “coalesce”=fusionar-se, unir-se • Consulta: SELECT tablespace_name, total_extents, percent_extents_coalesced FROM dba_free_space_coalesced WHERE percent_extens_coalesced <>100;

  16. Before After File header Used extent Free extent Unificant extents lliures colindants (evitant fragmentacions) ALTER TABLESPACE data01 COALESCE;

  17. Control de l’espai usat per bloc • PCTFREE (d’un segment de dades) representa el % de cada bloc que deixem lliure per suportar el creixement per “updates” de la fila • PCTUSED (d’un segment de dades) representa el % mínim d’espai utilitzat d’un bloc

  18. Inserts Control de l’espai usat per bloc PCTFREE=20 PCTUSED=40 Inserts Inserts 80% 1 2 80% Inserts 40% 3 4

  19. Data Dictionary Views Used extents DBA_EXTENTS Free extents DBA_FREE_SPACE Segments DBA_SEGMENTS Data files DBA_DATA_FILES Tablespaces DBA_TABLESPACES

  20. Gestionant taules. Creació CREATE TABLE nom( columna tipus [, columna tipus]...) [TABLESPACE nom_tablespace] [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [STORAGE clàusula_storage] [LOGGING|NOLOGGING] [CACHE|NOCACHE] INITRANS: nº de slots per transaccions* preassignades a cada bloc (1 per defecte) MAXTRANS: nº màxim de slots per transaccions* assignades a cada bloc (255 per defecte) LOGGING: s’utilitza un redo log file (les operacions es guarden a un log) CACHE: s’usa una cache a memòria * Un slot per transaccions és un espai reservat que s’utilitza per guardar informació sobre les transaccions que fan canvis al bloc en un moment concret

  21. Gestionant taules. Creació (un exemple) CREATE TABLE employees( empno NUMBER(4), last_name VARCHAR2(30) deptno NUMBER(2)) PCTFREE 20 PCTUSED 50 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE data01;

  22. Gestionant taules • Posar les taules en un tablespace diferent • No amb rollback segments, temporary segments ni índexs • A ser possible a discos diferents per evitar colls d’ampolla • Cada usuari té assignat un tablespace, per exemple els vostres usuaris a users • Utilitzar tamanys d’extent “estàndard” múltiples de 5*DB_BLOCK_SIZE, per minimitzar la fragmentació • Usar la clàusula CACHE per petites taules de referència que s’accedeixen molt freqüentment • Valorar si NOLOGGING per guanyar eficiència (arriscat!)

  23. (Average Row Size - Initial Row Size) * 100 Average Row Size Average Row Size * 100100 - PCTFREE - Available Data Space Gestió de taules. Càlcul de PCTFREE i PCTUSED • Calcular PCTFREE • Un alt PCTFREE suposa més espai per a les modificacions i una menor densitat (menys files per bloc) • Aquesta fórmula assegura que hi hagi espai per fer crèixer les files • Calcular PCTUSED • Quan s’ha de fer una inserció, es fa una búsqueda linial fins a trobar un bloc amb espai suficient (si n’hi ha) • La fórmula pretén reduir el temps d’aquesta búsqueda, de forma que s’incrementi la probabilitat de trobar un bloc amb espai

  24. Gestió de taules. Reassignant l’espai sense usar (després de diversos deletes) • El “High water mark” indica el darrer bloc que ha estat usat • Una lectura de la taula per blocs s’aturarà en arribar-hi • Amb un “deallocate unused” desassignem els blocs no usats dels extents d’un segment perquè es puguin assignar a d’altres Beforedeallocation ALTER TABLE employees DEALLOCATE UNUSED; High water mark Afterdeallocation Used block Unused block Free space after delete

  25. Diccionari de dades de les taules DBA_OBJECTS OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID CREATED DBA_TABLES OWNER TABLE_NAME PCT_FREE PCT_USED INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE CACHE BLOCKS EMPTY_BLOCKS CHAIN_CNT DBA_EXTENTS OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS DBA_SEGMENTS OWNER SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

  26. Exemple amb el diccionari de dades • Exemple: recuperar els extents de la taula empleats SELECT file_id, SUM(blocks) AS Blocks FROM dba_extents WHERE segment_name=‘empleats’;

  27. Validant l’estructura • El gestor Oracle pot verificar la integritat de cada bloc de dades • L’opció CASCADE s’usa per validar l’estructura de tots els índexs sobre la taula, així com les referències creuades entre la taula i els seus índexs ANALYZE TABLE empleats VALIDATE STRUCTURE;

  28. Gestionant índexs: tipus d’índexs • A nivell lògic: • De columna simple o concatenat • Únic o no únic • A nivell físic: • Arbre B o Bitmap • Si Arbre B: de clave normal o revers • Particionat o no particionat • Per a altres tipus de dades hi ha altres tipus d’implementacions dels índexs • Per exemple, R-trees i quadtrees per a dades espacials

  29. Arbre B Index entry Root Branch Index entry header Key column length Leaf Key column value ROWID (veure seg. transp.) Un parell (key column length, key column value) per a cada camp de la clau

  30. Rowid • Rowid normal : • Identificador únic per a una fila • Usat per localitzar una fila • Rowid restringit: • Pot identificar files dins d’un segment • Necessita menys espai OOOOOO FFF BBBBBB RRR Data object number Relative file number Block number Row number . . BBBBBBBB RRRR FFFF Block number Row number File number

  31. Algunes característiques dels arbres B d’Oracle • Si hi ha múltiples files amb els mateixos valors de claus, aquests s’han de repetir a l’arbre • Si una fila té tots els valors de les columnes claus com a NULL, no té entrada a l’índex • S’utilitza Rowid restringit, ja que totes les files pertanyen al mateix segment

  32. Algunes característiques dels arbres B d’Oracle • Les operacions d’insert, resulten també en la inserció d’una entrada a l’índex • Les operacions de delete d’una fila, produeix només un esborrat lògic de l’entrada de l’índex: • l’espai no està disponible fins que no s’hagin esborrat totes les entrades del bloc • Els update de columnes de clau, es corresponen a un esborrat lògic i una inserció a l’índex

  33. Creació d’ún índex amb arbre B (un exemple) CREATE INDEX emp_lname_idx ON employees(last_name) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx01;

  34. Consells per crear índexs • Els índex acceleren consultes però enlenteixen operacions DML (A/B/M) (veure seg. transp.) • Situar l’índex en un tablespace diferent al de la taula (també diferent del rollback i temporary segment) • Utilitzar tamanys d’extent uniformes, típicament múltiples de 5 blocs • L’ús de NOLOGGING per grans índexs pot millorar l’eficiència • Les files d’un índex són més petites que les d’una taula. Això fa que hi hagi moltes més files per bloc que en una taula. Per això, INITRANS és típicament més gran que en una taula • Especificar un alt PCTFREE si és probable que hi hagi insercions de valors entre dues claus (això no passa si les claus son correlatives)

  35. Nombre d’índexs per taula • Una taula pot tenir qualsevol nombre d’índexs • Quant més índexs tingui, més overhead produceixen les modificacions de la taula: • quan s’insereixen o eliminen files, tots els índexs sobre la taula també es modifiquen • quan es modifica una columna, tots els índexs que contenen la columna també es modifiquen • Cal cercar un compromís entre la velocitat de recuperació de les dades des d’una taula (als selects) i la velocitat de modificació de la taula (als inserts, updates, deletes) • Si una taula és bàsicament de lectura, pot ser útil tenir més índexs (un per a cada possible consulta) • Si una taula té moltes modificacions, caldrà utilitzar menys índexs. Cal analitzar en detall quins índexs crear i quins no

  36. Índex revers Index on EMP (EMPNO) EMPNO ENAME JOB ... ----- ----- -------- 7499 ALLEN SALESMAN 7369 SMITH CLERK 7521 WARD SALESMAN ... 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER ... ... ... ... ... ... ... ... EMP table KEY ROWID EMPNO (BLOCK# ROW# FILE#) ----- ------------------- 1257 0000000F.0002.0001 2877 0000000F.0006.0001 4567 0000000F.0004.0001 6657 0000000F.0003.0001 8967 0000000F.0005.0001 9637 0000000F.0001.0001 9947 0000000F.0000.0001 ... ... ... ... • Per evitar que amb claus correlatives tots els valors s’insereixen al mateix lloc de l’arbre • Especialment útil en entorns paral·lelitzats

  37. Creació d’un índex revers (un exemple) CREATE UNIQUE INDEX ord_ord_no_idx ON ord(ord_no) REVERSE PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx01;

  38. Índex Bitmap Fila 3 Taula Bloc 10 • Útil quan: • Taules llargues on la columna clau té baixa cardinalitat (pocs possibles valors) • Quan les consultes utilitzen l’operador OR Bloc 11 Bloc 12 Índex ROWID inicial ROWID final clau bitmap <Blau, 10.0.3, 12.8.3, 1000100100010010100> <Verd, 10.0.3, 12.8.3, 0001010000100100000> <Vermell, 10.0.3, 12.8.3, 0100000011000001001> <Groc, 10.0.3, 12.8.3, 0010001000001000010>

  39. Comparació entre arbres B i índex bitmap

  40. Creació d’un índex bitmap (un exemple) CREATE BITMAP INDEX ord_region_id_idx ON ord(region_id) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx01;

  41. Modificar els paràmetres d’emmagatzematge • Exemple: ALTER INDEX emp_lname_idx STORAGE(NEXT 400KMAXEXTENTS 100);

  42. Reconstruir un índex Utilitzar la comanda de REBUILD per: • Moure un índex a un espai de taules diferent • Millorar l’utilització d’espai eliminant les entrades esborrades • Canviar un índex amb clau reversa a un índex amb arbre B normal o viceversa ALTER INDEX ord_region_id_idx REBUILDTABLESPACE indx02;

  43. Validant un índex ANALYZE INDEX ord_region_id_idx VALIDATE STRUCTURE; INDEX_STATS SELECT blocks, pctused, distinct_keys, lf_rows, del_lf_rows FROM index_stats; Reorganitzar l’índex si el rati del_lf_rows a lf_rows passa del 30%

  44. Clusters i taules organitzades per índex • La disposició de les dades als blocs no és aleatòria • Clusters: organitzats per una clau (o conjunt de claus) • Taules organitzades per índex: els valors estan als nodes fulla de l’arbre B de l’índex

  45. Distribució de files a les taules Table Cluster Index-organizedtable Ordering of Rows Random Grouped Ordered

  46. ORD_NO PROD QTY ... ----- ------ ------ 101 A4102 20 102 A2091 11 102 G7830 20 102 N9587 26 101 A5675 19 101 W0824 10 Cluster Key (ORD_NO) 101 ORD_DT CUST_CD 05-JAN-97 R01 PROD QTY A4102 20 A5675 19 W0824 10 102 ORD_DT CUST_CD 07-JAN-97 N45 PROD QTY A2091 11 G7830 20 N9587 26 ORD_NO ORD_DT CUST_CD ------ ------ ------ 101 05-JAN-97 R01 102 07-JAN-97 N45 Cluster Clustered ORD and ITEM tables (VENDA i LINIA_VENDA) Unclustered ORD and ITEM tables (VENDA i LINIA_VENDA)

  47. Hash cluster Dos tipus de clusters Index cluster Hash function

  48. Creant un cluster. Un exemple create cluster clu (as_id number(3)) tablespace tt; create index ind_clu on cluster clu tablespace ti; create table assignatura( as_id number(3), ... constraint pk_assignatura primary key (as_id) using index tablespace ti ) cluster clu(as_id); create table matricula ( ma_num number(6), ma_al_id number(4) not null, ma_as_id number(3) not null, constraint pk_matricula primary key (ma_num) ) cluster clu(ma_as_id);

  49. Creant un cluster amb Hash. Un exemple CREATE CLUSTER hashclu (as_id number(3)) HASHKEYS 10 TABLESPACE tt;

  50. Exemple d’overflow en un cluster amb hash Key 21 Key 12 Key 11 Key 1 Key 2 Key 3 Key 22 Overflow block Preallocated blocks

More Related