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

--
-- Segment Advisor for Individual Segments
--

-- 1. Replace all occurances of [OWNER] with the owner of the segments.
-- 2. Replace [TABLE_NAME] or [INDEX_NAME] with the segment to be analyzed.

set echo off
set feedback off
set verify off

davidhooey

-- Shared Pool
select * from table(dbms_xplan.display_cursor('SQL_ID',null,'ALL'));

-- AWR
select * from table(dbms_xplan.display_awr('SQL_ID',null,null,'ALL'));
select * from table(dbms_xplan.display_awr('SQL_ID',null,DBID,'ALL'));

davidhooey

select
    name,
    round(time_secs, 2) time_secs,
    case when time_secs = 0 then
        0
    else
        round(time_secs*100 / sum(time_secs) Over(), 2)
    end pct
from
    (

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

drop table skewed;
drop sequence skewed_sequence;

create table skewed
(
    id number,
    type varchar2(50),
    constraint skewed_id_pk primary key (id)
);

davidhooey

drop table million;
drop sequence million_sequence;

create table million
(
    id number,
    type varchar2(50),
    constraint million_id_pk primary key (id)
);

davidhooey

select dbms_sqltune_util0.sqltext_to_sqlid('SQL_TEXT_HERE'||chr(0)) sql_id from dual;

davidhooey

-- Shared Pool
select *
from
    (
        select
            case when executions = 0 then 0
            else round(elapsed_time/executions, 3)
            end "ElapsedPerExec(ms)",
            elapsed_time "ElapsedTime (ms)",
            executions "Executions",

davidhooey

set timing on;
set linesize 120;


--
-- Bind Variable Setup
--
variable Bind1 number;
variable Bind2 varchar2(2000);