14 Replies Latest reply: Mar 29, 2013 11:12 PM by user522961 RSS

    ORA-13780: SQL statement does not exist

    user522961
      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
          How did you get the sql_id ?

          Nicolas.
          • 2. Re: ORA-13780: SQL statement does not exist
            user522961
            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
              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
                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
                  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
                    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
                      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
                        Thank you.
                        select count(sql_id) from v$sql;
                        
                        COUNT(SQL_ID)
                        -------------
                                  969
                        • 9. Re: ORA-13780: SQL statement does not exist
                          995200
                          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
                            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
                              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
                                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
                                  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.