tuning

Why does my query have a MERGE JOIN CARTESIAN?

Submitted by dave on Thu, 02/16/2012 - 23:57

Yes, the mention of Cartesian Joins usually makes a DBA's heart skip a beat. The cartesian joins caused by missing join conditions are definitely a pain to deal with - these are the types of joins that can "blow up" temp space and cause all types of alarms to go off.

I didn't find anything in Oracle's official 11g documentation on this particular join method but I did find plenty of articles about issues with it in their support DB. I have chased down a few of these in the past couple of weeks and here is what I found.

Tags: 
performance
tuning
optimization

SQL Tuning Advisor - what profile am I accepting?

Submitted by dave on Thu, 10/13/2011 - 13:35

When working on a SQL Tuning issue I often kick off a SQL Tuning Advisor job on the SQL while I gather info about statistics and other things I like to check when tuning. After I get an idea of where performance might be suffering I use the SQL Tuning Advisor results for a second opion.

Unfortunately more often than not the SQL Tuning Advisor reports it can dramatically improve the performance of a SQL Statement by using a SQL Profile to force a different plan.

Tags: 
sql
oem
tuning

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

Submitted by dave on Tue, 07/26/2011 - 14:30

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:

Tags: 
sql
oem
tuning

Finding Query Block names for a SQL that you can't modify...

Submitted by dave on Tue, 07/20/2010 - 12:36

I recently had an issue where a query being submitted to the database had a large change in performance. I could attribute the change to a recent change in object statistics, but the query wasn't behaving because of a combination of a view, subquery, UNION, and LIKE operators. After spending too much time on this statement I decided to hint it to make it perform like it did before the most recent stats change.

Tags: 
performance
tuning
Hint
SQL Profile

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

Submitted by dave on Mon, 07/20/2009 - 12:35

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

Tags: 
development
sql
pl/sql
tuning
Subscribe to tuning