Thursday 15 December 2011

table detail query

Here are some scripts related to 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
    
    

  • 1 comment:

    1. 25% OFF on Oracle Apps R12 Financials Self Paced Course along with 11 Additional Add On Courses (321 Session Videos of 120 Hours Recordings). Our Top Trending Course with 1700 Enrolled Udemy Students

      Please Check https://www.oracleappstechnical.com for details

      ReplyDelete