Leggere lo schema di dettaglio di sqlutil

Gli script SQL contenuti in sqlutil sono molto utili per l'amministratore di database Oracle. Tra le varie funzioni disponibili e' presente uno script che genera uno schema di dettaglio dell'RDBMS Oracle (det.sql).

Poiche' lo schema riporta i principali componenti dell'RDBMS Oracle questo documento e' di interesse generale. E' quindi di interesse per utenti, programmatori ed amministratori dell'RDBMS Oracle.

Lo schema e' suddiviso in sezioni, nel seguito sono analizzate le singole sezioni.

Analogo allo script det.sql vi e' lo script dethtml.sql che presenta informazioni analoghe in formato HTML quindi facilmente pubblicabile su Web server.

Versione prodotti

Il questa sezione vengono riportate le versioni dei pacchetti Oracle installati. L'RDBMS Oracle infatti ha un numero di versione (composto da diverse cifre) e puo' essere installato con diversi altri pacchetti opzionali e non.

version
-------
Oracle7 Server Release 7.2.3.0.0 - Production Release
PL/SQL Release 2.2.3.0.0 - Production
CORE Version 3.4.3.0.0 - Production
TNS for SVR4: Version 2.2.3.0.0 - Production
NLSRTL Version 3.1.4.6.0 - Production

 

Utilizzo della memoria

Per avere una maggior efficienza nell'accesso ai dati, Oracle mantiene un insieme di strutture in memoria. Tale area di memoria, utilizzata in condivisione tra i diversi processi di Oracle, e' chiamata System Global Area (SGA).

Tra le informazioni in essa contenute si trovano i buffer dei dati (Database Buffer), le principali tabelle del dizionario dati e gli statement SQL compilati ed attivi (allocati con il parametro di SHARED_POOL_SIZE), i buffer dei redo log.

Questa sezione riporta i nomi delle principali aree e la relativa dimensione.

sga                  bytes
-------------------- ---------------
Fixed Size           49772
Variable Size        9872788
Database Buffers     8192000
Redo Buffers         163840

 

Utenti e privilegi

La gestione dei diritti e della sicurezza in Oracle e' molto sofisticata.

E' possibile fornire diritti su attivita' di sistema (collegarsi, creare una tabella, creare un indice, esportare i dati, ...) o sull'accesso ad oggetti (selezionare/inserire/modificare/cancellare dati da tabelle/viste, ...). I diritti possono essere assegnati ad utenti specifici o a ruoli. A loro volta uno o piu' ruoli possono essere assegnati agli utenti.

Un utente puo' creare oggetti su uno o piu' tablespace. La quantita' massima di spazio utilizzabile e' anch'essa definibile. Per creare un oggetto su un determinato tablespace e' utilizzabile il relativo parametro di storage, in caso contrario l'oggetto viene creato sul tablespace di default associato all'utente.

Durante particolari operazioni (ordinamenti, creazioni di indici, ...) puo' essere necessario utilizzare aree temporanee per memorizzare i risultati dei vari passaggi intermedi. Ad ogni utente puo' essere assegnato un tablespace su cui creare i segmenti temporanei.

Poiche' l'insieme di dati relativi agli accessi ed alla sicurezza e' in genere molto ampio, in questa sezione vengono riportate solo alcune informazioni: gli utenti, i ruoli loro assegnati, il tablespace di default ed il tablespace per i temporanei.

username        role def.       tabspace tmp.   tabspace
--------------- --------------- --------------- ---------------
HELPDESK        CONNECT         USERS           IDX
SCOTT           CONNECT         TOOLS           TOOLS
SYS             CONNECT         SYSTEM          SYSTEM
SYS             DBA             SYSTEM          SYSTEM
SYS             EXP_FULL_DATABA SYSTEM          SYSTEM
SYS             IMP_FULL_DATABA SYSTEM          SYSTEM
SYS             RESOURCE        SYSTEM          SYSTEM
SYSTEM          DBA             TOOLS           TOOLS
TPM             CONNECT         USERS           TEMP
TPM             RESOURCE        USERS           TEMP

 

Spazi definiti

Ogni tablespace e' composto fisicamente da piu' datafile. Ogni datafile corrisponde ad un file del sistema operativo o ad un dispositivo fisico (raw device) e naturalmente ha una dimensione associata. Lo spazio totale di un tablespace corrisponde alla somma degli spazi dei singoli datafile che lo compongono.

Su ogni datafile vengono effettuate letture e scritture fisiche.

TABLESPACE DATA_FILE                           BYTES      Letture    Scritture
---------- ----------------------------------- ---------- ---------- ----------
IDX        /dev/vx/rdsk/datadg/ora01            524285952         81       2148
RBS        /usr1/oracle/product/7.2.3/dbs/rbs.   20971520          9       3078
SYSTEM     /usr1/oracle/product/7.2.3/dbs/syst  104857600        607         17
TOOLS      /uisr1/oracle/product/7.2.3/dbs/too   15728640          1          0
USERS      /dev/vx/rdsk/datadg/ora02            524285952        467        797

 

Utilizzo dello spazio

Lo spazio in Oracle e' suddiviso in tablespace. Lo spazio sui tablespace viene preallocato ed e' quindi interamente ed immediatamente disponibile al database. All'interno del tablespace vengono allocati gli extent (le porzioni) di tutti gli oggetti creati. Quindi ciascun oggetto creato (sia esso una tabella, indice, rollback, ..) e' composto da un certo numero di extent. In genere si ottiene la maggior efficienza mantenendo limitato il numero di extent (in tal modo le operazioni avvengono su spazi disco contigui). Oracle consente una definizione molto precisa sulle moldalita' di crescita di ogni singolo oggetto (parametri di storage).

Un extent viene creato se esiste sufficiente spazio contiguo per la sua creazione. Nel caso in cui non vi sia sufficiente spazio contiguo l'operazione richiesta viene abortita e viene segnalato un errore.

In questa sezione sono riportati:

tabspace     total      occuped    max_free   max_next   max_ext
------------ ---------- ---------- ---------- ---------- --------
IDX           524285952  211701760   44134400    3092480        3
RBS            20971520   10547200    3440640    1048576        4
SYSTEM        104857600   12251136   86392832    1048576        5
TOOLS          15728640    2068480   13658112      10240        1
USERS         524285952  208791552  178165760    7364608        2

 

Transazioni e Rollback Segments

Oracle gestisce in maniera completa le transazioni. Questo significa che se, per un qualsiasi motivo, una transazione non va a buon fine Oracle ripristina la situazione precedente. Inoltre ogni dato inserito o modificato non e' visibile ad altre transazioni fino a quando non e' stato confermato (COMMIT).

Per mantenere le informazioni precedenti Oracle utilizza particolari strutture dette segmenti di rollback. Dal punto di vista dello spazio i rollback sono gestiti come le altre strutture dati: trovano quindi spazio su un tablespace, sono composti da piu' extent, ... Un rollback puo' essere posto fuori linea, in tal caso non viene utilizzato per dalle transazioni.

I dati riportati in questa sezione sono:

segment          tablespace      bytes           extents status
---------------- --------------- --------------- ------- ------------
R01              RBS                     2109440       2 ONLINE
R02              RBS                     2109440       2 ONLINE
R03              RBS                     2109440       2 ONLINE
R04              RBS                     2109440       2 ONLINE
SYSTEM           SYSTEM                   204800       4 ONLINE

 

Recupero dati e Log Files

Oracle e' in grado di ripristinare i dati a fronte di eventuali cadute di programmi applicativi, dell'RDBMS o del sistema operativo ospite stesso. Per far cio' al termine di ogni transazione avviene una scritttura su disco. La tecnica utilizzata (che fornisce le migliori prestazioni e consente il rispristino a freddo di transazioni) sfrutta un insieme di file di log. I log vengono utilizzati circolarmente, terminato un file di log si passa quindi al sucessivo.

In questa sezione sono presenti i nomi dei file di log e la loro dimensione.

log file                                      bytes
--------------------------------------------- ---------------
/usr1/oracle/product/7.2.3/dbs/log1a.dbf              1048576
/usr1/oracle/product/7.2.3/dbs/log2a.dbf              1048576
/usr1/oracle/product/7.2.3/dbs/log3a.dbf              1048576

 

Frammentazione

Gli oggetti in oracle sono composti da extent. La presenza di un numero eccessivo numero di extent porta a basse prestazioni. E' infatti necessario effettuare piu' seek su disco per accedere a tutti i dati presenti in una tabella.

In questa sezione vengono riportati tutti gli estremi degli oggetti la cui frammentazione e' superiore a cinque extent.

tabspace   owner      segment          type    bytes        extents
---------- ---------- ---------------- -----   ------------ ---------
SYSTEM     SYS        C_OBJ#           CLUSTER       993280        10
SYSTEM     SYS        IDL_UB2$         TABLE         593920         9
SYSTEM     SYS        VIEW$            TABLE         266240         7
...

 

Oggetti definiti per utente

Sull'RDBMS Oracle vengono mantenuti diversi oggetti. Per il DBS e' necessario poterli gestire con efficacia.

Tutti i dati su un database relazionale vengono mantenuti su tabelle. Su un'istanza Oracle sono gestiti piu' utenti e ciascuno puo' possedere piu' tabelle.

Per consentire un piu' efficiente accesso vengono utilizzati gli indici. Ogni tabella puo' avere associati piu' indici univoci o non univoci.

Oracle consente la creazione di trigger. Si tratta di particolari "programmi" che vengono scatenati da eventi (quali l'inserimento di un record, la cancellazione, ...) associati alle tabelle. Il trigger vengono scritti in PL/SQL un estensione procedurale del linguaggio SQL.

E' possibile creare packages che sono un insieme di funzioni PL/SQL. Oracle mantiene i packages in forma precompilata fornendo cosi' migliori prestazioni.

Per generare chiavi univoche Oracle fornisce le sequence. Le sequence sono particolari oggetti che, ad ogni lettura, restituscono cempre valori differenti (tipicamente crescenti). L'utilizzo delle sequence e' semplice, molto efficiente e non richiede l'effettuazione di transazioni.

In Oracle e' possibile definire sinonimi con cui accedere ad oggetti di altri utenti o con nomi complessi.

Su tutti gli RDBMS relazionali e' possibile definire viste. Le viste sono tabelle logiche i cui dati vengono ricavati con le relative selezioni.

In questa sezione vengono riportati gli utenti definiti ed il numero di oggetti di loro proprieta' siano essi tabelle, indici, trigger, package, sequenze, sinonimi, viste. Viene inoltre riportato il totale degli oggetti (attenzione il totale tiene anche conto dei database link, procedure e package body)... Questa sezione e' di notevole utilita' quando vengono effettuate operazioni di riorganizzazione del database.

OWNER            TABS  IDXS  TRGS  PKGS  SEQS  SYNS  VIES  ALLS
--------------- ----- ----- ----- ----- ----- ----- ----- -----
PUBLIC              0     0     0     0     0   348     0   348
SCOTT               5     0     0     0     0     0     0     5
SYS                78    68     0    29     9     5   410   640
SYSTEM             22    26     0     0     2    59    16   125
TPM                 1     1     0     0     0     0     0     2

 

Parametri

L'RDBMS Oracle ha un elevatissimo grado di parametrizzazione. I parametri di configurazione vengono specificati nel file initX.ora. I Parametri di Oracle sono fondamentali per il tuning della base dati.

In questa sezione vengono riportati tutti i parametri che non hanno valori di default.

PARAM                          VALUE
------------------------------ ---------------------------------------------
audit_trail                    NONE
background_dump_dest           /usr1/oracle/product/7.2.3/rdbms/log
cache_size_threshold           400
control_files                  /usr1/oracle/product/7.2.3/dbs/ctrl1a.ctl,
                               /usr1/oracle/product/7.2.3/dbs/ctrl2a.ctl,
                               /usr1/oracle/product/7.2.3/dbs/ctrl3a.ctl
core_dump_dest                 /usr1/oracle/product/7.2.3/dbs
cpu_count                      4
db_block_buffers               4000
db_block_size                  2048
db_file_multiblock_read_count  8
db_files                       20
db_name                        prova
distributed_transactions       31
dml_locks                      200
enqueue_resources              265
gc_db_locks                    4000
ifile                          /usr1/oracle/product/7.2.3/dbs/configa.ora
log_archive_dest               /usr2/orabck/arc/arch.log
log_archive_start              TRUE
log_buffer                     163840
log_checkpoint_interval        10000000
log_simultaneous_copies        4
max_dump_file_size             10240
mts_max_dispatchers            0
mts_max_servers                0
mts_servers                    0
mts_service                    a
optimizer_mode                 CHOOSE
processes                      100
remote_login_passwordfile      NONE
rollback_segments              r01, r02, r03, r04
sequence_cache_entries         100
sequence_cache_hash_buckets    89
sessions                       115
shared_pool_size               8000000
sort_area_retained_size        65536
temporary_table_locks          115
transactions                   126
transactions_per_rollback_segm 16
user_dump_dest                 /usr1/oracle/product/7.2.3/rdbms/log
 

Dati storici

Poiche' il risultato dello script det.sql e' un file di testo e' molto utile raccogliere periodicamente tale file in modo da disporre di una storia, per altro molto dettagliata, dell'utilizzo dell'RDBMS Oracle.

Indicazioni molto interessanti sono anche riportate dagli altri script distribuiti con sqlutil (in particolare perf.sql e schema7u.sql). E' consigliabile salvare periodicamente anche il risultato di tali script. Per una descrizione delle statistiche ottenute con perf.sql consultare il documento Statistiche prestazionali di Oracle.

Note

Questo documento e' riferito alla versione 2.4 dello script det.sql contenuto in sqlutil. Nuove utili informazioni vengono continuamente aggiunte in particolare quando vengono distribuite nuove versioni dell'RDBMS Oracle. Il software e' stato realizzato e posto in pubblico dominio e' quindi liberamente utilizzabile.

Analogo allo script det.sql vi e' lo script dethtml.sql che presenta informazioni analoghe in formato HTML quindi facilmente pubblicabile su Web server.

Piu' veloce da eseguire e' lo script gen.sql che riporta solo alcune delle informazioni riportate da det.sql. Ovviamente i contenuti di questo documento si applicano ad entrambe le utility.

Il database utilizzato quale esempio e' un RDBMS Oracle 7.2.3, analoghe informazioni si ottengono da database Oracle 7.X.


Testo: Leggere lo schema di dettaglio di sqlutil
Data: 30 Giugno 1997
Versione: 1.1.1
Autore: mail@meo.bogliolo.name