Bibliography
Appendix
Fundamentals I - Übungen und Lösungen
Grundlage sind die Scripte zur Erstellung der Nordwind-DB.
1. Melden Sie sich als sys and der Datenbank an.
2. Erstellen Sie die Tabelle employees aus dem Skript employees.txt. Wie viel Datensätze hat die Tabelle employees?
start ~/1ZO-033/Northwind-Skript/employees.txt
select count(*) from employees;
COUNT(*)
----------
9
3. Führen Sie folgende Anweisung aus, um die Ausgabe des Ausführungsplanes für die jeweiligen Anweisungen durchzusetzen.
set autotrace on explain;4. Was passiert und was ist die Ursache?
SP2-0613: Format oder Existenz von PLAN_TABLE kann nicht überprüft werden SP2-0611: Fehler beim Initialisieren von EXPLAIN - Bericht5. Erstellen Sie nun die Plan-Tabelle, indem Sie das Skript utlxplan.sql (den Speicherort der Datei müssen Sie selber herausfinden) in Ihrem aktuellen Fenster ausführen. Führen Sie anschließend erneut die Anweisung aus.
start /oracle/ora92/rdbms/admin/utlxplan.sql set autotrace on explain;6. Was passiert? Wo lag die Datei utlxplan.sql?
/oracleora92/rdbms/admin/utlxplan.sql7. Führen Sie eine Abfrage aus, welche alle Datensätze und alle Attribute (Spalten) der Tabelle employees zurückgibt. Was sagt Ihnen der Ausführungsplan?
select * from employees; Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES'8. Nun wollen wir uns auch jeweils die Zeit anzeigen lassen, die eine Abfrage benötigte. Führen Sie hierfür folgende Anweisung aus:
set timing on;9. Führen Sie eine Abfrage aus, welche alle Datensätze und alle Attribute (Spalten) der Tabelle employees zurückgibt. Wie lange dauerte die Abfrage?
select * from employees; Abgelaufen: 00:00:00.01 Ausführungsplan --------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES'10. Erzeugen Sie eine Tabelle namens empl_cop, welche genau die Spalten und die Datensätze der Employees enthält. Wie lautet die Anweisung hierfür?
create table empl_cop as select * from employees;11. Verdoppeln Sie die Anzahl der Datensätze in dieser Tabelle empl_cop insgesamt 16 mal. Wie viel Datensätze haben Sie nunmehr?
-- 16 mal:
insert into empl_cop select * from empl_cop;
commit;
select count(*) from empl_cop;
COUNT(*)
----------
589824
12. WICHTIG:
Alles weitere bezieht sich jeweils immer auf die empl_cop!
Beispielergebnismenge
LASTNAME COUNT(*) -------------------- ---------- Buchanan 65536 Callahan 65536 Davolio 65536 Dodsworth 65536 Fuller 65536 King 65536 Leverling 65536 Peacock 65536 Suyama 65536
select lastname, count(*) from empl_cop group by lastname; Abgelaufen: 00:00:20.09 Ausführungsplan ----------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 TABLE ACCESS (FULL) OF 'EMPL_COP'13. Löschen Sie die Tabelle employees und auch die Tabelle Empl_cop wieder.
drop table employees; drop table empl_cop; commit;Im folgenden werden Sie das Tool tkprof etwas genauer kennen lernen. Führen Sie hierfür folgende Schritte durch:
1. Erstellen Sie die Tabelle customers aus dem Skript customers.txt. Wie viel Datensätze hat die Tabelle customers?
start ~/1ZO-033/Northwind-Skript/customers.txt
select count(*) from customers;
COUNT(*)
----------
91
Abgelaufen: 00:00:00.00
Ausführungsplan
----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'CUSTOMERS'
2. Nun muss das Tracing eingeschaltet werden, um eine Trace-Datei als Grundlage für tkprof zu haben.
Führen Sie hierfür folgenden Befehl aus:
alter session set sql_trace = true;3. Mit welchen Befehl hätten Sie diese Einstellung permanent machen können?
alter system set sql_trace=true scope=spfile;4. Wo befindet sich der Ordner, in dem die Trace-Datei gespeichert wird?
/oracle/admin/pingudb/udump/pingudb_ora_2404.trc5. Wie könnten Sie diesen Ort verändern, auf eine andere Platte zum Beispiel?
USER_DUMP_DESTAndere Platte entsprechend Inhalt von USER_DUMP_DEST mounten. ;-)
6. Ermitteln Sie die Kunden, die aus Berlin kommen
select * from customers where city = 'Berlin'; 1 Zeilen ausgewählt. Abgelaufen: 00:00:00.00 Ausführungsplan ----------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'Ermitteln Sie die Kunden, die aus London kommen
select * from customers where city = 'London'; 6 Zeilen ausgewählt. Abgelaufen: 00:00:00.03 Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'Ermitteln Sie die Kunden, die aus Paris kommen
2 Zeilen ausgewählt. Abgelaufen: 00:00:00.01 Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'8. Nun sollten Sie das Tracing ausschalten.
alter session set sql_trace = false;9. Schauen Sie nun nach, ob die Trace-Datei erstellt wurde.
ja
10. Führen Sie tkprof an der Unix-Shell aus, um sich eine lesbare Datei erstellen zu lassen.
tkprof.exe /oracle/admin/pingudb/udump/pingudb_ora_2404.trc /tmp/1.txt11. Öffnen Sie nun die eben erstellte Textdatei und schauen Sie sich die Ergebnisse an
Zusatzaufgaben 1
Sicher kommt es vor, dass eine Abfrage so lange dauert, dass es sinnvoller wäre, diese Session abzubrechen. Hierfür können Sie folgendes machen: Melden Sie sich als Scott an und führen Sie eine lange Abfrage aus (z. B. Cross Join über order_details)
Machen Sie eine weitere Session auf:
Führen Sie folgende Abfrage aus, um den Benutzer herauszufinden, dessen Session beendet werden soll select username,sid,serial# from v$session;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
1 1
2 1
...
SYS 9 34
SCOTT 12 242
Beenden Sie die Session folgendermassen (SID, SERIAL#):
alter system kill session '12,242';
WICHTIG: Legen Sie sich ein Cold-Backup Ihrer Datenbank an, um im Fehlerfall die Datenbank schnell wiederherstellen und weiterarbeiten zu können.
Bitte führen Sie folgende Schritte als Vorbereitung für die Übung durch.
Falls Sie die Plan-Tabelle in einer der vorigen Übungen noch nicht erstellt haben, so tun Sie dies bitte jetzt (utlxplan.sql ggf. ausführen).
Aktivieren Sie die Anzeige der Zeit und des Ausführungsplanes.
Geben Sie hierfür folgendes ein:
set autotrace on exp; set timing on;Führen Sie folgende kleine Anweisung aus um zu sehen, ob die Einstellungen ordnungsgemäß umgesetzt wurden:
select * from dual;Als Ergebnis müsste eine Zeitangabe und ein Ausführungsplan erscheinen
Im folgenden wollen wir untersuchen, wie lange die Suche nach einem Datensatz einer Tabelle bei einem Full Table Scan dauert und anschließend diese Prozedur durch einen Index verbessern:
1. Erstellen Sie die Tabelle order_det_prod, welche aus der orderid, productname, productid, quantity und unitprice (aus products und order_details) besteht. Verdoppeln Sie die Anzahl der Datensätze jeweils 12 mal. Wie viel Datensätze haben Sie nun?
create table order_det_prod as
select o.orderid,p.productname,p.productid,o.quantity,p.unitprice
from products p,order_details o
where p.productid = o.productid
;
-- 12 mal:
insert into order_det_prod select * from order_det_prod;
commit;
select count(*) from order_det_prod;
COUNT(*)
----------
8826880
Abgelaufen: 00:05:32.09
Ausführungsplan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'ORDER_DET_PROD'
2. Ermitteln Sie die größte Bestellnummer.
Wie heißt die Abfrage und wie lange dauert die Abfrage?
Mit welcher Methode wurde dieser Datensatz ermittelt?
select max(orderid) from order_det_prod;
MAX(ORDERID)
------------
11077
Abgelaufen: 00:01:06.05
Ausführungsplan
------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'ORDER_DET_PROD'
3. Erstellen Sie einen Index namens id1 basierend auf der Spalte orderid.
create index id1 on order_det_prod(orderid); Index wurde angelegt. Abgelaufen: 00:17:14.034. Ermitteln Sie die größte Bestellnummer. Wie lange dauert die Abfrage nunmehr? Mit welcher Methode wurde dieser Datensatz ermittelt?
select max(orderid) from order_det_prod;
MAX(ORDERID)
------------
11077
Abgelaufen: 00:00:00.00
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'ID1' (NON-UNIQUE)
5. Wie oft wurde bisher 'Chai' bestellt (bitte keine Funktion wie lower etc. benutzen)?
Wie schnell wurde die Abfrage ausgeführt?
select count(*) from order_det_prod where productname='Chai';
COUNT(*)
----------
155648
Abgelaufen: 00:01:07.05
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'ORDER_DET_PROD'
6. Erstellen Sie einen geeigneten Index namens id2 und führen Sie die Abfrage erneut aus.
Wie schnell wurde die Abfrage nunmehr ausgeführt?
create index id2 on order_det_prod(productname); Index wurde angelegt. Abgelaufen: 00:25:17.03 ...7. Erstellen Sie eine Tabelle namens emp_cop basierend auf der employees-Tabelle. Die Tabelle sollte identisch zur employees sein (gleiche Spalten, gleiche Zeilen)
create table emp_cop as select * from employees;8. Erstellen Sie einen Index namens id3 auf Basis von lastname
create index id3 on emp_cop(lastname);9. Nun werden Sie den Index id3 analysieren, dass heißt, den Prozentsatz von ungenutztem (durch Löschen) zu dem gesamten Platz ermitteln.
ANALYZE INDEX id3 VALIDATE STRUCTURE;
select (del_lf_rows_len/lf_rows_len)*100 from index_stats;
(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100
---------------------------------
0
0% gelöschte Rows.
10. Löschen Sie den ältesten Mitarbeiter. Wie viel Datensätze wurden gelöscht?
delete from emp_cop where birthdate =
(
select min(birthdate) from emp_cop
)
;
1 Zeile wurde gelöscht.
11. Nun werden Sie den Index analysieren,
dass heißt, den Prozentsatz von ungenutztem (durch Löschen) zu dem gesamten Platz ermitteln.
Legen Sie Ihre Schlussfolgerungen dar.
ANALYZE INDEX id3 VALIDATE STRUCTURE;
select (del_lf_rows_len/lf_rows_len)*100 from index_stats;
(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100
---------------------------------
11,0465116
11% gelöschte ROWs
12. Nun werden Sie ein rebuild des Index durchführen. Führen Sie diesen Befehl bitte auch durch.
ALTER INDEX id3 REBUILD;13. Nun werden Sie den Index analysieren, dass heißt, den Prozentsatz von ungenutztem (durch Löschen) zu dem gesamten Platz ermitteln. Legen Sie Ihre Schlussfolgerungen dar:
ANALYZE INDEX id3 VALIDATE STRUCTURE;
select (del_lf_rows_len/lf_rows_len)*100 from index_stats;
(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100
---------------------------------
0
Im folgenden werden Sie einen Bitmap-Index erstellen und testen:
1. Erweitern Sie die Tabelle order_det_prod um eine Spalte namens done (varchar2(1)).
alter table order_det_prod add(done varchar2(1));2. Für alle Orderids unter 11000 soll Done 'N' sein und für alle darüber soll Done 'Y' sein.
update order_det_prod set done = 'N' where orderid < 11000; update order_det_prod set done = 'Y' where orderid > 11000; commit;Hier geht der Rechner in die Knie!
3. In wie vielen Datensätzen steht bei Done Y drin?
select count(*) from order_det_prod where done='Y';
COUNT(*)
----------
880640
4. Erstellen Sie einen Bitmap-Index auf der Spalte Done.
create bitmap index id4 on order_det_prod(done);5. In wie vielen Datensätzen steht bei Done N drin (Bitte benutzen Sie kein * beim Zählen)? Schreiben Sie hierfür eine Abfrage und messen Sie die Zeit.
select count(done) from order_det_prod where done='N';Im folgenden werden Sie einen funktionsbasierten Index kennen lernen.
1. Ermitteln Sie alle Datensätze, bei denen der Productname "Chai" ist. Hierbei soll es egal sein, ob das Wort groß oder klein geschrieben wurde.
select count(*) from order_det_prod where lower(productname) = 'chai' ;2. Erstellen Sie einen normalen Index, der die Suche beschleunigen soll. Was passiert und warum?
create index id5 on order_det_prod(productname);3. Löschen Sie diesen Index und schreiben Sie einen funktionsbasierten Index. Was passiert und warum?
drop index id5; create index ... lower(..).. ;Zusatzaufgabe 1:
Schreiben Sie eine Abfrage, die Ihnen angibt, welche Indizes auf der Tabelle customers liegen und welche Spalten diese umfassen.
select mit Hint
Zusatzaufgabe 2:
Löschen Sie den Bitmap-Index auf Done und erstellen Sie anstelle dessen einen normalen Index. Führen Sie die entsprechenden Messungen erneut durch. Benutzen auch eventuell Hints.
Zusatzaufgabe 3:
Erstellen Sie eine große Tabelle (ca. 3 bis 4 Mio Datensätze) und erstellen Sie einen Index. Überprüfen Sie nun, ob ein Rebuild schneller geht als ein Drop und Recreate des Index. Überprüfen Sie des weiteren, ob während des Rebuilds weitergearbeitet werden kann. Überprüfen Sie außerdem, ob während des normalen Recreate (also Drop und Create) weitergearbeitet werden kann. Legen Sie Ihrer Ergebnisse kurz schriftlich dar.
1. Erstellen Sie die Tabelle order_details neu aus dem Script zur Nordwind-DB.
start ~/1ZO-033/Northwind-Skript/order_details.txt2. Schalten Sie Autotrace ein.
set autotrace on explain;3. Fügen Sie zur Tabelle order_details eine Spalte namens paketgroesse varchar(10) hinzu.
alter table order_details add(paketgroesse varchar(10));4. Geben Sie für 1/3 der Datensätze den Wert 'klein' in die Spalte Paketgroesse.
update order_details set paketgroesse=mod(rownum,3); update order_details set paketgroesse='klein' where paketgroesse='1';5. Geben Sie für 1/3 der Datensätze den Wert 'mittel' in die Spalte Paketgroesse.
update order_details set paketgroesse='mittel' where paketgroesse='2';6. Geben Sie für 1/3 der Datensätze den Wert 'gross' in die Spalte Paketgroesse.
update order_details set paketgroesse='gross' where paketgroesse='0'; commit; select paketgroesse,count(*) from order_details group by paketgroesse; PAKETGROES COUNT(*) ---------- ---------- gross 718 klein 719 mittel 7187. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.
select * from order_details where paketgroesse='klein'; 719 Zeilen ausgewählt. Ausführungsplan ---------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'ORDER_DETAILS'8. Erstellen Sie einen Index basierend auf der Spalte paketgroesse.
create index id1 on order_details(paketgroesse);9. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'mittel' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.
select * from order_details where paketgroesse='mittel'; 718 Zeilen ausgewählt. Ausführungsplan -------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)Es wird RBO genommen, weil keine Analyse der Tabelle existiert.
10. Zwingen Sie den Optimierer, den cost based optimizer (CBO) bei der nun folgenden Abfrage zu nehmen. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'gross' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.
select /*+ALL_ROWS*/ * from order_details where paketgroesse='gross' ; Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=6 Bytes=432) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' (Cost=1 Card=6 Bytes=432) 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE) (Cost=1 Card=2)Es erfolgt eine Index-Scan weil CBO, Index vorhanden und es gibt keine Statistik.
11. Fügen Sie einen Datensatz hinzu, bei dem die Paketgröße 'riesig' ist.
insert into order_details values(11111,22,3,4,44,'riesig');12. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'riesig' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.
select * from order_details where paketgroesse='riesig' ; Ausführungsplan -------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)RBO, da noch keine Statistik vorhanden ist.
13. Zwingen Sie den Optimierer, den CBO bei der nun folgenden Abfrage zu nehmen. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'riesig' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.
select /*+ALL_ROWS*/ * from order_details where paketgroesse='riesig' ; Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=6 Bytes=432) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' (Cost=1 Card=6 Bytes=432) 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE) (Cost=1 Card=2)Index-Scan, da keine Analyse,
14. Zwingen Sie den Optimierer, den rules based optimizer (RBO) bei der nun folgenden Abfrage zu nehmen. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'riesig' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.
select /*+RULE*/ * from order_details where paketgroesse='riesig' ; Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)RULE: Wenn Index da, dann nehme Index.
15. Erstellen Sie ein Histogramm der Tabelle order_details.
analyze table order_details compute statistics for columns quantity size 4 ;Histogramm wird nur mit 'size ...' erstellt.
16. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'riesig' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Welcher Optimierungsmodus wird eingesetzt? Begründen Sie Ihre Antwort.
select * from order_details where paketgroesse='riesig' ; Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)Index-Scan auf Grund des erstellten Histogramms.
17. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'mittel' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Welcher Optimierungsmodus wird eingesetzt? Begründen Sie Ihre Antwort.
select * from order_details where paketgroesse='mittel' ; Ausführungsplan ------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)Index-Scan wurde verwendet. Full-Table-Scan könnte auch ausgewählt werden, wenn das Verhältnis Anzahl der Datensätze zur Anzahl unterschiedlicher Werte etwas anders wäre.
18. Erstellen Sie die Tabellen products und categories aus dem Skript.
start ~/1ZO-033/Northwind-Skript/products.txt start ~/1ZO-033/Northwind-Skript/categories.txt19. Erstellen Sie eine Abfrage, die Ihnen den Produktnamen und den Kategorienamen als Ergebnismenge zurückliefert. Um welchen Join handelte es sich hierbei und warum?
select p.productname,c.categoryname from products p, categories c where p.categoryid=c.categoryid ; Ausführungsplan ------------------------------------------------ 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'CATEGORIES' 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'PRODUCTS'20. Erstellen Sie einen Index auf der Spalte categoryid der Tabelle products. Führen Sie nun die Abfrage nochmals aus. Um welchen Join handelte es sich hierbei und warum?
create index id2 on products(categoryid); select p.productname,c.categoryname from products p, categories c where p.categoryid=c.categoryid ; Ausführungsplan --------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS' 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'CATEGORIES' 4 2 INDEX (RANGE SCAN) OF 'ID2' (NON-UNIQUE)
1. Schalten Sie Autotrace ein
set autotrace on explain;2. Erstellen Sie die Tabelle order_details.
start ~/1ZO-033/Northwind-Skript/order_details.txt3. Fügen Sie zur Tabelle order_details eine Spalte namens paketgroesse varchar(10) hinzu
alter table order_details add(paketgroesse varchar(10));4. Geben Sie für 1/3 der Datensätze den Wert 'klein' in die Spalte Paketgroesse.
update order_details set paketgroesse=mod(rownum,3); update order_details set paketgroesse='klein' where paketgroesse='1';5. Geben Sie für 1/3 der Datensätze den Wert 'mittel' in die Spalte Paketgroesse.
update order_details set paketgroesse='mittel' where paketgroesse='2';6. Geben Sie für 1/3 der Datensätze den Wert 'gross' in die Spalte Paketgroesse.
update order_details set paketgroesse='gross' where paketgroesse='0'; commit;7. Erstellen Sie einen Index basierend auf der Spalte paketgroesse
create index id1 on order_details(paketgroesse);
8. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?
select * from order_details where paketgroesse='klein'; Ausführungsplan ------------------------------------------------------ 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)9. Erstellen Sie eine Stored Outline für die eben erstellte Abfrage.
create or replace outline o1 for category cat1 on select * from order_details where paketgroesse='klein';10. Aktivieren Sie diese Stored Outline Wie lautet die Anweisung hierfür?
alter session set use_stored_outlines=cat1;11. Generieren Sie eine Statistik für die Tabelle order_details
analyze table order_details compute statistics;12. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?
select * from order_details where paketgroesse='klein'; Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=718 Bytes=14360) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' (Cost=9 Card=718 Bytes=14360) 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE) (Cost=2 Card=718)13. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'mittel' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?
select * from order_details where paketgroesse='mittel'; Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=718 Bytes=14360) 1 0 TABLE ACCESS (FULL) OF 'ORDER_DETAILS' (Cost=3 Card=718 Bytes=14360)14. Deaktivieren Sie diese Stored Outline.
alter session set use_stored_outlines=false; Session wurde geändert.15. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?
select * from order_details where paketgroesse='klein'; Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=718 Bytes=14360) 1 0 TABLE ACCESS (FULL) OF 'ORDER_DETAILS' (Cost=3 Card=718 By tes=14360)16. Aktivieren Sie diese Stored Outline erneut und ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?
alter session set use_stored_outlines=cat1; select * from order_details where paketgroesse='klein'; Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=718 Bytes=14360) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' (Cost=9 Card=718 Bytes=14360) 2 1 INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE) (Cost=2 Card=718)
Voraussetzung:
1. Optimierung: Ermittlung der minimalen orderid
create index inx_oid
on scott.orders(orderid) tablespace indx
;
analyze table scott.orders compute statistics
for columns orderid size 16
;
select distinct min(orderid) from scott.orders;
MIN(ORDERID)
------------
10248
Abgelaufen: 00:00:00.00
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'INX_OID' (NON-UNIQUE)
-- Ausführungsplan speichern
create outline oid_min for category cat1 on
select distinct min(orderid) from scott.orders
;
2. Optimierung:
Wie viel Auslaufprodukte gibt es?
-- bitmap index erstellen
create bitmap index inx_disc
on scott.products(discontinued) tablespace indx
;
-- analysieren
analyze table scott.products compute statistics
for columns discontinued size 2
;
-- Fall 1
select count(*) from scott.products where discontinued=1;
COUNT(*)
----------
131072
Abgelaufen: 00:00:02.07
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'PRODUCTS'
-- Fall 2
select /*+first_rows*/ count(*)
from scott.products where discontinued = 1
;
COUNT(*)
----------
131072
Abgelaufen: 00:00:00.00
Ausführungsplan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'INX_DISC'
3. Optimierung:
Welche Produkte fangen mit CH (Klein- oder Großschreibung egal) an?
create bitmap index id3 on products(productname); analyze table products compute statistics for columns productname size 80; select distinct productname from products where lower(productname) like 'ch%'; PRODUCTNAME ---------------------------------------- Chai Chang Chartreuse verte Chef Anton's Cajun Seasoning Chef Anton's Gumbo Mix Chocolade 6 Zeilen ausgewählt. Abgelaufen: 00:00:09.05 Ausführungsplan --------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (UNIQUE) 2 1 TABLE ACCESS (FULL) OF 'PRODUCTS' select /*+ index(id3) */distinct productname from products where lower(productname) like 'ch%' ; PRODUCTNAME ---------------------------------------- Chai Chang Chartreuse verte Chef Anton's Cajun Seasoning Chef Anton's Gumbo Mix Chocolade 6 Zeilen ausgewählt. Abgelaufen: 00:00:00.01 Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=139 Card=77 Bytes=1309) 1 0 SORT (UNIQUE) (Cost=139 Card=77 Bytes=1309) 2 1 BITMAP INDEX (FAST FULL SCAN) OF 'ID3'4. Optimierung: Wie teuer ist das Produkt namen Chai. Sie wissen nicht, ob dieses Produkt groß oder klein geschrieben ist. Optimieren Sie dieses Statement so weit es geht. Hier geht es um Geschwindigkeit um jeden Preis.
create index id34 on products(productname); analyze index id34 validate structure; select distinct productname,unitprice from products where lower(productname) = 'chai' ; PRODUCTNAME UNITPRICE --------------------------------- Chai 18 Abgelaufen: 00:00:03.09 Ausführungsplan --------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (UNIQUE) 2 1 TABLE ACCESS (FULL) OF 'PRODUCTS' select /*+ index(id34)*/ distinct productname,unitprice from products where lower(productname) = 'chai'; PRODUCTNAME UNITPRICE --------------------------------- Chai 18 Abgelaufen: 00:00:03.08 Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1082 Card=8625 Bytes=301875) 1 0 SORT (UNIQUE) (Cost=1082 Card=8625 Bytes=301875) 2 1 TABLE ACCESS (FULL) OF 'PRODUCTS' (Cost=1017 Card=8625 Bytes=301875) analyze index id34 delete statistics; drop index id34; create index fid34 on products(lower(productname)); select /*+ index(fid34)*/ distinct productname,unitprice from products where lower(productname) = 'chai'; PRODUCTNAME UNITPRICE --------------------------------- Chai 18 Abgelaufen: 00:00:02.02 Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=67 Card=8625 Bytes=301875) 1 0 SORT (UNIQUE) (Cost=67 Card=8625 Bytes=301875) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS' (Cost=2 Card=8625 Bytes=301875) 3 2 INDEX (RANGE SCAN) OF 'FID34' (NON-UNIQUE) (Cost=1 Card=3450) analyze index fid34 delete statistics; drop index fid34; create bitmap index bid34 on products(productname,unitprice); select /*+ index(bid34)*/ distinct productname,unitprice from products where lower(productname) = 'chai'; PRODUCTNAME UNITPRICE --------------------------------- Chai 18 Abgelaufen: 00:00:00.01 Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=8625 Bytes=301875) 1 0 SORT (UNIQUE) (Cost=88 Card=8625 Bytes=301875) 2 1 BITMAP INDEX (FAST FULL SCAN) OF 'BID34' analyze index bid34 delete statistics; drop index bid34;5. Optimierung: Welche Produkte (productname und unitprice) werden von Lieferanten aus UK geliefert? Optimieren Sie hier um jeden Preis.
create index s_1 on suppliers(country, supplierid); create bitmap index s_2 on products(productname, unitprice, supplierid); select distinct productname, unitprice, country from products, suppliers where suppliers.supplierid(+)=products.supplierid and suppliers.country='UK' ; PRODUCTNAME UNITPRICE COUNTRY ----------------------------------------------------------- Aniseed Syrup 10 UK Chai 18 UK Chang 19 UK Scottish Longbreads 12,5 UK Sir Rodney's Marmalade 81 UK Sir Rodney's Scones 10 UK Teatime Chocolate Biscuits 9,2 UK 7 Zeilen ausgewählt. Abgelaufen: 00:00:01.07 Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2393 Card=3376 Bytes=148544) 1 0 SORT (UNIQUE) (Cost=2393 Card=3376 Bytes=148544) 2 1 HASH JOIN (Cost=60 Card=142688 Bytes=6278272) 3 2 INDEX (RANGE SCAN) OF 'S_1' (NON-UNIQUE) (Cost=2 Card=2 Bytes=44) 4 2 BITMAP CONVERSION (TO ROWIDS) 5 4 BITMAP INDEX (FAST FULL SCAN) OF 'S_2'6. Optimierung: Bei welchen Lieferanten ist der maximale Produktpreis größer als 200?
create bitmap index s_2 on products(supplierid, unitprice);
select supplierid
from (select supplierid, max(unitprice) m1
from products group by supplierid) where m1 > 200
;
SUPPLIERID
----------
18
Abgelaufen: 00:00:02.08
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=2 Bytes=10)
1 0 FILTER
2 1 SORT (GROUP BY NOSORT) (Cost=29 Card=2 Bytes=10)
3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP INDEX (FULL SCAN) OF 'S_2'
7. Optimieren Sie folgende Abfrage:
Wieviel Produkte haben an der zweiten Stelle ein h?
SQL> select count(distinct productname) from products where productname Like '_h%';
COUNT(DISTINCTPRODUCTNAME)
--------------------------
8
Abgelaufen: 00:00:00.02
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=1 Bytes=17)
1 0 SORT (GROUP BY)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (FAST FULL SCAN) OF 'B2_IND'
grant CREATE SESSION to scott; grant CREATE TABLE to scott; grant CREATE MATERIALIZED VIEW to scott; grant QUERY REWRITE to scott;2. Melden Sie sich als scott mit dem Kennwort tiger an der Datenbank an.
connect scott/tiger@testdb29;3. Erstellen Sie eine Tabelle namens bigtab, die auf den Spalten und Datensätzen der Tabelle all_objects basiert.
create table bigtab as select * from all_objects;4. Verdoppeln Sie die Tabelle 5 mal. Wie viel Datensätze hat die Tabelle?
-- 5 mal
insert into bigtab select * from bigtab;
commit;
select count(*) from bigtab;
COUNT(*)
----------
751840
5. Analysieren Sie die Tabelle.
analyze table bigtab compute statistics;6. Schreiben Sie eine Abfrage, die Ihnen die Anzahl der Datensätze je owner anzeigt. Messen Sie die Zeit für die Ausführung dieser Abfrage und halten Sie Ihr Ergebnis schriftlich fest.
OWNER COUNT(*) --------------------- CTXSYS 6264 ELAN 1272 HR 816 MDSYS 5640 ODM 9768 ODM_MTR 288 set timing on;7. Führen Sie folgende Befehle aus.
alter session set query_rewrite_enabled=true; alter session set query_rewrite_integrity=enforced;8. Erstellen Sie folgende Materialized View:
create materialized view mv_bigtab build immediate refresh on commit enable query rewrite as select owner, count(*) from bigtab group by owner ; Materialized View wurde erstellt. Abgelaufen: 00:00:12.089. Führen Sie folgende Abfrage aus. Messen Sie bitte die Zeit:
select owner,count(*) from bigtab group by owner; Abgelaufen: 00:00:00.0010. Was sind Ihre Schlussfolgerungen?
Es wurde die Materialized View verwendet. Dies ist eine sehr optimale Methode.
11. Führen Sie folgende Anweisung aus:
alter session set query_rewrite_enabled=false;12. Was macht die Anweisung?
Die Materialized View kann so nicht mehr verwendet werden.
13. Führen Sie folgende Abfrage aus. Messen Sie bitte die Zeit:
select owner, count(*) from bigtab group by owner; Abgelaufen: 00:00:07.0514. Was sind Ihre Schlussfolgerungen?
Die Materialized View wurde nicht mehr verwendet.
15. Führen Sie folgende Anweisung aus:
alter session set query_rewrite_enabled = true;16. Was macht die Anweisung?
Ermöglicht die Verwendung der Materialized View.
17. Fügen Sie einen Datensatz in die Tabelle bigtab hinzu.
insert into bigtab
(OWNER, OBJECT_NAME, OBJECT_ID, CREATED, LAST_DDL_TIME)
values ('Hans','bla',1,sysdate,sysdate)
;
18. Schließen Sie das Einfügen mit commit auch ab.
commit;19. Führen Sie folgende Abfrage aus.
select owner, count(*) from bigtab group by owner; Abgelaufen: 00:00:00.0020. Was sind Ihre Schlussfolgerungen? Ist der neue Datensatz mit enthalten und wurde der materialisierte View benutzt?
Mit commit wird die Materialized View aktualisiert.
21. Testen Sie selbständig, ob das auch für das Löschen gilt.
delete bigtab where OWNER='Hans'; commit; select owner, count(*) from bigtab group by owner; OWNER COUNT(*) ------------------------------ ---------- CTXSYS 2048 MDSYS 5376 ... Abgelaufen: 00:00:00.00Auch beim Löschen wird die Materialized View aktualisiert.
22. Schreiben Sie die Abfrage so um, dass nur die Datensätze angezeigt werden, bei denen zum Owner mehr als 1000 Datensätze gehören. Führen Sie die Abfrage aus und beobachten Sie, ob der materialisierte View benutzt wird.
start /oracle/ora92/rdbms/admin/utlxplan.sql set autotrace on explain; select owner, count(*) from bigtab group by owner having count(*) > 1000; OWNER COUNT(*) ------------------------------ ---------- CTXSYS 2048 MDSYS 5376 ODM 7904 OLAPSYS 4256 ORDSYS 28896 PUBLIC 369280 SYS 322528 WKSYS 3776 WMSYS 1824 XDB 5152 10 Zeilen ausgewählt. Abgelaufen: 00:00:00.00 Ausführungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=120) 1 0 TABLE ACCESS (FULL) OF 'MV_BIGTAB' (Cost=2 Card=4 Bytes=120)Es wird die Materialized View verwendet, obwohl das Matching nicht exakt ist. Bei HAVING bringen Materialized Views Vorteile.
23. Löschen Sie die Materialized View wieder.
drop materialized view name;Zusatzaufgaben
1. Erstellen Sie eine Tabelle namens lehrer und tragen Sie dort 5 Lehrer ein. Bestätigen Sie das mit commit.
create table lehrer
(
Name varchar2(29)
)
;
insert into lehrer values ('Meier');
insert into lehrer values ('Müller');
insert into lehrer values ('Schmidt');
insert into lehrer values ('Schulz');
insert into lehrer values ('Meyer');
commit;
2. Erstellen Sie einen materialized view namens mv1, der die Anzahl der Datensätze der Tabelle lehrer ausgibt.
create materialized view mv1 build immediate refresh on commit enable query rewrite as select count(*) from lehrer ;Hinweis (ohne count):
SQL> create materialized view mv1
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select * from lehrer
7 ;
select * from lehrer
*
FEHLER in Zeile 6:
ORA-12014: Tabelle 'LEHRER' enthält kein Primärschlüssel-Constraint
3. Schreiben Sie eine Abfrage basierend auf der Tabelle lehrer,
welche die Anzahl der Datensätze ermittelt und beobachten Sie, ob die Materialized View benutzt wird.
select count(*) from lehrer;
COUNT(*)
----------
5
Abgelaufen: 00:00:00.00
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'LEHRER'
Die Materialized Views wurde nicht verwendet, da der CBO nicht aktiv ist (kein ANALYZE).
4. Fügen Sie einen Datensatz zur Tabelle lehrer hinzu.
insert into lehrer values ('Kohl');
commit;
5. Schreiben Sie eine Abfrage basierend auf der Tabelle lehrer,
welche die Anzahl der Datensätze ermittelt und beobachten Sie, ob der Materialized View benutzt wird.
select count(*) from lehrer;
COUNT(*)
----------
6
Abgelaufen: 00:00:00.00
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'LEHRER'
Erstellen Sie folgenden Ressource-Manager-Plan per Anweisung.
Aktivieren Sie ihn anschließend.
Erstellen Sie auch folgende Benutzer und ordnen Sie diese den entsprechenden Gruppen zu.
OLTP_Group: OLTP_1
BATCH_Group: Batch_1
ADHOC_Group: Adhoc_1
| Ressourcen Gruppen | Level 1 | Level 2 | Level 3 | Parallel Degree Limit |
|---|---|---|---|---|
| OLTP_GROUP | 20% | 0% | 0% | 0 |
| BATCH_GROUP | 0% | 80% | 0% | 10 |
| ADHOC_GROUP | 0% | 20% | 0% | 50 |
| OTHER_GROUP | 0% | 0% | 100% | 0 |
User erstellen
create user OLTP_1 identified by OLTP_1; create user Batch_1 identified by Batch_1; create user Adhoc_1 identified by Adhoc_1;Pending-Area neu erstellen.
exec dbms_resource_manager.clear_pending_area(); exec dbms_resource_manager.create_pending_area();NIGHT_PLAN erstellen
exec dbms_resource_manager.create_plan(plan => 'NIGHT_PLAN',comment=>'Mein erster Plan');Gruppen erstellen
exec dbms_resource_manager.create_consumer_group (consumer_group => 'OLTP_Group',comment=>'Meine erste Gruppe') ; exec dbms_resource_manager.create_consumer_group (consumer_group => 'BATCH_Group',comment=>'Meine zweite Gruppe') ; exec dbms_resource_manager.create_consumer_group (consumer_group => 'ADHOC_Group',comment=>'Meine dritte Gruppe') ;Resources
exec dbms_resource_manager.create_plan_directive (plan => 'NIGHT_PLAN', group_or_subplan => 'OLTP_Group', cpu_p1 => 20, parallel_degree_limit_p1 => 0, comment=>'') ; exec dbms_resource_manager.create_plan_directive (plan => 'NIGHT_PLAN', group_or_subplan => 'BATCH_Group', cpu_p2 => 80, parallel_degree_limit_p1 => 10, comment=>'') ; exec dbms_resource_manager.create_plan_directive (plan => 'NIGHT_PLAN', group_or_subplan => 'ADHOC_Group', cpu_p2 => 20, parallel_degree_limit_p1 => 5, comment=>'') ; exec dbms_resource_manager.create_plan_directive (plan => 'NIGHT_PLAN', group_or_subplan => 'OTHER_GROUPS', cpu_p3 => 100, parallel_degree_limit_p1 => 0, comment=>'') ;Submit
exec dbms_resource_manager.submit_pending_area(); exec dbms_resource_manager.create_pending_area();Berechtigung den Users zuweisen
exec dbms_resource_manager_privs.grant_switch_consumer_group (grantee_name => 'OLTP_1',consumer_group => 'OLTP_Group', grant_option => FALSE) ; exec dbms_resource_manager_privs.grant_switch_consumer_group (grantee_name => 'Batch_1',consumer_group => 'BATCH_Group', grant_option => FALSE) ; exec dbms_resource_manager_privs.grant_switch_consumer_group (grantee_name => 'Adhoc_1',consumer_group => 'ADHOC_Group', grant_option => FALSE) ;User zuweisen
exec dbms_resource_manager.set_initial_consumer_group (user => 'OLTP_1',consumer_group => 'OLTP_Group') ; exec dbms_resource_manager.set_initial_consumer_group (user => 'Batch_1',consumer_group => 'BATCH_Group') ; exec dbms_resource_manager.set_initial_consumer_group (user => 'Adhoc_1',consumer_group => 'ADHOC_Group') ;Plan aktivieren
alter system set resource_manager_plan = 'NIGHT_PLAN';Teil 2
Verändern Sie den Plan so, dass für alle anderen (also other_groups) alle Abfragen, die länger als 5 Sekunden dauern, geblockt werden. Überprüfen Sie Ihr Ergebnis anhand eines geeigneten Szenarios
exec dbms_resource_manager.clear_pending_area(); exec dbms_resource_manager.create_pending_area();Update
exec dbms_resource_manager.update_plan_directive (plan => 'NIGHT_PLAN', group_or_subplan => 'OTHER_GROUPS', new_max_est_exec_time => 5) ;Submit
exec dbms_resource_manager.SUBMIT_pending_area();Teil 3
Verändern Sie den Plan so, dass für adhoc_user alle Abfragen, die länger als 20 Sekunden dauern, geblockt werden. Außerdem sollten diese User nicht mehr als 6 paralelle Sessions geöffnet haben können. Überprüfen Sie Ihr Ergebnis anhand eines geeigneten Szenarios.
exec dbms_resource_manager.clear_pending_area(); exec dbms_resource_manager.create_pending_area();Update
exec dbms_resource_manager.update_plan_directive (plan => 'NIGHT_PLAN', group_or_subplan => 'ADHOC_Group', new_max_est_exec_time => 20, new_parallel_degree_limit_p1 => 6) ;Submit
exec dbms_resource_manager.SUBMIT_pending_area();
1. Erstellen Sie eine Tabelle abteilung mit einer Spalte S1 char(1000) und zehn Datensätzen (von 'AAA' bis 'JJJ')
create table abteilung (s1 char(1000));
insert into abteilung values('AAA');
insert into abteilung values('BBB');
insert into abteilung values('CCC');
insert into abteilung values('DDD');
insert into abteilung values('EEE');
insert into abteilung values('FFF');
insert into abteilung values('GGG');
insert into abteilung values('HHH');
insert into abteilung values('III');
insert into abteilung values('JJJ');
2. Verdoppeln Sie die Tabelle 10 mal.
-- 10 x insert into abteilung select * from abteilung; commit;3. Wie viel Datensätze haben Sie nun?
select count(*) from abteilung;
COUNT(*)
----------
10240
4. Stellen Sie sicher, dass die gecachten Blöcke niemals ausgelagert werden.
Ermitteln Sie hierfür die Größe der Tabelle abteilung.
Erstellen Sie gemäß dieser Größe den entsprechenden Buffer-Pool.
Stellen Sie sicher, dass die Tabelle in den Keep-Pool geschrieben wird.
select sum(bytes)
from dba_segments
where owner = 'SYS'
and segment_name = 'ABTEILUNG'
;
SUM(BYTES)
----------
12582912
= 12 MByte
alter system set DB_KEEP_CACHE_SIZE=16M;
-- Anzeige:
select 1, name, to_number(value/1024/1024) value
from v$parameter
where upper(name) like 'DB%CACHE_SIZE'
or
upper(name)
in ('SHARED_POOL_SIZE','LARGE_POOL_SIZE','JAVA_POOL_SIZE','LOG_BUFFER')
union
select 1, '+ 1MB', 1 from dual order by 2
;
-- Tabelle in den Keep-Pool
ALTER TABLE ABTEILUNG STORAGE (buffer_pool KEEP);
5. Führen Sie mehrfach eine Abfrage der Tabelle Abteilung durch und beobachten Sie jeweils die Hit Ratio für den Keep-Pool.
-- Abfrage (mehrmals)
select count(*) from abteilung where s1='AAA';
-- Abfrage Hit Ratio:
select 1-value/
(
select sum(value)
from v$sysstat
where lower(name) in ('consistent gets','db block gets')
)
from v$sysstat
where lower(name) in ('physical reads')
;
,959986246
,960109863
,960304683
Hit Ratio steigt.
6. Beobachten Sie ebenfalls die Werte in der Tabelle v$buffer_pool_statistics (PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS).
select name,
100-round((physical_reads/(db_block_gets+consistent_gets))*100,2)
as Hit_Ratio
from v$buffer_pool_statistics
;
FEHLER in Zeile 1:
ORA-01476: Divisor ist Null
Da KEEP gleich 0 ist.
Ggf. ANALYZE ??
select name, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS from v$buffer_pool_statistics ; NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS -------------- -------------- ------------- --------------- KEEP 0 0 0 DEFAULT 17251 357396 2020937. Löschen Sie die Tabelle Abteilung.
drop table abteilung;8. Führen Sie die gleichen Tests für den Recycle-Pool durch. Halten Sie auch hier Ihre Ergebnisse schriftlich fest:
Bis 4. sind die Schritte identisch.
alter system set DB_KEEP_CACHE_SIZE=0M; alter system set db_recycle_cache_size=16M; ALTER TABLE ABTEILUNG STORAGE (buffer_pool recycle);
-- Abfrage (mehrmals)
select count(*) from abteilung where s1='AAA';
-- Abfrage Hit Ratio:
select 1-value/
(
select sum(value)
from v$sysstat
where lower(name) in ('consistent gets','db block gets')
)
from v$sysstat
where lower(name) in ('physical reads')
;
,958975396
,959156127
,959268817
,959413019
select name,
100-round((physical_reads/(db_block_gets+consistent_gets))*100,2)
as Hit_Ratio
from v$buffer_pool_statistics
;
FEHLER in Zeile 1:
ORA-01476: Divisor ist Null
select name, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS from v$buffer_pool_statistics ; NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS -------------------- -------------- ------------- --------------- RECYCLE 0 0 0 DEFAULT 19180 394619 2231139. Setzen Sie den buffer_cache auf 16 MB.
alter system set db_cache_size=16M;10. Erzeugen Sie eine Tabelle angestellte auf Basis der Tabelle scott.emp
create table angestellte as select * from scott.emp;
11. Vergrößern Sie die Tabelle so lange, bis diese 32MB groß ist.
insert into ANGESTELLTE select * from ANGESTELLTE;
commit;
insert into ANGESTELLTE select * from ANGESTELLTE;
commit;
-- ...
select sum(bytes)
from dba_segments
where owner = 'SYS'
and segment_name = 'ANGESTELLTE'
;
SUM(BYTES)
----------
45088768
12. Fragen Sie die Tabelle ab.
select count(*) from ANGESTELLTE;
COUNT(*)
----------
917504
13. Schauen Sie sich das Hit Ratio an.
Was passiert und warum?
select 1-value/
(
select sum(value)
from v$sysstat
where lower(name) in ('consistent gets','db block gets')
)
from v$sysstat
where lower(name) in ('physical reads')
;
,938784339
,93226288
,92621924
,919666046
Hit Ratio nimmt ab.
14. Setzen Sie den Buffer_Cache auf 38 MB.
alter system set db_cache_size=38M;15. Fragen Sie die Tabelle Angestellte ab.
select count(*) from ANGESTELLTE;16. Schauen Sie sich das Hit Ratio an. Was passiert und warum?
select 1-value/
(
select sum(value)
from v$sysstat
where lower(name) in ('consistent gets','db block gets')
)
from v$sysstat
where lower(name) in ('physical reads')
;
,697806485
17. Fragen Sie die Tabelle Angestellte ab.
Da Sie die Abfrage gleich wiederholen möchten, sollten die Blöcke nicht gleich wieder aus dem cache rausfliegen.
Was müssen Sie tun?
,691264487 ,68297872318. Stellen Sie sicher, dass Full-Table-Scans der Tabelle Angestellte immer am MRU-Ende angefügt werden. Was müssen Sie hierfür tun?
select /*+ CACHE(ANGESTELLTE) */ count(*) from ANGESTELLTE ; ,73996183519. Probieren Sie den Cache-Advise aus.
,727582741
grant CREATE SESSION to scott; grant CREATE TABLE to scott; grant CREATE MATERIALIZED VIEW to scott; grant QUERY REWRITE to scott;Da Materialized View unter sys nicht möglich sind, loggen wir uns als scott ein.
connect scott/tiger@testdb29;
start ~/1ZO-033/Northwind-Skript/suppliers.txt start ~/1ZO-033/Northwind-Skript/products.txt set timing on;
alter session set query_rewrite_enabled=true; alter session set query_rewrite_integrity=enforced; analyze table products compute statistics; analyze table suppliers compute statistics;
select q.companyname, p.productname, q.Preis
from products p,
(
select s.companyname as companyname,s.supplierid, min(p.unitprice) as Preis
from products p, suppliers s
where p.supplierid = s.supplierid
group by s.companyname, s.supplierid
) q
where p.supplierid = q.supplierid
and q.Preis = p.unitprice
;
30 Zeilen ausgewählt.
Abgelaufen: 00:00:00.03
create materialized view mv_min_preise
build immediate
enable query rewrite
as
select q.companyname, p.productname, q.Preis
from products p,
(
select s.companyname as companyname,s.supplierid, min(p.unitprice) as Preis
from products p, suppliers s
where p.supplierid = s.supplierid
group by s.companyname, s.supplierid
) q
where p.supplierid = q.supplierid
and q.Preis = p.unitprice
;
-- refresh on commit nicht möglich ??
select * from mv_min_preise; 30 Zeilen ausgewählt. Abgelaufen: 00:00:00.01
select q.companyname, p.productname, q.Preis
from products p,
(
select s.companyname as companyname,s.supplierid, min(p.unitprice) as Preis
from products p, suppliers s
where p.supplierid = s.supplierid
group by s.companyname, s.supplierid
) q
where p.supplierid = q.supplierid
and q.Preis = p.unitprice
;
Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=4674)
1 0 TABLE ACCESS (FULL) OF 'MV_MIN_PREISE' (Cost=2 Card=82 Bytes=4674)
In der Unix-Shell:
cd /oracle/admin/testdb29/udump/ tkprof testdb29_ora_1792.trc pinguin.trc
1. Stellen Sie das Undo-Management auf Manuell um.
select * from v$tablespace; ... 1 UNDOTBS1 YES ...Undo-Tablespace ist vorhanden.
alter system set undo_management=manual scope=spfile; shutdown immediate; startup;2. Erstellen Sie ein neues Undo-Tablespace namens undots02. Alle weiteren Parameter können Sie frei wählen. Schalten Sie diesen Online.
CREATE UNDO TABLESPACE undots02 DATAFILE '/oracle/oradata/testdb29/undots02.dbf' SIZE 100M AUTOEXTEND ON ; Tablespace wurde angelegt. alter tablespace undots02 online;3. Erstellen Sie in diesem Tablespace 5 Rollbacksegmente (rbs01 bis rbs05) und schalten Sie diese Online.
CREATE ROLLBACK SEGMENT rbs01 TABLESPACE undots02; CREATE ROLLBACK SEGMENT rbs02 TABLESPACE undots02; CREATE ROLLBACK SEGMENT rbs03 TABLESPACE undots02; CREATE ROLLBACK SEGMENT rbs04 TABLESPACE undots02; CREATE ROLLBACK SEGMENT rbs05 TABLESPACE undots02; alter rollback segment rbs01 online; alter rollback segment rbs02 online; alter rollback segment rbs03 online; alter rollback segment rbs04 online; alter rollback segment rbs05 online;4. Ändern Sie den Preis aller Produkte der Tabelle products um 10% nach oben. Die Undo-Informationen sollten im Rollback-Segment rbs01 erstellt werden.
set transaction use rollback segment rbs01; start ~/1ZO-033/Northwind-Skript/products.txt5. Versuchen Sie, die Szenarien 1 bis 5 nachzuspielen.
GRANT select ON products TO scott;Szenario 1
-- Session 1: connect "sys/sys@testdb29 as sysdba"; -- Session 2: connect scott/tiger@testdb29; -- Session 1: update products set unitprice = unitprice*1.1; 77 Zeilen wurden aktualisiert. -- Session 2: select productname, unitprice from sys.products where productid = '75' ; PRODUCTNAME UNITPRICE ---------------------------------------- ---------- Rhonbräu Klosterbier 9,38 -- Session 1: commit; -- Session 2: select productname, unitprice from sys.products where productid = '75' ; PRODUCTNAME UNITPRICE ---------------------------------------- ---------- Rhonbräu Klosterbier 10,32Szenario 2 und 3
-- Session 1: delete from products where productid=77; select productname, unitprice from sys.products where productid = '77'; Es wurden keine Zeilen ausgewählt. -- Session 2: select productname, unitprice from sys.products where productid = '77'; PRODUCTNAME UNITPRICE ---------------------------------------- ---------- Original Frankfurter grüne So¯e 17,3 -- Session 1: commit; -- Session 2: select productname, unitprice from sys.products where productid = '77'; Es wurden keine Zeilen ausgewählt.Szenario 4
-- Session 2: set transaction read only; select productname, unitprice from sys.products where productid = '75'; PRODUCTNAME UNITPRICE ---------------------------------------- ---------- Rhönbräu Klosterbier 10,32 -- Session 1: update products set unitprice = unitprice*1.1; commit; -- Session 2: set transaction read only; select productname, unitprice from sys.products where productid = '75'; PRODUCTNAME UNITPRICE ---------------------------------------- ---------- Rhönbräu Klosterbier 10,32 -- Session 1: update products set unitprice = unitprice*1.1; commit; select productname, unitprice from sys.products where productid = '75'; PRODUCTNAME UNITPRICE ---------------------------------------- ---------- Rhönbräu Klosterbier 12,49 -- Session 2: select productname, unitprice from sys.products where productid = '75'; PRODUCTNAME UNITPRICE ---------------------------------------- ---------- Rhönbräu Klosterbier 10,32 -- -- Ein commit beendet set transaction read only: commit; -- Session 1: update products set unitprice = unitprice*1.1; commit; select productname, unitprice from sys.products where productid = '75'; PRODUCTNAME UNITPRICE ---------------------------------------- ---------- Rhönbräu Klosterbier 13,74 -- Session 2: select productname, unitprice from sys.products where productid = '75'; PRODUCTNAME UNITPRICE ---------------------------------------- ---------- Rhönbräu Klosterbier 13,74Szenario 5:
-- Session 2: set transaction read only; -- Session 1: -- 12 mal insert into sys.products select * from sys.products; commit; delete from sys.products where productid=75; 4096 Zeilen wurden gelöscht. insert into sys.products select * from sys.products; ... -- Session 2: select productname, unitprice from sys.products where productid = '75'; select productname, unitprice from sys.products where productid = '75' * FEHLER in Zeile 1: ORA-01555: Snapshot zu alt: Rollback-Segmentnummer 24 namens "RBS04" ist zu klein.
drop table ...;2. Erstellen Sie die Datenbank northwind vom Skript.
start ~/1ZO-033/Northwind-Skript/alles.txt3. Führen Sie alle notwendigen Schritte aus, um StatsPack zu installieren.
start /oracle/ora92/rdbms/admin/spcreate.sql ... Geben Sie einen Wert für perfstat_password ein: pinguin ... Geben Sie einen Wert für default_tablespace ein: USERS ... Geben Sie einen Wert für temporary_tablespace ein: TEMP ... NOTE: SPCPKG complete. Please check spcpkg.lis for any errors.4. Erstellen Sie folgende Abfragen, speichern Sie diese, um diese Abfragen später ausführen zu können.
Wie viele Kunden haben Getränke bestellt?
select distinct companyname
from
customers cus,
orders ord,
order_details ode,
products pro,
categories cat
where
cus.customerid = ord.customerid
and ode.orderid = ord.orderid
and pro.productid = ode.productid
and cat.categoryid = pro.categoryid
and cat.Categoryname = 'Beverages'
;
Wie heißt der älteste Mitarbeiter?
select lastname,firstname,birthdate from employees where birthdate in (select min(birthdate) from employees) ;Welche Produkte liefern deutsche Lieferanten?
select distinct pro.productname
from
suppliers sup, products pro
where
sup.supplierid = pro.supplierid
and country = 'Germany'
;
5. Erstellen Sie den ersten Snapshot.
sqlplus perfstat/pinguin@testdb29 execute statspack.snap6. Führen Sie die Abfragen aus.
s.o.
7. Erstellen Sie den zweiten Snapshot
sqlplus perfstat/pinguin@testdb29 execute statspack.snap8. Erstellen Sie den Bericht
select snap_id, snap_time from stats$snapshot;
SNAP_ID SNAP_TIM
---------- --------
1 05.03.04
2 05.03.04
start /oracle/ora92/rdbms/admin/spreport.sql
...
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Geben Sie einen Wert für begin_snap ein: 1
Begin Snapshot Id specified: 1
Geben Sie einen Wert für end_snap ein: 2
...
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
Geben Sie einen Wert für report_name ein: /tmp/sp_report_1_2
9. Schauen Sie sich den Bericht im Betriebssystem an.
less /tmp/sp_report_1_2
1. Führen Sie folgende Anweisung aus, um den Cluster zu erstellen.
CREATE CLUSTER personnel (department_number NUMBER(2)) SIZE 512;2. Was bedeutet Size 512?
SIZE sollte in der Größe eines "Datensatz" definiert werden.
3. Erstellen Sie eine Tabelle emp und ordnen diese dem Cluster zu.
CREATE TABLE emp
(
empno NUMBER PRIMARY KEY,
ename VARCHAR2(10) NOT NULL CHECK (ename = UPPER(ename)),
job VARCHAR2(9),
mgr NUMBER ,
hiredate DATE ,
sal NUMBER(10,2) CHECK (sal > 500),
comm NUMBER(9,0) DEFAULT NULL,
deptno NUMBER(2) NOT NULL
)
CLUSTER personnel (deptno)
;
4. Erstellen Sie eine Tabelle dept und ordnen diese dem Cluster zu.
CREATE TABLE dept
(
deptno NUMBER(2),
dname VARCHAR2(9),
loc VARCHAR2(9)
)
CLUSTER personnel (deptno)
;
5. Versuchen Sie einen Datensatz in eine der beiden Tabellen hinzuzufügen.
insert into dept values (1,'Head','Berlin');
*
FEHLER in Zeile 1:
ORA-02032: Cluster-Tabellen sind erst nach Aufbau des Cluster-Indexes zu
verwenden
6. Erstellen Sie nun den Index-Cluster.
CREATE INDEX idx_personnel ON CLUSTER personnel;7. Versuchen Sie einen Datensatz in eine der beiden Tabellen hinzuzufügen.
insert into dept values (1,'Head','Berlin'); 1 Zeile wurde erstellt. commit;8. Veranlassen Sie, dass die Tabellen products_cl und suppliers_cl in einem Index-Cluster zusammengefasst werden. Begründen Sie Ihre Wahl für die Size.
Berechnung der Größe von SIZE:
describe products; Name Null? Typ ----------------------------------------- -------- ------------- PRODUCTID NOT NULL NUMBER(38) PRODUCTNAME NOT NULL VARCHAR2(40) SUPPLIERID NUMBER(38) CATEGORYID NUMBER(38) QUANTITYPERUNIT VARCHAR2(20) UNITPRICE NUMBER(20,2) UNITSINSTOCK NUMBER(38) UNITSONORDER NUMBER(38) REORDERLEVEL NUMBER(38) DISCONTINUED NOT NULL NUMBER(38) Spaltenbreite gesamt: 346
describe suppliers; Name Null? Typ ----------------------------------------- -------- -------------- SUPPLIERID NOT NULL NUMBER(38) COMPANYNAME NOT NULL VARCHAR2(40) CONTACTNAME VARCHAR2(30) CONTACTTITLE VARCHAR2(30) ADDRESS VARCHAR2(60) CITY VARCHAR2(15) REGION VARCHAR2(15) POSTALCODE VARCHAR2(10) COUNTRY VARCHAR2(15) PHONE VARCHAR2(24) FAX VARCHAR2(24) HOMEPAGE VARCHAR2(255)Spaltenbreite gesamt: 556
Ermitteln des Durchschnittswertes der 1-n-Beziehung:
select avg(count(*)) from products group by supplierid order by 1; AVG(COUNT(*)) ------------- 2,65517241Spaltenbreite gesamt der ersten Tabelle minus Spaltenbreite der gemeinsamen Spalte (SUPPLIERID) multipliziert mit dem ermittelten Durchschnitt. Die Spaltenbreite der Tabelle zwei wird dazu addiert und zur Sicherheit wird alles mit 20% multipliziert.
((346 - 38) * 2,65517241 + 556) * 1,2 = 1648,551722736
Das Ergebnis wird aufgerundet.
Cluster erstellen:
CREATE CLUSTER prod_supp_cl (SUPPLIERID NUMBER(38)) SIZE 1700;Cluster Index erstellen:
CREATE INDEX idx_prod_supp_cl ON CLUSTER prod_supp_cl;Erste Tabellen erstellen
CREATE TABLE products_cl
(
ProductID int NOT NULL ,
ProductName varchar2 (40) NOT NULL ,
SupplierID number(38) NULL ,
CategoryID int NULL ,
QuantityPerUnit varchar2 (20) NULL ,
UnitPrice number(20,2) NULL ,
UnitsInStock int NULL ,
UnitsOnOrder int NULL ,
ReorderLevel int NULL ,
Discontinued int NOT NULL
)
CLUSTER prod_supp_cl(SUPPLIERID)
;
insert into products_cl select * from products;
Zweite Tabelle
alter table suppliers modify (SUPPLIERID number(38)); CREATE TABLE suppliers_cl CLUSTER prod_supp_cl(SUPPLIERID) as select * from suppliers ;Im folgenden werden Sie eine IOT erstellen. Führen Sie hierfür folgende Schritte durch: Führen Sie folgende Anweisung aus:
CREATE TABLE my_iot (partno number,name varchar2(20),CONSTRAINT pk_my_iot PRIMARY KEY (partno)) ORGANIZATION INDEX TABLESPACE users including name OVERFLOW TABLESPACE users ;
Bibliography
Appendix
Fundamentals I - Übungen und Lösungen
Stefan Hietel dama.go GmbH, Robert Warnke http://rowa.giso.de