top of page
Writer's picturekyle Hailey

VST – Visual SQL Tuning , the next generation

If you saw my presentation at HOTSOS 2010 or attended one of my recent Oracle 10g Performance Tuning course, you know I’m excited about Visual SQL Tuning or VST diagrams.

Now, I’m even more excited because we are adding statistics to the diagram! For example:


In the above diagram the #s in red are the two table join set sizes and the numbers in green are the table sizes. I can see immediately that the query should return 0 rows because the join (rentalitem,movierental) returns 0 rows. I can also see that the join (mr,movierental) is a horribly inefficient join it returns over 1 million rows for a join between 2018 rows and 2018 rows.

The VST diagram tells me immediately what’s happening in the query and now with the statistics I can immediately see where there are efficient joins and inefficient joins.

The text for this query, which has none of this information is:


SELECT

cs.customerid,

cs.firstname,

cs.lastname,

mr.rentalid,

mr.duedate,

mr.totalcharge,

ri.itemnumber

FROM

MOVIES.customer cs,

MOVIES.movierental mr,

MOVIES.rentalitem ri

WHERE

LENGTH (cs.lastname) = 5 AND

cs.zip > 75062 AND

1 <>

cs.phone BETWEEN 9625569900 AND 9999569900 AND

ROUND (ri.rentalid) > 10 AND

TRUNC (ri.itemnumber) > 1 AND

mr.totalcharge > (SELECT AVG (totalcharge)

FROM MOVIES.movierental) AND

mr.CUSTOMERID = cs.CUSTOMERID AND

ri.RENTALID = mr.RENTALID


Stay tuned because next week we will be adding filter ratios to the diagram. A filter ratio is the ratio of rows returned after a predicate filter has been applied to a table. In the above diagram CUSTOMER and RENTALITEM both have predicate filters as denoted by the “F” in green.

In DB Optimizer the diagram is context sensitve so if I click on a table in the diagram the text will be highlighted


VST diagrams are available now in DB Optimizer 2.0 but in May we are scheduled to release VST diagrams with displayed statistics in with DB Optimizer 2.5 !

Big thanks to DB Optimizer’s development team for this awesome work. Special kudo’s to Stan who’s recuperating and itching to even start working again even from his bed!

14 views0 comments

Recent Posts

See All

Kommentare


bottom of page