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: SOME SOLUTIONS!

RE: SOME SOLUTIONS!

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Thu, 08 Aug 2002 07:43:35 -0800
Message-ID: <F001.004AF0AB.20020808074335@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


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_at_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_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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Paula_Stankus_at_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_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 Aug 08 2002 - 10:43:35 CDT

Original text of this message

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