Ausgewählte PL/SQL Packages (Teil 3)
DBMS_XPLAN DBMS_ASSERT DBMS_ASSERT DBMS_RESUMABLE DBMS_UTILITY
Ulrike Schwinn Business Unit Database
ORACLE Deutschland B.V. & Co. KG.
1. DBMS_XPLAN - zur Anzeige von Ausführungspläne
2. DBMS_ASSERT - zur Vermeidung von SQL Injektion
3. DBMS_RESUMABLE - zum Aufschieben von Operationen
Agenda
4 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
4. DBMS_UTILITY - ausgewählte Programme zur Unterstützung unterschiedlicher Funktionen
5 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Das Package DBMS_XPLAN
Ausführungspläne� Kommandos und Werkzeuge
– EXPLAIN PLAN: SQL Kommando zur Generierung des Plans in der PLAN_TABLE
– utlxplp.sql/utlxpls.sql:Standard Skripte zur formatierten Ausgabe (in $ORACLE_HOME/rdbms/admin)
– SQL*Plus: SET AUTOTRACE ON EXPLAIN
DBMS_XPLAN: Formatierte Ausgabe mit PL/SQL Package
6 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
– DBMS_XPLAN: Formatierte Ausgabe mit PL/SQL Package – Graphische Werkzeuge
� SQL Developer im SQL Worksheet� Cloud Control im SQL Worksheet
SQL Developer: SQL Worksheet
7 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
EM: Performance=>SQL=>SQL Worksheet
8 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
DBMS_XPLAN - Funktionsumfang� Aufgabe: Anzeige von formatierten Ausführungsplänen� Quellen:
– EXPLAIN PLAN– Laufzeit Statistiken aus dem Cursor Cache aus V$SQL_PLAN und
V$SQL_PLAN_STATISTICS– AWR (Automatic Workload Repository)
9 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
– AWR (Automatic Workload Repository)– SQL Tuning Sets (STS)
– SQL Plan Baselines
� Voraussetzung – Zugriffsrechte auf DBA Tabellen bzw. SELECT_CATALOG_ROLE – Interpretation von Ausführungsplänen ☺ (White Paper: The Oracle
Optimizer Explain the Explain Plan)
• DISPLAY – aus der PLAN_TABLE (siehe EXPLAIN PLAN)
• DISPLAY_PLAN – wie DISPLAY mit Formatausgabe wie HTML,TEXT..
• DISPLAY_CURSOR – aus dem Cursor Cache
• DISPLAY_SQLSET – aus dem SQL Tuning Set
DBMS_XPLAN: einige Funktionen
10 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
• DISPLAY_SQLSET – aus dem SQL Tuning Set
• DISPLAY_SQL_PLAN_BASELINE – aus der SQL Plan Baseline
• DISPLAY_AWR – aus dem AWR
• DIFF_PLAN – Vergleich zweier Pläne (neu in 12c)
Ausgabe mit DISPLAY
� Stelle mit utlxplan.sql die PLAN_TABLE zur Verfügung
� Führe EXPLAIN PLAN FOR ausEXPLAIN PLAN FOR
SELECT p.prod_name, SUM(s.quantity_sold)
FROM sh.sales s, sh.products p
WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;
11 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
� Einstellungen wählen– FORMAT: BASIC, TYPICAL , ALL, SERIAL mit Keyword Kombinationen
– FILTER_PREDS: letzter gespeicherter Plan (Default), sonst PLAN_IDSELECT * FROM table (DBMS_XPLAN.DISPLAY());
SELECT * FROM table (DBMS_XPLAN.DISPLAY(table_name => null,
statement_id => null, format => 'BASIC'‚ filter_preds => null));
Beispiel für DISPLAY mit FORMAT TYPICAL
12 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Ausgabe mit DISPLAY_PLAN
� Zusätzlich zu DISPLAY Angabe von TYPE für spezielles Format
� Beispiele: 'TEXT‘ (default), 'ACTIVE', 'HTML', 'XML‘
set long 10000 heading off
spool planausgabe.html
SELECT DBMS_XPLAN.DISPLAY_PLAN(type=>'HTML') from dual;
...
13 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
...
</script>
</body>
</html>
</iframe>
</body>
</html>
spool off
Verwendung von DISPLAY_CURSOR
1. Zuerst Statement ausführen
2. Cursor Child aus V$SQL selektieren
3. SQL*Plus Formatierung (set pagesize 0 linesize 130)
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR());
14 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => 'f4py1wd1vh5nj',
cursor_child_no => null,
format =>'BASIC ROWS'));
Ausgabe mit DISPLAY_CURSORSQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'f4py1wd1vh5nj',
CURSOR_CHILD_NO => null, format => 'BASIC ROWS'));
EXPLAINED SQL STATEMENT:
------------------------
SELECT p.prod_name, SUM(s.quantity_sold) FROM sh.sales s,
sh.products p WHERE s.prod_id =p.prod_id GROUP By p.prod_name
Plan hash value: 3535171836
--------------------------------------------------
15 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
--------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | HASH GROUP BY | | 71 |
| 2 | HASH JOIN | | 2804 |
| 3 | TABLE ACCESS FULL | PRODUCTS | 72 |
| 4 | PARTITION RANGE ALL| | 2804 |
| 5 | TABLE ACCESS FULL | SALES | 2804 |
--------------------------------------------------
DISPLAY_CURSOR ... Mehr Infos
• Optimizer kann sich verschätzen ...
• Ausgabe mit Estimated Rows (E-Rows) und Actual Rows (A-Rows) ist möglich
• Dazu: • Hint gather_plan_statistics mitgebenselect /*+ gather_plan_statistics */ count(*) Anzahl
16 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
• Spezielles Format IOSTATS LAST nutzen
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR
(sql_id =>'1nyxryfq8mzpk',
cursor_child_no => NULL,
format =>'IOSTATS LAST'))
select /*+ gather_plan_statistics */ count(*) Anzahl
from sh.customers where
cust_state_province = 'Bayern' and cust_city = 'Erding';
Estimated Rows und Actual RowsSQL_ID 30xu9nkcatfx0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) Anzahl from sh.customers
where cust_state_province = 'Bayern' and cust_city = 'Erding'
Plan hash value: 296924608
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------
17 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.23 | 1473 | 1471 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.23 | 1473 | 1471 |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 1 | 1 | 69 |00:00:00.06 | 1473 | 1471 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("CUST_CITY"='Erding' AND "CUST_STATE_PROVINCE"='Bayern'))
DISPLAY ... Indexgröße -Schätzungen?SQL> explain plan for create index i_test on test(object_name);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3303956404
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
18 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 88616 | 2076K| 153 (1)| 00:00:01
| 1 | INDEX BUILD NON UNIQUE| I_TEST | | | |
| 2 | SORT CREATE INDEX | | 88616 | 2076K| |
| 3 | TABLE ACCESS FULL | TEST | 88616 | 2076K| 77 (2)| 00:00:01
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- estimated index size: 4194K bytes
Neu in 12c: Adaptive Plan Generierung
19 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
� Ziel: Verhinderung von schlechten Plänen –während der ersten Ausführung (adaptive Pläne) bzw. nach der ersten Ausführung (Reoptimization)
� Implementierung auch in DBMS_XPLAN
Neu in 12c: Adaptive Pläne
� Adaptive Pläne haben verschiedene SQL Plan Optionenz.B. Subpläne (z.B. Hash Join, Nested Loop Join)
• Während der erste Ausführung, bevor der Plan aktiviert wird, wird einen Entscheidung getroffen=> Finaler Plan (Hash Join)
20 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Neu in 12c: Anzeige der adaptiven Pläne
select product_name
from oe.order_items o, oe.product_information p
where o.unit_price=15
and quantity > 1 and p.product_id = o.product_id;
21 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
SELECT * FROM table
(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ADAPTIVE'));
Das Ergebnis
22 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
23 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Das Package DBMS_ASSERT
SQL Injection: DBMS_ASSERT
� Mitgeliefertes dokumentiertes Package zur Überprüfung von Eingabe Werten zum Schutz vor SQL Injection
– Beispiele: --, 1=1, or, and, select, ...
� Überprüfung von Literalen, Objektnamen, existierenden Objekten bzw. Schemas
� Einsatz bei dynamisches SQL wie z.B. mit
24 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
� Einsatz bei dynamisches SQL wie z.B. mit – DBMS_SQL
– OPEN_FOR
– EXECUTE IMMEDIATE
Wichtige Funktionen
Existierende ObjekteExistierendes Schema
OBJECT_NAMESCHEMA_NAME
Einfacher SQL NameQualifizierter SQL Name
SIMPLE_SQL_NAMEQUALIFIED_SQL_NAME
Hochkomma – einfach ENQUOTE_LITERAL
25 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
� Arbeitsweise:– Rückgabe des Parameters, wenn OK
– PL/SQL-Exception, wenn nicht OK
Hochkomma – einfachDoppelte Hochkomma
ENQUOTE_LITERALENQUOTE_NAME
Exkurs – Quotes in SQL und PL/SQL
� StattSQL> SELECT 'ulrike''s web blog. It''s ...' str FROM dual;
STR
---------------------------------
ulrike's web blog. It's ...
26 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
� Nutze Quote Operator q
SELECT q'/ulrike's web blog. It's .../' str FROM dual;
SELECT q'|ulrike's web blog. It's ...|' str FROM dual;
SELECT q'#ulrike's web blog. It's ...#' str FROM dual;
ulrike's web blog. It's ...
ENQUOTE_LITERAL: Korrekte Literale?
SQL> SELECT DBMS_ASSERT.ENQUOTE_LITERAL(q'#KING#') FROM dual;
DBMS_ASSERT.ENQUOTE_LITERAL(Q'#KING#')
-----------------------------------------------------------------
'KING'
1 Zeile wurde ausgewählt.
SQL> SELECT DBMS_ASSERT.ENQUOTE_LITERAL(q'#KING' or 1=1 --#')
27 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
SQL> SELECT DBMS_ASSERT.ENQUOTE_LITERAL(q'#KING' or 1=1 --#')
FROM dual;
SELECT dbms_assert.enquote_literal(q'#King' or 1=1 --#') FROM
dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ASSERT", line 342
ORA-06512: at "SYS.DBMS_ASSERT", line 411
Korrekte Nutzung wäre ...
SQL> SELECT DBMS_ASSERT.ENQUOTE_LITERAL(q'#King'' or 1=1 --#')
FROM dual;
DBMS_ASSERT.ENQUOTE_LITERAL(Q'#KING''OR1=1--#')
-----------------------------------------------------------------
'King'' or 1=1 --'
1 Zeile wurde ausgewählt.
28 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
1 Zeile wurde ausgewählt.
SIMPLE_SQL_NAME: Korrekter Name?� Regeln:
– Mit alphabetische und alphanumerische Zeichen und ab der 2.ten Stelle auch _, $, und #
– Jedes Zeichen ist in “Double Quotes” erlaubt
– Leerzeichen sind erlaubt
29 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
select dbms_assert.SIMPLE_SQL_NAME('TEST') from dual;
select dbms_assert.SIMPLE_SQL_NAME('1TEST') from dual;
select dbms_assert.SIMPLE_SQL_NAME('TEST-') from dual;
select dbms_assert.SIMPLE_SQL_NAME('"TEST-"') from dual;
select dbms_assert.SIMPLE_SQL_NAME(' TEST') from dual;
select dbms_assert.SIMPLE_SQL_NAME(q'#TEST'S#') from dual;
SCHEMA_NAME: Existierendes Schema?SQL> SELECT DBMS_ASSERT.ENQUOTE_LITERAL
(dbms_assert.SCHEMA_NAME('SCOTT')) FROM dual;
DBMS_ASSERT.ENQUOTE_NAME(DBMS_ASSERT.SCHEMA_NAME('SCOTT'))
---------------------------------------------------------------
"SCOTT"
SQL> SELECT DBMS_ASSERT.ENQUOTE_LITERAL
30 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
SQL> SELECT DBMS_ASSERT.ENQUOTE_LITERAL
(DBMS_ASSERT.SCHEMA_NAME('TTT')) FROM dual;
select dbms_assert.enquote_name(dbms_assert.SCHEMA_NAME('TTT'))
from dual
*
ERROR at line 1:
ORA-44001: invalid schema
ORA-06512: at "SYS.DBMS_ASSERT", line 266
Beispiel: Passwort Prüfung
CREATE OR REPLACE PROCEDURE change_passwordproc
(username VARCHAR2, password VARCHAR2)
AS
stmt VARCHAR2(2000);
BEGIN
stmt := 'alter user '||sys.DBMS_ASSERT.ENQUOTE_NAME
31 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
stmt := 'alter user '||sys.DBMS_ASSERT.ENQUOTE_NAME
(DBMS_ASSERT.SCHEMA_NAME(username),FALSE)||
' identified by '||DBMS_ASSERT.SIMPLE_SQL_NAME(password);
...
dbms_output.put_line(stmt);
END;
/
Beispiel für Passwort Prüfung
SQL> EXEC change_passwordproc('HR','"test"')
alter user "HR" identified by test
PL/SQL procedure successfully completed.
SQL> EXEC change_passwordproc('HR','"test" quota unlimited on
system');
BEGIN change_passwordproc('HR','"test" quota unlimited on
32 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
BEGIN change_passwordproc('HR','"test" quota unlimited on
system'); END;
*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 160
ORA-06512: at "SYS.CHANGE_PASSWORDPROC", line 5
ORA-06512: at line 1
Fazit und Anwendungen
� Unbedingt berücksichtigen bei Verwendung von dynamischem SQL
� Auch interne Verwendung (z.B. SQL Patches etc.)
� Vorgehen: Überprüfung vor der Durchführung der Eingabe oderAusgabe einer Fehlermeldung
� Allerdings:
33 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
– Prüft nicht die Länge von Eingabe Strings (Buffer Overflow Attacken) oderTNS Connection Strings
– Schützt nicht vor Security Risiken durch nicht ausreichende Privilegien Vergabe – nicht berechtigte User usw.
– Bietet keinen Schutz vor HTML Injection
� Weitere gute Beispiele im Internet
34 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Das Package DBMS_RESUMABLE
Resumable Space Allocation� Problem: Operationen brechen wegen Speicherplatzfehlern ab
� Lösung: Resumable Space Allocation Feature– Ausführung der Operationen anhalten, Fehler zu beheben und die
Operation fortsetzen
� Welche Operationen:– Abfragen, DML-Kommandos, DDL- Kommandos, SQL*Loader,
35 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
– Abfragen, DML-Kommandos, DDL- Kommandos, SQL*Loader, Import/Export
� Wann?: zu kleine Quota, zu wenig Privilegien, zu wenig TEMP Space, nicht ausreichende Storage Settings
� Achtung: Automatische Verwendung bei Data Pump Export/Import! => Default: 2 Stunden (7200 Sekunden)
Ablauf I1. Ein Statement wird im RESUMABLE Mode angestartet.
– über SYSTEM/SESSION Parameter RESUMABLE_TIMEOUT
– über die Session-Einstellung ENABLE RESUMABLE
– Resumable Privileg ist erforderlich!
ALTER SESSION ENABLE RESUMABLE [TIMEOUT 3600];
36 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
2. Ein RESUMABLE Statement wird eine definierte Zeitdauer (TIMEOUT-Wert) angehalten (suspended), wenn eine OUT OF SPACE Bedingung eintritt.
ALTER SESSION|SYSTEM SET RESUMABLE_TIMEOUT = 3600;
Ablauf II3. Die eingetretenen Fehler werden aufgezeigt in
– Views wie DBA_RESUMABLE, V$SESSION_WAIT, DBA_OUTSTANDING_ALERTS
– der Alert-Datei
– AFTER SUSPEND Triggern, die automatisch auf das Speicherplatz-Problem reagieren können.
37 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Problem reagieren können.
4. Nach Lösung des Problems wird die gestoppte Operation weitergeführt, und das Alert "Resumable Session Suspended" wird gelöscht.
Beispiel DBA_RESUMABLESQL> SELECT user_id, session_id, status, timeout, start_time,
status, start_time, suspend_time, resume_time, sql_text, error_msg
FROM dba_resumable;
USER_ID SESSION_ID STATUS TIMEOUT START_TIME STATUS
---------- ---------- --------- ---------- -------------------- -----
START_TIME SUSPEND_TIME RESUME_TIME
-------------------- -------------------- --------------------
SQL_TEXT
38 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
SQL_TEXT
---------------------------------------------------------------------
ERROR_MSG
---------------------------------------------------------------------
110 70 SUSPENDED 28800 05/19/14 14:52:20 SUSPENDED
05/19/14 14:52:20 05/19/14 14:52:43
insert into t select * from t
ORA-01653: unable to extend table SCOTT.T by 1024 in tablespace LOW_COST_STORE
AnzeigeSQL> SELECT decode(message_level,5,'WARNING',1, 'CRITICAL') alert_level, reason
FROM dba_outstanding_alerts
WHERE reason LIKE '%resumable%';
ALERT_LE
--------
REASON
---------------------------------------------------------------------
WARNING
39 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
WARNING
Operation on resumable session User SCOTT(110), Session 70, Instance 1 session
id 70 suspended because of errors in tablespace LOW_COST_STORE. Error message
is
ORA-01653: unable to extend table SCOTT.T by 1024 in tablespace LOW_COST_STORE
In Cloud Control
40 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Das Package DBMS_RESUMABLE� Folgende Funktionen stehen zur Verfügung
– Prozedur ABORT
– Funktionen GET_SESSION_TIMEOUT oder GET_TIMEOUTSQL> select DBMS_RESUMABLE.GET_TIMEOUT() FROM dual;
DBMS_RESUMABLE.GET_TIMEOUT()
----------------------------
-1
41 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
– Prozedur SET_SESSION_TIMEOUT bzw. SET_TIMEOUT
– Funktion SPACE_ERROR_INFO (Ergebnis Boolean)
-1
Database Trigger
CREATE OR REPLACE TRIGGER resumable_default_timeout
� AFTER SUSPEND Database Trigger wird ausgeführt, sobald eine Resumable Operation eintritt
� Innerhalb des Triggers können Informationen aus DBMS_RESUMABLE und DBA_RESUMABLE verwendet werden.
� Beispiel
42 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
CREATE OR REPLACE TRIGGER resumable_default_timeout
AFTER SUSPEND
ON DATABASE
BEGIN
DBMS_RESUMABLE.SET_TIMEOUT(10800);
END;
/
Database TriggerCREATE OR REPLACE TRIGGER resumable_default
AFTER SUSPEND ON DATABASE
DECLARE …
BEGIN
ret_value :=
DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner,
table_space_name,object_name, sub_object_name);
IF table_space_name = 'TEST' THEN
INSERT INTO sys.resume_error (
43 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
SELECT sql_text, error_msg, suspend_time FROM DBMS_RESUMABLE
WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst);
-- email senden etc...
DBMS_RESUMABLE.ABORT(cur_sid);
ELSE
DBMS_RESUMABLE.SET_TIMEOUT(28800);
END IF;
COMMIT;
ErgebnisSQL> SELECT * FROM resume_error;
ERR_TYPE
--------------------------------------------------------------------------
OBJECT_OWNER OBJECT_TYPE TABLE_SPACE_NAM OBJECT_NAM SUB_OBJECT
--------------- --------------- --------------- ---------- ----------
NO MORE SPACE
TABLE SCOTT TEST T
44 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
� Dabei gibt es folgende Error Typen:– NO MORE SPACE– MAX EXTENTS REACHED– SPACE QUOTA EXCEEDED
45 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Das Package DBMS_UTILITY
DBMS_UTILITY... ein paar Beispiele
Messungen durchführen GET_TIMEGET_CPU_TIME
Informationen zur Datenbank DB_VERSION
46 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Warten auf Transaktionen WAIT_ON_PENDING_DML
12c: View Subquery ausgeben EXPAND_SQL_TEXT
Informationen zur Datenbank übre DB_VERSION
� Informationen zur genauen Version mit PL/SQLSQL> variable version varchar2(200)
SQL> variable compatibility varchar2(200)
SQL> execute DBMS_UTILITY.DB_VERSION(:version,:compatibility);
PL/SQL procedure successfully completed.
SQL> print version
VERSION
47 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
� Weitere Möglichkeit mit DBMS_DB_VERSION
VERSION
---------------------------------------------------------------
11.2.0.3.0
SQL> set serveroutput on
SQL> execute DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION ||
' und ' '||DBMS_DB_VERSION.RELEASE);
11 und 2
Messungen mit GET_CPU_TIME
� Zeitmessung
� Messung der CPU Zeiten in hundertstel Sekunden
...
start_cpu_time := DBMS_UTILITY.GET_CPU_TIME;
-- Programmlauf
end_cpu_time := DBMS_UTILITY.GET_CPU_TIME;
48 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
end_cpu_time := DBMS_UTILITY.GET_CPU_TIME;
dbms_output.put_line('CPU Time= ' || end_cpu_time - start_cpu_time);
...
Warten mit WAIT_ON_PENDING_DML
� Eine Session kann so lange warten, bis die zuvor angestarteten Transaktionen abgeschlossen sind oder bis zu einem Timeout.
� Wartezeit und SCN, ab wann die Transaktionen relevant sind, können angegeben werden
� Implementierung in 11g Release 2 zur Unterstützung des Features
49 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
„Edition Based Redefinition“
Anwendungsfall: Online Application Upgradeaka "Edition Based Redefinition"
� PL/SQL-Code im laufenden Betrieb ersetzen– Keine Downtime mehr nötig
– Versionsmanagement in der Datenbank
50 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
– Parallelbetrieb mehrerer Editionen möglich
– "Retirement" nicht mehr benötigter Versionen
Beispiel WAIT_ON_PENDING_DML
CREATE OR REPLACE PROCEDURE wait_test
AS
v_bool BOOLEAN;
v_scn NUMBER;
BEGIN
v_bool := dbms_utility.wait_on_pending_dml
(tables => 'SCOTT.T, SCOTT.DEPT',timeout => 10, scn => v_scn );
dbms_output.put_line( 'scn: ' || v_scn );
IF v_bool
51 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
– Wartezeit 10 Sekunden
– SCN Zeitpunkt für Transaktionen
IF v_bool
THEN dbms_output.put_line( 'true' );
ELSE dbms_output.put_line( 'false' );
END IF;
END;
/
Beispiel: WAIT_ON_PENDING_DML
� Starte eine Session mit Prozedur wait_test
� Unabhängige davon gibt es weitere Sessions, die INSERTs durchführen
– Dabei erfolgt kein Transaktionsende innerhalb von 10 Sekunden
� Ergebnis der ersten Session nach 10 Sekunden …
52 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
� Ergebnis der ersten Session nach 10 Sekunden … – FALSE da Transaktionen noch laufen
SQL> execute wait_test …
scn: 13618833
false
Neu in 12c: EXPAND_SQL_TEXT
� Löst Abfragen auf Views auf
� Eingabe: Subquery auf Views; Ergebnis: Abfrage auf zugeh. TabellenSQL> variable outclob clob
SQL> create or replace view my_view as select * from sales
where amount_sold<1300;
SQL> execute DBMS_UTILITY.EXPAND_SQL_TEXT ('select
53 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
OUTCLOB
---------------------------------------------------------------
SELECT COUNT("A1"."PROD_ID") "COUNT(PROD_ID)" FROM (SELECT
"A2"."PROD_ID" "PROD_ID" FROM SH."SALES" "A2" WHERE
"A2"."AMOUNT_SOLD"<1300) "A1"
count(prod_id) from my_view', :outclob);
SQL> print outclob
Zusammenfassung
Package Einsatz
DBMS_XPLAN Anzeige von Ausführungspläne
DBMS_ASSERT Vermeidung von SQL Injektion
54 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
DBMS_RESUMABLE Aufschieben von Operationen
DBMS_UTILITY Utility Programme
� Homepage der deutschsprachigen DBA Tipps: blogs.oracle.com/dbacommunity_deutsch
� Alle Veranstaltungen (events) und Links untertinyurl.com/oraclebudb
– Oracle Developers' Monthly
Nützliche Informationen und Links
55 Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
– Oracle Developers' Monthly
– Veranstaltung in Frankfurt am 17.Juni
� Download: apex.oracle.com/folienSchlüsselwort: webinar_packages3
� Seminarreihe hat Sommerpause ☺