Oracle

Koennen Arrays oder Records im Spreicher Performance gegen die Nutzung von kleinen Tabellen mitbringen?
Es ist schwierig zu beweisen welche Möglichkeit die beste ist, da es von der Belastung der Datenbank, die Konfiguration und andere Parameters abhängig ist, deswegen werde ich hier zwei Prozesse ausführen, deren Abläufe ich analysieren werde. Damit sehen wir, welcher Prozess sich besser im idealen Fall benehmt.

Beispiel

Wir haben eine WEB-Applikation entwieckelt, die in jedem Moment auf verschiedenen Servern laufen kann. Deswegen haben wir in einer Tabelle die vollständigen Addressen unseres WEB-Systemes gespeichert. Als einen Benutzer sich in unserem System einloggt, wird die aktuelle Addresse der Aplikation mit Hilfe der “Addresse” Tabelle erteilen. Gleichseitig andere Teile unseres System werden Programme ausführen, die mit PHP entwickelt wurden, die durch eine http Request Ausführung benutzt werden. Das heisst, die betroffene Tabelle wird häufig benutzt und sie hat sich in einem Problem umgewandelt. Wie Können mit diesem Problem umgehen? Mein Vorschlag ist die Nutzung von Arrays. Wir werden den Inhalt der Tabelle im einem Array speichern, der als Variable deklariert ist, und der immer im Cache sein wird. Mit dieser Vorgehensweise werden wir die Abfragen aus der Tabelle vermeiden und das Problem wird beheben.

Unsere Tabelle ist innerhalb einer Funktion eines Pakets abgefragt. Die Funktion gilt als Schnittstelle für die Anwendungen, die die Adressen des Servers benötigen. Das betroffene Paket werden wir für unseres Beispiel verdoppeln, die Kopie des Pakets wird die nachgefragte Adresse eines Servers von dem Array zurückgeben, anstatt der Tabelle zu benutzen.

Beschreibung der betroffenen Objekten:

1. Zwei Tabellen werden angelegt, deren Inhalt gleich sein wird. Grund dafür ist, dass für jeden Ablauf unserer zwei unterschiedlichen Prozesse die benutzten Tabellen sich nicht im Cache finden und deswegen muss jeder Ablauf durch Oracle analysiert werden.

hostname_mapping
hostname_mapping_array

2. Zwei Paketen werden angelet, die beiden werden die Tabelle bzw. Array als Quelle der Daten benutzen.

bis_utils
bis_utils_array

3. Neues “pa_table_to_array” Paket. Dieses Paket wird die Abfrage der Tabelle durch eine Suche aus der Daten des Array ersetzen.

pa_table_to_array

4. Um die Häufigkeit der Nutzung der Tabelle innerhalb des Systems werden wir zwei PL/SQL Skripten generieren. Jedes Skript enthält zwei Schleife, in deren 100.000 Ausführungen von den Funktionen bis_utils.get_host_url bzw. bis_utils_array.get_host_url durchgeführt werden. Innerhalb der PL/SQL Skripten wird die “trace” aktiviert und die verbrauchte Zeit jeder Schleife protokoliert.

pl_get_host_url_array.sql
pl_get_host_url_table.sql

5. Um einen realistischen Ablauf zu reproduzieren, werden wir ein UNIX-Shell-Skript entwickeln, das alternativ jedes PL/SQL Skript ausführen wird. Es wird auch das Ergebeniss in einer Datei protokollieren.

6. Am Ende der Ausführung des UNIX-Shell-Skriptes werden wir vier .trc Dateien auf dem /usr/lib/oracle/xe/app/oracle/admin/XE/udump Verzeichnis. Diese sind nicht lesbar, es sei denn, dass wir sie mit dem “tkprof” Oracles Befehls umwandeln:

tkprof datei_name.trc datei_name.out explain=fran/(PASSWORD)

-rw-r—– 1 oracle dba 29427133 2009-08-18 23:19 xe_ora_5478.trc
-rw-r—– 1 oracle dba 3809 2009-08-18 23:19 xe_ora_5482.trc
-rw-r—– 1 oracle dba 29429620 2009-08-18 23:20 xe_ora_5485.trc
-rw-r—– 1 oracle dba 3809 2009-08-18 23:20 xe_ora_5568.trc

Ergebnis:

Das Ergebnis zeigt, dass der Ablauf der “array” Funktion mehrfach schneller als der Ablauf der “table” Funktion ist:

* Ablauf der “TABLE” Funtkion:

Beginn des Prozess NORMAL, der die Tabelle benutzt
Ende des Prozess NORMAL. Zeit verbraucht : 16.24 seconds
Beginn des Prozess NORMAL, der die Tabelle benutzt
Ende des Prozess NORMAL. Zeit verbraucht : 16.2 seconds

* Ablauf der “ARRAY” Funtkion:

Beginn des Prozess ARRAY, der den Array benutzt
Ende des Prozess ARRAY. Zeit verbraucht : .97 seconds
Beginn des Prozess ARRAY, der den Array benutzt
Ende des Prozess ARRAY. Zeit verbraucht : .98 seconds

Analyse des Ergebnisses der trc Dateien:

* TRC-Datei des Ablaufes der Tabelle:

SELECT DECODE(H.PROTOCOL, NULL, ”, ”, ”, H.PROTOCOL || ‘://’) ||
H.HOSTNAME || DECODE(H.PORT, NULL, ”, ”, ”, ‘:’ || TO_CHAR(H.PORT))
FROM
HOSTNAME_MAPPING H WHERE H.HOSTALIAS = UPPER(:B2 ) AND H.ENTERPRISE_ID = :B1

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 200000 7.40 4.76 0 0 0 0
Fetch 200000 15.39 8.36 0 600000 0 200000
——- —— ——– ———- ———- ———- ———- ———-
total 400001 22.79 13.12 0 600000 0 200000

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 4 0.00 0.00 0 0 0 0
Execute 5 10.63 19.92 0 3 0 3
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 9 10.63 19.92 0 3 0 3

Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 200000 7.40 4.76 0 0 0 0
Fetch 200000 15.39 8.36 0 600000 0 200000
——- —— ——– ———- ———- ———- ———- ———-
total 400001 22.79 13.12 0 600000 0 200000

* TRC-Datei des Ablaufes des Array:

SELECT *
FROM
HOSTNAME_MAPPING_ARRAY

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 8
——- —— ——– ———- ———- ———- ———- ———-
total 3 0.00 0.00 0 3 0 8

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 4 0.00 0.00 0 0 0 0
Execute 5 1.90 1.91 0 3 0 3
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 9 1.90 1.91 0 3 0 3

Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 8
——- —— ——– ———- ———- ———- ———- ———-
total 3 0.00 0.00 0 3 0 8

Umgebung:

1. Betriebssystem: Ubuntu Server 9.04
2. Datenbank: Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production.

Skripten, LOG-Dateien und “trc” Dateien:

mk_get_host_url.sql
cr_struktuk_get_host.sql
ins_get_host.sql
ins_get_host_array.sql
pa_table_to_array_ph.sql
pa_table_to_array_pb.sql
bis_utils_ph.sql
bis_utils_pb.sql
bis_utils_array_ph.sql
bis_utils_array_pb.sql
pl_get_host_url_table.sql
pl_get_host_url_array.sql
get_host_url.sh
get_host_url.log
xe_ora_5568.trc
xe_ora_5568.out
xe_ora_5478.trc
xe_ora_5478.out
xe_ora_5482.trc
xe_ora_5482.out
xe_ora_5485.trc
xe_ora_5485.out

Nach der Installation der Datenbank finden sich alle pre-definierten Benutzer der Datenbank blockiert, ausgenommen die “sys” und “system” Benutzer, die, da sie zu vielen Privilegien haben, nicht für unsere Tests benutzen möchten. Deswegen werden wir unseres eigene Konto anlegen.

Beispiel:

Um das Konto anzulegen, müssen wir folgenden Schritten folgen:

1. Einloggen auf der Datenbank unter dem “system” Benutzer


fran@seat:~$ sqlplus system/XXXXXX


2. Anlagen des neuen Benutzers


SQL> create user fran identified by XXXXXX ;


3. Die gewünschten Privielegen den Benutzer erteilen


SQL> grant dba to fran ;

Ergebnis:

Ein Bild ist Wertvoller als ein Tausend Worte – Sagt man auf Spanisch:

Continue reading »

Normalerweise entwickle ich bei meiner Arbeit aber nicht immer. Was passiert, wenn ich zu Hause etwas probieren möchte oder bei einem Freund? muss ich immer die Installation einer Oracles Datenbank neu durchführen? Oder muss ich noch ein Mal alles installieren, weil ich einen Fehler gemacht habe und habe ich die Datenbank komplett zerstört. Um wo ich möchte entwickeln zu können oder furchtlos etwas neu zu probieren, habe ich Oracle unter Linux – Ubuntu – auf einer virtuellen Maschine installiert. Hierbei werde ich erklären, was und wie ich es installiert habe.

Vorbereitung:

1. Ubuntu Server herunterladen

Ich habe die Ubuntu Server 9.04 Version installiert, da ich nicht viel brauche und echt Stabil ist. Anbei den Link zu dieser Version:
Ubuntu server 9.04

2. Virtual Box

Von Virtual Box habe ich die Version 3.0.4, diese war die Lezte als ich diesen Artikel veröffentlicht habe.
Virtual Box

3. Oracle Express Edition for Linux 10g Release 2

Trotz der Einschränkungen der Version habe ich sie installiert, da sie ideal für Tests, lernen ist, bzw. ihre Voraussetzungen sind kleiner als die anderen Versionen.
Oracle XE 10g

Installationseinleitung:

1. Virtual Box Installation

Die Installation ist richtig intuitiv und man braucht nicht speziel, deshalb werde ich hier nicht erläutern.

2. Ubuntu Server unter Virtual Box

Sie ist fast so einfach, wie die Virtual Box installation, ausgenommen, dass es muss berücksichtigt werden, dass Oracle benötigt unbedingt eine Swap von 1MB, und wenn sie nicht vorhande ist, lässt sich diese Intallation nicht machen.

3. Oracle Express Edition for Linux 10g Release 2
   Da wir die Installation durch die Konsole durchführen müssen, wird sie ein Bisschen interessanter und deshalb werde ich hier die notwendigen Schritten beschreiben:
   • Anpassung der “sources.list” Datei, der Reporsitory für Oracle muss angelegt werden de:

   
   sudo vi /etc/apt/sources.list
deb http://oss.oracle.com/debian unstable main non-free
   

   • Laden der GPG Verschlüssel:

   
   wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -
   

   • Aktuallisierung der Datenbank der Paketten:

   
   sudo apt-get update
   

   • Installation der Oracle 10g XE Datenbank:

   
   sudo apt-get install oracle-xe
   

   • Nach der Installation müss die Konfiguration ausgeführt werden:

   
   sudo /etc/init.d/oracle-xe configure
   

   • Anpassung der Umgebung, um die Datenbank aus unserer Umgenung benutzen zu können

   
   vi .profile
xport ORACLE_SID=XE
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export PATH=$PATH:$ORACLE_HOME/bin
   

Ergebnis:

Ein Bild ist Wertvoller als ein Tausend Worte – Sagt man auf Spanisch:

Wenn man wissen muss, an welchen Stellen ein bestimmtes Objekt referenziert ist, kann man mit Hilfe von Fremdanwendungen diese Information aus der Datenbank herausziehen oder einfach folgende Abfrage ausführen:

Beispiel:

Technische Daten:

Datenbankversion: Oracle Database 10g Express Edition Release 10.2.0.1.0 – Product
Betriebssystem : Linux 2.6.27-11-generic

select owner, object_type, object_name, status
from  sys.dba_objects
where object_id in (
select object_id
from public_dependency
connect by prior object_id        = referenced_object_id
start with referenced_object_id = (
select object_id
from sys.dba_objects
where owner        = ‘FRAN’
and object_name  = ‘DEPARTMENTS’
and object_type  = ‘TABLE’
)
)
;

Continue reading »

Manchmal müssen die Tests von Prozesse mit produktiven Daten stattfinden. Obwohl wir die Benutzer und ihre Kennwörter auf der Testumgebung besitzen, auf Sicherheitsgründe sind diese Zugriffsdaten der Produktivumgebung fürs uns unbekannten. Zum Glück haben wir einen Benutzer auf der Produktivumgebung, der die “select any tables” Rechte hat. Das reicht für uns, da wir die Tabellen auf unserem Schema anlegen können und danach mit Hilfe der Oracle Befehlen exp bzw. imp können wir diese Tabellen exportieren und importieren. Schließlich geben wir die “select” Rechte zu dem endgültigen Benutzer, da er die Daten zugreifen kann, um sie kopieren zu können.

Wir haben auch angenommen, dass die DB-LINKS zwischen der Testdatenbank und der Prod – Datenbank verboten sind.

Beispiel:

Technische Daten:

Datenbankversion: 10g
Produtivdatenbank: prod_db
Testdatenbank : test_db
Prodschema : sct
Benutzerschema : fran
Betriebssystem : AIX seat 3 5 00C783704C00

Ablauf:

• Tabelle auf unserem produktiven schema anlegen:

fran@prod_db> create table fran.karten as
select *
from sct.karten

/

• Tabelle exportieren:

exp fran@prod_db tables=karten file=karten.dmp

• Tabelle auf unserem Testschema importieren:

exp fran@prod_db tables=karten file=karten.dmp

• Grant vergeben:

fran@test_db> grant select on fran.karten to sct ;

Wichtige Himweise:

Muss berücksichtigt werden, dass genug Platz in dem betroffenen Tablespace bzw. Filesystem vorhanden ist.

Hilfbefehle:

• „ls –lrt karten.dmp“: Um die Größe unserer Exportdatei zu kontrollieren:

-rw-r–r– 1 fran entw 263135232 Aug 05 16:28 karten.dmp

• „df –k | grep lhome“: Um den Platz auf dem Filesystem zu überprüfen.

/dev/home 5242880 780164 86% 88358 7% /lhome

• Mit der folgenden Abfrage können wir uns anschauen, wie viel Platz noch Übrig in unserem „users“ Tablespace haben:

select  a.tablespace_name,

b.file_name,

b.bytes sizeb,

sum(a.bytes) free

from (select file_id, tablespace_name, bytes, rownum from dba_free_space) a,

(select file_id, file_name, bytes, rownum from dba_data_files) b

where a.file_id = b.file_id

and a.tablespace_name = ‘USERS’

group by a.tablespace_name, b.file_name, b.bytes

;