DOAG Regio 2013 -1 · Hinweis: Im Metalink Dokument (Package for disabling AWR without a Diagnostic...

DOAG Regio 2013 -1

DOAG Regio 2013 -2

DOAG Regio 2013 -3

DOAG Regio 2013 -4

DOAG Regio 2013 -5

DOAG Regio 2013 -6

DOAG Regio 2013 -7

Hinweis:

Im Metalink Dokument (Package for disabling AWR without a Diagnostic Pack

license in Oracle (Doc ID 436386.1) wird erklärt, dass für den Segment und Undo-

Advisor kein Diagnostic Pack Voraussetzung ist.

Stichwörter: ADDM, Automatic Database Diagnostic Monitor

DOAG Regio 2013 -8

DOAG Regio 2013 -9

DOAG Regio 2013 -10

DOAG Regio 2013 -11

DOAG Regio 2013 -12

DOAG Regio 2013 -13

DOAG Regio 2013 -14

DOAG Regio 2013 -15

DOAG Regio 2013 -16

DOAG Regio 2013 -17

DOAG Regio 2013 -18

Top 50 Index Vorschläge: SELECT * FROM (

SELECT tc.owner||'.'||o.object_name as

segment_name,tc.column_name,

round(s.bytes/1024/1024) Size_M,

cu.equality_preds "=",

cu.equijoin_preds "E-JOIN",cu.nonequijoin_preds "NE-

JOIN",cu.range_preds range,cu.like_preds "LIKE",

cu.null_preds "NULL",

cu.equality_preds+cu.equijoin_preds+cu.nonequijoin_preds+cu.r

ange_preds+cu.like_preds+cu.null_preds as summe

FROM sys.col_usage$ cu, dba_objects o,

dba_tab_columns tc, dba_ind_columns ic, dba_segments s

WHERE o.object_id=cu.obj#

AND o.object_name=tc.table_name

AND o.owner=tc.owner

AND cu.equality_preds>0

AND cu.intcol#= tc.column_id

AND tc.owner=ic.table_owner(+)

AND tc.table_name=ic.table_name(+)

AND tc.column_name=ic.column_name(+)

AND ic.index_name is NULL

AND o.owner=s.owner(+)

AND o.object_name=s.segment_name(+)

AND o.owner ='SCOTT'

ORDER BY 10 desc,1,2)

WHERE rownum <50;

DOAG Regio 2013 -19

DOAG Regio 2013 -20

DOAG Regio 2013 -21

DOAG Regio 2013 -22

Der Fragmentierungsgrad einer Tabelle kann auch von folgenden Kriterien

abhängen :

• PCTFREE

• Verwaltungsgröße des Tablespace

• Zeilenlänge (insbesondere, wenn sie die Blockgröße übersteigt)

Stichwörter: Fragmentierung von Tabellen

DOAG Regio 2013 -23

DOAG Regio 2013 -24

Hinweis: Index zu groß? SELECT owner,

segment_name,

bytes/1024/1024

FROM dba_segments

WHERE segment_type='INDEX'

AND owner NOT IN ('SYS','SYSTEM')

ORDER BY 3 DESC;

Welcher Index wird nicht benutzt ? SELECT *

FROM dba_objects

WHERE object_id IN

(SELECT i.obj#

FROM sys.icol$ i,

sys.col_usage$ c

WHERE i.bo# =c.obj#(+)

AND i.col# =c.intcol#(+)

AND c.obj# IS NULL )

AND owner NOT IN ('SYS','SYSTEM','OUTLN','WMSYS','XDB','EXFSYS','SYSMAN','HR','OLAPSYS',

'MDSYS','FLOWS_030000','OE','IX','DBSNMP','PERFSTAT','CTXSYS','ORDDATA','ORDSYS','APEX_0302

00');

REM Welche Indizes haben Spalten mehrfach indiziert?

SELECT index_owner,index_name,table_owner,table_name,column_name,column_position,descend

FROM dba_ind_columns

WHERE (table_owner||table_name||column_name) IN

(SELECT table_owner||table_name||column_name

FROM dba_ind_columns

WHERE table_owner IN ('SCOTT')

GROUP BY table_owner,

table_name,

column_name

HAVING COUNT(*)>1

);

DOAG Regio 2013 -25

DOAG Regio 2013 -26

DOAG Regio 2013 -27

DOAG Regio 2013 -28

DOAG Regio 2013 -29