Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Tricky SQL Question -- Solved

Re: Tricky SQL Question -- Solved

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 06 Mar 2003 13:39:29 -0800
Message-ID: <F001.005628B6.20030306133929@fatcity.com>

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

> 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: jonathan_at_jlcomp.demon.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 Thu Mar 06 2003 - 15:39:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US