--Query to find out fragementation
select owner,table_name,round((blocks*16),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*16),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*16),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*16),2))*100 -10 "reclaimable space % " from dba_tables where table_name ='DEPDMDSTATIC' AND OWNER LIKE 'SCPOMGR';
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => 'PLANORDER',
segment_type => 'TABLE',
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
-- partition_name => 'P_-106'
);
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
end;
/
INDEX FRAGMENTATION:
We need to analyze the index fragmentation post migration
Please use the below script to do so. After completion of the PLSQL block check the dl_lf_rows and based on the high values we can select candidates for doing rebuild.
CREATE TABLE index_frag
(
exec_date DATE,
index_name VARCHAR2 (30),
blocks NUMBER,
lf_blks NUMBER,
del_lf_rows NUMBER
);
DECLARE
BEGIN
FOR c_rec IN (SELECT index_name
FROM user_indexes
WHERE table_name NOT IN (SELECT TABLE_NAME
FROM user_tables
WHERE IOT_TYPE IS NOT NULL))
LOOP
BEGIN
EXECUTE IMMEDIATE
'ANALYZE INDEX ' || c_rec.index_name || 'VALIDATE STRUCTURE';
INSERT INTO index_frag
SELECT SYSDATE,
c_rec.index_name,
blocks,
lf_blks,
del_lf_rows
FROM index_stats;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Issue with Index : ' || c_rec.index_name);
CONTINUE;
END;
END LOOP;
END;
/
set echo off
set termout off
set verify off
set trimspool on
set feedback off
set heading off
set lines 300
set pages 0
set serverout on
spool analyze_User1_indexes.tmp
select 'exec DBMS_STATS.UNLOCK_TABLE_STATS ('''|| user ||''','''|| table_name ||''');' from user_tables order by table_name asc;
begin
for x in ( select index_name from user_indexes where index_type = 'NORMAL')
loop
dbms_output.put_line('ANALYZE INDEX ' || x.index_name || ' COMPUTE STATISTICS;');
dbms_output.put_line('ANALYZE INDEX ' || x.index_name || ' VALIDATE STRUCTURE;');
dbms_output.put_line('select name, height, lf_rows, del_lf_rows, round((del_lf_rows/lf_rows)*100,2) as ratio from index_stats where (lf_rows > 100 and del_lf_rows
> 0)
and (height > 3 or ((del_lf_rows/lf_rows)*100) > 20);');
end loop;
end;
/
select 'exec DBMS_STATS.LOCK_TABLE_STATS ('''|| user ||''','''|| table_name ||''');' from user_tables order by table_name asc;
spool off
column name format a40
spool FPATH/analyze_User1_index_report.txt
PROMPT NAME | HEIGHT | LF_ROWS | DEL_LF_ROWS | RATIO (del_lf_rows/lf_rows) %
@@$FPATH/analyze_User1_indexes.tmp
spool off
create or replace
procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
for x in ( select tablespace_name
from dba_tablespaces
where tablespace_name = ( select tablespace_name
from dba_segments
where segment_type = p_type
and segment_name = p_segname
and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' )
)
loop
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
freelist_group_id => 0,
free_blks => l_free_blks );
end loop;
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
---- File name: indexes_2b_shrunk.sql---- Purpose: List of candidate indexes to be shrunk (rebuild online)---- Author: Carlos Sierra---- Version: 2017/07/12---- Usage: Execute on PDB---- Example: @indexes_2b_shrunk.sql---- Notes: Execute connected into a PDB.-- Consider then:-- ALTER INDEX [schema.]index REBUILD ONLINE;------------------------------------------------------------------------------------------- select only those indexes with an estimated space saving percent greater than 25%VAR savings_percent NUMBER;EXEC :savings_percent := 25;-- select only indexes with current size (as per cbo stats) greater then 1MBVAR minimum_size_mb NUMBER;EXEC :minimum_size_mb := 1;SET SERVEROUT ON ECHO OFF FEED OFF VER OFF TAB OFF LINES 300;COL report_date NEW_V report_date;SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS') report_date FROM DUAL;SPO /tmp/indexes_2b_shrunk_&&report_date..txt;DECLAREl_used_bytes NUMBER;l_alloc_bytes NUMBER;l_percent NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('PDB: '||SYS_CONTEXT('USERENV', 'CON_NAME'));DBMS_OUTPUT.PUT_LINE('---');DBMS_OUTPUT.PUT_LINE(RPAD('OWNER.INDEX_NAME', 35)||' '||LPAD('SAVING %', 10)||' '||LPAD('CURRENT SIZE', 20)||' '||LPAD('ESTIMATED SIZE', 20));DBMS_OUTPUT.PUT_LINE(RPAD('-', 35, '-')||' '||LPAD('-', 10, '-')||' '||LPAD('-', 20, '-')||' '||LPAD('-', 20, '-'));FOR i IN (SELECT x.owner, x.index_name, SUM(s.leaf_blocks) * TO_NUMBER(p.value) index_size,REPLACE(DBMS_METADATA.GET_DDL('INDEX',x.index_name,x.owner),CHR(10),CHR(32)) ddlFROM dba_ind_statistics s, dba_indexes x, dba_users u, v$parameter pWHERE u.oracle_maintained = 'N'AND x.owner = u.usernameAND x.tablespace_name NOT IN ('SYSTEM','SYSAUX')AND x.index_type LIKE '%NORMAL%'AND x.table_type = 'TABLE'AND x.status = 'VALID'AND x.temporary = 'N'AND x.dropped = 'NO'AND x.visibility = 'VISIBLE'AND x.segment_created = 'YES'AND x.orphaned_entries = 'NO'AND p.name = 'db_block_size'AND s.owner = x.ownerAND s.index_name = x.index_nameGROUP BYx.owner, x.index_name, p.valueHAVINGSUM(s.leaf_blocks) * TO_NUMBER(p.value) > :minimum_size_mb * POWER(2,20)ORDER BYindex_size DESC)LOOPDBMS_SPACE.CREATE_INDEX_COST(i.ddl,l_used_bytes,l_alloc_bytes);IF i.index_size * (100 - :savings_percent) / 100 > l_alloc_bytes THENl_percent := 100 * (i.index_size - l_alloc_bytes) / i.index_size;DBMS_OUTPUT.PUT_LINE(RPAD(i.owner||'.'||i.index_name, 35)||' '||LPAD(TO_CHAR(ROUND(l_percent, 1), '990.0')||' % ', 10)||' '||LPAD(TO_CHAR(ROUND(i.index_size / POWER(2,20), 1), '999,999,990.0')||' MB', 20)||' '||LPAD(TO_CHAR(ROUND(l_alloc_bytes / POWER(2,20), 1), '999,999,990.0')||' MB', 20));END IF;END LOOP;END;/SPO OFF;
