Quick Steps to fine tune a Oracle database

At first ADDM and AWR reports need to be analyzed to see the real cause on database load and the below steps could be performed later.

1.    Purging recycle Bin
purge recyclebin; -- It Purges the Dropped Objects and releases storage

2.    Space Issues

select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name;

Pct-Free -- It should not be <10% unless it’s a less prioritized or unused tables space

select * from v$sgastat where NAME = 'free memory';

shared pool, large pool, java pool should be predominantly large

3.    Check for any Anonymous  or long running process
select s.sid,s.serial#,s.username,s.osuser   from v$session s,v$process p  where s.paddr=p.addr
select * from v$session where osuser!='oracle';

OSUSER should not be Anonymous or any other Adhoc Users like DESKTOP, Standalone device like Laptop or Mobile

If you identify any Long running process , Go ahead , kill with prior notice to stakeholders

ALTER SYSTEM KILL SESSION '36, 57056, @1' IMMEDIATE;

4.    Result Cache
SELECT dbms_result_cache.status() FROM dual;
Result Cache can be enabled


5.    Trace at session level should be disabled which may increase the load on Log files
ALTER SESSION SET sql_trace = false;