Thank you for this article it just confirmed what I was suspecting - we are new to 10g and we tried the sql profile. It works well for a while and then the query just "runs away" doing millions instead of a couple of thousands of reads. We are in the unfortunate position of NOT HAVING primary/foreign key relationships within our application. Don't ask me, not my choice. But if I understand this article correctly, we should not use sql profile. Tom, do I understand this correctly. I even had a tar open with Oracle and no one could give me an answer.

Let me try to explain: This happened a couple of times. A certain application runs fine, completes in seconds, does about 2000 to 14000 logical reads. The same program hangs up doing millions of logical reads (almost looks like an index probem). While this is happening we try to determine why it is doing so much, could not find any data problems, but after re-analyzing the tables, the problem went away. This behaviour started after migration to 10.2 and installing sql profile for this specific sql. None of the indexes had a problem, also the explain plans looked normal. I suspect that using the sql profile without having primary/foreign keys might cause this? What is your opinion?

Thank you Tom you did ansser my question about the sqlprofile, I will investigate some more - I agree that it was parsed again - the only thing that worries me, is the fact that we did run the stats just before this happened.

Tom,
I always suspected that the issue described in "sql profile" thread can also be generated by bind variable peeking.
a) The query is working fine.
b) The query is not used for sometime and is aged out of shared pool.
c) The next time it is hard parsed, the value for some bind variable causes a very bad plan to be generated.
d) The bad plan is stuck in the shared pool since the query is taking is very long to execute.
e) Analyzing an underlying table will invalidate the existing plan.
f) On next hard parse, a different value for same bind variable causes a better plan to be generated.

The net result is that DBA feels that analyzing a table fixed the query and starts suspecting the statistics. If they checked the query plans before and after analyzing, they will find the difference. It has happened to me many times and the query plans before and after analyzing the tables were always different. Is my reasoning correct?

Thanks

Thank you to "reader" - I think it is most useful and I do agree 100% that we have the exact scenario. This sql only runs daily (mostly in the mornings - weektime). One of the occurences was on a Monday - we do run stats on a Sunday, that is why it was confusing... concentrating on the stats did not give us an answer. So the only solution in a case like this is to analyze one of the underlying tables: TRUE - it did change the execution plan, we just did not understand why it was happening. To answer your question Tom: tables only gets updated (deletes, inserts). We do analyze stats using:
dbms_stats.gather_table_stats(ownname=> 'DCS3000',tabname=> 'TT_WHCP5305_99144', partname=> NULL);

But is there a v$ or dba_* view to see the "auxiliary statistics" associated to the sql profile ?

Thanks !

"believe" - that really hurts

I always thought this vocable belongs into church
and not into asktom

The same scenario - depicted by "A reader" - is tipical in our production database.

Sometime a grant, or a truncate partition, etc. against a table invalidates sql statements, and Oracle picks up a different plan for one of those because of variable peeking (the table is partitioned).

The solution provided above is to use gather_table_stats (with no_invalidate=false) to invalidate the cursor, but this method invalidates too statements and drops too plans in our database!

Do you know how to invalidate ONLY a single cursor in the sqlarea ?

Thank you in advance.
Regards,
EBer

Tom,
I recently ran SQL profile on a query and grid control created a profile. It told me that there is a 65% improvement in CPU used with the profile. Since I can modify the query, I was curious to know what SQL profile had done to improve the query by 65%. Is there a way to see what exactly the profile has done so I can make the same changes manually to my query?

Thanks

Yes, I did see the old plan and new plan, but I think it did not show modifications it did to the old query to get the new plan. Maybe I wasn't looking hard enough. I will check again.

I have one more question about profiles. Can the profile analyze queries with FGAC?

Thanks

it would have been putting corrected cardinalities in the plan.  for example, if you run this procedure:

ops$tkyte@ORA10G> create or replace procedure p
  2  as
  3          cursor c1
  4          is
  5          select object_id, object_name
  6            from sqlprof
  7           order by object_id;
  9          l_object_id   sqlprof.object_id%type;
 10          l_object_name sqlprof.object_name%type;
 11  begin
 12          open c1;
 13          for i in 1 .. 10
 14          loop
 15              fetch c1 into l_object_id, l_object_name;
 16              exit when c1%notfound;
 17              -- ....
 18          end loop;
 19  end;
 20  /                                                                                                             

over and over and over (sqlprof is a copy of all objects) - a sql profile might look like this:

ops$tkyte@ORA10G> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sqlprof_query')
  2    FROM DUAL;
                                                                                                             
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLPROF_QUERY')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
…
                                                                                                             
-------------------------------------------------------------------------------
SQL ID  : 3zfpa86satsm3
SQL Text: SELECT OBJECT_ID, OBJECT_NAME FROM SQLPROF ORDER BY OBJECT_ID
                                                                                                             
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
                                                                                                             
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
                                                                                                             
  Recommendation (estimated benefit: 99.45%)
  ------------------------------------------
    Consider accepting the recommended SQL profile.
    execute :profile_name := dbms_sqltune.accept_sql_profile(task_name =>
                             'sqlprof_query')

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
                                                                                                             
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1044598349
                                                                                                             
--------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         | 47487 |  1391K|       |   546   (3)| 00:00:07 |
|   1 |  SORT ORDER BY     |         | 47487 |  1391K|  3736K|   546   (3)| 00:00:07 |
|   2 |   TABLE ACCESS FULL| SQLPROF | 47487 |  1391K|       |   151   (2)| 00:00:02 |
--------------------------------------------------------------------------------------
                                                                                                             
2- Using SQL Profile
--------------------
Plan hash value: 337606071
                                                                                                             
------------------------------------------------------------------------------------------ | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     | ------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    10 |   300 |     3   (0)|00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SQLPROF    | 47487 |  1391K|     3   (0)|00:00:01 |
|   2 |   INDEX FULL SCAN           | SQLPROF_PK |    10 |       |     2   (0)|00:00:01 |
------------------------------------------------------------------------------------------


<b>based on the execution history - it recognized "10" as being the right number of rows and would favor the index over the full scan at that point</b>


fine grained access control is going to through a wrench into this - since you generally have different predicates for many different groups of people - that single query is really "many queries"
 

Many thanks Tom. Appreciate the example. Now I know how to find out what SQL profile did.

Tom,
How do I pass on the bind_list parameter to create_tuning_task? I have to pass a number 1234 and a varchar '03' as the binds. When I try:
bind_list=>'1234,''03''', I get an error:

PLS-00306: wrong number or types of arguments in call to 'CREATE_TUNING_TASK'

There is no example in the Oracle® Database PL/SQL Packages and Types Reference under DBMS_SQLTUNE on how to do this.

Thanks

That was faster than a sev 1 SR!! It looks like the create_tuning_task still does not like something in my sql_text. How do I pass bind variables in my query in sql_text? Currently I am using ...t1.col1 = :a1 AND t2.col2 = :a2. Is this right?

Sorry I am not posting the entire statement here. It is kind of big and I want to figure out things myself (with your help, of course).

Many thanks!!

Can you please make 5 stars as the default rating? I always forget to select it and get an error.

Looks like I won't be able to fix this on my own. Here is what I am doing:

declare
bind_lst sql_binds;
begin
bind_lst := sql_binds(anydata.convertnumber(670384248),anydata.convertvarchar2('02'));
DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'my sql text', -
bind_list => bind_lst, -
user_name => 'mci_app_pkg', -
time_limit => 1800, -
task_name => 'Tune_ECIS_Query');
end;
/

ERROR at line 5:
ORA-06550: line 5, column 787:
PLS-00707: unsupported construct or internal error [2603]
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

This query is proving to be a problem on one of our databases. If I understand how to run the DBMS_SQLTUNE from command line, it will be a big help.

Thanks for your help.

ops$tkyte@ORA10GR2> declare
  2  bind_lst sql_binds;
  3  begin
  4  bind_lst :=
  5  sql_binds(anydata.convertnumber(670384248),anydata.convertvarchar2('02'));<b>
  6  dbms_output.put_line(</b>
  7  DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'my sql text',
  8  bind_list => bind_lst,
  9  user_name => 'mci_app_pkg',
 10  time_limit => 1800,
 11  task_name => 'Tune_ECIS_Query')
 12  );
 13  end;
 14  /


dbms-sqltune.create_tuning_task is a "function"

bad error message, it should say "no such procedure can be found" 

Looks like it is throwing the error on bind_lst, which is the next parameter after sql_text. Am I passing bind_lst correctly?

dbms_output.put_line(DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'my sql here', -


*
ERROR at line 5:
ORA-06550: line 5, column 808:
PLS-00707: unsupported construct or internal error [2603]
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

Thanks...

The create_tuning_task worked !! I had a syntax error in the block. It was difficult to see because of the large query length.

I thank you very much for all the help rendered so quickly.
It has helped me learn a new feature.

Keep in mind that if you use the DBMS_SQLTUNE package, you're supposed to license the Oracle Tuning Pack.

</code> http://download-east.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm#sthref80 <code>

Tom:

How do I export the profile and import it back?


Thanks,

When I SELECT DBMS_SQLTUNE.report_tuning_task('emp_tuning_task') as recommendation from dual;

RECOMMENDATION
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------


1 row selected.


I did delete existing tasks and reran the creating and executing the tasks. Something else I need to run to see the recommendations? And also, how do we tell Oracle to ignore whitespaces when the SQL is profiled?

SQL> DECLARE
  2   my_task_name VARCHAR2(30);
  3   my_sqltext   CLOB;
  4  BEGIN
  5   my_sqltext := 'SELECT /*+ ORDERED */ * '                      ||
  6                 'FROM employees e, locations l, departments d ' ||
  7                 'WHERE e.department_id = d.department_id AND '  ||
  8                       'l.location_id = d.location_id AND '      ||
  9                       'e.employee_id < :bnd';
 10
 11   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
 12           sql_text    => my_sqltext,
 13           bind_list   => sql_binds(anydata.ConvertNumber(100)),
 14           user_name   => 'SYS',
 15           scope       => 'COMPREHENSIVE',
 16           time_limit  => 60,
 17           task_name   => 'my_sql_tuning_taskA',
 18           description => 'Task to tune a query on a specified employee');
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL> 


SQL> BEGIN
dbms_sqltune.execute_tuning_task (task_name => 'my_sql_tuning_taskA');
END;
/  2    3    4

PL/SQL procedure successfully completed.

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

STATUS
-----------
FATAL ERROR

SQL> 

SQL> SET LONG 230000
SQL> /

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : FATAL ERROR
Started at         : 08/08/2007 01:52:59
Completed at       : 08/08/2007 01:57:49

-------------------------------------------------------------------------------
SQL ID  : 5tqr0fqh8k6dd
SQL Text: select *  from dws.account_holdings         where HLDG_DATE_ID_N
          in                  (select date_id_n                  from
          dws.date_dimension                    where date_dt_d > trunc(sysdate)
          - 10)) ah,            dws.asset_group ag,                     dws.industry
          i                     where a.io_acct_code_c = b.io_acct_code_c         and
          b.io_acct_code_c is not null            and NVL(b.INSTN_EFF_THRU_D,sysdate)
          >= SYSDATE      and a.acct_id_n = ah.acct_id_n          and ah.secu_id_n =
          s.secu_id_n     and s.io_inds_code_c = i.inds_code_c    AND
          ag.Io_Aset_Grp_C=s.Io_Aset_Grp_C


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-16951: Too many bind variables supplied for this SQL statement.

-------------------------------------------------------------------------------


SQL>


Also I need to understand how to add sqls 
like 
***  and b.dspl_code_c in ('ACT','AIE','SEQ','SIN')


Thanks 
Mahalingesh 

Tom,

information is very helpful. I ran into issue that sql statement is not stable with execution plan. It used to run within 80 seconds, sometime it can't run through after 2hours, and the plan was changed with merge cartecian join which really should not. No lock / no contension on temp space when it happens.

I will use sql_profile to see if it could eliminate this problem, and now I am testing it. However, after create task and accept sql_profile, how can I know it will be used? if I create the task with schema name user1, and user2 run similar query, will this profile be used or not?

If I use bind variable to create sql_profile, and user use literal, will sql_profile be used if force_match is set to true?

Also, interesting part is that, even myself is granted with DBA, I can't report tuning task which is created under another user ID using DBMS_SQLTUNE.report_TUNING_TASK. Did I miss any privilge? or it is designed to be this way?

Similar, I have to log in as SYSDBA to get ADDM report by using dbms_advisor.get_task_report. any privilege missed as well?

Thanks in advance, as always

Tom, there is a remote query generated by OWB that changed the access plan. I think the cause is bind peeking. But my question is about Stored Outlines. Is there anyway to put a Hint inside Stored Outlines? I am asking you because I know what is the good plan. OEM 9i had an Outline Manager a GUI and put a hint was very easy to do. http://www.ardentperf.com/wp-content/uploads/2007/03/outln3.gif OEM 10g doesnt have Outline Manager and I dont know how to edit the outline to put hints. Could you help me? Thanks in advanced. Veiga

I have following 2 queries whose output is same. Both queries work well and use hash_join in 4 out of 5 environment. all environments are similar, there could be difference in data.

my question is that in one of the environment, one query used nested loop and second one uses hash_join. The performance of hash_join is good and nested loop is worst.

So, please tell me the reason behind it and does that also means that we should always go with first query syntax, since its always work across any environment.

Here is the query:

select nvl(GRP_GROUP_NME, 'NONE') ---into v_portfolio_nme
from G002_GRPS_MEMBRSHP d1,gnvods_stg_taxlot_rsl i
where MBR_ACCOUNT_NO = i.acct_id
and grp_group_typ = 'BO' --and d1.buss_dt='04-Sep-2009'
and run_id=4 and subtask_id=0

select
( select distinct (nvl(GRP_GROUP_NME, 'NONE')) from
G002_GRPS_MEMBRSHP where grp_group_typ = 'BO' and
MBR_ACCOUNT_NO=r.acct_id)
FROM gnvods_stg_taxlot_rsl r
where run_id=4 and subtask_id=0

Hi Tom,

It has come to my attention that in 11g Oracle automatically creates tuning tasks for sql it thinks is "bad" based off of data in the awr tables (true?).

Not only that, but you can set it to automatically accept the profiles or whatever it suggests. So my question is, wouldn't that be a bad idea to configure it to do that, or is it fool proof and always right. I'm picturing scenarios where it comes up with a lower cost on a plan, but it's actually less efficient than the current one. (Assuming it's basing it's judgment on cost, but you get the idea.)

Also, this feature appears to be very awesome (the automatic tuning tasks). I'd like to create my own job to do the same thing for 10g. Do you know how Oracle is doing this in 11g so I can copy it?

"the auto testing didn't exist in 10g - capturing the high load sql, 10g does that. creates the tuning set, 10g does that. You would just run tuning advisor and then you would test."

Right, this much I knew, what I'm saying is I want to know how it's running the tuning advisor against the set in those tables. I am being blatantly lazy and trying not to reinvent the wheel.

This magical job is spooky. I can't find any evidence of it.

(lx-cplithd-t01p:oracle)> sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 26 16:05:27 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> column comments format a80
SQL> select job_name, comments from DBA_SCHEDULER_JOBS where owner = 'SYS';

JOB_NAME                       COMMENTS
------------------------------ --------------------------------------------------------------------------------
BSLN_MAINTAIN_STATS_JOB        Oracle defined automatic moving window baseline statistics computation job
DRA_REEVALUATE_OPEN_FAILURES   Reevaluate open failures for DRA
FGR$AUTOPURGE_JOB              file group auto-purge job
FILE_WATCHER                   File watcher job
HM_CREATE_OFFLINE_DICTIONARY   Create offline dictionary in ADR for DRA name translation
ORA$AUTOTASK_CLEAN             Delete obsolete AUTOTASK repository data
PURGE_LOG                      purge log job
RSE$CLEAN_RECOVERABLE_SCRIPT   auto clean job for recoverable script
SM$CLEAN_AUTO_SPLIT_MERGE      auto clean job for auto split merge
XMLDB_NFS_CLEANUP_JOB

10 rows selected.

Do you know where it is or better yet where I can see the job definition?

He's my first attempt, I don't understand why it's telling me the sql doesn't exist when I'm querying it from the AWR tables. Where does it need to be?

(p2a3pdc:oracle)> sqlplus /

SQL*Plus: Release 10.2.0.2.0 - Production on Fri May 28 13:45:16 2010

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> DECLARE L_SQL_TUNE_TASK_ID  VARCHAR2(100);
  2
  3  BEGIN
  4
  5  FOR X IN (SELECT A.SQL_ID
  6             FROM DBA_HIST_SQLSTAT A, DBA_HIST_SQLTEXT B
  7            WHERE PARSING_SCHEMA_NAME <> 'SYS'
  8             AND  ELAPSED_TIME_DELTA/1000000/60 > 5) LOOP
  9
 10    L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
 11          SQL_ID      => X.SQL_ID,
 12          SCOPE       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        TASK_NAME   => 'OCMD_TUNING_TASK' || X.SQL_ID,
 13   14          DESCRIPTION => 'TUNING TASK FOR SPECIFIED SQL_ID');
 15    DBMS_OUTPUT.PUT_LINE('L_SQL_TUNE_TASK_ID: ' || L_SQL_TUNE_TASK_ID);
 16    END LOOP;
 17  END;
 18  /
DECLARE L_SQL_TUNE_TASK_ID  VARCHAR2(100);
*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SQLTUNE", line 784
ORA-06512: at line 10

Ugh, ignore that stupid query with the unreferenced table....this is what I meant:

"afiedt.buf" 15 lines, 563 characters

  1  DECLARE L_SQL_TUNE_TASK_ID  VARCHAR2(100);
  2  BEGIN
  3  FOR X IN (SELECT A.SQL_ID
  4             FROM DBA_HIST_SQLSTAT A
  5            WHERE PARSING_SCHEMA_NAME <> 'SYS'
  6             AND  ELAPSED_TIME_DELTA/1000000/60 > 5) LOOP
  7    L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
  8          SQL_ID      => X.SQL_ID,
  9          SCOPE       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
 10          TASK_NAME   => 'OCMD_TUNING_TASK' || X.SQL_ID,
 11          DESCRIPTION => 'TUNING TASK FOR SPECIFIED SQL_ID');
 12    DBMS_OUTPUT.PUT_LINE('L_SQL_TUNE_TASK_ID: ' || L_SQL_TUNE_TASK_ID);
 13    END LOOP;
 14* END;
SQL> /
DECLARE L_SQL_TUNE_TASK_ID  VARCHAR2(100);
*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SQLTUNE", line 784
ORA-06512: at line 7

I have a few reasons why I don't want to use EM, the top of the list is that I didn't know you could and don't know how. There's a million screens and links. It takes 10 minutes just to get the thing up and running.

If I can't get it working on the command line there's no way I'll understand what the problem is through a GUI.

And lastly, I won't learn anything about this clicking buttons.

Doesn't mean I'm still not going to try to use EM, but I'd like to get to know the API it's calling as well.

 1  DECLARE L_SQL_TUNE_TASK_ID  VARCHAR2(100);
  2      BEGIN
  3      FOR X IN (SELECT A.SQL_ID, B.SQL_TEXT, PARSING_SCHEMA_NAME
  4                 FROM DBA_HIST_SQLSTAT A, DBA_HIST_SQLTEXT B
  5                WHERE PARSING_SCHEMA_NAME <> 'SYS'
  6                 AND A.SQL_ID = B.SQL_ID
  7                 AND  ELAPSED_TIME_DELTA/1000000/60 > 5
  8                 AND COMMAND_TYPE = 3)
  9       LOOP
 10       DBMS_OUTPUT.PUT_LINE(X.SQL_TEXT);
 11       L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
 12             SQL_ID      => X.SQL_ID,
 13             SCOPE       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
 14             TASK_NAME   => 'OCMD_TUNING_TASK' || X.SQL_ID,
 15             DESCRIPTION => 'TUNING TASK FOR SPECIFIED SQL_ID');
 16       DBMS_OUTPUT.PUT_LINE(X.SQL_TEXT);
 17      END LOOP;
 18*   END;
SQL> /
SELECT AL1.EMERGENCY_LABEL, AL1.CHANGENUMBER, AL1.APPROVAL_MANAGER, AL1.APPROVING_MANAGER_GROUP, AL1.CHANGE_OWNER,
AL1.PRIMARY_IMPLEMENTER, AL1.APPROVAL_STATUS, AL1.CREATION_DATE, AL1.PLANNED_START, AL1.APPROVAL_TYPE, AL1.APPROVER_OPERATOR,
AL1.APPROVAL_DATE_TIME, AL1.CATEGORY, AL1.ENVIRONMENT, AL1.STATUS, AL1.EMERGENCY_DESIGNATED, AL1.COMPLETION_CODE,
AL1.NONCOMPLIANT_POLICY, AL1.NONCOMPLIANT_PROCESS, AL1.SCENARIO FROM SERVICE_CUSTOM.SC_APPROVALS_POST_IMPLMNT_V AL1 WHERE
(AL1.CREATION_DATE> TO_TIMESTAMP('2009-06-30 23:59:00.000','YYYY-MM-DD HH24:MI:SSXFF'))
DECLARE L_SQL_TUNE_TASK_ID  VARCHAR2(100);
*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SQLTUNE", line 784
ORA-06512: at line 11

DBMS_SQLTUNE.CREATE_TUNING_TASK(<b>
  begin_snap      IN NUMBER,
  end_snap        IN NUMBER,</b>
  sql_id          IN VARCHAR2,
  plan_hash_value IN NUMBER   := NULL,
  scope           IN VARCHAR2 := SCOPE_COMPREHENSIVE,
  time_limit      IN NUMBER   := TIME_LIMIT_DEFAULT,
  task_name       IN VARCHAR2 := NULL,
  description     IN VARCHAR2 := NULL)
RETURN VARCHAR2;

Tom,

I changed my approach a little bit. I'm using the SQL text instead so it doesn't have to exist anywhere.

I am also stuffing the results into a temp table because it's ugly printing using dbms_output.

There's still something wrong though I'm hoping you can help me track it down. Basically, I'm only getting one row inserted into my temp table but there should be 3, based on the results of the driving query.

/database/oracle
(x216kdc:oracle)> sqlplus /

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Jun 11 09:53:26 2010

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from (select a.sql_id, b.sql_text
  2                 from dba_hist_sqlstat a, dba_hist_sqltext b
  3               where parsing_schema_name <> 'SYS'
  4                 and a.sql_id = b.sql_id
               and  elapsed_time_delta/1000000/60 > 5
  5    6                 and command_type = 3);

  COUNT(*)
----------
         3

1 row selected.

SQL> declare
  2
  3  l_sql_tune_task_id  varchar2(100);
  4  l_seq number;
  5  l_report clob;
l_offset number := 1;
  6    7
  8  begin
  9
 10  execute immediate 'create global temporary table sqltune_results(report clob) on commit delete rows';
 11
 12     for x in (select a.sql_id, b.sql_text
 13                 from dba_hist_sqlstat a, dba_hist_sqltext b
 14               where parsing_schema_name <> 'SYS'
 15                 and a.sql_id = b.sql_id
 16                 and  elapsed_time_delta/1000000/60 > 5
 17                 and command_type = 3
 18                 ) loop
 19
 20     select dbms_random.random into l_seq from dual;

 21   22     l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
 23        sql_text    => x.sql_text,
 24        time_limit  => 60,
 25        scope       => dbms_sqltune.scope_comprehensive,
 26        task_name   => 'ocmd_tuning_task' || l_seq,
 27        description => 'tuning task for specified sql_id');
 28
 29     dbms_sqltune.execute_tuning_task(l_sql_tune_task_id);
 30
 31  execute immediate 'insert into sqltune_results (select dbms_sqltune.report_tuning_task(''ocmd_tuning_task'' || :x) from dual)'
 32  using l_seq;

 33   34    end loop;
 35  end;
 36  /

PL/SQL procedure successfully completed.

SQL> select count(*) from sqltune_results;

  COUNT(*)
----------
         1

1 row selected.

I wouldn't be asking for your help if I hadn't already done that. What do you want to see?

I think global temp. table option should be ON COMMIT PRESERVE ROWS instead of ON COMMIT DELETE ROWS. In my test environment (Oracle XE !), you get the same row count in the global temp. table than in first SELECT query.

Thank you both, that was the problem. I knew it was a stupid little thing because I spent too much time on it.

As a side note, why do I have to use dynamic sql to insert into a temporary table? I know you must use it to execute ddl inside of pl/sql, but I figured I could use static sql once it was created.

begin
  execute immediate 'create table t ( x int )';
  for x in ( select * from t ) loop null; end loop;
end;

Ah yes of course, because it wasn't a procedure I wasn't thinking about what happens at compile time.

As for the approach, I am well aware of these things (I was actually waiting for it, pages 403-410 in your last book come to mind). I've been following you for how long now :)

This isn't application code, this is not going to be used in a multi-user, concurrent environment. It's a batch job to run once a week on the weekend. I need to use a temp table to get the formatting looking good, and I don't want to leave it there because this is a command that is going to be offered to all of our markets. We build them to be completely self-contained.

Also, if we have objects sitting out there under a dba account, they will absolutely flagged by auditors somehow, someway, sometime.

Tom,

Do you know what the "_delta" columns mean in dba_hist_sqlstat?

If I have a statement that shows as being executed 1000 times, and elapsed_time_delta shows 11 seconds, is that for each execution it took 11 seconds? Or is it cumulative? I'm confused because I'm looking at a 1 row insert showing 11 seconds in elapsed_time_delta and I don't see how that could possibly be for 1 run.

Hi Tom,

In the 11g documentation it says, in some cases new profiles can be created without new SQL baselines. In such cases there could be multiple SQL profiles for the same SQL statement/ SQL baseline.
When there are multiple SQL profiles for the same SQL statement, how a SQL profile is selected?

Hi Tom,

Recently I have created the sql profile(on OEM 11gR2) for one Sql which is performing poorly from last 2 weeks.

After creating the profile the Query Performs good, which uses the best plan for that query. few days later same query again started using the old plan which was bad.

In OEM this profile show plan was accepted, But fixed column in OEM show NO.how to make this profile to be fixed. I couldn't understand what OEM will run internally while creating the Profile for SQL statments.



Is there any way to tell optimizer to use the best plan for this sql.

It will be good if you give me package name which is used to create the sql profile.