performance related queries
----------------------------
Rem DATABASE BUFFER CACHE:
Rem Formula =1-((Physical Reads - Physical Reads Direct - Physical Reads Direct [LOB] / Session Logical Reads)

Rem Physical Reads : This statics indicates the number of data blocks (i.e Tables,Indexes, and Rllback segments) 
Rem 		     read from disk into buffer cache since instance startup.

Rem Physical Reads Direct: This statics indicates the number of reads that bypassed the buffer cache 
Rem because the data blocks were read directly from disk instead. Because direct physical reads are done intentionally
Rem by Oracle when using certain features like Parallel Query, these reads are subtracted from the 'Physical Reads' value
Rem When buffer cache hit ratio is calculated. Otherwise including these direct reads in buffer cache hit ratio
Rem calculation would result in an artificially low hit ratio.

Rem Physical Reads Direct (LOB) : This statics indicates the number of reads that bypassed the buffer cache
Rem because the data blocks were associated with a LOB datatype.

Rem Session Logical Reads :This statics indicates the number of times a request for a data block was stisfied by using 
Rem a buffer that was already cached in the Buffer cache. For read consistency, some of these  buffers may have 
Rem contained data from rollback segments.

Rem A well-tuned OLTP system should have Database buffer cache hit ratios of 90% or higher

Select 1 - ((physical.value - direct.value - lobs.value ) / logical.value ) "Buffer Cache Hit Ratio"
From v$sysstat physical,
     v$sysstat direct,
     v$sysstat lobs,
     v$sysstat logical
where physical.name = 'physical reads'
And   direct.name   = 'physical reads direct'
And   lobs.name     = 'physical reads direct (lob)'
And   logical.name  = 'session logical reads';

Rem OR

Select (1 - ((physical.value - direct.value ) / logical.value ))*100 "Buffer Cache Hit Ratio"
From v$sysstat physical,
     v$sysstat direct,
     v$sysstat logical
where physical.name = 'physical reads'
And   direct.name   like 'physical reads dir%'
And   logical.name  = 'session logical reads';

---------------------------------------------------------------------------------------------------------------------------
rem Free buffers inspected :no of buffer cache buffers inspected by user server process before finding a free buffer
rem Free buffer wait :no of waits experienced by user server process during free buffer inspected activity.
rem These waits occur whenever the server process had to wait for database writer to write a dirty buffers to disk
rem Buffer Busy Waits :No of times user server process waited for a free buffer to become available.
rem These waits occurs whenever a buffer req by user server process is already in memory,but is in use by another process.
Rem These waits rem can occur for rollback segment buffers as well as data and index buffers.

select name,value
from v$sysstat
where name in ('free buffer inspected')
union
select event,total_waits
from v$system_event
where event in ('free buffer waits','buffer busy waits');

---------------------------------------------------------------------------------------------------------------------------

Rem LIBRARY CACHE

Rem The RELOADS and INVALIDATIONS columns of v$librarycache are also useful for Library cache tuning.
Rem Oracle uses the term 'get' to refer a type of lock, called a 'Parse Lock', 
Rem that is taken out on an object during the parse phase for the statements that references that object.
Rem Each time a statement is parsed, the value for GETS in the  v$librarycache view is incremented by 1.
Rem The column GETHIT stores the number of times that the SQL and PL/SQL statements issued by appl found 
Rem a parsed copy of themselves already in memory.When this occurs, there is no parsing of the statement required.
Rem The Ratio of parsed statements (GETS) to those that did not require parsing (GETHITS) is calculated in the GETHITRATIO
Rem column of v$librarycache. The higher this number is, the better the application is performing.
Rem A well tuned OLTP systems can expect to have GETHITRATIOs of 90% or higher for the SQL Area portion of Libr Cache.

Rem PINS like GETS, are also related to locking. However, while GETs are associated with locks that occur at parse time, 
Rem PINS are related to locks that occur at execution time. 
Rem Each time a statement is executed, the value for PINS is incremented by 1.
Rem A well tuned OLTP systems can expect to have PINHITRATIOs of 90% or higher for the SQL Area portion of Libr Cache.


select NAMESPACE,GETHITRATIO,PINHITRATIO,RELOADS,INVALIDATIONS
from v$librarycache
where NAMESPACE in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');

---------------------------------------------------------------------------------------------------------------------------
Rem Reloads
Rem RELOADS column shows the number of times that an executed statement had to be re-parsed because the Library cache had 
Rem aged out or invalidated the parsed version of the statement. Reload activity can be monitored by comparing the number
Rem of statements that have been executed(PINS) to the number of those statements that required a reload (RELOADS);
Rem Reload ratio should be less than 1%

Rem INVALIDATIONS: occur when a cached SQL statement is marked as invalid and 
Rem therefore forced to parse even though it was already in the Library Cache 
Rem E.g Recompliling a View that was used by previously cached SQL statements will cause those cached statements 
Rem to be markd as Invalid.Therefore, any subsequent statements that use this view will need to be parsed 
Rem even though an exact copy of that statement may already be cached.High value means addnl Overhead for the application.

select (sum(reloads)/sum(PINS))*100 "Reload Ratio"
from v$librarycache;
---------------------------------------------------------------------------------------------------------------------------

Rem DICTIONARY CACHE

Rem The Dict Cache hit ratio showws how frequently the application finds the data dictionary information it needs in memory,
Rem instead of having to read it from disk.This hit-ratio infomn is contained in dynamic performance view called V$ROWCACHE

Rem Oracle recommends that consideration be given to tuning the Shared Pool 
Rem if the Overall Data Dict Cache  Ratio is less than 85%

select 1-(sum(getmisses)/sum(gets)) "Data Dictionary Hit Ratio"
from v$rowcache;
---------------------------------------------------------------------------------------------------------------------------

Rem To estimate the Shared Pool size
Rem current value set is 170MB

select sum(SHARABLE_MEM) from  v$db_object_cache;

Rem SUM(SHARABLE_MEM)
Rem -----------------
Rem         100780229

select sum(SHARABLE_MEM) from v$sqlarea
where EXECUTIONS >10;

Rem SUM(SHARABLE_MEM)
Rem -----------------
Rem          12168574

select (98687841+12168574)/1024/1024 "Size in MB" from dual;

Rem Size in MB
Rem -----------------------------
Rem                   105.720916

Rem Estimated shared pool is 105 Mb ,where in the size allocated is 170MB


---------------------------------------------------------------------------------------------------------------------------
Rem Performance of Redo log Buffer can be measured in terms of the number and length of waits that user server 
Rem processes experience when trying to place entries in the Redo log Buffer.

Rem The statics redo entries reports the number of entries that have been placed into the Redo Log Buffer by 
rem the user server Process since instance startup.
rem The statics redo buffer allocation retries refers to the number of times user server process had to wait and 
rem then retry placing their entries in the Redo log Buffer because LGWR had not yet written the current entries to 
Rem the Online Redo log.Using these 2 statistics ,it is possible to calculate a Redo Log Buffer Retry Ratio

Rem Oracle recommends that this Redo log Buffer Retry Ratio should be less than 1 Percent.

select (retries.value/entries.value)*100 "Redo Log Buffer Retry Ratio"
from v$sysstat retries ,v$sysstat entries
where retries.name='redo buffer allocation retries'
and entries.name ='redo entries';

---------------------------------------------------------------------------------------------------------------------------
Rem How Often LGWR is waiting for a Redo Log swith to occur when moving from the current Online Redo log to the Next.
Rem During a Redo log Switch,LWGRBcannot empty the contents of the Redo Log buffer. this may cause user server process 
Rem to exprience a wait when trying to acess the redo log Buffer.This wait generates a retry req as explained above.
Rem High or increasing values for 'redo log space requests' indicates that your Redo Logs may be too small and 
Rem thus contibuting to the edo log Buffer Retry Ratio.

select name ,value
from v$sysstat
where name ='redo log space requests';

---------------------------------------------------------------------------------------------------------------------------

Rem The log buffer space statistics indicates how long the user session had to wait to place an entry in the Redo log Buffer
Rem because LGWR had not yet finished writing the contents of the Redo Log Buffer to the Online Redo log 
Rem To monitor this activity on a per-user basis:

Rem HIgh or increasing values for waits related to log buffer space may indicate that the Redo log Buffer needs to be tuned.

select username,wait_time,seconds_in_wait,state
from v$session_wait,v$session
where v$session_wait.sid=v$session.sid
and event  like '%log buffer space%';

Rem the Default value for LOG_BUFFER is 512KB or 128K*the no of  CPU's in the server ,which ever is greater.


Rem create table without generating any redo informatiom
Create table aa as select * from bb unrecoverable;
---------------------------------------------------------------------------------------------------------------------------

Rem High value for this statistics indicates that your application is performing frequent full table scans and may benefit from an increase in the DB_FILE_MULTIBLOCK_READ_COUNT default values is 8

select name,value from v$sysstat
where name ='table scans (long tables)';

---------------------------------------------------------------------------------------------------------------------------
Rem V$session _longops view that can be used for monitoring full table scan activity.

select Username,opname,round(((totalwork-sofar)/totalwork),4)*100 "Pct Remaining"
from v$session_longops
where time_remaining >0;

---------------------------------------------------------------------------------------------------------------------------
Rem  Following query shows to identify all the segments that are already using 95% of the Oracle blocks allocated to them.

Select Owner,table_name,1-(empty_blocks/(empty_blocks+blocks)) "%Blocks Used"
from Dba_tables
where Owner !='SYS'
and 1-(empty_blocks/(empty_blocks+blocks)) > .95
order by 1;

Rem Adding this extent to above tables will avoid the dynamic allocation that would have occured during the next row insert.
Rem For optimal performance,Oracle states that the maximum number of extents for a segment in a 
Rem dictionary managed tablespace should not exceed 1,000. For Segments stored in 
Rem Locally Managed tablespaces can have thousands of extents with no negative impact on performance.
Rem You can use the following query  to pre-allocate an additional extent to the table 
Rem Alter table appl.sales allocate extent;

---------------------------------------------------------------------------------------------------------------------------
Rem Oracle Recommends that 95% of all sorts happen in Memory, otherwise increase sort_area_size
Rem Create a locally managed temporary tablespace for sorting on disk
Rem UNIFORM SIZE should be equal to,or a multiple of,SORT_AREA_SIZE+1 block.This allow each chunk to be written with one I/O.

select (mem.value/(disk.value + mem.value))*100 "In memory Sort Ratio "
from v$sysstat mem, v$sysstat disk
where mem.name='sorts (memory)'
and disk.name = 'sorts (disk)';

---------------------------------------------------------------------------------------------------------------------------
Rem Query shows how many users are currently using the sort segment stored in the Temp tablespace.
Rem In addition,the Largest single sort by an individual user was the equivalent of max_sort_blocks value oracle block size.
Rem This max_sort_blocks value can be useful when trying to determine what 
Rem the optimal setting for sort_area_size and sort_area_retained_size should be,By setting =max_sort_blocks * db_block_size

Select tablespace_name,current_users,max_sort_blocks
from v$sort_segment;


---------------------------------------------------------------------------------------------------------------------------
Rem v$sort_segment Shows over all sort operations well,it does not allow you to see which individual users are 
Rem causing large sorts to disk. However, the v$sort_usage view does include a USER Column which can be used 
Rem to monitor sort activity by user as shown by the following Query.

select user,tablespace,blocks
from v$sort_usage
order by blocks

---------------------------------------------------------------------------------------------------------------------------
Rem Knowing which user is which query is performing most sorting

select sess.username,sql.sql_text,sort.blocks
from v$session sess, v$sqltext sql,v$sort_usage sort
where sess.serial# = sort.session_num
and sort.sqladdr=sql.address
and sort.sqlhash = sql.hash_value
and sort.blocks >200;

---------------------------------------------------------------------------------------------------------------------------
Rem Rollback Segments
Rem Using V$system_event to measure Undo Header contention
Rem Ideally, the value of the average_wait statistics should be consistently at or near Zero

select event,total_waits,time_waited,average_wait
from V$system_event
where event like '%undo%'

---------------------------------------------------------------------------------------------------------------------------
Rem Using V$waitstat to measure Undo Header contention
Rem Included in this statistics are counts for the number of times application users have experienced a wait for 
Rem Access to the header block of rollback segments.
Rem Ideally, the counts for these waits should be consistently at or near Zero

Select class,count
from v$waitstat 
where class in ('undo header','system undo header');

Rem CLASS                   COUNT
Rem ------------------ ----------
Rem system undo header          0
Rem undo header            184935

Rem The Statistics for system undo header relates to the system rollback segment.
Rem The undo header statistics is for the non system rollback segments.
Rem Query indicates no contention for the system rllback system's header,
Rem but that where 184935 occasions where waits occurred for access the headers of non-system rollback segments.

---------------------------------------------------------------------------------------------------------------------------
Rem Using V$Rollstat to measure Undo Header contention
Rem GETS : Number of times a user's server process needed to access the rollback segment header and did so successfully
Rem WAITS: Number of times a user's server process needed to access the rollback segment header and experienced a wait
Rem Oracle Recommends that OLTP system have a rollback segment header get ratio of 95% or higher for all rollback segments.

select n.name,s.usn,
	Decode(s.waits,0,1,
		1-(s.waits/s.gets))*100 "RBS Header Get Ratio"
from v$rollstat s,v$rollname n
where s.usn = n.usn
order by usn;

---------------------------------------------------------------------------------------------------------------------------
Rem To see how often transaction have wrapped from one extent to another since instance startup
Rem Frequent wrapping indiactes that the extent sizes of each rollback segment may be too small

select n.name,s.usn,s.wraps
from v$rollname n,v$rollstat s
where n.usn=s.usn;
---------------------------------------------------------------------------------------------------------------------------
Rem The statistics undo segment extension in the v$system_event dynamic performance view records 
Rem how long user server process had to wait for rollback segments to add extents to handle transaction processing


Select event,total_waits,time_waited,average_wait
from v$system_event 
where event like '%undo%';

Rem EVENT                          TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
Rem ------------------------------ ----------- ----------- ------------
Rem undo segment extension             4121731       34988   .008488667

Rem The output from above query indicates that application users waited 34988 
Rem times for rollback segments to add extents in order to store their before images.
Rem High or steadily increasing values for these wait statistics indicate that you either have too few rollback segments,
Rem too small rollback segments,or both

---------------------------------------------------------------------------------------------------------------------------
Rem To see how often transaction have wrapped from one extent to another since instance startup
Rem Frequent wrapping indiactes that the extent sizes of each rollback segment may be too small
Rem If your rollback segments are frequently adding extents to support the transaction activity in your database, 
Rem then the rollback segments are probably too small

Select n.name,s.usn,s.extends
from v$rollname n,v$rollstat s
where n.usn=s.usn;

---------------------------------------------------------------------------------------------------------------------------

prompt ******************************************************************************************************
prompt Query to Find Empty Blocks below HWM so as to avoid scanning empty blocks during FTS
prompt Needs Export,drop or truncate and then reimport the table OR Use Alter Table....Move to rebuild Table
prompt ******************************************************************************************************

Select owner,TABLE_NAME,blocks "Actual Blocks Used Below HWM",
Round((t.avg_row_len*t.num_rows)/s.block_size,0) "Est Blocks Needed Below HWM",
blocks - Round((t.avg_row_len*t.num_rows)/s.block_size,0) "Wasted Blocks Below HWM"
From dba_tables t,(select VALUE as block_size from v$parameter where NAME='db_block_size') s
where (blocks - Round((t.avg_row_len*t.num_rows)/s.block_size,0)) >1;




prompt ******************************************************************************************************
prompt Physical Read statistics
prompt ******************************************************************************************************
select s.file#||'D' "File# + Type",
d.name "Data File" ,s.PHYRDS,s.PHYWRTS,
s.AVGIOTIM,s.MINIOTIM,
s.MAXIOWTM,s.MAXIORTM
from v$filestat s,v$datafile d
where s.file#=d.file#
union 
select s.file#||'T' "File# + Type",
t.name "Data File" ,s.PHYRDS,s.PHYWRTS,
s.AVGIOTIM,s.MINIOTIM,
s.MAXIOWTM,s.MAXIORTM
from v$filestat s,v$tempfile t
where s.file#=t.file#
order by 3;


prompt ******************************************************************************************************
prompt See Which tablespaces are having highest Reads and Writes and place this datafiles on different disk
Prompt Balancing the IO

prompt Physical Write statistics
prompt ******************************************************************************************************
select s.file#||'D' "File# + Type",
d.name "Data File" ,s.PHYRDS,s.PHYWRTS,
s.AVGIOTIM,s.MINIOTIM,
s.MAXIOWTM,s.MAXIORTM
from v$filestat s,v$datafile d
where s.file#=d.file#
union 
select s.file#||'T' "File# + Type",
t.name "Data File" ,s.PHYRDS,s.PHYWRTS,
s.AVGIOTIM,s.MINIOTIM,
s.MAXIOWTM,s.MAXIORTM
from v$filestat s,v$tempfile t
where s.file#=t.file#
order by 4;


prompt ******************************************************************************************************
prompt Stats to Identify Row chaining and Migration
prompt ******************************************************************************************************

Select name,value
From v$sysstat
Where name='table fetch continued row';


prompt *****************************************************************************
prompt This script shows the user that has performed the most physical
prompt  disk reads. You use the columns sid and serial# as input into
prompt  dbms_system.set_sql_trace_in_session to commence tracing the 
prompt  offending user.
prompt *****************************************************************************
col sql_text for a50 word_wrap
col username for a10
col OSUSER for a10


SELECT ses.sid, ses.serial#,ses.username, ses.osuser, ses.process,t.sql_text
   FROM v$session ses, v$sess_io sio,v$sqlarea t
  WHERE ses.sid  = sio.sid
    AND(ses.sql_address=t.address AND ses.sql_hash_value=t.hash_value)
    AND nvl(ses.username,'SYS') not in ('SYS', 'SYSTEM')
    AND sio.physical_reads = (SELECT MAX(physical_reads)
                                FROM v$session ses2, v$sess_io sio2
                               WHERE ses2.sid = sio2.sid
                                 AND ses2.username 
                                  NOT IN ('SYSTEM', 'SYS'));

prompt *****************************************************************************
prompt find high parsing statements
prompt *****************************************************************************
select s.sid,s.serial#,s.username,s.osuser,t.sql_text, t.parse_calls, t.executions 
from v$sqlarea t,v$session s
where (s.sql_address=t.address AND s.sql_hash_value=t.hash_value)
AND parse_calls > 100 and executions < 2*parse_calls
Order by s.sid;

select s.sid,s.serial#,s.username,s.osuser,t.sql_text, t.parse_calls, t.executions,s.status,t.ROWS_PROCESSED
from v$sqlarea t,v$session s
where (s.sql_address=t.address AND s.sql_hash_value=t.hash_value)
AND parse_calls > 100 
Order by t.parse_calls desc;