| Dynamic Table List Selection for STATS [message #221601] |
Tue, 27 February 2007 11:02  |
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')
Please help in building this dynamic selection of tables for submitting in DBMS_JOB
Thanks
Abdul
|
|
|
|
|
|
| Re: Dynamic Table List Selection for STATS [message #221853 is a reply to message #221601] |
Wed, 28 February 2007 12:45  |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
