Oracle - Tables/Indexes

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.

select 	OWNER,
	TABLE_NAME,
	CACHE
from dba_tables
where OWNER not in ('SYS','SYSTEM')
and CACHE like '%Y'
order by OWNER,TABLE_NAME

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4082/viewspace-1003487/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4082/viewspace-1003487/