Re: Special Offer for readers of Oracle-L!
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: martinfbrown@xxxxxxxxxxx
- Date: Wed, 13 Oct 2010 13:22:15 -0700
See the previous answer about OEM as well
Quest's SQL Optimizer only does a blind generation of different execution
paths and analysis of missing indexes which DB Optimizer does as well. That
works for fast queries where many different execution plans can be tested in
a reasonable amount of time, but what if you can't find a better execution
path and/or there are no missing indexes and your SQL is still a problem
then you are at a dead end. Time to bring out the pen and paper and try to
understand the query. To understand the query what steps do you take? Well
I've tried to put most of the powerful steps into a diagram called the
Visual SQL Tuning diagram or VST diagram. What is a VST diagram? well that
takes a few minutes to explain, but with a VST diagram I can understand
quickly, easily and powerfully what is going on in the query, immediately
identify flaws in the query and probably most importantly I can find a best
candidate for execution plan. Here is some reading:
- https://sites.google.com/site/embtdbo/tuner - 2cd video at top is good
on VSTs, rest of doc talks about VSTs
-
http://db-optimizer.blogspot.com/2010/09/sql-tuning-best-practice-visualizing.html
-
short high level view of why VSTs are good
- https://sites.google.com/site/embtdbo/sql-tuning-1 - discussion of VST
diagrams, how the work, screen shots are mainly from specs and haven't been
update with the live product
- http://db-optimizer.blogspot.com/2010/07/product-design.html - blog on
thoughts about designing VSTs
More info on diagramming from other sources
- SQL Tuning by Dan Tow - seminal book on the subjet
- Refactoring SQL Applications by Stephan Faroult , chapter 5 - more
ideas about visualizing SQL
- "Designing Efficient SQL: A Visual Approach" by Jonathan Lewis - a
discussion of the power of visualizing SQL.
http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/
<http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/>
The
SQL query from this paper was used in the video on DB Optimizer here:
http://www.youtube.com/watch?v=EoDnR02S2WA
Believe it or not (I wouldn't have 2 years ago) DB Optimizers goal for the
end of next year is to create it's own intelligent execution plans instead
of blindly generating them. If you read the above documents you will see how
to pick good candidates for execution plans and thus instead of doing it
manually we plan to do it in the software. How the heck could we compete
with the native optimizers? by the fact that we do some expensive analysis
of predicate filter percentages and table join result set sizes. Such
expensive analysis is not appropriate fast database response times, but for
queries that get rerun repeatedly and are not correctly optimized by the
database, then it's a great option. Doesn't Oracle's SQL Tuning Advisor do
this? Apparently not - seems like it should.
Also for the load on a database, TOAD, AFAIK, doesn't do process sampling, a
la ASH in Oracle, and ASH really is the only way to correlate bottlenecks,
to users, to SQL statements and have the details to drill into a problem and
resolve it. Here is some reading on profiling/sampling
http://db-optimizer.blogspot.com/2010/01/sampling-we-will-go.html
little more info on profiling:
https://sites.google.com/site/embtdbo/profiler
Here is some info on DB Optimizer verses Quest Spotlight and SQL Optimizer:
https://sites.google.com/site/embtdbo/market-place-profile/spotlight
Best Wishes
Kyle Hailey
http://db-optimizer.blogspot.com/
*
*
On Wed, Oct 13, 2010 at 12:51 PM, Martin Brown <martinfbrown@xxxxxxxxxxx>wrote:
Not familiar with it. What does it offer that TOAD DBA Suite with QUEST SQL
Optimizer does not?
>
> ------------------------------
> Date: Wed, 13 Oct 2010 11:50:47 -0700
> From: kjped1313@xxxxxxxxx
>
> Subject: Re: RE: Special Offer for readers of Oracle-L!
> To: tyfaniew@xxxxxxxxxxx; niall.litchfield@xxxxxxxxx
> CC: Oracle-L@xxxxxxxxxxxxx
>
>
> I also have to add that I like, when performing a Profile, the high
> level "Event" pane and that the "Sessions" are all of the sessions, vs. just
> the top sessions.
>
> I'm still playing with the SQL Optimizer to see how much has improved with
> Embarcadaro's product in the years since I last had anything other than
> AllAround Automation's PL/SQL Developer... :D
>
> I'm soooo deprived... :P
>
> Kellyn Pedersen
> Sr. Database Administrator
> I-Behavior Inc.
> http://www.linkedin.com/in/kellynpedersen
> www.dbakevlar.blogspot.com
>
> "Go away before I replace you with a very small and efficient shell
> script..."
>
>
> --- On *Wed, 10/13/10, Niall Litchfield <niall.litchfield@xxxxxxxxx>*wrote:
>
>
> From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
> Subject: Re: RE: Special Offer for readers of Oracle-L!
> To: tyfaniew@xxxxxxxxxxx
> Cc: Oracle-L@xxxxxxxxxxxxx
> Date: Wednesday, October 13, 2010, 10:18 AM
>
> Try licensing the EM equivalent for the same price......
>
> On 13 Oct 2010 17:05, "Tyfanie Wineriter"
> <tyfaniew@xxxxxxxxxxx<http://us.mc1202.mail.yahoo.com/mc/compose?to=tyfaniew@xxxxxxxxxxx>>
> wrote:
>
> This looks interesting, but it looks exactly like OEM, doing most the
> things
> OEM currently does.
>
> For those of you that enjoy using it... is there something you use this for
> that you can't/don't use with OEM?
>
>
> ~ Tyfanie Wineriter ~
>
> Database Administrator
> University of Oregon
> 1212 University of Oregon
> Eugene, OR 97402-1212
> (541) 346-1366
>
>
> -----Original Message-----
> From:
> oracle-l-bounce@xxxxxxxxxxxxx<http://us.mc1202.mail.yahoo.com/mc/compose?to=oracle-l-bounce@xxxxxxxxxxxxx>[mailto:
> oracle-l-bounce@xxxxxxxxxxxx<http://us.mc1202.mail.yahoo.com/mc/compose?to=oracle-l-bounce@xxxxxxxxxxxx>
> ..
> Subject: Special Offer for readers of Oracle-L!
> Kyle Hailey has wangled us a killer deal on Embarcadero's DB Optimizer, his
> database performance and...
>
>
>
Other related posts:
