Dynamische Session-Einstellungen am Beispiel des "invisible index"-Features
von Ulrike Schwinn, ORACLE Deutschland GmbH

Initialisierungsparameter können statisch oder dynamisch sein. Dynamische Parameter lassen sich mit dem ALTER SESSION-Kommando einstellen und gelten für die Dauer der Session. Dadurch wird es möglich, viele Funktionen in der Session zu nutzen, ohne das gesamte System zu beeinflussen.

Die folgende Liste zeigt einige dieser dynamischen Parameter und gibt eine kurze Beschreibung dazu

  • OPTIMIZER_USE_SQL_PLAN_BASELINES (11g): Verwendung von SQL Plan Baselines
  • DB_SECUREFILE (11g): Nutzung von LOBs als SECUREFILEs
  • SQLTUNE_CATEGORY: Nutzung von SQL Profiles
  • OPTIMIZER_USE_PENDING_STATISTICS (11g): Verwendung von Pending Statistiken
  • PLSQL_CODE_TYPE: Kompilierungsmodus für PL/SQL (einzige notwendige Einstellung in 11g)
  • RESULT_CACHE_MODE (11g): Nutzung des Result Caches
  • OPTIMIZER_FEATURES_ENABLE: Nutzung von versionsabhängigen Optimizer Features
  • OPTIMIZER_MODE: Genereller Einfluss auf das Optimizerverhalten
  • RECYCLEBIN: Ein- und Ausschalten des Recyclebin


    • (11g): Die erst ab 11g zur Verfügung stehenden Funktionen sind gekennzeichnet.

    Besonders im aktuellen Datenbank Release 11g sind einige wichtige Funktionen und Features eingeführt worden, die sich meist auch über dynamische Initialisierungsparameter einstellen lassen.
    Einige dieser Features und Parameter sind in den letzten Tipps besprochen worden. So finden Sie z.B. die Informationen zu

  • SQL Plan Management und dem Parameter OPTIMIZER_USE_SQL_PLAN_BASELINES im Tipp SQL Pläne in 11g und das SQL Plan Management Feature.
  • Pending Statistiken und dem Parameter OPTIMIZER_USE_PENDING_STATISTICS im Tipp Neues zum Statistikmanagement mit DBMS_STATS.
  • Einsatz des Result Cache und dem Parameter RESULT_CACHE im Tipp Der Result Cache in 11g: Nutzung, Administration und Monitoring.


    • Generell gibt übrigens das Inhaltsverzeichnis einen guten Überblick über alle bis jetzt veröffentlichten Tipps.
    Im aktuellen Tipp wird die Verwendung der dynamischen Parameter am Beispiel der Nutzung der invisible Indizes demonstriert.

    Möchte man zuerst eine vollständige Liste aller dynamischen Parameter einsehen, bietet sich ein einfaches SELECT auf V$PARAMETER an. Folgendes Beispiel zeigt einen Ausschnitt 
    SQL> col name format a40
SQL> col value format a30
SQL> SELECT name, value FROM v$parameter
     WHERE ISSES_MODIFIABLE='TRUE';

NAME                                     ISSES
---------------------------------------- -----
...
recyclebin                               on
db_securefile                            PERMITTED
create_stored_outlines
global_names                             FALSE
session_cached_cursors                   50
remote_dependencies_mode                 TIMESTAMP
smtp_out_server
plsql_v2_compatibility                   FALSE
plsql_warnings                           DISABLE:ALL
plsql_code_type                          INTERPRETED
plsql_debug                              FALSE
plsql_optimize_level                     2
plsql_ccflags
plscope_settings                         IDENTIFIERS:ALL
java_jit_enabled                         TRUE
parallel_min_percent                     0
cursor_sharing                           EXACT
result_cache_mode                        MANUAL
...
    Graphisch kann man diese Informationen auch mit Werkzeugen wie dem Enterprise Manager oder dem SQL Developer veranschaulichen. Der folgende Screenshot zeigt alle dynamischen Initialisierungspartameter im Enterprise Manager an. Zu finden sind die Einstellungen je nach Datenbank-Release im Bereich "Server" oder "Administration".


    Für eine größere Ansicht auf das Bild klicken.

    Der folgende Screenshot zeigt die Implementierung dieser Funktionalität im "Report"-Zweig des SQL Developers.


    Für eine größere Ansicht auf das Bild klicken.

    In den folgenden Abschnitten wird das Feature invisible Index-Nutzung nun näher betrachtet.

    Indizes im Rahmen von Tuningmassnahmen zu optimieren bedeutet, dass Indizes unter Umständen gelöscht werden müssen oder neue Indizes hinzugefügt werden. Diese Operationen können bei grossen Tabellen sehr aufwändig und langlaufend sein. Um dieser Problematik Abhilfe zu verschaffen sind mit dem aktuellen Release 11g die sogenannten invisible indices eingeführt worden. Der Name des Index beschreibt dabei treffend seine Eigenschaft: Der Index ist für den Optimizer bzw. die Statementausführung unsichtbar (engl: invisible). Allerdings ist der Index physikalisch vorhanden und wird bei allen DML-Operationen gepflegt.

    Möchte man zum Beispiel die Auswirkungen von neu erzeugten Indizes zunächst nur für gewisse Statements und Applikationen austesten, ist dieses neue Feature optimal geeignet. Der Index wird einfach im CREATE- oder ALTER-Statement mit der Eigenschaft INVISIBLE belegt. Folgendes Beispiel zeigt eine Implementierung mit dem CREATE-Statement. 
    CREATE INDEX customers_marital_bix ON customers (cust_marital_status) INVISIBLE;

    Die Standardeinstellung beim Indexerzeugen ist natürlich VISIBLE. Jeder beliebige Index kann übrigens im nachhinein die Eigenschaft INVISIBLE erhalten. Die aktuelle Einstellung der Indizes lässt sich leicht in der View USER_INDEXES in der neuen Spalte VISIBILITY nachvollziehen. 
    SQL> ALTER INDEX customers_marital_bix INVISIBLE;
Index altered.

SQL> SELECT index_name, visibility FROM user_indexes where visibility='INVISIBLE';
INDEX_NAME                     VISIBILIT
------------------------------ ---------
CUSTOMERS_MARITAL_BIX          INVISIBLE
    Nutzen kann man diese Indexeinstellung mit einem Sessionparameter oder mit einem Statementhint. Verändert man den Sessionparameter nicht und fügt keinen Hint hinzu, bleibt der Index ungenutzt wie folgender Screenshot im SQL Developer zeigt.


    Für eine größere Ansicht auf das Bild klicken.

    Folgendes Beispiel zeigt nun die Nutzung mit dem dynamischen Parameter OPTIMIZER_USE_INVISIBLE_INDEXES. 
    ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
    Der Optimizer verwendet offensichtlich den Index.


    Für eine größere Ansicht auf das Bild klicken.

    Im letzten Beispiel wird demonstriert wie mithilfe eines Statementhints die Ausführung eines Statements beeinflusst werden kann. Diese Einstellung ist unabhängig von der Sessioneinstellung. Dazu muss nun die Hinteinstellung /*+ USE_INVISIBLE_INDEXES INDEX(tabname indexname) */ zum Beispiel-Statement hinzugefügt werden. Das vollständige Statement sieht dann folgendermassen aus 
    SELECT /*+ USE_INVISIBLE_INDEXES INDEX(customers customers_marital_bix) */ count(*) 
FROM customers 
WHERE cust_marital_status ='married';


    Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

    Zurück zur Community-Seite