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;

/

MOVE LESS THAN 5 GB TABLES AND INDEXES INTO OTHER TABLESPACE AND REBUILD INDEXES

·      Note:- DO NOT RUN ON PRODUCTION WITHOUT TEST THIS ON TEST ENVIRONMENT.

           --> Accept source tablespace name parameter

·        --> Loop on all tables less than 5GB  inside the tablespace.

·        --> Move Each table to target tablespace and rebuild its indexes

·        --> Indexes reside on same source tablespace will be moved to target tablespace.

·        -->  Reset of indexes reside in different tablespaces will be rebuild their in place.

CREATE OR REPLACE PROCEDURE move_smaller_than_5g (v_source_ts in varchar,v_target_ts in varchar)
AS
BEGIN
FOR i
IN (SELECT OWNER ,SEGMENT_NAME
FROM dba_segments
where TABLESPACE_NAME = v_source_ts
and SEGMENT_TYPE='TABLE'
AND BYTES<5368709120
GROUP BY OWNER,SEGMENT_NAME,SEGMENT_TYPE)
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '|| i.owner|| '.'|| i.SEGMENT_NAME|| '  MOVE TABLESPACE '|| v_target_ts;
--DBMS_OUTPUT.put_line ('ALTER TABLE '|| i.owner|| '.'|| i.SEGMENT_NAME|| '  MOVE TABLESPACE '|| v_target_ts);
for ii in(
select A.owner,A.index_name,A.index_type,A.PARTITIONED,A.status,B.CREATED,A.LAST_ANALYZED,A.TABLESPACE_NAME from dba_indexes A ,DBA_OBJECTS B, dba_part_indexes C where A.table_name = i.SEGMENT_NAME
AND A.OWNER=B.OWNER
AND A.INDEX_NAME=B.OBJECT_NAME
Group by a.owner,a.index_name,A.index_type,A.PARTITIONED,A.status,B.CREATED,A.LAST_ANALYZED,A.TABLESPACE_NAME
order by 1,2)
loop
IF II.TABLESPACE_NAME =v_source_ts
THEN
EXECUTE IMMEDIATE 'ALTER INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  rebuild NOLOGGING PARALLEL 12 TABLESPACE '|| v_target_ts;
EXECUTE IMMEDIATE 'ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  LOGGING NOPARALLEL ';
--DBMS_OUTPUT.put_line ('ALTER INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  rebuild NOLOGGING PARALLEL 12 TABLESPACE '|| v_target_ts);
--DBMS_OUTPUT.put_line ('ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  LOGGING NOPARALLEL ');
ELSE
EXECUTE IMMEDIATE 'ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  rebuild NOLOGGING PARALLEL 12';
EXECUTE IMMEDIATE 'ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  LOGGING NOPARALLEL ';
--DBMS_OUTPUT.put_line ('ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  rebuild NOLOGGING PARALLEL 12');
--DBMS_OUTPUT.put_line ('ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  LOGGING NOPARALLEL ');
END IF;
END LOOP;
END LOOP;
END;
/

PERFORMANCE IMPROVEMENT with STALE PARTITIONS

First check the list of Stale Partitions and Tables in your Database.

SQL> select TABLE_NAME,PARTITION_NAME,STALE_STATS from dba_tab_statistics where owner=upper('&OWNER') and STALE_STATS='YES';
Enter value for owner:

Enter Your Schema Name.

It will provide you the list of tables and partitions (optional if you have partitions)

after that

create file in /home/oracle/stats_gather.sh

then
#####################Copy the following in your stats_gather.sh file ###################
#!/bin/ksh
set -x

gather_stats()
{
sqlplus -s /nolog > /home/oracle/stat_gather.log <<sqlconn

connect / as sysdba

set timing on

select to_char(sysdate,'DD-MON-YYYY HH:MI:SS') from dual;

THIS IS FOR TABLES

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');


THIS IS FOR PARTITIONS

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');



select to_char(sysdate,'DD-MON-YYYY HH:MI:SS') from dual;


exit
sqlconn
}

ORACLE_HOME=YOUR ORACLE HOME
PATH=$PATH:YOUR ORACLE PATH
ORACLE_SID=YOUR ORACLE SID
export ORACLE_HOME PATH ORACLE_SID
gather_stats

uuencode /home/oracle/stat_gather.log | mailx -m -s "stats gather " youremail@yourdomain.com


NOW SAVE AND EXIT THE FILE

Note how to run this

nohup sh stats_gather.sh &

press enter

press enter

press enter  press enter 3 to 4 times

now verify that its running

tail -f nohup.out

the output should be like following:-

+ ORACLE_HOME=YOUR ORACLE HOME
+ PATH=YOUR ORACLE PATH:/usr/local/bin:/usr/bin:/usr/ccs/bin:/usr/contrib/bin:/usr/contrib/Q4/bin:/opt/perl/bin:/opt/ipf/bin:/opt/gvsd/bin:/opt/nettladm/bin:/opt/fcms/bin:/
opt/wbem/bin:/opt/wbem/sbin:/opt/sas/bin:/opt/graphics/common/bin:/opt/atok/bin:/usr/bin/X11:/usr/contrib/bin/X11:/opt/sec_mgmt/bastille/bin:/opt/caliper/bin:/opt/drd/bin:/opt/dsau/bin:/opt/d
sau/sbin:/opt/resmon/bin:/opt/firefox:/opt/gnome/bin:/opt/ignite/bin:/opt/propplus/bin:/usr/contrib/kwdb/bin:/opt/mozilla:/opt/perl_32/bin:/opt/perl_64/bin:/opt/prm/bin:/opt/sfm/bin:/opt/swm/
bin:/opt/sec_mgmt/spc/bin:/opt/ssh/bin:/opt/sentinel/bin:/opt/swa/bin:/opt/hpsmh/bin:/opt/thunderbird:/opt/langtools/bin:/opt/gwlm/bin:/opt/cfg2html:.:YOUR ORACLE HOME
+ ORACLE_SID=YOUR ORACLE SID