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 ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 09, 2002 8:28 AM > > 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@LIFE To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > TOUCH.COM> cc: > Sent by: Subject: RE: SOME SOLUTIONS! > [EMAIL PROTECTED] > 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. > [EMAIL PROTECTED] > > -----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:[EMAIL PROTECTED]> ] > Sent: Thursday, August 08, 2002 12:44 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > > > > 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 > > > > > > > [EMAIL PROTECTED] > > tate.fl.us To: Multiple recipients > of > list ORACLE-L <[EMAIL PROTECTED]> > Sent by: cc: > > [EMAIL PROTECTED] 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:[EMAIL PROTECTED]> ] > 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 > > > --- [EMAIL PROTECTED] 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; '[EMAIL PROTECTED]' > > > > > > 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; '[EMAIL PROTECTED]' > > > > > > 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; '[EMAIL PROTECTED]' > > > > > > 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 <http://health.yahoo.com> > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > <http://www.orafaq.com> > -- > Author: Jack Silvey > INET: [EMAIL PROTECTED] > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
