It's great that I don't have to set my NLS_DATE_FORMAT everytime I connect.
BUT, one of the changes to the Explain Plan is that some operations are being coloured red, including Cartesian Joins and Full Table Scans.
1.0 was fine. Nice, simple, black and white.
Using Explain Plan, Oracle tells us the optimal plan that the Optimizer came up with for the query we gave it. It doesn't tell us the second best plan, and keep the best hidden away. It doesn't tell us the plans it rejected. It doesn't tell us "But you'd do better with a TO_CHAR on this to make us of an index" or "The stats indicate you don't have any nulls in this column, but it does allow nulls so I'm having to avoid this index". If you asked Oracle to join a table to itself on the primary key, the Explain Plan still wouldn't say "Are you just plain stupid ?" In short, the Explain Plan does NOT tell us what's wrong with an SQL.
10G has introduced features to make suggestions about how to improve SQLs, for example suggesting new or changed indexes. Those features are not part of the explain plan. The Explain Plan describes what the optimizer decided was the best access path, with the cardinality and costs giving a (vague) suggestion as to why it might have decided that. The 10053 event can be used to obtain very detailed trace information on why a particular plan was chosen ahead of other plans.
A developer (or DBA) can look at an Explain Plan, and (if desperate) the 10053 trace, and use their knowledge of the business, the application, the data and the requirements to see if Oracle has made any incorrect assumptions based on insufficient data.
Quite simply, you cannot look at an Explain Plan and, without any additional information, say "This plan is BAD". Worse is the concept of saying "This operation in this plan is bad". The plan is the sum of the operations. You can't just excise one and pretend that there's a magic way of doing that process for free. Even worse than that is saying "This operation is pretty much always a bad idea."
Sticking a highlighter over the Explain Plan is trying to turning the very complex process of query optimization into a childs game of follow-the-leader and it won't work.
"FULL SCANs must be bad, SQL Developer Says so."
I say NO, and I'll keep a SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) in my snippets folder.
6 comments:
I just removed the colors in the source and the next update of the tool will have that. The idea is that the user can define colors for the different operations however the prefernece pane for that has not been made yet.
Just a note to say that Quest SQL Navigator does color the "TABLE ACCESS FULL" in red as well, even if you select * from dual.
Of course, full table scans on inapropriate tables are a very very common symptom of missing indexes...
I've always found the highlighting in SQL Navigator to be quite useful since it reminds me the check that the full table scan is appropriate. And in 8 years of Oracle I can only remember a handful of times when a cartesian join was actually required.
As with everything else out there, you need to understand what it's saying before you act. That doesn't mean the highlighter isn't useful!
I'm impressed with the support for SQL Developer. That's a quick response. I think I may not have been so irked if the highlight was in blue rather than red. Blue is a more 'hey look at me' whereas red is 'hey, somethings wrong'.
Personally, I'm not convinced by the 'missing indexes' argument, but if you expand it to 'invalid indexes' I'd be inclined to agree.
As for cartesian joins, even an SQL as simple as
SELECT ord.order_date, item.qty
FROM orders ord, order_item item
WHERE ord.order_no =:ord_no
AND item.order_no =:ord_no
AND item.item_no=:ord_item_no
may use a cartesian join. Most of the times I see a cartesian join, it is between single row sets.
Good on you, sql developer folks, for listening.
That's a lot more than has been happening in other areas of Oracle.
Gotta give it another try, I reckon, just for that!
Well Query Optimization is a complex thing . Overasll I have found SQLDeveloper quite good.
Post a Comment