FAQ
Arrgggg...

So anyway, I am thinking, hey it's high time I start using DBMS_STATS
instead of my own procedure so I kick of the following (Oracle 8.1.7.4).
After the first run I have SYS and SYSTEM stats on indexes and on other
schemas with NO STATS it just ignored those tables even though you can see I
have GATHER EMPTY below. So I kick it off again and guess what, it starts
analyzing the tables it missed the first time, including SYS and SYSTEM.
Guess I am going to use DBMS_STATS.GATHER_TABLE_STATS and be a bit more
specific about what I get.

define estimate_percent=5

declare

begin

Can easily change to gather_schema_stats (make sure you add schema
name)
dbms_stats.gather_database_stats(
&estimate_percent,FALSE,'FOR ALL COLUMNS SIZE 1',
NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER EMPTY');

dbms_stats.gather_database_stats(

&estimate_percent,FALSE,'FOR ALL COLUMNS SIZE 1',
NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER STALE');

exception

when others then

dbms_output.put_line(dbms_utility.format_error_stack);
end;

Ethan Post
perotdba (AIM), epost1 (Yahoo)

-----Original Message-----
Sent: Friday, October 04, 2002 3:56 PM
To: ORACLE-L_at_fatcity.com
Cc: Ethan.Post_at_ps.net
indexes?
Importance: High

I seem to recall this is a bug. You may want to check MetaLink.

In any case, you don't want to analyze SYS on any version
of Oracle. ( yet )

Don't see what harm in having stats on SYSTEM tables though.

It's just a DBA account.

Jared

"Post, Ethan"
Sent by: root_at_fatcity.com
10/04/2002 02:23 PM
Please respond to ORACLE-L



To: Multiple recipients of list ORACLE-L
cc:
Subject: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and

SYSTEM indexes?

Looks like this is the case, does it know something I don't know? Are
indexes OK to analyze in the SYS and SYSTEM schemas? Looks like is
correctly does not do tables.


Ethan

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Post, Ethan
INET: Ethan.Post_at_ps.net

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Jesse, Rich at Oct 7, 2002 at 3:29 pm
    Yes, the DBMS_STATS package is very quirky in 8i, IMHO. Knowing the bug in
    DATABASE_STATS, I've written a procedure to iteratively use SCHEMA_STATS
    instead. Of course, this too has a bug that will report ORA-1403 on the
    "first" table in the schema, so I needed to code around that.

    And for all this trouble Oracle still recommends using DBMS_STATS over
    ANALYZE. Then fix it!

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Post, Ethan
    Sent: Friday, October 04, 2002 5:09 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM
    inde


    Arrgggg...

    So anyway, I am thinking, hey it's high time I start using DBMS_STATS
    instead of my own procedure so I kick of the following
    (Oracle 8.1.7.4).
    After the first run I have SYS and SYSTEM stats on indexes
    and on other
    schemas with NO STATS it just ignored those tables even
    though you can see I
    have GATHER EMPTY below. So I kick it off again and guess
    what, it starts
    analyzing the tables it missed the first time, including SYS
    and SYSTEM.
    Guess I am going to use DBMS_STATS.GATHER_TABLE_STATS and be
    a bit more
    specific about what I get.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Connor McDonald at Oct 7, 2002 at 4:18 pm
    Here is a work-in-progress utility, ie, I'm posting
    this on an "all care, no responsibility" basis.
    "Features" include:

    will process all schemas or a nominated one
    has been deliberately restricted tables and indexes
    (so if you want lobs etc, you'll need to edit it a
    little)
    can run in synchronous (foreground) mode or
    asynchronous ( submits itself as a dbms_job)
    can run in parallel (multiple streams done via
    modulo the object_id)
    has a debugging mode
    uses dbms_space to derive a meaningful estimate size
    for each segment
    records progress in v$session_longops
    doesn't go against DBA_SEGMENTS 'cos thats so slow
    cranks up sort_area_size to improve perf.

    Cheers
    Connor

    create or replace
    package system.dbstat is

    procedure analyze_db (

    p_owner varchar2 default null, --
    if only one owner to be processed

    p_debug number default 0, --
    0=do work, 1=msgs+work, 2=msgs only

    p_segment_type varchar2 default null, --
    TABLE or INDEX

    p_parallel number default 1, --
    concurrency (>1 means must be asych)

    p_mode varchar2 default 'S', --
    A=run as dbms_job, S=run synchronous

    p_int1 number default 1, --
    internal use only

    p_int2 number default 0); --
    internal use only
    end;
    /
    create or replace
    package body system.dbstat is

    --
    ----------------------------------------------------------------------------------
    -- Routines
    --
    ----------------------------------------------------------------------------------

    procedure analyze_db (
    p_owner varchar2 default null, --
    if only one owner to be processed
    p_debug number default 0, --
    0=do work, 1=msgs+work, 2=msgs only
    p_segment_type varchar2 default null, --
    TABLE or INDEX
    p_parallel number default 1, --
    concurrency (>1 means must be asych)
    p_mode varchar2 default 'S', --
    A=run as dbms_job, S=run synchronous
    p_int1 number default 1, --
    internal use only
    p_int2 number default 0) is --
    internal use only

    type varchar_list is table of varchar2(80);

    v_start date := sysdate;
    v_tot_count number := 0;
    v_cum_count number := 0;
    v_cum_bytes number := 0;

    v_owner varchar_list;
    v_segment_name varchar_list;
    v_segment_type varchar_list;
    v_partitioned varchar_list;

    v_longop_rindex pls_integer;
    v_longop_slno pls_integer;
    v_job pls_integer;
    v_job_plsql varchar2(240);

    procedure process_segment(p_owner
    varchar2,p_segment_name varchar2,
    p_segment_type
    varchar2,p_part_name varchar2 default null,
    p_granularity varchar2
    default 'GLOBAL') is
    v_total_blocks number;
    v_total_bytes number;
    v_unused_blocks number;
    v_unused_bytes number;
    v_last_file_id number;
    v_last_block_id number;
    v_last_block number;
    v_amount_to_analyze number;
    v_ana_command varchar2(500);
    begin

    dbms_application_info.set_client_info(p_owner||','||p_segment_name||','||p_segment_type||','||p_part_name);

    dbms_space.unused_space (
    p_owner,
    p_segment_name,
    p_segment_type,
    v_total_blocks,
    v_total_bytes,
    v_unused_blocks,
    v_unused_bytes,
    v_last_file_id,
    v_last_block_id,
    v_last_block,
    p_part_name);

    --
    -- This gives a reasonable degree of analysis. Up to
    about 10M is effectively a compute, and
    -- it reduces from there, eventually down to about
    0.5% for a 1G segment
    -- The formula is: percent to analyze := 500 *
    power(used megabytes,-1.05)
    -- with a ceiling of 99.99 percent (since dbms_stats
    does not allow a '100' to be passed)
    --
    v_amount_to_analyze :=
    least(99.99,round(131072000*power(greatest(v_total_bytes-v_unused_bytes,1),-1.05),5));

    if p_debug > 0 then
    dbms_output.put_line(p_segment_type||':
    '||p_owner||'.'||p_segment_name||' '||p_part_name);
    dbms_output.put_line(v_total_bytes||' bytes
    allocated');

    dbms_output.put_line((v_total_bytes-v_unused_bytes)||'
    bytes in use');
    dbms_output.put_line('Analyze
    '||nvl(v_amount_to_analyze,100)||'%');
    dbms_output.put_line('---------------------');
    end if;

    dbms_application_info.set_client_info('Obj:
    '||v_cum_count||' '||p_owner||'.'||p_segment_name||'
    '||

    (v_total_bytes-v_unused_bytes)||' byt
    '||nvl(v_amount_to_analyze,100)||'%');

    if p_debug < 2 then

    dbms_application_info.set_session_longops(v_longop_rindex,
    v_longop_slno,
    'Analyze', 0, 0, v_cum_count, v_tot_count,
    p_segment_type, 'objects');
    if p_segment_type like 'TABLE%' then --
    could be a table or a table partition
    sys.dbms_stats.gather_table_stats(
    ownname=>'"'||p_owner||'"',
    tabname=>'"'||p_segment_name||'"',
    granularity=>'ALL',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    else
    sys.dbms_stats.gather_index_stats(
    ownname=>'"'||p_owner||'"',
    indname=>'"'||p_segment_name||'"',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    end if;
    v_cum_count := v_cum_count + 1;
    v_cum_bytes := v_cum_bytes +
    v_total_bytes-v_unused_bytes;
    end if;
    end;

    begin
    if upper(p_mode) not in ('A','S') then
    raise_application_error(-20000,'Mode must be A or
    S');
    end if;
    if p_parallel > 1 and upper(p_mode) != 'A' then
    raise_application_error(-20000,'Cannot run
    parallel in synchronous mode');
    end if;
    if p_parallel not between 1 and 4 then
    raise_application_error(-20000,'Parallel limited
    to 1 to 4');
    end if;

    if p_parallel > 1 or p_mode = 'A' then
    for i in 1 .. p_parallel loop
    v_job_plsql :=
    'dbstat.analyze_db('''||p_owner||''',0,'''||p_segment_type||

    ''',1,''S'','||p_parallel||','||(i-1)||');';
    if p_debug > 0 then
    dbms_output.put_line('Job: '||v_job_plsql);
    end if;
    if p_debug < 2 then
    dbms_job.submit(v_job,v_job_plsql);
    end if;
    end loop;
    commit;
    return;
    end if;

    dbms_output.put_line('---------------------');

    dbms_output.enable(999999);

    if p_debug > 0 then
    execute immediate 'alter session set
    sort_area_size = 8192000';
    execute immediate 'alter session set
    sort_area_retained_size = 8192000';
    v_longop_rindex :=
    dbms_application_info.set_session_longops_nohint;
    end if;

    SELECT segment_TYPE,segment_NAME,owner,
    decode(subname,null,'NO','YES') partitioned
    bulk collect into
    v_segment_type, v_segment_name, v_owner,
    v_partitioned
    from ( select u.name owner, o.name segment_name,
    o.subname, so.object_type segment_type, o.obj#
    object_id
    from sys.user$ u, sys.obj$ o, sys.ts$ ts,
    sys.seg$ s, sys.file$ f,
    ( select 'TABLE' object_type, 2
    object_type_id, 5 segment_type_id, t.obj#, t.file#,
    t.block#, t.ts#
    from sys.tab$ t
    where bitand(t.property, 1024) = 0
    and bitand(t.property,8192) = 0
    union all
    select 'INDEX', 1, 6, i.obj#, i.file#,
    i.block#, i.ts#
    from sys.ind$ i
    where i.type# in (1, 2, 3, 4, 6, 7, 9)
    ) so
    where s.file# = so.file#
    and s.block# = so.block#
    and s.ts# = so.ts#
    and s.ts# = ts.ts#
    and o.obj# = so.obj#
    and o.owner# = u.user#
    and s.type# = so.segment_type_id
    and o.type# = so.object_type_id
    and s.ts# = f.ts#
    and s.file# = f.relfile#
    and u.name not in ('SYS','SYSTEM'))
    where owner = nvl(upper(p_owner),owner)
    and segment_type =
    nvl(upper(p_segment_type),segment_type)
    and mod(object_id,p_int1) = p_int2;

    v_tot_count := v_segment_type.count;
    for i in v_segment_type.first .. v_segment_type.last
    loop
    if v_partitioned(i) = 'YES' then
    for j in (
    SELECT O.SUBNAME PART_NAME,
    decode(O.TYPE#,19,'TABLE PARTITION',
    20,'INDEX PARTITION',
    34,'TABLE
    SUBPARTITION',
    35,'INDEX
    SUBPARTITION') segment_type
    FROM SYS.USER$ U,SYS.OBJ$ O
    WHERE U.NAME = v_owner(i)
    AND O.OWNER# = U.USER#
    AND O.NAME = v_segment_name(i)
    AND O.TYPE# in (19,20,34,35)
    ORDER BY PART_NAME ) loop

    process_segment(v_owner(i),v_segment_name(i),j.segment_type,j.part_name,'PARTITION');
    end loop;
    else

    process_segment(v_owner(i),v_segment_name(i),v_segment_type(i));
    end if;
    end loop;
    dbms_output.put_line('Objects Analyzed:
    '||v_cum_count);
    dbms_output.put_line('Bytes scanned:
    '||v_cum_bytes);
    dbms_output.put_line('Elapsed Time:
    '||round((sysdate-v_start)*86400,1));
    end;

    END;
    /

    --- "Jesse, Rich" wrote: >
    Yes, the DBMS_STATS package is very quirky in 8i,
    IMHO. Knowing the bug in
    DATABASE_STATS, I've written a procedure to
    iteratively use SCHEMA_STATS
    instead. Of course, this too has a bug that will
    report ORA-1403 on the
    "first" table in the schema, so I needed to code
    around that.

    And for all this trouble Oracle still recommends
    using DBMS_STATS over
    ANALYZE. Then fix it!

    Rich Jesse System/Database
    Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech
    International, Sussex, WI USA

    -----Original Message-----
    From: Post, Ethan
    Sent: Friday, October 04, 2002 5:09 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS
    analyzes SYS and SYSTEM
    inde


    Arrgggg...

    So anyway, I am thinking, hey it's high time I
    start using DBMS_STATS
    instead of my own procedure so I kick of the following
    (Oracle 8.1.7.4).
    After the first run I have SYS and SYSTEM stats on indexes
    and on other
    schemas with NO STATS it just ignored those tables even
    though you can see I
    have GATHER EMPTY below. So I kick it off again and guess
    what, it starts
    analyzing the tables it missed the first time,
    including SYS
    and SYSTEM.
    Guess I am going to use
    DBMS_STATS.GATHER_TABLE_STATS and be
    a bit more
    specific about what I get.
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    =====
    Connor McDonald
    http://www.oracledba.co.uk
    http://www.oaktable.net

    "Remember amateurs built the ark - Professionals built the Titanic"

    __________________________________________________
    Do You Yahoo!?
    Everything you'll ever need on one web page
    from News and Sport to Email and Music Charts
    http://uk.my.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: =?iso-8859-1?q?Connor=20McDonald?=
    INET: hamcdc_at_yahoo.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • MacGregor, Ian A. at Oct 7, 2002 at 4:39 pm
    Does this script properly skip IOT overflow objects?

    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Monday, October 07, 2002 9:19 AM
    To: Multiple recipients of list ORACLE-L

    Here is a work-in-progress utility, ie, I'm posting
    this on an "all care, no responsibility" basis.
    "Features" include:

    will process all schemas or a nominated one
    has been deliberately restricted tables and indexes
    (so if you want lobs etc, you'll need to edit it a
    little)
    can run in synchronous (foreground) mode or
    asynchronous ( submits itself as a dbms_job)
    can run in parallel (multiple streams done via
    modulo the object_id)
    has a debugging mode
    uses dbms_space to derive a meaningful estimate size
    for each segment
    records progress in v$session_longops
    doesn't go against DBA_SEGMENTS 'cos thats so slow
    cranks up sort_area_size to improve perf.

    Cheers
    Connor

    create or replace
    package system.dbstat is

    procedure analyze_db (

    p_owner varchar2 default null, --
    if only one owner to be processed

    p_debug number default 0, --
    0=do work, 1=msgs+work, 2=msgs only

    p_segment_type varchar2 default null, --
    TABLE or INDEX

    p_parallel number default 1, --
    concurrency (>1 means must be asych)

    p_mode varchar2 default 'S', --
    A=run as dbms_job, S=run synchronous

    p_int1 number default 1, --
    internal use only

    p_int2 number default 0); --
    internal use only
    end;
    /
    create or replace
    package body system.dbstat is

    --
    ----------------------------------------------------------------------------------
    -- Routines
    --
    ----------------------------------------------------------------------------------

    procedure analyze_db (
    p_owner varchar2 default null, --
    if only one owner to be processed
    p_debug number default 0, --
    0=do work, 1=msgs+work, 2=msgs only
    p_segment_type varchar2 default null, --
    TABLE or INDEX
    p_parallel number default 1, --
    concurrency (>1 means must be asych)
    p_mode varchar2 default 'S', --
    A=run as dbms_job, S=run synchronous
    p_int1 number default 1, --
    internal use only
    p_int2 number default 0) is --
    internal use only

    type varchar_list is table of varchar2(80);

    v_start date := sysdate;
    v_tot_count number := 0;
    v_cum_count number := 0;
    v_cum_bytes number := 0;

    v_owner varchar_list;
    v_segment_name varchar_list;
    v_segment_type varchar_list;
    v_partitioned varchar_list;

    v_longop_rindex pls_integer;
    v_longop_slno pls_integer;
    v_job pls_integer;
    v_job_plsql varchar2(240);

    procedure process_segment(p_owner
    varchar2,p_segment_name varchar2,
    p_segment_type
    varchar2,p_part_name varchar2 default null,
    p_granularity varchar2
    default 'GLOBAL') is
    v_total_blocks number;
    v_total_bytes number;
    v_unused_blocks number;
    v_unused_bytes number;
    v_last_file_id number;
    v_last_block_id number;
    v_last_block number;
    v_amount_to_analyze number;
    v_ana_command varchar2(500);
    begin

    dbms_application_info.set_client_info(p_owner||','||p_segment_name||','||p_segment_type||','||p_part_name);

    dbms_space.unused_space (
    p_owner,
    p_segment_name,
    p_segment_type,
    v_total_blocks,
    v_total_bytes,
    v_unused_blocks,
    v_unused_bytes,
    v_last_file_id,
    v_last_block_id,
    v_last_block,
    p_part_name);

    --
    -- This gives a reasonable degree of analysis. Up to
    about 10M is effectively a compute, and
    -- it reduces from there, eventually down to about
    0.5% for a 1G segment
    -- The formula is: percent to analyze := 500 *
    power(used megabytes,-1.05)
    -- with a ceiling of 99.99 percent (since dbms_stats
    does not allow a '100' to be passed)
    --
    v_amount_to_analyze := least(99.99,round(131072000*power(greatest(v_total_bytes-v_unused_bytes,1),-1.05),5));

    if p_debug > 0 then
    dbms_output.put_line(p_segment_type||':
    '||p_owner||'.'||p_segment_name||' '||p_part_name);
    dbms_output.put_line(v_total_bytes||' bytes
    allocated');

    dbms_output.put_line((v_total_bytes-v_unused_bytes)||'
    bytes in use');
    dbms_output.put_line('Analyze '||nvl(v_amount_to_analyze,100)||'%');
    dbms_output.put_line('---------------------');
    end if;

    dbms_application_info.set_client_info('Obj:
    '||v_cum_count||' '||p_owner||'.'||p_segment_name||'
    '||

    (v_total_bytes-v_unused_bytes)||' byt '||nvl(v_amount_to_analyze,100)||'%');

    if p_debug < 2 then

    dbms_application_info.set_session_longops(v_longop_rindex,
    v_longop_slno,
    'Analyze', 0, 0, v_cum_count, v_tot_count, p_segment_type, 'objects');
    if p_segment_type like 'TABLE%' then --
    could be a table or a table partition
    sys.dbms_stats.gather_table_stats(
    ownname=>'"'||p_owner||'"',
    tabname=>'"'||p_segment_name||'"',
    granularity=>'ALL',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    else
    sys.dbms_stats.gather_index_stats(
    ownname=>'"'||p_owner||'"',
    indname=>'"'||p_segment_name||'"',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    end if;
    v_cum_count := v_cum_count + 1;
    v_cum_bytes := v_cum_bytes + v_total_bytes-v_unused_bytes;
    end if;
    end;

    begin
    if upper(p_mode) not in ('A','S') then
    raise_application_error(-20000,'Mode must be A or
    S');
    end if;
    if p_parallel > 1 and upper(p_mode) != 'A' then
    raise_application_error(-20000,'Cannot run
    parallel in synchronous mode');
    end if;
    if p_parallel not between 1 and 4 then
    raise_application_error(-20000,'Parallel limited
    to 1 to 4');
    end if;

    if p_parallel > 1 or p_mode = 'A' then
    for i in 1 .. p_parallel loop
    v_job_plsql := 'dbstat.analyze_db('''||p_owner||''',0,'''||p_segment_type||

    ''',1,''S'','||p_parallel||','||(i-1)||');';
    if p_debug > 0 then
    dbms_output.put_line('Job: '||v_job_plsql);
    end if;
    if p_debug < 2 then
    dbms_job.submit(v_job,v_job_plsql);
    end if;
    end loop;
    commit;
    return;
    end if;

    dbms_output.put_line('---------------------');

    dbms_output.enable(999999);

    if p_debug > 0 then
    execute immediate 'alter session set
    sort_area_size = 8192000';
    execute immediate 'alter session set sort_area_retained_size = 8192000';
    v_longop_rindex := dbms_application_info.set_session_longops_nohint;
    end if;

    SELECT segment_TYPE,segment_NAME,owner,
    decode(subname,null,'NO','YES') partitioned
    bulk collect into
    v_segment_type, v_segment_name, v_owner,
    v_partitioned
    from ( select u.name owner, o.name segment_name,
    o.subname, so.object_type segment_type, o.obj#
    object_id
    from sys.user$ u, sys.obj$ o, sys.ts$ ts,
    sys.seg$ s, sys.file$ f,
    ( select 'TABLE' object_type, 2
    object_type_id, 5 segment_type_id, t.obj#, t.file#,
    t.block#, t.ts#
    from sys.tab$ t
    where bitand(t.property, 1024) = 0
    and bitand(t.property,8192) = 0
    union all
    select 'INDEX', 1, 6, i.obj#, i.file#, i.block#, i.ts#
    from sys.ind$ i
    where i.type# in (1, 2, 3, 4, 6, 7, 9)
    ) so
    where s.file# = so.file#
    and s.block# = so.block#
    and s.ts# = so.ts#
    and s.ts# = ts.ts#
    and o.obj# = so.obj#
    and o.owner# = u.user#
    and s.type# = so.segment_type_id
    and o.type# = so.object_type_id
    and s.ts# = f.ts#
    and s.file# = f.relfile#
    and u.name not in ('SYS','SYSTEM'))
    where owner = nvl(upper(p_owner),owner)
    and segment_type =
    nvl(upper(p_segment_type),segment_type)
    and mod(object_id,p_int1) = p_int2;

    v_tot_count := v_segment_type.count;
    for i in v_segment_type.first .. v_segment_type.last
    loop
    if v_partitioned(i) = 'YES' then
    for j in (
    SELECT O.SUBNAME PART_NAME,
    decode(O.TYPE#,19,'TABLE PARTITION',
    20,'INDEX PARTITION',
    34,'TABLE
    SUBPARTITION',
    35,'INDEX
    SUBPARTITION') segment_type
    FROM SYS.USER$ U,SYS.OBJ$ O
    WHERE U.NAME = v_owner(i)
    AND O.OWNER# = U.USER#
    AND O.NAME = v_segment_name(i)
    AND O.TYPE# in (19,20,34,35)
    ORDER BY PART_NAME ) loop

    process_segment(v_owner(i),v_segment_name(i),j.segment_type,j.part_name,'PARTITION');
    end loop;
    else

    process_segment(v_owner(i),v_segment_name(i),v_segment_type(i));
    end if;
    end loop;
    dbms_output.put_line('Objects Analyzed:
    '||v_cum_count);
    dbms_output.put_line('Bytes scanned:
    '||v_cum_bytes);
    dbms_output.put_line('Elapsed Time:
    '||round((sysdate-v_start)*86400,1));
    end;

    END;
    /

    --- "Jesse, Rich" wrote: >
    Yes, the DBMS_STATS package is very quirky in 8i,
    IMHO. Knowing the bug in
    DATABASE_STATS, I've written a procedure to
    iteratively use SCHEMA_STATS
    instead. Of course, this too has a bug that will
    report ORA-1403 on the
    "first" table in the schema, so I needed to code
    around that.

    And for all this trouble Oracle still recommends
    using DBMS_STATS over
    ANALYZE. Then fix it!

    Rich Jesse System/Database
    Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech
    International, Sussex, WI USA

    -----Original Message-----
    From: Post, Ethan
    Sent: Friday, October 04, 2002 5:09 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS
    analyzes SYS and SYSTEM
    inde


    Arrgggg...

    So anyway, I am thinking, hey it's high time I
    start using DBMS_STATS
    instead of my own procedure so I kick of the following
    (Oracle 8.1.7.4).
    After the first run I have SYS and SYSTEM stats on indexes
    and on other
    schemas with NO STATS it just ignored those tables even
    though you can see I
    have GATHER EMPTY below. So I kick it off again and guess
    what, it starts
    analyzing the tables it missed the first time,
    including SYS
    and SYSTEM.
    Guess I am going to use
    DBMS_STATS.GATHER_TABLE_STATS and be
    a bit more
    specific about what I get.
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    =====
    Connor McDonald
    http://www.oracledba.co.uk
    http://www.oaktable.net

    "Remember amateurs built the ark - Professionals built the Titanic"

    __________________________________________________
    Do You Yahoo!?
    Everything you'll ever need on one web page
    from News and Sport to Email and Music Charts http://uk.my.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: =?iso-8859-1?q?Connor=20McDonald?=
    INET: hamcdc_at_yahoo.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: MacGregor, Ian A.
    INET: ian_at_SLAC.Stanford.EDU

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Cherie_Machler_at_gelco.com at Oct 7, 2002 at 4:53 pm
    Connor,

    What version of Oracle was this coded for?

    Thanks,

    Cherie

    Connor
    McDonald To: Multiple recipients of list ORACLE-L
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and
    Sent by: SYSTEM inde
    root_at_fatcity.c
    om

    10/07/02 11:18
    AM
    Please respond
    to ORACLE-L

    Here is a work-in-progress utility, ie, I'm posting
    this on an "all care, no responsibility" basis.
    "Features" include:

    will process all schemas or a nominated one
    has been deliberately restricted tables and indexes
    (so if you want lobs etc, you'll need to edit it a
    little)
    can run in synchronous (foreground) mode or
    asynchronous ( submits itself as a dbms_job)
    can run in parallel (multiple streams done via
    modulo the object_id)
    has a debugging mode
    uses dbms_space to derive a meaningful estimate size
    for each segment
    records progress in v$session_longops
    doesn't go against DBA_SEGMENTS 'cos thats so slow
    cranks up sort_area_size to improve perf.

    Cheers
    Connor

    create or replace
    package system.dbstat is

    procedure analyze_db (

    p_owner varchar2 default null, --
    if only one owner to be processed

    p_debug number default 0, --
    0=do work, 1=msgs+work, 2=msgs only

    p_segment_type varchar2 default null, --
    TABLE or INDEX

    p_parallel number default 1, --
    concurrency (>1 means must be asych)

    p_mode varchar2 default 'S', --
    A=run as dbms_job, S=run synchronous

    p_int1 number default 1, --
    internal use only

    p_int2 number default 0); --
    internal use only
    end;
    /
    create or replace
    package body system.dbstat is

    --
    ----------------------------------------------------------------------------------

    -- Routines
    --
    ----------------------------------------------------------------------------------

    procedure analyze_db (
    p_owner varchar2 default null, --
    if only one owner to be processed
    p_debug number default 0, --
    0=do work, 1=msgs+work, 2=msgs only
    p_segment_type varchar2 default null, --
    TABLE or INDEX
    p_parallel number default 1, --
    concurrency (>1 means must be asych)
    p_mode varchar2 default 'S', --
    A=run as dbms_job, S=run synchronous
    p_int1 number default 1, --
    internal use only
    p_int2 number default 0) is --
    internal use only

    type varchar_list is table of varchar2(80);

    v_start date := sysdate;
    v_tot_count number := 0;
    v_cum_count number := 0;
    v_cum_bytes number := 0;

    v_owner varchar_list;
    v_segment_name varchar_list;
    v_segment_type varchar_list;
    v_partitioned varchar_list;

    v_longop_rindex pls_integer;
    v_longop_slno pls_integer;
    v_job pls_integer;
    v_job_plsql varchar2(240);

    procedure process_segment(p_owner
    varchar2,p_segment_name varchar2,
    p_segment_type
    varchar2,p_part_name varchar2 default null,
    p_granularity varchar2
    default 'GLOBAL') is
    v_total_blocks number;
    v_total_bytes number;
    v_unused_blocks number;
    v_unused_bytes number;
    v_last_file_id number;
    v_last_block_id number;
    v_last_block number;
    v_amount_to_analyze number;
    v_ana_command varchar2(500);
    begin

    dbms_application_info.set_client_info(p_owner||','||p_segment_name||','
    p_segment_type||','||p_part_name);
    dbms_space.unused_space (
    p_owner,
    p_segment_name,
    p_segment_type,
    v_total_blocks,
    v_total_bytes,
    v_unused_blocks,
    v_unused_bytes,
    v_last_file_id,
    v_last_block_id,
    v_last_block,
    p_part_name);

    --
    -- This gives a reasonable degree of analysis. Up to
    about 10M is effectively a compute, and
    -- it reduces from there, eventually down to about
    0.5% for a 1G segment
    -- The formula is: percent to analyze := 500 *
    power(used megabytes,-1.05)
    -- with a ceiling of 99.99 percent (since dbms_stats
    does not allow a '100' to be passed)
    --
    v_amount_to_analyze :=
    least(99.99,round(131072000*power(greatest(v_total_bytes-v_unused_bytes,1),
    -1.05),5));

    if p_debug > 0 then
    dbms_output.put_line(p_segment_type||':
    '||p_owner||'.'||p_segment_name||' '||p_part_name);
    dbms_output.put_line(v_total_bytes||' bytes
    allocated');

    dbms_output.put_line((v_total_bytes-v_unused_bytes)||'
    bytes in use');
    dbms_output.put_line('Analyze
    '||nvl(v_amount_to_analyze,100)||'%');
    dbms_output.put_line('---------------------');
    end if;

    dbms_application_info.set_client_info('Obj:
    '||v_cum_count||' '||p_owner||'.'||p_segment_name||'
    '||

    (v_total_bytes-v_unused_bytes)||' byt
    '||nvl(v_amount_to_analyze,100)||'%');

    if p_debug < 2 then

    dbms_application_info.set_session_longops(v_longop_rindex,
    v_longop_slno,
    'Analyze', 0, 0, v_cum_count, v_tot_count,
    p_segment_type, 'objects');
    if p_segment_type like 'TABLE%' then --
    could be a table or a table partition
    sys.dbms_stats.gather_table_stats(
    ownname=>'"'||p_owner||'"',
    tabname=>'"'||p_segment_name||'"',
    granularity=>'ALL',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    else
    sys.dbms_stats.gather_index_stats(
    ownname=>'"'||p_owner||'"',
    indname=>'"'||p_segment_name||'"',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    end if;
    v_cum_count := v_cum_count + 1;
    v_cum_bytes := v_cum_bytes +
    v_total_bytes-v_unused_bytes;
    end if;
    end;

    begin
    if upper(p_mode) not in ('A','S') then
    raise_application_error(-20000,'Mode must be A or
    S');
    end if;
    if p_parallel > 1 and upper(p_mode) != 'A' then
    raise_application_error(-20000,'Cannot run
    parallel in synchronous mode');
    end if;
    if p_parallel not between 1 and 4 then
    raise_application_error(-20000,'Parallel limited
    to 1 to 4');
    end if;

    if p_parallel > 1 or p_mode = 'A' then
    for i in 1 .. p_parallel loop
    v_job_plsql :=
    'dbstat.analyze_db('''||p_owner||''',0,'''||p_segment_type||

    ''',1,''S'','||p_parallel||','||(i-1)||');';
    if p_debug > 0 then
    dbms_output.put_line('Job: '||v_job_plsql);
    end if;
    if p_debug < 2 then
    dbms_job.submit(v_job,v_job_plsql);
    end if;
    end loop;
    commit;
    return;
    end if;

    dbms_output.put_line('---------------------');

    dbms_output.enable(999999);

    if p_debug > 0 then
    execute immediate 'alter session set
    sort_area_size = 8192000';
    execute immediate 'alter session set
    sort_area_retained_size = 8192000';
    v_longop_rindex :=
    dbms_application_info.set_session_longops_nohint;
    end if;

    SELECT segment_TYPE,segment_NAME,owner,
    decode(subname,null,'NO','YES') partitioned
    bulk collect into
    v_segment_type, v_segment_name, v_owner,
    v_partitioned
    from ( select u.name owner, o.name segment_name,
    o.subname, so.object_type segment_type, o.obj#
    object_id
    from sys.user$ u, sys.obj$ o, sys.ts$ ts,
    sys.seg$ s, sys.file$ f,
    ( select 'TABLE' object_type, 2
    object_type_id, 5 segment_type_id, t.obj#, t.file#,
    t.block#, t.ts#
    from sys.tab$ t
    where bitand(t.property, 1024) = 0
    and bitand(t.property,8192) = 0
    union all
    select 'INDEX', 1, 6, i.obj#, i.file#,
    i.block#, i.ts#
    from sys.ind$ i
    where i.type# in (1, 2, 3, 4, 6, 7, 9)
    ) so
    where s.file# = so.file#
    and s.block# = so.block#
    and s.ts# = so.ts#
    and s.ts# = ts.ts#
    and o.obj# = so.obj#
    and o.owner# = u.user#
    and s.type# = so.segment_type_id
    and o.type# = so.object_type_id
    and s.ts# = f.ts#
    and s.file# = f.relfile#
    and u.name not in ('SYS','SYSTEM'))
    where owner = nvl(upper(p_owner),owner)
    and segment_type =
    nvl(upper(p_segment_type),segment_type)
    and mod(object_id,p_int1) = p_int2;

    v_tot_count := v_segment_type.count;
    for i in v_segment_type.first .. v_segment_type.last
    loop
    if v_partitioned(i) = 'YES' then
    for j in (
    SELECT O.SUBNAME PART_NAME,
    decode(O.TYPE#,19,'TABLE PARTITION',
    20,'INDEX PARTITION',
    34,'TABLE
    SUBPARTITION',
    35,'INDEX
    SUBPARTITION') segment_type
    FROM SYS.USER$ U,SYS.OBJ$ O
    WHERE U.NAME = v_owner(i)
    AND O.OWNER# = U.USER#
    AND O.NAME = v_segment_name(i)
    AND O.TYPE# in (19,20,34,35)
    ORDER BY PART_NAME ) loop

    process_segment(v_owner(i),v_segment_name(i),j.segment_type,j.part_name,'PARTITION');

    end loop;
    else

    process_segment(v_owner(i),v_segment_name(i),v_segment_type(i));
    end if;
    end loop;
    dbms_output.put_line('Objects Analyzed:
    '||v_cum_count);
    dbms_output.put_line('Bytes scanned:
    '||v_cum_bytes);
    dbms_output.put_line('Elapsed Time:
    '||round((sysdate-v_start)*86400,1));
    end;

    END;
    /

    --- "Jesse, Rich" wrote: >
    Yes, the DBMS_STATS package is very quirky in 8i,
    IMHO. Knowing the bug in
    DATABASE_STATS, I've written a procedure to
    iteratively use SCHEMA_STATS
    instead. Of course, this too has a bug that will
    report ORA-1403 on the
    "first" table in the schema, so I needed to code
    around that.

    And for all this trouble Oracle still recommends
    using DBMS_STATS over
    ANALYZE. Then fix it!

    Rich Jesse System/Database
    Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech
    International, Sussex, WI USA

    -----Original Message-----
    From: Post, Ethan
    Sent: Friday, October 04, 2002 5:09 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS
    analyzes SYS and SYSTEM
    inde


    Arrgggg...

    So anyway, I am thinking, hey it's high time I
    start using DBMS_STATS
    instead of my own procedure so I kick of the following
    (Oracle 8.1.7.4).
    After the first run I have SYS and SYSTEM stats on indexes
    and on other
    schemas with NO STATS it just ignored those tables even
    though you can see I
    have GATHER EMPTY below. So I kick it off again and guess
    what, it starts
    analyzing the tables it missed the first time,
    including SYS
    and SYSTEM.
    Guess I am going to use
    DBMS_STATS.GATHER_TABLE_STATS and be
    a bit more
    specific about what I get.
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    =====
    Connor McDonald
    http://www.oracledba.co.uk
    http://www.oaktable.net

    "Remember amateurs built the ark - Professionals built the Titanic"

    __________________________________________________
    Do You Yahoo!?
    Everything you'll ever need on one web page
    from News and Sport to Email and Music Charts
    http://uk.my.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: =?iso-8859-1?q?Connor=20McDonald?=
    INET: hamcdc_at_yahoo.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Cherie_Machler_at_gelco.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Deshpande, Kirti at Oct 7, 2002 at 6:34 pm
    It is still quirky in 9.2.0.1.
    Now it does not like an FBI on a table :(
    Check out bug# 2606697 on Metalink...

    Kirti

    -----Original Message-----
    Sent: Monday, October 07, 2002 10:29 AM
    To: Multiple recipients of list ORACLE-L
    inde

    Yes, the DBMS_STATS package is very quirky in 8i, IMHO. Knowing the bug in
    DATABASE_STATS, I've written a procedure to iteratively use SCHEMA_STATS
    instead. Of course, this too has a bug that will report ORA-1403 on the
    "first" table in the schema, so I needed to code around that.

    And for all this trouble Oracle still recommends using DBMS_STATS over
    ANALYZE. Then fix it!

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Deshpande, Kirti
    INET: kirti.deshpande_at_verizon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jesse, Rich at Oct 7, 2002 at 7:19 pm
    Hmmmm...I can't see the bug. What Oracle product is it placed under? One
    would think "Server" for something like this...

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Deshpande, Kirti
    Sent: Monday, October 07, 2002 12:30 PM
    To: oracle list
    Cc: Jesse, Rich
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM
    inde


    It is still quirky in 9.2.0.1.
    Now it does not like an FBI on a table :(
    Check out bug# 2606697 on Metalink...

    - Kirti

    -----Original Message-----
    From: Jesse, Rich
    Sent: Monday, October 07, 2002 10:29 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM
    inde


    Yes, the DBMS_STATS package is very quirky in 8i, IMHO.
    Knowing the bug in
    DATABASE_STATS, I've written a procedure to iteratively use
    SCHEMA_STATS
    instead. Of course, this too has a bug that will report
    ORA-1403 on the
    "first" table in the schema, so I needed to code around that.

    And for all this trouble Oracle still recommends using DBMS_STATS over
    ANALYZE. Then fix it!
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Post, Ethan at Oct 7, 2002 at 7:28 pm
    Man that is ridiculous. You would think Oracle would have it's act together
    on DBMS_STATS package by now. Since is it supposedly so superior to analyze
    table you would think it might actually work.

    Ethan Post
    perotdba (AIM), epost1 (Yahoo)

    -----Original Message-----
    Sent: Monday, October 07, 2002 1:34 PM
    To: Multiple recipients of list ORACLE-L
    inde

    It is still quirky in 9.2.0.1.
    Now it does not like an FBI on a table :(
    Check out bug# 2606697 on Metalink...

    Kirti

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Post, Ethan
    INET: Ethan.Post_at_ps.net

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Deshpande, Kirti at Oct 7, 2002 at 7:39 pm
    I just used the bug number window, rest all left to default.

    Kirti

    -----Original Message-----
    Sent: Monday, October 07, 2002 2:19 PM
    To: Multiple recipients of list ORACLE-L
    inde

    Hmmmm...I can't see the bug. What Oracle product is it placed under? One
    would think "Server" for something like this...

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Deshpande, Kirti
    Sent: Monday, October 07, 2002 12:30 PM
    To: oracle list
    Cc: Jesse, Rich
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM
    inde


    It is still quirky in 9.2.0.1.
    Now it does not like an FBI on a table :(
    Check out bug# 2606697 on Metalink...

    - Kirti

    -----Original Message-----
    From: Jesse, Rich
    Sent: Monday, October 07, 2002 10:29 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM
    inde


    Yes, the DBMS_STATS package is very quirky in 8i, IMHO.
    Knowing the bug in
    DATABASE_STATS, I've written a procedure to iteratively use
    SCHEMA_STATS
    instead. Of course, this too has a bug that will report
    ORA-1403 on the
    "first" table in the schema, so I needed to code around that.

    And for all this trouble Oracle still recommends using DBMS_STATS over
    ANALYZE. Then fix it!
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Deshpande, Kirti
    INET: kirti.deshpande_at_verizon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Post, Ethan at Oct 7, 2002 at 7:54 pm
    Here is my contribution...schedule with DBMS_JOB. It will gather stale
    stats using XXX_TAB_MODIFICATIONS, then get stats for tables and indexes
    without stats using DBMS_STATS or for tables where stats are older then "N"
    days. If DBMS_STATS has an error it will use the trusty "analyze
    statistics" command. It will quit after a specified period. This is a nice
    feature because I can have procedure run multiple times per week and I don't
    have to get everything done at once during a maintenance window. I like to
    run my stat analysis about 1-2 hours every night. Keeps everything up to
    date and running smooth. I ahve daily reporting the is generated with a
    section that shows me how old stats are in the database. That way I rarly
    miss anything. Just finished writing it and have not done much testing.
    Let me know if you see anything obvious.

    analyze_stats - Basic stats gatherer.
    --
    p_days - Calculate stats if they are >= "n" days old.
    p_minutes - Quit running after "n" minutes.
    p_monitor - Activate or deactivate table monitoring.

    create or replace procedure analyze_stats (

    p_days number default 50,
    p_minutes number default 600,
    p_monitor boolean default FALSE) as

    cursor c_tables is (
    select a.owner,
    a.table_name,
    a.last_analyzed,
    a.num_rows,
    a.monitoring,
    b.bytes
    from dba_tables a,
    dba_segments b
    where b.segment_type='TABLE'
    and a.table_name=b.segment_name);

    cursor c_indexes(p_owner varchar2,p_table varchar2) is (
    select a.owner,
    a.index_name,
    a.last_analyzed
    from dba_indexes a);

    l_gather_stats boolean;
    l_start_time date := sysdate;

    l_percent number(3);
    l_error boolean;


    begin


    Get stale database statistics at 5%.
    dbms_stats.gather_database_stats(5,FALSE,'FOR ALL COLUMNS SIZE 1',
    NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER STALE');

    for t in c_tables loop

    l_gather_stats := false;
    l_error := false;

    if (t.bytes/1048576) > 1000 then
    l_percent := 5;
    elsif (t.bytes/1048576) > 100 then
    l_percent := 10;
    elsif (t.bytes/1048578) > 10 then
    l_percent := 25;
    else
    l_percent := 50;
    end if;

    if t.owner in ('SYS','SYSTEM') then
    if t.num_rows is not null then
    execute immediate 'analyze table ' || t.owner || '.' ||
    t.table_name || ' delete statistics';
    -- dbms_stats.delete_table_stats(t.owner,t.table_name);
    end if;
    else

    if t.num_rows is null then
    l_gather_stats := true;
    else
    if trunc(sysdate-t.last_analyzed) > p_days then
    l_gather_stats := true;
    end if;
    end if;

    -- Activate monitoring if it is suppose to be monitored.
    if p_monitor then
    if t.monitoring = 'NO' then
    execute immediate 'alter table ' || t.owner || '.' ||
    t.table_name || ' monitoring';
    end if;
    else
    if t.monitoring = 'YES' then
    execute immediate 'alter table ' || t.owner || '.' ||
    t.table_name || ' nomonitoring';
    end if;
    end if;

    end if;

    if l_gather_stats = true then

    begin

    dbms_stats.gather_table_stats(ownname=>t.owner,tabname=>t.table_name,
    estimate_percent=>l_percent,cascade=>true);
    exception
    when others then
    l_error := true;
    end;

    -- DBMS_STATS is buggy so if error occurs go back to the old
    method.
    if l_error then
    execute immediate 'analyze table ' || t.owner || '.' ||
    t.table_name ||
    ' estimate statistics sample ' || l_percent || ' percent';
    -- Do indexes also.
    for i in c_indexes(t.owner,t.table_name) loop
    execute immediate 'analyze index ' || i.owner || '.' ||
    i.index_name ||
    ' estimate statistics sample ' || l_percent || ' percent';

    end loop;
    end if;

    -- If monitoring is active then reset it manually to overcome
    various bugs.
    if p_monitor then
    execute immediate 'alter table ' || t.owner || '.' ||
    t.table_name || ' nomonitoring';
    execute immediate 'alter table ' || t.owner || '.' ||
    t.table_name || ' monitoring';
    end if;
    end if;

    if l_start_time + (p_minutes/1440) <= sysdate then
    exit;
    end if;

    end loop;


    exception

    when others then

    dbms_output.put_line(dbms_utility.format_error_stack);
    end;
    /
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Post, Ethan
    INET: Ethan.Post_at_ps.net

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Connor McDonald at Oct 8, 2002 at 9:11 am
    Evidently not...

    SQL> grant dba to blah identified by blah;

    Grant succeeded.

    SQL> conn blah/blah
    Connected.
    SQL> create table IOT ( x number primary key,
    2 y char(100) ) organization index
    3 including x overflow;

    Table created.

    SQL> select segment_name from user_segments;

    SEGMENT_NAME

    SYS_IOT_OVER_3688

    SYS_IOT_TOP_3688

    SQL> exec system.dbstat.ANALYZE_DB('BLAH');
    BEGIN dbstat.ANALYZE_DB('BLAH'); END;

    *
    ERROR at line 1:
    ORA-25191: cannot reference overflow table of an
    index-organized table

    ORA-06512: at "SYS.DBMS_STATS", line 4481
    ORA-06512: at "SYSTEM.DBSTAT", line 85
    ORA-06512: at "SYSTEM.DBSTAT", line 185
    ORA-06512: at line 1

    My lazy fix has been just to catch a 25191 and ignore
    :-)

    Cheers
    Connor

    "MacGregor, Ian A."
    wrote: > Does this script properly skip IOT overflow
    objects?
    Ian MacGregor
    Stanford Linear Accelerator Center
    ian_at_SLAC.Stanford.edu

    -----Original Message-----
    Sent: Monday, October 07, 2002 9:19 AM
    To: Multiple recipients of list ORACLE-L


    Here is a work-in-progress utility, ie, I'm posting
    this on an "all care, no responsibility" basis.
    "Features" include:

    - will process all schemas or a nominated one
    - has been deliberately restricted tables and
    indexes
    (so if you want lobs etc, you'll need to edit it a
    little)
    - can run in synchronous (foreground) mode or
    asynchronous ( submits itself as a dbms_job)
    - can run in parallel (multiple streams done via
    modulo the object_id)
    - has a debugging mode
    - uses dbms_space to derive a meaningful estimate
    size
    for each segment
    - records progress in v$session_longops
    - doesn't go against DBA_SEGMENTS 'cos thats so slow
    - cranks up sort_area_size to improve perf.

    Cheers
    Connor

    create or replace
    package system.dbstat is

    procedure analyze_db (
    p_owner varchar2 default null,
    --
    if only one owner to be processed
    p_debug number default 0,
    --
    0=do work, 1=msgs+work, 2=msgs only
    p_segment_type varchar2 default null,
    --
    TABLE or INDEX
    p_parallel number default 1,
    --
    concurrency (>1 means must be asych)
    p_mode varchar2 default 'S',
    --
    A=run as dbms_job, S=run synchronous
    p_int1 number default 1,
    --
    internal use only
    p_int2 number default 0);
    --
    internal use only
    end;
    /
    create or replace
    package body system.dbstat is

    --
    -- Routines
    --
    procedure analyze_db (
    p_owner varchar2 default null,
    --
    if only one owner to be processed
    p_debug number default 0,
    --
    0=do work, 1=msgs+work, 2=msgs only
    p_segment_type varchar2 default null,
    --
    TABLE or INDEX
    p_parallel number default 1,
    --
    concurrency (>1 means must be asych)
    p_mode varchar2 default 'S',
    --
    A=run as dbms_job, S=run synchronous
    p_int1 number default 1,
    --
    internal use only
    p_int2 number default 0) is
    --
    internal use only

    type varchar_list is table of varchar2(80);

    v_start date := sysdate;
    v_tot_count number := 0;
    v_cum_count number := 0;
    v_cum_bytes number := 0;

    v_owner varchar_list;
    v_segment_name varchar_list;
    v_segment_type varchar_list;
    v_partitioned varchar_list;

    v_longop_rindex pls_integer;
    v_longop_slno pls_integer;
    v_job pls_integer;
    v_job_plsql varchar2(240);

    procedure process_segment(p_owner
    varchar2,p_segment_name varchar2,
    p_segment_type
    varchar2,p_part_name varchar2 default null,
    p_granularity varchar2
    default 'GLOBAL') is
    v_total_blocks number;
    v_total_bytes number;
    v_unused_blocks number;
    v_unused_bytes number;
    v_last_file_id number;
    v_last_block_id number;
    v_last_block number;
    v_amount_to_analyze number;
    v_ana_command varchar2(500);
    begin


    dbms_application_info.set_client_info(p_owner||','||p_segment_name||','||p_segment_type||','||p_part_name);
    dbms_space.unused_space (
    p_owner,
    p_segment_name,
    p_segment_type,
    v_total_blocks,
    v_total_bytes,
    v_unused_blocks,
    v_unused_bytes,
    v_last_file_id,
    v_last_block_id,
    v_last_block,
    p_part_name);

    --
    -- This gives a reasonable degree of analysis. Up
    to
    about 10M is effectively a compute, and
    -- it reduces from there, eventually down to about
    0.5% for a 1G segment
    -- The formula is: percent to analyze := 500 *
    power(used megabytes,-1.05)
    -- with a ceiling of 99.99 percent (since dbms_stats
    does not allow a '100' to be passed)
    --
    v_amount_to_analyze :=
    least(99.99,round(131072000*power(greatest(v_total_bytes-v_unused_bytes,1),-1.05),5));
    if p_debug > 0 then
    dbms_output.put_line(p_segment_type||':
    '||p_owner||'.'||p_segment_name||' '||p_part_name);
    dbms_output.put_line(v_total_bytes||' bytes
    allocated');

    dbms_output.put_line((v_total_bytes-v_unused_bytes)||'
    bytes in use');
    dbms_output.put_line('Analyze
    '||nvl(v_amount_to_analyze,100)||'%');
    dbms_output.put_line('---------------------');
    end if;

    dbms_application_info.set_client_info('Obj:
    '||v_cum_count||' '||p_owner||'.'||p_segment_name||'
    '||

    (v_total_bytes-v_unused_bytes)||' byt
    '||nvl(v_amount_to_analyze,100)||'%');

    if p_debug < 2 then

    dbms_application_info.set_session_longops(v_longop_rindex,
    v_longop_slno,
    'Analyze', 0, 0, v_cum_count,
    v_tot_count, p_segment_type, 'objects');
    if p_segment_type like 'TABLE%' then --
    could be a table or a table partition
    sys.dbms_stats.gather_table_stats(
    ownname=>'"'||p_owner||'"',
    tabname=>'"'||p_segment_name||'"',
    granularity=>'ALL',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    else
    sys.dbms_stats.gather_index_stats(
    ownname=>'"'||p_owner||'"',
    indname=>'"'||p_segment_name||'"',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    end if;
    v_cum_count := v_cum_count + 1;
    v_cum_bytes := v_cum_bytes +
    v_total_bytes-v_unused_bytes;
    end if;
    end;

    begin
    if upper(p_mode) not in ('A','S') then
    raise_application_error(-20000,'Mode must be A
    or
    S');
    end if;
    if p_parallel > 1 and upper(p_mode) != 'A' then
    raise_application_error(-20000,'Cannot run
    parallel in synchronous mode');
    end if;
    if p_parallel not between 1 and 4 then
    raise_application_error(-20000,'Parallel
    limited
    to 1 to 4');
    end if;

    if p_parallel > 1 or p_mode = 'A' then
    for i in 1 .. p_parallel loop
    v_job_plsql :=
    'dbstat.analyze_db('''||p_owner||''',0,'''||p_segment_type||
    ''',1,''S'','||p_parallel||','||(i-1)||');';
    if p_debug > 0 then
    dbms_output.put_line('Job: '||v_job_plsql);
    end if;
    if p_debug < 2 then
    dbms_job.submit(v_job,v_job_plsql);
    end if;
    end loop;
    commit;
    return;
    end if;

    dbms_output.put_line('---------------------');

    dbms_output.enable(999999);

    if p_debug > 0 then
    execute immediate 'alter session set
    sort_area_size = 8192000';
    execute immediate 'alter session set
    sort_area_retained_size = 8192000';
    v_longop_rindex :=
    dbms_application_info.set_session_longops_nohint;
    end if;

    SELECT segment_TYPE,segment_NAME,owner,
    decode(subname,null,'NO','YES') partitioned
    bulk collect into
    v_segment_type, v_segment_name, v_owner,
    v_partitioned
    from ( select u.name owner, o.name segment_name,
    o.subname, so.object_type segment_type, o.obj#
    object_id
    from sys.user$ u, sys.obj$ o, sys.ts$ ts,
    sys.seg$ s, sys.file$ f,
    ( select 'TABLE' object_type, 2
    object_type_id, 5 segment_type_id, t.obj#, t.file#,
    t.block#, t.ts#
    from sys.tab$ t
    where bitand(t.property, 1024) = 0
    and bitand(t.property,8192) = 0
    union all
    select 'INDEX', 1, 6, i.obj#,
    i.file#, i.block#, i.ts#
    from sys.ind$ i
    where i.type# in (1, 2, 3, 4, 6, 7,
    9)
    ) so
    where s.file# = so.file#
    and s.block# = so.block#
    and s.ts# = so.ts#
    and s.ts# = ts.ts#
    and o.obj# = so.obj#
    and o.owner# = u.user#
    and s.type# = so.segment_type_id
    and o.type# = so.object_type_id
    and s.ts# = f.ts#
    and s.file# = f.relfile#
    and u.name not in ('SYS','SYSTEM'))
    where owner = nvl(upper(p_owner),owner)
    and segment_type =
    nvl(upper(p_segment_type),segment_type)
    and mod(object_id,p_int1) = p_int2;

    v_tot_count := v_segment_type.count;
    for i in v_segment_type.first ..
    v_segment_type.last
    loop
    if v_partitioned(i) = 'YES' then
    for j in (
    SELECT O.SUBNAME PART_NAME,
    decode(O.TYPE#,19,'TABLE
    PARTITION',
    20,'INDEX
    PARTITION',
    34,'TABLE
    SUBPARTITION',
    35,'INDEX
    SUBPARTITION') segment_type
    FROM SYS.USER$ U,SYS.OBJ$ O
    WHERE U.NAME = v_owner(i)
    AND O.OWNER# = U.USER#
    AND O.NAME = v_segment_name(i)
    AND O.TYPE# in (19,20,34,35)
    ORDER BY PART_NAME ) loop

    process_segment(v_owner(i),v_segment_name(i),j.segment_type,j.part_name,'PARTITION');
    end loop;
    else

    process_segment(v_owner(i),v_segment_name(i),v_segment_type(i));
    end if;
    end loop;
    dbms_output.put_line('Objects Analyzed:
    '||v_cum_count);
    dbms_output.put_line('Bytes scanned:
    '||v_cum_bytes);
    dbms_output.put_line('Elapsed Time:
    '||round((sysdate-v_start)*86400,1));
    end;

    END;
    /


    --- "Jesse, Rich" wrote:

    Yes, the DBMS_STATS package is very quirky in 8i,
    IMHO. Knowing the bug in
    DATABASE_STATS, I've written a procedure to
    iteratively use SCHEMA_STATS
    instead. Of course, this too has a bug that will
    report ORA-1403 on the
    "first" table in the schema, so I needed to code
    around that.

    And for all this trouble Oracle still recommends
    using DBMS_STATS over
    ANALYZE. Then fix it!

    Rich Jesse
    System/Database
    Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech
    International, Sussex, WI USA

    -----Original Message-----
    From: Post, Ethan
    Sent: Friday, October 04, 2002 5:09 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS
    analyzes SYS and SYSTEM
    inde


    Arrgggg...

    So anyway, I am thinking, hey it's high time I
    start using DBMS_STATS
    instead of my own procedure so I kick of the following
    (Oracle 8.1.7.4).
    After the first run I have SYS and SYSTEM stats
    on
    indexes
    and on other
    schemas with NO STATS it just ignored those
    tables
    even
    though you can see I
    have GATHER EMPTY below. So I kick it off again and guess
    what, it starts
    analyzing the tables it missed the first time,
    including SYS
    and SYSTEM.
    Guess I am going to use
    DBMS_STATS.GATHER_TABLE_STATS and be
    a bit more
    specific about what I get.
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    =====
    Connor McDonald
    http://www.oracledba.co.uk
    http://www.oaktable.net

    "Remember amateurs built the ark - Professionals
    built the Titanic"

    __________________________________________________
    Do You Yahoo!?
    Everything you'll ever need on one web page
    from News and Sport to Email and Music Charts
    http://uk.my.yahoo.com
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: =?iso-8859-1?q?Connor=20McDonald?=
    INET: hamcdc_at_yahoo.co.uk

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in the message BODY, include a line
    containing: UNSUB ORACLE-L (or the name of mailing
    list you want to be removed from). You may also
    send the HELP command for other information (like
    subscribing).
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: MacGregor, Ian A.
    INET: ian_at_SLAC.Stanford.EDU

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    Connor McDonald
    http://www.oracledba.co.uk
    http://www.oaktable.net

    "Remember amateurs built the ark - Professionals built the Titanic"

    Do You Yahoo!?
    Everything you'll ever need on one web page
    from News and Sport to Email and Music Charts
    http://uk.my.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: =?iso-8859-1?q?Connor=20McDonald?=
    INET: hamcdc_at_yahoo.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Connor McDonald at Oct 8, 2002 at 9:11 am
    Had it for ages - I think I put it together 'round
    8.1.5 when dbms_stats was looking good, but still
    didn't work at all well at the SCHEMA or DATABASE
    level....which of course, is probably still the case
    :-)

    The main thing of course was being able to control the
    sample size based on segment size. 9.x now offers
    that, but I don't particularly like the rather
    expensive way it does it.

    hth
    connor

    Cherie_Machler_at_gelco.com wrote: >
    Connor,

    What version of Oracle was this coded for?

    Thanks,

    Cherie





    Connor


    McDonald To:
    Multiple recipients of list ORACLE-L




    co.uk> Subject:
    RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS
    and
    Sent by: SYSTEM
    inde

    root_at_fatcity.c


    om








    10/07/02 11:18


    AM


    Please respond


    to ORACLE-L












    Here is a work-in-progress utility, ie, I'm posting
    this on an "all care, no responsibility" basis.
    "Features" include:

    - will process all schemas or a nominated one
    - has been deliberately restricted tables and
    indexes
    (so if you want lobs etc, you'll need to edit it a
    little)
    - can run in synchronous (foreground) mode or
    asynchronous ( submits itself as a dbms_job)
    - can run in parallel (multiple streams done via
    modulo the object_id)
    - has a debugging mode
    - uses dbms_space to derive a meaningful estimate
    size
    for each segment
    - records progress in v$session_longops
    - doesn't go against DBA_SEGMENTS 'cos thats so slow
    - cranks up sort_area_size to improve perf.

    Cheers
    Connor

    create or replace
    package system.dbstat is

    procedure analyze_db (
    p_owner varchar2 default null,
    --
    if only one owner to be processed
    p_debug number default 0,
    --
    0=do work, 1=msgs+work, 2=msgs only
    p_segment_type varchar2 default null,
    --
    TABLE or INDEX
    p_parallel number default 1,
    --
    concurrency (>1 means must be asych)
    p_mode varchar2 default 'S',
    --
    A=run as dbms_job, S=run synchronous
    p_int1 number default 1,
    --
    internal use only
    p_int2 number default 0);
    --
    internal use only
    end;
    /
    create or replace
    package body system.dbstat is

    --
    -- Routines
    --

    procedure analyze_db (
    p_owner varchar2 default null,
    --
    if only one owner to be processed
    p_debug number default 0,
    --
    0=do work, 1=msgs+work, 2=msgs only
    p_segment_type varchar2 default null,
    --
    TABLE or INDEX
    p_parallel number default 1,
    --
    concurrency (>1 means must be asych)
    p_mode varchar2 default 'S',
    --
    A=run as dbms_job, S=run synchronous
    p_int1 number default 1,
    --
    internal use only
    p_int2 number default 0) is
    --
    internal use only

    type varchar_list is table of varchar2(80);

    v_start date := sysdate;
    v_tot_count number := 0;
    v_cum_count number := 0;
    v_cum_bytes number := 0;

    v_owner varchar_list;
    v_segment_name varchar_list;
    v_segment_type varchar_list;
    v_partitioned varchar_list;

    v_longop_rindex pls_integer;
    v_longop_slno pls_integer;
    v_job pls_integer;
    v_job_plsql varchar2(240);

    procedure process_segment(p_owner
    varchar2,p_segment_name varchar2,
    p_segment_type
    varchar2,p_part_name varchar2 default null,
    p_granularity varchar2
    default 'GLOBAL') is
    v_total_blocks number;
    v_total_bytes number;
    v_unused_blocks number;
    v_unused_bytes number;
    v_last_file_id number;
    v_last_block_id number;
    v_last_block number;
    v_amount_to_analyze number;
    v_ana_command varchar2(500);
    begin

    dbms_application_info.set_client_info(p_owner||','||p_segment_name||','
    p_segment_type||','||p_part_name);
    dbms_space.unused_space (
    p_owner,
    p_segment_name,
    p_segment_type,
    v_total_blocks,
    v_total_bytes,
    v_unused_blocks,
    v_unused_bytes,
    v_last_file_id,
    v_last_block_id,
    v_last_block,
    p_part_name);

    --
    -- This gives a reasonable degree of analysis. Up
    to
    about 10M is effectively a compute, and
    -- it reduces from there, eventually down to about
    0.5% for a 1G segment
    -- The formula is: percent to analyze := 500 *
    power(used megabytes,-1.05)
    -- with a ceiling of 99.99 percent (since dbms_stats
    does not allow a '100' to be passed)
    --
    v_amount_to_analyze :=
    least(99.99,round(131072000*power(greatest(v_total_bytes-v_unused_bytes,1),
    -1.05),5));

    if p_debug > 0 then
    dbms_output.put_line(p_segment_type||':
    '||p_owner||'.'||p_segment_name||' '||p_part_name);
    dbms_output.put_line(v_total_bytes||' bytes
    allocated');

    dbms_output.put_line((v_total_bytes-v_unused_bytes)||'
    bytes in use');
    dbms_output.put_line('Analyze
    '||nvl(v_amount_to_analyze,100)||'%');
    dbms_output.put_line('---------------------');
    end if;

    dbms_application_info.set_client_info('Obj:
    '||v_cum_count||' '||p_owner||'.'||p_segment_name||'
    '||

    (v_total_bytes-v_unused_bytes)||' byt
    '||nvl(v_amount_to_analyze,100)||'%');

    if p_debug < 2 then

    dbms_application_info.set_session_longops(v_longop_rindex,
    v_longop_slno,
    'Analyze', 0, 0, v_cum_count,
    v_tot_count,
    p_segment_type, 'objects');
    if p_segment_type like 'TABLE%' then --
    could be a table or a table partition
    sys.dbms_stats.gather_table_stats(
    ownname=>'"'||p_owner||'"',
    tabname=>'"'||p_segment_name||'"',
    granularity=>'ALL',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    else
    sys.dbms_stats.gather_index_stats(
    ownname=>'"'||p_owner||'"',
    indname=>'"'||p_segment_name||'"',
    estimate_percent=>v_amount_to_analyze,
    partname=>p_part_name);
    end if;
    v_cum_count := v_cum_count + 1;
    v_cum_bytes := v_cum_bytes +
    v_total_bytes-v_unused_bytes;
    end if;
    end;

    begin
    if upper(p_mode) not in ('A','S') then
    raise_application_error(-20000,'Mode must be A
    or
    S');
    end if;
    if p_parallel > 1 and upper(p_mode) != 'A' then
    raise_application_error(-20000,'Cannot run
    parallel in synchronous mode');
    end if;
    if p_parallel not between 1 and 4 then
    raise_application_error(-20000,'Parallel
    limited
    to 1 to 4');
    end if;

    if p_parallel > 1 or p_mode = 'A' then
    for i in 1 .. p_parallel loop
    v_job_plsql :=
    'dbstat.analyze_db('''||p_owner||''',0,'''||p_segment_type||
    ''',1,''S'','||p_parallel||','||(i-1)||');';
    if p_debug > 0 then
    dbms_output.put_line('Job: '||v_job_plsql);
    end if;
    if p_debug < 2 then
    dbms_job.submit(v_job,v_job_plsql);
    end if;
    end loop;
    commit;
    return;
    end if;

    dbms_output.put_line('---------------------');

    dbms_output.enable(999999);

    if p_debug > 0 then
    execute immediate 'alter session set
    sort_area_size = 8192000';
    execute immediate 'alter session set
    sort_area_retained_size = 8192000';
    v_longop_rindex :=
    dbms_application_info.set_session_longops_nohint;
    end if;

    SELECT segment_TYPE,segment_NAME,owner,
    decode(subname,null,'NO','YES') partitioned
    bulk collect into
    v_segment_type, v_segment_name, v_owner,
    v_partitioned
    from ( select u.name owner, o.name segment_name,
    o.subname, so.object_type segment_type, o.obj#
    object_id
    from sys.user$ u, sys.obj$ o, sys.ts$ ts,
    sys.seg$ s, sys.file$ f,
    ( select 'TABLE' object_type, 2
    object_type_id, 5 segment_type_id, t.obj#, t.file#,
    t.block#, t.ts#
    from sys.tab$ t
    where bitand(t.property, 1024) = 0
    and bitand(t.property,8192) = 0
    union all
    select 'INDEX', 1, 6, i.obj#,
    i.file#,
    i.block#, i.ts#
    from sys.ind$ i
    where i.type# in (1, 2, 3, 4, 6, 7,
    9)
    ) so
    where s.file# = so.file#
    and s.block# = so.block#
    and s.ts# = so.ts#
    and s.ts# = ts.ts#
    and o.obj# = so.obj#
    and o.owner# = u.user#
    and s.type# = so.segment_type_id
    and o.type# = so.object_type_id
    and s.ts# = f.ts#
    and s.file# = f.relfile#
    and u.name not in ('SYS','SYSTEM'))
    where owner = nvl(upper(p_owner),owner)
    and segment_type =
    nvl(upper(p_segment_type),segment_type)
    and mod(object_id,p_int1) = p_int2;

    v_tot_count := v_segment_type.count;
    for i in v_segment_type.first ..
    v_segment_type.last
    loop
    if v_partitioned(i) = 'YES' then
    for j in (
    SELECT O.SUBNAME PART_NAME,
    decode(O.TYPE#,19,'TABLE
    PARTITION',
    20,'INDEX
    PARTITION',
    34,'TABLE
    SUBPARTITION',
    35,'INDEX
    SUBPARTITION') segment_type
    FROM SYS.USER$ U,SYS.OBJ$ O
    WHERE U.NAME = v_owner(i)
    AND O.OWNER# = U.USER#
    AND O.NAME = v_segment_name(i)
    AND O.TYPE# in (19,20,34,35)
    ORDER BY PART_NAME ) loop

    process_segment(v_owner(i),v_segment_name(i),j.segment_type,j.part_name,'PARTITION');
    end loop;
    else

    process_segment(v_owner(i),v_segment_name(i),v_segment_type(i));
    end if;
    end loop;
    dbms_output.put_line('Objects Analyzed:
    '||v_cum_count);
    dbms_output.put_line('Bytes scanned:
    '||v_cum_bytes);
    dbms_output.put_line('Elapsed Time:
    '||round((sysdate-v_start)*86400,1));
    end;

    END;
    /


    --- "Jesse, Rich" wrote:

    Yes, the DBMS_STATS package is very quirky in 8i,
    IMHO. Knowing the bug in
    DATABASE_STATS, I've written a procedure to
    iteratively use SCHEMA_STATS
    instead. Of course, this too has a bug that will
    report ORA-1403 on the
    "first" table in the schema, so I needed to code
    around that.

    And for all this trouble Oracle still recommends
    using DBMS_STATS over
    ANALYZE. Then fix it!

    Rich Jesse
    System/Database
    Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech
    International, Sussex, WI USA

    -----Original Message-----
    From: Post, Ethan
    Sent: Friday, October 04, 2002 5:09 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: DBMS_STATS.GATHER_DATABASE_STATS
    analyzes SYS and SYSTEM
    inde


    Arrgggg...

    So anyway, I am thinking, hey it's high time I
    start using DBMS_STATS
    instead of my own procedure so I kick of the following
    (Oracle 8.1.7.4).
    After the first run I have SYS and SYSTEM stats
    on
    indexes
    and on other
    schemas with NO STATS it just ignored those
    tables
    even
    though you can see I
    have GATHER EMPTY below. So I kick it off again and guess
    what, it starts
    analyzing the tables it missed the first time,
    including SYS
    and SYSTEM.
    Guess I am going to use
    DBMS_STATS.GATHER_TABLE_STATS and be
    a bit more
    specific about what I get.
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    =====
    Connor McDonald
    http://www.oracledba.co.uk
    http://www.oaktable.net

    "Remember amateurs built the ark - Professionals
    built the Titanic"

    __________________________________________________
    Do You Yahoo!?
    Everything you'll ever need on one web page
    from News and Sport to Email and Music Charts
    http://uk.my.yahoo.com
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: =?iso-8859-1?q?Connor=20McDonald?=
    INET: hamcdc_at_yahoo.co.uk

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).




    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author:
    INET: Cherie_Machler_at_gelco.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    Connor McDonald
    http://www.oracledba.co.uk
    http://www.oaktable.net

    "Remember amateurs built the ark - Professionals built the Titanic"

    Do You Yahoo!?
    Everything you'll ever need on one web page
    from News and Sport to Email and Music Charts
    http://uk.my.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: =?iso-8859-1?q?Connor=20McDonald?=
    INET: hamcdc_at_yahoo.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 4, '02 at 10:08p
activeOct 8, '02 at 9:11a
posts12
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase