[Oracle-l] 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!

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

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,
=====
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__________________________________________________

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,
=====
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__________________________________________________

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||','
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,
=====
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__________________________________________________

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

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

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)

I just used the bug number window, rest all left to default.

Kirti

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.

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?
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

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: >
dbms_application_info.set_client_info(p_owner||','||p_segment_name||','
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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