Very clever ! Can I make a couple of suggestions:
You've got a very large number of tables in one group - and the startup time for the analyze might have a big impact on this group - so how about adding in (say) one second to the analyze type in order to cater for startup. Also - how about taking out any tables which individually take up more than the sum(all_times)/count(streams) before running the query on the rest. You might try randomising the ordering for the rest of the tables instead of ordering them by analyze time (since you have a large number and a lot use very small times) - I suspect this would help to flatten out the peaks in the timing, and make the number of tables per stream much more even - so reducing the effect of startup times. I have a very simple-minded (sub-optimal) procedural solution, but I'm trying to work out a way of expressing it non-procedurally. If I succeed I'll let you know. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______March 19th ____UK_______April 8th ____UK_______April 22nd ____USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 06 March 2003 19:38 > Okay, > > I cracked it ... if you are interested, read on ... it is not very optimal, > but close to what I want. To me 8 streams is standard, so you'd see 8 as > hardcoded. Also I found that > > select sum(obj_last_analyze_time)/8 from statistics_info > / > > was about 8425 (i.e. ~ 85 seconds). > > So I wrote this not-so-dynamic sql > > select group_id, sum(tm1), count(*) > from( > SELECT obj_owner, obj_name, tm1, > case when roll_sum <= 8400*1 then 1 else > case when roll_sum <= 8400*2 then 2 else > case when roll_sum <= 8400*3 then 3 else > case when roll_sum <= 8400*4 then 4 else > case when roll_sum <= 8400*5 then 5 else > case when roll_sum <= 8400*6 then 6 else > case when roll_sum <= 8400*7 then 7 else 8 > end > end > end > end > end > end > end group_id > FROM (SELECT rnum, obj_owner, obj_name, tm1, > SUM (tm1) OVER > (ORDER BY rnum RANGE UNBOUNDED PRECEDING) roll_sum > FROM (SELECT ROWNUM rnum, obj_owner, obj_name, tm1 > FROM (SELECT obj_owner ,obj_name,obj_last_analyze_time tm1 > FROM statistics_info > ORDER BY obj_last_analyze_time))) > ) group by group_id > / > > The output is as follows ... > > "GROUP_ID" "TOT_TIME" "TOT_TABLES" > ---------- ------------ ------------ > 1 8397 1755 > 2 8387 667 > 3 8204 135 > 4 7984 20 > 5 8954 7 > 6 6928 3 > 7 7113 2 > 8 11438 1 > > I'll probably make it dynamic enough ... inside my package ... > Cheers > Raj > ------------------------------------------------------------- > Rajendra dot Jamadagni at espn dot com > Any views expressed here are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art !! > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
