Segment Shrink for fragmented tables

we need to do this segment shrinks in an environment where you purge the data consistantly...

create the table as a one time process...

create table OBJECTS_TO_SHRINK
 (owner varchar2(30), table_name varchar2(30), tablespace_name varchar2(30), actual_mb varchar2(30), optimal_mb varchar2(30), claimable_mb varchar2(30));

script the below code to do segment shrink for the fragmented tables..

select * from OBJECTS_TO_SHRINK;
delete from OBJECTS_TO_SHRINK;
commit;

 declare
sql_stmt   VARCHAR2(1000);
sql_stmt_2 VARCHAR2(1000);
begin
sql_stmt := ' insert into OBJECTS_TO_SHRINK (owner,table_name,tablespace_name,actual_mb,optimal_mb,claimable_mb)
 select t.owner,t.table_name, s.tablespace_name,
round(s.bytes/1024/1024) actual_MB,
round((t.num_rows+1) * t.avg_row_len/1024/1024) optimal_MB,
round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) CLAIMABLE_MB
from dba_tables t, dba_segments s
where t.owner = s.owner and s.owner=''USER_SCHEMA''
and  t.table_name = s.segment_name
---and t.table_name = ''ABC''
and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) > 200
and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) < 1500
order by CLAIMABLE_MB desc';
execute immediate sql_stmt;
commit;

FOR indd IN
  (select owner,table_name from OBJECTS_TO_SHRINK)
  LOOP
execute immediate  'alter table  '||indd.owner||'.'||indd.table_name||' enable row movement';
execute immediate 'alter table  '||indd.owner||'.'||indd.table_name||' shrink space compact';
execute immediate  'alter table  '||indd.owner||'.'||indd.table_name||' shrink space';
execute immediate  'alter table  '||indd.owner||'.'||indd.table_name||' disable row movement';
execute immediate  'analyze table '||indd.owner||'.'||indd.table_name||' compute statistics';
commit;
end loop;
end;
/

once, the above is completed. do the datafile resizes which should reduce your space....


NOTE:- if you have any functional-based-indexes defined for that table then we cannot shrink that table directly. we need to remove them by saving their sql/script to recreate-again. then do this process.. below is the error you will see if you have any functional-based-indexes.

ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object


Hope this helps....

how to get Lun Id's for the ASM raw devices for EMC and Hitachi... AIX

Below information will be handy when you want to know the lun id'd for the raw devices you are using in the ASM environment ..there is a small difference in syntax ....when you use EMC's or Hitachi as your Storage..... the script only works if the server is NPIV connected..

set heading off

set feedback off

set echo off

set pagesize 0

col NAME for a30

col PATH for a30

spool asm_info.log

select NAME,PATH from V$ASM_DISK;

spool off

EMC......

cat asm_info.log| awk '{print $2}'|cut -c7- > asm_info_cut.log

cat asm_info_cut.log|while read disk

do

echo "$disk          `lscfg -vps -l $disk|grep LIC|cut -c37,38,39,40,41`" >> asm_disk_lun_info.log

done

paste asm_info.log asm_disk_lun_info.log| awk '

BEGIN { format = "%-29s %-28s %-28s %s\n"

printf format, "ASM_groups", "ASM_Disk_PATH", "UNIX_Disk", "LUN/Device_id"

printf format, "----------", "-------------", "---------", "-------------"}

{for(i=1;i<=NF;i++)

printf("%-30s%c", $i, (i==NF) ? ORS : "")}' >> asm_info_final.log;

exit;

Hitachi.....

cat asm_info.log| awk '{print $2}'|cut -c7- > asm_info_cut.log

cat asm_info_cut.log|while read disk

do

echo "$disk          `lscfg -vps -l $disk|grep Z1|cut -c37-| cut -c-5`" >> asm_disk_lun_info.log

done

paste asm_info.log asm_disk_lun_info.log| awk '

BEGIN { format = "%-29s %-28s %-28s %s\n"

printf format, "ASM_groups", "ASM_Disk_PATH", "UNIX_Disk", "LUN/Device_id"

printf format, "----------", "-------------", "---------", "-------------"}

{for(i=1;i<=NF;i++)

printf("%-30s%c", $i, (i==NF) ? ORS : "")}' >> asm_info_final.log;

exit;

Hope this helps....

Setting up Database Resource manager.

Below are the steps to setup the DRM ( Database Resource Manager)....

here we are creating a basic CPU based Plan....

SQL> show parameter resource;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

resource_limit                       boolean     FALSE

resource_manager_cpu_allocation      integer     8

resource_manager_plan                string                           <<<<   No Plan exists

step 1:-  create a plan that you want to name...

begin

 dbms_resource_manager.create_plan(

  plan => 'CPU_LIMIT_TEST_PLAN',

  comment => 'Resource plan/method for Single level sample');

end;

/

step 2:-  create a group to which the particular users will be assigned...

begin

 dbms_resource_manager.create_consumer_group(

  consumer_group => 'ASSIGNED_GROUP',

  comment => 'Resource consumer group/method for online users sessions');

end;

/

step 3:- In this step we will be creating sub plan or assigning cpu resources to the group that we created in step 2 along with the other users( which fall in OTHER groups)... here we are alloting 20% of CPU resources to the ASSIGNED_GROUP and 80% to OTHER_GROUPS in the database.

begin

 dbms_resource_manager.create_plan_directive(

   plan => 'CPU_LIMIT_TEST_PLAN',

   group_or_subplan => 'ASSIGNED_GROUP',

   comment => 'Online day users sessions at level 1',

   cpu_p1 => 20,

   parallel_degree_limit_p1 => 0);

 dbms_resource_manager.create_plan_directive(

   plan => 'CPU_LIMIT_TEST_PLAN',

   group_or_subplan => 'OTHER_GROUPS',

   comment => 'OTHER_GROUPS day users sessions at level 1',

   cpu_p1 => 80,

   parallel_degree_limit_p1 => 0);

end;

/

Step 5:- Validating the Plan

begin

 dbms_resource_manager.validate_pending_area();

end;

/

step 6:- submitting the plan

begin

 dbms_resource_manager.submit_pending_area();

end;

/

once the Setup is completed...... we need to set this plan in the Database..

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'CPU_LIMIT_TEST_PLAN';

SQL> show parameter resource;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

resource_limit                       boolean     FALSE

resource_manager_cpu_allocation      integer     8

resource_manager_plan                string      CPU_LIMIT_TEST_PLAN  

once this is completed. we need to assign the users to the required plans that we have created....

here we are assiging REPORT_USER to the ASSIGNED_GROUP in order to limit the CPU resources to this group.

step 7:- create a pending area where the changes are first made with out effecting the original or existing plan.

begin

 dbms_resource_manager.create_pending_area();

end;

/

step 8:-

begin

 dbms_resource_manager_privs.grant_switch_consumer_group(

   grantee_name => 'REPORT_USER',

   consumer_group => 'ASSIGNED_GROUP',

   grant_option => FALSE);

end;

/

step 9:- Validating the Plan

begin

 dbms_resource_manager.validate_pending_area();

end;

/

step 10:- once this is done we will again submit the plan.

begin

 dbms_resource_manager.submit_pending_area();

end;

/

This completes the DRM setup for your environment...

TO Delete/alter the existing plan and create a new one ...follow these steps:-

step 1:-  create pending area

begin

 dbms_resource_manager.create_pending_area();

end;

/

step 2:-

SQL> EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=>'CPU_LIMIT_TEST_PLAN',group_or_subplan=>'ASSIGNED_GROUP');

PL/SQL procedure successfully completed.

and follow steps above mentioned step up steps......

TO Delete DRM completely from the database:-

step 1:-

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

step 2:-  create pending area

begin

 dbms_resource_manager.create_pending_area();

end;

/

step 3:-

step 9:- Validating the Plan

begin

 dbms_resource_manager.validate_pending_area();

end;

/

step 10:-

begin

 DBMS_RESOURCE_MANAGER.DELETE_PLAN('CPU_LIMIT_TEST_PLAN'

end;

/

step 11:- once this is done we will again submit the plan.

begin

 dbms_resource_manager.submit_pending_area();

end;

/

--- Clearing any Pending Area….

BEGIN
 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/ 

Hope this helps.......

Index rebuild script

This is Simple script to automate the Index rebuild in your environment....
in this script we are capturing parallel degree before rebuilding the indexes and will reset to the existing once the index rebuild is done...



SET FEEDBACK OFF
set serveroutput on size unlimited
set pagesize 4000
set heading off
set linesize 200
DECLARE
 v_Count   INT;
    BEGIN
        SELECT 1
          INTO v_Count
          FROM User_Tables
         WHERE Table_Name = 'INDEX_REBUILD_LIST';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            BEGIN
                EXECUTE IMMEDIATE 'CREATE TABLE INDEX_REBUILD_LIST
                                   (OWNER               VARCHAR2(20),
                                    INDEX_NAME          VARCHAR2(40),
                                    DEGREE              NUMBER)';
 EXCEPTION
                WHEN OTHERS THEN
                    RAISE_APPLICATION_ERROR(-20001, 'ERROR in Creating USER_DROP_TAB table' || CHR(10) || SQLERRM);
            END;
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20002, 'ERROR - ' || CHR(10) || SQLERRM);
    END;
/


INSERT INTO INDEX_REBUILD_LIST select owner,index_name,degree from dba_indexes where owner = 'User_Schema';

select count(*) from INDEX_REBUILD_LIST;

--select * from INDEX_REBUILD_LIST;

Prompt "******Running Index Rebuilds with Parallel 4 ******"
spool exec_index_rebuilds_User_Schema.sql
select 'alter index '||owner||'.'||index_name||' rebuild nologging  parallel 4;' from INDEX_REBUILD_LIST;
spool off
prompt "******Setting Parallel to Original******";

spool exec_index_rebuild_reset_parallel.sql
select 'alter index '||owner||'.'||index_name||'  parallel '||degree||';' from INDEX_REBUILD_LIST;
spool off

drop table INDEX_REBUILD_LIST;

--below steps are to perform the operations
@exec_index_rebuilds_User_schema.sql
@exec_index_rebuild_reset_parallel.sql


exit;



Hope this Helps....

Checking Multiple Hash Values for a given Sql

below script will help you to find out if a given sql is behaving differently at different times..means if a sql is having a single sql_id but multiple hash values then the run times will be different for each time as picks each values... to avoid it we need to find pick a better hash value and then create a sql profile for this to improve the performance.

select * from
table(dbms_xplan.display_awr('banap88bftthv'));


SELECT TO_CHAR(begin_interval_time,'DD-MM-YY HH24:MI') begin_interval_time,
  TO_CHAR(ss.end_interval_time,'DD-MM-YY HH24:MI') end_interval_time,
  sql_id,
  plan_hash_value,
  optimizer_cost,
  executions_delta executions,
  ROUND((elapsed_time_delta)/DECODE((executions_delta*1000000),0,1,(executions_delta*1000000)),5) TIME_PER_EXEC,
  module,
  sorts_total,
  sorts_delta,
  executions_delta,
  executions_total,
  disk_reads_total,
  disk_reads_delta,
  buffer_gets_total,
  elapsed_time_total,
  elapsed_time_delta
FROM dba_hist_snapshot ss,
  dba_hist_sqlstat st
WHERE st.snap_id   =ss.snap_id
AND st.module NOT IN ('emagent_SQL_oracle_database','emagent_AQMetrics')
AND sql_id         ='banap88bftthv'
ORDER BY 1 DESC;

Hope this Helps....

Virtual Indexes

Thought this would be  handy in testing the performance. By creating virtual indexes in a session you can check whether the performance of the bad sql can be improved or not before we implement it permanently.
this is feature is available from 11g.

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

create index vir_idx1 on Table_1 (col_1,col_2,col_3) nosegment;

once the Virtual Index is created run the bad sql and check the plan for the performance boost. if this work then create an index permanently.

Hope this helps.....

Data Search String

Below script will help you to search a specific string of data and list out all the tables and column names which has this data string...

create table FIND_SEARCH_STRING (TABLE_NAME VARCHAR2(35) , COLUMN_NAME VARCHAR2(35),MAX_COUNT NUMBER(30,0), DATA_TYPE VARCHAR2(35 CHAR));

set linesize 200
set pagesize 500
col TABLE_NAME for a30
col COLUMN_NAME for a30
delete from FIND_SEARCH_STRING;
commit;
DECLARE
sql_stmt varchar2(256);
tab_count varchar2(100);
cursor c_f is SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS where TABLE_NAME not like 'FIND_SEARCH_STRING%' and data_type = 'VARCHAR2' and table_name like 'Table%';
  v_a USER_TAB_COLUMNS.TABLE_NAME%type;
  v_b USER_TAB_COLUMNS.COLUMN_NAME%type;
  v_c USER_TAB_COLUMNS.DATA_TYPE%type;

BEGIN
open c_f;
  loop
    fetch c_f into v_a, v_b , v_c;
    exit when c_f%notfound;

sql_stmt :='SELECT count(*) from '|| v_a || ' where '|| v_b ||' like :1';

----change below----
execute immediate sql_stmt into tab_count using 'ABC%';


---    execute immediate 'SELECT max(v_b) into tab_count from v_a where current of c_f';
 insert into FIND_SEARCH_STRING values (v_a,v_b,tab_count,v_c);
commit;
  END LOOP;
END;
/
select * from FIND_SEARCH_STRING where MAX_COUNT > 0;

With Owner:

create table FIND_SEARCH_TEST (OWNER VARCHAR(20) , TABLE_NAME VARCHAR2(35) , COLUMN_NAME VARCHAR2(35),MAX_COUNT NUMBER(30,0), DATA_TYPE VARCHAR2(35 CHAR));

set serveroutput on

set linesize 200

set pagesize 500

col TABLE_NAME for a30

col COLUMN_NAME for a30

delete from FIND_SEARCH_STRING;

drop table FIND_SEARCH_TEST;

commit;

DECLARE

sql_stmt varchar2(256);

tab_count varchar2(100);

cursor c_f is SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS where TABLE_NAME not like 'FIND_SEARCH_TEST%' 

and data_type = 'VARCHAR2' 

and owner like 'WM_%'

and column_name in('USER_NAME','LOGIN_USER_ID');

  v_d DBA_TAB_COLUMNS.OWNER%type;

  v_a DBA_TAB_COLUMNS.TABLE_NAME%type;

  v_b DBA_TAB_COLUMNS.COLUMN_NAME%type;

  v_c DBA_TAB_COLUMNS.DATA_TYPE%type;

  

BEGIN

open c_f;

  loop

    fetch c_f into v_d, v_a, v_b , v_c;

    exit when c_f%notfound;

sql_stmt :='SELECT count(*) from '|| v_d ||'.'|| v_a ||' where '|| v_b ||' like :1';

----change below----

execute immediate sql_stmt into tab_count using '%ABC%';

commit;

DBMS_OUTPUT.PUT_LINE('SELECT count(*) from '|| v_d ||'.'|| v_a ||' where '|| v_b ||' like ''%ABC%''; ');

---    execute immediate 'SELECT max(v_b) into tab_count from v_a where current of c_f';

 insert into FIND_SEARCH_TEST values (v_d,v_a,v_b,tab_count,v_c);

commit;

  END LOOP;

END;

/