| 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!
Cherie:
Do you know anything about the position your company has open for an Oracle DBA (KRHRCS-6)? I sent a cover letter and resume to Ken Dobson this morning.
Thanks,
Ken Janusz
>
> Dennis,
>
> The sales critter could give better pricing for Leccotech's SQL*Expert.
> For a SWAG I believe it's less than a $1000 per seat and I believe that is
> sold with a per-database component to the pricing.
>
> I think there are also discounts when you buy more copies.
>
> It was well worth what we paid for it, in the improvements we saw in
> performance times for some of our worst-performing SQL.
>
> They also have a full-featured tool called DB*Expert that is more
expensive
> and has more features. It's targetted more towards DBAs. SQL*Expert has
> a target-audience of Developers.
>
> Cherie
>
>
>
> DENNIS WILLIAMS
> <DWILLIAMS_at_LIFE To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
> TOUCH.COM> cc:
> Sent by: Subject: RE: SOME SOLUTIONS!
> root_at_fatcity.co
> m
>
>
> 08/08/02 05:36
> PM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> Paula - I agree. I reviewed Quest's SQL Expert about a year ago and was
> also
> disappointed. The advice it gave seemed pretty shallow. It didn't seem to
> suggest some of the newer Oracle features, for example. We didn't buy it.
>
> Cherie - Thanks for the tip on SQL*Expert. Can you give us the ballpark
> pricing, and whether that is per-seat or per-server? Thanks.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
> -----Original Message-----
> Sent: Thursday, August 08, 2002 12:59 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Wow,
>
> Thanks for the advice. I have been using Quest's SQL Expert but it has
> actually given me some bad advice and it doesn't seem to get to the level
> that I am dealing with - bitmap conversion to rowid performance solutions.
> Does anyone know if I am doing something wrong with the tool or if this
> tool
> would be better. It seems it would if it switches order of lines in code,
> arithmetic changes... Does it support the new analytical functions in
> Oracle, bitmapped indexes, etc.?
>
> -----Original Message-----
> <mailto:Cherie_Machler_at_gelco.com> ]
> Sent: Thursday, August 08, 2002 12:44 PM
> To: ORACLE-L_at_fatcity.com
> Cc: Paula_Stankus_at_doh.state.fl.us
>
>
>
> 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-----
> <mailto:jack_silvey_at_yahoo.com> ]
> 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_at_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_at_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_at_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_at_fatcity.com'
> >
> >
> > Help - weird performance problem!!!
> >
> >
> --------------------------------------------------------------------------
--Received on Fri Aug 09 2002 - 12:09:03 CDT
>
>
>
> > ----
> > | 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 <http://health.yahoo.com>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> <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: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: KENNETH JANUSZ INET: kjanusz_at_att.net 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).
 |
 |