Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g78FVeK24449 for ; Thu, 8 Aug 2002 11:31:40 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id IAA14265; Thu, 8 Aug 2002 08:31:36 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 004AF0FB; Thu, 08 Aug 2002 07:53:27 -0800 Message-ID: Date: Thu, 08 Aug 2002 07:53:27 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Paula_Stankus@doh.state.fl.us Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Paula_Stankus@doh.state.fl.us Subject: RE: SOME SOLUTIONS! Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C23EEA.F48484B0" ------_=_NextPart_001_01C23EEA.F48484B0 Content-Type: text/plain Guys, Any ideas why sum - with same exec. plan and not going to disk is 5 sec. response versus subsecond and anyway to get around this w/o building a preaggregated data set since there are so many permutations possible???? Will I have to redesign to use STAR SCHEMA and will that really help!!! -----Original Message----- Sent: Thursday, August 08, 2002 10:40 AM To: 'ORACLE-L@fatcity.com' Okay, Just going from #1 query to #2 query went from subsecond response to 6 secs. Not to mention for each sum it gets progressively worse!!!. The explain plan is exactly the same and I ran the sort query below and no rows returned. rem truncate table plan_table; set timing on; rem explain plan set statement_id='RON' for select /*+ INDEX(mv_birthstat,ndx_mvyr) */ brth_mthr_res_newco_code County,count(*), -- sum(cert_chld_brth_year) Num1 --sum(decode(greatest(cert_chld_brth_year,1996), least(cert_chld_brth_year,1998 ), 1, 0)) / 3 Num2, --sum(decode(greatest(cert_chld_brth_year,1997), least(cert_chld_brth_year,1999 ), 1, 0)) / 3 Num3 from mv_birthstat where cert_chld_brth_year between '1995' and '1999' and brth_mthr_res_state_code='10' and brth_mthr_age between 15 and 41 group by brth_mthr_res_newco_code; /*+ INDEX(mv_birthstat,ndx_mvyr) */ brth_mthr_res_newco_code County, sum(cert_chld_brth_year) Num1 --sum(decode(greatest(cert_chld_brth_year,1996), least(cert_chld_brth_year,1998 ), 1, 0)) / 3 Num2, --sum(decode(greatest(cert_chld_brth_year,1997), least(cert_chld_brth_year,1999 ), 1, 0)) / 3 Num3 from mv_birthstat where cert_chld_brth_year between '1995' and '1999' and brth_mthr_res_state_code='10' and brth_mthr_age between 15 and 41 group by brth_mthr_res_newco_code; WHY WHY WHY WHY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -----Original Message----- Sent: Wednesday, August 07, 2002 11:37 PM To: Multiple recipients of list ORACLE-L Paula, Sorry to join this thread late, if I am rehashing just igonre. I notice that your 'numbers' are surrounded by quotes, which will implicitly disable the index on those columns, perhaps the cause of the index hint. Are those columns text or numbers? If numbers, take out the quotes (and perhaps the hint) and rerun query. Also, your aggregate functions will cause sorting - are you sorting to disk? See the query at the end of this to find out. If so, perhaps a larger sort_area_size and sort_area_retained_size might be in order. Again, ignore if redundant. Run this to look at sorting: select t1.tablespace , extents , sum( t1.blocks * to_number( t3.value ) ) / 1024 / 1024 mb_used , sum( t1.extents ) tot_extents , t2.username , t2.osuser , t2.SID ||','|| t2.serial# SID_PID , t4.spid , t5.sql_text , t1.segtype from v$sort_usage t1 ,v$session t2 ,v$parameter t3 ,v$process t4 ,v$sqlarea t5 where t1.SESSION_ADDR = t2.SADDR and t3.name = 'db_block_size' and (t2.PROCESS=t4.SPID or t2.paddr = t4.addr) and t2.sql_address=t5.address and t2.status = 'ACTIVE' group by t1.tablespace , t2.username , t2.osuser , t2.machine , t2.schemaname , t2.program , t2.SID ||','|| t2.serial# , t4.spid , t5.sql_text , t1.segtype , segfile# , extents / hth, Jack --- Paula_Stankus@doh.state.fl.us wrote: > Guys, > > By changing objects to noparallel, creating > bitmapped index containing all > columns that was local and prefixed. Moving to > 8.1.7.4 I was able to > improve performance from minutes to many seconds to > seconds for the > following (not using 'in' or 'or' in predicate > helped too): > select > /*+ INDEX(mv_birthstat,ndx_mvyr) */ > brth_mthr_res_newco_code County,count(*) > from mv_birthstat > where cert_chld_brth_year between '1995' and '1999' > and brth_mthr_res_state_code='10' > and brth_mthr_age between 15 and 41 > group by brth_mthr_res_newco_code; > > Then adding the following the query takes 15 seconds > again - any ideas for > speeding it up? Please don't say hire a new DBA. I > have really been > working on this hard with only annoying input from > Oracle's technical > support and a lot of good reference manuals > (Jonathon Lewis, 101 Perf. > Tuning, Oracle SQL - Guy Harrison) for my > companions. Great I know but > lonely. > > select > /*+ INDEX(mv_birthstat,ndx_mvyr) */ > brth_mthr_res_newco_code County > sum(decode(greatest(cert_chld_birth_year,1995), > least(cert_chld_birth_year,1997), 1, 0)) / 3 Num1, > sum(decode(greatest(cert_chld_birth_year,1996), > least(cert_chld_birth_year,1998), 1, 0)) / 3 Num2, > sum(decode(greatest(cert_chld_birth_year,1997), > least(cert_chld_birth_year,1999), 1, 0)) / 3 Num3 > from mv_birthstat > where cert_chld_brth_year between '1995' and '1999' > and brth_mthr_res_state_code='10' > and brth_mthr_age between 15 and 41 > group by brth_mthr_res_newco_code; > > I am going to try the sums in a outer query. > > > > -----Original Message----- > Sent: Wednesday, August 07, 2002 4:45 PM > To: Stankus, Paula G; 'ORACLE-L@fatcity.com' > > > Mother's state 80% are in Florida. However, this is > a 'local' prefixed > bitmap index. I would expect to use year to > eliminate partion. then w/in > year by state - second col. in query. > > -----Original Message----- > Sent: Wednesday, August 07, 2002 4:35 PM > To: Stankus, Paula G; 'ORACLE-L@fatcity.com' > > > BTW, > > SQL> select blocks from dba_tables > 2 where table_name = 'MV_BIRTHSTAT'; > > BLOCKS > ---------- > 236542 > > SQL> select clustering_factor from user_indexes > 2 where table_name='MV_BIRTHSTAT'; > > CLUSTERING_FACTOR > ----------------- > 1657 > 170 > > -----Original Message----- > Sent: Wednesday, August 07, 2002 4:28 PM > To: Stankus, Paula G; 'ORACLE-L@fatcity.com' > > > Help - weird performance problem!!! > > ---------------------------------------------------------------------------- > ---- > | Operation | Name | Rows | > Bytes| Cost | Pstart| > Pstop | > ---------------------------------------------------------------------------- > ---- > | SELECT STATEMENT | | 1 | > 5 | 9331 | | > | > | SORT AGGREGATE | | 1 | > 5 | | | > | > | SORT AGGREGATE | | 1 | > 5 | | | > | > | INLIST ITERATOR | | | > | | | > | > | PARTITION RANGE ITERAT| | | > | |KEY(I) > |KEY(I) | > | BITMAP CONVERSION COU| | | > | | | > | > | BITMAP INDEX RANGE S|NDX_MVYRS | | > | |KEY(I) > |KEY(I) | > > Cost higher but less than a second. > > Ignore the cost - higher cost was sign. > Faster!!!!!Does Key(*) mean it is > doing part. Elimin. I think so but can you see the > inlist? > > Very fast with all years involved. Then added > predicate: > > select > /*+ INDEX(mv_birthstat,ndx_mvyrstatecoage) */ > count(*) from mv_birthstat > where cert_chld_brth_year > in('1995','1996','1997','1998','1999') > and brth_MTHR_res_STATE_CODE = '10'; > > - snails crawl by just adding the additional > column!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! > __________________________________________________ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: jack_silvey@yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). ------_=_NextPart_001_01C23EEA.F48484B0 Content-Type: text/html Content-Transfer-Encoding: quoted-printable RE: SOME SOLUTIONS!

Guys,

Any ideas why sum - with same exec. plan and not = going to disk is 5 sec. response versus subsecond and anyway to get = around this w/o building a preaggregated data set since there are so = many permutations possible????  Will I have to redesign to use = STAR SCHEMA and will that really help!!!

-----Original Message-----
From: Stankus, Paula G
Sent: Thursday, August 08, 2002 10:40 AM
To: 'ORACLE-L@fatcity.com'
Subject: RE: SOME SOLUTIONS!


Okay,

Just going from #1 query to #2 query went from = subsecond response to 6 secs.  Not to mention for each sum it gets = progressively worse!!!.  The explain plan is exactly the same and = I ran the sort query below and no rows returned.

rem truncate table plan_table;
set timing on;
rem explain plan set statement_id=3D'RON' for
select
/*+ INDEX(mv_birthstat,ndx_mvyr) */
 brth_mthr_res_newco_code  = County,count(*),
-- sum(cert_chld_brth_year) Num1
 --sum(decode(greatest(cert_chld_brth_year,1996), = least(cert_chld_brth_year,1998
), 1, 0)) / 3 Num2,
 --sum(decode(greatest(cert_chld_brth_year,1997), = least(cert_chld_brth_year,1999
), 1, 0)) / 3 Num3
from mv_birthstat
where cert_chld_brth_year between '1995' and = '1999'
and brth_mthr_res_state_code=3D'10'
and brth_mthr_age between 15 and 41
group by brth_mthr_res_newco_code;

/*+ INDEX(mv_birthstat,ndx_mvyr) */
 brth_mthr_res_newco_code  County,
 sum(cert_chld_brth_year) Num1
 --sum(decode(greatest(cert_chld_brth_year,1996), = least(cert_chld_brth_year,1998
), 1, 0)) / 3 Num2,
 --sum(decode(greatest(cert_chld_brth_year,1997), = least(cert_chld_brth_year,1999
), 1, 0)) / 3 Num3
from mv_birthstat
where cert_chld_brth_year between '1995' and = '1999'
and brth_mthr_res_state_code=3D'10'
and brth_mthr_age between 15 and 41
group by brth_mthr_res_newco_code;



WHY WHY WHY = WHY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-----Original Message-----
From: Jack Silvey [mailto:jack_silvey@yahoo.com]<= /FONT>
Sent: Wednesday, August 07, 2002 11:37 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SOME SOLUTIONS!


Paula,

Sorry to join this thread late, if I am rehashing = just
igonre.

I notice that your 'numbers' are surrounded by = quotes,
which will implicitly disable the index on = those
columns, perhaps the cause of the index hint. Are
those columns text or numbers? If numbers, take = out
the quotes (and perhaps the hint) and rerun = query.

Also, your aggregate functions will cause sorting = -
are you sorting to disk? See the query at the end = of
this to find out. If so, perhaps a larger
sort_area_size and sort_area_retained_size might be = in
order.

Again, ignore if redundant.

Run this to look at sorting:

select t1.tablespace
, extents
, sum( t1.blocks * to_number( t3.value ) ) / 1024 = /
1024 mb_used
, sum( t1.extents ) tot_extents
, t2.username
, t2.osuser
, t2.SID ||','|| t2.serial# SID_PID
, t4.spid
, t5.sql_text
, t1.segtype
from
v$sort_usage t1
,v$session t2
,v$parameter t3
,v$process t4
,v$sqlarea t5
where t1.SESSION_ADDR =3D t2.SADDR
and t3.name =3D 'db_block_size'
and (t2.PROCESS=3Dt4.SPID or t2.paddr =3D t4.addr) =
and t2.sql_address=3Dt5.address
and t2.status =3D 'ACTIVE'
group by
t1.tablespace
, t2.username
, t2.osuser
, t2.machine
, t2.schemaname
, t2.program
, t2.SID ||','|| t2.serial#
, t4.spid
, t5.sql_text
, t1.segtype
, segfile#
, extents
/


hth,

Jack

--- Paula_Stankus@doh.state.fl.us wrote:
> Guys,
>
> By changing objects to noparallel, = creating
> bitmapped index containing all
> columns that was local and prefixed.  = Moving to
> 8.1.7.4 I was able to
> improve performance from minutes to many = seconds to
> seconds for the
> following (not using 'in' or 'or' in = predicate
> helped too):
> select
> /*+ INDEX(mv_birthstat,ndx_mvyr) */
>  brth_mthr_res_newco_code  = County,count(*)
> from mv_birthstat
> where cert_chld_brth_year between '1995' and = '1999'
> and brth_mthr_res_state_code=3D'10'
> and brth_mthr_age between 15 and 41
> group by brth_mthr_res_newco_code;
>
> Then adding the following the query takes 15 = seconds
> again - any ideas for
> speeding it up?  Please don't say hire a = new DBA.  I
> have really been
> working on this hard with only annoying input = from
> Oracle's technical
> support and a lot of good reference = manuals
> (Jonathon Lewis, 101 Perf.
> Tuning, Oracle SQL - Guy Harrison) for = my
> companions.  Great I know but
> lonely. 
>
> select
> /*+ INDEX(mv_birthstat,ndx_mvyr) */
>  brth_mthr_res_newco_code  = County
>  = sum(decode(greatest(cert_chld_birth_year,1995),
> least(cert_chld_birth_year,1997), 1, 0)) / 3 = Num1,
>  = sum(decode(greatest(cert_chld_birth_year,1996),
> least(cert_chld_birth_year,1998), 1, 0)) / 3 = Num2,
>  = sum(decode(greatest(cert_chld_birth_year,1997),
> least(cert_chld_birth_year,1999), 1, 0)) / 3 = Num3
> from mv_birthstat
> where cert_chld_brth_year between '1995' and = '1999'
> and brth_mthr_res_state_code=3D'10'
> and brth_mthr_age between 15 and 41
> group by brth_mthr_res_newco_code;
>
> I am going to try the sums in a outer = query. 
>
>
>
> -----Original Message-----
> Sent: Wednesday, August 07, 2002 4:45 PM
> To: Stankus, Paula G; = 'ORACLE-L@fatcity.com'
>
>
> Mother's state 80% are in Florida.  = However, this is
> a 'local' prefixed
> bitmap index.  I would expect to use year = to
> eliminate partion. then w/in
> year by state - second col. in query.
>
> -----Original Message-----
> Sent: Wednesday, August 07, 2002 4:35 PM
> To: Stankus, Paula G; = 'ORACLE-L@fatcity.com'
>
>
> BTW,
>
> SQL> select blocks from dba_tables
>   2  where table_name =3D = 'MV_BIRTHSTAT';
>
>     BLOCKS
> ----------
>     236542
>
> SQL> select clustering_factor from = user_indexes
>   2  where = table_name=3D'MV_BIRTHSTAT';
>
> CLUSTERING_FACTOR
> -----------------
>          = ;    1657
>          = ;     170
>
> -----Original Message-----
> Sent: Wednesday, August 07, 2002 4:28 PM
> To: Stankus, Paula G; = 'ORACLE-L@fatcity.com'
>
>
> Help - weird performance problem!!!
>
>
---------------------------------------------------------------= -------------
> ----
> | = Operation          &nb= sp;      |  Name    = |  Rows |
> Bytes|  Cost  | Pstart|
> Pstop |
>
---------------------------------------------------------------= -------------
> ----
> | SELECT = STATEMENT          = |          = |     1 |  
> 5 |   9331 = |       |
> |
> |  SORT = AGGREGATE           = |          = |     1 |  
> 5 |        = |       |
> |
> |   SORT = AGGREGATE          = |          = |     1 |  
> 5 |        = |       |
> |
> |    INLIST = ITERATOR        = |          = |       |   
>  = |        = |       |
> |
> |     PARTITION RANGE = ITERAT|          = |       |   
>  = |        |KEY(I)
> |KEY(I) |
> |      BITMAP = CONVERSION COU|          = |       |   
>  = |        = |       |
> |
> |       BITMAP = INDEX RANGE S|NDX_MVYRS |       = |   
>  = |        |KEY(I)
> |KEY(I) |
>
> Cost higher but less than a second.
>
> Ignore the cost - higher cost was sign.
> Faster!!!!!Does Key(*) mean it is
> doing part. Elimin.  I think so but can = you see the
> inlist?
>
> Very fast with all years involved.  Then = added
> predicate:
>
> select
> /*+ INDEX(mv_birthstat,ndx_mvyrstatecoage) = */
> count(*) from mv_birthstat
> where cert_chld_brth_year
> in('1995','1996','1997','1998','1999')
>    and brth_MTHR_res_STATE_CODE = =3D '10';
>
> - snails crawl by just adding the = additional
> column!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
>


__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
  INET: jack_silvey@yahoo.com

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051
San Diego, = California        -- Public Internet = access / Mailing Lists
---------------------------------------------------------------= -----
To REMOVE yourself from this mailing list, send an = E-Mail message
to: ListGuru@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).

------_=_NextPart_001_01C23EEA.F48484B0-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Paula_Stankus@doh.state.fl.us Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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).