This content has been marked as final. Show 7 replies
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.
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
Can you paste the output of
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.
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';
Edited by: Anand.. on May 22, 2012 10:10 AM --> added few more sqls
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.
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:
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 queryThis error is not saying that the SQL is not available (although you're subsequent lookups against v$sql show it isn't).
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 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.