This discussion is archived
14 Replies Latest reply: Mar 29, 2013 9:12 PM by user522961 RSS

ORA-13780: SQL statement does not exist

user522961 Newbie
Currently Being Moderated
Hi,
on 11g R2 I receive the follwing error :
SQL> variable stmt_task VARCHAR2(64);
SQL> EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'gh9p77g1fr0kd');
BEGIN :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'gh9p77g1fr0kd'); END;

*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 125
ORA-06512: at "SYS.DBMS_SQLTUNE", line 655
ORA-06512: at line 1
Any idea ?

Thank you.
  • 1. Re: ORA-13780: SQL statement does not exist
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    How did you get the sql_id ?

    Nicolas.
  • 2. Re: ORA-13780: SQL statement does not exist
    user522961 Newbie
    Currently Being Moderated
    Thank you Nicolas.

    I ran :
    EXPLAIN PLAN FOR that gave me HASH_VALUE 
    and then :
    SQL> select sql_id, plan_hash_value,to_char(timestamp,'dd/mm/yy hh:mm:ss AM') from DBA_HIST_SQL_PLAN  where PLAN_HASH_VALUE='2522063293';
    
    SQL_ID        PLAN_HASH_VALUE TO_CHAR(TIMESTAMP,'D
    ------------- --------------- --------------------
    gh9p77g1fr0kd      2522063293 21/03/13 05:03:28 PM
    gh9p77g1fr0kd      2522063293 21/03/13 05:03:28 PM
    gh9p77g1fr0kd      2522063293 21/03/13 05:03:28 PM
    Regards.
  • 3. Re: ORA-13780: SQL statement does not exist
    606331 Explorer
    Currently Being Moderated
    please refer below link it may helpful to you...

    http://lefterhs.blogspot.in/2012/07/ora-13767-ora-13780-creating-sql-tuning.html
  • 4. Re: ORA-13780: SQL statement does not exist
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Error:     ORA-13780 (ORA-13780)
    Text:     SQL statement does not exist.
    ---------------------------------------------------------------------------
    Cause:     The user attempted to tune a SQL statement that does not exist.
    Action:     Verify the sql_id and the plan hash value of the statement and
         retry the operation.
  • 5. Re: ORA-13780: SQL statement does not exist
    rcc50886 Journeyer
    Currently Being Moderated
    The sql and execution plan of that sql_id was flushed off from shared pool , So oracle couldn't find it.

    -Thanks
  • 6. Re: ORA-13780: SQL statement does not exist
    user522961 Newbie
    Currently Being Moderated
    Hi,
    Ok. But why query is disapeared so soon (10 minutes after execution)?

    Does it mean that shared pool is very small ?
    SQL> show parameter shared
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------
    hi_shared_memory_address             integer     0
    max_shared_servers                   integer
    shared_memory_address                integer     0
    shared_pool_reserved_size            big integer 23488102
    shared_pool_size                     big integer 0
    shared_servers                       integer     1
    shared_server_sessions               integer
    SQL> show parameter target
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------
    archive_lag_target                   integer     0
    db_flashback_retention_target        integer     1440
    fast_start_io_target                 integer     0
    fast_start_mttr_target               integer     0
    memory_max_target                    big integer 1200M
    memory_target                        big integer 1200M
    parallel_servers_target              integer     32
    pga_aggregate_target                 big integer 0
    sga_target                           big integer 0
    Thank you.
  • 7. Re: ORA-13780: SQL statement does not exist
    sb92075 Guru
    Currently Being Moderated
    SQL> select count(sql_id) from v$sql;
    
    COUNT(SQL_ID)
    -------------
             1041
    what do you get on your DB from SQL above?
  • 8. Re: ORA-13780: SQL statement does not exist
    user522961 Newbie
    Currently Being Moderated
    Thank you.
    select count(sql_id) from v$sql;
    
    COUNT(SQL_ID)
    -------------
              969
  • 9. Re: ORA-13780: SQL statement does not exist
    995200 Newbie
    Currently Being Moderated
    Not always that mean that shared pool is small. There may be some load going on that time on database may be like some big ETL job running in case of DSS system. So things to consider is 1. Size of shared pool and then 2. Activity going on that time on database....

    Regards,
    Navneet
  • 10. Re: ORA-13780: SQL statement does not exist
    user522961 Newbie
    Currently Being Moderated
    Thank you.

    Our shared pool is automatique.

    Do you mean to run SQL Advisor in another time ?
    SQL> select distinct event from v$session;
    DIAG idle wait
    SQL*Net message from client
    SQL*Net message to client
    Space Manager: slave idle wait
    Streams AQ: qmn coordinator idle wait
    Streams AQ: qmn slave idle wait
    Streams AQ: waiting for messages in the queue
    Streams AQ: waiting for time management or cleanup tasks
    VKTM Logical Idle Wait
    pmon timer
    rdbms ipc message
    smon timer
    wait for unread message on broadcast channel
    Any suggestion ?
  • 11. Re: ORA-13780: SQL statement does not exist
    user622061 Newbie
    Currently Being Moderated
    I've already replied, but it seems my answer is lost.

    By any chance, when sql_id is not in v$sql, it can be in the AWR. ( it an AWR snapshot occurs just after the 10 minutes ...)
    to check it :

    select min(snap_id), max(snap_id) from dba_hist_sqlstat where sql_id='&sql_id';

    define snap1=
    define snap2=
    DECLARE
    a VARCHAR2(100);
    BEGIN
    a:= DBMS_SQLTUNE.create_tuning_task ( begin_snap=> &snap1, end_snap=>&snap2, sql_id=> '&sql_id', scope => 'comprehensive', task_name => 'T_&sql_id', time_limit=>7200);
    END;
    /


    Friendly.
  • 12. Re: ORA-13780: SQL statement does not exist
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    user622061 wrote:
    I've already replied, but it seems my answer is lost.
    Maybe just wrong thread : Re: primary database lost

    Nicolas.
  • 13. Re: ORA-13780: SQL statement does not exist
    user522961 Newbie
    Currently Being Moderated
    Thanks all.
    How can I verify if my share pool is well sized ? The same for my cache library ?

    We are taking advantage from memory_target=xxxxxxxxxx

    then shared pool is automatically sized by oracle . Isn't it ?
    SQL> select SHARED_POOL_SIZE_FOR_ESTIMATE, SHARED_POOL_SIZE_FACTOR, ESTD_LC_TIME_SAVED
      2    from v$shared_pool_advice;
                              128                   ,3077               2133
                              176                   ,4231             562036
                              224                   ,5385            1222401
                              256                   ,6154            1792874
                              272                   ,6538            2388126
                              288                   ,6923            2849267
                              304                   ,7308            3182062
                              320                   ,7692            3311317
                              336                   ,8077            3498945
                              352                   ,8462            3558953
                              368                   ,8846            3712735
                              384                   ,9231            3990778
                              400                   ,9615            4020007
                              416                       1            4128956
                              432                  1,0385            4130847
                              448                  1,0769            4131992
                              464                  1,1154            4133056
                              480                  1,1538            4133963
                              496                  1,1923            4134764
                              512                  1,2308            4135424
                              528                  1,2692            4136065
                              544                  1,3077            4136626
                              560                  1,3462            4137148
                              608                  1,4615            4138457
                              656                  1,5769            4139546
                              704                  1,6923            4140400
                              752                  1,8077            4141226
                              800                  1,9231            4141901
                              848                  2,0385            4142447
    
    SQL> show parameter shared_pool
    shared_pool_reserved_size            big integer 23488102
    shared_pool_size                     big integer 0
    SQL> show parameter target
    archive_lag_target                   integer     0
    db_flashback_retention_target        integer     1440
    fast_start_io_target                 integer     0
    fast_start_mttr_target               integer     0
    memory_max_target                    big integer 1200M
    memory_target                        big integer 1200M
    parallel_servers_target              integer     32
    pga_aggregate_target                 big integer 0
    sga_target                           big integer 0
    SQL>
    Thank you.
  • 14. Re: ORA-13780: SQL statement does not exist
    user522961 Newbie
    Currently Being Moderated
    ok.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points