| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde
Connor,
What version of Oracle was this coded for?
Thanks,
Cherie
Connor
McDonald To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
<hamcdc_at_yahoo. cc:
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:
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" <Rich.Jesse_at_qtiworld.com> 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! <sigh>
>
> Rich Jesse System/Database
> Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech
> International, Sussex, WI USA
>
>
> > -----Original Message-----
> > From: Post, Ethan [mailto:Ethan.Post_at_ps.net]
> > 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).
Received on Mon Oct 07 2002 - 11:53:53 CDT
 |
 |