Execution Plan

es wird behauptet, manchmal sei es besser nichts zu schreiben als nicht zu schreiben, daher hier ein paar Hinweise, die man an ca. 10000 anderen Orten finden kann:

In 10g lassen sich Zugriffspläne sehr komfortabel über das dbms_xplan-Package darstellen, also z.B.:

SQL> r
1  SELECT plan_table_output
2*   FROM TABLE( dbms_xplan.display_cursor ( 'a5ks9fhw2v9s1', 0))

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 Zeilen ausgewählt.

Dabei ist der erste Parameter die sql_id und der zweite cursor_child_no (beide findet man in v$sql). Wenn ich jetzt nichts durcheinander werfe, ist der Formatierungsmechanismus der gleiche der bei autotrace verwendet wird.

Flashback-Query

Ein ganz simples Beispiel dieser praktischen Funktionalität, mit deren Hilfe man sich ansehen kann, wie eine Tabelle zu einem bestimmten Zeitpunkt in der Vergangenheit gefüllt war:

-- Anlage der Tabelle test1, die mit fünf Sätze gefüllt wird:
create table test1 (a number);

insert into test1 values (1);
insert into test1 values (2);
insert into test1 values (3);
insert into test1 values (4);
insert into test1 values (5);

commit;

-- ein Blick auf die Uhr:
select to_char(sysdate, 'hh24:mi:ss') from dual;

TO_CHAR(
--------
10:30:29

-- und auf den Inhalt der Tabelle:
select * from test1;

A
----------
1
2
3
4
5

5 Zeilen ausgewählt.

-- jetzt löschen wir drei Zeilen:
delete from test1 where a > 2;

3 Zeilen wurden gelöscht.

commit;

-- 5 - 3 = 2
select * from test1;

A
----------
1
2

2 Zeilen ausgewählt.

-- mit Hilfe der flashback query können wir sehen, was vor der Löschung in der Tabelle enthalten war
SELECT *
  FROM Test1
  AS OF TIMESTAMP ( TO_TIMESTAMP( '30.05.07 10:30:30', 'DD.MM.YY HH24:MI:SS') )

A
----------
1
2
3
4
5

5 Zeilen ausgewählt.

Quizfrage

Bei Dominic Delmolino (http://www.oraclemusings.com/?p=57) gab's kürzlich eine interessante SQL-Aufgabe. Hier meine (nicht besonders hübsche) Lösung dazu:

SQL> r
select case when lag_col is null then min_col
when substr(min_col, 1, 1) <> substr(lag_col, 1, 1)
then substr(min_col, 1, 1)
when substr(min_col, 1, 1) = substr(lag_col, 1, 1)
and substr(min_col, 1, 2) <> substr(lag_col, 1, 2) then substr(min_col, 1, 2)
when substr(min_col, 1, 2) = substr(lag_col, 1, 2)
and substr(min_col, 1, 3) <> substr(lag_col, 1, 3) then substr(min_col, 1, 3)
when substr(min_col, 1, 3) = substr(lag_col, 1, 3)
and substr(min_col, 1, 4) <> substr(lag_col, 1, 4) then substr(min_col, 1, 4)
when substr(min_col, 1, 4) = substr(lag_col, 1, 4)
and substr(min_col, 1, 5) <> substr(lag_col, 1, 5) then substr(min_col, 1, 5)
when substr(min_col, 1, 5) = substr(lag_col, 1, 5)
and substr(min_col, 1, 6) <> substr(lag_col, 1, 6) then substr(min_col, 1, 6)
when substr(min_col, 1, 6) = substr(lag_col, 1, 6)
and substr(min_col, 1, 7) <> substr(lag_col, 1, 7) then substr(min_col, 1, 7)
when substr(min_col, 1, 7) = substr(lag_col, 1, 7)
and substr(min_col, 1, 8) <> substr(lag_col, 1, 8) then substr(min_col, 1, 8)
when substr(min_col, 1, 8) = substr(lag_col, 1, 8)
and substr(min_col, 1, 9) <> substr(lag_col, 1, 9) then substr(min_col, 1, 9)
when substr(min_col, 1, 9) = substr(lag_col, 1, 9)
and substr(min_col, 1, 10) <> substr(lag_col, 1, 10) then substr(min_col, 1, 10)
when substr(min_col, 1, 10) = substr(lag_col, 1, 10)
and substr(min_col, 1, 11) <> substr(lag_col, 1, 11) then substr(min_col, 1, 11)
when substr(min_col, 1, 11) = substr(lag_col, 1, 11)
and substr(min_col, 1, 12) <> substr(lag_col, 1, 12) then substr(min_col, 1, 12)
else null end
|| ' thru ' ||
case when lead_col is null then max_col
when substr(max_col, 1, 1) <> substr(lead_col, 1, 1)
then substr(max_col, 1, 1)
when substr(max_col, 1, 1) = substr(lead_col, 1, 1)
and substr(max_col, 1, 2) <> substr(lead_col, 1, 2) then substr(max_col, 1, 2)
when substr(max_col, 1, 2) = substr(lead_col, 1, 2)
and substr(max_col, 1, 3) <> substr(lead_col, 1, 3) then substr(max_col, 1, 3)
when substr(max_col, 1, 3) = substr(lead_col, 1, 3)
and substr(max_col, 1, 4) <> substr(lead_col, 1, 4) then substr(max_col, 1, 4)
when substr(max_col, 1, 4) = substr(lead_col, 1, 4)
and substr(max_col, 1, 5) <> substr(lead_col, 1, 5) then substr(max_col, 1, 5)
when substr(max_col, 1, 5) = substr(lead_col, 1, 5)
and substr(max_col, 1, 6) <> substr(lead_col, 1, 6) then substr(max_col, 1, 6)
when substr(max_col, 1, 6) = substr(lead_col, 1, 6)
and substr(max_col, 1, 7) <> substr(lead_col, 1, 7) then substr(max_col, 1, 7)
when substr(max_col, 1, 7) = substr(lead_col, 1, 7)
and substr(max_col, 1, 8) <> substr(lead_col, 1, 8) then substr(max_col, 1, 8)
when substr(max_col, 1, 8) = substr(lead_col, 1, 8)
and substr(max_col, 1, 9) <> substr(lead_col, 1, 9) then substr(max_col, 1, 9)
when substr(max_col, 1, 9) = substr(lead_col, 1, 9)
and substr(max_col, 1, 10) <> substr(lead_col, 1, 10) then substr(max_col, 1, 10)
when substr(max_col, 1, 10) = substr(lead_col, 1, 10)
and substr(max_col, 1, 11) <> substr(lead_col, 1, 11) then substr(max_col, 1, 11)
when substr(max_col, 1, 11) = substr(lead_col, 1, 11)
and substr(max_col, 1, 12) <> substr(lead_col, 1, 12) then substr(max_col, 1, 12)
else null end spine, min_col, max_col
from (select ntile_range,
min_col,
max_col,
lead( min_col) over (order by ntile_range) lead_col,
lag( max_col) over (order by ntile_range) lag_col
from (select ntile_range,
min(column_name) min_col,
max(column_name) max_col
from (select column_name,
ntile(15) over(order by column_name) ntile_range
from ac1
)
group by ntile_range order by ntile_range
)
) t

SPINE MIN_COL MAX_COL
------------------------- ------------------------------ ------------------------------
A thru BITMAP A BITMAP
BITMAPP thru C_ BITMAPPED C_OBJ#
C1 thru De C1 Default
D1 thru ERR_ D1 ERR_NUM
ERRO thru GENL ERRORS GENLINKS
GENO thru I_ GENOPTION I_AGREE
IN thru LOB_ INTRO LOB_COL_NAME
LOBI thru MV_QUA LOBINDEX MV_QUANTITY_SUM
MV_QUE thru Op MV_QUERY_GEN_MISMATCH Option
OS thru P_ OSHST P_REF_TIME
P1 thru R_ P1 R_CONSTRAINT_NAME
R1 thru SHORT_WAITS R1 SHORT_WAITS
SHORT_WAIT_ thru Si SHORT_WAIT_TIME_MAX Size
SU thru T_ SUMGROSSTURNOVER T_PER_EXEC
T1 thru ZERO_RESULTS T1OBJID ZERO_RESULTS

15 Zeilen ausgewählt.

case insensitive Suche unter Oracle

Bei Tom Kyte (wo sonst?) findet man folgende hübsche Möglichtkeit, in 10g case eine insensitive Konditionsprüfung zu ermöglichen. Relevant wäre diese Option beispielsweise dann, wenn man die Statements einer Applikation nicht ändern - und die Prüfung z.B. nicht mit einem UPPER beeinflussen - kann.

create table t ( data varchar2(20) );

insert into t values ( 'Hello' );
insert into t values ( 'HeLlO' );
insert into t values ( 'HELLO' );

-- wie zu erwarten liefert die folgende Query
-- zunächst kein Ergebnis
select * 
  from t
 where data = 'hello'

 Es wurden keine Zeilen ausgewählt

alter session set nls_comp=ansi;

Session wurde geändert.

alter session set nls_sort=binary_ci;

Session wurde geändert.

-- Nach Anpassung der beiden NLS-Parameter 
-- wird die Bedingung case insensitive behandelt
select * 
  from t
 where data = 'hello'

DATA
--------------------
Hello
HeLlO
HELLO

3 Zeilen ausgewählt.

set autot trace
select * 
  from t
 where data = 'hello'

3 Zeilen ausgewählt.

Ausführungsplan
--------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 |
--------------------------------------------------

-- durch die Anlage eines function based index kann man den
-- Zugriff dann auch noch optimieren.
create index t_idx on t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );

select * 
  from t
 where data = 'hello';

3 Zeilen ausgewählt.

Ausführungsplan
-----------------------------------------------------
Plan hash value: 470836197
-----------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 |
-----------------------------------------------------

Zu Prüfen wäre in einem solchen Fall nur, welche weiteren Auswirkungen die Änderung der NLS-Parameter auf eine Applikation haben könnte. Zusätzliche Details zum Thema findet man im AskTom-Thread.

OracleDBConsole-Dienst neu erzeugen

Der OracleDBConsole-Dienst hat (zumindest bei mir) einen gewissen Hang dazu, den Geist aufzugeben. Um ihn neu zu konfigurieren, kann man in 10.2 folgenden Befehl verwenden:

C:\> emca -config dbcontrol db -repos recreate

STARTED EMCA um 30.04.2007 09:43:02
EM-Konfigurationsassistent, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved. Alle Rechte vorbehalten.

Geben Sie folgende Informationen ein:
Datenbank-SID: ...
(hier werden dann noch diverse Passwörter und Mail-Adressen abgefragt)

Die Installation wird in einer Log-Datei protokolliert und dauert ein paar Minuten.

In 10.1 konnte man zu diesem Zweck noch die Variante emca -r verwenden.

Nachtrag 16.05.2011: in 11.2 funktioniert das Verfahren ebenfalls, allerdings ergaben sich auf meinem Windows7-Test-Rechner diverse Rechte-Probleme, die z.T. durch Vergabe von Schreibrechten an meinen User und z.T. durch Ausführung des Befehls in einer als Administrator geöffneten Eingabeaufforderung gelöst werden konnten.

Logging fehlerhafter Sätze bei direct path inserts

Tim Hall erläutert unter http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php, wie man beim Bulk-Insert alle Sätze, die Constraint-Bedingungen widersprechen, in einer mit Hilfe von dbms_errlog erzeugten Log-Tabelle vermerken kann. Die Beispiele sind sehr ausführlich und hübsch präsentiert (wie's beim Herrn Hall üblich ist).

Verkleinerung von LOB-Segmenten

Nachtrag am 19.10.2010: inzwischen muss ich von der hier vorgestellten Operation abraten, da sie einen recht häßlichen Bug auf den Plan rufen kann.

In Oracle 10g können Tabellen über ein ALTER TABLE ... SHRINK im laufenden Betrieb reorganisiert und verkleinert werden. Durch die CASCADE-Option werden auch abhängige Objekte wie Indizes und LOBs verkleinert. Voraussetzung für den Einsatz dieses Features ist ein Tablespace, für den ASSM (Automatic Segment Space Management) aktiviert ist (das ist bei uns und per default der Fall), außerdem muss für die entsprechende Tabelle das <row movement> aktiviert werden, so dass die physikalische Adresse der Einträge geändert werden kann:

select segment_name
     , bytes
  from user_segments
 where segment_name IN (select segment_name
                          from user_lobs
                         where table_name = 'SIMPLENODECONTENT')
    or segment_name = 'SIMPLENODECONTENT';

SEGMENT_NAME                        BYTES
------------------------------ ----------
SYS_LOB0000109571C00002$$       855638016
SIMPLENODECONTENT                 7340032

alter table simplenodecontent enable row movement;

Tabelle wurde geändert.

-- diese Operation kann längere Zeit in Anspruch nehmen:
alter table simplenodecontent shrink space cascade;

Tabelle wurde geändert.

Abgelaufen: 00:02:34.96

alter table simplenodecontent disable row movement;

Tabelle wurde geändert.

select segment_name
     , bytes
  from user_segments
 where segment_name IN (select segment_name
                          from user_lobs
                         where table_name = 'SIMPLENODECONTENT')
    or segment_name = 'SIMPLENODECONTENT';

SEGMENT_NAME                        BYTES
------------------------------ ----------
SYS_LOB0000109571C00002$$       158466048
SIMPLENODECONTENT                 1769472

Shrink temporary tablespace

Der schnellste Weg, einen temporären Tablespace zu verkleinern, ist, einen neuen temporären TS anzulegen, alle User des alten auf den neuen umzuleiten und den alten zu droppen. Da die Extents eines temporären Tablespaces erst beim Herunterfahren der Instanz freigegeben werden, läuft ein SHRINK oder RESIZE in der Regel auf Fehler.

Also funktioniert in 10g folgendes:

-- Anlage eines neuen temporären TS
create temporary tablespace temp1 tempfile …;

-- Generierung von Queries, mit denen allen DB-User 
-- der neue temporären TS zugeordnet wird
select 'alter user ' || username || ' temporary tablespace temp1;'
  from dba_users
 where temporary_tablespace = 'TEMP';

-- den neuen temporären TS als default TS setzen
alter database default temporary tablespace temp1;

-- alle Sessions beenden, die noch Einträge in v$sort_usage halten

-- jetzt kann man den alten temporären TS wegwerfen
drop tablespace temp;

alter tablespace temp1 rename to temp;

Nachtrag 16.05.2011: In 11.2.0.1 funktioniert das Vorgehen immer noch (was nicht allzu sehr überrascht)

Nachtrag 23.03.2012: Tom Kyte weist dieser Tage darauf hin, dass das Verkleinern in 11g deutlich einfacher geworden ist - es genügt ein "alter tablespace temp_xyz shrink space".