Clear cache EBS 11i and R12

Clear cache in Apache/iAS, Cabo, browser, Java, Jinitiator, portal, webadi

Apache / iAS 
For 11i and earlier versions:
- shutdown iAS server
- go to $OA_HTML (for 11.5.9) or $COMMON_TOP (for 11.5.10.x) directory
- backup the directory _pages and delete its contents by running for instance:

rm -rf $COMMON_TOP/_pages/*

- for modplsql caches remove contents of $IAS_ORACLE_HOME/Apache/modplsql/cache directory
- restart iAS server

To clear middle tier cache in release 12:
(please review Note 759038.1 for details)

- go to "Functional Administrator" responsibility
- select Core Services => Caching Framework => Global Configuration => Clear cache

In case you have login issue after accidentally cleared the _pages instead of using the method above for r12
please review Note 433386.1 to recompile jsp files.

Cabo
Images and style sheets can be corrupted or out of sync in the cabo caches,
you may need to clear the related directories after backup:

$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache
Web Browser

for Internet Explorer:
- go to menu Tools => Internet Options,
- select 'General' tab,
- click on button 'Delete Files' in 'Temporary Internet files' area
or
- click on button 'Delete...' then in 'Delete Browsing History' pop-up window click on 'Delete Files...'
- close all IE windows and restart new browser session.

for Mozilla Firefox:
- go to menu Tools
- select 'Clear Private Data...' or 'Clear Recent History...' then check 'Cache'
or
- go to menu Edit or Tools
- select Preferences or Options.
- expand the 'Advance' options and choose 'Cache' or Privacy
- click the button called 'Clear Cache'.

Jinitiator 

Two possibilities depending of the Jinitiator version:

for 1.1.8.x versions:
- delete all files in directory:

C:\Program Files\Oracle\Jinitiator \jcache\

for 1.3.1.x versions:
- go to Start => Parameters => Control Panel
- double-click on "Jinitiator " icon
- in the new pop-up window, click on "Cache" tab
- click on "Clear Jar Cache " button. On prompt, click Yes.

(you can also delete directly all files under directory:
C:\Documents and Settings\\Oracle Jar Cache)


Java
Java/JRE plug-in (Windows)
- go to Start => Parameters => Control Panel
- double-click on 'Java' icon
- in the new pop-up window, click on 'General' tab
- click on 'Setting...' button in 'Temporary Internet files' area then click on 'Delete Files...' button
(you can select Applets, Applications or other files)

JVM

- go to responsibility "Functional Administrator"
- click on "Core Services" tab then the "Caching Framework" sub-tab
- click on "Global Configuration" link then click on "Clear all cache" button to clear all of the Java Cache's


Portal
- go to the url: http://:/pls/admin_/gateway.htm or cache.htm
- click on "Cache Settings" option and note the "Cache Directory"
- go to this directory and delete all the cache files in the directory and sub directories

See also modplsql caches in Apache/iAS section above.


WebADI / BNE cache
For instance when enabling BNE log
- go to url: http://:/oa_servlets/oracle.apps.bne.framework.BneAdminServlet
- click on the "clear-cache" link
- at the bottom of the page you should see 'Cache Cleared'

Script to check DB performance

Script to check DB performace:

set serveroutput on
declare
cursor c1 is select version
from v$instance;
cursor c2 is
    select
          host_name
       ,  instance_name
       ,  to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime
       ,  to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime
     from v$instance;
cursor c4 is
select * from (SELECT count(*) cnt, substr(event,1,50) event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC) where rownum <6;
cursor c5 is
select round(sum(value)/1048576) as sgasize from v$sga;
cursor c6 is select round(sum(bytes)/1048576) as dbsize
from v$datafile;
cursor c7 is select 'top physical i/o process' category, sid,
       username, total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('physical reads', 'physical writes',
                     'physical reads direct',
                     'physical reads direct (lob)',
                     'physical writes direct',
                     'physical writes direct (lob)')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('physical reads', 'physical writes',
                       'physical reads direct',
                       'physical reads direct (lob)',
                       'physical writes direct',
                       'physical writes direct (lob)'))
where rownum < 2
union all
select 'top logical i/o process', sid, username,
       total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
 and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid,
       username, total_user_mem,
       round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_mem
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('session pga memory', 'session uga memory')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_mem
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username,
       total_user_cpu,
       round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session'
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session')
where rownum < 2;

cursor c8 is select username, sum(VALUE/100) cpu_usage_sec
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.sid = ss.sid
and username is not null
and username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by username
order by 2 desc;
begin
dbms_output.put_line ('Database Version');
dbms_output.put_line ('-----------------');
for rec in c1
loop
dbms_output.put_line(rec.version);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Hostname');
dbms_output.put_line ('----------');
for rec in c2
loop
     dbms_output.put_line(rec.host_name);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('SGA Size (MB)');
dbms_output.put_line ('-------------');
for rec in c5
loop
     dbms_output.put_line(rec.sgasize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Database Size (MB)');
dbms_output.put_line ('-----------------');
for rec in c6
loop
     dbms_output.put_line(rec.dbsize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Instance start-up time');
dbms_output.put_line ('-----------------------');
for rec in c2 loop
 dbms_output.put_line( rec.starttime );
  end loop;
dbms_output.put_line( chr(13) );
  for b in
    (select total, active, inactive, system, killed
    from
       (select count(*) total from v$session)
     , (select count(*) system from v$session where username is null)
     , (select count(*) active from v$session where status = 'ACTIVE' and username is not null)

     , (select count(*) inactive from v$session where status = 'INACTIVE')
     , (select count(*) killed from v$session where status = 'KILLED')) loop
dbms_output.put_line('Active Sessions');
dbms_output.put_line ('---------------');
dbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed ');
  end loop;
  dbms_output.put_line( chr(13) );
 dbms_output.put_line( 'Sessions Waiting' );
  dbms_output.put_line( chr(13) );
dbms_output.put_line('Count      Event Name');
dbms_output.put_line('-----      -----------------------------------------------------');
for rec in c4
loop
dbms_output.put_line(rec.cnt||'          '||rec.event);
end loop;
dbms_output.put_line( chr(13) );

dbms_output.put_line('-----      -----------------------------------------------------');

dbms_output.put_line('TOP Physical i/o, logical i/o, memory and CPU processes');
dbms_output.put_line ('---------------');
for rec in c7
loop
dbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used);
end loop;

dbms_output.put_line('------------------------------------------------------------------');

dbms_output.put_line('TOP CPU users by usage');
dbms_output.put_line ('---------------');
for rec in c8
loop

dbms_output.put_line (rec.username||'--'||rec.cpu_usage_sec);
dbms_output.put_line ('---------------');
end loop;

end;

Find UNUSABLE indexes in Oracle DB 11g

Find all unusable indexes in db

select owner, index_name from dba_indexes where status='UNUSABLE';

Rebuild index

alter index <INDEX_NAME> rebuild;