Häufig stellt sich die Frage, wie man die Zugriffe auf Objekte wie Tabellen und Indizes überwachen kann. Abhängig davon können beispielsweise Entscheidungen über die Existenz von Indizes oder die Art der Partitionierung getroffen werden bzw. Optimizerzugriffe besser eingeschätzt werden. Die Nutzung von Auditing bzw. die Verwendung von Objekt Statistiken kann dabei natürlich hilfreich sein. Darüberhinaus bietet Oracle allerdings weitere Monitoring Techniken an, die es erlauben, mit speziellen Views und Tabellen Spaltenzugriffe in der WHERE Klausel, TRUNCATE Operationen oder DML Zugriffe zu überwachen.
Hinweis: Für Indizes wurde dazu schon in einem älteren Tipp das Index Monitoring beschrieben. Dies beantwortet die Frage, ob bestimmte Indizes überhaupt verwendet werden. Mehr dazu können Sie hier nachlesen.

Im folgenden Tipp sollen zwei Techniken des Tabellen Monitorings erläutert werden: das Tabellen Monitoring speziell für DML, TRUNCATE und DROP PARTITION Operationen und das Monitoring der Prädikatnutzung von Spalten in der WHERE Klausel.

DML Monitoring mit DBA_TAB_MODIFICATIONS
Seit jeher gibt es die Möglichkeit, Aktivitäten wie INSERT, UPDATE, DELETE und TRUNCATE auf Tabellen sowie DROP Operationen auf Partitionen zu überwachen. Vor 10g musste man dazu manuell die entsprechenden Tabellen mit dem Attribut MONITORING (vergleichbar mit dem Index Monitoring) versehen. Ab 10g ist dies nicht mehr erforderlich, da der Statistik Maintenance Job diese Informationen für das eigene Statistik Gathering benötigt und daher automatisch das Monitoring einschaltet. Einzige Voraussetzung ist das Setzen von STATISTICS_LEVEL auf TYPICAL. Wird beispielsweise ein bestimmter Prozentsatz (Default ist 10 Prozent) an veränderten Zeilen überschritten, sind die Tabellen Statistiken STALE und werden beim nächsten Statistik Gathering Job mitgepflegt.

Standardmässig werden diese Informationen in der SGA gehalten. Möchte man diese Statistiken allerdings umgehend sichtbar machen (materialisieren), kann man dazu die Prozedur FLUSH_DATABASE_MONITORING_INFO aus dem Package DBMS_STATS verwenden. Zu finden sind die entsprechenden Informationen dann in der View DBA_TAB_MODIFICATIONS. So lässt sich zum Beispiel verifizieren, ob eine Tabelle nach dem Maintenance Job starken Veränderungen unterliegt.

Folgendes einfache Beispiel demonstriert das Verhalten.

-- aktuelle Statistiken der Tabelle EMP_TEST
SQL> SELECT num_rows, last_analyzed
     FROM user_tab_statistics WHERE table_name='EMP_TEST'

  NUM_ROWS LAST_ANAL
---------- ---------
        14 22-MAY-13

-- keine Zeile in USER_TAB_MODIFICATIONS, da keine Veränderung nach dem Maintenance Job erfolgte
SQL> SELECT * FROM user_tab_modifications;
no rows selected

-- DML Operationen
insert into emp_test select * from emp_test;
update emp_test set empno=empno;
commit;

-- SGA Information in die Data Dictionary View DBA_TAB_MODIFICATIONS überführen  
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
PL/SQL procedure successfully completed.

-- Informationen aus der View
SQL> SELECT * FROM user_tab_modifications;

TABLE_NAME    PARTITION_NAME  SUBPARTITION_NA    INSERTS    UPDATES    DELETES
------------- --------------- --------------- ---------- ---------- ----------
TIMESTAMP TRU DROP_SEGMENTS
--------- --- -------------
EMP_TEST                                              14         28          0
22-MAY-13 NO              0

-- Informationen auch in die DD Views
SQL> execute DBMS_STATS.GATHER_TABLE_STATS('SH','EMP_TEST');
PL/SQL procedure successfully completed.

SQL> SELECT num_rows, last_analyzed 
     FROM user_tab_statistics WHERE table_name='EMP_TEST';

  NUM_ROWS LAST_ANAL
---------- ---------
        28 22-MAY-13

-- USER_TAB_MODIFICATIONS ist leer
SQL> SELECT * FROM user_tab_modifications;
no rows selected

SQL> SELECT dbms_stats.get_prefs(pname=>'STALE_PERCENT',ownname=>'SH',tabname=> 'EMP_TEST') 
     FROM dual;
DBMS_STATS.GET_PREFS('STALE_PERCENT','SH','EMP_TEST')
--------------------------------------------------------------------------------
10
SQL> execute DBMS_STATS.SET_TABLE_PREFS(ownname=>null,tabname=>'EMP_TEST',pname=>'STALE_PERCENT',pvalue=>20);
SQL> SELECT dbms_stats.get_prefs(pname=>'STALE_PERCENT',ownname=>'SH',tabname=> 'EMP_TEST') 
     FROM dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SH','EMP_TEST')
--------------------------------------------------------------------------------
20

SQL> desc sys.col_usage$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                               NUMBER
 INTCOL#                                            NUMBER
 EQUALITY_PREDS                                     NUMBER
 EQUIJOIN_PREDS                                     NUMBER
 NONEQUIJOIN_PREDS                                  NUMBER
 RANGE_PREDS                                        NUMBER
 LIKE_PREDS                                         NUMBER
 NULL_PREDS                                         NUMBER
 TIMESTAMP                                          DATE

create view dba_column_usage
as
select oo.name owner,
o.name,
c.name column_name,
u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds,
u.timestamp
from sys.col_usage$ u,
sys.obj$ o,
sys.user$ oo,
sys.col$ c
where o.obj# = u.obj#
and oo.user# = o.owner#
and c.obj# = u.obj#
and c.col# = u.intcol#;

create public synonym dba_column_usage for dba_column_usage;

select * from emp_kopie where deptno=10 and empno!=0;
select e.empno, d.deptno from emp_kopie e, dept_kopie d where d.deptno=e.deptno and d.deptno in (10,20); 

SQL> SELECT * FROM dba_column_usage WHERE name LIKE '%KOPIE';

OWNER                          NAME
------------------------------ ------------------------------
COLUMN_NAME                    EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS
------------------------------ -------------- -------------- -----------------
RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
----------- ---------- ---------- ---------
SCOTT                          EMP_KOPIE
EMPNO                                       1              0                 0
          0          0          0 23-MAY-13

SCOTT                          EMP_KOPIE
DEPTNO                                      1              1                 0
          0          0          0 23-MAY-13

SCOTT                          DEPT_KOPIE
DEPTNO                                      1              1                 0
          0          0          0 23-MAY-13

• MOS Note [ID 252597.1]: Relationship between Table Monitoring and STATISTICS_LEVEL Parameter
• MOS Note [ID 400214.1]: Column usage in Multi Column Index
• Indizes in der Datenbank: Monitoring und Komprimierung (Index Key Compression)

Zurück zur Community-Seite