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
> <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:
>
> - 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" <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).
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).
Received on Tue Oct 08 2002 - 04:11:08 CDT
