03 Jul Applying a SQL Profile and the ORA-13786 error
Here is a quick tip that mainly comes from an OEM bug.
In OEM you had a tuning task, and the result of this tuning tasks is advising you that if you apply a profile you go from a Full table scan to a Parallel Execution of the FTS , reducing your CPU cost.
You can view the results of the Tuning Advisor Task with the query below
TESTDB1 >SET LONG 10000000;
TESTDB1 >COLUMN RECOMMENDATIONS FORMAT A200 WORD_WRAPPED
TESTDB1 >SET pagesize 0
TESTDB1 >SET LONG 10000000
TESTDB1 >SET feed OFF
TESTDB1 >SET TRIMSPOOL ON
TESTDB1 >SET linesize 200
TESTDB1 >SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&TASK_NAME') AS RECOMMENDATIONS
2 FROM DUAL;
Enter value for task_name: SQL_TUNING_123456789
old 1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&TASK_NAME') AS RECOMMENDATIONS
new 1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_123456789') AS RECOMMENDATIONS
In there you will find the recomendations, like the one below and you will try to make a decision
From this
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1362235188
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5294K| 247M| | 597K (1)| 01:59:36 |
| 1 | SORT ORDER BY | | 5294K| 247M| 344M| 597K (1)| 01:59:36 |
| 2 | TABLE ACCESS FULL| TEST_TABLE | 5294K| 247M| | 533K (1)| 01:46:41 |
---------------------------------------------------------------------------------------------
To This
2- Using Parallel Execution
---------------------------
Plan hash value: 2286333026
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5294K| 247M| | 3082 (1)| 00:00:37 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 5294K| 247M| | 3082 (1)| 00:00:37 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 5294K| 247M| 344M| 3082 (1)| 00:00:37 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5294K| 247M| | 3079 (1)| 00:00:37 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5294K| 247M| | 3079 (1)| 00:00:37 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 5294K| 247M| | 3079 (1)| 00:00:37 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| TEST_TABLE | 5294K| 247M| | 3079 (1)| 00:00:37 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
But when you try to apply it , you get this error:
There was a problem creating the SQL Profile ORA-13786: missing SQL text of statement object “1” for tuning task
The thing is that there is a bug in OEM 11g where if it’s for a parallel execution, it errors out , this is caused by bug 10313110 in OEM.
The quick workaround is that you just have to apply it manually with the option DBMS_SQLTUNE.PX_PROFILE and you are set 🙂
DECLARE
l_sql_tune_task_id VARCHAR2(200);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => 'SQL_TUNING_123456789',
name => 'TEST_PROFILE',
force_match => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
END;
/
Sorry, the comment form is closed at this time.