CHECK THE DEGREE OF PARALLEL INDEXES

FOLLOWING SCRIPT WILL SHOW YOU THE MISMATCH DEGREE OF PARALLELISM

REPORT MISMATCH INDEXES.

==========================

SET PAGESIZE 1000

col table_name format a35

col index_name format a35

Rem Tables that have Indexes with not the same DOP

Rem !!!!! This command can take some time to execute !!!

Rem ---------------------------------------------------;

set lines 150

select substr(t.owner,1,15) Owner ,

t.table_name ,

substr(trim(t.degree),1,7) Degree ,

substr(trim(t.instances),1,9) Instances,

i.index_name ,

substr(trim(i.degree),1,7) Degree ,

substr(trim(i.instances),1,9) Instances

from all_indexes i,

all_tables t

where ( trim(i.degree) != trim(t.degree) or

trim(i.instances) != trim(t.instances) ) and

i.owner = t.owner and

i.table_name = t.table_name

and i.owner<>'SYS';

FOLLOWING SCRIPT WILL GENERATE THE SCRIPT TO RUN THE SCRIPT OF MIS-PARALLEL SCRIPT.  

AFTER RUN THE SCRIPT IT WILL SHOW YOU SOME COMMANDS AND RUN IT MANUALLY.

GENERATE ALTER STATMENTS

===========================

SET SERVEROUT ON

SET PAGESIZE 1000

SET LONG 10000

BEGIN

FOR I_CURS IN (

select substr(t.owner,1,15) Owner ,

t.table_name table_name,

substr(trim(t.degree),1,7) T_Degree ,

substr(trim(t.instances),1,9) T_Instances,

i.index_name INDEX_NAME,

substr(trim(i.degree),1,7) I_Degree ,

substr(trim(i.instances),1,9) I_Instances

from all_indexes i,

all_tables t

where ( trim(i.degree) != trim(t.degree) or

trim(i.instances) != trim(t.instances) ) and

i.owner = t.owner and

i.table_name = t.table_name

and i.owner<>'SYS')

LOOP

IF

I_CURS.T_DEGREE<>I_CURS.I_DEGREE

AND I_CURS.T_DEGREE<>'DEFAULT'

then

DBMS_OUTPUT.put_line ( 'alter INDEX '||I_CURS.OWNER||'.'||I_CURS.INDEX_name||' PARALLEL '||I_CURS.T_DEGREE||';' );

ELSE IF

I_CURS.T_DEGREE<>I_CURS.I_DEGREE

AND I_CURS.T_DEGREE='DEFAULT'

then

DBMS_OUTPUT.put_line ( 'alter INDEX '||I_CURS.OWNER||'.'||I_CURS.INDEX_name||' PARALLEL ;' );

Else

NULL;

END IF;

END IF;

END LOOP;

END;

/