Oracle Performance Tuning views

alter session set sql_trace=TRUE
alter system set timed_statistics=TRUE
SELECT * FROM V$PROCESS
select username,addr,spid,program,terminal,traceid from v$process
SELECT * FROM V$SYSTEM_EVENT
select event,total_waits from v$system_event
SELECT * FROM V$ROWCACHE
SELECT * FROM V$STATNAME

select st.name,se.sid,se.statistic#,sy.username from v$statname st,v$sesstat se,v$session sy where st.statistic#=se.statistic# and se.sid=sy.sid

SELECT * FROM V$SESSTAT
SELECT * FROM V$STATNAME

select event,wait_time,state from v$session_wait
select event,state from v$session_wait
select event from v$session_wait where wait_time=0
SELECT * FROM V$DATABASE
SELECT * FROM V$LIBRARYCACHE

select gets,pins,reloads,namespace,dlm_invalidations from v$librarycache

SELECT * FROM V$SQL
SELECT * FROM V$SQLAREA
select sql_text,users_executing,executions,loads from v$sqlarea

select sum(pins) “exec”,sum(reloads) “miss”,sum(reloads)/sum(pins) from v$librarycache

select gets,pins,reloads,namespace,gethitratio,invalidations from v$librarycache

select count(*) from lokesh.emp
select sum(pins) “exec”,sum(reloads) “miss”,sum(reloads)/sum(pins) from v$librarycache
analyze table lokesh.emp compute statistics
select gets,pins,reloads,namespace,gethitratio,invalidations from v$librarycache

SELECT * FROM V$DB_OBJECT_CACHE
select owner,name,db_link,namespace from v$db_object_cache
select sum(sharable_mem) from v$db_object_cache

SELECT * FROM V$SHARED_POOL_RESERVED

select * from v$db_object_cache where sharable_mem>10000 and (type=’PACKAGE%’ or type=’FUNCTION’ or type=’PROCEDURE’) and kept=’NO’

alter system flush shared_pool
select sql_text from v$sql

select sum(value) || ‘bytes’ “tot sess mem” from v$mystat,v$statname where name=’session uga memory’ and v$mystat.statistic#=v$statname.statistic#

Latches are of two types:

Willing to wait :- it will wait and then request this process will be continously carried out till the latch is not available.

Immediate:doesn;t wait but continous process other instruictions

desc v$latch