OK, I think I'll jump in a little late here (thanks for the plug Lee!). As far as pricing is concerned, it's best if you contact a sales rep (I have noticed an increasing amount of competition around here :>). Broadly, here are the licensing structures:
SQLExpert is now only sold in 3-packs (3 "seat" licenses, unlimited instance connections) or more (5-Packs, 10-Packs etc.). LSE also supports DB2, SQLServer and Sybase - who actually have full rights to sell SQLExpert on their platform. The prices are a little more than the $1,000 per seat mark that Cherie talked of now, since LECCO changed their license structure lately, but not grossly so.. DBExpert is priced slightly differently, as it's now seen as a "Database Tuning" tool, not just "SQL Tuning", though it does still have all the functionality of SQLExpert. I'm not sure if you have seen or used DBExpert Cherie (?). DBExpert also has an "Index Tuning Expert", that runs on the same kind of engine as SQL Optimiser does. It looks at a particular statement, then generates a list of all indexes or index "sets" (such as indexes on columns A+B or A+C or A+B+C or C+A or B+C etc.) that could be used, and shows you the permutations of those that offer alternate execution plans to the current. You can then generate "virtual indexes", to test through a batch run to find those with better response/elapsed times. The "virtual indexes" also mean that the indexes don't have to be physically created as well (only Lecco and Oracle themselves can currently do this *apparently*).. Sorry - bit of a ramble there, and I could still go on for ages about the rest of the new functions such as impact analysis etc. but that would take ages.. Back to pricing: DBExpert has a "seat license", but is also licensed on a "per instance" basis as well. You get your first instance license included in the above price, and then pay to connect to extra instances after that. Discounts apply to multiple "seats", and "instance" licenses get progressively less with the more you buy (2-5, 6-10 etc.). You can read more about each in their respective datasheets: SQLExpert: http://www.cool-tools.co.uk/Products/Docs/LSEOracleDataSheet.pdf DBExpert: http://www.cool-tools.co.uk/Products/Docs/DBExpertDatasheet.pdf Or simply check them out on our site: http://www.cool-tools.co.uk If any of you would like an evaluation copy, or just extra info about the tools and what they can provide give me a shout. I can also help with putting you in touch with a more local point of contact. Downloads are available from the Cool-Tools site, but only by direction from us. If you want to grab a copy, and need a license key, get in touch with me off the list. Hope this has cleared things up a little for you. Regards Mark =================================================== Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] =================================================== http://www.cool-tools.co.uk Maximising throughput & performance -----Original Message----- [EMAIL PROTECTED] Sent: 09 August 2002 14:28 To: Multiple recipients of list ORACLE-L 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]> seat 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: Mark Leith 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).
