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

  • Go to the Performance Tab for your database.
  • Select the Search SQL link.
  • Check the "AWR Snapshots time Period" and select ALL from the dropdown.
  • Enter identifying information for your SQL statement.
  • Click Search.
  • Select the AWR snapshots tab.
  • Select the SQL ID of the statement you are interested in.
  • You are now at the SQL Details screen which shows the Statistics/Activity/Plan/Plan Control/Tuning History/SQL Monitoring tabs.
  • Click SQL Worksheet in the bottom right.
  • A SQL Worksheet window will be presented with your SQL Statement already loaded.
  • Click the Execute button.

Now your statement has been executed and has at least 1 execution plan available in the library cache. Now you can run SQL Tuning advisor on it and review its results. I have also read that you may be able to run SQL Tuning advisor if you can get the statement added to a SQL Tuning Set. I have not tried this yet but will report back if I can give it a shot.

Tags: 
sql
oem
tuning

Comments

Submitted by Kanar (not verified) on Tue, 09/25/2012 - 09:34.

I did this but:

(1) The SQL Statement got a new SQL_ID
(2) It keeps timing out and restarting

Submitted by dave on Wed, 10/03/2012 - 13:43.

Hmm, that is odd. I went round and round with many client tools like SQL*Plus, SQL Developer, Toad and they all gave me different SQL IDs for the same cut and pasted simple queries as noted in my post here http://ba6.us/sql_id_by_any_other_name . Going through OEM was the only way I could get things to match up. I wish there was a "Normalized SQL ID" available for all the SQL that Oracle sees so we could get past this and make sure we are talking about the same SQL!

As far as timing out and restarting, not sure I can be much help there. OEM is a fickle beast.

Add new comment