| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SOME SOLUTIONS!
Paula,
I'm sorry that I don't have an answer for you with regard to this particular query. However, I've been using a SQL optimization tool called SQL*Expert (or a more feature-rich tool called DB*Expert) that is sold by Leccotech. I've had fantastic results with this tool and I can't recommend it highly enough for SQL that is tough to tune manually.
The tool will generate a vast number of permutations of possible SQL that contains switched order of lines in the code, arithmetic changes, moving variables from one side of the equal sign to the other, and possible hints. Once all these possibilites are generated, it is extremely easy to run them, even with bind variables. You can even configure it that you want to run all the options for a short period of time and stop trying them if they don't succeed right away.
You can download a test copy and probably get a short-term key from the sales rep within a day. It is pretty intuitive and I got it up and running out-of-the-box within 24 hours. I believe their web site is www.leccotech.com. They've got a button on there to register with them and download the tool. Then you can contact the sales rep and have him give you a short-term key before you can install.
Like I said, I've achieved super results tuning with this tool. It's really cut down my tuning time and suggested alternatives that I would have never thought of, not to mention whipping them all out in a very short amount of time.
I should mention that I am not affiliated with the vendor, Leccotech, other than as a customer.
Cherie Machler
Oracle DBA
Gelco Information Network
Paula_Stankus_at_doh.s
tate.fl.us To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity.com Subject: RE: SOME SOLUTIONS!
08/08/02 10:43 AM
Please respond to
ORACLE-L
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-----
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
> ----
> | 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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- 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: Cherie_Machler_at_gelco.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).Received on Thu Aug 08 2002 - 12:48:58 CDT
 |
 |