Home » RDBMS Server » Performance Tuning » Dynamic Table List Selection for STATS
Dynamic Table List Selection for STATS [message #221601] Tue, 27 February 2007 11:02 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

We have 110 tables in PROD schema,
want to gather STATS in 3 different JOBS ( 3 different approach )

1 JOB for 11 huge partitioned tables so daily only current partition P_YYYYMM( P_200702 ) and 1 previous P_YYYYMM (P_200701 ) should be gathered,partition names are same for all 11 tables, so how can i schedule this dynamic selection table list and submit it in DBMS_JOB for daily 12:01:00 a.m

Currently i have this code for whole schema ( which is running out and as well as ORA-01555 since it's very huge tables and OLTP 24/7 Application )

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => '/* job_name: JOB_DAILY_GATHER_STATS */BEGIN dbms_stats.gather_schema_stats(ownname => ''APP'', estimate_percent => NULL, cascade => TRUE); END;'
     ,next_date => to_date('27/02/2007 23:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'TRUNC(SYSDATE + 1, ''MI'')'
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
commit;
END;
/

I know here in this 11 tables daily job i can submit 11 tables X 2 partitions 22 times like this

exec dbms_stats.gather_Table_stats('APP','TABLE_NAME','PART_NAME');

but have to write this 22 times and then change every month..so looking for dynamic options.

2 JOB for every saturday 11.30 PM for whole schema except the above 11 huge tables should be gathered,


3 job for every saturday 2:00:00 a.m 11 huge partitioned tables should be gathered completely, here also i can write fixed like this
 exec dbms_stats.gather_table_Stats('APP','TABLE_NAME')
-- 11 times,another doubt here is should an option of % should be selected on huge tables.

Please help in building this dynamic selection of tables for submitting in DBMS_JOB


Thanks

Abdul
Re: Dynamic Table List Selection for STATS [message #221652 is a reply to message #221601] Tue, 27 February 2007 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>but have to write this 22 times and then change every month..so looking for dynamic options.
Write SQL to write SQL that contains all permutations & combinations.
Re: Dynamic Table List Selection for STATS [message #221853 is a reply to message #221601] Wed, 28 February 2007 12:45 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
As Anacedent said, writing sql to write sql can be very handy.

MYDBA@orcl > select 'exec dbms_stats.gather_table_stats(' || user || ',' || table_name || ');' statement
  2  from user_tables;

STATEMENT
--------------------------------------------------------------------------------------------------
exec dbms_stats.gather_table_stats(MYDBA,EMP);
exec dbms_stats.gather_table_stats(MYDBA,DEPT);


You may also want to look into letting oracle monitor itself whether or not enough data has changed to warrant gathering new statistics.
Re: Dynamic Table List Selection for STATS [message #221885 is a reply to message #221853] Wed, 28 February 2007 15:26 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks anacedent and Smartin for followup's

Smartin >>" You may also want to look into letting oracle monitor itself whether or not enough data has changed to warrant gathering new statistics."

Do you mean to say that we should go with this option
 estimate_percent=>dbms_stats.auto_sample_size);



Thanks

Re: Dynamic Table List Selection for STATS [message #222000 is a reply to message #221601] Thu, 01 March 2007 07:40 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Read the docs for dbms_stats, as well as in the 10gR2 performance tuning guide (Chapter 14).
Previous Topic: Alter Session (workareas and area_sizes)
Next Topic: setting db_file_multiblock_read_count
Goto Forum:
  


Current Time: Sat Dec 14 16:13:34 CST 2024