oracle managements packs used by DB

http://kerryosborne.oracle-guy.com/2008/10/oracle-management-packs/

select version, name, detected_usages, currently_used, first_usage_date, last_usage_date from DBA_FEATURE_USAGE_STATISTICS where detected_usages > 0 order by 1, 2;

select * from DBA_HIGH_WATER_MARK_STATISTICS;

select * from V$PARAMETER WHERE NAME LIKE '%control_management_pack_access%';

Temp_tablespace_usage_by_sort

select distinct
c.username "user",
c.osuser ,
c.sid,
c.serial#,
b.spid "unix_pid",
c.machine,
c.program "program",
a.blocks * e.block_size/1024/1024 mb_temp_used  ,
a.tablespace,
d.sql_text
from
v$sort_usage a,
v$process b,
v$session c,
v$sqlarea d,
dba_tablespaces e
where c.saddr=a.session_addr
and b.addr=c.paddr
and c.sql_address=d.address(+)
and a.tablespace = e.tablespace_name;

Query to find out temp tablespace usage

SELECT
   A.tablespace_name tablespace,
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM
   v$sort_segment A,
(
SELECT
   B.name,
   C.block_size,
   SUM (C.bytes) / 1024 / 1024 mb_total
FROM
   v$tablespace B,
   v$tempfile C
WHERE
   B.ts#= C.ts#
GROUP BY
   B.name,
   C.block_size
) D
WHERE
   A.tablespace_name = D.name
GROUP by
   A.tablespace_name,
   D.mb_total

Query to find out missing FK indexes

select table_name, constraint_name,
        cname1 || nvl2(cname2,','||cname2,null) ||
        nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
        nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
        nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
               altercolumns
    from ( select b.table_name,
                   b.constraint_name,
                   max(decode( position, 1, column_name, null )) cname1,
                   max(decode( position, 2, column_name, null )) cname2,
                   max(decode( position, 3, column_name, null )) cname3,
                   max(decode( position, 4, column_name, null )) cname4,
                  max(decode( position, 5, column_name, null )) cname5,
                   max(decode( position, 6, column_name, null )) cname6,
                   max(decode( position, 7, column_name, null )) cname7,
                   max(decode( position, 8, column_name, null )) cname8,
                   count(*) col_cnt
              from (select substr(table_name,1,30) table_name,
                           substr(constraint_name,1,30) constraint_name,
                           substr(column_name,1,30) column_name,
                          position
                      from user_cons_columns ) a,
                   user_constraints b
            where a.constraint_name = b.constraint_name
              and b.constraint_type = 'R'
              and b.table_name='DFUVIEW'
            group by b.table_name, b.constraint_name
         ) cons
   where col_cnt > ALL
           ( select count(*)
               from user_ind_columns i
              where i.table_name = cons.table_name
                and i.column_name in (cname1, cname2, cname3, cname4,
                                      cname5, cname6, cname7, cname8 )
                and i.column_position <= cons.col_cnt
              group by i.index_name
            )

Table or index Fragmentation

https://blog.yannickjaquier.com/oracle/candidates-index-rebuild-shrink.html

https://carlos-sierra.net/2017/07/12/script-to-identify-index-rebuild-candidates-on-12c/

TABLE FRAGMENTATION:

--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 1MB

VAR 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;

 

DECLARE

l_used_bytes NUMBER;

l_alloc_bytes NUMBER;

l_percent NUMBER;

BEGIN

DBMS_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)) ddl

FROM dba_ind_statistics s, dba_indexes x, dba_users u, v$parameter p

WHERE u.oracle_maintained = 'N'

AND x.owner = u.username

AND 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.owner

AND s.index_name = x.index_name

GROUP BY

x.owner, x.index_name, p.value

HAVING

SUM(s.leaf_blocks) * TO_NUMBER(p.value) > :minimum_size_mb * POWER(2,20)

ORDER BY

index_size DESC)

LOOP

DBMS_SPACE.CREATE_INDEX_COST(i.ddl,l_used_bytes,l_alloc_bytes);

IF i.index_size * (100 - :savings_percent) / 100 > l_alloc_bytes THEN

l_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;