ryanthames

-- table info is only as up to date as last time stats were gathered (last_analyzed)
select t.owner, t.table_name, t.num_rows, t.avg_row_len,
    t.blocks as blocks_below_hwm, t.empty_blocks,
    s.blocks as segment_blocks,
    s.bytes / 1048576 as size_in_mb,
    to_char(t.last_analyzed, 'YYYY-MM-DD HH24:MI') as last_analyzed
from all_tables t, dba_segments s
where t.owner = s.owner and t.table_name = s.segment_name
and t.owner = 'SCHEMA_NAME';

ryanthames

with statements as
(
    select force_matching_signature,
        count(1) over (partition by force_matching_signature) as statement_count,
        row_number() over (partition by force_matching_signature order by last_load_time desc) as row_index,
        parsing_schema_name,
        sql_text
    from v$sql
    where force_matching_signature > 0
    and force_matching_signature <> exact_matching_signature

ryanthames

-- Value is in microseconds
-- Run this query at a couple regular intervals and take the delta to get
-- an idea of how much hard parsing is going on
select sn.statistic#, sn.name, s.value
from v$sysstat s, v$statname sn
where s.statistic# = sn.statistic#(+)
and sn.name in ('parse time cpu', 'parse count (hard)');

ryanthames

-- if an index isn't being used or is lightly used, can it be dropped to free up resources?
-- only reflective of statements in shared SQL area, to get a more accurate reflection on
-- indexes used in the DB, run this query several times a day and dump all the output into
-- a spreadhseet or something.
with index_usage as
(
    select pl.sql_id, pl.object_owner, pl.object_name, pl.operation,
        pl.options, count(1) as use_count
    from v$sql_plan pl

ryanthames

-- REPLACE <<sql id>> with actual sql id
select plan_table_output from table(dbms_xplan.display_cursor('<<sql id>>', null, 'typical'));

ryanthames

select pl.object_owner, pl.object_name,
    pl.sql_id, q.sql_text, q.module,
    pl.operation, pl.options, pl.cost, pl.cpu_cost, pl.io_cost,
    q.executions
from v$sql_plan pl, v$sql q
where pl.sql_id = q.sql_id
and ((pl.operation = 'TABLE ACCESS' and pl.options = 'FULL')
or (pl.operation = 'INDEX' and pl.options = 'FAST FULL SCAN')
or (pl.operation = 'INDEX' and pl.options = 'FULL SCAN (MIN/MAX)')
or (pl.operation = 'INDEX' and pl.options = 'FULL SCAN'))

ryanthames

select * from
(
    select sql_id, sql_text, executions,
        elapsed_time, cpu_time, buffer_gets, disk_reads,
        elapsed_time / executions as avg_elapsed_time,
        cpu_time / executions as avg_cpu_time,
        buffer_gets / executions as avg_buffer_gets,
        disk_reads / executions as avg_disk_reads
    from v$sqlstats

ryanthames

select s.sid, s.username, s.osuser,
    s.machine, s.process, s.program, s.module,
    q.sql_text, q.optimizer_cost,
    s.blocking_session, bs.username as blocking_user,
    bs.machine as blocking_machine, bs.module as blocking_module,
    bq.sql_text as blocking_sql, s.event as wait_event,
    q.sql_fulltext
from v$session s, v$sql q, v$session bs, v$sql bq
where s.sql_id = q.sql_id
and s.blocking_session = bs.sid(+)

ryanthames

select s.sid, s.username, s.osuser,
    to_char(sm.begin_time, 'HH24:MI:ss') as interval_start,
    to_char(sm.end_time, 'HH24:MI:ss') as interval_end,
    s.machine, s.process, s.program, s.module,
    sm.cpu, sm.pga_memory, sm.logical_reads, sm.physical_reads,
    sm.hard_parses, sm.soft_parses,
    s.logon_time
from v$session s, v$sessmetric sm
where sm.session_id = s.sid
and s.type = 'USER'

ryanthames

select username, osuser, program, module,
    machine, process,
    count(1) as login_count
from v$session
where type = 'USER'
group by username, osuser, program, module,
    machine, process;