Restarting a patch after completing pre-req

How to backup current adpatch, start another patch then restore your previous adpatch
============================================================
Using the adctrl utility, shutdown the workers.
a. adctrl
b. Select option 3 “Tell worker to shutdown/quit”

c. Backup tables
sqlplus applsys/
create table FND_INSTALL_PROCESSES_3480000
as select * from FND_INSTALL_PROCESSES;
create table AD_DEFERRED_JOBS_3480000
as select * from AD_DEFERRED_JOBS;
drop table FND_INSTALL_PROCESSES;
drop table AD_DEFERRED_JOBS;

d.From OS:
cd $APPL_TOP/admin/
mv restart restart_
mkdir restart

d.Apply the new patch.
Once patch completed.
cd $APPL_TOP/admin/
mv restart restart_
mv restart_ restart

e. recreate the tablessqlplus applsys/
create table FND_INSTALL_PROCESSES
as select * from FND_INSTALL_PROCESSES_3480000;
create table AD_DEFERRED_JOBS
as select * from AD_DEFERRED_JOBS_3480000;
create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;


Now Restart adpatch, it will resume where it stopped previously

Reference: Metalink Note: 175485.1
http://vinitkan.blogspot.ae/

Database monitoring Script

REM Monitoring and tuning script for Oracle databases all versions
REM This script has no adverse affects. There are no DML or DDL actions taken
REM Parts will not work in all versions but useful info should be returned from other parts
REM Uses anonymous procedures to avoid storing objects in the SYS schema
REM therefore this script must be run as sys 
REM calls to v$parameter need to be moved into subblocks to prevent NO_DATA_FOUND exceptions
REM parameter numbers are different between Oracle Versions
REM
REM This daily script is a subset of the weekly script 
REM This script monitors only those things that might cause an application or database failure
REM and not all of those
REM
REM For nicer formatting run the following in vi: %s/  *$//
REM This strips the trailing whitespace returned from oracle
REM
REM These scripts have been collected from many sources and I am sure there are
REM acknowledgements missing from below. Among those are Steve Adams, Rachel Carmichael,
REM Jared Still and other members of the oracle-l mailing list
REM
REM Unknown authors  1990 - 1995
REM Oracle Corporation 1990 - 
REM Bill Beaton, QC Data  1995
REM D. Morgan, QC Data  1997
REM Hari Krishnamoorthy, QC Data 1999
REM J. J. Wang, Bartertrust  2000
REM D. Morgan, 1001111 Alberta Ltd. 2002
REM

set pause off
set verify off
set echo off
set term off
set heading off

REM Set up dynamic spool filename
spool tmp7_spool.sql
 select 'spool '||name||'_'||'daily'||'_'||to_char(sysdate,'yymondd')||'.dat'
 from sys.v_$database;
spool off

set heading on
set verify on
set term on
set serveroutput on size 1000000
set wrap on
set linesize 200
set pagesize 1000

/**************************************** START REPORT ****************************************************/

/* Run dynamic spool output name */
@tmp7_spool.sql

set feedback off
set heading off

select 'Report Date: '||to_char(sysdate,'Monthdd, yyyy hh:mi')
from dual;

set heading on
prompt =================================================================================================
prompt .                      DATABASE (V$DATABASE) (V$VERSION)
prompt =================================================================================================
select NAME "Database Name",
 CREATED "Created",
 LOG_MODE "Status"
  from sys.v_$database;

select banner "Current Versions"
  from sys.v_$version;

prompt =================================================================================================
prompt .                      UPTIME (V$DATABASE) (V$INSTANCE)
prompt =================================================================================================

set heading off
column sttime format A30

SELECT NAME, ' Database Started on ',TO_CHAR(STARTUP_TIME,'DD-MON-YYYY "at" HH24:MI')
FROM V$INSTANCE, v$database;
set heading on


prompt .
prompt =================================================================================================
prompt .                      SGA SIZE (V$SGA) (V$SGASTAT)
prompt =================================================================================================
column Size format 99,999,999,999
select decode(name, 'Database Buffers',
  'Database Buffers (DB_BLOCK_SIZE*DB_BLOCK_BUFFERS)',
  'Redo Buffers',
  'Redo Buffers     (LOG_BUFFER)', name) "Memory",
  value  "Size"
 from sys.v_$sga
UNION ALL
 select '------------------------------------------------------' "Memory",
  to_number(null)  "Size"
   from dual
UNION ALL
 select 'Total Memory' "Memory",
  sum(value) "Size"
   from sys.v_$sga;

prompt .
prompt .
prompt Current Break Down of (SGA) Variable Size
prompt ------------------------------------------

column Bytes  format 999,999,999
column "% Used"  format 999.99
column "Var. Size" format 999,999,999

select a.name   "Name",
 bytes   "Bytes",
 (bytes / b.value) * 100 "% Used",
 b.value   "Var. Size"
from sys.v_$sgastat a,
 sys.v_$sga b
where a.name not in ('db_block_buffers','fixed_sga','log_buffer')
and b.name='Variable Size'
order by 3 desc;

prompt .

set feedback ON

declare
 h_char          varchar2(100);
 h_char2  varchar(50);
 h_num1          number(25);
 result1         varchar2(50);
 result2         varchar2(50);

 cursor c1 is
        select lpad(namespace,17)||': gets(pins)='||rpad(to_char(pins),9)||
                                     ' misses(reloads)='||rpad(reloads,9)||
               ' Ratio='||decode(reloads,0,0,to_char((reloads/pins)*100,999.999))||'%'
        from v$librarycache;

begin
    dbms_output.put_line
     ('=================================================================================================');
    dbms_output.put_line('.                      SHARED POOL: LIBRARY CACHE (V$LIBRARYCACHE)');
    dbms_output.put_line
     ('=================================================================================================');
    dbms_output.put_line('.');
    dbms_output.put_line('.         Goal: The library cache ratio < 1%' );
    dbms_output.put_line('.');
    
    Begin
     SELECT 'Current setting: '||substr(value,1,30) INTO result1
     FROM V$PARAMETER 
     WHERE NUM = 23;
     SELECT 'Current setting: '||substr(value,1,30) INTO result2
     FROM V$PARAMETER 
     WHERE NUM = 325;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN 
      h_num1 :=1;
    END;
    dbms_output.put_line('Recommendation: Increase SHARED_POOL_SIZE '||rtrim(result1));
    dbms_output.put_line('.                        OPEN_CURSORS '    ||rtrim(result2));
    dbms_output.put_line('.               Also write identical sql statements.');
    dbms_output.put_line('.');
        
    open c1;
    loop
 fetch c1 into h_char;
 exit when c1%notfound;
 
 dbms_output.put_line('.'||h_char);
    end loop;
    close c1;

    dbms_output.put_line('.');

    select lpad('Total',17)||': gets(pins)='||rpad(to_char(sum(pins)),9)||
                                 ' misses(reloads)='||rpad(sum(reloads),9),
               ' Your library cache ratio is '||
                decode(sum(reloads),0,0,to_char((sum(reloads)/sum(pins))*100,999.999))||'%'
    into h_char,h_char2
    from v$librarycache;
    dbms_output.put_line('.'||h_char);
    dbms_output.put_line('.           ..............................................');
    dbms_output.put_line('.           '||h_char2);

    dbms_output.put_line('.');
end;
/

declare
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);
        result1         varchar2(50);

begin
    dbms_output.put_line
     ('=================================================================================================');
        dbms_output.put_line('.                      SHARED POOL: DATA DICTIONARY (V$ROWCACHE)');
    dbms_output.put_line
     ('=================================================================================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: The row cache ratio should be < 10% or 15%' );
        dbms_output.put_line('.');
        dbms_output.put_line('.         Recommendation: Increase SHARED_POOL_SIZE '||result1);
        dbms_output.put_line('.');

        select sum(gets) "gets", sum(getmisses) "misses", round((sum(getmisses)/sum(gets))*100 ,3)
        into h_num1,h_num2,h_num3
        from v$rowcache;

        dbms_output.put_line('.');
        dbms_output.put_line('.             Gets sum: '||h_num1);
        dbms_output.put_line('.        Getmisses sum: '||h_num2);

        dbms_output.put_line('         .......................................');
        dbms_output.put_line('.        Your row cache ratio is '||h_num3||'%');

end;
/

declare
        h_char          varchar2(100);
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);
        h_num4          number(25);
        result1         varchar2(50);
begin
    dbms_output.put_line('.');
    dbms_output.put_line
     ('=================================================================================================');
        dbms_output.put_line('.                      BUFFER CACHE (V$SYSSTAT)');
    dbms_output.put_line
     ('=================================================================================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: The buffer cache ratio should be > 70% ');
        dbms_output.put_line('.');
 Begin
      SELECT 'Current setting: '||substr(value,1,30) INTO result1
      FROM V$PARAMETER 
      WHERE NUM = 125;
     EXCEPTION
     WHEN NO_DATA_FOUND THEN 
      result1 := 'Unknown parameter';
 END;
        dbms_output.put_line('.          Recommendation: Increase DB_BLOCK_BUFFERS '||result1);
        dbms_output.put_line('.');

        select lpad(name,15)  ,value
        into h_char,h_num1
        from v$sysstat
        where name ='db block gets';
        dbms_output.put_line('.         '||h_char||': '||h_num1);

        select lpad(name,15)  ,value
        into h_char,h_num2
        from v$sysstat
        where name ='consistent gets';
        dbms_output.put_line('.         '||h_char||': '||h_num2);

        select lpad(name,15)  ,value
        into h_char,h_num3
        from v$sysstat
        where name ='physical reads';
        dbms_output.put_line('.         '||h_char||': '||h_num3);

        h_num4:=round(((1-(h_num3/(h_num1+h_num2))))*100,3);

        dbms_output.put_line('.          .......................................');
        dbms_output.put_line('.          Your buffer cache ratio is '||h_num4||'%');

    dbms_output.put_line('.');
end;
/

declare
        h_char          varchar2(100);
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);

        cursor buff2 is
        SELECT name
                ,consistent_gets+db_block_gets, physical_reads
                ,DECODE(consistent_gets+db_block_gets,0,TO_NUMBER(null)
                ,to_char((1-physical_reads/(consistent_gets+db_block_gets))*100, 999.999))
        FROM v$buffer_pool_statistics;
begin
     dbms_output.put_line
     ('=================================================================================================');
        dbms_output.put_line('.                      BUFFER CACHE (V$buffer_pool_statistics)');
    dbms_output.put_line
     ('=================================================================================================');

        dbms_output.put_line('.');
        dbms_output.put_line('.');
        dbms_output.put_line('Buffer Pool:         Logical_Reads     Physical_Reads        HIT_RATIO');
        dbms_output.put_line('.');

        open buff2;
        loop
            fetch buff2 into h_char, h_num1, h_num2, h_num3;
            exit when buff2%notfound;

     dbms_output.put_line(rpad(h_char, 15, '.')||'         '||lpad(h_num1, 10, ' ')||'         '||
      lpad(h_num2, 10, ' ')||'       '||lpad(h_num3, 10, ' '));

        end loop;
        close buff2;

    dbms_output.put_line('.');
end;
/

declare
        h_char          varchar2(100);
        h_num1          number(25);
        result1         varchar2(50);

        cursor c2 is
        select name,value
        from v$sysstat
        where name in ('sorts (memory)','sorts (disk)')
        order by 1 desc;

begin
  dbms_output.put_line
      ('=================================================================================================');
        dbms_output.put_line('.                      SORT STATUS (V$SYSSTAT)');
 dbms_output.put_line
      ('=================================================================================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: Very low sort (disk)' );
        dbms_output.put_line('.');
        BEGIN
      SELECT 'Current setting: '||substr(value,1,30) INTO result1
      FROM V$PARAMETER 
      WHERE NUM = 320;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN 
       result1 := 'Unknown parameter';
     END;
        dbms_output.put_line('           Recommendation: Increase SORT_AREA_SIZE '||result1);
        dbms_output.put_line('.');
        dbms_output.put_line('.');
        dbms_output.put_line(rpad('Name',30)||'Count');
        dbms_output.put_line(rpad('-',25,'-')||'     -----------');

        open c2;
        loop
        fetch c2 into h_char,h_num1;
        exit when c2%notfound;
                dbms_output.put_line(rpad(h_char,30)||h_num1);
        end loop;
        close c2;
end;
/

prompt .
prompt =================================================================================================
prompt .                      TABLESPACE USAGE (DBA_DATA_FILES, DBA_FREE_SPACE)
prompt =================================================================================================
column Tablespace format a30
column Size  format 999,999,999,999
column Used  format 999,999,999,999
column Free  format 999,999,999,999
column "% Used"  format 999.99
select tablespace_name  "Tablesapce",
        bytes   "Size",
        nvl(bytes-free,bytes) "Used",
        nvl(free,0)  "Free",
        nvl(100*(bytes-free)/bytes,100) "% Used"
  from(
 select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
 FROM (select tablespace_name, sum(bytes) bytes
 from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
 where ddf.tablespace_name = dfs.tablespace_name(+)
 group by ddf.tablespace_name, ddf.bytes)
  order by 5 desc;

set feedback off
set heading off
select rpad('Total',30,'.')  "Tablespace",
   sum(bytes)   "Size",
        sum(nvl(bytes-free,bytes)) "Used",
        sum(nvl(free,0))  "Free",
        (100*(sum(bytes)-sum(free))/sum(bytes)) "% Used"
  from(
 select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
 FROM (select tablespace_name, sum(bytes) bytes
 from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
 where ddf.tablespace_name = dfs.tablespace_name(+)
 group by ddf.tablespace_name, ddf.bytes);

set feedback on
set heading on

prompt .
prompt =================================================================================================
prompt .                      FREE SPACE FRAGMENTATION (DBA_FREE_SPACE)
prompt =================================================================================================
column Tablespace format a30
column "Available Size" format 99,999,999,999
column "Fragmentation" format 99,999
column "Average Size" format 9,999,999,999
column "   Max"  format 9,999,999,999
column "   Min"  format 9,999,999,999
select tablespace_name Tablespace, 
 count(*) Fragmentation, 
 sum(bytes) "Available Size",
 avg(bytes) "Average size",
 max(bytes) Max, 
 min(bytes) Min
from dba_free_space
group by tablespace_name
order by 3 desc ;

prompt .
prompt ============================================================================================
prompt .                   SUMMARY OF INVALID OBJECTS (DBA_OBJECTS)
prompt ============================================================================================

select owner, object_type, substr(object_name,1,30) object_name, status
from dba_objects
where status='INVALID'
order by object_type;

prompt .
prompt ============================================================================================
prompt .                   LAST REFRESH OF SNAPSHOTS (DBA_SNAPSHOTS)
prompt ============================================================================================

select owner, name, last_refresh 
from dba_snapshots 
where last_refresh < (SYSDATE - 1);


prompt .
prompt ============================================================================================
prompt .                   LAST JOBS SCHEDULED (DBA_JOBS)
prompt ============================================================================================

set arraysize 10
set linesize 65
col what format a65
col log_user format a10
col job format 9999
select job, log_user, last_date, last_sec, next_date, next_sec, 
failures, what 
from dba_jobs
where failures > 0;

set linesize 100

prompt .
prompt =================================================================================================
prompt .                      ERROR- These segments will fail during NEXT EXTENT (DBA_SEGMENTS)
prompt =================================================================================================
column Tablespaces format a30
column Segment  format a40
column "NEXT Needed" format 999,999,999
column "MAX Available" format 999,999,999
select a.tablespace_name "Tablespaces",
 a.owner   "Owner",
 a.segment_name  "Segment",
 a.next_extent  "NEXT Needed",
 b.next_ext  "MAX Available"
  from sys.dba_segments a,
 (select tablespace_name,max(bytes) next_ext
 from sys.dba_free_space 
 group by tablespace_name) b
 where a.tablespace_name=b.tablespace_name(+)
   and b.next_ext < a.next_extent;

prompt =================================================================================================
prompt .                      WARNING- These segments > 70% of MAX EXTENT (DBA_SEGMENTS)
prompt =================================================================================================
column Tablespace format a30
column Segment  format a40
column Used  format 9999
column Max  format 9999
select tablespace_name "Tablespace",
 owner  "Owner",
 segment_name "Segment",
 extents  "Used",
 max_extents "Max"
  from sys.dba_segments
 where (extents/decode(max_extents,0,1,max_extents))*100 > 70
   and max_extents >0;

prompt =================================================================================================
prompt .                      LIST OF OBJECTS HAVING > 12 EXTENTS (DBA_EXTENTS)
prompt =================================================================================================
column Tablespace_ext format a30
column Segment  format a40
column Count  format 9999
break on "Tablespace_ext" skip 1
select tablespace_name "Tablespace_ext" ,
 owner  "Owner",
 segment_name    "Segment",
 count(*)        "Count"
  from sys.dba_extents
 group by tablespace_name,owner,segment_name
 having count(*)>12
 order by 1,3 desc;

prompt =================================================================================================
prompt End of Report

spool off

/* Remove temp spool scripts */
host rm tmp7_*.sql

exit;

Ref:

====

http://www.1001111.com/daily_script.html

Creating APPS READ ONLY schema in Oracle Applications

Creating APPS READ ONLY schema in Oracle Applications
=====================================================
bash $ sqlplus "/ as sysdba"

SQL > create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;

SQL> grant connect, resource to appsro;

SQL> grant create synonym to appsro;

SQL> exit;
bash $ sqlplus apps/******

SQL>set head off

SQL> set newpage none

SQL> set pagesize 9999

SQL> spool create_synonyms.sql

SQL> select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

SQL> spool off

SQL> spool grant_select.sql

SQL> select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to appsro;' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

SQL> spool off

SQL> exit;

bash $ sqlplus "/as sysdba"

SQL> @grant_select.sql

SQL> exit;

bash $ sqlplus appsro/appsro

SQL> @create_synonyms.sql

SQL> exit;



Ref:
===
https://muneer2908.wordpress.com/tag/apps-read-only-schema-apps_read_only-appsro-database-objects-owned-by-apps-user-applications-oracle-apps-11i-r12-9i-10g/

Important Queries for apps

Query 1: Select responsibility name along with application name
==============================================

SELECT application_short_name ,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt , fnd_application fa
WHERE fa.application_id = frt.application_id;

Query 2: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1
=====================================================================

SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = &resp_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';

Query 3: Get User name and related assigned responsibilities
============================================

SELECT distinct u.user_id, u.user_name user_name,
r.responsibility_name responsiblity,
a.application_name application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
order by 1;

Query 4: Get Request Group associate with Responsibility Name
==============================================
SELECT responsibility_name responsibility, request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

Query 5: Gets Form personalization listing
===============================
Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables

applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes
SELECT ffft.user_function_name “User Form Name”, ffcr.SEQUENCE,
ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;
Query 6: Query to view the patch level status of all modules
SELECT a.application_name,
DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,
patch_level
FROM apps.fnd_application_vl a, apps.fnd_product_installations b
WHERE a.application_id = b.application_id;
Query 7: SQL to view all request who have attached to a responsibility
SELECT responsibility_name , frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = ‘P’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
Query 8: SQL to view all requests who have attached to a responsibility
SELECT responsibility_name , frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = ‘P’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Query 9: SQL to view all types of request Application wise
==========================================

SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
‘B’, ‘Request Set Stage Function’,
‘Q’, ‘SQL*Plus’,
‘H’, ‘Host’,
‘L’, ‘SQL*Loader’,
‘A’, ‘Spawned’,
‘I’, ‘PL/SQL Stored Procedure’,
‘P’, ‘Oracle Reports’,
‘S’, ‘Immediate’,
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name,
minimum_width,
minimum_length, concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description

Query 10: SQL to view concurrent request processing time, quite useful
===================================================

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ‘ HOURS ‘ ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ‘ MINUTES ‘ ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ‘ SECS ‘ time_difference
,
DECODE(p.concurrent_program_name,’ALECDC’,p.concurrent_program_name||'[‘||f.description||’]’,p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,’R’,’Running’,’C’,’Complete’,f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV(‘Lang’)
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;

Gather Stats

All About Statistics In Oracle



################################
Database | Schema | Table | Index Statistics
################################

Gather Database Stats:
===================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
     ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',
     CASCADE => TRUE,
     degree => 4,
     OPTIONS => 'GATHER STALE',
     GATHER_SYS => TRUE,
     STATTAB => PROD_STATS);

CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether to collected or not.
DEGREE => 4 :Degree of parallelism.
options:
       =>'GATHER' :Gathers statistics on all objects in the schema.
       =>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to gather those statistics.
       =>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
       =>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats objects.
        =>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
        =>'LIST STALE': Returns a list of stale objects as determined by looking at the *_tab_modifications views.
        =>'LIST EMPTY': Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the 'SYS' user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT STATISTICS section -last third in this post-.

Note: All above parameters are valid for all stats kind (schema,table,..) except Gather_SYS.
Note: Skew data means the data inside a column is not uniform, there is a particular one or more value are being repeated much than other values in the same column, for example the gender column in employee table with two values (male/female) in a construction or security service company is skewed where most of employees are male, but in an entity like a hospital where the number of males is near to the number of females, the gender column is not skewed.

For faster execution:
------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);

What's new?
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed values always gives excellent results.(DEFAULT).
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not recommended to be gathered on all columns.
Removed  "cascade => TRUE" To let Oracle determine whether index statistics to be collected or not.
Doubled the "degree => 8" but this depends on the number of CPUs on the machine and accepted CPU overhead during gathering DB statistics.

Starting from Oracle 10g, Oracle introduced an automated task gathers statistics on all objects in the database that having [stale or missing] statistics, To check the status of that task:
SQL> select status from dba_autotask_client where client_name = 'auto optimizer stats collection';

To Enable automatic optimizer statistics task:
SQL> BEGIN
     DBMS_AUTO_TASK_ADMIN.ENABLE(
     client_name => 'auto optimizer stats collection',
     operation => NULL,
     window_name => NULL);
     END;
     /

In case you want to Disable automatic optimizer statistics task:
SQL> BEGIN
     DBMS_AUTO_TASK_ADMIN.DISABLE(
     client_name => 'auto optimizer stats collection',
     operation => NULL,
     window_name => NULL);
     END;
     /

To check tables having stale statistics:

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';

[update on 03-Sep-2014]
Note: In order to get an accurate information from DBA_TAB_STATISTICS or (*_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS) views, you should manually run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to refresh it's parent table mon_mods_all$ from SGA recent data, or you have wait for an Oracle internal that refresh that table  once a day in 10g onwards [except for 10gR2] or every 15 minutes in 10gR2 or every 3 hours in 9i backwards. or when you run manually run one of GATHER_*_STATS procedures.
[Reference: Oracle Support and MOS ID 1476052.1]

Gather SCHEMA Stats:
=====================
SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS (
     ownname =>'SCOTT',
     estimate_percent=>10,
     degree=>1,
     cascade=>TRUE,
     options=>'GATHER STALE');


Gather TABLE Stats:
===================
Check table statistics date:
SQL> select table_name, last_analyzed from user_tables where table_name='T1';

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'SCOTT',
     tabname => 'EMP',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

CASCADE => TRUE : Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 2:  Degree of parallelism.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=>  :  For gathering Histograms:
 FOR COLUMNS SIZE AUTO :  You can specify one column between "" instead of all columns.
 FOR ALL COLUMNS SIZE REPEAT :  Prevent deletion of histograms and collect it only for columns already have histograms.
 FOR ALL COLUMNS  :  Collect histograms on all columns.
 FOR ALL COLUMNS SIZE SKEWONLY :  Collect histograms for columns have skewed value should test skewness first>.
 FOR ALL INDEXED COLUMNS :  Collect histograms for columns have indexes only.


Note: Truncating a table will not update table statistics, it will only reset the High Water Mark, you've to re-gather statistics on that table.

Inside "DBA BUNDLE", there is a script called "gather_stats.sh", it will help you easily & safely gather statistics on specific schema or table plus providing advanced features such as backing up/ restore new statistics in case of fallback.
To learn more about "DBA BUNDLE" please visit this post:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html


Gather Index Stats:
==================
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',indname => 'EMP_I',
     estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);

###################
Fixed OBJECTS Statistics
###################

What are Fixed objects:
---------------------
-Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
-If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
-Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.

How frequent to gather stats on fixed objects?
------------------------------------------
Only one time for a representative workload unless you've one of these cases:

- After a major database or application upgrade.
- After implementing a new module.
- After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
- Poor performance/Hang encountered while querying dynamic views e.g. V$ views.


Note:
- It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity.
- Also note that performance degradation may be experienced while the statistics are gathering.
- Having no statistics is better than having a non representative statistics.

How to gather stats on fixed objects:
----------------------------------

First Check the last analyzed date:
---- --------------------------
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED
        from dba_tab_statistics where table_name='X$KGLDP';
Second Export the current fixed stats in a table: (in case you need to revert back)
------- -----------------------------------
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE
        ('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');

SQL> EXEC dbms_stats.export_fixed_objects_stats
        (stattab=>'STATS_TABLE_NAME',statown=>'OWNER');
Third Gather the fixed objects stats:
------  ------------------------
SQL> exec dbms_stats.gather_fixed_objects_stats;

Note:
In case you experienced a bad performance on fixed tables after gathering the new statistics:

SQL> exec dbms_stats.delete_fixed_objects_stats();
SQL> exec DBMS_STATS.import_fixed_objects_stats
        (stattab =>'STATS_TABLE_NAME',STATOWN =>'OWNER');


#################
SYSTEM STATISTICS
#################

What is system statistics:
-----------------------
System statistics are statistics about CPU speed and IO performance, it enables the CBO to
effectively cost each operation in an execution plan. Introduced in Oracle 9i.

Why gathering system statistics:
------------------------------
Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.

There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):

NOWORKLOAD statistics:
--------------------------
This will simulates a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD statistics" but if you can't capture the statistics during a typical workload you can use noworkload statistics.
To gather noworkload statistics:
SQL> execute dbms_stats.gather_system_stats();

WORKLOAD statistics:
----------------------
This will gather statistics during the current workload [which supposed to be representative of actual system I/O and CPU workload on the DB].
To gather WORKLOAD statistics:
SQL> execute dbms_stats.gather_system_stats('start');
Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:
SQL> execute dbms_stats.gather_system_stats('stop');
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60);

Check the system values collected:
-------------------------------
col pname format a20
col pval2 format a40
select * from sys.aux_stats$;

cpuspeedNW:  Shows the noworkload CPU speed, (average number of CPU cycles per second).
ioseektim:    The sum of seek time, latency time, and OS overhead time.
iotfrspeed:  I/O transfer speed,tells optimizer how fast the DB can read data in a single read request.
cpuspeed:      Stands for CPU speed during a workload statistics collection.
maxthr:          The maximum I/O throughput.
slavethr:      Average parallel slave I/O throughput.
sreadtim:     The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim:     The average time (seconds) for a sequential multiblock read.
mbrc:             The average multiblock read count in blocks.

Notes:
-When gathering NOWORKLOAD statistics it will gather (cpuspeedNW, ioseektim, iotfrspeed) system statistics only.
-Above values can be modified manually using DBMS_STATS.SET_SYSTEM_STATS procedure.
-According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.

Delete system statistics:
---------------------
SQL> execute dbms_stats.delete_system_stats();


####################
Data Dictionary Statistics
####################

Facts:
-----
> Dictionary tables are the tables owned by SYS and residing in the system tablespace.
> Normally data dictionary statistics in 9i is not required unless performance issues are detected.
> In 10g Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window.

If you choose to switch off that job for application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET from AUTO to ORACLE using the procedure:

SQL> Exec DBMS_STATS.SET_PARAM(AUTOSTATS_TARGET,'ORACLE');

When to gather Dictionary statistics:
---------------------------------
-After DB upgrades.
-After creation of a new big schema.
-Before and after big datapump operations.

Check last Dictionary statistics date:
----------------------------------
SQL> select table_name, last_analyzed from dba_tables
     where owner='SYS' and table_name like '%$' order by 2;

Gather Dictionary Statistics:
-------------------------
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
->Will gather stats on 20% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
->Will gather stats on 100% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(gather_sys=>TRUE);
->Will gather stats on the whole DB+SYS schema.



#############
Extended Statistics "11g onwards"
#############

Extended statistics can be gathered on columns based on functions or column groups.

Gather extended stats on column function:
=====================================
If you run a query having in the WHERE statement a function like upper/lower the optimizer will be off and index on that column will not be used:
SQL> select count(*) from EMP where lower(ename) = 'scott';

In order to make optimizer work with function based terms you need to gather extended stats:

1-Create extended stats:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SCOTT','EMP','(lower(ENAME))') from dual;

2-Gather histograms:
>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP', method_opt=> 'for all columns size skewonly');

OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>

SQL> Begin dbms_stats.gather_table_stats
     (ownname => 'SCOTT',tabname => 'EMP',
     method_opt => 'for all columns size skewonly for
     columns (lower(ENAME))');
     end;
     /

To check the Existance of extended statistics on a table:
---------------------------------------------------
SQL> select extension_name,extension from dba_stat_extensions where owner='SCOTT'and table_name = 'EMP';
SYS_STU2JLSDWQAFJHQST7$QK81_YB  (LOWER("ENAME"))

Drop extended stats on column function:
-------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SCOTT','EMP','(LOWER("ENAME"))');

Gather extended stats on column group: -related columns-
=================================
Certain columns in a table that are part of a join condition (where statement  are correlated e.g.(country,state). You want to make the optimizer aware of this relationship between two columns and more instead of using separate statistics for each columns. By creating extended statistics on a group of columns, the Optimizer can determine a more accurate the relation between the columns are used together in a where clause of a SQL statement. e.g. columns like country_id and state_name the have a relationship, state like Texas can only be found in USA so the value of state_name are always influenced by country_id.
If there are extra columns are referenced in the "WHERE statement  with the column group the optimizer will make use of column group statistics.

1- create a column group:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)')from dual;
2- Re-gather stats|histograms for table so optimizer can use the newly generated extended statistics:
>>>>>>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SH','customers',method_opt=> 'for all columns size skewonly');

OR
---

*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>

SQL> Begin dbms_stats.gather_table_stats
     (ownname => 'SH',tabname => 'CUSTOMERS',
     method_opt => 'for all columns size skewonly for
     columns (country_id,cust_state_province)');
     end;
     /

Drop extended stats on column group:
--------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)');


########
Histograms
########

What are Histograms?
-----------------------
> Holds data about values within a column in a table for number of occurrences for a specific value/range.
> Used by CBO to optimize a query to use whatever index Fast Full scan or table full scan.
> Usually being used against columns have data being repeated frequently like country or city column.
> gathering histograms on a column having distinct values (PK) is useless because values are not repeated.
> Two types of Histograms can be gathered:
  -Frequency histograms: is when distinct values (buckets) in the column is less than 255 (e.g. the number of countries is always less than 254).
  -Height balanced histograms: are similar to frequency histograms in their design, but distinct values  > 254
    See an Example: http://aseriesoftubes.com/articles/beauty-and-it/quick-guide-to-oracle-histograms
> Collected by DBMS_STATS (which by default doesn't collect histograms, it deletes them if you didn't use the parameter).
> Mainly being gathered on foreign key columns/columns in WHERE statement.
> Help in SQL multi-table joins.
> Column histograms like statistics are being stored in data dictionary.
> If application exclusively uses bind variables, Oracle recommends deleting any existing histograms and disabling Oracle histograms generation.

Cautions:
   – Do not create them on Columns that are not being queried.
   – Do not create them on every column of every table.
   – Do not create them on the primary key column of a table.

Verify the existence of histograms:
--------------------------------------
SQL> select column_name,histogram from dba_tab_col_statistics
     where owner='SCOTT' and table_name='EMP';

Creating Histograms:
-----------------------
e.g.
SQL> Exec dbms_stats.gather_schema_stats
     (ownname => 'SCOTT',
     estimate_percent => dbms_stats.auto_sample_size,
     method_opt => 'for all columns size auto',
     degree => 7);


method_opt:
FOR COLUMNS SIZE AUTO                 => Fastest. you can specify one column instead of all columns.
FOR ALL COLUMNS SIZE REPEAT     => Prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS => collect histograms on all columns .
FOR ALL COLUMNS SIZE SKEWONLY => collect histograms for columns have skewed value .
FOR ALL INDEXES COLUMNS      => collect histograms for columns have indexes.

Note: AUTO & SKEWONLY will let Oracle decide whether to create the Histograms or not.

Check the existence of Histograms:
SQL> select column_name, count(*) from dba_tab_histograms
     where OWNER='SCOTT' table_name='EMP' group by column_name;

Drop Histograms: 11g
------------------
e.g.
SQL> Exec dbms_stats.delete_column_stats
     (ownname=>'SH', tabname=>'SALES',
     colname=>'PROD_ID', col_stat_type=> HISTOGRAM);


Stop gather Histograms: 11g
----------------------
[This will change the default table options]
e.g.
SQL> Exec dbms_stats.set_table_prefs
     ('SH', 'SALES','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS SIZE 1 PROD_ID');
>Will continue to collect histograms as usual on all columns in the SALES table except for PROD_ID column.

Drop Histograms: 10g
------------------
e.g.
SQL> exec dbms_stats.delete_column_stats(user,'T','USERNAME');


##################################
Save/IMPORT & RESTORE STATISTICS:
##################################
===================
Export /Import Statistics:
===================
In this way statistics will be exported into table then imported later from that table.

1-Create STATS TABLE:
-  -------------------------
SQL> Exec dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'prod_stats',tblspace => 'USERS');

2-Export the statistics to the STATS table:
---------------------------------------------
For Database stats:
SQL> Exec dbms_stats.export_database_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For System stats:
SQL> Exec dbms_stats.export_SYSTEM_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For Dictionary stats:
SQL> Exec dbms_stats.export_Dictionary_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For Fixed Tables stats:
SQL> Exec dbms_stats.export_FIXED_OBJECTS_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For Schema stas:
SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA','STATS_TABLE',NULL,'STATS_TABLE_OWNER');
For Table:
SQL> Conn scott/tiger
SQL> Exec dbms_stats.export_TABLE_stats(ownname => 'SCOTT',tabname => 'EMP',stattab => 'prod_stats');
For Index:
SQL> Exec dbms_stats.export_INDEX_stats(ownname => 'SCOTT',indname => 'PK_EMP',stattab => 'prod_stats');
For Column:
SQL> Exec dbms_stats.export_COLUMN_stats (ownname=>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');

3-Import the statistics from PROD_STATS table to the dictionary:
--------------------------------------------------------------------
For Database stats:
SQL> Exec DBMS_STATS.IMPORT_DATABASE_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM');
For System stats:
SQL> Exec DBMS_STATS.IMPORT_SYSTEM_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM');
For Dictionary stats:
SQL> Exec DBMS_STATS.IMPORT_Dictionary_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM');
For Fixed Tables stats:
SQL> Exec DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM');
For Schema stats:
SQL> Exec DBMS_STATS.IMPORT_SCHEMA_STATS
     (ownname => 'SCOTT',stattab => 'prod_stats', statown => 'SYSTEM');
For Table stats and it's indexes:
SQL> Exec dbms_stats.import_TABLE_stats
     ( ownname => 'SCOTT', stattab => 'prod_stats',tabname => 'EMP');
For Index:
SQL> Exec dbms_stats.import_INDEX_stats
     ( ownname => 'SCOTT', stattab => 'prod_stats', indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.import_COLUMN_stats
     (ownname=>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');

4-Drop Stat Table:
-------------------
SQL> Exec dbms_stats.DROP_STAT_TABLE (stattab => 'prod_stats',ownname => 'SYSTEM');

===============
Restore statistics: -From Dictionary-
===============
Old statistics are saved automatically in SYSAUX for 31 day.

Restore Dictionary stats as of timestamp:
-----------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DICTIONARY_STATS(sysdate-1);

Restore Database stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DATABASE_STATS(sysdate-1);

Restore SYSTEM stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_SYSTEM_STATS(sysdate-1);

Restore FIXED OBJECTS stats as of timestamp:
-----------------------------------------------
SQL> Exec DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(sysdate-1);

Restore SCHEMA stats as of timestamp:
---------------------------------------
SQL> Exec dbms_stats.restore_SCHEMA_stats
     (ownname=>'SYSADM',AS_OF_TIMESTAMP=>sysdate-1);
OR:
SQL> Exec dbms_stats.restore_schema_stats
     (ownname=>'SYSADM',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');

Restore Table stats as of timestamp:
-----------------------------------
SQL> Exec DBMS_STATS.RESTORE_TABLE_STATS
     (ownname=>'SYSADM', tabname=>'T01POHEAD',AS_OF_TIMESTAMP=>sysdate-1);
=========
Advanced:
=========

To Check current Stats history retention period (days):
-------------------------------------------------
SQL> select dbms_stats.get_stats_history_retention from dual;
SQL> select dbms_stats.get_stats_history_availability from dual;
To modify current Stats history retention period (days):
-------------------------------------------------
SQL> Exec dbms_stats.alter_stats_history_retention(60);

Purge statistics older than 10 days:
------------------------------
SQL> Exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

Procedure To claim space after purging statstics:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Space will not be claimed automatically when you purge stats, you must claim it manually using this procedure:

Check Stats tables size:
>>>>>>
        col Mb form 9,999,999
        col SEGMENT_NAME form a40
        col SEGMENT_TYPE form a6
        set lines 120
        select sum(bytes/1024/1024) Mb,
        segment_name,segment_type from dba_segments
         where  tablespace_name = 'SYSAUX'
        and segment_name like 'WRI$_OPTSTAT%'
        and segment_type='TABLE'
        group by segment_name,segment_type order by 1 asc
        /

Check Stats indexes size:
>>>>>
        col Mb form 9,999,999
        col SEGMENT_NAME form a40
        col SEGMENT_TYPE form a6
        set lines 120
        select sum(bytes/1024/1024) Mb, segment_name,segment_type
        from dba_segments
        where  tablespace_name = 'SYSAUX'
        and segment_name like '%OPT%'
        and segment_type='INDEX'
        group by segment_name,segment_type order by 1 asc
        /
Move Stats tables in same tablespace:
>>>>>
        select 'alter table '||segment_name||'  move tablespace
        SYSAUX;' from dba_segments
        where tablespace_name = 'SYSAUX'
        and segment_name like '%OPT%' and segment_type='TABLE'
        /
Rebuild stats indexes:
>>>>>>
        select 'alter index '||segment_name||'  rebuild online;'
        from dba_segments where tablespace_name = 'SYSAUX'
        and segment_name like '%OPT%' and segment_type='INDEX'
        /

Check for un-usable indexes:
>>>>>
        select  di.index_name,di.index_type,di.status  from
        dba_indexes di , dba_tables dt
        where  di.tablespace_name = 'SYSAUX'
        and dt.table_name = di.table_name
        and di.table_name like '%OPT%'
        order by 1 asc
        /

Delete Statistics:
===============
For Database stats:
SQL> Exec DBMS_STATS.DELETE_DATABASE_STATS ();
For System stats:
SQL> Exec DBMS_STATS.DELETE_SYSTEM_STATS ();
For Dictionary stats:
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS ();
For Fixed Tables stats:
SQL> Exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ();
For Schema stats:
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS ('SCOTT');
For Table stats and it's indexes:
SQL> Exec dbms_stats.DELETE_TABLE_stats(ownname=>'SCOTT',tabname=>'EMP');
For Index:
SQL> Exec dbms_stats.DELETE_INDEX_stats(ownname => 'SCOTT',indname => 'PK_EMP');
For Column:
SQL> Exec dbms_stats.DELETE_COLUMN_stats(ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO');

Note: This procedure can be rollback by restoring STATS using DBMS_STATS.RESTORE_ procedure.


Pending Statistics:  "11g onwards"
===============
What is Pending Statistics:
Pending statistics is a feature let you test the new gathered statistics without letting the CBO (Cost Based Optimizer) use them "system wide" unless you publish them.

How to use Pending Statistics:
Switch on pending statistics mode:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','FALSE');
Note: Any new statistics will be gathered on the database will be marked PENDING unless you change back the previous parameter to true:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE');

Gather statistics: "as you used to do"
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('sh','SALES');
Enable using pending statistics on your session only:
SQL> Alter session set optimizer_use_pending_statistics=TRUE;
Then any SQL statement you will run will use the new pending statistics...

When proven OK, publish the pending statistics:
SQL> Exec DBMS_STATS.PUBLISH_PENDING_STATS();

Once you finish don't forget to return the Global PUBLISH parameter to TRUE:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE');
>If you didn't do so, all new gathered statistics on the database will be marked as PENDING, the thing may confuse you or any DBA working on this DB in case he is not aware of that parameter change.

References:
http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm
http://dba-tips.blogspot.com/2012/11/all-about-statistics-in-oracle.html