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!
댓글