Aspectes d’administració de l’emmagatzematge d’ Oracle
Download
1 / 58

Toni Navarrete Enginyeria del Software II – UPF 2002 - PowerPoint PPT Presentation


  • 70 Views
  • Uploaded on

Aspectes d’administració de l’emmagatzematge d’ Oracle (parcialement basat en la Guia de l’estudiant “Oracle 8: Database Administration”). Toni Navarrete Enginyeria del Software II – UPF 2002. Normalització: 1FN. Una relació R està en 1FN si i només si cada columna conté només valors atòmics

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Toni Navarrete Enginyeria del Software II – UPF 2002' - ace


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Aspectes d’administració de l’emmagatzematge d’ Oracle(parcialement basat en la Guia de l’estudiant “Oracle 8: Database Administration”)

Toni Navarrete

Enginyeria del Software II – UPF 2002


Normalitzaci 1fn
Normalització: 1FN Oracle

  • Una relació R està en 1FN si i només si cada columna conté només valors atòmics

  • (un atribut només pot tenir un valor)


Normalitzaci 2fn
Normalització: 2FN Oracle

  • Una relació R està en 2FN si i només si està en 1FN i tots els atributs que no formen part de la clau primària depenen funcionalment d’aquesta


Normalitzaci 3fn
Normalització: 3FN Oracle

  • Dependència no transitiva: és una relació que té almenys tres atributs (A,B,C), on A determina B, B determina C, però C no determina A

  • Una relació R està en 3FN si i només si està en 2FN i tots els seus atributs que no pertanyen a la clau primària depenen no transitivament d’aquesta

  • (no hi haurà cap atribut que depengui d’un altre que no sigui clau primària; si fos així, han d’anar a una altra relació)


Normalitzaci fnbc forma normal de boyce codd
Normalització: FNBC (Forma Normal de Boyce/Codd) Oracle

  • Determinant: Un o més atributs que, de manera funcional, determinen un altre atribut o atributs. Per exemple, en la dependència funcional (A,B)-->C, (A,B) són els determinants

  • Una relació R està en FNBC si i només si cada determinant és part de la clau primària

  • (si trobem una dependència, el determinant d’aquesta formarà part de la clau)


Estructura de l emmagatzematge a oracle
Estructura de Oraclel’emmagatzematge a Oracle

Database

Tablespace

Data file

Logical

Physical

Segment

Extent

Oracle block

O/S Block


Qu s un espai de taules tablespace
Què és un espai de taules (tablespace)? Oracle

  • É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

  • N’hi ha un espai de taules anomenat SYSTEM


Tablespaces system i no system

SYSTEM Tablespace Oraclecontains:

Data dictionary information

SYSTEM rollback segment

Tablespaces SYSTEM i no SYSTEM

  • Non-SYSTEMTablespace contains:

    • Rollback segments

    • Temporary segments

    • Application data

    • Application indexes


Com es crea un tablespace
Com es crea un tablespace? Oracle

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);


Storage
Storage Oracle

  • 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


Storage1
Storage Oracle

  • En aquest primer extent es posen dades de control del segment i l’espai restant s’usa per insercions de files

  • Quan és ple, es crea un segon

    • El seu tamany vé 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 (redondejant 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


Exemple storage
Exemple Storage Oracle

  • 1er extent: 5 blocs = 20K

  • 2on extent: 5 blocs = 20K

  • 3er extent: 10 blocs (5*1,5=7,5 redondejats) = 40K

  • 4rt extent: 15 blocs (7,5*1,5=11,25) = 60K

  • 5è extent: 20 blocs (11,25*1,5=16,875) = 80K


Com obtenim informaci dels tablespaces existents
Com obtenim informació dels tablespaces existents? Oracle

  • 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;

  • Els comptes d’usuari es2_xx només tenen permís per veure els tablespaces definits pel propi usuari (no poden accedir a dba_tablespaces)

    • user_tablespaces


Recomanaci
Recomanació Oracle

  • A la taula del diccionari de dades dba_extents (user_extents) tenim els extents

  • Si un segment té més de 10 extents és convenient refer la taula

  • Abans de crear (o refer) una taula, convé calcular l’espai de les dades que ja té per assignar-ho al primer extent


Tipus de segments 1 3

Cluster Oracle

Tipus de segments (1/3)

Table

Table partition

Index


Tipus de segments 2 3

Index-organized Oracletable

Tipus de segments (2/3)

Index partition

Rollbacksegment

Temporarysegment


Tipus de segments 3 3
Tipus de segments (3/3) Oracle

LOBsegment

LOB index

Nested table

Bootstrapsegment


Jerarquia de les cl usules d emmagatzematge
Jerarquia de les clàusules d’emmagatzematge Oracle

  • 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


Unificant extents lliures colindants evitant fragmentacions
Unificant extents lliures colindants (evitant fragmentacions)

  • Això passa, per exemple, si s’esborren dues taules consecutives

  • Per determinar els exten fragmentats:

    • Vista DBA_FREE_SPACE_COALESCED

    • Nota: “coalesce”=fusionarse, unirse

  • Consulta:

    SELECT tablespace_name, total_extents, percent_extents_coalesced

    FROM dba_free_space_coalesced

    WHERE percent_extens_coalesced <>100;


Unificant extents lliures colindants evitant fragmentacions1

Before fragmentacions)

After

File header

Used extent

Free extent

Unificant extents lliures colindants (evitant fragmentacions)

ALTER TABLESPACE data01 COALESCE;


Control de l espai usat per bloc
Control de l’espai usat per bloc fragmentacions)

  • 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


Control de l espai usat per bloc1

Inserts fragmentacions)

Control de l’espai usat per bloc

PCTFREE=20 PCTUSED=40

Inserts

Inserts

80%

1

2

80%

Inserts

40%

3

4


Data dictionary views
Data Dictionary Views fragmentacions)

Used extents

DBA_EXTENTS

Free extents

DBA_FREE_SPACE

Segments

DBA_SEGMENTS

Data files

DBA_DATA_FILES

Tablespaces

DBA_TABLESPACES


Gestionant taules creaci
Gestionant taules. Creació fragmentacions)

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 transaccions preassignades a cada bloc (1 per defecte)

MAXTRANS: nº màxim de transaccions assignades a cada bloc (255 per defecte)

LOGGING: s’utilitza un redo log file (les operacions són es guarden a un log)

CACHE: s’usa una cache a memoria


Gestionant taules creaci un exemple
Gestionant taules. Creació (un exemple) fragmentacions)

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;


Gestionant taules
Gestionant taules fragmentacions)

  • 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

    • Per defecte, els vostres segments es creen al tablespace 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!)


Gesti de taules c lcul de pctfree i pctused
Gestió de taules. Càlcul de PCTFREE i PCTUSED fragmentacions)

  • Compute PCTFREE

(Average Row Size - Initial Row Size) * 100

Average Row Size

  • Compute PCTUSED

Average Row Size * 100100 - PCTFREE - Available Data Space


Gesti de taules reassignant l espai sense usar despr s de diversos deletes
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

  • Amb un deallocate unused desassignem els blocs no usat 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


Diccionari de dades de les taules
Diccionari de dades de les taules (després de diversos deletes)

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


Exemple amb el diccionari de dades
Exemple amb el diccionari de dades (després de diversos deletes)

  • Exemple: recuperar els extents de la taula empleats

    SELECT file_id, SUM(blocks) AS Blocks

    FROM dba_extents

    WHERE segment_name=‘empleats’;


Validant l estructura
Validant l’estructura (després de diversos deletes)

  • The Oracle server verifies the integrity of each data block.

  • Use the CASCADE option to validate the structure of all indexes on the table, and perform cross-referencing between the table and its indexes.

ANALYZE TABLE empleats

VALIDATE STRUCTURE;


Gestionant ndexs tipus d ndexs
Gestionant índexs: tipus d’índexs (després de diversos deletes)

  • 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


Arbre b
Arbre B (després de diversos deletes)

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


Rowid
Rowid (després de diversos deletes)

  • Normal Rowid:

    • Unique identifier for a row

    • Used to locate a row

  • Restricted Rowid:

    • Can identify rows within a segment

    • Needs less space

OOOOOO

FFF

BBBBBB

RRR

Data object number

Relative file number

Block number

Row number

.

.

BBBBBBBB

RRRR

FFFF

Block number

Row number

File number


Algunes caracter stiques dels arbres b d oracle
Algunes característiques dels arbres B d’Oracle (després de diversos deletes)

  • Si hi ha múltiples files amb els mateixos valors de claus, aquests s’han de repetir a l’arbre

  • No hi ha entrada a l’índex si per a una fila, tots els valors de les columnes clau són NULL

  • S’utilitza Rowid restringit, ja que totes les files pertanyen al mateix segment


Algunes caracter stiques dels arbres b d oracle1
Algunes característiques dels arbres B d’Oracle (després de diversos deletes)

  • 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


Creaci d n ndex amb arbre b un exemple
Creació d’ún índex amb arbre B (un exemple) (després de diversos deletes)

CREATE INDEX emp_lname_idx

ON employees(last_name)

PCTFREE 30

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MAXEXTENTS 50)

TABLESPACE indx01;


Consells per crear ndexs
Consells per crear índexs (després de diversos deletes)

  • 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. 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)


Nombre d ndexs per taula
Nombre d’índexs per taula (després de diversos deletes)

  • A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated.

  • Thus, there is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes may be preferable.


Ndex revers
Índex revers (després de diversos deletes)

Index on EMP (EMPNO)

EMP table

EMPNO ENAME JOB ...

----- ----- --------

7499 ALLEN SALESMAN

7369 SMITH CLERK

7521 WARD SALESMAN ...

7566 JONES MANAGER

7654 MARTIN SALESMAN

7698 BLAKE MANAGER

7782 CLARK MANAGER

... ... ... ...

... ... ... ...

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


Creaci d un ndex revers un exemple
Creació d’un índex revers (un exemple) (després de diversos deletes)

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;


Ndex bitmap

start (després de diversos deletes)

ROWID

end

ROWID

key

bitmap

<Blue, 10.0.3, 12.8.3, 1000100100010010100>

<Green, 10.0.3, 12.8.3, 0001010000100100000>

<Red, 10.0.3, 12.8.3, 0100000011000001001>

<Yellow, 10.0.3, 12.8.3, 0010001000001000010>

Índex Bitmap

File 3

Table

Block 10

  • Útil quan:

    • Taules llargues on la columna clau té baixa cardinalitat (pocs possibles valors)

    • Quan les consultes utilitzen l’operador OR

Block 11

Block 12

Index


Comparaci entre arbres b i ndex bitmap
Comparació entre arbres B i índex bitmap (després de diversos deletes)

B-tree

Suitable for high-cardinality columns

Updates on keys relativelyinexpensive

Inefficient for queries using OR predicates

Bitmap

Suitable for low-cardinality columns

Updates to key columns veryexpensive

Efficient for queries using OR predicates


Creaci d un ndex bitmap un exemple
Creació d’un índex bitmap (un exemple) (després de diversos deletes)

CREATE BITMAP INDEX ord_region_id_idx

ON ord(region_id)

PCTFREE 30

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MAXEXTENTS 50)

TABLESPACE indx01;


Modificar els par metres d emmagatzematge
Modificar els paràmetres d’emmagatzematge (després de diversos deletes)

  • Exemple:

    ALTER INDEX emp_lname_idx

    STORAGE(NEXT 400KMAXEXTENTS 100);


Reconstruir un ndex
Reconstruir un índex (després de diversos deletes)

Use this command to:

  • Move an index to a different tablespace

  • Improve space utilization by removing deleted entries

  • Change a reverse key index to a normal B-tree index and vice versa

ALTER INDEX ord_region_id_idx REBUILDTABLESPACE indx02;


Validant un ndex
Validant un índex (després de diversos deletes)

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%


Manegar integritat constraints
Manegar integritat (constraints) (després de diversos deletes)

  • Tipus de constraints:

    • NOT NULL

    • UNIQUE

    • PRIMARY KEY

    • FOREIGN KEY

    • CHECK

  • Pot ser diferida o no diferida (immediata)

    • “diferida” indica que espera a acabar una transacció per fer la validació


Is an index (després de diversos deletes)available for use?

Yes

Yes

Key enabled?

Use existingindex

No

No

Yes

Constraintdeferrable?

No

Create nonunique index

Do not useindex

Createunique index


Definint constraints quan es crea la taula un exemple
Definint constraints quan es crea la taula (un exemple) (després de diversos deletes)

CREATE TABLE employees(empno NUMBER(4) CONSTRAINT emp_pk PRIMARY KEY DEFERRABLE USING INDEX STORAGE(INITIAL 100K NEXT 100K) TABLESPACE indx01, last_nameVARCHAR2(30) CONSTRAINT emp_ln_nn NOT NULL,

deptno NUMBER(2))

TABLESPACE data01;


Estat d una constraint
Estat d’una constraint (després de diversos deletes)

  • Una constraint pot tenir tres estats:

Disabled

Enablednovalidate

Enabledvalidate

New data

Existing data


Disabling constraints
Disabling Constraints (després de diversos deletes)

  • Disable before bulk load, especially self-referencing foreign keys.

  • Disable referencing foreign keys before disabling parent keys.

  • Unique indexes are dropped, but nonunique indexes are retained.

ALTER TABLE departmentsDISABLE CONSTRAINT dept_pk CASCADE;


Enabling constraints
Enabling Constraints (després de diversos deletes)

No locks on table

Primary/unique keys must use nonunique indexes

Enable NOVALIDATE

ALTER TABLE departmentsENABLE NOVALIDATE CONSTRAINT dept_pk;


Enabling constraints1
Enabling Constraints (després de diversos deletes)

  • Locks table

  • Can use unique or nonunique indexes

  • Needs valid table data

Enable VALIDATE

ALTER TABLE employeesENABLE VALIDATE CONSTRAINT emp_dept_fk;


Consells per definir constraints
Consells per definir constraints (després de diversos deletes)

  • Primary and unique constraints:

    • Place indexes in a separate tablespace

    • Use nonunique indexes if bulk loads are frequent

  • Self-referencing foreign keys:

    • Define or enable foreign keys after initial load

    • Defer constraint checking


Diccionari de dades
Diccionari de dades (després de diversos deletes)

DBA_CONSTRAINTS

OWNER

CONSTRAINT_NAME

CONSTRAINT_TYPE

TABLE_NAME

SEARCH_CONDITION

R_OWNER

R_CONSTRAINT_NAME

DELETE_RULE

STATUS

DEFERRABLE

DEFERRED

VALIDATED

GENERATED

BAD

LAST_CHANGE

DBA_CONS_COLUMNS

OWNER

CONSTRAINT_NAME

TABLE_NAME

COLUMN_NAME

POSITION


Altres temes
Altres temes (després de diversos deletes)

  • Manegar clusters i taules organitzades per índex

  • Partició d’una taula

  • SQL: manegar Joins de forma eficient

  • Establir plans de consultes

  • Altres temes d’administració no lligats amb la pràctica:

    • Gestió d’usuaris i privilegis


Bibliografia
Bibliografia (després de diversos deletes)

  • Oracle8 Database Administration. Oracle Education

  • La biblia de Oracle8. Anaya Multimedia

  • Guy Harrison: Oracle SQL, High-Performance Tunning


ad