REM REM A basic routine to assist with gathering optimizer stats in REM your database, which is designed to offer some improvements REM on the functionality offered by DBMS_STATS REM REM Features: REM - Can be run for all schemas, or a nominated one REM - Can be limited to just tables, indexes or both REM - Can be run in 3 debug modes: REM 0=just do the work REM 1=do the work, spit out debug info REM 2=don't do the work, just spit it what would be done REM - Records it progress in V$SESSION_LONGOPS so you can see REM what is going on REM - Can run synchronously (in the foreground) or asynchronously REM (submits itself as a dbms_job) REM - Can be parallelised, that is, it can split itself into REM multiple streams (based on modulo the object_id) REM REM REM Disclaimer: Normal stuff, this routine is provided on an REM "all care, no responsibility" basis REM (c) Connor McDonald, OakTable REM grant analyze any to system; grant select any table to system; 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 begin 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); exception when others then if sqlcode = -25191 then null; else raise; end if; end; 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; / show errors