sql

A SQL_ID by any other name... Same SQL / Different Tools / Different SQL_ID

I have been trying to chase down the same SQL being submitted against the same 10.2.0.4 Solaris database from different query tools used in our organization. I have been striking out when trying to match up with SQL IDs.

I connected with 5 different tools (including SQLPLUS on 2 platforms) and submitted the following 4 lines of SQL:

SELECT *
  FROM EMP, 
       DEPT 
 WHERE EMP.DEPTNO=DEPT.DEPTNO
Tags: 

"There was a problem creating a SQL tuning task. ORA-13780: SQL statement does not exist. ORA-06512"

I recently got this error while trying to run a SQL Tuning Task against a SQL statement that was available in AWR but not currently in the library cache.
I got around it by locating the SQL in OEM and using SQL Worksheet to execute it:

Functions in the WHERE clause...

Business logic. Sometimes it lives in the application, sometimes it lives in the database. I'll save the application/db argument for each individual case that I encounter. But if you do have significant business logic in your database, there will probably come a time when someone wants to incorporate some of that logic into a query they are writing.

I am often asked to improve the speed of queries that have User Defined Functions (UDF) in the where clause. Sometimes I am successful at tuning them, sometimes a major structural or logic change is required to get good performance.

Oracle IN Condition and Multi Column Subqueries

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):

Tags: 

Documented Hints available in 11.2...

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.

Tags: 

Food for thought - Who Should Tune SQL - DBA or Developer?

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.

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

-Dave

Make your queries Self Aware...

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:

Tags: 

Pages

Subscribe to sql