7 Replies Latest reply: May 22, 2012 4:34 AM by Dom Brooks RSS

    SQL_ID in oracle 10g

    822778
      Hi ,


      I have certain doubts regarding the SQL_ID which is introduced since 10g in oracle :

      1) Is SQL_ID unique for every SQL statement? Is it unique across databases? How is the sql_id for a statement decided?

      2) Suppose my Sql statement runs at time A and get sql_id of 'abcd1234' . Again after a few days ( a fortnight for eg ) the same statement (not a single change in my sql statement ) runs on my database at time B,this time however the bounce of the database has been taken. Will the sql_id allotted for the statement this time be 'abcd1234' or some different?

      3) I need this information on sql_id cos the sql adviser has suggested accepting a sql profile for a particular statement. However when i execute the command for accepting the profile,it shows the sql statement does not exist despite the same procedure which has the statement in it being executed very recently.
      Is there any other way i can implement/execute the sql advisor.

      Thanks a ton in advance,

      Regards
      rdxdba
        • 1. Re: SQL_ID in oracle 10g
          anand prakash - oracle
          1) Is SQL_ID unique for every SQL statement? Is it unique across databases? How is the sql_id for a statement decided?
          SQL_ID is unique for every sql statement in the database and its a representation of the hash_value. Based on the sql statement its hash value is generated.
          2) Suppose my Sql statement runs at time A and get sql_id of 'abcd1234' . Again after a few days ( a fortnight for eg ) the same statement (not a single change in my sql statement ) runs on my database at time B,this time however the bounce of the database has been taken. Will the sql_id allotted for the statement this time be 'abcd1234' or some different?
          If there is no change in the sql syntax/statement, its the same as it was then sql_id would be the same.
          3) I need this information on sql_id cos the sql adviser has suggested accepting a sql profile for a particular statement. However when i execute the command for accepting the profile,it shows the sql statement does not exist despite the same procedure which has the statement in it being executed very recently.Is there any other way i can implement/execute the sql advisor.
          What are you running? Copy paste what you are doing to make it easier to understand.

          Anand
          • 2. Re: SQL_ID in oracle 10g
            822778
            Hi Anand,

            Thanks for reply, it was indeed helpful.

            Please find below snippet of the executed statement, the sql_id 5s7agg69q9dn0 mentioned here corresponds to an sql query and the scenario is exactly same as the one mentioned in point 2 of my previous update.


            SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'5s7agg69q9dn0_AWR_tuning_task', task_owner => 'SYS', replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE, FORCE_MATCH => TRUE);
            BEGIN dbms_sqltune.accept_sql_profile(task_name =>'5s7agg69q9dn0_AWR_tuning_task', task_owner => 'SYS', replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE, FORCE_MATCH => TRUE); END;

            *
            ERROR at line 1:
            ORA-13605: The specified task or object 5s7agg69q9dn0_AWR_tuning_task does not
            exist for the current user.
            ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
            ORA-06512: at "SYS.PRVT_ADVISOR", line 5836
            ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 28
            ORA-06512: at "SYS.DBMS_SQLTUNE", line 7133
            ORA-06512: at "SYS.DBMS_SQLTUNE", line 7157
            ORA-06512: at line 1




            Regards,
            rdxdba
            • 3. Re: SQL_ID in oracle 10g
              anand prakash - oracle
              Hi,

              Paste the output of --
              col owner for a15
              col TASK_NAME for a35
              select OWNER,TASK_ID,TASK_NAME,STATUS,STATUS_MESSAGE from dba_advisor_log where TASK_NAME='5s7agg69q9dn0_AWR_tuning_task'
              Anand
              • 4. Re: SQL_ID in oracle 10g
                822778
                Hi anand,

                Sorry for late reply. The query u sent didnt returrn any rows despite of the fact that the procedure which includes these statements was very recently executed ( abt 24 hours back ).


                Regards
                Rdxdba
                • 5. Re: SQL_ID in oracle 10g
                  anand prakash - oracle
                  Hi,

                  Can you paste the output of
                  col owner for a15
                  col TASK_NAME for a35
                  select OWNER,TASK_ID,TASK_NAME,STATUS,STATUS_MESSAGE from dba_advisor_log where TASK_NAME like '%5s7agg69q9dn0_%';
                  
                  select sql_id
                      ,      child_number
                      ,      is_bind_aware
                      ,      is_bind_sensitive
                      ,      is_shareable
                      ,      to_char(exact_matching_signature) sig
                      ,      executions
                     ,      plan_hash_value,
                     sql_plan_baseline
                     from   v$sql
                  where  sql_id = '&sql_id';  --> sql_id for which you are creating the task
                  
                  select SQL_ID,PLAN_HASH_VALUE,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,FETCHES,EXECUTIONS,VERSION_COUNT,ELAPSED_TIME,LAST_ACTIVE_TIME from V$SQLSTATS where sql_id='&sql_id';
                  Also, i remember reading somewhere, problem with lower case task name till 11gR1. Can you create task with full upper case letters and check if you get the same error.

                  Anand

                  Edited by: Anand.. on May 22, 2012 10:10 AM --> added few more sqls
                  • 6. Re: SQL_ID in oracle 10g
                    822778
                    Hi Anand,

                    Unfortunately these queries also return no rows....The procedure was executed on 17th may last. Moreover,the database was bounced on 19th May. I have the associated queries with me and hence will execute them on toad to try and get the sql_id again. Will update you in some time. Thanks a ton for your guidance and support. It has been extremely helpful.


                    Regards
                    Rdxdba
                    • 7. Re: SQL_ID in oracle 10g
                      Dom Brooks
                      The sql id will be consistent for an identicial statment - i.e.'s just a hash value.

                      It's just a hash value so it's not guaranteed unique, but collisions are rare especially with the newer hashing algorithms.

                      For further information, see:
                      http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/

                      If you're looking for a SQL_ID and it's no longer in V$SQL, it may be in DBA_HIST_SQL* tables which you need to be appropriately licensed to access (assume you are since you're trying to use sql profiles).
                      Please find below snippet of the executed statement, the sql_id 5s7agg69q9dn0 mentioned here corresponds to an sql query
                      and the scenario is exactly same as the one mentioned in point 2 of my previous update.
                      ...
                      ERROR at line 1:
                      ORA-13605: The specified task or object 5s7agg69q9dn0_AWR_tuning_task does not exist for the current user.
                      This error is not saying that the SQL is not available (although you're subsequent lookups against v$sql show it isn't).
                      This is telling you that the tuning task does not exist - see DBA_ADVISOR_TASKS.
                      To accept a recommended sql profile, you first have to create and run the tuning task.