PGA,SGA and Memory target Estimate.

select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;


select memory_size,memory_size_factor,estd_db_time from v$memory_target_advice;


select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;

Explain Plan for Oracle Query

Process 1

select * from table(dbms_xplan.display_cursor('SQL_ID'));


Process 2
explain plan for Query

@?/rdbms/admin/utlxpls

Process 3

select * from table(dbms_xplan.display('SQL_ID'));

Oracle Performance Tuning Cheat Sheet

buffer cache hit ratio

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio" FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads';

====================================================================

fragmentation in tables


select table_name,round((blocks*8),2) "size (kb)" ,
                            round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
                            (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and table_name='FUNDSTRANSFER_LOG'
order by 4 desc

===========================================================================

data dictionary cache hit ratio


SELECT (1 - (SUM (getmisses) / SUM (gets))) * 100 "Hit Ratio"
 FROM v$rowcache;



=================================================================================

library cache hit ratio

SELECT SUM (pins) / (SUM (pins) - SUM (reloads)) * 100 "Hit Ratio"
 FROM v$librarycache;


===========================================================================================

To find out physical reads on a query 



SELECT disk_reads, sql_text
 FROM v$sqlarea
 WHERE disk_reads > 1000
ORDER BY disk_reads DESC;

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session
where status='ACTIVE' and type<>'BACKGROUND' and last_call_et>30;

Backup Oracle Database Stats

Backup Database stats:-

a)Create the STATS table only one time.
EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=> 'SYS',stattab=>'DB_NAME_STATS');

b) Export the full database stats as below.
exec dbms_stats.EXPORT_DATABASE_STATS(STATTAB=>'DB_NAME_STATS',statid=>'DB_NAME_STATS_2SDATE',STATOWN=>'SYS');


Please change the above steps as per requirement.

VMWARE XCLOCK Issue

.runInstaller exist with error Or xclock is not working for VMAWRE,Try below steps and it should work fine.
Steps to resolve this issue in VM:

1) login into root user( su -l root)

2) execute this command : xhost +SI:localuser:oracle

3) login to the oracle user

4) execute ./runInstaller

How to purge AUD$?

1. Count the number of rows
------------------------------------
select count(*) from AUD$;
it will show the number of rows

2. Then take the export backup
--------------------------------------
NOTE:-export backup must be taken inside the perticular mountpoint where it's intended to keep the DMP file

exp file=/bkp/audit_bkp/exp_aud_Date.dmp log=/bkp/audit_bkp/exp_aud_Date.log tables=AUD$

password: / as sysdba

After Export Backup Check the number of rows the difference may b there.
There may or may n't b large difference

3.Clear the Table and Keep the Structure
------------------------------------------
Truncate table AUD$

4.Check again
-------------------------------------------
select count(*) from AUD$;

How to purge old AWR Snapshots??

Make sure there is adequate space for Archive Generation.

Procedure for AWR Purging

 1) Run following script as SYS user to take export backup
    cd $ORACLE_HOME/rdbms/admin
    @awrextr.sql

@?/rdbms/admin/awrextr


 2) It will ask for dbid
    Give dbid for database

3) It will ask for num_days
    press enter,by default it will give all snaps generated

4) It will ask for begin_snap(Snap_id from where u want start export backup)

5) It will ask for end_snap(Snap_id upto which u want to take export backup)

    export dump will be taken from begin_snap to end_snap
6) It will ask for Directory name
    Give AWR_PURGE

7) It will ask for file_name,For file name use following conventions
    awr_dbname_(begin_snap_date)_(end_snap_date)

8) It will generate export backup from begin_snap to end_snap
    once export backup completed successfully.

Do following Steps for deleting snaps

Ex.

exec  dbms_workload_repository.DROP_SNAPSHOT_RANGE(115112,115440);

Once it will complete successfully please run  awrrpt.sql to confirm.