davidhooey

-- Shared Pool
select
    event,
    time_waited "time_waited(s)",
    case when time_waited = 0 then 
        0
    else
        round(time_waited*100 / sum(time_waited) Over(), 2)
    end "percentage"
from

davidhooey

select distinct
    -- Snapshot ID
    min(blocked.snap_id)      as first_snap_id,
    max(blocked.snap_id)      as last_snap_id,

    -- Sample ID and Time
    min(blocked.sample_id)    as first_sample_id,
    min(blocked.sample_id)    as last_sample_id,
    to_char(
        min(blocked.sample_time),

davidhooey

--
-- Segment Advisor for All Segments in Schema
--

-- 1. Grant the ADVISOR and SELECT ANY DICTIONARY roles to the schema owner.
-- 2. Execute the script as the schema owner.
-- 3. Revoke the ADVISOR and SELECT ANY DICTIONARY roles from the schema owner.

set echo off
set feedback off

davidhooey

create or replace trigger [SCHEMA_NAME]_startsqltracing after logon on [SCHEMA_NAME].schema
begin
    execute immediate 'alter session set timed_statistics=true';
    execute immediate 'alter session set max_dump_file_size=unlimited';
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
end;
/


create or replace trigger [SCHEMA_NAME]_endsqltracing before logoff on [SCHEMA_NAME].schema

davidhooey

NOTE:
Oracle9i:	estimate_percent defaults to NULL which is COMPUTE
Oracle10g:	estimate_percent defaults to to_estimate_percent_type (get_param('ESTIMATE_PERCENT'))

-- ********************************
-- * Generating Schema Statistics *
-- ********************************
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree)
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO')
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1') -- No Histograms

davidhooey

select
    snap_id,
    sql_id,
    name,
    position,
    datatype_string,
    to_char(last_captured,'YYYY-MM-DD HH24:MI:SS') last_captured,
    value_string
from
    dba_hist_sqlbind

davidhooey

# -*- mode: ruby -*-
# vi: set ft=ruby :

# -----------------------------------------------------------------------------
#  Rails Development Vagrantfile
# -----------------------------------------------------------------------------
#
# Server Info:
#
#   Ubuntu 12.04 32-bit

davidhooey

select
    -- Session causing the block
    blockers.blocker_instance_id as blocker_instance_id,
    blocker.sid                  as blocker_sid,
    blocker.serial#              as blocker_serial#,
    blocker.username             as blocker_username,
    blocker.status               as blocker_status,
    blocker.machine              as blocker_machine,
    blocker.program              as blocker_program,
    blocker.sql_id               as blocker_sql_id,

davidhooey

select
    *
from
(
    select
        module,
        sql_id,
        child_number,
        plan_hash_value,
        executions,

davidhooey

select
    *
from
(
    select
        ss.module,
        ss.snap_id,
        ss.sql_id,
        ss.plan_hash_value,
        ss.executions_total,