Wednesday, October 15, 2014

Oracle PL SQL - Tables/Indexes


Tabs w/ Questionable Inds
TABLES WITH QUESTIONABLE INDEX(ES) NOTES:
?  Owner - Owner of the table
?  Table Name - Name of the table
?  Column - Name of the column in question
?  The above query shows all tables that have more than one index with the same leading column. These indexes can cause queries to use an inappropriate indexes; in other words, Oracle will use the index that was created most recently if two indexes are of equal ranking. This can cause different indexes to be used from one environment to the next (e.g., from DEV to TEST to PROD).
?  The information does not automatically indicate that an index is incorrect; however, you may need to justify the existence of each of the indexes above.
select  
TABLE_OWNER,
        TABLE_NAME,
        COLUMN_NAME
from  dba_ind_columns
where COLUMN_POSITION=1
and      TABLE_OWNER not in ('SYS','SYSTEM')
group by TABLE_OWNER, TABLE_NAME, COLUMN_NAME
having  count(*) > 1
       
Tabs With More Than 5 Inds
TABLES WITH MORE THAN 5 INDEXES NOTES:
?  Owner - Owner of the table
?  Table Name - Name of the table
?  Index Count - Number of indexes
select   OWNER,
        TABLE_NAME,
        COUNT(*) index_count
from     dba_indexes
where    OWNER not in ('SYS','SYSTEM')
group    by OWNER, TABLE_NAME
having  COUNT(*) > 5
order    by COUNT(*) desc, OWNER, TABLE_NAME
Tables With No Indexes
TABLES WITHOUT INDEXES NOTES:
?  Owner - Owner of the table
?  Table Name - Name of the table
select   OWNER,
        TABLE_NAME
from
(
select   OWNER,
        TABLE_NAME
from     dba_tables
minus
select   TABLE_OWNER,
        TABLE_NAME
from     dba_indexes
)
orasnap_noindex
where   OWNER not in ('SYS','SYSTEM')
order    by OWNER,TABLE_NAME
Tables With No PK
NO PRIMARY KEY NOTES:
?  Table Owner - Owner of the table
?  Table Name - Name of the table
select  OWNER,
        TABLE_NAME
from    dba_tables dt
where   not exists (
        select  'TRUE'
        from    dba_constraints dc
        where   dc.TABLE_NAME = dt.TABLE_NAME
        and     dc.CONSTRAINT_TYPE='P')
and      OWNER not in ('SYS','SYSTEM')
order   by OWNER, TABLE_NAME
Disabled Constraints
DISABLED CONSTRAINT NOTES:
?  Owner - Owner of the table
?  Table Name - Name of the table
?  Constraint Name - Name of the constraint
?  Constraint Type - Type of constraint
?  Status - Current status of the constraint
select  OWNER,
        TABLE_NAME,
        CONSTRAINT_NAME,
        decode(CONSTRAINT_TYPE, 'C','Check',
                                'P','Primary Key',
                                'U','Unique',
                                'R','Foreign Key',
                                'V','With Check Option') type,
        STATUS
from     dba_constraints
where    STATUS = 'DISABLED'
order    by OWNER, TABLE_NAME, CONSTRAINT_NAME
FK Constraints
FOREIGN KEY CONSTRAINTS NOTES:
?  Table Owner - Owner of the table
?  Table Name - Name of the table
?  Constraint Name - Name of the constraint
?  Column Name - Name of the column
?  Referenced Table - Name of the referenced table
?  Reference Column - Name of the referenced column
?  Position - Position of the column
select   c.OWNER,
        c.TABLE_NAME,
        c.CONSTRAINT_NAME,
        cc.COLUMN_NAME,
        r.TABLE_NAME,
        rc.COLUMN_NAME,
        cc.POSITION
from     dba_constraints c,
        dba_constraints r,
        dba_cons_columns cc,
        dba_cons_columns rc
where    c.CONSTRAINT_TYPE = 'R'
and      c.OWNER not in ('SYS','SYSTEM')
and      c.R_OWNER = r.OWNER
and      c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
and      c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and      c.OWNER = cc.OWNER
and      r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and      r.OWNER = rc.OWNER
and      cc.POSITION = rc.POSITION
order    by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION
FK Index Problems
FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE NOTES:
?  Owner - Owner of the table
?  Constraint Name - Name of the constraint
?  Column Name - Name of the column
?  Position - Position of the index
?  Problem - Nature of the problem
?  It is highly recommended that an index be created if the Foreign Key column is used in joining, or often used in a WHERE clause. Otherwise a table level lock will be placed on the parent table.
select   acc.OWNER,
        acc.CONSTRAINT_NAME,
        acc.COLUMN_NAME,
        acc.POSITION,
        'No Index' Problem
from     dba_cons_columns acc,
        dba_constraints ac
where    ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
and      ac.CONSTRAINT_TYPE = 'R'
and     acc.OWNER not in ('SYS','SYSTEM')
and     not exists (
        select  'TRUE'
        from    dba_ind_columns b
        where   b.TABLE_OWNER = acc.OWNER
        and     b.TABLE_NAME = acc.TABLE_NAME
        and     b.COLUMN_NAME = acc.COLUMN_NAME
        and     b.COLUMN_POSITION = acc.POSITION)
order   by acc.OWNER, acc.CONSTRAINT_NAME, acc.COLUMN_NAME, acc.POSITION
Inconsistent Column Names
INCONSISTENT COLUMN DATATYPE NOTES:
?  Owner - Owner of the table
?  Column - Name of the column
?  Table Name - Name of the table
?  Datatype - Datatype of the column
select   OWNER,
        COLUMN_NAME,
        TABLE_NAME,
        decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH) datatype
from     dba_tab_columns
where    (COLUMN_NAME, OWNER) in
                (select COLUMN_NAME,
                        OWNER
                  from     dba_tab_columns
                  group   by COLUMN_NAME, OWNER
                  having  min(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) <
                         max(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) )
and      OWNER not in ('SYS', 'SYSTEM')
order   by COLUMN_NAME,DATA_TYPE
Object Extent Warning
TABLES THAT CANNOT EXTEND NOTES:
?  Owner - Owner of the object
?  Object Name - Name of the object
?  Object Type - Type of object
?  Tablespace - Name of the tablespace
?  Next Extent - Size of next extent (bytes)
select   OWNER,
        SEGMENT_NAME,
        SEGMENT_TYPE,
        TABLESPACE_NAME,
        NEXT_EXTENT
from (
        select   seg.OWNER,
                seg.SEGMENT_NAME,
                        seg.SEGMENT_TYPE,
                seg.TABLESPACE_NAME,
                        t.NEXT_EXTENT
        from     dba_segments seg,
                        dba_tables t
        where    (seg.SEGMENT_TYPE = 'TABLE'
        and       seg.SEGMENT_NAME = t.TABLE_NAME
        and       seg.owner = t.OWNER
        and    NOT EXISTS (
                        select   TABLESPACE_NAME
                                from     dba_free_space free
                                where    free.TABLESPACE_NAME = t.TABLESPACE_NAME
                                and      BYTES >= t.NEXT_EXTENT))
        union
        select   seg.OWNER,
                seg.SEGMENT_NAME,
                        seg.SEGMENT_TYPE,
                seg.TABLESPACE_NAME,
                        c.NEXT_EXTENT
        from     dba_segments seg,
                        dba_clusters c
        where    (seg.SEGMENT_TYPE = 'CLUSTER'
        and       seg.SEGMENT_NAME = c.CLUSTER_NAME
        and       seg.OWNER = c.OWNER
        and      NOT EXISTS (
                        select   TABLESPACE_NAME
                        from     dba_free_space free
                        where    free.TABLESPACE_NAME = c.TABLESPACE_NAME
                        and      BYTES >= c.NEXT_EXTENT))
        union
        select   seg.OWNER,
                seg.SEGMENT_NAME,
                        seg.SEGMENT_TYPE,
                seg.TABLESPACE_NAME,
                        i.NEXT_EXTENT
        from     dba_segments seg,
                        dba_indexes  i
        where    (seg.SEGMENT_TYPE = 'INDEX'
        and       seg.SEGMENT_NAME = i.INDEX_NAME
        and       seg.OWNER        = i.OWNER
        and       NOT EXISTS (
                        select   TABLESPACE_NAME
                                        from     dba_free_space free
                                        where    free.TABLESPACE_NAME = i.TABLESPACE_NAME
                        and      BYTES >= i.NEXT_EXTENT))
        union
        select   seg.OWNER,
                seg.SEGMENT_NAME,
                        seg.SEGMENT_TYPE,
                seg.TABLESPACE_NAME,
                        r.NEXT_EXTENT
        from     dba_segments seg,
                        dba_rollback_segs r
        where    (seg.SEGMENT_TYPE = 'ROLLBACK'
        and       seg.SEGMENT_NAME = r.SEGMENT_NAME
        and       seg.OWNER        = r.OWNER
        and       NOT EXISTS (
                        select  TABLESPACE_NAME
                                        from     dba_free_space free
                                        where    free.TABLESPACE_NAME = r.TABLESPACE_NAME
                and      BYTES >= r.NEXT_EXTENT))
)
orasnap_objext_warn
order    by OWNER,SEGMENT_NAME
Segment Fragmentation
OBJECTS WITH MORE THAN 50% OF MAXEXTENTS NOTES:
?  Owner - Owner of the object
?  Tablespace Name - Name of the tablespace
?  Segment Name - Name of the segment
?  Segment Type - Type of segment
?  Size - Size of the object (bytes)
?  Extents - Current number of extents
?  Max Extents - Maximum extents for the segment
?  Percentage - Percentage of extents in use
?  As of v7.3.4, you can set MAXEXTENTS=UNLIMITED to avoid ORA-01631: max # extents (%s) reached in table $s.%s.
?  To calculate the MAXEXTENTS value on versions < 7.3.4 use the following equation: DBBLOCKSIZE / 16 - 7
?  Here are the MAXEXTENTS for common blocksizes: 1K=57, 2K=121, 4K=249, 8K=505, and 16K=1017
?  Multiple extents in and of themselves aren't bad. However, if you also have chained rows, this can hurt performance.
select   OWNER,
        TABLESPACE_NAME,
        SEGMENT_NAME,
        SEGMENT_TYPE,
        BYTES,
        EXTENTS,
        MAX_EXTENTS,
        (EXTENTS/MAX_EXTENTS)*100 percentage
from     dba_segments
where    SEGMENT_TYPE in ('TABLE','INDEX')
and      EXTENTS > MAX_EXTENTS/2
order    by (EXTENTS/MAX_EXTENTS) desc
Extents reaching maximum
TABLES AND EXTENTS WITHIN 3 EXTENTS OF MAXIMUM :
?  Owner - Owner of the segment
?  Segment Name - Name of the segment
select owner "Owner",
       segment_name "Segment Name",
       segment_type "Type",
       tablespace_name "Tablespace",
       extents "Ext",
       max_extents "Max"
from dba_segments
where ((max_extents - extents) <= 3)
and owner not in ('SYS','SYSTEM')
order by owner, segment_name
Analyzed Tables
ANALYZED TABLE NOTES:
?  Owner - Owner of the table
?  Analyzed - Number of analyzed tables
?  Not Analyzed - Number of tables that have not be analyzed
?  Total - Total number of tables owned by user
?  The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE operation was used. It is best to compute your statistics.
?  A COMPUTE will cause a table-level lock to be placed on the table during the operation.
select  OWNER,
        sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
        sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
        count(TABLE_NAME) total
from     dba_tables
where    OWNER not in ('SYS', 'SYSTEM')
group    by OWNER
Recently Analyzed Tables
LAST ANALYZED TABLE NOTES:
?  Owner - Owner of the table
?  Table Name - Name of the table
?  Last Analyzed - Last analyzed date/time
select   OWNER,
        TABLE_NAME,
        to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') last_analyzed
from     dba_tab_columns
where    OWNER not in ('SYS','SYSTEM')
and      LAST_ANALYZED is not null
and     COLUMN_ID=1
and      (SYSDATE-LAST_ANALYZED) < 30
order   by (SYSDATE-LAST_ANALYZED)
Cached Tables
CACHED TABLE NOTES:
?  Owner - Owner of the table
?  Table Name - Name of the table
?  Cache - Cached?
?  Oracle 7.1+ provides a mechanism for caching table in the buffer cache. Caching tables will speed up data access and improve performance by finding the data in memory and avoiding disk reads.
select   OWNER,
        TABLE_NAME,
        CACHE
from dba_tables
where OWNER not in ('SYS','SYSTEM')
and CACHE like '%Y'
order by OWNER,TABLE_NAME

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect