ba6.us - Dave's Database Related Stuff

I keep coming across a construct in some legacy SQL that has been causing all kinds of performance issues for us. I guess you could call it using the IN condition with multi-column subqueries. I located the syntax for the IN condition here but it doesn't really get into much detail about using this construct.

Here is an example of the subquery (it returns the lowest salary and department_id for each department):
Read more »

As I get deeper into tuning with Statistics and the CBO I have been paying a lot more attention to hints.

In the past I always tried to avoid hints. After working with the CBO and staistics in recent versions I believe they are necessary at times to give guidance to the CBO.

The Oracle Database SQL Language Reference has a comprehensive list of hints included in the documentation of SQL Comments. The list for 11g is available here: link. Read more »

Iggy Fernandez covers the topic of who is better suited to tune SQL - Developers or DBAs. Also listed are 5 Dangerous Beliefs which may influence the answer to that question.

http://iggyfernandez.wordpress.com/2009/07/12/who-should-tune-sql-the-dba-or-the-developer/

-Dave

In past Oracle versions you may have used USERENV() to access some of this information. The information is now available in the 'USERENV' namespace of the sys_context function.

http://www.techonthenet.com/oracle/functions/sys_context.php

Here is a useful example. When monitoring the current in-flight SQL it is nice to exclude the current query from the results: Read more »