Übungen und Lösungen: Oracle 9i
Werbung
EINFÜHRUNG Übungen Performance Tuning AGENDA Advanced Education BUFFER CACHE INDIZES Oracle Certified Professional INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Database Administrator 9i ROLLBACK SEGMENT STATSPACK STORED OUTLINES Übungen Performance Tuning Vorbereitung OCP Exam 1Z0-033 TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 1 von164 + / EXIT EINFÜHRUNG Einführung Übungen Performance Tuning AGENDA BUFFER CACHE Arten der Zertifizierung INDIZES INDEX-CLUSTER MATERIALIZED VIEWS Bedingungen / Dauer / Preise OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Vor- und Nachteile STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 Vorbereitung BUFFER CACHE INDIZES MATERIALIZED VIEWS Hinweis OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 2 von164 + / EXIT Einführung Arten der Zertifizierung EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE OCM INDIZES Oracle Certified Master INDEX-CLUSTER Achieved by the Top Oracle Professionals Provides a performance based benchmark for hiring experts MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN OCP ROLLBACK SEGMENT STATSPACK Oracle Certified Professional STORED OUTLINES Hiring manager approved industry benchmark Get certified, establish credibility, increase opportunities Demonstrates professional commitment in developing or administration TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES OCA MATERIALIZED VIEWS Oracle Certified Associate OPTIMIZER RESSOURCENPLAN Can be used as the first step in a professional career and development path ROLLBACK SEGMENT James DiIanni Director Oracle Certification Program RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 3 von164 + STATSPACK STORED OUTLINES / EXIT Einführung Arten der Zertifizierung EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE Erster Einstieg http://www.oracle.com/education/certification INDIZES INDEX-CLUSTER MATERIALIZED VIEWS Entscheidung für eine Laufbahn OPTIMIZER RESSOURCENPLAN Oracle Database 10g Oracle Database 9i Oracle9i Forms Developer Oracle Application Server 10g Oracle9i Application Server ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE Beispiellaufbahn OCP DBA 9i 1Z0-007 Oracle 9i 1Z0-031 Oracle 9i 1Z0-032 Oracle 9i 1Z0-033 Oracle 9i INDIZES (4 Prüfungen) : SQL : Fundamentials I : Fundamentials II : Performance Tuning RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 4 von164 MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES + / EXIT Einführung Bedingungen / Dauer / Preise EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE Je nach Kenntnisstand INDIZES INDEX-CLUSTER 2 bis 7 Monate MATERIALIZED VIEWS OPTIMIZER Praktische Erfahrung ist sinnvoller als stures Auswendiglernen RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES 10 Stunden pro Woche für Fragetechnik und praktische Aufgaben TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE Preis für Instructor Based Training ca. 3.000,00 Euro Preis pro Prüfung ca. 140,00 Euro Vgl. Informationen zur Vorbereitung RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 5 von164 + INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Einführung EINFÜHRUNG Vor- und Nachteile Übungen Performance Tuning AGENDA BUFFER CACHE + Nachweisbare Fähigkeiten in einem bestimmten Bereich + Hintergrundwissen über einen normalen Tagesjob hinaus + Enormer Detailierungsgrad durch theoretische Datenbanklehre + Verbesserte Karrierechancen durch privaten Einsatz für den Beruf + Erweiterung für Know-How auch bei Einsatz in anderen Berufsbereichen + International sehr angesehene Auszeichnung + Begleitend Fremdsprachenlernen durch freie Sprachwahl (bevorzugt Englisch) INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 6 von164 + / EXIT Einführung EINFÜHRUNG Vor- und Nachteile Übungen Performance Tuning AGENDA BUFFER CACHE – Enormer zeitlicher Einsatz muss sich nicht im Gehalt wiederspiegeln INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER – Unternehmen könnten Zertifizierungen von Mitarbeitern voraussetzen RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK – Kein Garant für Berufseinstellung, da viele Faktoren beim Einstellungsgespräch ausschlaggebend sind STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 – Prüfungen bei OCA und OCP enthalten keinen Praxisbezug BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER – Verwässerung durch “Zertifikatsschwemme” RESSOURCENPLAN ROLLBACK SEGMENT – Vorgesetztes Lernen, freies Denken geht verloren STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 7 von164 + / EXIT Einführung EINFÜHRUNG Vorbereitung Übungen Performance Tuning AGENDA BUFFER CACHE Self-Test-Software (250-300 Fragen) und Oracle-Press bzw. Sybex OCP Bücher. Simulation von prüfungsnahen Fragestellungen. Kosten Bücher ca. 70,00 € und pro Examen ca. 140,00 €. 2 Jahre praktische Berufserfahrung wird empfohlen. http://www.selftestsoftware.com INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Technical Based Trainings und Self-Test Software, ca. 1.000,00 € - 3.000.00 €. 1-2 Jahre Berufserfahrung. http://www.oracle.com/education TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES Instructor Based Trainings und Self-Test Software, ca. 3.000,00 €. 0-1 Jahre Berufserfahrung. Wird in Deutschland auch von der Arbeitsagentur als Fortbildungsmaßnahme [für Arbeitssuchende] angeboten. http://www.oracle.com/education RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 8 von164 + MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Einführung EINFÜHRUNG Vorbereitung Übungen Performance Tuning AGENDA BUFFER CACHE Empfohlene Bücher für Kursteilnehmer INDIZES INDEX-CLUSTER PACK, Charles, Oracle 9i Performance Tuning Exam Guide 1Z0-033, Osborne Oracle Press 2002 MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT Empfehlung für Dozenten und Kursteilnehmer – Vermittlung von fortgeschrittenen Administrationstechniken aus der Praxis und Tipps – STATSPACK STORED OUTLINES TKPROF FOOT, Christopher T., OCP Instructors Guide for Oracle DBA Certification, Rampant TechPress, 2003 Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS Empfehlung Vorbereitung auf Vorstellungsgespräche OPTIMIZER RESSOURCENPLAN BURLESON, Donald / AULT, Mike, Conducting the Oracle Job Interview, Rampant TechPress 2003 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 9 von164 + ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Einführung EINFÜHRUNG Hinweis Übungen Performance Tuning AGENDA BUFFER CACHE Diese Präsentation behandelt ausgewählte INDIZES INDEX-CLUSTER MATERIALIZED VIEWS Administrationstechniken und simuliert OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT ausgewählte prüfungsnahe Fragen STATSPACK STORED OUTLINES TKPROF zur Vorbereitung auf das Testfragen OCP Exam 1Z0-033 BUFFER CACHE OCP Exam 1Z0-033 : Performance Tuning INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Kostenloser Download der prüfungsnahen Testfragen vom 2005-11-21 http://www.freebraindumps.com/braindump-43659.htm ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 10 von164 + / EXIT EINFÜHRUNG Agenda Übungen Performance Tuning AGENDA Übungen Performance Tuning Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS Buffer Cache Indizes Index-Cluster Materialized Views Optimizer Ressourcenplan Rollback Segmente Statspack Stored Outlines Tkprof Buffer Cache Indizes Materialized Views Optimizer Ressourcenplan Rollback Segmente StatsPack Stored Outlines OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 11 von164 + / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Übung INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER BUFFER CACHE RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: ca. 2 Stunden STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 12 von164 + / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 1. Erstellen Sie eine Tabelle ABTEILUNG mit einer Spalte S1 CHAR(1000) und zehn Datensätzen (von ’AAA’ bis ’JJJ’) BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER CREATE TABLE abteilung (s1 CHAR(1000)); RESSOURCENPLAN ROLLBACK SEGMENT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO abteilung abteilung abteilung abteilung abteilung abteilung abteilung abteilung abteilung abteilung VALUES(’AAA’); VALUES(’BBB’); VALUES(’CCC’); VALUES(’DDD’); VALUES(’EEE’); VALUES(’FFF’); VALUES(’GGG’); VALUES(’HHH’); VALUES(’III’); VALUES(’JJJ’); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 13 von164 + STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA BUFFER CACHE 2. Verdoppeln Sie die Tabelle 10 mal. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS INSERT INTO abteilung SELECT * FROM abteilung; COMMIT; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF 3. Wie viel Datensätze haben Sie nun? Testfragen OCP Exam 1Z0-033 BUFFER CACHE SELECT COUNT(*) FROM abteilung; INDIZES MATERIALIZED VIEWS Es existieren nun 10240 Datensätze. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 14 von164 + / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA BUFFER CACHE 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. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF SELECT SUM(bytes) FROM dba_segments WHERE owner = ’SYS’ AND segment_name = ’ABTEILUNG’; Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER Anzahl der Bytes 12582912 = 12 MegaByte RESSOURCENPLAN ROLLBACK SEGMENT ALTER SYSTEM SET db_keep_cache_size=16M; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 15 von164 + STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA BUFFER CACHE Anzeige: INDIZES 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; INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS Tabelle in den Keep-Pool laden bzw. Speichern OPTIMIZER RESSOURCENPLAN ALTER TABLE ABTEILUNG STORAGE (buffer_pool KEEP); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 16 von164 + ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 5. Führen Sie mehrfach eine Abfrage der Tabelle Abteilung durch und beobachten Sie jeweils die Hit Ratio für den Keep-Pool. BUFFER CACHE INDIZES INDEX-CLUSTER Abfrage (mehrmals) SELECT COUNT(*) FROM abteilung WHERE s1=’AAA’; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN 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’); ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN 0,959986246 0,960109863 Hit Ratio steigt. 0,960304683 ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 17 von164 + / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 6. Beobachten Sie ebenfalls die Werte in der Tabelle V$BUFFER_ POOL statistics (PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS). BUFFER CACHE INDIZES INDEX-CLUSTER SELECT NAME, 100-round( (physical_reads/(db_block_gets+consistent_gets))*100,2) AS hit_ratio FROM v$buffer_pool_statistics; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES FEHLER in Zeile 1: ORA-01476: Divisor ist Null Da KEEP gleich 0 ist, ggf. ANALYZE ?? TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE SELECT name, physical_reads, db_block_gets, consistent_gets FROM v$buffer_pool_statistics; INDIZES MATERIALIZED VIEWS NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS --------- --------------------------- --------------------------- ----------------------------KEEP 0 0 0 DEFAULT 17251 357396 202093 OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 18 von164 + / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 7. Löschen Sie die Tabelle Abteilung. BUFFER CACHE INDIZES INDEX-CLUSTER DROP TABLE abteilung; MATERIALIZED VIEWS 8. Führen Sie die gleichen Tests für den Recycle-Pool durch. Halten Sie auch hier Ihre Ergebnisse schriftlich fest (bis Punkt 4 sind die Schritte identisch): OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES ALTER SYSTEM SET db_keep_cache_size=0M; ALTER SYSTEM SET db_recycle_cache_size=16M; ALTER TABLE abteilung STORAGE (buffer_pool recycle); TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS Folgende Abfrage mehrmals ausführen: OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT SELECT COUNT(*) FROM abteilung WHERE s1=’AAA’; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 19 von164 STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning Abfrage Hit Ratio: AGENDA BUFFER CACHE 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’); 0,958975396 0,959156127 0,959268817 INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF 0,959413019 SELECT name, 100-round ( (physical_reads/(db_block_gets+consistent_gets)) *100,2 ) AS hit_ratio FROM v$buffer_pool_statistics; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 20 von164 + Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA FEHLER in Zeile 1: ORA-01476: Divisor ist Null BUFFER CACHE INDIZES INDEX-CLUSTER SELECT name, physical_reads, db_block_gets, consistent_gets FROM v$buffer_pool_statistics; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS --------- --------------------------- --------------------------- ----------------------------RECYCLE 0 0 0 DEFAULT 19180 394619 223113 ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen 9. Setzen Sie den buffer cache auf 16 MB. OCP Exam 1Z0-033 BUFFER CACHE ALTER SEYSTEM SET db_cache_size=16M; INDIZES 10. Erzeugen Sie eine Tabelle angestellte auf Basis der Tabelle SCOTT.EMP MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN CREATE TABLE angestellte AS SELECT * FROM scott.emp; ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 21 von164 + / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 11. Vergrößern Sie die Tabelle so lange, bis diese 32MB groß ist. BUFFER CACHE INDIZES INSERT INTO angestellte SELECT * FROM angestellte; COMMIT; … INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN SELECT sum(bytes) FROM dba_segments WHERE owner = ’SYS’ AND segment_name = ’ANGESTELLTE’; ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 Anzahl der Bytes 45088768 BUFFER CACHE INDIZES MATERIALIZED VIEWS 12. Fragen Sie die Tabelle ab. OPTIMIZER RESSOURCENPLAN SELECT COUNT(*) FROM angestellte; ROLLBACK SEGMENT STATSPACK Anzahl der Datensätze 917504 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 22 von164 STORED OUTLINES + / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA BUFFER CACHE 13. Schauen Sie sich das Hit Ratio an. Was passiert und warum? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS 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’); OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN 0,938784339 0,93226288 Hit Ratio nimmt ab. 0,92621924 0,919666046 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 23 von164 ROLLBACK SEGMENT STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning 14. Setzen Sie den Buffer Cache auf 38 MB. AGENDA BUFFER CACHE ALTER SYSTEM SET db_cache_size=38M; INDIZES INDEX-CLUSTER 15. Fragen Sie die Tabelle Angestellte ab. MATERIALIZED VIEWS OPTIMIZER SELECT COUNT(*) FROM angestelte; RESSOURCENPLAN 16. Schauen Sie sich das Hit Ratio an. Was passiert und warum? ROLLBACK SEGMENT STATSPACK STORED OUTLINES 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’); TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Die Hit Ratio beträgt 0,697806485 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 24 von164 STORED OUTLINES + / EXIT Übung EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 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? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN 0,691264487 18. 0,682978723 ROLLBACK SEGMENT 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; STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS 0,739961835 OPTIMIZER RESSOURCENPLAN 19. Probieren Sie den Cache-Advise aus. ROLLBACK SEGMENT STATSPACK 0,727582741 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 25 von164 STORED OUTLINES + / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Übung INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER INDIZES RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: ca. 2 Stunden STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 26 von164 + / EXIT Übung EINFÜHRUNG INDIZES Übungen Performance Tuning 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? AGENDA BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS 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; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES INSERT INTO order_det_prod SELECT * FROM order_det_prod; COMMIT; SELECT COUNT(*) FROM order_det_prod; TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE Datensätze in der Tabelle: 8826880 INDIZES MATERIALIZED VIEWS Abgelaufen: 00:05:32.09 Ausführungsplan ------------------------------------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 10 SORT (AGGREGATE) 21 TABLE ACCESS (FULL) OF ’ORDER_DET_PROD’ RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 27 von164 + OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG INDIZES Übungen Performance Tuning AGENDA 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 ? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS SELECT max(orderid) FROM order_det_prod; OPTIMIZER RESSOURCENPLAN Die Bestellnummer lautet 11077. ROLLBACK SEGMENT Abgelaufen: 00:01:06.05 Ausführungsplan ------------------------------------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 10 SORT (AGGREGATE) 21 TABLE ACCESS (FULL) OF ’ORDER_DET_PROD’ STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE 3. Erstellen Sie einen Index namens id1 basierend auf der Spalte orderid. CREATE INDEX id1 ON order_det_prod(orderid); INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT Index wurde angelegt. Abgelaufen: 00:17:14.03 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 28 von164 STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG INDIZES Übungen Performance Tuning AGENDA BUFFER CACHE 4. Ermitteln Sie die größte Bestellnummer. Wie lange dauert die Abfrage nunmehr ? Mit welcher Methode wurde dieser Datensatz ermittelt? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN SELECT max(orderid) FROM order_det_prod; ROLLBACK SEGMENT STATSPACK STORED OUTLINES Die Bestellnummer lautet 11077. TKPROF Testfragen 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) OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 29 von164 + / EXIT Übung EINFÜHRUNG INDIZES Übungen Performance Tuning AGENDA BUFFER CACHE 5. Wie oft wurde bisher ‘Chai’ bestellt (bitte keine Funktion wie lower etc. benutzen)? Wie schnell wurde die Abfrage ausgeführt? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN SELECT COUNT(*) FROM order_det_prod WHERE productname=’Chai’; ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen Es wurde 155648 Mal ‘Chai’ bestellt. OCP Exam 1Z0-033 BUFFER CACHE 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’ RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 30 von164 + INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG INDIZES Übungen Performance Tuning AGENDA 6. Erstellen Sie einen geeigneten Index namens id2 und führen Sie die Abfrage erneut aus. Wie schnell wurde die Abfrage nunmehr ausgeführt? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER CREATE INDEX id2 ON order_det_prod(productname); RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Index wurde angelegt. Abgelaufen: 00:25:17.03 TKPROF Testfragen 7. Erstellen Sie eine Tabelle namens EMP_COP basierend auf der employees-Tabelle. Die Tabelle sollte identisch zur EMPLOYEES sein (gleiche Spalten, gleiche Zeilen) OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT CREATE TABLE emp_cop AS SELECT * FROM employees; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 31 von164 STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG INDIZES Übungen Performance Tuning 8. Erstellen Sie einen Index namens id3 auf Basis von lastname CREATE AGENDA BUFFER CACHE INDEX id3 ON emp_cop(lastname); INDIZES 9. Nun werden Sie den Index id3 analysieren, dass heißt, den Prozentsatz von ungenutztem (durch Löschen) zu dem gesamten Platz ermitteln. INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ANALYZE INDEX id3 VALIDATE STRUCTURE; SELECT (del_lf_rows_len/lf_rows_len)*100 FROM index_stats; ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF 0% gelöschte Rows. Testfragen OCP Exam 1Z0-033 10. Löschen Sie den ältesten Mitarbeiter. Wie viele Datensätze wurden gelöscht? BUFFER CACHE INDIZES DELETE FROM emp_cop WHERE birthdate = ( SELECT min(birthdate) FROM emp_cop ); MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK 1 Zeile wurde gelöscht. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 32 von164 STORED OUTLINES + / EXIT Übung EINFÜHRUNG INDIZES Übungen Performance Tuning AGENDA BUFFER CACHE 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. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT ANALYZE INDEX id3 VALIDATE STRUCTURE; SELECT (del_lf_rows_len/lf_rows_len)*100 FROM index_stats; STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE 11% gelöschte ROWs INDIZES MATERIALIZED VIEWS 12. Nun werden Sie ein “rebuild” des Index durchführen. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT ALTER INDEX id3 REBUILD; STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 33 von164 + / EXIT Übung EINFÜHRUNG INDIZES Übungen Performance Tuning AGENDA BUFFER CACHE 13. Nun werden Sie den Index analysieren, dass heißt, den Prozentsatz von ungenutztem (durch Löschen) zu dem gesamten Platz ermitteln. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Legen Sie Ihre Schlussfolgerungen dar: ROLLBACK SEGMENT STATSPACK ANALYZE INDEX id3 VALIDATE STRUCTURE; SELECT (del_lf_rows_len/lf_rows_len)*100 FROM index_stats; STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 -------------------------------------------------------------0 MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK 0% gelöschte Rows. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 34 von164 STORED OUTLINES + / EXIT Übung EINFÜHRUNG INDIZES – Bitmap Index Übungen Performance Tuning AGENDA BUFFER CACHE Im Folgenden werden Sie einen Bitmap-Index erstellen und testen: INDIZES INDEX-CLUSTER 1. Erweitern Sie die Tabelle ORDER_DET_PROD um eine Spalte namens DONE (varchar2(1)). MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ALTER TABLE order_det_prod ADD(done VARCHAR2(1)); ROLLBACK SEGMENT STATSPACK 2. Für alle ORDERIDs unter 11000 soll Done ’N’ sein und für alle darüuber soll Done ’Y’ sein. STORED OUTLINES TKPROF Testfragen UPDATE order_det_prod SET done = ’N’ WHERE orderid < 11000; UPDATE order_det_prod SET done = ’Y’ WHERE orderid > 11000; COMMIT; OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT Hier geht der Rechner in die Knie! STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 35 von164 + / EXIT Übung EINFÜHRUNG INDIZES – Bitmap Index Übungen Performance Tuning AGENDA BUFFER CACHE 3. In wie vielen Datensätzen steht bei Done ‘Y’ drin? INDIZES INDEX-CLUSTER SELECT COUNT(*) FROM order_det_prod WHERE done=’Y’; MATERIALIZED VIEWS OPTIMIZER Es wurden 880640 Datensätze ausgewählt. RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES 4. Erstellen Sie einen Bitmap-Index auf der Spalte Done. TKPROF CREATE BITMAP INDEX id4 ON order_det_prod(done); Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES 5. In wie vielen Datensätzen steht bei Done ‘N’ drin (Bitte benutzen Sie kein * beim Zählen)? MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN SELECT COUNT(done) FROM order_det_prod WHERE done=’N’; ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 36 von164 + / EXIT Übung INDIZES – Funktionsbasierter Index EINFÜHRUNG Übungen Performance Tuning Im Folgenden werden Sie einen funktionsbasierten Index erstellen und testen. 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’; AGENDA BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES 2. Erstellen Sie einen normalen Index, der die Suche beschleunigen soll. Was passiert und warum ? TKPROF Testfragen OCP Exam 1Z0-033 CREATE INDEX id5 ON order_det_prod(productname); BUFFER CACHE INDIZES 3. Löschen Sie diesen Index und schreiben Sie einen funktionsbasierten Index. Was passiert und warum ? MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN DROP INDEX id5; CREATE INDEX id6 ON order_det_prod(lower(productname)); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 37 von164 + ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Übung INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER INDEX-CLUSTER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: ca. 2 Stunden STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 38 von164 + / EXIT Übung EINFÜHRUNG Index-Cluster Übungen Performance Tuning 1. Führen Sie folgende Anweisung aus, um den Cluster zu erstellen. AGENDA BUFFER CACHE CREATE CLUSTER personnel (department_number NUMBER(2)) SIZE 512; 2. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS Was bedeutet Size 512 ? OPTIMIZER SIZE sollte in der Größe eines ”Datensatzes“ 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); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 39 von164 + RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Index-Cluster Übungen Performance Tuning AGENDA BUFFER CACHE 4. Erstellen Sie eine Tabelle DEPT und ordnen diese dem Cluster zu. INDIZES INDEX-CLUSTER CREATE TABLE dept ( deptno NUMBER(2), dname VARCHAR2(9), loc VARCHAR2(9) ) CLUSTER personnel (deptno); MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF 5. Versuchen Sie einen Datensatz in eine der beiden Tabellen hinzuzufügen. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INSERT INTO dept VALUES (1,’Head’,’Berlin’); INDIZES FEHLER in Zeile 1: ORA-02032: Cluster-Tabellen sind erst nach Aufbau des Cluster-Indexes zuverwenden MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN 6. Erstellen Sie nun den Index-Cluster. ROLLBACK SEGMENT CREATE INDEX idx_personnel ON CLUSTER personnel; STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 40 von164 + / EXIT Übung EINFÜHRUNG Index-Cluster Übungen Performance Tuning 7. Versuchen Sie einen Datensatz in die Tabellen hinzuzufügen. AGENDA BUFFER CACHE INSERT INTO dept VALUES (1,’Head’,’Berlin’); COMMIT; 8. INDIZES INDEX-CLUSTER Veranlassen Sie, dass die Tabellen PRODUCTS_CL und SUPPLIERS_CL in einem Index-Cluster zusammengefasst werden. Begründen Sie Ihre Wahl für die Berechnung der Größe ‘Size’. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT DESCRIBE PRODUCTS; STATSPACK Name Null? Typ ------------------------------PRODUCTID PRODUCTNAME SUPPLIERID CATEGORYID QUANTITYPERUNIT UNITPRICE UNITSINSTOCK UNITSONORDER REORDERLEVEL DISCONTINUED ---------------------------------------NOT NULL NUMBER(38) NOT NULL VARCHAR2(40) NUMBER(38) NUMBER(38) VARCHAR2(20) NUMBER(20,2) NUMBER(38) NUMBER(38) NUMBER(38) NOT NULL NUMBER(38) Spaltenbreite gesamt: 346 STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 41 von164 TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG Index-Cluster Übungen Performance Tuning AGENDA DESCRIBE SUPPLIERS; BUFFER CACHE Name Null? Typ ------------------------------SUPPLIERID COMPANYNAME CONTACTNAME CONTACTTITLE ADDRESS CITY REGION POSTALCODE COUNTRY PHONE FAX HOMEPAGE --------------------------------------NOT NULL NUMBER(38) NOT NULL VARCHAR2(40) VARCHAR2(30) VARCHAR2(30) VARCHAR2(60) VARCHAR2(15) VARCHAR2(15) VARCHAR2(10) VARCHAR2(15) VARCHAR2(24) VARCHAR2(24) VARCHAR2(255) Spaltenbreite gesamt: 556 INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS 9. Ermitteln des Durchschnittswertes der 1-n-Beziehung: OPTIMIZER RESSOURCENPLAN SELECT AVG(COUNT(*)) FROM products GROUP BY supplierid ORDER BY 1; ROLLBACK SEGMENT STATSPACK Durchschnittswert: 2,65517241 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 42 von164 STORED OUTLINES + / EXIT Übung EINFÜHRUNG Index-Cluster Übungen Performance Tuning AGENDA Spaltenbreite 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. BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ((346 - 38) * 2,65517241 + 556) * 1,2 = 1648,551722736 ROLLBACK SEGMENT STATSPACK Das Ergebnis wird aufgerundet. STORED OUTLINES TKPROF Cluster erstellen: Testfragen OCP Exam 1Z0-033 CREATE CLUSTER prod_supp_cl (SUPPLIERID NUMBER(38)) SIZE 1700; BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER Cluster Index erstellen: RESSOURCENPLAN ROLLBACK SEGMENT CREATE INDEX idx_prod_supp_cl ON CLUSTER prod_supp_cl; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 43 von164 STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG Index-Cluster Übungen Performance Tuning AGENDA 10. Erste Tabellen erstellen. Beachte: CATEGORYID ist als INT zu definieren und nicht als NUMBER. BUFFER CACHE INDIZES INDEX-CLUSTER CREATE TABLE products_cl ( productid INT productname VARCHAR2 (40) supplierid NUMBER (38) categoryid INT quantityperunit VARCHAR2 (20) unitprice NUMBER (20,2) unitsinstock INT unitsonorder INT reorderlevel INT discontinued INT ) CLUSTER prod_supp_cl(SUPPLIERID); MATERIALIZED VIEWS OPTIMIZER NOT NULL , NOT NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NOT NULL RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK INSERT INTO products_cl SELECT * FROM products; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 44 von164 + STORED OUTLINES / EXIT Übung EINFÜHRUNG Index-Cluster Übungen Performance Tuning AGENDA 11. Zweite Tabelle erstellen. Beachte: CATEGORYID ist als INT zu definieren und nicht als NUMBER. Die Quelltabelle muss daher angepasst werden: BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS alter table suppliers modify (SUPPLIERID number(38)); CREATE TABLE suppliers_cl CLUSTER prod_supp_cl(SUPPLIERID) as select * from suppliers; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Im folgenden werden Sie eine IOT erstellen. Führen Sie hierfür folgende Schritte durch: Führen Sie folgende Anweisung aus: Testfragen OCP Exam 1Z0-033 BUFFER CACHE 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; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 45 von164 + INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Übung INDEX-CLUSTER MATERIALIZED VIEWS MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: ca. 2 Stunden STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 46 von164 + / EXIT Übung EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA 1. BUFFER CACHE Geben Sie Scott die folgenden Berechtigungen: INDIZES grant grant grant grant 2. CREATE SESSION to scott; CREATE TABLE to scott; CREATE MATERIALIZED VIEW to scott; QUERY REWRITE to scott; INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Melden Sie sich als scott mit dem Kennwort tiger an der Datenbank an. ROLLBACK SEGMENT STATSPACK connect scott/tiger@testdb29; 3. STORED OUTLINES 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; TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES 4. Verdoppeln Sie die Tabelle 5 mal. Wie viel Datensätze hat die Tabelle? insert into bigtab select * from bigtab; commit; select count(*) from bigtab; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK In der Tabelle befinden sich 751840 Datensätze. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 47 von164 STORED OUTLINES + / EXIT Übung EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA 5. BUFFER CACHE Analysieren Sie die Tabelle. INDIZES analyze table bigtab compute statistics; 6. INDEX-CLUSTER 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. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT OWNER --------------------CTXSYS ELAN HR MDSYS ODM ODM_MTR COUNT(*) -------------6264 1272 816 5640 9768 288 STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS set timing on; OPTIMIZER 7. Führen Sie folgende Befehle aus. RESSOURCENPLAN ROLLBACK SEGMENT alter session set query_rewrite_enabled=true; alter session set query_rewrite_integrity=enforced; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 48 von164 + STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA 8. BUFFER CACHE Erstellen Sie folgende Materialized View: INDIZES INDEX-CLUSTER create materialized view mv_bigtab build immediate refresh on commit enable query rewrite as select owner, count(*) from bigtab group by owner; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Materialized View wurde erstellt. Abgelaufen: 00:00:12.08 Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES 9. Führen Sie folgende Abfrage aus. Messen Sie bitte die Zeit: MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN select owner,count(*) from bigtab group by owner; ROLLBACK SEGMENT STATSPACK Abgelaufen: 00:00:00.00 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 49 von164 STORED OUTLINES + / EXIT Übung EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA BUFFER CACHE 10. Was sind Ihre Schlussfolgerungen? INDIZES INDEX-CLUSTER Es wurde die Materialized View verwendet. Dies ist eine sehr optimale Methode. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN 11. Führen Sie folgende Anweisung aus: ROLLBACK SEGMENT STATSPACK alter session set query_rewrite_enabled=false; STORED OUTLINES TKPROF 12. Was macht die Anweisung? Testfragen OCP Exam 1Z0-033 Die Materialized View kann so nicht mehr verwendet werden. BUFFER CACHE INDIZES MATERIALIZED VIEWS 13. Führen Sie folgende Abfrage aus. Messen Sie bitte die Zeit: OPTIMIZER RESSOURCENPLAN select owner, count(*) from bigtab group by owner; ROLLBACK SEGMENT STATSPACK Abgelaufen: 00:00:07.05 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 50 von164 STORED OUTLINES + / EXIT Übung EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA BUFFER CACHE 14. Was sind Ihre Schlussfolgerungen? INDIZES INDEX-CLUSTER Die Materialized View wurde nicht mehr verwendet. MATERIALIZED VIEWS OPTIMIZER 15. Führen Sie folgende Anweisung aus: RESSOURCENPLAN alter session set query_rewrite_enabled = true; ROLLBACK SEGMENT STATSPACK 16. Was macht die Anweisung? STORED OUTLINES TKPROF Ermöglicht die Verwendung der Materialized View. Testfragen OCP Exam 1Z0-033 17. Fügen Sie einen Datensatz in die Tabelle BIGTAB hinzu. BUFFER CACHE INDIZES insert into bigtab ( OWNER, OBJECT_NAME, OBJECT_ID, CREATED, LAST_DDL_TIME ) values (’Hans’,’bla’,1,sysdate,sysdate); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 51 von164 + MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA BUFFER CACHE 18. Schließen Sie das Einfügen mit COMMIT auch ab. INDIZES INDEX-CLUSTER commit; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN 19. Führen Sie folgende Abfrage aus. ROLLBACK SEGMENT STATSPACK select owner, count(*) from bigtab group by owner; STORED OUTLINES TKPROF Testfragen Abgelaufen: 00:00:00.00 OCP Exam 1Z0-033 BUFFER CACHE INDIZES 20. Was sind Ihre Schlussfolgerungen? Ist der neue Datensatz mit enthalten und wurde der materialisierte View benutzt? MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Mit COMMIT wird die Materialized View aktualisiert. ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 52 von164 + / EXIT Übung EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA BUFFER CACHE 21. Testen Sie selbständig, ob das auch für das Löschen gilt. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER delete bigtab where OWNER=’Hans’; RESSOURCENPLAN ROLLBACK SEGMENT commit; STATSPACK STORED OUTLINES select owner, count(*) from bigtab group by owner; TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE OWNER ----------------------------CTXSYS MDSYS ... COUNT(*) -------------2048 5376 INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT Abgelaufen: 00:00:00.00 Auch beim Löschen wird die Materialized View aktualisiert. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 53 von164 + STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA 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 set select from /oracle/ora92/rdbms/admin/utlxplan.sql autotrace on explain; owner, count(*) bigtab group by owner having count(*) > 1000; BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES OWNER -----------------------------CTXSYS MDSYS ODM OLAPSYS ORDSYS PUBLIC SYS WKSYS WMSYS XDB COUNT(*) --------------2048 5376 7904 4256 28896 369280 322528 3776 1824 5152 TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK 10 Zeilen ausgewählt. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 54 von164 STORED OUTLINES + / EXIT Übung EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA BUFFER CACHE Abgelaufen: 00:00:00.00 INDIZES INDEX-CLUSTER Ausführungsplan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=120) 10 TABLE ACCESS (FULL) OF ’MV_BIGTAB’ (Cost=2 Card=4 Bytes=120) MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Es wird die Materialized View verwendet, obwohl das Matching nicht exakt ist. Bei HAVING bringen Materialized Views Vorteile. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER 23. Löschen Sie die Materialized View wieder. RESSOURCENPLAN ROLLBACK SEGMENT drop materialized view name; STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 55 von164 + / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE Übung INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT – Einfache Optimierung – STATSPACK STORED OUTLINES Zeit: ca. 2 Stunden TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 56 von164 + / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 1. Schalten Sie AUTOTRACE ein: INDIZES INDEX-CLUSTER MATERIALIZED VIEWS SET AUTOTRACE ON EXPLAIN; OPTIMIZER RESSOURCENPLAN 2. Erstellen Sie eine Tabelle ORDER_DETAILS ROLLBACK SEGMENT STATSPACK STORED OUTLINES CREATE TABLE order_details ( orderid int NOT NULL, productid int NOT NULL, unitprice number(20,2) NOT NULL, quantity int NOT NULL, discount number(20,6) NOT NULL ); TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 57 von164 + / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 3. Fügen Sie der Tabelle ORDER_DETAILS eine Spalte namens PAKETGROESSE hinzu. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER ALTER TABLE order_details ADD paketgroesse VARCHAR2(10); RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES 4. Geben Sie für 1/3 der Datensätze den Wert „klein“ in die Spalte PAKETGROESSE. Denken Sie sich hierfür eine geeignete Vorgehensweise aus. TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES UPDATE order_details SET paketgroesse = ‘klein’ WHERE rowid IN (SELECT rowid FROM order_details GROUP BY rowid, rownum HAVING mod (rownum,3)=1); MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 58 von164 + / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 5. Geben Sie für 1/3 der Datensätze den Wert „mittel“ in die Spalte PAKETGROESSE. Denken Sie sich hierfür eine geeignete Vorgehensweise aus. UPDATE order_details SET paketgroesse = ‘mittel’ WHERE rowid IN (SELECT rowid FROM order_details GROUP BY rowid, rownum HAVING mod (rownum,3)=2); 6. Geben Sie für 1/3 der Datensätze den Wert „gross“ in die Spalte PAKETGROESSE. Denken Sie sich hierfür eine geeignete Vorgehensweise aus. UPDATE order_details SET paketgroesse = ‘gross’ WHERE rowid IN (SELECT rowid FROM order_details GROUP BY rowid, rownum HAVING mod (rownum,3)=3); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 59 von164 + INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 7. Ermitteln Sie die Datensätze aus ORDER_DETAILS, deren Paketgröße klein ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN SELECT * FROM order_details WHERE paketgroesse=’klein’; ROLLBACK SEGMENT STATSPACK STORED OUTLINES 2159 Dateien. Es wird ein immer ein FTS durchgeführt, wenn es keinen Index gibt, sonst Index-Scan. TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES 8. Erstellen Sie einen Index basierend auf der Spalte Paketgröße. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT CREATE INDEX id_P ON order_details(paketgroesse); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 60 von164 STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 9. Ermitteln Sie die Datensätze aus ORDER_DETAILS, deren Paketgröße „mittel“ ist. Wird ein Index-Scan oder ein Full-TableScan (FTS) durchgeführt? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER SELECT * FROM order_details WHERE paketgroesse=’klein’; RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Es wird ein Index-Scan durchgeführt (Rule-Index). STORED OUTLINES TKPROF 10. Zwingen Sie den Optimierer, den 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? Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS SELECT /*+ALL ROWS*/ paketgroesse FROM order_details; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT Ohne WHERE-Bedingung Full-Scan und mit WHERE-Bedingung Index-Scan. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 61 von164 + STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 11. Fügen Sie einen Datensatz hinzu, bei dem die Paketgröße „riesig“ ist. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER INSERT INTO order_details VALUES (10249,14,18.6,9,0,'riesig'); RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES 12. Ermitteln Sie die Datensätze aus ORDER_DETAILS, deren Paketgröße „riesig“ ist. Wird ein Index-Scan oder ein Full-Table-Scan (FTS) durchgeführt? TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES SELECT * FROM order_details WHERE paketgroesse=’riesig’; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Index-Scan, weil ein Index existiert und Optimizer auf RULE eingestellt ist, da keine Statistik vorhanden. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 62 von164 + ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 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? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN SELECT /*+ALL ROWS*/ paketgroesse FROM order_details WHERE paketgroesse=‘riesig‘; ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Index-Scan, da keine Analyse. Testfragen OCP Exam 1Z0-033 14. Zwingen Sie den Optimierer, den rules based optimizer (RBO) bei der nun folgenden Abfrage zu nehmen. Ermitteln Sie die Datens¨atze aus order details, deren Paketgr¨oße ’riesig’ ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN SELECT /*+RULE*/ * FROM order_details WHERE paketgroesse=’riesig’; ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 63 von164 + / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 15. Erstellen Sie ein Histogramm der Tabelle ORDER_DETAILS. Wie lautet die Anweisung hierfür? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS ANALYZE TABLE order_details COMPUTE STATISTICS FOR COLUMNS paketgroesse SIZE 3; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK 16. Ermitteln Sie die Datensätze aus ORDER_DETAILS, deren Paketgröße ’riesig’ ist. Wird ein Index-Scan oder ein Full-TableScan durchgef¨uhrt? Welcher Optimierungsmodus wird eingesetzt? STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES SELECT * FROM order_details WHERE paketgroesse=’riesig’; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Es wird ein Index-Scan durchgeführt aufgrund des erstellten Histogramms. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 64 von164 + ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 17. Ermitteln Sie die Datensätze aus ORDER_DETAILS, deren Paketgröße „mittel“ ist. Wird ein Index-Scan oder ein Full-TableScan durchgeführt? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER SELECT * FROM order_details WHERE paketgroesse=‘mittel‘; RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Full-Table-Scan erwarten, sonst bei Index-Scan Daten vervielfachen. TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE 18. Erstellen Sie die Tabellen „products“ und „categories“ aus dem Skript. Wie lautet die Anweisung hierfür? INDIZES MATERIALIZED VIEWS OPTIMIZER start ~/1ZO-033/Northwind-Skript/products.txt start ~/1ZO-033/Northwind-Skript/categories.txt RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 65 von164 + / EXIT Übung EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA BUFFER CACHE 19. 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; INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK RESERT-MERGE-Join, weil keine Spalte indiziert ist. STORED OUTLINES TKPROF 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? Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES CREATE INDEX id2 ON products(categoryid); SELECT p.productname,c.categoryname FROM products p, categories c WHERE p.categoryid=c.categoryid; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK NESTED-LOOP-JOIN, da Index erstellt worden ist. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 66 von164 STORED OUTLINES + / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Übung INDEX-CLUSTER MATERIALIZED VIEWS RESSOURCENPLAN OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: ca. 2 Stunden STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 67 von164 + / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA BUFFER CACHE TEIL 1 INDIZES INDEX-CLUSTER Erstellen Sie folgenden Ressource-Manager-Plan per Anweisung. Aktivieren Sie ihn anschließend. Speichern Sie die Anweisung im Ordner auf dem Trainerrechner ab. Führen Sie hierfür alle notwendigen Schritte selbständig durch. Erstellen Sie auch folgende Benutzer und ordnen Sie diese den entsprechenden Gruppen zu Schalten Sie AUTOTRACE ein: MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 OLTP_Group: BATCH_Group: ADHOC_Group: OLTP_1 BATCH_1 ADHOC_1 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER create user OLTP_1 identified by OLTP_1; create user BATCH_1 identified by BATCH_1; create user ADHOC_1 identified by ADHOC_1; RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 68 von164 + / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA BUFFER CACHE exec dbms_resource_manager.clear_pending_area(); INDIZES INDEX-CLUSTER MATERIALIZED VIEWS exec dbms_resource_manager.create_pending_area() ; exec dbms_resource_manager.create_plan(plan => 'NIGHT_PLAN',comment=>'Mein erster Plan'); OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE exec dbms_resource_manager.create_consumer_group (consumer_group => 'OLTP_Group',comment=>'Meine erste Gruppe'); INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 69 von164 + / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA BUFFER CACHE exec dbms_resource_manager.create_consumer_group (consumer_group => 'BATCH_Group',comment=>'Meine zweite Gruppe'); INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK exec dbms_resource_manager.create_consumer_group (consumer_group => 'ADHOC_Group',comment=>'Meine dritte Gruppe'); STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 70 von164 + / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning 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=>''); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 71 von164 + AGENDA BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning exec dbms_resource_manager.create_plan_directive ( plan => 'NIGHT_PLAN', group_or_subplan => 'OTHER_GROUPS', cpu_p3 => 100, parallel_degree_limit_p1=>0, comment=>''); exec dbms_resource_manager.submit_pending_area() ; exec dbms_resource_manager.create_pending_area() ; exec dbms_resource_manager_privs.grant_switch_co nsumer_group(grantee_name=>’OLTP_1’, consumer_group=>’OLTP_Group’, grant_option=>FALSE); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 72 von164 + AGENDA BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA exec dbms_resource_manager_privs.grant_switch_co nsumer_group(grantee_name=>’BATCH_1’, consumer_group=>’BATCH_Group’, grant_option=>FALSE); exec dbms_resource_manager_privs.grant_switch_co nsumer_group(grantee_name=>’ADHOC_1’, consumer_group=>’ADHOC_Group’, grant_option=>FALSE); exec dbms_ressource_manager.set_initial_consumer _group(user=>’OLTP_1’, consumer_group=>’OLTP_Group’); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 73 von164 + BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA BUFFER CACHE exec dbms_ressource_manager.set_initial_consumer _group(user=>’BATCH_1’, consumer_group=>’BATCH_Group’); INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT exec dbms_ressource_manager.set_initial_consumer _group(user=>’ADHOC_1’, consumer_group=>’ADHOC_Group’); STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES alter system set resource_manager_plan=’NIGHT_PLAN’; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 74 von164 + / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA Example_2_ Plan Resource Consumer Group Level 1 CPU Resource Allocation Level 2 CPU Resource Allocation Level 3 CPU Resource Allocation Parallel Degree Limit BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OLTP_ GROUP 20% 0% 0% 0 OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT Night_Plan STATSPACK BATCH_ GROUP 0% 80% 0% 10 STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 ADHOC_ GROUP 0% 20% 0% 5 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER OTHER_ GROUPS 0% 0% 100% 0 RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 75 von164 + / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA BUFFER CACHE TEIL 2 INDIZES INDEX-CLUSTER 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(); exec dbms_resource_manager.update_plan_directive( plan => 'NIGHT_PLAN', group_or_subplan => 'OTHER_GROUPS', new_max_est_exec_time => 5); exec dbms_resource_manager.submit_pending_area(); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 76 von164 + MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA BUFFER CACHE TEIL 3 INDIZES Verändern Sie den Plan so, dass für alle ADHOC_USER 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(); 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); exec dbms_resource_manager.submit_pending_area(); RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 77 von164 + INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Übung INDEX-CLUSTER MATERIALIZED VIEWS ROLLBACK SEGMENTE OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: ca. ½ Stunde STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 78 von164 + / EXIT Übung EINFÜHRUNG ROLLBACK SEGMENTE Übungen Performance Tuning AGENDA 1. Stellen Sie das Undo-Management auf Manuell um. Beschreiben Sie kurz die notwendigen Schritte: SELECT * FROM v$tablespace; ALTER SYSTEM SET undo_management=manual scope=spfile; SHUTDOWN IMMEDIATE; STARTUP; BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES 2. Erstellen Sie ein neues Undo-Tablespace namens undots02. Alle weiteren Parameter können Sie frei wählen. Schalten Sie diesen Online. Wie lautet die entsprechende Anweisung? CREATE UNDO TABLESPACE undots02 DATAFILE ‘c:\oracle\oradata\testdb19\undtots02.dbf’ SIZE 100 m AUTOEXTEND ON; TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 79 von164 + / EXIT Übung EINFÜHRUNG ROLLBACK SEGMENTE Übungen Performance Tuning AGENDA 3. Erstellen Sie in diesem Tablespace 5 Rollbacksegmente (rbs01 bis rbs05) und schalten Sie diese Online. Wie lauten die Anweisungen hierfür? 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 rbs01 ONLINE; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 80 von164 + BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG ROLLBACK SEGMENTE Übungen Performance Tuning 4. Ändern Sie den Preis aller Produkte der Tabelle Products (falls diese noch nicht da ist, dann müssen Sie diese Tabelle natürlich erst per Skript erstellen) um 10% nach oben. Die UndoInformationen sollten im Rollback-Segment rbs01 erstellt werden. Wie lauten die Anweisungen hierfür? AGENDA BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT SET TRANSACTION USE ROLLBACK SEGMENT rbs01; START ‘c:\nordwind-skript\products.txt’ STATSPACK STORED OUTLINES _____________ CREATE TABLE productid productname supplierid categoryid quantityperunit unitprice unitsinstock unitsonorder reorderlevel discontinued products INT VARCHAR2 INT INT VARCHAR2 NUMBER INT INT INT INT TKPROF ( (40) (20) (20,2) Testfragen NOT NULL , NOT NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NOT NULL ); OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT INSERT INTO products VALUES (1,'Chai',1,1,'10 boxes x 20 bags',18,39,0,10,0); INSERT INTO products VALUES (2,'Chang',1,1,'24 - 12 oz bottles',19,17,40,25,0); ... RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 81 von164 + STATSPACK STORED OUTLINES / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Übung INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER STATSPACK RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: ca. 2 Stunden STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 82 von164 + / EXIT Übung EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA BUFFER CACHE 1. Löschen Sie alle großen Tabellen a) im Oracle Enterprise Manager unter dem Schema von SYS b) mit dem Befehl INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT DROP TABLE order_details; DROP TABLE suppliers; DROP TABLE products; STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE 2. Erstellen Sie die Datenbank „northwind“ vom Skript INDIZES MATERIALIZED VIEWS START ‘c:\nordwind-skript\products.txt’ OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT Ändern Sie die Laufwerkspfade auch in den Dateien !!! STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 83 von164 + / EXIT Übung EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA BUFFER CACHE 3. Führen Sie alle notwendigen Schritte aus, um StatsPack zu installieren. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER START c:/oracle/ora92/rdbms/spcreate.txt RESSOURCENPLAN ROLLBACK SEGMENT Folgendes in der Datei umändern auf: -- Build the tables and synonyms STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 CONNECT perfstat/perf@testdb19 BUFFER CACHE INDIZES Password ist perf !!! MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Abfrage1: users eingeben Abfrage2: temp eingeben ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 84 von164 + / EXIT Übung EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA BUFFER CACHE 4. Erstellen Sie folgende Abfragen, speichern Sie diese, um diese Abfragen später ausführen zu können. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER Wie viel Kunden haben Getränke bestellt ? RESSOURCENPLAN ROLLBACK SEGMENT SELECT DISTINCT FROM WHERE AND AND AND AND companyname customers cus, orders ord, order_details ode, products pro, categories cat cus.customerid = ode.orderid = pro.productid = cat.categoryid = cat.Categoryname = STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE ord.customerid ord.orderid ode.productid pro.categoryid 'Beverages'; INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 85 von164 + / EXIT Übung EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA BUFFER CACHE Wie heißt der älteste Mitarbeiter ? SELECT FROM WHERE IN INDIZES INDEX-CLUSTER lastname,firstname,birthdate employees birthdate ( SELECT min(birthdate) FROM employees ); MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE Welche Produkte liefern deutsche Lieferanten ? INDIZES MATERIALIZED VIEWS SELECT FROM WHERE AND distinct pro.productname suppliers sup, products pro sup.supplierid = pro.supplierid country = 'Germany‘; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 86 von164 + OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA BUFFER CACHE 5. Erstellen Sie den ersten Snapshot ! INDIZES INDEX-CLUSTER MATERIALIZED VIEWS sqlplus perfstat/perf@testdb19 execute statspack.snap; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES 6. TKPROF Führen Sie die Abfragen aus. Testfragen OCP Exam 1Z0-033 BUFFER CACHE 7. INDIZES Erstellen Sie den zweiten Snapshot ! MATERIALIZED VIEWS OPTIMIZER sqlplus perfstat/perf@testdb19 execute statspack.snap; RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 87 von164 + / EXIT Übung EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA 8. Erstellen Sie einen Bericht und werten Sie diesen aus ! BUFFER CACHE INDIZES SELECT snap_id, snap_time FROM stats$snapshot; INDEX-CLUSTER MATERIALIZED VIEWS SNAP_ID SNAP_TIM---------- -------- 1 17.09.04 2 17.09.04 3 17.09.04 START c:/oracle/ora92/rdbms/admin/spreport.sql ... DB Id DB Name Inst Num Instance ----------------------------------------------------------------------------------------------------1509350452 TESTDB19 1 testdb19 Instances in this Statspack schema ----------------------------------------------------------------------------------------------------DB Id Inst Num DB Name Instance Host ----------------------------------------------------------------------------------------------------1509350452 1 TESTDB19 testdb19 DAMAGO_04 Using 1509350452 for database Id Using 1 for instance number Completed Snapshots Snap Snap Instance DB Name Id Snap Started Level Comment ----------------------------------------------------------------------------------------------------testdb19 TESTDB19 1 17 Sep 2004 10:13 5 2 17 Sep 2004 10:30 5 3 17 Sep 2004 10:31 5 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 88 von164 + OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Übung INDEX-CLUSTER MATERIALIZED VIEWS STORED OUTLINES OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: ca. 2 Stunden STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 89 von164 + / EXIT Übung EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA BUFFER CACHE 1. Schalten Sie AUTOTRACE ein: INDIZES INDEX-CLUSTER MATERIALIZED VIEWS SET AUTOTRACE ON EXPLAIN; OPTIMIZER RESSOURCENPLAN 2. Erstellen Sie eine Tabelle ORDER_DETAILS ROLLBACK SEGMENT STATSPACK STORED OUTLINES CREATE TABLE order_details ( orderid int NOT NULL, productid int NOT NULL, unitprice number(20,2) NOT NULL, quantity int NOT NULL, discount number(20,6) NOT NULL ); TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 90 von164 + / EXIT Übung EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA BUFFER CACHE 3. Fügen Sie der Tabelle ORDER_DETAILS eine Spalte namens PAKETGROESSE hinzu. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER ALTER TABLE order_details ADD paketgroesse VARCHAR2(10); RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF 4. Geben Sie für 1/3 der Datensätze den Wert „klein“ in die Spalte PAKETGROESSE. Denken Sie sich hierfür eine geeignete Vorgehensweise aus. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS UPDATE order_details SET paketgroesse = ‘klein’ WHERE unitprice BETWEEN 0 AND 15; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 91 von164 + / EXIT Übung EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA BUFFER CACHE 5. Geben Sie für 1/3 der Datensätze den Wert „mittel“ in die Spalte PAKETGROESSE. Denken Sie sich hierfür eine geeignete Vorgehensweise aus. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN UPDATE order_details SET paketgroesse = ‘mittel’ WHERE unitprice BETWEEN 15 AND 25; ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen 6. Geben Sie für 1/3 der Datensätze den Wert „gross“ in die Spalte PAKETGROESSE. Denken Sie sich hierfür eine geeignete Vorgehensweise aus. OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER UPDATE order_details SET paketgroesse = ‘gross’ WHERE unitprice BETWEEN 25 AND 60; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 92 von164 + RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA BUFFER CACHE 7. Erstellen Sie einen Index basierend auf der Spalte PAKETGROESSE. INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER CREATE INDEX id1 ON order_details(paketgroesse); RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK 8. Ermitteln Sie die Datensätze aus ORDER_DETAILS, deren Paketgröße „klein“ ist. Wird ein Index-Scan oder ein Full-Table-Scan (FTS) durchgeführt? STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES SELECT * FROM order_details WHERE paketgroesse=’klein’; MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Es wird ein immer ein FTS durchgeführt , wenn es keinen Index gibt, sonst Index-Scan. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 93 von164 + ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA BUFFER CACHE 9. Erstellen Sie eine STORED OUTLINE (im Deutschen heißt es Gliederung) für die eben erstellte Abfrage INDIZES INDEX-CLUSTER MATERIALIZED VIEWS CREATE OR REPLACE OUTLINE o1 FOR CATEGORY cat1 ON SELECT * FROM order_details WHERE paketgroesse=’klein’; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 10. Aktivieren Sie diese STORED OUTLINE. Wie lautet die Anweisung hierfür? BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER ALTER SESSION SET used_stored_outlines=cat1; RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 94 von164 + / EXIT Übung EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA BUFFER CACHE 11. Generieren Sie eine Statistik für die Tabelle ORDER_DETAILS. Wie lautet die Anweisung hierfür? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER ANALYZE TABLE order_details COMPUTE STATISTICS; RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES 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? TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS SELECT * FROM order_details WHERE paketgroesse=’klein’; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT Es wird ein Index-Scan durchgeführt. STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 95 von164 + / EXIT Übung EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA BUFFER CACHE 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? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN SELECT * FROM order_details WHERE paketgroesse=’mittel’; ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Es wird ein Index-Scan durchgeführt. Testfragen OCP Exam 1Z0-033 BUFFER CACHE 14. Deaktivieren Sie diese STORED OUTLINE. Wie lautet die Anweisung hierfür? INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ALTER SESSION SET used_stored_outlines=false; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 96 von164 ROLLBACK SEGMENT STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA BUFFER CACHE 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’; INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Es wird ein Full-Table-Scan durchgeführt, STORED OUTLINES zwingen Oracle zum Index-Scan! STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 16. Aktivieren Sie diese STORED OUTLINE erneut und ermitteln Sie die Datensätze, deren Paketgröße „klein“ ist. Wird ein Index-Scan oder ein Full-TableScan durchgeführt? ALTER SESSION SET used_stored_outlines=cat1; SELECT * FROM ... RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 97 von164 + BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE Übung INDIZES INDEX-CLUSTER MATERIALIZED VIEWS TKPROF OPTIMIZER RESSOURCENPLAN – Tools für die Leistungsüberwachung – ROLLBACK SEGMENT STATSPACK STORED OUTLINES Zeit: ca. 1 Stunde TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 98 von164 + / EXIT Übung Tools für die Leistungsüberwachung EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE 1. Melden Sie sich als SYS an der Datenbank an. INDIZES INDEX-CLUSTER 2. Erstellen Sie eine Tabelle EMPLOYEES aus dem Skript „employees.txt“. Wie viele Datensätze hat die Tabelle EMPLOYEES ? MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK START c:\oracle9i\employees.txt STORED OUTLINES TKPROF SELECT COUNT(*) FROM EMPLOYEES; Testfragen OCP Exam 1Z0-033 9 Datensätze befinden sich in der Tabelle. BUFFER CACHE INDIZES 3. Führen Sie folgende Anweisung aus, um die Ausgabe des Ausführungsplanes für die jeweiligen Anweisunggen durchzusetzen: OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK SET AUTOTRACE ON EXPLAIN; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 99 von164 MATERIALIZED VIEWS STORED OUTLINES + / EXIT Übung Tools für die Leistungsüberwachung EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE 4. Was passiert und was ist die Ursache ? INDIZES INDEX-CLUSTER SP2-0613: Format oder Existenz von PLAN_TABLE kann nicht überprüft werden SP2-0611: Fehler beim Initialisieren von EXPLAIN - Bericht MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES 5. 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. TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER START /oracle/ora92/rdbms/admin/utlxplan.sql RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK SET AUTOTRACE ON EXPLAIN; RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 100 von164 STORED OUTLINES + / EXIT Übung Tools für die Leistungsüberwachung EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE 6. Was passiert ? Wo lag die Datei UTLXPLAN.SQL ? INDIZES INDEX-CLUSTER C:\oracle\ora92\rdbms\admin\utlxplan.sql MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN 7. 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? ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 SELECT * FROM employees; BUFFER CACHE INDIZES Ausführungsplan --------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF ’EMPLOYEES’ MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 101 von164 + / EXIT Übung Tools für die Leistungsüberwachung EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE 8. Nun wollen wir uns auch jeweils die Zeit anzeigen lassen, die eine Abfrage benötigte. Führen Sie hierfür folgende Anweisung aus: INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN SET TIMING ON; ROLLBACK SEGMENT 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 ? STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 SELECT * FROM employees; BUFFER CACHE INDIZES Abgelaufen: 00:00:00.01 Ausführungsplan --------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF ’EMPLOYEES’ RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 102 von164 + MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung Tools für die Leistungsüberwachung EINFÜHRUNG Übungen Performance Tuning AGENDA 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? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN CREATE TABLE empl_cop AS SELECT * FROM employees; 11. BUFFER CACHE ROLLBACK SEGMENT STATSPACK Verdoppeln Sie die Anzahl der Datensäze in dieser Tabelle EMPL_COP insgesamt 16 mal. Wie viel Datensätze haben Sie nunmehr? INSERT INTO empl_cop SELECT * FROM empl_cop; COMMIT; SELECT COUNT(*) FROM empl_cop; STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK 589824 Datensätze befinden sich nun in der Tabelle. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 103 von164 + STORED OUTLINES / EXIT Übung Tools für die Leistungsüberwachung EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE 12. WICHTIG: Alles weitere bezieht sich jeweils immer auf die EMPL_COP! Schreiben Sie eine Abfrage, welche alle Employees und daneben die Anzahl der Datensätze der Employees ausgibt. Wie lautet die Abfrage und wie lange dauert diese? Überlegen Sie, warum diese Abfrage so aufwendig ist. Wir werden später eine ähnliche Abfrage so optimieren, dass die Ausführung unter 1/10 Sekunde dauert. INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Beispielergebnismenge STORED OUTLINES LASTNAME COUNT(*) -------------------- ---------Buchanan 65536 Callahan 65536 Davolio 65536 Dodsworth 65536 Fuller 65536 King 65536 Leverling 65536 Peacock 65536 Suyama 65536 RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 104 von164 INDIZES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES + / EXIT Übung Tools für die Leistungsüberwachung EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE SELECT lastname, count(*) FROM empl_cop GROUP BY lastname; INDIZES INDEX-CLUSTER MATERIALIZED VIEWS 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’ OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 13. Löschen Sie die Tabelle EMPLOYEES und auch die Tabelle EMPL_COP wieder. BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER DROP TABLE employees; DROP TABLE empl_cop; COMMIT; RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 105 von164 + / EXIT Übung EINFÜHRUNG Tkprof Übungen Performance Tuning AGENDA BUFFER CACHE 1. Erstellen Sie die Tabelle CUSTOMERS aus dem Skript “customers.txt”. Wie viel Datensätze hat die Tabelle ? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS START ~/Northwind-Skript/customers.txt OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT SELECT COUNT(*) FROM customers; STATSPACK STORED OUTLINES In der Tabelle befinden sich 91 Datensätze. TKPROF Testfragen OCP Exam 1Z0-033 Abgelaufen: 00:00:00.00 BUFFER CACHE Ausführungsplan --------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF ’CUSTOMERS’ RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 106 von164 + INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Übung EINFÜHRUNG Tkprof Übungen Performance Tuning AGENDA 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: BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER ALTER SESSION SET sql_trace = TRUE; RESSOURCENPLAN ROLLBACK SEGMENT 3. Mit welchen Befehl hätten Sie diese Einstellung permanent machen können? STATSPACK STORED OUTLINES TKPROF Testfragen ALTER SYSTEM SET sql_trace=true scope=spfile; OCP Exam 1Z0-033 BUFFER CACHE INDIZES 4. Wo befindet sich der Ordner, in dem die Trace-Datei gespeichert wird? MATERIALIZED VIEWS C:\oracle\admin\testdb19\udump\ testdb19_ora_2052.trc ROLLBACK SEGMENT RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 107 von164 OPTIMIZER RESSOURCENPLAN STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG Tkprof Übungen Performance Tuning AGENDA BUFFER CACHE 5. Wie könnten Sie diesen Ort verändern, auf eine andere Platte zum Beispiel ? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS USER_DUMP_DEST OPTIMIZER Andere Platte entsprechend Inhalt von USER_DUMP_ DEST mounten. RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK 6. Schreiben Sie für folgende Fragen bitte die entsprechende Abfragen: 7. Ermitteln Sie die Kunden, die aus Berlin kommen ? STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 SELECT * FROM customers WHERE city = ’Berlin’; INDIZES 1 Zeile ausgewählt. Abgelaufen: 00:00:00.00 Ausführungsplan --------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF ’CUSTOMERS’ RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 108 von164 BUFFER CACHE MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG Tkprof Übungen Performance Tuning AGENDA BUFFER CACHE Ermitteln Sie die Kunden, die aus London kommen ? INDIZES SELECT * FROM customers WHERE city = ’London’; INDEX-CLUSTER MATERIALIZED VIEWS 6 Zeilen ausgewählt. Abgelaufen: 00:00:00.03 Ausführungsplan --------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF ’CUSTOMERS’ OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen Ermitteln Sie die Kunden, die aus Paris kommen ? OCP Exam 1Z0-033 SELECT * FROM customers WHERE city = ’Paris’; BUFFER CACHE INDIZES 2 Zeilen ausgewählt. Abgelaufen: 00:00:00.01 Ausführungsplan --------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF ’CUSTOMERS’ RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 109 von164 MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES + / EXIT Übung EINFÜHRUNG Tkprof Übungen Performance Tuning AGENDA 8. Nun sollten Sie das Tracing ausschalten. Führen Sie hierfür folgenden Befehl aus: BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS ALTER SESSION SET sql_trace = false; OPTIMIZER RESSOURCENPLAN 9. Schauen Sie nun nach, ob die Trace-Datei erstellt wurde. ROLLBACK SEGMENT STATSPACK STORED OUTLINES 10. Führen Sie tkprof an der MS-DOS-Eingabeaufforderung aus, um sich eine lesbare Datei erstellen zu lassen: TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE tkprof.exe c:\oracle\admin\testdb19\udump\ testdb19_ora_2052.trc c:\t1.txt INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Hinweis: Natürlich müssen Sie die Werte anpassen ! ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 110 von164 + / EXIT Übung 11. EINFÜHRUNG Tkprof Übungen Öffnen Sie nun die eben erstellte Textdatei und schauen Sie sich die Ergebnisse an: SELECT * FROM customers WHERE city = ‚Berlin’; => 1 SELECT * FROM customers WHERE city = ‚London’; => 6 SELECT * FROM customers WHERE city = ‚Paris’; => 2 Performance Tuning AGENDA BUFFER CACHE INDIZES INDEX-CLUSTER Tracedatei erstellt : ja c:> tkprof c:\oracle\admin\testdb19\udump\testdb19_ora_2052.trc c:\t1.txt MATERIALIZED VIEWS OPTIMIZER File t1.txt TKPROF: Release 9.2.0.1.0 - Production on Di Sep 14 10:48:18 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: c:\oracle\admin\testdb19\udump\testdb19_ora_2052.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** Trace file: c:\oracle\admin\testdb19\udump\testdb19_ora_2052.trc Trace file compatibility: 9.00.01 Sort options: default 1 session in tracefile. 0 user SQL statements in trace file. 0 internal SQL statements in trace file. 0 SQL statements in trace file. 0 unique SQL statements in trace file. 21 lines in trace file RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 111 von164 + RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Testfragen INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER BUFFER CACHE RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: 15 Minuten STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Kostenloser Download der prüfungsnahen Testfragen vom 2005-11-21 http://www.freebraindumps.com/braindump-43659.htm ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 112 von164 + / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 1. Which two statements are true of the buffer cache? (Choose two) BUFFER CACHE INDIZES INDEX-CLUSTER A. B. C. D. The buffer pools in Oracle9i can be resized dynamically. The blocks in the buffer cache are managed using one list. The blocks in the buffer cache are managed using two lists. In Oracle9i buffer caches can be dynamically added. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Explanation: Begining with Oracle9i, the dynamic SGA infrastructure will allow for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown the database. The buffer pools in Oracle9i can be resized dynamically without stopping instance with commands like ALTER SYSTEM SET DB_CACHE_SIZE=<size>. The blocks in the buffer cache are managed using two lists. STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS Incorrect Answers: B: The blocks in the buffer cache are managed using two lists, not one list. D: Buffer caches, other than DEFAULT, i.e. DB_KEEP_CACHE_SIZE or DB_RECYCLE_CACHE_SIZE cannot be dynamically added. They need to be present in the init.ora or spfile. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 113 von164 + / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 2. Which three statements about improving the performance of the database buffer cache by creating multiple buffer pools are true? BUFFER CACHE INDIZES INDEX-CLUSTER A. One, two, or three pools may be defined. B. There are at least 50 blocks per LRU latch for each pool. C. Each buffer pool is assigned latches taken from DB_BLOCK_LRU_LATCHES. D. The size if the DEFAULT pool is obtained by adding all the pools to the value of the DB_BLOCK_BUFFERS parameter. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Explanation: Unless you specify otherwise in the init.ora, only the Default Pool is created at instance startup. But Oracle provides you also with the ability to divide the Database Buffer Cache into as many as three separate areas called Buffer Pools: Keep Pool (optional), Recycle Pool (optional) and Default Pool (mandatory). There are at least 50 blocks per LRU latch for each pool. Also each buffer pool is assigned latches taken from DB_BLOCK_LRU_LATCHES. Incorrect Answer: D: Default Pool is used to cache segments that are not designated for either the Keep or Recycle pools. The size of this pool is designated in bytes, kilobytes, megabytes, or gigabytes, by the init.ora parameter DB_CACHE_SIZE. Unlike Oracle8i, where the memory for the Keep and Recycle pools was taken from the memory allocated to the Default Pool, Oracle9i independently assigns the memory to each of the three Buffer Pool types. TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 114 von164 + / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 3. Select the statement that most accurately describes the function of a latch. BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. A latch is responsible for protecting database tables from concurrent changes by multiple processes. B. A latch prevents two or more process from modifying the same data structure at the same time. C. The purpose of a latch is to prevent Oracle memory structures from growing too large for real memory. D. The purpose of a latch is to coordinate which background process will be used to read data blocks from data files. E. Latches are used to speed recovery following a database crash. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE Explanation: The function of a latch is to protect Oracle memory structures from being modified by more than one process at a time. A latch is not responsible for protecting database tables from concurrent changes by multiple processes. This statement more accurately describes a lock instead of a latch. To prevent Oracle memory structures from growing too large for real memory is not the responsibility of a latch, but it is the responsibility of the DBA. Other choices are simply not functions associated with latches. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 115 von164 + INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 4. Dynamic SGA allocation enables the DBA to increase the size of the SGA components up to? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN A. B. C. D. MAX_SGA_SIZE SGA_MAX_SIZE SHARED_POOL_SIZE+BUFFER_CACHE_SIZE DB_BLOCK_SIZE ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE Explanation: The SGA_MAX_SIZE parameter indicates the maximum size that you can dynamically increase the SGA components up to. The buffer cache, redo buffers, the Java pool, large pool, shared pool, and reserved pool all must fit inside the SGA_MAX_SIZE limit. INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 116 von164 + / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 5. Which type of table is the best candidate to be cached? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. B. C. D. Small table rarely retrieved with a full table scan. Large table rarely retrieved with a full table scan. Small table frequently retrieved with a full table scan. Large table frequently retrieved with a full table scan. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Explanation: Small tables frequently retrieved with a full table scan can be cached. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES Incorrect Answers: A: If table is rarely retrieved there is no sense to cache it. B: Large table cannot be easy cached because CACHE option works mostly for the small tables that can be loaded in the memory. Also if table is rarely retrieved there is no sense to cache it. D: Large table is not good candidate to be cached. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 117 von164 + MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 6. The default buffer cache can be expanded dynamically if needed, but only in granule-size increments of? (Choose two.) BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER A. B. C. D. E. 2Mb if the SGA is less than 128Mb 4Mb if the SGA is less than 128Mb 8Mb if the SGA is less than 128Mb 8Mb if the SGA is larger than 128Mb 16Mb if the SGA is larger than 128Mb RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 Explanation: The granule is defined as a unit of allocation in the dynamic SGA. When an SGA component (DB buffer cache, shared pool) is expanded or shrunk, it must be in granule increments. The minimum number of granules allocated is 1 for each of the SGA structures (buffer cache; shared pool, and fixed SGA). The maximum number of granules is determined by MAX_SGA_SIZE, which is measured in bytes BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 118 von164 + / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 7. Summing the system statistics, (db blocks gets + consistent gets) gives the total number of requests. What is the other system, statistic required to calculate the buffer cache hit ratio? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER A. B. C. D. Physical reads. Session logical gotten. Table scan blocks gotten. DBWR buffers scanned. RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Explanation: To calculate the buffer cache hit ratio you also need to know number of physical reads. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES Incorrect Answers: B: You don’t need to know session logical gotten to calculate this ratio. C: Table scan blocks gotten statistic is not required to calculate the buffer cache hit ratio. D: DBWR buffers scanned also unnecessary to calculate it. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 119 von164 + / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 8. Which V$ view which is used to monitor granule allocation and deallocation in the buffer cache? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. B. C. D. V$CACHE V$SESSTAT V$BUFFER_POOL V$LIBRARYCACHE OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Explanation: This view displays information about all buffer pools available for the instance, including granule allocation and de-allocation in the buffer cache. Incorrect Answers: A: This is an Oracle9i Real Application Clusters view. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects. B: This view lists user session statistics. D: This view contains statistics about library cache performance and activity. TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 120 von164 + / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 9. Which dynamic view is most useful for determining the current number of blocks allocated to a buffer pool? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. B. C. D. V$CACHE V$SESS_IO V$SYSSTAT V$BUFFER_POOL OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Explanation: The V$BYUFFER_POOL dynamic performance view contains information about the configuration of the multiple Buffer Pools themselves. You can monitor the performance of the Buffer Pools using the V$BUFFER_POOL and V$BUFFER_POOL_STATISTICS dynamic performance views. STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE Incorrect Answers: A: V$CACHE dynamic view is an Oracle9i Real Application Clusters view. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects. B: This view lists I/O statistics for each user session. C: Sort activity can be monitored using the V$SYSSTAT and V$SORT_SEGMENT dynamic data dictionary views, using the output from STATPACK and REPORT.TXT, and using the output from the OEM Performance Manager. INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 121 von164 + / EXIT Testfragen EINFÜHRUNG Buffer Cache Übungen Performance Tuning AGENDA 10. What is the least number of buffers an LRU latch must cover in the database buffer cache? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. B. C. D. E. 5 10 30 50 100 OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen Explanation: There are at least 50 blocks per LRU latch for each pool. OCP Exam 1Z0-033 BUFFER CACHE INDIZES Incorrect Answers: A: There are at least 50, not 5, blocks per LRU latch for each pool. B: There are at least 50, not 10, blocks per LRU latch for each pool. C: There are at least 50, not 30, blocks per LRU latch for each pool. E: There are at least 50, not 100, blocks per LRU latch for each pool. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 122 von164 + / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Testfragen INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER Indizes RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: 6 Minuten STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Kostenloser Download der prüfungsnahen Testfragen vom 2005-11-21 http://www.freebraindumps.com/braindump-43659.htm ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 123 von164 + / EXIT Testfragen EINFÜHRUNG Indizes Übungen Performance Tuning AGENDA BUFFER CACHE 1. Which type of index is best suited for low-cardinality data; that is, few distinct column values in a table with many rows? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER A. Compressed key B. Function-based C. Bitmap D. B-Tree E. Reverse key RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen Explanation: Bitmap indexes are designed for low-cardinality data, such as gender in an employee table. B-tree indexes are designed for high-cardinality data; that is, many distinct keys relative to the number of rows. A compressed B-tree is a compromise between the extremes, but not the correct answer. Reverse key indexes, function-based indexes, and IOT's each have a niche, but none are designed for low-cardinality data. OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 124 von164 + / EXIT Testfragen EINFÜHRUNG Indizes Übungen Performance Tuning AGENDA BUFFER CACHE 2. When an IOT is created, the DBA can specify the cutoff point in a row, determining which columns will be stored in the IOT leaf block and which will be stored in the overflow segment, by specifying which of the following parameters? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN A. PCTTHRESHOLD B. OVERFLOW C. INCLUDING D. ORGANIZATION INDEX ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen Explanation: The INCLUDING keyword specifies which columns to keep in the IOT leaf blocks. Those columns after the column specified in the INCLUDING clause will be written to the overflow segment. The PCTTHRESHOLD value indicates how much of the leaf block to reserve for row data; the OVERFLOW clause specifies the storage parameters for the columns that are not kept in the leaf blocks. The ORGANIZATION INDEX clause is used to specify that the table is an IOT. OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 125 von164 + / EXIT Testfragen EINFÜHRUNG Indizes Übungen Performance Tuning AGENDA BUFFER CACHE 3. The DBA can build bitmap indexes on an index organized table IOT by creating which of the following? INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER A. A mapping table B. A bitmap IOT C. An IOT cluster D. A bitmap cluster RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Explanation: The mapping table is used to relate logical row ROWIDs required by the IOT to physical row ROWIDs required by the bitmap. One mapping table is used for an IOT even if there are several bitmap indexes are on the structure. Each of the other answers are incorrect because they don't exist. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 126 von164 + / EXIT Testfragen EINFÜHRUNG Indizes Übungen Performance Tuning AGENDA BUFFER CACHE 4. This DDL is an example of which type of index? INDIZES INDEX-CLUSTER CREATE INDEX MY_INDEX ON EMPLOYEE (UPPER(LAST_NAME)); MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN A. Reverse key B. Function-based C. Bitmap D. Cluster E. IOT ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 Explanation: The DDL clearly shows that the UPPER function is used on the employee's last name column. Reverse key, Cluster, IOT, and Bitmap are incorrect because the DDL will not create any one of these types of indexes. BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 127 von164 + / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Testfragen INDEX-CLUSTER MATERIALIZED VIEWS MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: 6 Minuten STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Kostenloser Download der prüfungsnahen Testfragen vom 2005-11-21 http://www.freebraindumps.com/braindump-43659.htm ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 128 von164 + / EXIT Testfragen EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA 1. Which four statements are true regarding materialized views? A. Materialized views cannot be partitioned, nor can they be defined on partitioned tables. B. Materialized views are often used in data warehouses to increase the speed of queries on very large datatables. C. Queries that benefit from the use of materialized views often involve joins between tables or aggregations such as SUM. D. A materialized view stores both the definition of a view and the rows resulting from the execution of the views. E. Materialized views can be used to replicate data, which was formerly achieved using the CREATE SNAPSHOT statement. BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Explanation: Stored outlines help speed up queries by telling the optimizer how to tackle the query execution associated with a particular SQL statement. Materialized views are also designed to speed up queries by storing data from queries in a prejoined, pre-summarized format. Unlike a traditional view, which is merely stored in the data dictionary as a SELECT statement that is executed when the view is accessed, a materialized view stores the physical results of the view in its own segment, separate and distinct from the underlying table on which the view is based. Materialized views are intended primarily for use in data warehouses and Decision Support Systems where large volumes of data are accessed and summarized using queries. Incorrect Answer: A: Materialized view segment can be stored in its own tablespace and can be indexed and partitioned. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 129 von164 + Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA 2. A. B. C. D. E. Which two statements correctly describe the use of the REFRESH option when creating materialized views? (Choose two) BUFFER CACHE Use the REFRESH COMPLETE option to truncate the materialized view and repopulate the view with data from the base tables in the query. Use the REFRESH FAST option to populate the materialized view data from the base tables that has changed since the last resync. Use the REFRESH FAST option to truncate the materialized view and populate the view with data from the base tables in the query. Use the REFRESH FAST ON DEMAND option to repopulate the materialized view with data from base tables after each commit to any of the base tables. Use the REFRESH COMPLETE option to update the existing data in the view with all the new changes from the base tables since the last re-sync, without truncating the existing materialized view. INDEX-CLUSTER Explanation: Materialized views are designed to speed up queries by storing data from queries in a prejoined, presummarized format. If you do want the data to be kept in sync, you will specify either the COMPLETE, FAST, or FORCE option during the creation of the view. If you use a COMPLETE option, during a refresh, the materialized view is truncated and then completely repopulated with data from the base tables in the query. Using the FAST option, the materialized view is populated only with data that has changed in the base table since the last re-sync. This refresh is performed using the view’s log data or by ROWID. Incorrect Answers: C: The REFRESH FAST option does not truncate the materialized view. D: ON DEMAND option defines that you can also manually refresh the contents of a materialized view. E: Using the REFRESH COMPLETE option the materialized view is truncated and then completely repopulated with data from the base RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 130 von164 + INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA 3. A. B. C. D. E. The optimizer rewrites a query so that the query can access a materialized view instead of the base tables. Although query rewrite activity is transparent to the applications, there are certain prerequisites that need to be satisfied for the optimizer to rewrite queries. Which statement correctly describe one of the prerequisites? OPTIMIZER_MODE must be set with cost-based optimization. QUERY_REWRITE_ENABLED must be specified in the parameter file. A user who owns the materialized view must also own PLAN_TABLE. A user must be granted QUERY REWRITE system privilege to enable materialized views in any schema. QUERY_REWRITE_ENABLED=TRUE must be included in the option clause when a materialized view is created. Explanation: To rewrite queries for better performance OPTIMIZER_MODE should be set with cost-based optimization. Unlike the rule-based optimizer that has only its predefined guidelines to follow when executing a query, the cost-based optimizer (CBO) considers many different execution plans and then selects the one with the lowest execution plan. The QUERY_REWRITE_ENABLED init.ora parameter allows optimizer to dynamically rewrite queries to take advantage of materialized views when set to TRUE. QUERY_REWRITE_INTEGRITY determines the degree to which the data consistency is to be adhered to when accessing materialized views. This will work only if OPTIMIZER_MODE is set to one of the costbased optimizer modes. Incorrect Answers: B: QUERY_REWRITE_ENABLED can be specified in the parameter file, but there is not requirement. C: There is no prerequisite that a user who owns the materialized view must also own PLAN_TABLE. D: It is not required that a user must be granted QUERY REWRITE system privilege to enable materialized views in any schema. E: QUERY_REWRITE_ENABLED=TRUE may be set only in the init.ora file to allow optimizer to dynamically rewrite queries to take advantage of materialized views. It cannot be used in the option when a materialized view is created. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 131 von164 + BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Materialized Views Übungen Performance Tuning AGENDA 4. What is the effect of setting the initialization parameter QUERY_REWRITE_INTEGRITY to STALE_TOLERATED? BUFFER CACHE INDIZES A. Oracle server allows query rewrites based on declared, but not enforced, relationships. B. Oracle server allows all updated materialized views and constraints with RELY flag to be used for the query rewrites. C. Query rewrites can occur even when the materialized view’s data has not been refreshed and is inconsistent with the underlying detail data in the base tables. Explanation: If you will set the initialization parameter QUERY_REWRITE_INTEGRITY to STALE_TOLERATED, query rewrites can occur even when the view’s data and the underlying table data are not current. Incorrect Answers: A: This state of QUERY_REWRITE_INTEGRITY does not mean that Oracle server allows query rewrites based on declared, but not enforced, relationships. This can be done if TRUSTED state set for the QUERY_REWRITE_INTEGRITY parameter: query rewrites can occur when declared relationships exist, but without complete data currency. B: Oracle server does not allow all updated materialized views and constraints with RELY flag to be used for the query rewrites if QUERY_REWRITE_INTEGRITY = STALE_TOLERATED. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 132 von164 + INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Testfragen INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: 3 Minuten STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Kostenloser Download der prüfungsnahen Testfragen vom 2005-11-21 http://www.freebraindumps.com/braindump-43659.htm ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 133 von164 + / EXIT Testfragen EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA 1. You are responsible for selecting the Oracle optimizer mode for a new database that will be used in conjunction with a new Accounts Receivable application. The new application comes pre-configured with table and indexes statistics. BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS Which three Oracle optimizer modes might be suitable for the new application? OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT A. B. C. D. RULE FIRST_ROWS_n ALL_ROWS FIRST_ROWS STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 Explanation: When RULE is specified, statistics are not used. The CBO is a statisticsdriven optimizer, and mode ALL_ROWS, FIRST_ROWS,FIRST_ROWS_n, CHOOSE are CBO settings. Incorrect Answer: A: The rule-based optimizer mode does not use table or index statistics at all. BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 134 von164 + / EXIT Testfragen EINFÜHRUNG Optimizer Übungen Performance Tuning AGENDA 2. The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which executation plan will be the result? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER A. B. C. D. The sort-merge join The nested loops join This depends on some sort parameter values. This depends on the number of rows in each table. RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Explanation: If the OPTIMIZER_MODE is set to FIRST_ROWS and all tables are analyzed the cost-based optimizer will choose a nested loops join operation. Incorrect Answers: A: If the OPTIMIZER_MODE is not set to FIRST_ROWS or tables are not analyzed the cost-based optimizer can choose a sort merge join operation. C: It does not depend on some sort parameter values. D: It does not depend on the number of rows in each table. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 135 von164 + / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Testfragen INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: 15 Minuten STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Kostenloser Download der prüfungsnahen Testfragen vom 2005-11-21 http://www.freebraindumps.com/braindump-43659.htm ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 136 von164 + / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 1. Database Resource Manager uses resource plans to determine resource limits for the set of users. Which statement is true in reference to resource plans? A. Resource plans are set using profiles. B. Only one resource plan can be stored in the database at one time. C. The database can have many resources plans, but only one can be active at any one time. D. The database can have many resources plans, and each user chooses which plan to belong to. BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Explanation: Oracle8i introduced a new feature, Resource Manager, which was designed to improve the allocation and management of server resources needed by application users. In Oracle 9i, it is possible to control numerous aspects of application processing via Resource Manager. The database can have many resources plans, but only one can be active at any one time. Incorrect Answers: A: Resource plans are not set using profiles. B: The database can have many resources plans. D: Each user cannot choose which plan to belong to because only one plan can be active at any one time. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 137 von164 + TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 2. A. B. C. D. E. F. At the database instance level, how many database resource plans can be active at the same time? BUFFER CACHE Only one All None Up to 2 Up to 4 Up to 8 INDEX-CLUSTER INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Explanation: Oracle8i introduced a new feature, Resource Manager, which was designed to improve the allocation and management of server resources needed by application users. In Oracle 9i, it is possible to control numerous aspects of application processing via Resource Manager. The database can have many resources plans, but only one can be active at any one time. Incorrect Answers: B: The database can have many resources plans, but only one can be active at any one time. C: Only one, not none, resource plan can be active at the same time. D: Only one, not up to 2, resource plan can be active at the same time. E: Only one, not up to 4, resource plan can be active at the same time. F: Only one, not up to 8, resource plan can be active at the same time. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 138 von164 + STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 3. You just created a resource plan and placed this line in the init.ora RESOURCE_MANAGER_PLAN = day_oltp BUFFER CACHE What does ‘day_oltp’ specify? INDEX-CLUSTER INDIZES MATERIALIZED VIEWS OPTIMIZER A. B. C. D. Resource plan. Plan directive. Consumer group. Resource manager privilege. RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Explanation: RESOURCE_MANAGER_PLAN initialization parameter specifies a resource plan. Incorrect Answers: B: It does not specify a plan directive. A plan directive is used to link a resource consumer group to a resource plan. C: RESOURCE_MANAGER_PLAN initialization parameter does not specify a consumer group. D: Resource manager privilege cannot be set with this initialization paramater. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 139 von164 + / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 4. Which procedure for the DBMS_RESOURCE_MANAGER package would first need to be performed when creating a new resource object? BUFFER CACHE INDIZES INDEX-CLUSTER A. B. C. D. CREATE_PLAN CREATE_PENDING_AREA CREATE_CONSUMER_GROUP CREATE_PLAN_DIRECTIVE MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Explanation: Whenever a new resource consumer group, plan, or directive is created =, it is temporarily stored in the pending area until it is validated and written to the database. The purpose of the pending area is to give the DBA an opportunity to confirm that the definition of each consumer group, plan, and directive is correct before implementing it. The pending area is created using the DBMS_RESOURCE_MANAGER package and the CREATE_PENDING_AREA procedure. STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS Incorrect Answers: A: CREATE_PLAN procedure should be run after you created resource consumer group. C: CREATE_CONSUMER_GROUP should be run after you created a pending area. D: Directives can be created after you created resource plan. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 140 von164 + OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 5. What can database resource manager help you to limit for a set of users? BUFFER CACHE INDIZES INDEX-CLUSTER A. B. C. D. Amount of I/O performed. Maximum connection time. Number of concurrent sessions. Number of Parallel Query servers available. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Explanation: Resources that can be controlled through the use of Resource Manager include the amount of CPU allocated to each user who is a member of the resource consumer group; the degree of parallelism allowed for Parallel Queries performed by a user who is a member of the resource consumer group; the amount of undo segment space that a user who is a member of a resource group is allowed to consume when performing a long running transaction; the total number of active, concurrent sessions that a given resource consumer group is allowed to have at one time; the maximum expected total time of a database action taken by a user who is a member of the resource consumer group. Incorrect Answers: A: It does not control an amount of I/O performed. B: Database Resources Manager does not work with maximum connection time. C: Number of concurrent sessions is not a resource controlled by Database Resources Manager. STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 141 von164 + / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 6. Which package would you use to grant resource groups to users? BUFFER CACHE INDIZES A. B. C. D. E. INDEX-CLUSTER DBMS_REPCAT_AUTH DBMS_SPACE_ADMIN DBMS_REPCAT_ADMIN DBMS_RESOURCE_MANAGER DBMS_RESOURCE_MANAGER_PRIVS MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Explanation: The DBMS_RESOURCE_MANAGER_PRIVS package maintains privileges associated with the Resource Manager. STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 Incorrect Answers: A: There is no DBMS_REPCAT_AUTH DBMS package in Oracle 9i. B: The DBMS_SPACE_ADMIN package provides functionality for locally managed tablespaces. C: DBMS_REPCAT_ADMIN enables you to create users with the privileges needed by the symmetric replication facility. D: The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives. It also provides semantics so that you may group together changes to the plan schema. BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 142 von164 + / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 7. Which three are used when administering systems with the database Resource Manager? (Choose three) BUFFER CACHE INDIZES INDEX-CLUSTER A. B. C. D. E. F. G. Histograms Explain Plan Resource Plan Resource User Group Resource Consumer Group Resource Plan Directives Resource Plan Assignments MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Explanation: Resource Plan, Resource Consumer Group and Resource Plan Directives are used when administering systems with the database Resource Manager. Incorrect Answers: A: Histograms are not used with the database Resource Manager. B: Explain Plan has no relation with the database Resource Manager. D: There is Resource Consumer Group, not Resource User Group in Resource Manager. G: There is no Resource Plan Assignments for Resource Manager. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 143 von164 + / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 8. In order to avoid validation errors, every resource plan directive must include which resource consumer plan? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. B. C. D. OTHER SYS_GROUP OTHER_GROUPS DEFAULT_GROUPS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Explanation: This description fits the OTHER_GROUPS consumer group. DEFAULT_CONSUMER_GROUP is the initial consumer group for all users/sessions that have not been explicitly assigned an initial consumer group. DEFAULT_CONSUMER_GROUP has switch privileges granted to PUBLIC; therefore, all users are automatically granted switch privilege for this consumer group. SYS_GROUP is the initial consumer group for the users SYS and SYSTEM. LOW_GROUP provides a group having lower priority than SYS_GROUP and OTHER_GROUPS in this plan. TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 144 von164 + / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 9. From the following, choose the correct sequence of events to configure Resource Manager: BUFFER CACHE INDIZES INDEX-CLUSTER A. Create a pending area, create a plan, create plan directives, and create consumer groups. B. Create a pending area, create consumer groups, create plan directives, and create a plan. C. Create a pending area, create a plan, create consumer groups, and create plan directives. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Explanation: This specifies the correct sequence of events. We did not include validate and submit steps, not did we assign users or roles to consumer groups. However, the steps to create a pending area, create a plan, create consumer groups, and create plan directives are in correct order. Other answers are both incorrect because the sequence of events is incorrect. TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 145 von164 + / EXIT Testfragen EINFÜHRUNG Ressourcenplan Übungen Performance Tuning AGENDA 10. Before a resource plan can become the active top-level plan, which of the following rules must be validated? (Choose all that apply.) BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. All referenced subplans and consumer groups must exist. B. The sum of percentage for resources that use the emphasis method cannot exceede 100 at any given priority level. C. The plan must have a directive for OTHER_GROUPS. D. All plans must contain at least one subplan. E. A plan has a maximum of 32 consumer groups and 32 subplans. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Explanation: Each of the rules specified must be met for a plan to be validated. All referenced subplans and consumer groups must exist. CPU percentage for resources in the emphasis method cannot exceed 100 percent at any level, and there must be a directive for OTHER_GROUPS in the plan. There is a maximum of 32 consumer groups allowed in a plan, and a maximum of 32 subplans in a plan. Also no plan is required to have a subplan. Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 146 von164 + / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Testfragen INDEX-CLUSTER MATERIALIZED VIEWS ROLLBACK SEGMENTE OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: 9 Minuten STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Kostenloser Download der prüfungsnahen Testfragen vom 2005-11-21 http://www.freebraindumps.com/braindump-43659.htm ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 147 von164 + / EXIT Testfragen EINFÜHRUNG Rollback Segmente Übungen Performance Tuning AGENDA 1. Which type of transaction should you assign to a specific large rollback segment? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. B. C. D. Batch jobs that modify many rows. Long running serializable transactions. Long running reports, to avoid ‘snapshot too old’ errors. Discrete transactions that modify many rows in the same block. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Explanation: You need to assign a batch jobs modifying many rows to specific large rollback segment using SET TRANSACTION command. TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES Incorrect Answers: B: Long running serializable transactions do not require large rollback segments. C: Reports do not modify any table records. So they do not need specific rollback segment. D: Discrete transactions that modify many rows in the same block will not cause rollback segment contention. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 148 von164 + MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Rollback Segmente Übungen Performance Tuning AGENDA 2. What should be two goals in tuning rollback segments? BUFFER CACHE INDIZES INDEX-CLUSTER A. Transactions should never wait for access to rollback segment. B. No transaction, however large or exceptional, should ever run out of rollback space. C. Rollback segments should be configured to extend continually during normal processing. D. The ratio of waits to the rollback segment header blocks should be less than 5% of the sum of access. MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen Explanation: There are two main goals for rollback segments: transactions should never wait for access to rollback segment; no transaction should ever run out of rollback space because it used to keep the read-consistent view of the changed data. OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER Incorrect Answers: C: Rollback should not extend continually during normal processing. It is possible only as exception to keep data for batch jobs performing DML operations with many rows. D: Transactions should never wait for access to rollback segment. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 149 von164 + RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Rollback Segmente Übungen Performance Tuning AGENDA 3. You are creating a new rollback segment. Why should you choose the same value for the NEXT and INITIAL attributes? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. B. C. D. That depends on the PCTINCREASE value you specify. To avoid contention on the rollback segment header. Rollback segment extents are used in a circular way. Because you get an error message if you specify different values. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES Explanation: Like any other database segment, rollback segments are made up of extents, which are in turn comprised of five or more contiguous Oracle blocks. Within each rollback segment, Oracle uses the extents in a circular fashion until the rollback segment is full. That’s why you should choose the same value for the NEXT and INITIAL attributes. Incorrect Answers: A: You cannot set the PCTINCREASE value when creating a new rollback segment: it’s always 0 for rollback segments. B: Rollback segment extents are used in a circular way. Setting the same value for the NEXT and INITIAL attributes has nothing to do with contention on the rollback segment header. D: You will get an error message, but this is not a reason, it’s only a consequence of circular usage of rollback segments. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 150 von164 + TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Rollback Segmente Übungen Performance Tuning AGENDA 4. You execute the following query: SELECT sum(waits) * 100 / sum(gets) “Ratio”, sum(waits) “Waits”, sum(gets) “Gets” FROM v$rollstat; If the number of waits for any class is greater than 1% of the total number of requests, what should you do? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT A. B. C. D. E. Create more rollback segments. Check for WAITS in v$rollstat and create more extents. Increase the number of extents in the rollback segments. Check for WRAPS in v$rollstat and create larger extents. Increase the size of the extents in the rollback segments. STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 Explanation: You should create more rollback segments to decrease rollback segments contention. BUFFER CACHE INDIZES MATERIALIZED VIEWS Incorrect Answers: B: You don’t need to create more extents. C: It’s not required to increase the number of extents in the rollback segments. D: It’s not possible to create larger extents: in rollback segments all extents are the same size. E: Because of the fixed size of extents in the rollback segments you cannot increase their size. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 151 von164 + OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Rollback Segmente Übungen Performance Tuning AGENDA 5. What may happen if you size your rollback segments too small? BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. B. C. D. The long transaction switches to SYSROL rollback. The long transaction fails for lack of rollback space. The long transaction hangs until the DBA alters MINEXTENTS. The long transaction is forces to switch to another rollback segment. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen Explanation: The long transaction can fail for lack of rollback space if you size your rollback segments too small. Incorrect Answers: A: Transaction cannot be switched to another rollback segment. C: MINEXTENTS parameter has nothing to do with rollback segments. The long transaction will just fail. D: Transaction cannot be switched to another rollback segment. OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 152 von164 + / EXIT Testfragen EINFÜHRUNG Rollback Segmente Übungen Performance Tuning AGENDA BUFFER CACHE 6. You’re operating an OLTP database in manual undo management mode and notice that the query INDIZES INDEX-CLUSTER MATERIALIZED VIEWS SELECT COUNT(*) FROM V$TRANSACTION; OPTIMIZER is always less than or equal to 160 during peak transaction hours. How many rollback segments should you configure? RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK A. B. C. D. E. F. G. 640 40 160 180 50 4 20 STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER Explanation: The general guideline is to configure the maximum number of concurrent transactions divided by 4. For this case, it’s 40, so we should configure 40 rollback segments. Based on the guideline and the query, each of the other answers is an incorrect value. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 153 von164 + RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Testfragen INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER STATSPACK RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: 7 ½ Minuten STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Kostenloser Download der prüfungsnahen Testfragen vom 2005-11-21 http://www.freebraindumps.com/braindump-43659.htm ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 154 von164 + / EXIT Testfragen EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA 1. A. B. C. D. You have been taking snapshots once a week over the last two years. You will use these snapshots to outline the cost associated with the increase in database resources over the past two years. Which tool or utility will allow you to compare the snapshots? INDIZES INDEX-CLUSTER STATSPACK LOCK MANAGER ORACLE EXPERT UTLBSTAT /UTLESTAT MATERIALIZED VIEWS OPTIMIZER Explanation: The term snapshot as used here denotes a set of performance statistics gathered at a single time, identified by a unique ID that includes the snapshot number (or SNAP_ID). This kind of snapshot has nothing to do with Oracle's snapshot replication technology.Statspack is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack differs from the existing UTLBSTAT/UTLESTAT performance scripts in the following ways: - Statspack collects more data, including high-resource SQL. - Statspack precalculates many ratios useful when performance tuning, such as cache hit ratios, rates, and transaction statistics. (Many of these ratios must be calculated manually when using BSTAT/ESTAT. - Permanent tables owned by PERFSTAT store performance statistics. Instead of creating/dropping tables each time, data is inserted into the preexisting tables. This makes it easier to compare historical data. - Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected. - Data collection is easy to automate using either DBMS_JOB or an operating system utility. - Statspack considers a transaction to finish either with a COMMIT or a ROLLBACK, and so calculates the number of transactions as 'user commits' + 'user rollbacks.' BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = 'user commits.' For this reason, comparing statistics for each transaction between Statspack and BSTAT/ESTAT can result in significantly different ratios. Incorrect Answers: B: Lock Manager is used to monitor locks only. C: Oracle Expert will not provide you with functionality described in the question. D: UTLBSTAT /UTLESTAT performance scripts present you with less valuable statistics. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 155 von164 BUFFER CACHE + RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA 2. Which two statements are true regarding the use of STATSPACK? (Choose two) BUFFER CACHE INDIZES A. B. C. D. E. F. Since undo segments are new as of Oracle9i, STATSPACK does not cover undo segments in the rollback segment statistics section. If snapshot collection is automated through DBMS_JOB package, the interval of statistics collection cannot be changed. Unlike UTLBSTAT/UTLESTAT, STATSPACK allows the flexibility of basing a report on any data points selected over the same uninterrupted running timeframe of each instance. Each time the snapshot is taken, STATSPACK will automatically generate a report that displays the statistical output, including load profile, efficiency percentage of the instance, and top five wait events. Since many init<SID>.ora parameters are dynamic, STATSPACK guarantees that the current parameter values shown in the output will be the ones used if the database is restarted. STATSPACK.snap procedure will store the current values for the performance statistics in the STATSPACK tables, which can be used as a baseline snapshot for comparison with another snapshot taken at a later time. INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen Explanation: STATSPACK guarantees that the current parameter values shown in the output will be the ones used if the database is restarted. Incorrect Answers: A: STATSPACK supports undo segments in the rollback segment statistics section. B: The interval of statistics collection can be changed. D: STATSPACK will not automatically generate a report that displays the statistical output. Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected. E: STATSPACK guarantees that the current parameter values shown in the output will be the ones used. The database does not require to be restarted. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 156 von164 + OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA 3. A developer has sent you multiple user SQL trace files related to the user session. BUFFER CACHE INDIZES INDEX-CLUSTER Which Oracle utility will convert this trace data into useable information? MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN A. B. C. D. E. F. TKPROF UTLESTAT UTLBSTAT STATSPACK LOG MINER SQL ANALYZER ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 157 von164 + / EXIT Testfragen EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA 4. Which two statements are true regarding STATSPACK? (Choose two) BUFFER CACHE INDIZES A. STATSPACK data should be separated from production data by putting it in its own tablespace. B. Obsolete statistical data collected by STATSPACK cannot be purged. DBAs will need to drop the user PERFSTAT and reinstall STATSPACK. C. To make performance comparisons from one day, week, or year to the next, there must be multiple snapshots taken over a period of time. D. You can capture both the complete text of a high-load SQL statement and information on any SQL plan(s) associated with that statement in a STATSPACK report. E. When a snapshot is taken, STATSPACK will automatically generate a report that displays the statistical output, including load profile, efficiency percentage of the instance, and top five wait events. INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE Explanation: In der Prüfung muss man 3 auswählen. Die Antworten sind identisch. Da STATSPACK keinen Report automatisch erzeugt, muss Antwort “D” richtig sein. In den Schulungsunterlagen steht auch ansatzweise drinnen, dass STATSPACK High-Load SQL Statements diagnostiziert. Antwort “E” ist definitiv falsch, STATSPACK generiert keinen Report automatisch, wenn ein Snapshot ausgeführt wird. Ein Report wird nur dann erstellt, wenn das Script “spreport.sql” ausgeführt wird. RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 158 von164 + INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG StatsPack Übungen Performance Tuning AGENDA 5. The STATSPACK report is similar in nature to which of the following? BUFFER CACHE INDIZES INDEX-CLUSTER A. B. C. D. MATERIALIZED VIEWS OEM Oracle Expert OEM Tuning Template SQL Trace and TKPROF UTLSTAT report.txt OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Explanation: The Statspack report is meant to replace UTLBSTAT/UTLESTAT and the resulting report.txt. Statspack enables the retention of multiple snapshots of performance statistics in the database, so you can generate a Statspack report to calculate the difference between parameters taken at two snapshots. STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 159 von164 + / EXIT EINFÜHRUNG Übungen Performance Tuning AGENDA BUFFER CACHE INDIZES Testfragen INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER STORED OUTLINES RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK Zeit: 4 ½ Minuten STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN Kostenloser Download der prüfungsnahen Testfragen vom 2005-11-21 http://www.freebraindumps.com/braindump-43659.htm ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 160 von164 + / EXIT Testfragen EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA 1. Which two statements about plan stability and stored outlines are true? (Choose two) A. B. C. D. E. You can group outlines in categories. You can only have one stored outline per SQL statement. Plan stability only wants when SQL statements match textually. Stored outlines are saved in the data dictionary (SYS schema). Stored outlines become invalid when you analyze the associated objects. Explanation: Oracle9i maintains predefined execution plans in the data dictionary in the form of stored outlines. Ascertain whether the statement you have identified can be lumped into two or more broad categories of statements. Through a process known as plan equivalence, a SQL statement must exactly match the original statement used to generate an outline in order for the stored outline to be used. Even an identical statement with the addition of a hint or comment will not be considered equivalent for stored outline usage. BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER Incorrect Answers: B: You can have as many stored outline per SQL statement as you need. D: Stored outlines are saved in the OUTLN schema. E: Stored outlines do not become invalid when you analyze the associated objects. That’s why they are used to preserve predefined execution plans RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 161 von164 + / EXIT Testfragen EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA 2. Which three statements are true about stored outlines in Oracle9i? (Choose three) A. Outlines always use the rule-based optimizer. B. The USE_STORED_OUTLINES parameter can be set at the session level. C. The same SQL statement can have a stored outline in more than one category. D. If USE_STORED_OUTLINES is set to TRUE, then outlines from the DEFAULT category are used. Explanation: The USE_STORED_OUTLINES parameter can be set at the session level. Outlines can be categorized to simplify the management task. The CREATE OUTLINE statement allows for specification of a category. The DEFAULT category is chosen if unspecified. Likewise, the CREATE_STORED_OUTLINES parameter lets you specify a category name, where specifying true produces outlines in the DEFAULT category. Incorrect Answer: A: By creating outlines for an application before switching to cost-based optimization, the plans generated by the rule-based optimizer can be used, while statements generated by newly written applications developed after the switch use cost-based plans RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 162 von164 + BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES / EXIT Testfragen EINFÜHRUNG Stored Outlines Übungen Performance Tuning AGENDA 3. Which two statements are valid settings for the initialization parameter ‘USE_STORED_OUTLINES’? (Choose two) BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS A. B. C. D. E. SQL> alter session set USE_STORED_OUTLINES = TRUE; SQL> alter session set USE_STORED_OUTLINES = PUBLIC; SQL> alter session set USE_STORED_OUTLINES = PRIVATE; SQL> alter session set USE_STORED_OUTLINES = DEFAULT; SQL> alter session set USE_STORED_OUTLINES = "outline name"; F. SQL> alter session set USE_STORED_OUTLINES = "category name"; OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE Explanation: There are two valid settings for the initialization parameter USE_STORED_OUTLINES: TRUE or category name. INDIZES MATERIALIZED VIEWS Incorrect Answers: B: This statement is incorrect. C: This statement is incorrect. D: This statement is incorrect. E: This initialization can use category name, not outline name. OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 163 von164 + / EXIT EINFÜHRUNG Übungen ¾ Der Mitarbeiter von morgen wird zum Kostenfaktor. Drin oder draußen lautet die Wahlmöglichkeit. Nur wer innovativ bleibt und schneller als der Konkurrent ist, wird überleben. Heute ist jeder selbst für seine Karriere zuständig. Finanzieller Erfolg hängt davon ab, wie gut man sich selbst verkauft. Robert Reich Performance Tuning AGENDA BUFFER CACHE INDIZES INDEX-CLUSTER MATERIALIZED VIEWS OPTIMIZER RESSOURCENPLAN ROLLBACK SEGMENT STATSPACK STORED OUTLINES ¾ If I have seen further than others, it is because I was standing on the shoulders of the giants who went before. Sir Isaac Newton TKPROF Testfragen OCP Exam 1Z0-033 BUFFER CACHE INDIZES MATERIALIZED VIEWS OPTIMIZER ¾ There’s always room at the top! RESSOURCENPLAN Don Burleson world’s “top-notch” Oracle DBA ROLLBACK SEGMENT STATSPACK STORED OUTLINES RENO FÖLLMER – STEFAN HIETEL – TIMO MEYER OCP DBA 10g und 9i | Wintersem. 2005 / 2006 | Seite 164 von164 + / EXIT
