Oracle - Tables/Indexes
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.[@more@]
select OWNER, TABLE_NAME, CACHE from dba_tables where OWNER not in ('SYS','SYSTEM') and CACHE like '%Y' order by OWNER,TABLE_NAME
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4082/viewspace-1003487/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4082/viewspace-1003487/