Few Important Oracle Queries

I found the one more excellent query which can help the oracle dba in doing performance tuning.

--> Find the Large Table Scans:

SELECT table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
FROM (SELECT a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
SUM(c.executions ) executions,
COUNT( DISTINCT a.hash_value ) statement_count,
COUNT( * ) reference_count
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
WHERE a.object_owner (+) = b.owner
AND a.object_name (+) = b.segment_name
AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
AND a.operation LIKE '%TABLE%'
AND a.options = 'FULL'
AND a.hash_value = c.hash_value
AND b.bytes / 1024 > 1024
GROUP BY a.object_owner, a.object_name, a.operation, b.bytes /
1024, b.segment_type
ORDER BY 4 DESC, 1, 2 )

--> Full Table Scans:

select ss.username||'('||se.sid||') ' "USER_PROCESS",
sum(decode(name,'table scans (short tables)',value)) "SHORT_SCANS",
sum(decode(name,'table scans (long tables)', value)) "LONG_SCANS",
sum(decode(name,'table scan rows gotten',value)) "ROWS_RETRIEVED"
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
OR name like '%table scans (long tables)%'
OR name like '%table scan rows gotten%' )
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') ';

--> Top 10 Sessions by Physical Reads:

select distinct c.osuser,c.username,c.sid, b.name, a.value
from v$sesstat a,
v$statname b,
v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and nvl(a.value,0) > 0
and c.username not in ('SYS','SYSTEM')
and b.name = 'physical reads'
and c.type <> 'BACKGROUND'
and rownum <= 10
order by a.value desc;

--> Top 10 Sessions by Physical Writes:

select distinct c.osuser,c.username,c.sid, b.name, a.value
from v$sesstat a,
v$statname b,
v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and nvl(a.value,0) > 0
and c.username not in ('SYS','SYSTEM')
and b.name = 'physical writes'
and c.type <> 'BACKGROUND'
and rownum <= 10
order by a.value desc;

TABLESPACE USAGE:

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc

Based on the few of these queries who can plan according and do the performance tuning.